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

benchmark effects of innodb change buffer

Details

    Description

      In order to make an educated decision to drop the change buffer, benchmark suitable workloads and check effect of change buffer turned on vs. off.

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-515 innodb bulk insert

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Task - A task that needs to be done. MDEV-11634 Improve the InnoDB change buffer

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            axel Axel Schwenke added a comment - - edited

            I did a benchmark with 10.2.9 and sysbench OLTP write-only. The workload is 18 updates per transaction, changing the value of an indexed column. I have 3 result sets, restricting the size of the change buffer to 25% (default), 50% and 0% (aka turned off) of the buffer pool.

            Numbers (transactions per second, bigger=better)

            threads 25%     50%     0%
            		 
            1       436.93  454.15  414.97
            		 
            2       1128.5  1108.0  1072.1
            		 
            4       2179.0  2152.6  2256.9
            		 
            8       4353.1  4355.5  4169.6
            		 
            16      7659.4  7623.6  7181.1
            		 
            32      10250   10415   10317
            		 
            64      11032   11139   10782
            		 
            128     11447   11543   11104
            		 
            256     11424   11452   11161

            Conclusion: turning off the change buffer does affect performance, mostly for light to medium load.

            axel Axel Schwenke added a comment - - edited I did a benchmark with 10.2.9 and sysbench OLTP write-only. The workload is 18 updates per transaction, changing the value of an indexed column. I have 3 result sets, restricting the size of the change buffer to 25% (default), 50% and 0% (aka turned off) of the buffer pool. Numbers (transactions per second, bigger=better) threads 25% 50% 0% 1 436.93 454.15 414.97 2 1128.5 1108.0 1072.1 4 2179.0 2152.6 2256.9 8 4353.1 4355.5 4169.6 16 7659.4 7623.6 7181.1 32 10250 10415 10317 64 11032 11139 10782 128 11447 11543 11104 256 11424 11452 11161 Conclusion: turning off the change buffer does affect performance, mostly for light to medium load.
            marko Marko Mäkelä added a comment -

            axel, thank you for running the benchmarks.
            I think that we should rerun the benchmark after implementing InnoDB bulk-loading (DML transactions on an empty InnoDB table or partition), some time during the 10.4 development.

            marko Marko Mäkelä added a comment - axel , thank you for running the benchmarks. I think that we should rerun the benchmark after implementing InnoDB bulk-loading (DML transactions on an empty InnoDB table or partition), some time during the 10.4 development.
            marko Marko Mäkelä added a comment -

            MDEV-515 will not make it to 10.4, but will hopefully be in 10.5.

            marko Marko Mäkelä added a comment - MDEV-515 will not make it to 10.4, but will hopefully be in 10.5.

            People

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