0

I have 4 different MySQL 8.0.30 servers on Rocky Linux 9, in one I replaced NVME SSD hoping it will perform better. I made an image with "Macrium Reflect" software and copied exact same partitions

  • from a Kingston DC1000 "enterprise" nvme (3000 MB read / 550 MB write)
  • to a Samsung 980 PRO nvme (6900 MB read / 5000 MB write)

Now I have 3 servers with that previous Kingston enterprise SSD and one with the new Samsung 980 pro. Problem is, the Samsung which is much faster in specs, performs many many times slower in MySQL queries. I am able to compare all tests on the other 3 servers, they are all close to one another, but the server with Samsung SSD is always much much slower. For example in 10 seconds, all 3 servers insert ~80000 records and the problematic server only inserts 2000-5000 records.

If I set these 2 variables in my.cnf in all 4 servers, then they perform the same (as I understand, these settings take the write speed out of the equation):

innodb_flush_log_at_trx_commit = 0
innodb_flush_log_at_timeout = 3
  • Can the below slow benchmarks just be the result of the Samsung "PRO" SSD not being enterprise grade ? And it's write cache just filling up and start performing bad that fast ? (0 QPS, 12 seconds after benchmark starts, see second benchmark).
  • Can slow writes like these happen because I cloned two different drives and this write problem can be related to storage cluster / block size ? I don't know much about that, not even sure if is a partition thing or a hardware thing, partitions should be the same in cloned one.

I ran tests on a Samsung 980 pro and a Samsung 970 pro, the 970 is just ~30% faster but still extremly slower than the Kingston "enterprise" SSD. The 970 PRO has better endurance from what I know. Are all these performance drops somehow related to endurance ?

Also I found some topics on samsung site and on mysql bug report forums, some users complain about similar Samsung SSDs having slow writes.

Read speed is better on the Samsung SSD (like in specs). Just write is problematic.

The sysbench attached, notice how much slower is the slower one:

The "normal", faster server(s):

sysbench oltp_write_only --threads=16 --events=0 --time=60 --mysql-host=localhost --mysql-user=sbtest --mysql-password='****' --mysql-port=3306 --tables=10 --table-size=1000000 --range_selects=off --db-ps-mode=disable --report-interval=1 run
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 16
Report intermediate results every 1 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 1s ] thds: 16 tps: 18905.49 qps: 113467.89 (r/w/o: 0.00/75640.93/37826.96) lat (ms,95%): 1.06 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 16 tps: 1850.30 qps: 11089.78 (r/w/o: 0.00/7389.19/3700.60) lat (ms,95%): 2.76 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 16 tps: 7985.75 qps: 47912.49 (r/w/o: 0.00/31940.99/15971.50) lat (ms,95%): 2.35 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 16 tps: 7614.21 qps: 45734.27 (r/w/o: 0.00/30506.85/15227.42) lat (ms,95%): 2.35 err/s: 0.00 reconn/s: 0.00
[ 5s ] thds: 16 tps: 5932.28 qps: 35586.69 (r/w/o: 0.00/23721.13/11865.56) lat (ms,95%): 2.52 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 16 tps: 5713.84 qps: 34284.01 (r/w/o: 0.00/22856.34/11427.67) lat (ms,95%): 2.39 err/s: 0.00 reconn/s: 0.00
[ 7s ] thds: 16 tps: 6260.42 qps: 37520.50 (r/w/o: 0.00/24999.67/12520.83) lat (ms,95%): 2.35 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 16 tps: 6811.44 qps: 40876.61 (r/w/o: 0.00/27260.74/13615.87) lat (ms,95%): 2.35 err/s: 0.00 reconn/s: 0.00
[ 9s ] thds: 16 tps: 6927.07 qps: 41554.42 (r/w/o: 0.00/27693.28/13861.14) lat (ms,95%): 2.30 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 16 tps: 8123.90 qps: 48767.38 (r/w/o: 0.00/32522.59/16244.79) lat (ms,95%): 2.30 err/s: 0.00 reconn/s: 0.00
[ 11s ] thds: 16 tps: 5210.95 qps: 31243.67 (r/w/o: 0.00/20818.78/10424.89) lat (ms,95%): 2.39 err/s: 0.00 reconn/s: 0.00
[ 12s ] thds: 16 tps: 7038.81 qps: 42255.86 (r/w/o: 0.00/28181.24/14074.62) lat (ms,95%): 2.39 err/s: 0.00 reconn/s: 0.00
[ 13s ] thds: 16 tps: 5205.27 qps: 31241.63 (r/w/o: 0.00/20828.09/10413.54) lat (ms,95%): 2.26 err/s: 0.00 reconn/s: 0.00
[ 14s ] thds: 16 tps: 6899.46 qps: 41369.77 (r/w/o: 0.00/27570.85/13798.93) lat (ms,95%): 2.26 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 16 tps: 6247.58 qps: 37476.48 (r/w/o: 0.00/24981.32/12495.16) lat (ms,95%): 2.39 err/s: 0.00 reconn/s: 0.00
[ 16s ] thds: 16 tps: 5494.83 qps: 33008.00 (r/w/o: 0.00/22019.33/10988.67) lat (ms,95%): 2.43 err/s: 0.00 reconn/s: 0.00
[ 17s ] thds: 16 tps: 5644.39 qps: 33835.36 (r/w/o: 0.00/22545.57/11289.79) lat (ms,95%): 2.14 err/s: 0.00 reconn/s: 0.00
[ 18s ] thds: 16 tps: 6123.58 qps: 36762.48 (r/w/o: 0.00/24516.32/12246.16) lat (ms,95%): 2.18 err/s: 0.00 reconn/s: 0.00
[ 19s ] thds: 16 tps: 5539.89 qps: 33263.36 (r/w/o: 0.00/22182.57/11080.79) lat (ms,95%): 2.35 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 16 tps: 8187.36 qps: 49074.18 (r/w/o: 0.00/32699.45/16374.73) lat (ms,95%): 2.30 err/s: 0.00 reconn/s: 0.00
[ 21s ] thds: 16 tps: 6125.19 qps: 36764.12 (r/w/o: 0.00/24513.75/12250.37) lat (ms,95%): 2.43 err/s: 0.00 reconn/s: 0.00
[ 22s ] thds: 16 tps: 4325.04 qps: 25977.23 (r/w/o: 0.00/17328.15/8649.08) lat (ms,95%): 2.66 err/s: 0.00 reconn/s: 0.00
[ 23s ] thds: 16 tps: 5452.18 qps: 32672.05 (r/w/o: 0.00/21766.70/10905.35) lat (ms,95%): 2.43 err/s: 0.00 reconn/s: 0.00
[ 24s ] thds: 16 tps: 4860.67 qps: 29221.04 (r/w/o: 0.00/19499.69/9721.35) lat (ms,95%): 3.43 err/s: 0.00 reconn/s: 0.00
[ 25s ] thds: 16 tps: 2931.71 qps: 17542.27 (r/w/o: 0.00/11678.84/5863.42) lat (ms,95%): 3.68 err/s: 0.00 reconn/s: 0.00
[ 26s ] thds: 16 tps: 3928.16 qps: 23562.93 (r/w/o: 0.00/15706.62/7856.31) lat (ms,95%): 4.49 err/s: 0.00 reconn/s: 0.00
[ 27s ] thds: 16 tps: 4209.08 qps: 25245.47 (r/w/o: 0.00/16827.32/8418.16) lat (ms,95%): 3.82 err/s: 0.00 reconn/s: 0.00
[ 28s ] thds: 16 tps: 6064.60 qps: 36389.63 (r/w/o: 0.00/24260.42/12129.21) lat (ms,95%): 2.66 err/s: 0.00 reconn/s: 0.00
[ 29s ] thds: 16 tps: 7259.95 qps: 43579.71 (r/w/o: 0.00/29059.81/14519.90) lat (ms,95%): 3.49 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 16 tps: 4897.71 qps: 29385.28 (r/w/o: 0.00/19589.86/9795.43) lat (ms,95%): 3.49 err/s: 0.00 reconn/s: 0.00
[ 31s ] thds: 16 tps: 6064.17 qps: 36410.03 (r/w/o: 0.00/24284.69/12125.34) lat (ms,95%): 3.89 err/s: 0.00 reconn/s: 0.00
[ 32s ] thds: 16 tps: 6080.33 qps: 36443.01 (r/w/o: 0.00/24279.34/12163.67) lat (ms,95%): 3.75 err/s: 0.00 reconn/s: 0.00
[ 33s ] thds: 16 tps: 5415.57 qps: 32549.39 (r/w/o: 0.00/21718.26/10831.13) lat (ms,95%): 2.39 err/s: 0.00 reconn/s: 0.00
[ 34s ] thds: 16 tps: 5098.88 qps: 30577.27 (r/w/o: 0.00/20379.52/10197.76) lat (ms,95%): 2.26 err/s: 0.00 reconn/s: 0.00
[ 35s ] thds: 16 tps: 7266.62 qps: 43558.74 (r/w/o: 0.00/29025.49/14533.25) lat (ms,95%): 2.35 err/s: 0.00 reconn/s: 0.00
[ 36s ] thds: 16 tps: 7066.32 qps: 42430.91 (r/w/o: 0.00/28298.27/14132.64) lat (ms,95%): 2.35 err/s: 0.00 reconn/s: 0.00
[ 37s ] thds: 16 tps: 5372.87 qps: 32212.22 (r/w/o: 0.00/21466.48/10745.74) lat (ms,95%): 2.30 err/s: 0.00 reconn/s: 0.00
[ 38s ] thds: 16 tps: 7014.31 qps: 42073.85 (r/w/o: 0.00/28045.23/14028.62) lat (ms,95%): 2.35 err/s: 0.00 reconn/s: 0.00
[ 39s ] thds: 16 tps: 6805.10 qps: 40891.63 (r/w/o: 0.00/27281.42/13610.21) lat (ms,95%): 2.43 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 16 tps: 5137.25 qps: 30821.48 (r/w/o: 0.00/20546.99/10274.49) lat (ms,95%): 2.39 err/s: 0.00 reconn/s: 0.00
[ 41s ] thds: 16 tps: 5341.71 qps: 31993.26 (r/w/o: 0.00/21309.84/10683.42) lat (ms,95%): 2.26 err/s: 0.00 reconn/s: 0.00
[ 42s ] thds: 16 tps: 7085.02 qps: 42505.15 (r/w/o: 0.00/28335.10/14170.05) lat (ms,95%): 2.35 err/s: 0.00 reconn/s: 0.00
[ 43s ] thds: 16 tps: 5437.44 qps: 32688.66 (r/w/o: 0.00/21813.77/10874.89) lat (ms,95%): 2.43 err/s: 0.00 reconn/s: 0.00
[ 44s ] thds: 16 tps: 6667.54 qps: 39945.23 (r/w/o: 0.00/26610.15/13335.08) lat (ms,95%): 2.39 err/s: 0.00 reconn/s: 0.00
[ 45s ] thds: 16 tps: 6886.09 qps: 41320.51 (r/w/o: 0.00/27548.34/13772.17) lat (ms,95%): 2.43 err/s: 0.00 reconn/s: 0.00
[ 46s ] thds: 16 tps: 3925.06 qps: 23606.38 (r/w/o: 0.00/15756.26/7850.13) lat (ms,95%): 2.48 err/s: 0.00 reconn/s: 0.00
[ 47s ] thds: 16 tps: 6490.99 qps: 38890.93 (r/w/o: 0.00/25908.96/12981.98) lat (ms,95%): 2.66 err/s: 0.00 reconn/s: 0.00
[ 48s ] thds: 16 tps: 4353.70 qps: 26123.18 (r/w/o: 0.00/17415.79/8707.39) lat (ms,95%): 3.96 err/s: 0.00 reconn/s: 0.00
[ 49s ] thds: 16 tps: 2337.32 qps: 14077.90 (r/w/o: 0.00/9403.27/4674.63) lat (ms,95%): 9.06 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 16 tps: 4153.69 qps: 24910.13 (r/w/o: 0.00/16603.75/8306.38) lat (ms,95%): 3.43 err/s: 0.00 reconn/s: 0.00
[ 51s ] thds: 16 tps: 5466.85 qps: 32762.12 (r/w/o: 0.00/21827.42/10934.71) lat (ms,95%): 2.39 err/s: 0.00 reconn/s: 0.00
[ 52s ] thds: 16 tps: 5962.55 qps: 35765.27 (r/w/o: 0.00/23840.18/11925.09) lat (ms,95%): 2.18 err/s: 0.00 reconn/s: 0.00
[ 53s ] thds: 16 tps: 5421.69 qps: 32539.12 (r/w/o: 0.00/21695.74/10843.37) lat (ms,95%): 2.14 err/s: 0.00 reconn/s: 0.00
[ 54s ] thds: 16 tps: 4658.89 qps: 27954.31 (r/w/o: 0.00/18636.54/9317.77) lat (ms,95%): 2.07 err/s: 0.00 reconn/s: 0.00
[ 55s ] thds: 16 tps: 8168.23 qps: 49014.40 (r/w/o: 0.00/32677.93/16336.46) lat (ms,95%): 2.30 err/s: 0.00 reconn/s: 0.00
[ 56s ] thds: 16 tps: 4832.03 qps: 28976.18 (r/w/o: 0.00/19312.12/9664.06) lat (ms,95%): 2.43 err/s: 0.00 reconn/s: 0.00
[ 57s ] thds: 16 tps: 7029.96 qps: 42184.73 (r/w/o: 0.00/28124.82/14059.91) lat (ms,95%): 2.26 err/s: 0.00 reconn/s: 0.00
[ 58s ] thds: 16 tps: 6834.35 qps: 41061.10 (r/w/o: 0.00/27392.40/13668.70) lat (ms,95%): 2.35 err/s: 0.00 reconn/s: 0.00
[ 59s ] thds: 16 tps: 5067.55 qps: 30366.31 (r/w/o: 0.00/20233.21/10133.10) lat (ms,95%): 2.30 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 16 tps: 5989.09 qps: 35921.51 (r/w/o: 0.00/23941.34/11980.17) lat (ms,95%): 2.35 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            0
        write:                           1444912
        other:                           722456
        total:                           2167368
    transactions:                        361228 (6012.31 per sec.)
    queries:                             2167368 (36073.85 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0811s
    total number of events:              361228

Latency (ms):
         min:                                    0.31
         avg:                                    2.66
         max:                                  830.42
         95th percentile:                        2.61
         sum:                               960943.62

Threads fairness:
    events (avg/stddev):           22576.7500/131.34
    execution time (avg/stddev):   60.0590/0.00

The slower server with [faster specs] Samsung SSD

sysbench oltp_write_only --threads=16 --events=0 --time=60 --mysql-host=localhost --mysql-user=sbtest --mysql-password='****' --mysql-port=3306 --tables=10 --table-size=1000000 --range_selects=off --db-ps-mode=disable --report-interval=1 run
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 16
Report intermediate results every 1 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 1s ] thds: 16 tps: 1001.31 qps: 6086.80 (r/w/o: 0.00/4068.19/2018.61) lat (ms,95%): 30.81 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 16 tps: 476.98 qps: 2811.91 (r/w/o: 0.00/1857.94/953.97) lat (ms,95%): 37.56 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 16 tps: 407.03 qps: 2493.17 (r/w/o: 0.00/1679.11/814.06) lat (ms,95%): 55.82 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 16 tps: 48.00 qps: 224.00 (r/w/o: 0.00/128.00/96.00) lat (ms,95%): 20.74 err/s: 0.00 reconn/s: 0.00
[ 5s ] thds: 16 tps: 150.00 qps: 963.99 (r/w/o: 0.00/663.99/300.00) lat (ms,95%): 1032.01 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 16 tps: 486.00 qps: 2852.02 (r/w/o: 0.00/1880.01/972.01) lat (ms,95%): 26.68 err/s: 0.00 reconn/s: 0.00
[ 7s ] thds: 16 tps: 423.00 qps: 2538.00 (r/w/o: 0.00/1692.00/846.00) lat (ms,95%): 62.19 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 16 tps: 62.00 qps: 436.00 (r/w/o: 0.00/312.00/124.00) lat (ms,95%): 943.16 err/s: 0.00 reconn/s: 0.00
[ 9s ] thds: 16 tps: 376.97 qps: 2209.80 (r/w/o: 0.00/1455.87/753.93) lat (ms,95%): 59.99 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 16 tps: 355.03 qps: 2138.20 (r/w/o: 0.00/1428.14/710.07) lat (ms,95%): 337.94 err/s: 0.00 reconn/s: 0.00
[ 11s ] thds: 16 tps: 378.00 qps: 2247.99 (r/w/o: 0.00/1491.99/756.00) lat (ms,95%): 41.10 err/s: 0.00 reconn/s: 0.00
[ 12s ] thds: 16 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 13s ] thds: 16 tps: 367.03 qps: 2257.17 (r/w/o: 0.00/1523.11/734.05) lat (ms,95%): 530.08 err/s: 0.00 reconn/s: 0.00
[ 14s ] thds: 16 tps: 249.99 qps: 1444.94 (r/w/o: 0.00/944.96/499.98) lat (ms,95%): 44.98 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 16 tps: 338.00 qps: 2028.03 (r/w/o: 0.00/1352.02/676.01) lat (ms,95%): 68.05 err/s: 0.00 reconn/s: 0.00
[ 16s ] thds: 16 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 17s ] thds: 16 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 18s ] thds: 16 tps: 131.01 qps: 845.08 (r/w/o: 0.00/583.05/262.02) lat (ms,95%): 3151.62 err/s: 0.00 reconn/s: 0.00
[ 19s ] thds: 16 tps: 309.00 qps: 1858.99 (r/w/o: 0.00/1240.99/618.00) lat (ms,95%): 530.08 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 16 tps: 117.00 qps: 638.00 (r/w/o: 0.00/404.00/234.00) lat (ms,95%): 21.50 err/s: 0.00 reconn/s: 0.00
[ 21s ] thds: 16 tps: 317.00 qps: 1902.01 (r/w/o: 0.00/1268.01/634.00) lat (ms,95%): 282.25 err/s: 0.00 reconn/s: 0.00
[ 22s ] thds: 16 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 23s ] thds: 16 tps: 191.00 qps: 1171.00 (r/w/o: 0.00/789.00/382.00) lat (ms,95%): 1739.68 err/s: 0.00 reconn/s: 0.00
[ 24s ] thds: 16 tps: 225.99 qps: 1332.93 (r/w/o: 0.00/880.95/451.98) lat (ms,95%): 287.38 err/s: 0.00 reconn/s: 0.00
[ 25s ] thds: 16 tps: 101.01 qps: 604.03 (r/w/o: 0.00/402.02/202.01) lat (ms,95%): 1050.76 err/s: 0.00 reconn/s: 0.00
[ 26s ] thds: 16 tps: 375.96 qps: 2267.77 (r/w/o: 0.00/1515.84/751.92) lat (ms,95%): 43.39 err/s: 0.00 reconn/s: 0.00
[ 27s ] thds: 16 tps: 173.01 qps: 1059.09 (r/w/o: 0.00/713.06/346.03) lat (ms,95%): 1032.01 err/s: 0.00 reconn/s: 0.00
[ 28s ] thds: 16 tps: 218.01 qps: 1275.03 (r/w/o: 0.00/839.02/436.01) lat (ms,95%): 325.98 err/s: 0.00 reconn/s: 0.00
[ 29s ] thds: 16 tps: 79.00 qps: 492.00 (r/w/o: 0.00/334.00/158.00) lat (ms,95%): 861.95 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 16 tps: 416.00 qps: 2477.99 (r/w/o: 0.00/1646.00/832.00) lat (ms,95%): 73.13 err/s: 0.00 reconn/s: 0.00
[ 31s ] thds: 16 tps: 107.00 qps: 642.00 (r/w/o: 0.00/428.00/214.00) lat (ms,95%): 1032.01 err/s: 0.00 reconn/s: 0.00
[ 32s ] thds: 16 tps: 360.95 qps: 2165.71 (r/w/o: 0.00/1443.81/721.90) lat (ms,95%): 102.97 err/s: 0.00 reconn/s: 0.00
[ 33s ] thds: 16 tps: 330.04 qps: 2044.27 (r/w/o: 0.00/1384.18/660.09) lat (ms,95%): 48.34 err/s: 0.00 reconn/s: 0.00
[ 34s ] thds: 16 tps: 16.00 qps: 32.00 (r/w/o: 0.00/0.00/32.00) lat (ms,95%): 24.38 err/s: 0.00 reconn/s: 0.00
[ 35s ] thds: 16 tps: 333.00 qps: 2062.00 (r/w/o: 0.00/1396.00/666.00) lat (ms,95%): 31.37 err/s: 0.00 reconn/s: 0.00
[ 36s ] thds: 16 tps: 207.93 qps: 1183.62 (r/w/o: 0.00/767.75/415.87) lat (ms,95%): 42.61 err/s: 0.00 reconn/s: 0.00
[ 37s ] thds: 16 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 38s ] thds: 16 tps: 633.09 qps: 3802.56 (r/w/o: 0.00/2540.37/1262.18) lat (ms,95%): 52.89 err/s: 0.00 reconn/s: 0.00
[ 39s ] thds: 16 tps: 2.00 qps: 8.00 (r/w/o: 0.00/0.00/8.00) lat (ms,95%): 58.92 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 16 tps: 479.94 qps: 2879.63 (r/w/o: 0.00/1919.75/959.88) lat (ms,95%): 383.33 err/s: 0.00 reconn/s: 0.00
[ 41s ] thds: 16 tps: 54.01 qps: 388.06 (r/w/o: 0.00/280.04/108.02) lat (ms,95%): 1050.76 err/s: 0.00 reconn/s: 0.00
[ 42s ] thds: 16 tps: 236.00 qps: 1416.00 (r/w/o: 0.00/944.00/472.00) lat (ms,95%): 634.66 err/s: 0.00 reconn/s: 0.00
[ 43s ] thds: 16 tps: 234.98 qps: 1345.87 (r/w/o: 0.00/875.92/469.95) lat (ms,95%): 41.85 err/s: 0.00 reconn/s: 0.00
[ 44s ] thds: 16 tps: 87.01 qps: 524.05 (r/w/o: 0.00/350.03/174.02) lat (ms,95%): 1453.01 err/s: 0.00 reconn/s: 0.00
[ 45s ] thds: 16 tps: 318.97 qps: 1963.83 (r/w/o: 0.00/1325.89/637.95) lat (ms,95%): 38.25 err/s: 0.00 reconn/s: 0.00
[ 46s ] thds: 16 tps: 133.00 qps: 748.01 (r/w/o: 0.00/482.01/266.00) lat (ms,95%): 29.19 err/s: 0.00 reconn/s: 0.00
[ 47s ] thds: 16 tps: 105.01 qps: 692.05 (r/w/o: 0.00/482.03/210.01) lat (ms,95%): 1129.24 err/s: 0.00 reconn/s: 0.00
[ 48s ] thds: 16 tps: 298.99 qps: 1767.92 (r/w/o: 0.00/1169.95/597.97) lat (ms,95%): 634.66 err/s: 0.00 reconn/s: 0.00
[ 49s ] thds: 16 tps: 73.00 qps: 454.01 (r/w/o: 0.00/308.01/146.00) lat (ms,95%): 943.16 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 16 tps: 304.01 qps: 1834.05 (r/w/o: 0.00/1226.03/608.02) lat (ms,95%): 634.66 err/s: 0.00 reconn/s: 0.00
[ 51s ] thds: 16 tps: 284.97 qps: 1656.83 (r/w/o: 0.00/1086.89/569.94) lat (ms,95%): 38.25 err/s: 0.00 reconn/s: 0.00
[ 52s ] thds: 16 tps: 231.02 qps: 1435.15 (r/w/o: 0.00/973.10/462.05) lat (ms,95%): 1032.01 err/s: 0.00 reconn/s: 0.00
[ 53s ] thds: 16 tps: 117.00 qps: 655.00 (r/w/o: 0.00/421.00/234.00) lat (ms,95%): 33.12 err/s: 0.00 reconn/s: 0.00
[ 54s ] thds: 16 tps: 14.00 qps: 135.00 (r/w/o: 0.00/107.00/28.00) lat (ms,95%): 1903.57 err/s: 0.00 reconn/s: 0.00
[ 55s ] thds: 16 tps: 145.01 qps: 870.04 (r/w/o: 0.00/580.03/290.01) lat (ms,95%): 831.46 err/s: 0.00 reconn/s: 0.00
[ 56s ] thds: 16 tps: 389.93 qps: 2275.62 (r/w/o: 0.00/1495.75/779.87) lat (ms,95%): 31.94 err/s: 0.00 reconn/s: 0.00
[ 57s ] thds: 16 tps: 125.02 qps: 750.09 (r/w/o: 0.00/500.06/250.03) lat (ms,95%): 1050.76 err/s: 0.00 reconn/s: 0.00
[ 58s ] thds: 16 tps: 430.99 qps: 2585.97 (r/w/o: 0.00/1723.98/861.99) lat (ms,95%): 38.25 err/s: 0.00 reconn/s: 0.00
[ 59s ] thds: 16 tps: 153.00 qps: 928.01 (r/w/o: 0.00/622.01/306.00) lat (ms,95%): 1032.01 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 16 tps: 398.98 qps: 2383.90 (r/w/o: 0.00/1585.94/797.97) lat (ms,95%): 39.65 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            0
        write:                           57580
        other:                           28790
        total:                           86370
    transactions:                        14395  (236.10 per sec.)
    queries:                             86370  (1416.61 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.9691s
    total number of events:              14395

Latency (ms):
         min:                                    4.38
         avg:                                   67.76
         max:                                 3169.51
         95th percentile:                      325.98
         sum:                               975468.52

Threads fairness:
    events (avg/stddev):           899.6875/15.79
    execution time (avg/stddev):   60.9668/0.00
8
  • I also noticed the "min" latency is 4.38 ms ?! That can't be good. And the other faster 'enterprise / datacenter' drive shows 0.31 ms min latency.
    – adrianTNT
    Apr 9 at 14:53
  • I think negatively about "throwing hardware at a performance problem". I find it to be better to find the bottlenecks (via the slowlog) and fix them. Hardware rarely gives even a 2x speedup in production; fixing queries sometimes gives 10x. And you must wait years before the next hardware 2x.
    – Rick James
    Apr 9 at 18:40
  • Is your app read-constrained? Or write? Or maybe CPU? I rarely see an app that can make use of more than a few "cores". I/O --> indexing; CPU --> indexing and query formulation.
    – Rick James
    Apr 9 at 18:41
  • In this particular issue it was write constrained, was trying to debug an extremly slow write, I moved on to simply using fastest SSD of the two. Could be a bug / firmware / etc. Can you tell me if using innodb_flush_log_at_trx_commit = 0; innodb_flush_log_at_timeout = 3; I just risk losing some insert / edits or does that also risk crashing tables ? This is by fast most effective change after innodb_buffer_pool_size.
    – adrianTNT
    Apr 9 at 22:59
  • 1
    Yes, enterprise-grade hardware really does make a difference. Your tests seem to bear that out. I also had a conversation with a Quality Control engineer from a major hardware maker, who said his day-to-day work was benchmarking enterprise-grade storage devices. He said confidently that you really need to use enterprise-rated products for database servers if performance is important. He couldn't share specific results, since those were proprietary internal data. Apr 10 at 2:55

1 Answer 1

1

TL;DR For MYSQL usage you need to compare random write speed specs of the drives, and not the (much faster) sequential speeds.


You are comparing the sequential read/write speeds of your two drives, but a database server operates a lot of random interspersed reads and writes, mostly the size of a single page (default page size for InnoDB is 16K).

SSD drives writing speed is ultimately limited by the NAND tecnology used for the memory layer, which is mostly the same for all drives (and a faster technology can be associated with a lower retention or lower number of write cycles).

To improve on this low write speed barrier, manufacturers add lots of mechanisms to cache data, parallelize read and write operations, distribute the wear all over the chips and so on.

But all these improvements can only benefit some kind of operations, like sequential reads or writes, or parallel access to different sector of the drives.

In normal usage all these optimizations can provide benefits and the user perceives a high speed, but for some specific, intensive operations, like database usage, these improvements cannot do much.

A database needs to read and write single pages from the drive. Drive internal cache will probably not help (the DBMS has his own cache, if he needs a page not in its cache it won't probably be in the drive cache either). Also, a DBMS must guarantee consistency of data, so when it writes a page, it cannot simply pass it to the OS and hope for it to be eventually written to the disk, it has to wait for the whole process to finish before proceeding, thus negating any write cache.

(unless you set innodb_flush_log_at_trx_commit=0, which doesn't flush to disk every transaction and this is why write performance becomes comparable).

Also, while you can parallelize read and write transactions on your mysql database, ultimately all those writes cannot be performed in parallel, but they must be serialized by the DBMS to ensure consistency of data (this is why DBMS all have a locking mechanism).

Also, operating at high speeds will generate a lot of heat in the drive, like when your CPU is operating at 100%, but the drive doesn't have a big fan over it to dissipate the extra heat. Many drives cannot sustain those speeds for more than a handful of minutes before overheating and having to reduce the speed.

This is another difference between "enterprise" grade and "consumer" grade which is easy to avoid noticing when comparing advertised speed.

2
  • Thanks for the details. Sequential speed specs were very different, but the random speed was about the same for the two drives, yet that Samsung one was around 20 times slower. And 4ms minimum latency seems huge, I looked around many hours, I concluded it is eider a compatibility problem with these SSDs & MySQL 8.0 or Rocky Linux 9, or some kind of hardware design probem. This MySQL bug report thread is about same Samsung 970 pro and 980 pro, that user says around 30 times slower on MySQL 8.0 vs 5.7 (same Samsung SSDs). bugs.mysql.com/bug.php?id=93734&thanks=3&notify=195
    – adrianTNT
    Apr 12 at 13:57
  • I found it funny that MySQL Verification Team said What is Samsung 970 NVME ? Is that some kind of flash drive ? :)
    – adrianTNT
    Apr 12 at 13:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.