The workload is a large database where all updates are done to a small number of rows. I think it is important to use a large database to include the overhead from searching multiple levels of a b-tree. The inspiration for this is maintaining counts for popular entities like Justin Bieber and One Direction. This comes from serving the social graph. For more on that read about TAO and LinkBench.
The most popular benchmark for MySQL is sysbench and it is usually run with a uniform distribution so that all rows are equally likely to be queried or modified. But real workloads have skew which can cause stress in unexpected places and I describe one such place within InnoDB from this microbenchmark. YCSB and LinkBench are benchmarks that have skew and can be run for MySQL. I hope that more of the MySQL benchmark results in the future include skew.
Configuration
See a previous post for more details. Eight collections/tables with 400M documents/rows per collection/table were created. All collections/tables are in one database so MongoDB suffers from the per-database RW-lock. But MySQL and TokuMX also suffer from a similar issue when all clients are trying to update the same row. Tests were run for 1, 2, 4 and 8 tables where one row per table was updated. So when the test used 4 tables there were 4 rows getting updates. For each number of tables tests were run for up to 64 concurrent clients/threads. The result tables listed in the next section should make that clear.
The workload is updating the non-indexed column of one document/row by PK per transaction. There are no secondary indexes on the table. In this case the document/row with ID=1 is chosen for every update. For MySQL and TokuMX an auto-commit transaction is used. The journal (redo log) is used but the update does not wait for the journal/log to be forced to disk. The updates should not require disk reads as all relevant index and data blocks remain in cache. TokuMX might do reads in the background to maintain fractal trees but I don't understand their algorithm to be certain.
The database was loaded in PK order and about 8 hours of concurrent & random updates were done to warmup the database prior to this test. The warmup was the same workload as described in a previous post.
The MySQL test client limits clients to one table. So when there are 64 clients and 8 tables then there are 8 clients updating the 1 row per table. The MongoDB/TokuMX client does not do that. It lets all clients update all tables so in this case there are at most 64 clients updating the row per table and on average there would be 8.
The test server has 40 CPU cores with HT enabled, fast flash storage and 144G of RAM. The benchmark client and database servers shared the host. Tests were run for several configurations:
- mongo26 - MongoDB 2.6.0rc2, powerOf2Sizes=1, journalCommitInterval=300, w:1,j:0
- mongo24 - MongoDB 2.6.0rc2, powerOf2Sizes=0, journalCommitInterval=300, w:1,j:0
- mysql - MySQL 5.6.12, InnoDB, no compression, flush_log_at_trx_commit=2, buffer_pool_size=120G, flush_method=O_DIRECT, page_size=8k, doublewrite=0, io_capacity=16000, lru_scan_depth=2000, buffer_pool_instances=8, write_io_threads=32, flush_neighbors=0
- toku-32 - TokuMX 1.4.1, readPageSize=32k, quicklz compression, logFlushPeriod=300, w:1,j:0. I don't have results for toku-32 yet.
- toku-64 - TokuMX 1.4.1, readPageSize=64k, quicklz compression, logFlushPeriod=300, w:1,j:0
Results per DBMS
I first list the results by DBMS to show the impact from spreading the workload over more rows/tables. The numbers below are the updates per second rate. I use "DOP=X" to indicate the number of concurrent clients and "DOP" stands for Degree Of Parallelism (it is an Oracle thing). A few conclusions from the results below:- MySQL/InnoDB does much better with more tables for two reasons. The first is that it allows for more concurrency. The second is that it avoids some of the overhead in the code that maintains row locks and threads waiting for row locks. I describe that in more detail at the end of this post.
- MongoDB 2.4.9 is slightly faster than 2.6.0rc2. I think the problem is that mongod requires more CPU per update in 2.6 versus 2.4 and this looks like a performance regression in 2.6 (at least in 2.6.0rc2). I am still profiling to figure out where. More details on this are at the end of the post. I filed JIRA 13663 for this.
- MongoDB doesn't benefit from spreading the load over more collections when all collections are in the same database. This is expected given the per-database RW-lock.
Updates per second
config #tables DOP=1 DOP=2 DOP=4 DOP=8 DOP=16 DOP=32 DOP=64
mysql 1 8360 15992 30182 24932 23924 23191 21048
mysql 2 X 16527 30824 49999 41045 40506 38357
mysql 4 X X 32351 51791 67423 62116 59137
mysql 8 X X X 54826 80409 73782 68128
config #tables DOP=1 DOP=2 DOP=4 DOP=8 DOP=16 DOP=32 DOP=64
mongo24 1 10212 17844 30204 34003 33895 33564 33451
mongo24 2 X 10256 17698 30547 34125 33717 33573
mongo24 4 X X 10670 17690 30903 34027 33586
mongo24 8 X X X 10379 17702 30920 33758
config #tables DOP=1 DOP=2 DOP=4 DOP=8 DOP=16 DOP=32 DOP=64
mongo26 1 9187 16131 27648 28506 27784 27437 27021
mongo26 2 X 9367 16035 27490 28326 27746 27354
mongo26 4 X X 9179 16028 27666 28330 27647
mongo26 8 X X X 9125 16038 27275 27858
config #tables DOP=1 DOP=2 DOP=4 DOP=8 DOP=16 DOP=32 DOP=64
toku-64 1 7327 12804 16179 12154 11021 9990 8344
toku-64 2 X 7173 12690 20483 23064 22354 20349
toku-64 4 X X 7191 12943 21399 33485 40124
toku-64 8 X X X 7121 12727 22096 38207
Results per number of tables
This reorders the results from above to show them for all configurations at the same number of tables. You are welcome to draw conclusions about which is faster.Updates per second
config #tables DOP=1 DOP=2 DOP=4 DOP=8 DOP=16 DOP=32 DOP=64
mysql 1 8360 15992 30182 24932 23924 23191 21048
mongo24 1 10212 17844 30204 34003 33895 33564 33451
mongo26 1 9187 16131 27648 28506 27784 27437 27021
toku-64 1 7327 12804 16179 12154 11021 9990 8344
mysql 2 X 16527 30824 49999 41045 40506 38357
mongo24 2 X 10256 17698 30547 34125 33717 33573
mongo26 2 X 9367 16035 27490 28326 27746 27354
toku-64 2 X 7173 12690 20483 23064 22354 20349
config #tables DOP=1 DOP=2 DOP=4 DOP=8 DOP=16 DOP=32 DOP=64
mysql 4 X X 32351 51791 67423 62116 59137
mongo24 4 X X 10670 17690 30903 34027 33586
mongo26 4 X X 9179 16028 27666 28330 27647
toku-64 4 X X 7191 12943 21399 33485 40124
config #tables DOP=1 DOP=2 DOP=4 DOP=8 DOP=16 DOP=32 DOP=64
mysql 8 X X X 54826 80409 73782 68128
mongo24 8 X X X 10379 17702 30920 33758
mongo26 8 X X X 9125 16038 27275 27858
toku-64 8 X X X 7121 12727 22096 38207
Row locks for InnoDB
I used PMP to understand MySQL/InnoDB on this workload. I frequently saw all user threads blocked on a condition variable with this stack trace. It seems odd that all threads are sleeping. I think the problem is that one thread can run but has yet to be scheduled by Linux. My memory of the row lock code is that it wakes threads in FIFO order and when N threads wait for a lock on the same row then each thread waits on a separate condition variable. I am not sure if this code has been improved in MySQL 5.7. A quick reading of some of the 5.6.12 row lock code showed many mutex operations. Problems in this code have escaped scrutiny in the past because much of our public benchmark activity has used workloads with uniform distributions.
pthread_cond_wait@@GLIBC_2.3.2,os_cond_wait,os_event_wait_low2,lock_wait_suspend_thread,row_mysql_handle_errors,row_search_for_mysql,ha_innobase::index_read,handler::read_range_first,handler::multi_range_read_next,QUICK_RANGE_SELECT::get_next,rr_quick,mysql_update,mysql_execute_command,mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection
This was a less frequent stack trace from the test ...
lock_get_mode,lock_table_other_has_incompatible,lock_table,row_search_for_mysql,ha_innobase::index_read,handler::read_range_first,handler::multi_range_read_next,QUICK_RANGE_SELECT::get_next,rr_quick,mysql_update,mysql_execute_command,mysql_parse,dispatch_command,do_command,do_handle_one_connection,handle_one_connection
Row locks for TokuMX
TokuMX has a similar point at which all threads wait. It isn't a big surprise given that both provide fine-grained concurrency control but there is no granularity finer than a row lock.
pthread_cond_timedwait@@GLIBC_2.3.2,toku_cond_timedwait,toku::lock_request::wait,toku_db_wait_range_lock,toku_c_getf_set(__toku_dbc*,,db_getf_set,autotxn_db_getf_set(__toku_db*,,mongo::CollectionBase::findByPK(mongo::BSONObj,mongo::queryByPKHack(mongo::Collection*,,mongo::updateObjects(char,mongo::lockedReceivedUpdate(char,mongo::receivedUpdate(mongo::Message&,,mongo::assembleResponse(mongo::Message&,,mongo::MyMessageHandler::process(mongo::Message&,,mongo::PortMessageServer::handleIncomingMsg(void*)
MongoDB 2.4 versus 2.6
I get about 1.2X more updates/second with MongoDB 2.4.9 compared to 2.6.0rc2. I think the problem is that 2.6 uses more CPU per update. I filed JIRA 13663 for this but am still trying to profile the code. So far I know the following all of which indicates that the 2.4.9 test is running 1.2X faster than 2.6.0rc2 with 32 client threads and 1 table:
- I get ~1.2X more updates/second with 2.4.9
- the Java sysbench client uses ~1.2X more CPU per "top" with 2.4.9
- the context switch rate is ~1.2X higher with 2.4.9
The interesting point is that mongod for 2.4.9 only uses ~1.03X more CPU than 2.6.0rc2 per "top" during this test even though it is doing 1.2X more updates/second. So 2.6.0rc2 uses more CPU per update. I will look at "perf" output. I can repeat this with the GA version of 2.6.
%20_%20(QPS%20for%208.0.34).png)
No comments:
Post a Comment