This test is done with a small and cached database - 8M rows. InnoDB is a great choice for such a workload. I am not surprised that MyRocks and TokuDB are not great at this workload. The value for MyRocks and TokuDB is better compression and less write-amplification and that usually requires a larger database and the benefit grows when the database doesn't fit in RAM. But early evaluators might use sysbench so I want to understand how they perform in this setup.
tl;dr
- The common pattern was InnoDB was faster than MyRocks and MyRocks was faster than TokuDB.
- InnoDB in MySQL 5.7.10 is much faster than in MySQL 5.6.26 on tests that are write-heavy or have long range scans. But InnoDB in 5.7 isn't strictly faster than in 5.6 even at high concurrency.
- MyRocks suffers on tests with longer range scans but did pretty good for point queries.
- TokuDB was the slowest for all of the workloads except read-write with --oltp-range-size=10000 and read-only with --oltp-range-size in 100 and 10000.
- Percona fixed a bug in TokuDB that I reported. Thank you.
It can be interesting to compare this result with the result from the smaller servers I use at home. The smaller server has a slower CPU (core i3) and slower SSD. Unfortunately I need to repeat tests on my work servers using the latest version of sysbench.
I used version of sysbench that is a few months old with an older version of my scripts. For my home servers I upgraded to modern sysbench and I will soon do that at work. I will also upgrade from MySQL 5.7.10 to a more recent version and that should make a famous MySQL support guru happy.
The test server has 24 cores, 48 threads, 2 sockets and 256gb of RAM. The storage is from multiple NVMe SSDs. I tested 4 engines -- myrocks-5635 is MyRocks from FB MySQL merged to upstream MySQL 5.6.35, innodb-5626 is InnoDB from upstream MySQL 5.6.26, innodb-5710 is InnoDB from upstream MySQL 5.7.10 and tokudb-5717 is TokuDB from Percona Server 5.7.17-11. The my.cnf files are here for MyRocks, InnoDB-5.7 and InnoDB-5.6. In all cases the binlog is enabled and fsync is disabled for both the storage engine redo log and the binlog.
Tests are run for 1 to 128 concurrent clients. When describing the results I divide that into
low concurrency (<= 8 clients), medium concurrency (16 to 40 clients) and high concurrency (>= 48 clients). The test was run at each concurrency level for 180 seconds for read-heavy tests and 300 seconds for write-heavy tests. The sysbench process is run on the same host as mysqld.
Tests are run in this order:
- prepare - this isn't a test, it creates and loads the tables. There were 8 tables with 1M rows per table. The sysbench table has one secondary index.
- update-only and secondary index maintenance is required
- update-only and secondary index maintenance is not required
- read-write with --oltp-range-size=100 and then optionally with --oltp-range-size=10000
- read-only with --oltp-range-size in 10, 100, 1000 and 10000. I don't report results for --oltp-range-size=1000
- point queries
- insert-only
For the performance summaries below I use "X >> Y >> Z" to mean X is faster than Y and Y is faster than Z. I did not include graphs for one or both of 1) that is a lot more work and 2) I want some readers to take the time and think about the numbers. Unfortunately the formatting is not great for a wide table.
Update-only with secondary index maintenance
The numbers in the table are the QPS for 1 to 128 threads.
1 2 4 8 16 24 32 40 48 64 80 96 128 threads
2895 10363 20118 34017 38720 42138 44736 44646 44169 43633 43104 42690 42442 myrocks-5635
4029 8373 15522 24563 32448 37061 40251 42522 44219 44631 45123 45762 45808 innodb-5626
4254 8792 17020 30524 48443 60623 67052 68773 69232 70556 72134 72696 74222 innodb-5710
2145 4026 7344 13575 21222 27807 30039 29541 29106 27964 26493 25435 23210 tokudb-5717
Throughput:
- 1 to 8 clients - MyRocks >> InnoDB-5.7 >> InnoDB-5.6 >> TokuDB. I don't know why MyRocks was lousy for 1 client but OK for 2+.
- 16 to 40 clients - InnoDB-5.7 >> MyRocks >> InnoDB-5.6 >> TokuDB
- 48+ clients - InnoDB-5.7 >> MyRocks, InnoDB-5.6 >> TokuDB
Update-only without secondary index maintenance
The numbers in the table are the QPS for 1 to 128 threads.
1 2 4 8 16 24 32 40 48 64 80 96 128 threads
2901 10696 20983 37078 43756 47549 50627 52753 52318 51690 50853 50003 49074 myrocks-5635
6384 12625 23847 40501 51505 53024 53587 53523 53316 52483 51632 51276 51117 innodb-5626
5618 11060 21361 38810 58197 69717 74286 75519 75545 76520 77335 78117 79152 innodb-5710
2260 4470 8206 15322 26552 33024 37739 41461 44358 44415 41899 39626 35341 tokudb-5717
Throughput:
- 1 to 8 clients - InnoDB-5.6 >> InnoDB-5.7 >> MyRocks >> TokuDB. I don't know why MyRocks was lousy for 1 client but OK for 2+.
- 16 to 40 clients - InnoDB-5.7 >> InnoDB-5.6 >> MyRocks >> TokuDB.
- 48+ clients - InnoDB-5.7 >> InnoDB-5.6 >> MyRocks >> TokuDB
Read-write with --oltp-range-size=100
The numbers in the table are the QPS for 1 to 128 threads.
1 2 4 8 16 24 32 40 48 64 80 96 128 threads
4798 9955 20426 46361 98537 132010 153726 165477 167280 166549 168513 166807 166722 myrocks-5635
6391 12591 27534 61157 112516 146970 172971 193107 196981 191683 190707 191579 190615 innodb-5626
6143 13051 27216 57447 108786 145127 169957 192548 204655 205408 205278 205629 206721 innodb-5710
5157 9009 17931 42903 76633 95322 108896 114310 114650 113619 112220 108055 103181 tokudb-5717
Throughput:
- 1 to 8 clients - InnoDB-5.7, InnoDB-5.6 >> MyRocks >> TokuDB
- 16 to 40 clients - InnoDB-5.7, InnoDB-5.6 >> MyRocks >> TokuDB
- 48+ clients - InnoDB-5.7 >> InnoDB-5.6 >> MyRocks >> TokuDB
Read-write with --oltp-range-size=10000
The numbers in the table are the QPS for 1 to 128 threads.
1 2 4 8 16 24 32 40 48 64 80 96 128 threads
210 412 774 2091 4077 5288 5568 6409 6885 6235 6675 6543 5889 myrocks-5635
306 612 1216 2403 4713 6439 7398 8098 8416 8397 8393 8364 8247 innodb-5626
448 888 1758 3462 6699 8770 9724 10302 10482 10448 10330 10382 10360 innodb-5710
360 722 1409 2713 5218 6932 7529 7851 7973 7954 7856 7822 7675 toku5717.none.100g
Throughput:
- 1 to 8 clients - InnoDB-5.7 >> TokuDB >> InnoDB-5.6 >> MyRocks
- 16 to 40 clients - InnoDB-5.7 >> TokuDB >> InnoDB-5.6 >> MyRocks
- 48+ clients - InnoDB-5.7 >> InnoDB-5.6 >> TokuDB >> MyRocks
Read-only with --oltp-range-size=10
The numbers in the table are the QPS for 1 to 128 threads.
1 2 4 8 16 24 32 40 48 64 80 96 128 threads
5659 12266 25778 61366 128290 175337 204504 230534 246007 262875 269596 272178 274313 myrocks-5635
6491 13838 30028 72355 144523 194622 230314 265632 298481 300703 302924 303685 305162 innodb-5626
6052 12700 27143 63307 129096 177797 209601 243368 273429 272255 272563 275147 276691 innodb-5710
5391 11261 23935 55313 113932 164953 195290 223643 249579 247840 246926 247897 247045 tokudb-5717
Throughput:
- 1 to 8 clients - InnoDB-5.6 >> InnoDB-5.7 >> MyRocks >> TokuDB
- 16 to 40 clients - InnoDB-5.6 >> InnoDB-5.7 >> MyRocks >> TokuDB
- 48+ clients - InnoDB-5.6 >> InnoDB-5.7 >> MyRocks >> TokuDB
Read-only with --oltp-range-size=100
The numbers in the table are the QPS for 1 to 128 threads.
1 2 4 8 16 24 32 40 48 64 80 96 128 threads
4031 8429 17417 38579 82542 116461 134208 150897 165211 166491 164694 166504 166625 myrocks-5635
5375 11273 23474 54099 110142 151311 177268 202868 225283 228292 229257 229828 231068 innodb-5626
5383 11292 23629 54050 110015 152462 176974 201112 223618 223737 224161 226272 226484 innodb-5710
4804 10062 20795 47474 98569 138827 159757 180406 198294 198680 198103 198374 196718 tokudb-5717
Throughput:
- 1 to 8 clients - InnoDB-5.6, InnoDB-5.7 >> TokuDB >> MyRocks
- 16 to 40 clients - InnoDB-5.6, InnoDB-5.7 >> TokuDB >> MyRocks
- 48+ clients - InnoDB-5.6, InnoDB-5.7 >> TokuDB >> MyRocks
Read-only with --oltp-range-size=10000
The numbers in the table are the QPS for 1 to 128 threads.
1 2 4 8 16 24 32 40 48 64 80 96 128 threads
161 325 634 1238 2438 3385 3807 4093 4300 4261 4239 4214 4135 myrocks-5635
242 484 961 1900 3729 5099 5857 6439 6748 6663 6562 6589 6500 innodb-5626
357 708 1407 2788 5354 6984 7689 8095 8246 8222 8145 8138 8071 innodb-5710
301 606 1203 2365 4583 6057 6582 6885 6992 6908 6862 6802 6772 tokudb-5717
Throughput:
- 1 to 8 clients - InnoDB-5.7 >> TokuDB >> InnoDB-5.6 >> MyRocks
- 16 to 40 clients - InnoDB-5.7 >> TokuDB >> InnoDB-5.6 >> MyRocks
- 48+ clients - InnoDB-5.7 >> TokuDB >> InnoDB-5.6 >> MyRocks
Point-query
The numbers in the table are the QPS for 1 to 128 threads.
1 2 4 8 16 24 32 40 48 64 80 96 128 threads
6784 14328 31277 80458 166882 222750 265885 306227 341095 354327 349912 345317 349403 myrocks-5635
7236 15236 33569 87991 177791 236463 278423 319764 362604 371093 374380 371092 375698 innodb-5626
6802 14177 30860 78950 163909 222998 265156 307198 345832 354835 362774 366825 367057 innodb-5710
6249 12951 27608 67805 142064 194320 230720 264171 294814 306298 308177 309542 310391 tokudb-5717
Throughput:
- 1 to 8 clients - InnoDB-5.6 >> MyRocks >> InnoDB-5.7 >> TokuDB
- 16 to 40 clients - InnoDB-5.6 >> MyRocks, InnoDB-5.7 >> TokuDB
- 48+ clients - InnoDB-5.6 >> InnoDB-5.7 >> MyRocks >> TokuDB
The numbers in the table are the QPS for 1 to 128 threads.
1 2 4 8 16 24 32 40 48 64 80 96 128 threads
5254 16950 31561 41074 47720 52063 54066 53899 53900 53725 53343 53098 52278 myrocks-5635
5753 11506 36083 57235 62591 62469 62577 61899 61131 60592 59080 56895 52913 innodb-5626
5291 15657 33358 55385 79656 90812 97735 99944 100714 101967 103374 104934 106194 innodb-5710
2975 5754 9033 17695 29039 37341 38410 35859 33792 30053 27362 25028 21275 tokudb-5717
Throughput:
- 1 to 8 clients - InnoDB-5.6 >> InnoDB-5.7 >> MyRocks >> TokuDB
- 16 to 40 clients - InnoDB-5.7 >> InnoDB-5.6 >> MyRocks >> TokuDB
- 48+ clients - InnoDB-5.7 >> InnoDB-5.6 >> MyRocks >> TokuDB
MyRocks is worst than innoDB in almost benchmarks or I'm dreaming?
ReplyDeleteDid you read the second paragraph?
DeleteI edited this because I used InnoDB from MySQL 5.6.26, not from 5.6.35.
ReplyDelete