Skip to content

Instantly share code, notes, and snippets.

@mdcallag

mdcallag/gist:d2011ceb6269e9a3b63cef8f96b65cea

Last active June 5, 2020 15:04
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save mdcallag/d2011ceb6269e9a3b63cef8f96b65cea to your computer and use it in GitHub Desktop.
Save mdcallag/d2011ceb6269e9a3b63cef8f96b65cea to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
Stats immediately after the load
--- intra-L0 enabled, bloom filters enabled
Level Files Size Score Read(GB) Rn(GB) Rnp1(GB) Write(GB) Wnew(GB) Moved(GB) W-Amp Rd(MB/s) Wr(MB/s) Comp(sec) CompMergeCPU(sec) Comp(cnt) Avg(sec) KeyIn KeyDrop
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
L0 20/14 8.21 GB 6.2 125.2 0.0 125.2 187.3 62.2 0.0 2.9 40.9 61.3 3130.93 2687.92 2297 1.363 3891M 0
L4 187/178 12.15 GB 0.1 32.7 12.6 20.1 32.3 12.1 0.0 2.6 31.8 31.4 1053.87 619.82 7 150.552 624M 0
L5 83/0 5.40 GB 0.3 151.2 41.0 110.1 149.6 39.5 0.0 3.6 34.6 34.3 4470.74 2718.29 27 165.583 2045M 0
L6 536/0 32.85 GB 0.0 47.7 34.4 13.3 46.2 32.8 0.0 1.3 28.7 27.8 1703.47 768.18 159 10.714 710M 0
Sum 826/192 58.60 GB 0.0 356.8 88.0 268.8 415.4 146.6 0.0 6.3 35.3 41.1 10359.01 6794.22 2490 4.160 7271M 0
Int 0/0 0.00 KB 0.0 5.5 0.0 5.5 8.4 2.8 0.0 2.8 43.7 66.0 129.70 116.56 107 1.212 171M 0
Uptime(secs): 6188.3 total, 188.3 interval
Flush(GB): cumulative 65.477, interval 2.982
AddFile(GB): cumulative 0.000, interval 0.000
AddFile(Total Files): cumulative 0, interval 0
AddFile(L0 Files): cumulative 0, interval 0
AddFile(Keys): cumulative 0, interval 0
Cumulative compaction: 415.41 GB write, 68.74 MB/s write, 356.81 GB read, 59.04 MB/s read, 10359.0 seconds
Interval compaction: 8.36 GB write, 45.49 MB/s write, 5.54 GB read, 30.11 MB/s read, 129.7 seconds
Stalls(count): 603 level0_slowdown, 580 level0_slowdown_with_compaction, 0 level0_numfiles, 0 level0_numfiles_with_compaction, 0 stop for pending_compaction_bytes, 12 slowdown for pending_compaction_bytes, 0 memtable_compaction, 0 memtable_slowdown, interval 27 total count
Cumulative stall: 00:41:50.227 H:M:S, 40.6 percent
Interval stall: 00:00:3.763 H:M:S, 2.0 percent
--- intra-L0 disabled, bloom filters enabled
Level Files Size Score Read(GB) Rn(GB) Rnp1(GB) Write(GB) Wnew(GB) Moved(GB) W-Amp Rd(MB/s) Wr(MB/s) Comp(sec) CompMergeCPU(sec) Comp(cnt) Avg(sec) KeyIn KeyDrop
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
L0 27/18 1003.22 MB 2.2 0.0 0.0 0.0 65.5 65.5 0.0 1.0 0.0 73.5 911.93 783.98 1799 0.507 0 0
L3 45/45 2.91 GB 0.0 66.7 17.3 49.4 65.4 16.1 0.0 3.8 33.6 32.9 2035.96 1288.47 30 67.865 1522M 0
L4 40/0 2.59 GB 0.8 178.7 50.9 127.8 175.5 47.8 3.9 3.4 36.1 35.5 5065.00 3356.41 184 27.527 4021M 0
L5 173/0 10.78 GB 0.9 106.4 47.6 58.8 105.7 46.9 6.7 2.2 29.3 29.1 3714.47 2030.07 590 6.296 3302M 0
L6 672/0 40.92 GB 0.0 113.6 43.1 70.5 111.4 40.9 0.0 2.6 28.2 27.6 4129.32 1988.09 583 7.083 4235M 0
Sum 957/63 58.19 GB 0.0 465.3 158.9 306.4 523.5 217.1 10.6 8.0 30.1 33.8 15856.68 9447.03 3186 4.977 13G 0
Int 0/0 0.00 KB 0.0 26.6 8.9 17.7 28.7 11.0 0.9 11.8 27.9 30.0 977.80 551.48 146 6.697 705M 0
Uptime(secs): 7612.2 total, 412.2 interval
Flush(GB): cumulative 65.494, interval 2.436
AddFile(GB): cumulative 0.000, interval 0.000
AddFile(Total Files): cumulative 0, interval 0
AddFile(L0 Files): cumulative 0, interval 0
AddFile(Keys): cumulative 0, interval 0
Cumulative compaction: 523.52 GB write, 70.42 MB/s write, 465.33 GB read, 62.60 MB/s read, 15856.7 seconds
Interval compaction: 28.69 GB write, 71.28 MB/s write, 26.62 GB read, 66.14 MB/s read, 977.8 seconds
Stalls(count): 2540 level0_slowdown, 2492 level0_slowdown_with_compaction, 31 level0_numfiles, 31 level0_numfiles_with_compaction, 0 stop for pending_compaction_bytes, 0 slowdown
for pending_compaction_bytes, 0 memtable_compaction, 0 memtable_slowdown, interval 174 total count
Cumulative stall: 01:03:33.776 H:M:S, 50.1 percent
Interval stall: 00:04:25.072 H:M:S, 64.3 percent
--- relevant part of my.cnf
rocksdb
default-storage-engine=rocksdb
skip-innodb
default-tmp-storage-engine=MyISAM
binlog_format=ROW
collation-server=latin1_bin
transaction-isolation=READ-COMMITTED
rocksdb_flush_log_at_trx_commit=2
rocksdb_max_total_wal_size=4G
rocksdb_wal_dir=/data/m/fbmy/txlogs
# x1
# default=512m
rocksdb_block_cache_size=8G
# x2b
rocksdb_max_background_jobs=8
# default compaction_pri=kMinOverlappingRatio
# x3
# default is disabled
#rocksdb_default_cf_options=level_compaction_dynamic_level_bytes=true
# x4
# default is no bloom
rocksdb_default_cf_options=level_compaction_dynamic_level_bytes=true;block_based_table_factory={cache_index_and_filter_blocks=1;filter_policy=bloomfilter:10:false;whole_key_filtering=1};optimize_filters_for_hits=true
--- perf summary with intra-L0 enabled
8 processes, 62500000 rows-per-process, 6143 seconds, 81393.4 rows-per-second, 10174.1 rows-per-second-per-user, 0 queries, 0 queries-per-second
iostat, vmstat normalized by insert rate
samp r/s rkb/s wkb/s r/q rkb/q wkb/q ips spi
1227 2271.8 35862 86730 0.028 0.441 1.066 81393.4 0.009829
samp cs/s cpu/c cs/q cpu/q
1287 15496 37.1 0.190 0.000456
iostat, vmstat normalized by query rate
samp r/s rkb/s wkb/s r/q rkb/q wkb/q qps spq
samp cs/s cpu/c cs/q cpu/q
75G /data/m/fbmy
ec2-user 3406 483 13.1 15100156 8130648 pts/0 Sl 13:26 498:59 ./bin/mysqld --basedir=. --datadir=/data/m/fbmy/data --plugin-dir=./lib/plugin --log-error=/data/m/fbmy/data/ip-10-2-0-200.us-west-2.compute.internal.err --pid-file=/data/m/fbmy/data/ip-10-2-0-200.us-west-2.compute.internal.pid
Max insert
1.708
--- perf summary with intra-L0 disabled
8 processes, 62500000 rows-per-process, 7567 seconds, 66076.3 rows-per-second, 8259.5 rows-per-second-per-user, 0 queries, 0 queries-per-second
iostat, vmstat normalized by insert rate
samp r/s rkb/s wkb/s r/q rkb/q wkb/q ips spi
1512 2895.2 43183 84655 0.044 0.654 1.281 66076.3 0.012107
samp cs/s cpu/c cs/q cpu/q
1586 14476 33.6 0.219 0.000509
iostat, vmstat normalized by query rate
samp r/s rkb/s wkb/s r/q rkb/q wkb/q qps spq
samp cs/s cpu/c cs/q cpu/q
66G /data/m/fbmy
ec2-user 77378 448 13.4 14272252 8307320 pts/0 Sl Jun03 569:29 ./bin/mysqld --basedir=. --datadir=/data/m/fbmy/data --plugin-dir=./lib/plugin --log-error=/data/m/fbmy/data/ip-10-2-0-200.us-west-2.compute.internal.err --pid-file=/data/m/fbmy/data/ip-10-2-0-200.us-west-2.compute.internal.pid
Max insert
50.167
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment