View gist:74f3646bdbed129a51560ce49e2318df
Test is modern sysbench with 8 tables, 1M rows per table. Database is cached.
Server has fast SSD, 24 real cores and 48 HW threads with HT enabled.
Tests are described at http://smalldatum.blogspot.com/2017/02/using-modern-sysbench-to-compare.html
Each test has 4 groups of results:
group 1) binlog on, sync_binlog=0, flush_log_at_trx_commit=2
group 2) binlog off, flush_log_at_trx_commit=2
group 3) binlog off, flush_log_at_trx_commit=1
group 4) binlog on, sync_binlog=1, flush_log_at_trx_commit=1
View gist:da335d8ae8d7cfa5cb3453c4ea0f6a96
vmstat and iostat metrics for q1 (first PK scan), q5 (second PK scan) and q4 (scan of 3rd secondary index)
* innodb sustains higher IO rates, even when myrocks uses readahead
* ignoring q1, innodb uses less CPU per row
q1
secs rMB/s wMB/s rKB/o wKB/o r/o rGB cs/o Mcpu/o wa.sec engine
121 847 73 0.052 0.004 0.003 99 0.007 1.250 19 myrocks.jun16.none
60 1693 68 0.052 0.002 0 99 0 1.121 0.1 myrocks.jun16.none.ra
235 678 464 0.081 0.056 0.005 155 0.095 4.216 0.7 inno5717
View gist:1a3c1ac6e3f8f1c89295d98f5a184643
vmstat and iostat metrics for q1 (first PK scan), q5 (second PK scan) and q4 (scan of 3rd secondary index)
* for q1, not only is the InnoDB PK ~200gb, but from iostat I see ~300GB of reads. For q5 that returns to ~207 GB which is what I expected
* toku has the best scan times
* myrocks and innodb have similar CPU rates
q1
secs rMB/s wMB/s rKB/o wKB/o r/o rGB cs/o Mcpu/o wa.sec engine
1043 98 8.1 0.052 0.004 0.011 99 0.011 1.918 1.2 myrocks.none
881 116 10.7 0.052 0.005 0 100 0.004 1.768 0.2 myrocks.none.ra
1260 36 6.6 0.023 0.004 0.003 44 0.012 2.150 0.1 myrocks.zstd
View gist:f35459fd80fbb92c702e5cf2e0ab7f3d
38 __io_getevents_0_4,LinuxAIOHandler::collect,LinuxAIOHandler::poll,os_aio_linux_handler,os_aio_handler,fil_aio_wait,io_handler_thread,std::_Bind<void,std::_Bind<void,Runnable::operator()<void,std::_Bind_simple<Runnable,std::_Bind_simple<Runnable,std::thread::_Impl<
std::_Bind_simple<Runnable,execute_native_thread_routine,start_thread,clone
20 pthread_cond_wait@@GLIBC_2.3.2,background_thread_sleep,background_work_sleep_once,background_work,background_thread_entry,start_thread,clone
7 pthread_cond_wait@@GLIBC_2.3.2,os_event::wait,os_event::wait_low,os_event_wait_low,buf_flush_page_cleaner_thread,std::_Bind<void,std::_Bind<void,Runnable::operator()<void,std::_Bind_simple<Runnable,std::_Bind_simple<Runnable,std::thread::_Impl<std::_Bind_simple<Ru
nnable,execute_native_thread_routine,start_thread,clone
3 pthread_cond_wait@@GLIBC_2.3.2,os_event::wait,os_event::wait_low,os_event_wait_low,srv_worker_thread,std::_Bind<void,std::_Bind<void,Runnable::operator()<void,std::_Bind_simple<Runnable,std
View gist:1b86a00ba81167fc1dfc5181778f249e
legend:
* secs - number of seconds for index scan
* rMB/s - iostat MB read/second
* r/o - iostat reads per row scanned
* rGB - iostat GB read during the index scan
* cs/o - vmstat context switches per row scanned
* Mcpu/o - vmstat us+sy * 1M / rows scanned
* wa.sec - number of seconds in vmstat wa state during the test
* engine - *.ra means readahead was enabled, none/zstd/zlib means compression was used
View gist:f2548bcfa59612d878eb05109449524b
legend:
* secs - number of seconds for index scan
* rMB/s - iostat MB read/second
* r/o - iostat reads per row scanned
* rGB - iostat GB read during the index scan
* cs/o - vmstat context switches per row scanned
* Mcpu/o - vmstat us+sy * 1M / rows scanned
* wa.sec - number of seconds in vmstat wa state during the test
* engine - *.ra means readahead was enabled, none/zstd/zlib means compression was used
View gist:90bd72fa19a4f7c2fed8f10a790f6838
legend:
* secs - number of seconds for index scan
* rMB/s - iostat MB read/second
* r/o - iostat reads per row scanned
* rGB - iostat GB read during the index scan
* cs/o - vmstat context switches per row scanned
* Mcpu/o - vmstat us+sy * 1M / rows scanned
* wa.sec - number of seconds in vmstat wa state during the test
* engine - *.ra means readahead was enabled, none/zstd/zlib means compression was used
View gist:18e2e0eecdfdd2298678b9d4ea827f1a
8.0.3-labs/extra/zlib/zlib.h:1420:9: error: unknown type name ‘z_size_t’
ZEXTERN z_size_t ZEXPORT gzfread OF((voidp buf, z_size_t size, z_size_t nitems,
View gist:e100bf977f5a0d279fc0d9ec08ae8eae
Notes:
* maxid1=1b
* 4 load clients for link table, loaders=4
* 16 transaction clients,requestors=16
* server has 48 HW-threads, 50g RAM, fast SSD
* transactions run for 12 hours, metrics from last hour are below
* I did not adjust wKB/i and wMB/s for MyRocks where it can overstate bytes written by up to 2X courtesy of counting bytes trimmed as bytes written
--- load
ips r/i rKB/i wKB/i Mcpu/i size rss rMB/s wMB/s cpu engine
View gist:22838d174f12b058deea9403d5d70d47
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "ib.pi1",
"indexFilterSet" : false,
"parsedQuery" : {
"customerid" : {
"$lt" : 0
}