Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-42155

Allow MyISAM INSERT … SELECT to use ordinary INSERT flushing behavior

    XMLWordPrintable

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`?!

      Attachments

        Activity

          People

            Unassigned Unassigned
            bwoebi Bob Weinand
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.