InnoDB plugin versus built-in InnoDB

Preamble

As already mentioned InnoDB release that is included in MySQL 5.5.x (and so called built-in) is what was previously called InnoDB plugin in MySQL 5.1.x and which is not activated by default (we have already seen in this post how to activate it).

Benchmarks have been done with Sysbench and DBT-2 where we have already seen their installation.

All test have been done with below InnoDB configuration:

innodb_fast_shutdown=0
innodb_additional_mem_pool_size = 16M
innodb_data_home_dir = /mysql/data02/
innodb_data_file_path = ibdata1:10M:autoextend:max:1G
innodb_log_group_home_dir = /mysql/data02/
innodb_buffer_pool_size = 100M
innodb_log_file_size = 25M
innodb_log_buffer_size = 8M
innodb_autoextend_increment = 10

With additionally for InnoDB plugin:

# innodb plugin
ignore_builtin_innodb
plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so
innodb_file_format=barracuda
innodb_strict_mode=1

Sysbench

The command to launch Sysbench benchmark is something like (parameter for read only mode is oltp-read-only):

sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=yjaquier --mysql-password=password --mysql-host=server1.domain.com --mysql-port=3306 --max-time=300 --oltp-read-only=off --max-requests=0 --num-threads=4 run

MySQL 5.1.49sp1 with InnoDB plugin (read/write mode)

OLTP test statistics:
    queries performed:
        read:                            471772
        write:                           168490
        other:                           67396
        total:                           707658
    transactions:                        33698  (112.32 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 640262 (2134.05 per sec.)
    other operations:                    67396  (224.64 per sec.)
 
Test execution summary:
    total time:                          300.0225s
    total number of events:              33698
    total time taken by event execution: 1199.0227
    per-request statistics:
         min:                                 14.91ms
         avg:                                 35.58ms
         max:                                820.20ms
         approx.  95 percentile:              69.20ms
 
Threads fairness:
    events (avg/stddev):           8424.5000/22.07
    execution time (avg/stddev):   299.7557/0.01

MySQL 5.1.49sp1 with InnoDB plugin (read only mode)

OLTP test statistics:
    queries performed:
        read:                            902482
        write:                           0
        other:                           128926
        total:                           1031408
    transactions:                        64463  (214.87 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 902482 (3008.19 per sec.)
    other operations:                    128926 (429.74 per sec.)
 
Test execution summary:
    total time:                          300.0087s
    total number of events:              64463
    total time taken by event execution: 1198.2452
    per-request statistics:
         min:                                  4.53ms
         avg:                                 18.59ms
         max:                                449.48ms
         approx.  95 percentile:              30.49ms
 
Threads fairness:
    events (avg/stddev):           16115.7500/47.93
    execution time (avg/stddev):   299.5613/0.01

MySQL 5.1.49sp1 with built-in InnoDB (read/write mode)

OLTP test statistics:
    queries performed:
        read:                            314020
        write:                           112150
        other:                           44860
        total:                           471030
    transactions:                        22430  (74.76 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 426170 (1420.37 per sec.)
    other operations:                    44860  (149.51 per sec.)
 
Test execution summary:
    total time:                          300.0425s
    total number of events:              22430
    total time taken by event execution: 1199.4879
    per-request statistics:
         min:                                 17.87ms
         avg:                                 53.48ms
         max:                                711.27ms
         approx.  95 percentile:              76.73ms
 
Threads fairness:
    events (avg/stddev):           5607.5000/7.26
    execution time (avg/stddev):   299.8720/0.01

MySQL 5.1.49sp1 with built-in InnoDB (read only mode)

OLTP test statistics:
    queries performed:
        read:                            929516
        write:                           0
        other:                           132788
        total:                           1062304
    transactions:                        66394  (221.30 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 929516 (3098.23 per sec.)
    other operations:                    132788 (442.60 per sec.)
 
Test execution summary:
    total time:                          300.0150s
    total number of events:              66394
    total time taken by event execution: 1198.2235
    per-request statistics:
         min:                                  5.90ms
         avg:                                 18.05ms
         max:                                 96.72ms
         approx.  95 percentile:              26.39ms
 
Threads fairness:
    events (avg/stddev):           16598.5000/78.81
    execution time (avg/stddev):   299.5559/0.01

MySQL 5.5.9 (read/write mode)

OLTP test statistics:
    queries performed:
        read:                            453488
        write:                           161960
        other:                           64784
        total:                           680232
    transactions:                        32392  (107.97 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 615448 (2051.37 per sec.)
    other operations:                    64784  (215.93 per sec.)
 
Test execution summary:
    total time:                          300.0185s
    total number of events:              32392
    total time taken by event execution: 1198.9965
    per-request statistics:
         min:                                 17.18ms
         avg:                                 37.02ms
         max:                               3146.80ms
         approx.  95 percentile:              60.17ms
 
Threads fairness:
    events (avg/stddev):           8098.0000/14.71
    execution time (avg/stddev):   299.7491/0.01

MySQL 5.5.9 (read only mode)

OLTP test statistics:
    queries performed:
        read:                            810964
        write:                           0
        other:                           115852
        total:                           926816
    transactions:                        57926  (193.08 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 810964 (2703.10 per sec.)
    other operations:                    115852 (386.16 per sec.)
 
Test execution summary:
    total time:                          300.0128s
    total number of events:              57926
    total time taken by event execution: 1198.3991
    per-request statistics:
         min:                                  5.90ms
         avg:                                 20.69ms
         max:                                576.58ms
         approx.  95 percentile:              29.86ms
 
Threads fairness:
    events (avg/stddev):           14481.5000/147.18
    execution time (avg/stddev):   299.5998/0.00

DBT-2

The command to launch DBT-2 benchmark is something like:

sh scripts/run_workload.sh -c 20 -d 300 -t 20 -n -w 5 -s 10 -u yjaquier -x password

MySQL 5.1.49sp1 with InnoDB plugin

                         Response Time (s)
 Transaction      %    Average :    90th %        Total        Rollbacks      %
------------  -----  ---------------------  -----------  ---------------  -----
    Delivery   3.98      2.370 :     2.930          501                0   0.00
   New Order  44.86      2.279 :     2.824         5651               65   1.16
Order Status   3.61      2.037 :     2.501          455                0   0.00
     Payment  41.86      2.670 :     3.444         5274                0   0.00
 Stock Level   5.69      2.143 :     2.691          717              237  49.38
------------  -----  ---------------------  -----------  ---------------  -----
 
1107.89 new-order transactions per minute (NOTPM)
5.0 minute duration
0 total unknown errors
1 second(s) ramping up

MySQL 5.1.49sp1 with built-in InnoDB

                         Response Time (s)
 Transaction      %    Average :    90th %        Total        Rollbacks      %
------------  -----  ---------------------  -----------  ---------------  -----
    Delivery   3.79      2.885 :     3.589          390                0   0.00
   New Order  43.76      2.786 :     3.524         4502               41   0.92
Order Status   3.87      2.470 :     3.179          398                0   0.00
     Payment  42.36      3.257 :     4.254         4358                0   0.00
 Stock Level   6.23      2.568 :     3.217          641              214  50.12
------------  -----  ---------------------  -----------  ---------------  -----
 
886.96 new-order transactions per minute (NOTPM)
5.0 minute duration
0 total unknown errors
0 second(s) ramping up

MySQL 5.5.9

                         Response Time (s)
 Transaction      %    Average :    90th %        Total        Rollbacks      %
------------  -----  ---------------------  -----------  ---------------  -----
    Delivery   4.15      2.772 :     3.442          539                0   0.00
   New Order  43.80      2.386 :     3.030         5685               65   1.16
Order Status   4.13      2.055 :     2.465          536                0   0.00
     Payment  42.37      2.309 :     2.982         5500                0   0.00
 Stock Level   5.55      2.464 :     3.105          720              242  50.63
------------  -----  ---------------------  -----------  ---------------  -----
 
1118.46 new-order transactions per minute (NOTPM)
5.0 minute duration
0 total unknown errors
1 second(s) ramping up

Conclusion

Transactions/minute Sysbench (read/write) Sysbench (read only) DBT-2
MySQL 5.1.49sp1 Built-in InnoDB 74.76 221.30 886.96
MySQL 5.1.49sp1 InnoDB plugin 112.32 214.87 1107.89
MySQL 5.5.9 107.97 193.08 1118.46

Apparently there is not so much difference between InnoDB 1.1 (Mysql 5.5.x) and InnoDB 1.0 (MySQL 5.1.x InnoDB plugin), the difference is anyway clearly visible versus built-in InnoDB. Obviously those small test are not exhaustive but as most of the people are still using the built-in InnoDB they should see huge performance difference when migrating to MySQL 5.5.x. Another good point to upgrade with no delay to this latest release…

References

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>