Using the configuration described elsewhere I ran a modified version of sysbench to compare PBXT, InnoDB and MyISAM using two update intensive workloads on a CPU-bound server. The workloads are simple but reproduce a bottleneck that is intermittent on some of our MySQL servers. Each workload is one statement run by many concurrent connections. In both cases the statements increment a column in one very popular row. One test uses an update statement like UPDATE sbtest set c=c+1 where id=1. The other test uses an insert statement like INSERT INTO sbtest values(1,0,'0','ayyyyyyyyy') ON DUPLICATE KEY UPDATE c = c + 1 and always executes the ON DUPLICATE KEY UPDATE clause.
Note that this is a very narrow benchmark (nothing but updates, CPU bound server) but it tests a case that I care about. My summary of the results are:
- There is significant overhead from using the binlog with sync_binlog=1 even when fsync is fast. We are trying to fix this by supporting group commit for the binlog fsync. Ryan McElroy is working on it and will talk about this at the MySQL conference. The MySQL replication and MariaDB teams are also working on the problem. We hope to learn that the InnoDB team is also working on a fix.
- InnoDB is much faster when deadlock detection is disabled for workloads with a lot of concurrency and contention.
- PBXT continues to be competitive.
- PBXT needs to get better at INSERT ON DUPLICATE KEY UPDATE.
- MyISAM is faster. This doesn't surprise me. I am surprised that InnoDB is very close to MyISAM on some of the tests.
The tests were run for MySQL 5.1.45 with the Facebook patch for MySQL 5.1 using the PBXT 1.1, InnoDB plugin 1.0.6 and MyISAM engines. Several configurations were run:
- binlog enabled, innodb deadlock detection disabled/enabled
- binlog disabled, innodb deadlock detection disabled/enabled
- binlog disabled, innodb deadlock detection disabled/disabled, fsync on commit disabled
The Facebook patch for MySQL includes a change that signficantly reduces the overhead from InnoDB deadlock detection courtesy of MySQL support. This patch will soon be in an official release. I don't include results without the patch. They are much worse. Even with this patch the overhead from InnoDB deadlock detection can be significant.
With the configuration below the database is cached. The server uses HW RAID with a battery backed write cache so O_DIRECT writes and fsync are fast. The basic my.cnf values are:
pbxt_index_cache_size=250M pbxt_record_cache_size=750M pbxt_flush_log_at_trx_commit=1 innodb_buffer_pool_size=1000M innodb_log_file_size=100M innodb_flush_log_at_trx_commit=1 innodb_doublewrite=0 innodb_flush_method=O_DIRECT innodb_thread_concurrency=0 innodb_max_dirty_pages_pct=80 innodb_file_per_table innodb_file_format=barracuda max_connections=2000 table_cache=2000 key_buffer_size=1000M
The value innodb_deadlock_detect=0 was added for the tests that disabled InnoDB deadlock detection. For the tests in which fsync on commit was disabled these values were appended to my.cnf: innodb_flush_log_at_trx_commit=2 and pbxt_flush_log_at_trx_commit. For tests that used the binlog it was enabled by the following options:
log_bin binlog_format=row innodb_autoinc_lock_mode=2 sync_binlog=1
For all of the results:
- innodb-ndl is the 1.0.6 InnoDB plugin with innodb_deadlock_detect=0 used to disable InnoDB deadlock detection. Row lock timeouts would still resolve deadlocks, but only after the timeout expires. This option is in the Facebook patch for MySQL 5.1.
- All results are reported for 1, 2, 4, 8, 16, 32, 64, 128, 256, 512 and 1024 concurrent clients.
- The mysqld server and sysbench clients ran on the same 16 core server.
- Results are transactions per second (TPS) reported by sysbench where each transaction is one UPDATE or INSERT statement.
Results for concurrent UPDATE
The statement was UPDATE sbtest set c=c+1 where id=1
TPS with the binlog enabled:
innodb-ndl 1263 2076 2373 2556 2448 2537 2584 2483 2350 1949 1495 innodb 1633 2313 2395 2522 2492 2498 2360 1457 576 155 43 pbxt 1626 2113 2214 2250 2217 2149 2008 1906 1689 1368 1088 myisam 2725 3844 3767 3880 4129 3947 4523 3857 3906 3876 4021
TPS with the binlog disabled:
innodb-ndl 6453 11650 16576 12242 12672 12431 12044 10691 7652 4747 2603 innodb 5046 11760 16710 11775 12299 10348 5898 2173 674 165 50 pbxt 6775 12982 15101 11033 10545 9175 8155 6292 4512 2885 1809 myisam 9186 12931 9638 9319 9720 9515 8851 9529 8680 7827 8691
Results for concurrent INSERT ON DUPLICATE KEY UPDATE
The statement was INSERT INTO sbtest values(1,0,'0','ayyyyyyyyy') ON DUPLICATE KEY UPDATE c = c + 1. The value 0 is listed for some results from PBXT because of bug 551250.
TPS with the binlog enabled:
innodb-ndl 1502 2300 2483 2205 2563 2614 2436 2381 2260 1934 1507 innodb 1262 2481 2678 2547 2306 2433 2225 1483 594 163 43 pbxt 1643 2132 1925 1832 1548 1291 895 557 0 0 0 myisam 2573 4145 4088 3704 4043 4442 4611 4694 4280 4265 4037
TPS with the binlog disabled:
innodb-ndl 2704 4990 9772 13543 12109 11747 11123 9706 7316 4696 2480 innodb 2271 5878 9488 13494 11631 9760 5754 2277 700 172 56 pbxt 2112 4167 2581 1914 1170 46 0 0 0 0 0 myisam 9350 15388 11897 10484 11235 11178 11036 10405 10008 8727 10829
TPS with the binlog disabled and fsync on commit disabled:
innodb-ndl 6092 11126 14765 11054 11308 10975 10854 9424 7165 4491 2524 innodb 5908 11379 14585 10734 10891 9372 5554 2152 680 170 45 pbxt 5289 0 0 0 0 0 5036 2681 2238 646 153 myisam 9350 15388 11897 10484 11235 11178 11036 10405 10008 8727 10829