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

Performance regression for sysbench oltp_read_write test in 10.4.17

    XMLWordPrintable

Details

    Description

      Try to run sysbench oltp_read_write test on 10.4.17 vs 10.4.15 and 10.5.8 vs 10.5.6, with several tables and notably more concurrent threads than you have cores, while total size of tables created is notably smaller than the buffer pool.

      For short runs (100 or 300 seconds) at least you'll notice a clear drop in throughput in QPS and TPS, around 15%, and notable increase in 95th percentile latency.

      Consider the following example when MariaDB is started with only the following non-default settings besides port/socket:

      MariaDB [(none)]> select @@innodb_buffer_pool_size, @@innodb_flush_log_at_trx_commit;
      +---------------------------+----------------------------------+
      | @@innodb_buffer_pool_size | @@innodb_flush_log_at_trx_commit |
      +---------------------------+----------------------------------+
      |                1073741824 |                                2 |
      +---------------------------+----------------------------------+
      1 row in set (0.000 sec)
      

      and 5 tables 100000 rows each are created:

      [openxs@fc31 maria10.4.17]$ sysbench oltp_read_write --db-driver=mysql --tables=5 --table-size=100000 --mysql-user=openxs --mysql-socket=/tmp/mariadb.sock --mysql-db=sbtest --threads=4 prepare
       
      ...
       
      MariaDB [(none)]> show table status from sbtest like 'sbtest1'\G
      *************************** 1. row ***************************
                  Name: sbtest1
                Engine: InnoDB
               Version: 10
            Row_format: Dynamic
                  Rows: 100000
        Avg_row_length: 225
           Data_length: 22593536
       Max_data_length: 0
          Index_length: 1589248
             Data_free: 2097152
        Auto_increment: 100001
           Create_time: 2020-11-24 10:44:53
           Update_time: 2020-11-24 10:44:51
            Check_time: NULL
             Collation: latin1_swedish_ci
              Checksum: NULL
        Create_options:
               Comment:
      Max_index_length: 0
             Temporary: N
      1 row in set (0.000 sec)
      

      With 10.4.15 on Fedora 31 I get the following on my old quadcore test box:

      [openxs@fc31 maria10.4.15]$ sysbench oltp_read_write --db-driver=mysql --tables=5 --table-size=100000 --mysql-user=openxs --mysql-socket=/tmp/mariadb.sock --mysql-db=sbtest --threads=32 --time=100 run
      sysbench 1.1.0-174f3aa (using bundled LuaJIT 2.1.0-beta2)
       
      Running the test with following options:
      Number of threads: 32
      Initializing random number generator from current time
       
       
      Initializing worker threads...
       
      Threads started!
       
      SQL statistics:
          queries performed:
              read:                            1226148
              write:                           350328
              other:                           175164
              total:                           1751640
          transactions:                        87582  (875.32 per sec.)
          queries:                             1751640 (17506.49 per sec.)
          ignored errors:                      0      (0.00 per sec.)
          reconnects:                          0      (0.00 per sec.)
       
      Throughput:
          events/s (eps):                      875.3245
          time elapsed:                        100.0566s
          total number of events:              87582
       
      Latency (ms):
               min:                                    2.13
               avg:                                   36.54
               max:                                 1614.12
               95th percentile:                      125.52
      ...
      

      while with 10.4.17:

      [openxs@fc31 maria10.4.17]$ sysbench oltp_read_write --db-driver=mysql --tables=5 --table-size=100000 --mysql-user=openxs --mysql-socket=/tmp/mariadb.sock --mysql-db=sbtest --threads=32 --time=100 run
      sysbench 1.1.0-174f3aa (using bundled LuaJIT 2.1.0-beta2)
       
      Running the test with following options:
      Number of threads: 32
      Initializing random number generator from current time
       
       
      Initializing worker threads...
       
      Threads started!
       
      SQL statistics:
          queries performed:
              read:                            1058232
              write:                           302352
              other:                           151176
              total:                           1511760
          transactions:                        75588  (755.07 per sec.)
          queries:                             1511760 (15101.33 per sec.)
          ignored errors:                      0      (0.00 per sec.)
          reconnects:                          0      (0.00 per sec.)
       
      Throughput:
          events/s (eps):                      755.0666
          time elapsed:                        100.1077s
          total number of events:              75588
       
      Latency (ms):
               min:                                    2.14
               avg:                                   42.34
               max:                                 1113.80
               95th percentile:                      170.48
      ...
      

      The difference is visible with 8 and 16 threads as well. So, there is a systematic notable performance regression in recent 10.4.x (and 10.5.x, for that matter) for this test.

      Attachments

        1. sysbench_result_standalone_10.5.8_innodb_thread_concurrency_0.txt
          4 kB
          MikaH
        2. sysbench_result_standalone_10.5.6_innodb_thread_concurrency_0.txt
          4 kB
          MikaH
        3. MDEV-24272.txt
          3 kB
          Axel Schwenke
        4. MDEV-24272.pdf
          22 kB
          Axel Schwenke
        5. MDEV-24272.my.cnf
          0.6 kB
          Axel Schwenke

        Issue Links

          Activity

            People

              axel Axel Schwenke
              valerii Valerii Kravchuk
              Votes:
              2 Vote for this issue
              Watchers:
              7 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.