Skip to content

Instantly share code, notes, and snippets.

@mdcallag

mdcallag/gist:22a419351ff8a13097e8d509a195e1ce

Created June 5, 2018 16:19
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save mdcallag/22a419351ff8a13097e8d509a195e1ce to your computer and use it in GitHub Desktop.
Save mdcallag/22a419351ff8a13097e8d509a195e1ce to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
Insert benchmark with 2b rows, 16 tables, 16 clients
* server changed to have ~56gb of RAM, 56 cores with HT enabled, fast SSD
* InnoDB has 35gb buffer pool, MyRocks has 30gb block cache
Insert benchmark has 4 steps - see http://smalldatum.blogspot.com/2017/06/the-insert-benchmark.html
1) concurrently load 16 tables with 1 client/table
2) concurrently scan 16 tables with 1 client/table - scan is done for PK, then each secondary index, then PK index again
3) OLTP using 16 write clients and 16 read clients (1 read/write pair per table), each writer does 1000 inserts/second
3) OLTP using 16 write clients and 16 read clients (1 read/write pair per table), each writer does 100 inserts/second
Summary for impact of direct IO
* it increases read IO during load (40 -> 227 MB/s, 23 -> 167 MB/s). This is extra IO during compaction and the extra
is a fraction of the write IO. So if write IO is less (or more), then the read IO overhead is less (or more).
* it makes full index scans 1.5X to 2X slower. With direct IO the read rate from storage is lower (see rMB/s below) for PK index scans.
* it has small impact (loss of 2% to 7%) on QPS for OLTP with 16x1000 inserts
* it has no impact on QPS for OLTP with 16x100 inserts
Summary other:
* loads are faster for MyRocks than InnoDB -- this is not new
* MyRocks full index scans are as fast as InnoDB in MySQL 5.7 -- this is new
* OLTP QPS is much better for MyRocks than InnoDB -- this is not new
---------- 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
136752 8409 8107 7905 0 0.30 4.00 152 226 4.9 324 40.5 547.5 20.8 myrocks.8may18.none
137429 8474 8058 7827 0.01 1.66 3.88 151 227 5.0 1816 227.6 533.8 20.8 myrocks.8may18.none.dio
137514 8525 8268 8077 0 0.17 3.13 175 104 4.8 187 23.1 430.5 24.0 myrocks.8may18.zstd
137845 8613 8262 7949 0.01 1.22 2.92 172 105 4.8 1338 167.7 402.2 23.7 myrocks.8may18.zstd.dio
71378 4674 4205 4121 0.09 1.39 13.18 324 404 39.9 6180 98.9 940.8 23.2 inno.o5717
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
* engine - describes engine, none/zstd = no/zstd compression, dio = uses O_DIRECT, inno.o5717 = upstream MySQL 5.7.17 with InnoDB
---------- scan
Notes
* This table is the number of seconds to scan PK (1), each secondary index (2, 3, 4) and then PK again (5)
* 2-4 is the sum of 2, 3 and 4
* Write back (or compaction) is frequently in progress during first scan of PK (1)
1 2 3 4 5 2-4 engine
89 102 93 102 86 297 myrocks.8may18.none
198 131 146 148 192 425 myrocks.8may18.none.dio
89 103 102 103 82 308 myrocks.8may18.zstd
172 128 136 133 161 397 myrocks.8may18.zstd.dio
94 123 115 141 43 379 inno.o5717
The next 3 tables are HW efficiency metrics for queries 1, 5 and 4
* secs - time to scan the index
* rMB/s, wMB/s - read MB/s and write MB/s from iostat
* r/o, rKB/o, wKB/o - from iostat reads /row, read KB /row, write KB /row
* rGB - GB read from iostat
* cs/o - vmstat context switches/row
* Mcpu/o - normalized CPU overhead /row
* wa.sec - iostat seconds in state wa (wait)
query 1
secs rMB/s wMB/s r/o rKB/o wKB/o rGB cs/o Mcpu/o wa.sec engine
89 1164.1 78.2 0 0.052 0.004 100 0.001 1.017 5.8 myrocks.8may18.none
198 669.6 53.3 0.003 0.067 0.005 128 0.008 1.089 37.6 myrocks.8may18.none.dio
89 613.0 199.2 0 0.028 0.009 53 0.001 1.664 2.7 myrocks.8may18.zstd
172 531.1 149.7 0.003 0.047 0.013 89 0.008 2.256 24.6 myrocks.8may18.zstd.dio
94 1533.9 602.3 0.005 0.074 0.029 141 0.010 1.017 1.0 inno.o5717
query 5
secs rMB/s wMB/s r/o rKB/o wKB/o rGB cs/o Mcpu/o wa.sec engine
86 1156.0 0 0 0.051 0 97 0.001 0.959 5.3 myrocks.8may18.none
192 652.1 0 0.003 0.063 0 121 0.008 1.002 36.2 myrocks.8may18.none.dio
82 538.9 33.5 0 0.022 0.001 42 0.001 1.181 1.9 myrocks.8may18.zstd
161 456.2 30.4 0.003 0.037 0.002 71 0.008 1.338 22.3 myrocks.8may18.zstd.dio
43 3096.0 0 0.004 0.067 0 127 0.006 0.691 0 inno.o5717
query 4
secs rMB/s wMB/s r/o rKB/o wKB/o rGB cs/o Mcpu/o wa.sec engine
102 469.3 0 0 0.025 0 47 0.001 1.257 2.2 myrocks.8may18.none
148 403.7 0 0.002 0.031 0 58 0.004 1.247 17.2 myrocks.8may18.none.dio
103 203.3 36.9 0 0.011 0.002 20 0.001 1.534 1.0 myrocks.8may18.zstd
133 251.7 35.0 0.002 0.017 0.002 32 0.004 1.527 10.3 myrocks.8may18.zstd.dio
141 644.9 0 0.003 0.046 0 88 0.007 0.686 25.4 inno.o5717
---------- oltp with 16 x 1000 inserts/second
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
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 41450 2356 0.94 19.52 5.05 1560 0.36 7.46 596 236 33.1 14907 309.3 80.0 24.7 myrocks.8may18.none
15842 996 38721 1944 0.94 20.24 5.03 1434 0.38 8.28 587 237 33.2 14887 320.7 79.7 22.7 myrocks.8may18.none.dio
15845 996 45568 2576 0.77 9.45 4.50 1781 0.27 3.28 619 107 33.1 12174 149.6 71.3 28.2 myrocks.8may18.zstd
15845 996 44618 2365 0.80 10.21 4.34 1723 0.28 3.63 612 107 33.2 12688 161.8 68.8 27.3 myrocks.8may18.zstd.dio
15845 995 6307 371 2.26 36.07 36.85 672 5.66 90.63 1689 415 40.0 35723 571.6 583.9 10.7 inno.o5717
---------- oltp with 16 x 100 inserts/second
Legend - see previous section
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 44123 2572 9.23 180.80 5.39 14042 0.33 6.49 504 238 33.1 14615 286.4 8.5 22.2 myrocks.8may18.none
1584 100 44205 2370 8.89 179.14 5.17 13315 0.33 6.72 500 238 33.1 14082 283.8 8.2 21.1 myrocks.8may18.none.dio
1584 100 49696 2896 6.99 72.79 4.40 16304 0.22 2.32 520 108 33.0 11078 115.3 7.0 25.8 myrocks.8may18.zstd
1584 100 48644 2676 7.49 80.28 4.59 15871 0.24 2.61 517 108 33.0 11857 127.2 7.3 25.1 myrocks.8may18.zstd.dio
1584 100 8148 449 27.72 443.49 112.62 4732 5.39 86.20 920 416 40.0 43900 702.4 178.4 7.5 inno.o5717
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment