Skip to content

Instantly share code, notes, and snippets.

@mdcallag

mdcallag/i1.txt Secret

Created January 4, 2024 01:00
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/aedac8b00b12d910754aa83490ebade7 to your computer and use it in GitHub Desktop.
Save mdcallag/aedac8b00b12d910754aa83490ebade7 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
i1.txt
--- 8-core, small tx, l.i1 (random writes)
1 processes, 5000000 rows-per-process, 31280 seconds, 159.8 rows-per-second, 159.8 rows-per-second-per-user, 0 queries, 0 queries-per-second, yes deletes -> LWT=ON
1 processes, 5000000 rows-per-process, 20149 seconds, 248.1 rows-per-second, 248.1 rows-per-second-per-user, 0 queries, 0 queries-per-second, yes deletes -> LWT=OFF
iostat, vmstat normalized by insert rate
nsamp r/s rMB/s w/s wMB/s r/i rKB/i w/i wKB/i ips
6254 0.8 0.0 405.2 1.9 0.005 0.079 2.535 12.257 159.8 LWT=ON
4026 1.3 0.0 492.6 2.8 0.005 0.083 1.985 11.420 248.1 LWT=OFF
nsamp cs/s cpu/s cs/q cpu/q
6551 7747 2.0 48.476 0.012624 LWT=ON
4217 4007 2.9 16.151 0.011580 LWT=OFF
--- 8-core, large tx, l.i1 (random writes)
1 processes, 5000000 rows-per-process, 6947 seconds, 719.7 rows-per-second, 719.7 rows-per-second-per-user, 0 queries, 0 queries-per-second, yes deletes -> LWT=ON
1 processes, 5000000 rows-per-process, 2319 seconds, 2156.1 rows-per-second, 2156.1 rows-per-second-per-user, 0 queries, 0 queries-per-second, yes deletes -> LWT=OFF
iostat, vmstat normalized by insert rate
nsamp r/s rMB/s w/s wMB/s r/i rKB/i w/i wKB/i ips
1387 5.9 0.1 464.4 5.0 0.008 0.131 0.645 7.127 719.7 LWT=ON
463 17.7 0.3 791.2 14.9 0.008 0.131 0.367 7.080 2156.1 LWT=OFF
nsamp cs/s cpu/s cs/q cpu/q
1454 7216 3.1 10.027 0.004268 LWT=ON
486 4674 6.7 2.168 0.003123 LWT=OFF
--- 32-core, small tx, l.i1 (random writes)
12 processes, 1000000 rows-per-process, 43064 seconds, 278.6 rows-per-second, 23.2 rows-per-second-per-user, 0 queries, 0 queries-per-second, yes deletes -> LWT=ON
12 processes, 1000000 rows-per-process, 3980 seconds, 3015.0 rows-per-second, 251.2 rows-per-second-per-user, 0 queries, 0 queries-per-second, yes deletes -> LWT=OFF
iostat, vmstat normalized by insert rate
nsamp r/s rMB/s w/s wMB/s r/i rKB/i w/i wKB/i ips
8613 2.6 0.0 437.0 4.7 0.009 0.147 1.569 17.265 278.6 LWT=ON
796 27.6 0.4 912.2 21.4 0.009 0.147 0.303 7.275 3015.0 LWT=OFF
nsamp cs/s cpu/s cs/q cpu/q
9023 10002 0.2 35.902 0.000566 LWT=ON
834 26733 3.9 8.867 0.001287 LWT=OFF
- at end of l.x
554634 OS file reads, 6426163 OS file writes, 2163798 OS fsyncs -> LWT=ON
553862 OS file reads, 5177849 OS file writes, 1282189 OS fsyncs -> LWT=OFF
- at end of l.i1
775423 OS file reads, 24275658 OS file writes, 10191440 OS fsyncs -> LWT=ON
774467 OS file reads, 21461393 OS file writes, 1723940 OS fsyncs -> LWT=OFF
- OS fsyncs during l.i1 is (total at end of l.i1) - (total at end of l.x)
8027642 -> LWT=ON
441751 -> LWT=OFF
So LWT=ON does ~18X more OS fsyncs from InnoDB
- There are 1M/5 transactions/client and 200,000 * 12 = 2.4M transactions total
--- 32-core, large tx, l.i1 (random writes)
12 processes, 1000000 rows-per-process, 49562 seconds, 242.1 rows-per-second, 20.1 rows-per-second-per-user, 0 queries, 0 queries-per-second, yes deletes -> LWT=ON
12 processes, 1000000 rows-per-process, 424 seconds, 28301.8 rows-per-second, 2358.4 rows-per-second-per-user, 0 queries, 0 queries-per-second, yes deletes -> LWT=OFF
iostat, vmstat normalized by insert rate
nsamp r/s rMB/s w/s wMB/s r/i rKB/i w/i wKB/i ips
9912 2.2 0.0 417.7 4.4 0.009 0.147 1.725 18.487 242.1 LWT=ON
85 259.4 4.1 2511.4 117.8 0.009 0.147 0.089 4.263 28301.8 LWT=OFF
nsamp cs/s cpu/s cs/q cpu/q
10383 8450 0.0 34.902 0.000042 LWT=ON
90 46653 15.9 1.648 0.000563 LWT=OFF
- at end of l.x
553846 OS file reads, 3498401 OS file writes, 982605 OS fsyncs -> LWT=ON
552859 OS file reads, 3340228 OS file writes, 128679 OS fsyncs -> LWT=OFF
- at end of l.i1
774541 OS file reads, 19393026 OS file writes, 10463654 OS fsyncs -> LWT=ON
773968 OS file reads, 17501208 OS file writes, 172794 OS fsyncs -> LWT=OFF
- OS fsyncs during l.i1 is (total at end of l.i1) - (total at end of l.x)
9481049 -> LWT=ON
45115 -> LWT=OFF
So LWT=ON does ~200X more OS fsyncs from InnoDB
--- 40-core, small tx, l.i1 (random writes), 24 clients
24 processes, 10000000 rows-per-process, 2952 seconds, 81300.8 rows-per-second, 3387.5 rows-per-second-per-user, 0 queries, 0 queries-per-second, yes deletes -> LWT=ON
24 processes, 10000000 rows-per-process, 2810 seconds, 85409.2 rows-per-second, 3558.7 rows-per-second-per-user, 0 queries, 0 queries-per-second, yes deletes -> LWT=OFF
iostat, vmstat normalized by insert rate
nsamp r/s rMB/s w/s wMB/s r/i rKB/i w/i wKB/i ips
590 656.3 10.3 43716.7 487.7 0.008 0.129 0.538 6.143 81300.8 LWT=ON
562 689.2 10.8 26708.3 419.1 0.008 0.129 0.313 5.024 85409.2 LWT=OFF
nsamp cs/s cpu/s cs/q cpu/q
619 1146141 51.6 14.098 0.000635 LWT=ON
589 1015621 50.9 11.891 0.000596 LWT=OFF
- at end of l.x
4977935 OS file reads, 168382476 OS file writes, 30290181 OS fsyncs -> LWT=ON
4970261 OS file reads, 37902165 OS file writes, 8545551 OS fsyncs -> LWT=OFF
- at end of l.i1
6912970 OS file reads, 850669091 OS file writes, 91309668 OS fsyncs -> LWT=ON
6905155 OS file reads, 143758427 OS file writes, 21851407 OS fsyncs -> LWT=OFF
- OS fsyncs during l.i1 is (total at end of l.i1) - (total at end of l.x)
61019487 -> LWT=ON
13305856 -> LWT=OFF
So LWT=ON does 4.6X more OS fsyncs from InnoDB
--- 40-core, large tx, l.i1 (random writes), 24 clients
24 processes, 10000000 rows-per-process, 1381 seconds, 173787.1 rows-per-second, 7241.1 rows-per-second-per-user, 0 queries, 0 queries-per-second, yes deletes -> LWT=ON
24 processes, 10000000 rows-per-process, 1096 seconds, 218978.1 rows-per-second, 9124.0 rows-per-second-per-user, 0 queries, 0 queries-per-second, yes deletes -> LWT=OFF
iostat, vmstat normalized by insert rate
nsamp r/s rMB/s w/s wMB/s r/i rKB/i w/i wKB/i ips
276 1403.5 21.9 48365.1 650.2 0.008 0.129 0.278 3.831 173787.1 LWT=ON
219 1768.9 27.6 28912.6 598.6 0.008 0.129 0.132 2.799 218978.1 LWT=OFF
nsamp cs/s cpu/s cs/q cpu/q
290 1139526 49.8 6.557 0.000287 LWT=ON
230 956075 57.3 4.366 0.000262 LWT=OFF
- at end of l.x
4966046 OS file reads, 54851241 OS file writes, 7637188 OS fsyncs -> LWT=ON
4981123 OS file reads, 27814628 OS file writes, 2308004 OS fsyncs -> LWT=OFF
- at end of l.i1
6901567 OS file reads, 288515630 OS file writes, 38664033 OS fsyncs -> LWT=ON
6916408 OS file reads, 86542692 OS file writes, 6460207 OS fsyncs -> LWT=OFF
- OS fsyncs during l.i1 is (total at end of l.i1) - (total at end of l.x)
31026845 -> LWT=ON
4152203 -> LWT=OFF
So LWT=ON does ~7.5X more OS fsyncs from InnoDB
--- 40-core, small tx, l.i1 (random writes), 40 clients
40 processes, 10000000 rows-per-process, 4788 seconds, 83542.1 rows-per-second, 2088.5 rows-per-second-per-user, 0 queries, 0 queries-per-second, yes deletes -> LWT=ON
40 processes, 10000000 rows-per-process, 5160 seconds, 77519.3 rows-per-second, 1937.9 rows-per-second-per-user, 0 queries, 0 queries-per-second, yes deletes -> LWT=OFF
iostat, vmstat normalized by insert rate
nsamp r/s rMB/s w/s wMB/s r/i rKB/i w/i wKB/i ips
958 680.7 10.6 37050.6 512.0 0.008 0.130 0.443 6.275 83542.1 LWT=ON
1032 625.9 9.8 21650.6 443.6 0.008 0.129 0.279 5.860 77519.3 LWT=OFF
nsamp cs/s cpu/s cs/q cpu/q
1004 1096054 60.3 13.120 0.000722 LWT=ON
1082 914544 54.8 11.798 0.000707 LWT=OFF
- at end of l.x
8296580 OS file reads, 283819469 OS file writes, 38837052 OS fsyncs -> LWT=ON
8289200 OS file reads, 57158425 OS file writes, 9304258 OS fsyncs -> LWT=OFF
- at end of l.i1
11550493 OS file reads, 1309791590 OS file writes, 118977314 OS fsyncs -> LWT=ON
11513598 OS file reads, 243027663 OS file writes, 23203094 OS fsyncs -> LWT=OFF
- OS fsyncs during l.i1 is (total at end of l.i1) - (total at end of l.x)
80140262 -> LWT=ON
13898836 -> LWT=OFF
So LWT=ON does ~5.8X more OS fsyncs from InnoDB
--- 40-core, large tx, l.i1 (random writes), 40 clients
40 processes, 10000000 rows-per-process, 1962 seconds, 203873.5 rows-per-second, 5096.8 rows-per-second-per-user, 0 queries, 0 queries-per-second, yes deletes -> LWT=ON
40 processes, 10000000 rows-per-process, 3921 seconds, 102014.7 rows-per-second, 2550.3 rows-per-second-per-user, 0 queries, 0 queries-per-second, yes deletes -> LWT=OFF
iostat, vmstat normalized by insert rate
nsamp r/s rMB/s w/s wMB/s r/i rKB/i w/i wKB/i ips
nsamp cs/s cpu/s cs/q cpu/q
iostat, vmstat normalized by insert rate
nsamp r/s rMB/s w/s wMB/s r/i rKB/i w/i wKB/i ips
393 1712.2 26.8 40060.1 735.3 0.008 0.134 0.196 3.693 203873.5 LWT=ON
785 823.1 12.9 15427.6 433.6 0.008 0.129 0.151 4.353 102014.7 LWT=OFF
nsamp cs/s cpu/s cs/q cpu/q
412 1203435 66.3 5.903 0.000325 LWT=ON
823 588458 36.7 5.768 0.000359 LWT=OFF
- at end of l.x
8277841 OS file reads, 89659993 OS file writes, 8667425 OS fsyncs -> LWT=ON
8275535 OS file reads, 46462652 OS file writes, 1716184 OS fsyncs -> LWT=OFF
- at end of l.i1
11637318 OS file reads, 391727508 OS file writes, 41189353 OS fsyncs -> LWT=ON
11500270 OS file reads, 200532059 OS file writes, 4360533 OS fsyncs -> LWT=OFF
- OS fsyncs during l.i1 is (total at end of l.i1) - (total at end of l.x)
32521928 -> LWT=ON
2644349 -> LWT=OFF
So LWT=ON does ~12.3X more OS fsyncs from InnoDB
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment