Details
-
New Feature
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
13.0.2
-
None
-
None
-
Linux 6.8.0
Description
We use MyISAM tables for a latency-sensitive application, where latency is much more important than durability in a power-loss event. We use `INSERT … SELECT` to derive attributes from a template table and insert them into a quite large GB-sized target table.
In fact our bulk insertion is split into statements of up to 100 rows to balance throughput against table-lock duration.
We observed on MariaDB 13.0.2 that completion of `INSERT … SELECT` explicitly flushes and syncs the destination table’s index and data files. This repeatedly causes `fdatasync()` calls; sometimes they spike in latency (e.g. due to the underlying storage), causing stalls of hundreds of milliseconds. (Which is generally unacceptable for us.)
Tracing the sync syscalls through the public mariadb server code, we see `HA_EXTRA_END_COPY` being emitted unconditionally in `select_insert::prepare_eof`, having the same behaviour than `HA_EXTRA_FLUSH` (i.e. unconditional key cache sync).
This happens despite us setting all of:
delay_key_write = ALL
flush = OFF
flush_time = 0
I assume the flushing behaviour of `INSERT ... SELECT` exists in that way to optimize total duration of large inserts, rather than supporting efficient insertion of small resultsets.
We do even know exactly how large the inserts will be beforehand, but there's no way to tell the mariadb server currently on what plan would be optimal.
We would like an opt-in mode that uses flushing behavior comparable to ordinary `INSERT … VALUES`.
Be it by keyword or some optimizer hint.
Unless, it actually is a bug, that `INSERT ... SELECT` does an unconditional key flush despite `delay_key_write = ALL`?!