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

Scale the purge effort according to the workload

    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.)

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                kevg Eugene Kosov
                Reporter:
                marko Marko Mäkelä
              • Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated: