Skip to content

Instantly share code, notes, and snippets.

@mdcallag
Created October 9, 2017 22:28
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/1a3c1ac6e3f8f1c89295d98f5a184643 to your computer and use it in GitHub Desktop.
Save mdcallag/1a3c1ac6e3f8f1c89295d98f5a184643 to your computer and use it in GitHub Desktop.
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
1160 40 8.5 0.024 0.005 0 45 0.006 2.305 0.3 myrocks.zstd.ra
3247 95 77.5 0.157 0.129 0.010 299 0.047 4.485 32.6 inno5635
2730 112 109.5 0.157 0.153 0.010 299 0.196 8.960 38.2 inno5717
757 151 19.6 0.058 0.008 0.001 110 0.003 1.565 0.1 toku5717.none
q5
secs rMB/s wMB/s rKB/o wKB/o r/o rGB cs/o Mcpu/o wa.sec engine
1022 97 0 0.051 0 0.003 97 0.011 1.846 1.0 myrocks.none
881 113 0 0.051 0 0 97 0.004 1.586 0.3 myrocks.none.ra
2617 81 0 0.108 0 0.007 207 0.026 2.731 31.9 inno5717
q4
secs rMB/s wMB/s rKB/o wKB/o r/o rGB cs/o Mcpu/o wa.sec engine
1269 36 0 0.023 0 0.001 45 0.009 1.996 1.2 myrocks.none
1037 44 0 0.024 0 0 45 0.005 1.804 0.3 myrocks.none.ra
1263 16 0 0.010 0 0.001 20 0.008 1.845 0 myrocks.zstd
1196 17 0 0.010 0 0 20 0.005 1.970 0.2 myrocks.zstd.ra
1413 41 0 0.029 0 0.002 56 0.010 1.770 14.1 inno5635
1201 51 0 0.031 0 0.002 59 0.010 1.430 12.6 inno5717
909 85 4.1 0.039 0.002 0 75 0.003 1.685 0.2 toku5717.none
du.gb ts.dl ts.il engine
217 97 116 myrocks.jun16.none
218 97 116 myrocks.jun16.none.ra
461 220 206 inno5717
inno5717
#PAGE_COUNT PAGE_TYPE
===============================================
26033375 Index page
0 Undo log page
1 Inode page
0 Insert buffer free list page
4134880 Freshly allocated page
1824 Insert buffer bitmap
0 System page
0 Transaction system page
1 File Space Header
1823 Extent descriptor page
0 BLOB page
0 Compressed BLOB page
0 Other type of page
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment