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 |
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 |
# 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 |
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 |
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.20msThreads 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 |
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.49msThreads 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 |
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.73msThreads 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 |
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.39msThreads 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 |
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.17msThreads 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 |
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.86msThreads 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 |
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 |
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 |
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 |
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
About Post Author
administrator
Find more about me on social media.
