MyRocks SSD Sysbench results with WritePrepared Transactions
| # Setup | |
| For each test there are 3 runs. All use a build from May 8 (8may18). The first is write-committed (wcmt) and the next is with write-prepared (wprep). These are listed as myrocks.8may18.none.wcmt and myrocks.8may18.none.wprep. | |
| The test server has 56 cores with HT enabled, 3 SSDs and 256gb of RAM. For the insert benchmark I use a table per client. Each table has a PK and 3 secondary indexes. | |
| # Benchmark 1 | |
| The first test is the insert benchmark load with 1 to 52 concurrent clients. 500m rows are inserted into N tables (table per client). Each insert transaction inserts 100 rows (~400 KV pairs given a PK and 3 secondary indexes). The insert rate is 7.8% better with write-prepared at 28 clients. | |
| The first test is run 1, 2, 4, 8, ... 52 clients. The perf benefit from write-prepared starts at 8 clients. | |
| -- 500m rows, n clients, n tables, in-memory | |
| Average insert rate for 1 to 52 concurrent clients | |
| 1 2 4 8 12 16 20 24 28 32 36 40 44 48 52 engine | |
| 38447 71654 132837 135722 133298 133085 133014 134916 135943 136575 138160 139276 138850 140410 140568 myrocks.8may18.none.wcmt | |
| 38323 71023 132450 153657 150240 150557 148721 145391 146670 144760 148544 151240 150150 152207 148765 myrocks.8may18.none.wprep | |
| # Benchmark 2 | |
| The next test uses 1 client to insert 100M rows and then do OLTP. Performance and HW efficiency metrics are similar for write-prepared and write-committed. This is good. | |
| -- 100m rows, 1 table, 1 thread, mysql, in-memory | |
| --- load | |
| ips.av ips.75 ips.95 ips.99 r/i rkb/i wkb/i Mcpu/i size rss r/s rmb/s wmb/s cpu engine | |
| 39124 38237 36001 33970 0 0 2.43 141 12 0.6 0 0 94.9 5.5 myrocks.8may18.none.wcmt | |
| 39604 38610 36387 34469 0 0 2.42 140 12 0.7 0 0 95.9 5.6 myrocks.8may18.none.wprep | |
| legend: | |
| * ips.av, ips.75, ips.95, ips.99 -- insert rates: average, p75, p95, p99 | |
| * r/i, rkb/i, wkb/i - iostat metrics, reads/insert, read KB/insert, write KB/insert | |
| * Mcpu/i - normalized CPU overhead /insert | |
| * size - database size in GB after load | |
| * rss - mysqld RSS after load | |
| * r/s, rmb/s, wmb/s - iostat metrics, reads/s, read MB/s, write MB/s | |
| * cpu - vmstat us+sy | |
| --- oltp - 1 reader, 1 writer at 1000 inserts/s | |
| ips.av ips.99 qps.av qps.99 r/i rkb/i wkb/i Mcpu/i r/q rkb/q Mcpu/q size rss r/s rmb/s wmb/s cpu engine | |
| 999 999 4265 4010 0 0 3.07 3221 0 0 754 14 15.8 0 0 3.1 3.2 myrocks.8may18.none.wcmt | |
| 999 998 4289 3962 0 0 3.31 3202 0 0 746 14 15.6 0 0 3.3 3.2 myrocks.8may18.none.wprep | |
| Legend | |
| * ips.av, ips.99 - insert/second average and p99. Note p99 is per client, thus about 1/16 of average | |
| * qps.av, qpa.99 - queries/second average and p99. Note p99 is per client, thus about 1/16 of average | |
| * r/i, rkb/i, wkb/i - from iostat, reads/insert, read KB/insert, write KB/insert - alas this includes IO done for inserts and queries | |
| * Mcpu/i - CPU overhead/insert - alas this includes CPU for inserts and queries | |
| * r/q, rkb/q, Mcpu/q - like r/i, rkb/i, wkb/i and Mcpu/i but per query - alas this includes IO/CPU for inserts and queries | |
| * size - database size in GB at end of test step | |
| * rss - mysqld RSS in GB at end of test step | |
| * r/s, rmb/s, wmb/s - iostat r/s, rMB/s and wMB/s | |
| * cpu - vmstat us+sy | |
| --- oltp - 1 reader, 1 writer at 100 inserts/s | |
| ips.av ips.99 qps.av qps.99 r/i rkb/i wkb/i Mcpu/i r/q rkb/q Mcpu/q size rss r/s rmb/s wmb/s cpu engine | |
| 100 100 4749 4573 0 0 0.75 29085 0 0 611 14 15.5 0 0 0.1 2.9 myrocks.8may18.none | |
| 100 100 4129 3921 0 0 0.70 32393 0 0 783 14 15.8 0 0 0.1 3.2 myrocks.8may18.none.wcmt | |
| 100 100 4142 3948 0 0 0.68 30202 0 0 728 14 15.6 0 0 0.1 3.0 myrocks.8may18.none.wprep | |
| # Benchmark 3 | |
| The final two tests use 16 clients and then 32 clients to load 500M rows and then do OLTP. The insert rate for write-prepared is 13% better at 16 clients and 4% better at 32 clients. The OLTP QPS is similar for write-prepared and write-committed -- that is good as those tests are not bound by writes. HW efficiency metrics are similar for write-prepared and write-committed. That is also good. | |
| -- 500m rows, 16 tables, 16 clients, in-memory | |
| --- load | |
| ips.av ips.75 ips.95 ips.99 r/i rkb/i wkb/i Mcpu/i size rss r/s rmb/s wmb/s cpu engine | |
| 133618 8291 8057 7846 0 0 3.07 139 61 1.7 0 0 409.8 18.6 myrocks.8may18.none.wcmt | |
| 150830 9246 8780 8504 0 0 2.99 139 65 1.8 0 0 450.2 20.9 myrocks.8may18.none.wprep | |
| legend: | |
| * ips.av, ips.75, ips.95, ips.99 -- insert rates: average, p75, p95, p99 | |
| * r/i, rkb/i, wkb/i - iostat metrics, reads/insert, read KB/insert, write KB/insert | |
| * Mcpu/i - normalized CPU overhead /insert | |
| * size - database size in GB after load | |
| * rss - mysqld RSS after load | |
| * r/s, rmb/s, wmb/s - iostat metrics, reads/s, read MB/s, write MB/s | |
| * cpu - vmstat us+sy | |
| --- oltp - 16 readers, 16 writers each at 1000 inserts/s | |
| ips.av ips.99 qps.av qps.99 r/i rkb/i wkb/i Mcpu/i r/q rkb/q Mcpu/q size rss r/s rmb/s wmb/s cpu engine | |
| 15845 996 56292 3299 0 0 4.76 1987 0 0 559 68 150.6 0 0 75.5 31.5 myrocks.8may18.none.wcmt | |
| 15845 996 56697 3319 0 0 4.67 1990 0 0 556 68 150.3 0 0 74.0 31.5 myrocks.8may18.none.wprep | |
| Legend | |
| * ips.av, ips.99 - insert/second average and p99. Note p99 is per client, thus about 1/16 of average | |
| * qps.av, qpa.99 - queries/second average and p99. Note p99 is per client, thus about 1/16 of average | |
| * r/i, rkb/i, wkb/i - from iostat, reads/insert, read KB/insert, write KB/insert - alas this includes IO done for inserts and queries | |
| * Mcpu/i - CPU overhead/insert - alas this includes CPU for inserts and queries | |
| * r/q, rkb/q, Mcpu/q - like r/i, rkb/i, wkb/i and Mcpu/i but per query - alas this includes IO/CPU for inserts and queries | |
| * size - database size in GB at end of test step | |
| * rss - mysqld RSS in GB at end of test step | |
| * r/s, rmb/s, wmb/s - iostat r/s, rMB/s and wMB/s | |
| * cpu - vmstat us+sy | |
| --- oltp - 16 readers, 16 writers each at 100 inserts/s | |
| ips.av ips.99 qps.av qps.99 r/i rkb/i wkb/i Mcpu/i r/q rkb/q Mcpu/q size rss r/s rmb/s wmb/s cpu engine | |
| 1584 100 58213 3438 0 0 4.54 18552 0 0 505 70 159.6 0 0 7.2 29.4 myrocks.8may18.none.wcmt | |
| 1584 100 58572 3430 0 0 4.49 18488 0 0 500 70 158.9 0 0 7.1 29.3 myrocks.8may18.none.wprep | |
| # Benchmark 4 | |
| The configuration is the same as in Benchmark 3. | |
| -- 500m rows, 32 tables, 32 clients, in-memory | |
| --- load | |
| ips.av ips.75 ips.95 ips.99 r/i rkb/i wkb/i Mcpu/i size rss r/s rmb/s wmb/s cpu engine | |
| 135648 4197 4115 4033 0 0 3.23 148 62 2.4 0 0 437.6 20.0 myrocks.8may18.none.wcmt | |
| 144467 4472 4353 4286 0 0 3.17 147 61 2.3 0 0 457.4 21.2 myrocks.8may18.none.wprep | |
| legend: | |
| * ips.av, ips.75, ips.95, ips.99 -- insert rates: average, p75, p95, p99 | |
| * r/i, rkb/i, wkb/i - iostat metrics, reads/insert, read KB/insert, write KB/insert | |
| * Mcpu/i - normalized CPU overhead /insert | |
| * size - database size in GB after load | |
| * rss - mysqld RSS after load | |
| * r/s, rmb/s, wmb/s - iostat metrics, reads/s, read MB/s, write MB/s | |
| * cpu - vmstat us+sy | |
| --- oltp - 32 readers, 32 writers each at 1000 inserts/s | |
| ips.av ips.99 qps.av qps.99 r/i rkb/i wkb/i Mcpu/i r/q rkb/q Mcpu/q size rss r/s rmb/s wmb/s cpu engine | |
| 31391 990 85483 2426 0 0.02 4.59 1884 0 0.01 692 80 193.5 9 0.6 144.1 59.1 myrocks.8may18.none.wcmt | |
| 31391 988 85042 2368 0 0.02 4.66 1901 0 0 702 80 193.5 9 0.7 146.3 59.7 myrocks.8may18.none.wprep | |
| Legend | |
| * ips.av, ips.99 - insert/second average and p99. Note p99 is per client, thus about 1/32 of average | |
| * qps.av, qpa.99 - queries/second average and p99. Note p99 is per client, thus about 1/32 of average | |
| * r/i, rkb/i, wkb/i - from iostat, reads/insert, read KB/insert, write KB/insert - alas this includes IO done for inserts and queries | |
| * Mcpu/i - CPU overhead/insert - alas this includes CPU for inserts and queries | |
| * r/q, rkb/q, Mcpu/q - like r/i, rkb/i, wkb/i and Mcpu/i but per query - alas this includes IO/CPU for inserts and queries | |
| * size - database size in GB at end of test step | |
| * rss - mysqld RSS in GB at end of test step | |
| * r/s, rmb/s, wmb/s - iostat r/s, rMB/s and wMB/s | |
| * cpu - vmstat us+sy | |
| --- oltp - 32 readers, 32 writers each at 100 inserts/s | |
| ips.av ips.99 qps.av qps.99 r/i rkb/i wkb/i Mcpu/i r/q rkb/q Mcpu/q size rss r/s rmb/s wmb/s cpu engine | |
| 3138 100 95850 2698 0 0.15 5.57 17729 0 0.01 580 80 193.5 6 0.5 17.5 55.6 myrocks.8may18.none.wcmt | |
| 3138 100 96682 2820 0 0.17 5.53 17740 0 0.01 576 79 193.5 5 0.5 17.3 55.7 myrocks.8may18.none.wprep |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment