Instantly share code, notes, and snippets.

Embed
What would you like to do?
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