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

Scale the purge effort according to the workload

    XMLWordPrintable

Details

    Description

      InnoDB in MySQL 5.6 (and MariaDB 10.0) introduced multiple inodb_purge_threads (4 by default).

      When there are few modifications that require purging, the purge threads will either be mostly sitting idle, or they will form ‘thundering herds’ whenever some work turns up. In such a scenario, it would be better to have only one purge worker.

      With this work, the poorly working parameter innodb_max_purge_lag should be revised or removed as well. The logic behind that parameter does not take into account the actual number of purgeable records; committed modifications cannot be purged while old read views exist that can access the old data.

      Benchmarking is the key

      This work must involve creating a benchmark to measure purge performance under various conditions:

      1. Ensure that everything has been purged (history list length is 0).
      2. Block purge by issuing START TRANSACTION WITH CONSISTENT SNAPSHOT from a new connection.
      3. From other connections, perform modifications that create workload for purge. Try different variations: tables with many indexes, many small transactions on many tables, large transactions on a single table.
      4. Commit or disconnect the connection that blocked purge.
      5. Measure the time to reach History list length 0 in SHOW ENGINE INNODB STATUS. (Some workaround for the batching logic related to innodb_purge_rseg_truncate_frequency may be needed.)
      6. Alternatively, measure the time to shutdown with innodb_fast_shutdown=0. But be sure to separate the impact of innodb_change_buffering. (Slow shutdown runs both purge and change buffer merge to completion.)
      7. We might also want to run with innodb_force_recovery=2 to disable the purge altogether, and then restart without that parameter and initiate a slow shutdown, to measure how long it takes to process all the undo log that was accumulated.

      Attachments

        1. mariadb-sys-purge-shutdown2.perf.txt
          140 kB
          Daniel Black
        2. mariadb-sys-purge-shutdown2.svg
          426 kB
          Daniel Black
        3. mariadb-sys-purge-shutdown3.perf.txt
          93 kB
          Daniel Black
        4. mariadb-sys-purge-shutdown3.svg
          417 kB
          Daniel Black
        5. mariadb-sys-purge-shutdown-no-dblbfr.txt
          424 kB
          Daniel Black

        Issue Links

          Activity

            People

              marko Marko Mäkelä
              marko Marko Mäkelä
              Votes:
              4 Vote for this issue
              Watchers:
              12 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.