mdcallag
commented
7 years ago QPS for the tests repeated using a 2g block cache (buffer pool) and buffered IO so all data is in the OS page cache. Results for MyRocks are similar to the results above for the cached database.
QPS for all test
1 8 16 24 32 40 48
InnoDB-801 554 4702 9202 12269 12181 11370 9506
InnoDB-5717 528 2310 6811 11646 14144 17271 17943
InnoDB-5635 351 3118 5317 6786 11876 12344 12857
MyRocks-Jun16 843 6131 11541 15495 17467 19048 20105
QPS for 1in10 test
1 8 16 24 32 40 48
InnoDB-801 2530 9670 9846 9842 11265 12547 13741
InnoDB-5717 2736 10854 11125 11247 12956 14341 15656
InnoDB-5635 1908 4265 4405 4631 5705 6529 7216
MyRocks-Jun16 1997 9112 9602 9480 9263 9123 8993
yiwu-arbug
QPS with MyRocks saturates at 8 clients because of mutex contention on the shard LRU mutexes. I think the workload ends up with 90% of the lookups going to the right end of the key space, so they all access the same block in each level of the LSM tree. This is the "1in10" variation of the test. When I change it to not do that (the "all" variation) then MyRocks does better, but might hit another known problem (ThreadLocalPtr).
I added a new sysbench test (to be shared soon) that does SELECT statements fetching 100 values by PK using an in-list. I first ran the test with 80M rows in 1 table. The table is ~20gb on disk and I started with a 50gb block cache. Compression was disabled and I used a tuned my.cnf including:
I used a MyRocks build from June 16 that does not include https://github.com/facebook/mysql-5.6/issues/658. The test server has 48 HW threads and the sysbench tests are run for 1, 8, 16, 24, 32, 40 and 48 clients. mysqld and sysbench run on the same host.
The test is run two ways. First it is run in the "all" variation where every lookup finds a row. Then I repeat it for the "1in10" variation where I tell sysbench the key space is 1 to 800M and only keys from 1 to 80M will find rows, so 1 in 10 keys will find a row.
All of the rocksdb options from my.cnf
Examples of the sysbench command line, but I still need to share oltp_inlist_select.lua
QPS by concurrency. Note that MyRocks saturates at ~9000 QPS for for the 1in10 variation which occurs at 8 or 16 clients. It does better on the "all" variation.
This is PMP output from the 1in10 variation at 24 threads. Note that 23 or 24 user query threads are block on lll_lock_wait on a mutex from the sharded LRU
PMP for the "all" variation doesn't show this problem. Although 4 of 24 threads have this at the top of the stack trace -> rocksdb::ThreadLocalPtr::Get(),rocksdb::get_perf_context(), which is a known problem recently fixed by Anirban.