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

TPC-C in-memory performance degradation (dblwr + s.t. more)

    XMLWordPrintable

Details

    Description

      I'm evaluating MariaDB 10.5.9 performance against 10.5.5 as was described in https://www.percona.com/blog/2020/08/14/evaluating-performance-improvements-in-mariadb-10-5-5/ . Also I tried current 10.6 (as of 2ad61c678243dec2a808b1f67b5760a725163052) and noticed significant performance degradation against 10.5.9 on in-memory workload (see mariadb_all.png).

      Data size produced with sysbench-tpcc is 104G and buffer pool is 116G, so this is pure in-memory workload (all buffer_LRU_batch* metrics are zero). The testbed machine has slow NVMe drive (26 KIOPS for random writes and 320 KIOPS for random reads), 128GB RAM and 40 hyperthreads.

      While the original tests are 3 hours, 10.6 shows much slower performance right at the begin of the test, so I reproduced the problem on 10 minutes workloads. As tpcc_mariadb-10.6_116bp_600.stat and tpcc_mariadb-10.5.9_116bp_600.stat sysbench statistic reports there are 3845 TPS for 10.6 and 4965 TPS for 10.5.9.

      10.6 and 10.5.9 have quite different I/O and CPU profiles shown in the .dstat files. Since this is in-memory workload, I suspected either doublewrite buffer or checkpointing. I reran the tests with doublewrite buffer switched off (_nodblwr.stat files): 10.6 is still slower than 10.5.9, but 10.6 is much more affected by switching off doublewrite buffer, than 10.5.9. So there is some problem with double write, but there is definitely something more.

      I collected some InnoDB LRU, flushing, checkpointing, and log metrics with 10 second intervals (*.metrics files), but I didn't find any significant differences except log_lsn_checkpoint_age (see checkpoint.png). There were more or less the same number of checkpoints, page flushes, and log writes.

      10.6 was built with io_uring, but I checked with gbd, that there were no aio_uring::submit_io() calls.

      Attachments

        1. 10.5.9.metrics
          139 kB
        2. 10.6.metrics
          129 kB
        3. checkpoint.png
          checkpoint.png
          11 kB
        4. mariadb_10.5.9_116g_600.dstat
          39 kB
        5. mariadb_10.6_116g_600.dstat
          39 kB
        6. mariadb_6_vs_59_boxplot.png
          mariadb_6_vs_59_boxplot.png
          9 kB
        7. mariadb_6_vs_59_io_boxplot.png
          mariadb_6_vs_59_io_boxplot.png
          8 kB
        8. mariadb_6_vs_59_io.png
          mariadb_6_vs_59_io.png
          16 kB
        9. mariadb_6_vs_59.png
          mariadb_6_vs_59.png
          16 kB
        10. mariadb_all.png
          mariadb_all.png
          8 kB
        11. mariadb_all.png
          mariadb_all.png
          49 kB
        12. tpcc_10.6_1h_multi.png
          tpcc_10.6_1h_multi.png
          66 kB
        13. tpcc_mariadb-10.5.9_116bp_600_nodblwr.stat
          77 kB
        14. tpcc_mariadb-10.5.9_116bp_600.stat
          76 kB
        15. tpcc_mariadb-10.6_110bp_3h.stat
          1.24 MB
        16. tpcc_mariadb-10.6_116bp_600_nodblwr.stat
          76 kB
        17. tpcc_mariadb-10.6_116bp_600.stat
          76 kB
        18. tpcc2.pdf
          197 kB

        Issue Links

          Activity

            People

              marko Marko Mäkelä
              krizhanovsky Alexander Krizhanovsky (Inactive)
              Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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