MySQL disk IO slower than M1 Pro

Ask Fedora
, , , , ,
1

Hey all,

I’ve been experiencing performance issue for a while and would appreciate some help. I have two workstations with the following specs:

My Desktop:

  • CPU: AMD Ryzen 9 7950X - Zen 4, 16-Core, 4.5 GHz
  • Motherboard: ASRock B650E TAICHI AM5 EATX Motherboard
  • RAM: G.SKILL Flare X Series 64GB (2 x 32GB) DDR5 6000 (PC5 48000)
  • SSD: SAMSUNG SSD 990 PRO 2TB, PCIe 4.0 M.2
  • Operating System: Fedora 41 Workstation
  • Filesystem: Ext4

My Laptop:

  • CPU: Apple M1 Pro, 16GB
  • Operating System: macOS Sonoma 14.6.1

I’m using MySQL 8.0.40 on both machines. I’m a software engineer working on a project that creates at least 10 test databases and migrates them for a test environment. On my MacBook, it takes around 20 seconds to create and migrate the 10 databases. However, on my desktop, it takes an extremely long time.

I did some investigation and noticed that my MacBook writes at ~400 MB/sec during the database operations, while my desktop only writes around ~30 MB/sec, sometimes spiking to ~60 MB/sec.

Here is my sysbench mysql benchmark results on Fedora 41

sysbench 1.0.20 (using system LuaJIT 2.1.1713773202)

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


Initializing worker threads...

Threads started!

[ 10s ] thds: 8 tps: 65.90 qps: 1328.31 (r/w/o: 931.73/263.98/132.59) lat (ms,95%): 253.35 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 8 tps: 50.60 qps: 1011.64 (r/w/o: 707.53/202.91/101.20) lat (ms,95%): 344.08 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 8 tps: 58.30 qps: 1162.49 (r/w/o: 813.59/232.30/116.60) lat (ms,95%): 314.45 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 8 tps: 64.90 qps: 1296.41 (r/w/o: 907.01/259.60/129.80) lat (ms,95%): 262.64 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 8 tps: 53.60 qps: 1074.10 (r/w/o: 752.10/214.80/107.20) lat (ms,95%): 314.45 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 8 tps: 66.80 qps: 1338.68 (r/w/o: 937.59/267.50/133.60) lat (ms,95%): 253.35 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 8 tps: 49.80 qps: 994.81 (r/w/o: 696.71/198.50/99.60) lat (ms,95%): 320.17 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 8 tps: 68.40 qps: 1369.50 (r/w/o: 959.10/273.60/136.80) lat (ms,95%): 244.38 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 8 tps: 67.30 qps: 1343.90 (r/w/o: 939.50/269.80/134.60) lat (ms,95%): 227.40 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 8 tps: 66.50 qps: 1329.20 (r/w/o: 930.20/266.00/133.00) lat (ms,95%): 262.64 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 8 tps: 65.00 qps: 1297.09 (r/w/o: 907.69/259.40/130.00) lat (ms,95%): 262.64 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 8 tps: 58.60 qps: 1174.09 (r/w/o: 821.69/235.20/117.20) lat (ms,95%): 303.33 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            103110
        write:                           29460
        other:                           14730
        total:                           147300
    transactions:                        7365   (61.31 per sec.)
    queries:                             147300 (1226.14 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          120.1331s
    total number of events:              7365

Latency (ms):
         min:                                   12.77
         avg:                                  130.45
         max:                                  557.78
         95th percentile:                      282.25
         sum:                               960783.62

Threads fairness:
    events (avg/stddev):           920.6250/14.47
    execution time (avg/stddev):   120.0980/0.05

This shows 29.460 writes, my m1 pro shows 2.734.212. Same sysbench config.

I ran a quick benchmark (KDiskMark), and my SSD seems fine, so I’m not sure what the issue is, or am I thinking wrong about my workstation?

2

Added f41, filesystem, mysql, workstation

3

A few things to look into:

  • What model is the SSD in the mac? (some are faster/slower)
  • What is the interface to the SSD? (PCIe, SATA, etcetera)
  • I would try to investigate and isolate RW speed of both by running a few different generic RW benchmark tests. Is there a trend, or is it just MySQL?
  • Are both instances of the MySQL from the official repos, or did you compile them yourself? (if so, what compile flags, etcereta. If not, where did you get the bins?)
  • What is the filesystem used by mac? Is it BTRFS? IDK, I don’t use mac.
1 Like
4

Actually, I think it has to do with Ext4 journaling. If I add barrier=0 it’s way wayyy better. But I’m not sure if this is the correct solution here. Here mysql benchmark results with/without barriers

With barrier

samet@bequiet:~$ sysbench oltp_read_write --threads=16 --events=0 --time=60 --mysql-host=127.0.0.1 --mysql-user=root --mysql-password=abcabc123 --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.1713773202)

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: 461.70 qps: 7593.07 (r/w/o: 4771.90/1881.78/939.39) lat (ms,95%): 40.37 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 16 tps: 70.01 qps: 1060.17 (r/w/o: 659.11/261.04/140.02) lat (ms,95%): 411.96 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 16 tps: 87.01 qps: 1416.21 (r/w/o: 882.13/360.05/174.03) lat (ms,95%): 308.84 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 16 tps: 100.99 qps: 1597.77 (r/w/o: 1011.85/383.94/201.97) lat (ms,95%): 282.25 err/s: 0.00 reconn/s: 0.00
[ 5s ] thds: 16 tps: 145.02 qps: 2311.31 (r/w/o: 1436.19/585.08/290.04) lat (ms,95%): 164.45 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 16 tps: 122.00 qps: 1947.01 (r/w/o: 1217.01/486.00/244.00) lat (ms,95%): 253.35 err/s: 0.00 reconn/s: 0.00
[ 7s ] thds: 16 tps: 121.98 qps: 1989.71 (r/w/o: 1242.82/502.93/243.96) lat (ms,95%): 227.40 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 16 tps: 124.02 qps: 2012.29 (r/w/o: 1251.18/513.07/248.04) lat (ms,95%): 215.44 err/s: 0.00 reconn/s: 0.00
[ 9s ] thds: 16 tps: 135.00 qps: 2145.98 (r/w/o: 1346.99/529.00/270.00) lat (ms,95%): 211.60 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 16 tps: 140.99 qps: 2252.78 (r/w/o: 1404.86/565.94/281.97) lat (ms,95%): 207.82 err/s: 0.00 reconn/s: 0.00
[ 11s ] thds: 16 tps: 128.01 qps: 2045.23 (r/w/o: 1287.15/502.06/256.03) lat (ms,95%): 211.60 err/s: 0.00 reconn/s: 0.00
[ 12s ] thds: 16 tps: 135.00 qps: 2156.99 (r/w/o: 1343.99/543.00/270.00) lat (ms,95%): 186.54 err/s: 0.00 reconn/s: 0.00
[ 13s ] thds: 16 tps: 129.00 qps: 2043.01 (r/w/o: 1264.01/521.00/258.00) lat (ms,95%): 189.93 err/s: 0.00 reconn/s: 0.00
[ 14s ] thds: 16 tps: 141.00 qps: 2200.96 (r/w/o: 1379.97/539.99/280.99) lat (ms,95%): 173.58 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 16 tps: 129.99 qps: 2126.88 (r/w/o: 1344.92/520.97/260.99) lat (ms,95%): 183.21 err/s: 0.00 reconn/s: 0.00
[ 16s ] thds: 16 tps: 120.01 qps: 1939.15 (r/w/o: 1204.09/495.04/240.02) lat (ms,95%): 204.11 err/s: 0.00 reconn/s: 0.00
[ 17s ] thds: 16 tps: 104.99 qps: 1686.82 (r/w/o: 1058.89/417.95/209.98) lat (ms,95%): 262.64 err/s: 0.00 reconn/s: 0.00
[ 18s ] thds: 16 tps: 116.01 qps: 1878.19 (r/w/o: 1167.12/479.05/232.02) lat (ms,95%): 231.53 err/s: 0.00 reconn/s: 0.00
[ 19s ] thds: 16 tps: 76.00 qps: 1198.00 (r/w/o: 773.00/273.00/152.00) lat (ms,95%): 235.74 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 16 tps: 43.00 qps: 702.00 (r/w/o: 418.00/198.00/86.00) lat (ms,95%): 1213.57 err/s: 0.00 reconn/s: 0.00
[ 21s ] thds: 16 tps: 122.00 qps: 1915.00 (r/w/o: 1188.00/483.00/244.00) lat (ms,95%): 248.83 err/s: 0.00 reconn/s: 0.00
[ 22s ] thds: 16 tps: 10.00 qps: 178.00 (r/w/o: 144.00/14.00/20.00) lat (ms,95%): 200.47 err/s: 0.00 reconn/s: 0.00
[ 23s ] thds: 16 tps: 0.00 qps: 3.00 (r/w/o: 0.00/3.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 24s ] thds: 16 tps: 136.98 qps: 2187.65 (r/w/o: 1369.78/543.91/273.96) lat (ms,95%): 2159.29 err/s: 0.00 reconn/s: 0.00
[ 25s ] thds: 16 tps: 94.01 qps: 1528.24 (r/w/o: 931.15/409.07/188.03) lat (ms,95%): 893.56 err/s: 0.00 reconn/s: 0.00
[ 26s ] thds: 16 tps: 103.00 qps: 1637.00 (r/w/o: 1039.00/392.00/206.00) lat (ms,95%): 227.40 err/s: 0.00 reconn/s: 0.00
[ 27s ] thds: 16 tps: 109.00 qps: 1743.00 (r/w/o: 1079.00/446.00/218.00) lat (ms,95%): 434.83 err/s: 0.00 reconn/s: 0.00
[ 28s ] thds: 16 tps: 68.00 qps: 1078.00 (r/w/o: 691.00/251.00/136.00) lat (ms,95%): 189.93 err/s: 0.00 reconn/s: 0.00
[ 29s ] thds: 16 tps: 6.00 qps: 98.00 (r/w/o: 60.00/26.00/12.00) lat (ms,95%): 1129.24 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 16 tps: 108.00 qps: 1720.01 (r/w/o: 1056.00/448.00/216.00) lat (ms,95%): 2198.52 err/s: 0.00 reconn/s: 0.00
[ 31s ] thds: 16 tps: 145.00 qps: 2318.99 (r/w/o: 1442.99/586.00/290.00) lat (ms,95%): 189.93 err/s: 0.00 reconn/s: 0.00
[ 32s ] thds: 16 tps: 93.00 qps: 1502.00 (r/w/o: 961.00/355.00/186.00) lat (ms,95%): 167.44 err/s: 0.00 reconn/s: 0.00
[ 33s ] thds: 16 tps: 70.00 qps: 1114.99 (r/w/o: 699.99/275.00/140.00) lat (ms,95%): 1191.92 err/s: 0.00 reconn/s: 0.00
[ 34s ] thds: 16 tps: 43.00 qps: 662.00 (r/w/o: 398.00/178.00/86.00) lat (ms,95%): 1069.86 err/s: 0.00 reconn/s: 0.00
[ 35s ] thds: 16 tps: 114.00 qps: 1850.99 (r/w/o: 1150.00/473.00/228.00) lat (ms,95%): 231.53 err/s: 0.00 reconn/s: 0.00
[ 36s ] thds: 16 tps: 136.00 qps: 2181.01 (r/w/o: 1365.00/544.00/272.00) lat (ms,95%): 223.34 err/s: 0.00 reconn/s: 0.00
[ 37s ] thds: 16 tps: 120.98 qps: 1920.73 (r/w/o: 1205.83/472.93/241.97) lat (ms,95%): 244.38 err/s: 0.00 reconn/s: 0.00
[ 38s ] thds: 16 tps: 108.01 qps: 1740.24 (r/w/o: 1074.15/450.06/216.03) lat (ms,95%): 240.02 err/s: 0.00 reconn/s: 0.00
[ 39s ] thds: 16 tps: 119.00 qps: 1929.02 (r/w/o: 1209.01/482.00/238.00) lat (ms,95%): 223.34 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 16 tps: 134.00 qps: 2129.98 (r/w/o: 1336.99/525.00/268.00) lat (ms,95%): 193.38 err/s: 0.00 reconn/s: 0.00
[ 41s ] thds: 16 tps: 125.00 qps: 2029.05 (r/w/o: 1260.03/519.01/250.01) lat (ms,95%): 227.40 err/s: 0.00 reconn/s: 0.00
[ 42s ] thds: 16 tps: 143.00 qps: 2175.01 (r/w/o: 1358.01/531.00/286.00) lat (ms,95%): 193.38 err/s: 0.00 reconn/s: 0.00
[ 43s ] thds: 16 tps: 20.00 qps: 391.00 (r/w/o: 273.00/78.00/40.00) lat (ms,95%): 200.47 err/s: 0.00 reconn/s: 0.00
[ 44s ] thds: 16 tps: 117.98 qps: 1879.70 (r/w/o: 1150.81/492.92/235.96) lat (ms,95%): 1191.92 err/s: 0.00 reconn/s: 0.00
[ 45s ] thds: 16 tps: 122.02 qps: 1970.25 (r/w/o: 1227.16/499.06/244.03) lat (ms,95%): 231.53 err/s: 0.00 reconn/s: 0.00
[ 46s ] thds: 16 tps: 122.98 qps: 1964.75 (r/w/o: 1240.84/477.94/245.97) lat (ms,95%): 207.82 err/s: 0.00 reconn/s: 0.00
[ 47s ] thds: 16 tps: 120.02 qps: 1924.28 (r/w/o: 1205.18/479.07/240.04) lat (ms,95%): 227.40 err/s: 0.00 reconn/s: 0.00
[ 48s ] thds: 16 tps: 112.00 qps: 1779.00 (r/w/o: 1109.00/446.00/224.00) lat (ms,95%): 248.83 err/s: 0.00 reconn/s: 0.00
[ 49s ] thds: 16 tps: 117.00 qps: 1887.00 (r/w/o: 1175.00/478.00/234.00) lat (ms,95%): 267.41 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 16 tps: 112.00 qps: 1783.97 (r/w/o: 1112.98/446.99/224.00) lat (ms,95%): 248.83 err/s: 0.00 reconn/s: 0.00
[ 51s ] thds: 16 tps: 68.99 qps: 1094.85 (r/w/o: 708.90/247.97/137.98) lat (ms,95%): 200.47 err/s: 0.00 reconn/s: 0.00
[ 52s ] thds: 16 tps: 61.99 qps: 990.91 (r/w/o: 619.94/246.98/123.99) lat (ms,95%): 1109.09 err/s: 0.00 reconn/s: 0.00
[ 53s ] thds: 16 tps: 82.02 qps: 1303.33 (r/w/o: 789.20/350.09/164.04) lat (ms,95%): 943.16 err/s: 0.00 reconn/s: 0.00
[ 54s ] thds: 16 tps: 121.99 qps: 1947.79 (r/w/o: 1210.87/492.95/243.97) lat (ms,95%): 257.95 err/s: 0.00 reconn/s: 0.00
[ 55s ] thds: 16 tps: 139.01 qps: 2254.24 (r/w/o: 1416.15/560.06/278.03) lat (ms,95%): 211.60 err/s: 0.00 reconn/s: 0.00
[ 56s ] thds: 16 tps: 11.00 qps: 170.00 (r/w/o: 124.00/24.00/22.00) lat (ms,95%): 227.40 err/s: 0.00 reconn/s: 0.00
[ 57s ] thds: 16 tps: 115.00 qps: 1817.01 (r/w/o: 1110.01/477.00/230.00) lat (ms,95%): 1170.65 err/s: 0.00 reconn/s: 0.00
[ 58s ] thds: 16 tps: 167.98 qps: 2691.61 (r/w/o: 1692.76/662.90/335.95) lat (ms,95%): 173.58 err/s: 0.00 reconn/s: 0.00
[ 59s ] thds: 16 tps: 153.00 qps: 2438.01 (r/w/o: 1522.01/610.00/306.00) lat (ms,95%): 183.21 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 16 tps: 62.01 qps: 1015.14 (r/w/o: 655.09/236.03/124.02) lat (ms,95%): 173.58 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            65830
        write:                           26332
        other:                           13166
        total:                           105328
    transactions:                        6583   (108.81 per sec.)
    queries:                             105328 (1741.02 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.4975s
    total number of events:              6583

Latency (ms):
         min:                                   14.29
         avg:                                  146.94
         max:                                 2229.49
         95th percentile:                      262.64
         sum:                               967324.31

Threads fairness:
    events (avg/stddev):           411.4375/8.25
    execution time (avg/stddev):   60.4578/0.03

Without barrier

samet@bequiet:~$ sysbench oltp_read_write --threads=16 --events=0 --time=60 --mysql-host=127.0.0.1 --mysql-user=root --mysql-password=abcabc123 --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.1713773202)

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: 3450.37 qps: 55368.88 (r/w/o: 34641.66/13810.48/6916.74) lat (ms,95%): 5.47 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 16 tps: 2925.46 qps: 46837.31 (r/w/o: 29258.57/11727.83/5850.91) lat (ms,95%): 6.55 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 16 tps: 3302.61 qps: 52859.72 (r/w/o: 33044.07/13210.43/6605.21) lat (ms,95%): 6.21 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 16 tps: 3847.30 qps: 61422.74 (r/w/o: 38373.96/15358.19/7690.59) lat (ms,95%): 6.32 err/s: 0.00 reconn/s: 0.00
[ 5s ] thds: 16 tps: 3825.15 qps: 61226.41 (r/w/o: 38271.51/15301.60/7653.30) lat (ms,95%): 5.99 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 16 tps: 3833.97 qps: 61357.44 (r/w/o: 38343.65/15344.86/7668.93) lat (ms,95%): 5.99 err/s: 0.00 reconn/s: 0.00
[ 7s ] thds: 16 tps: 3894.05 qps: 62254.77 (r/w/o: 38901.48/15566.19/7787.10) lat (ms,95%): 5.37 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 16 tps: 4415.93 qps: 70742.94 (r/w/o: 44228.34/17682.74/8831.87) lat (ms,95%): 5.47 err/s: 0.00 reconn/s: 0.00
[ 9s ] thds: 16 tps: 3320.03 qps: 53109.53 (r/w/o: 33202.33/13266.13/6641.07) lat (ms,95%): 5.00 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 16 tps: 4945.01 qps: 79074.13 (r/w/o: 49412.08/19772.03/9890.02) lat (ms,95%): 4.91 err/s: 0.00 reconn/s: 0.00
[ 11s ] thds: 16 tps: 3514.65 qps: 56374.34 (r/w/o: 35227.46/14117.58/7029.29) lat (ms,95%): 5.09 err/s: 0.00 reconn/s: 0.00
[ 12s ] thds: 16 tps: 4454.42 qps: 71158.76 (r/w/o: 44487.23/17762.69/8908.85) lat (ms,95%): 4.91 err/s: 0.00 reconn/s: 0.00
[ 13s ] thds: 16 tps: 4456.14 qps: 71308.28 (r/w/o: 44558.42/17839.57/8910.28) lat (ms,95%): 4.91 err/s: 0.00 reconn/s: 0.00
[ 14s ] thds: 16 tps: 4374.77 qps: 70013.28 (r/w/o: 43761.67/17500.07/8751.53) lat (ms,95%): 4.74 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 16 tps: 3864.62 qps: 61910.84 (r/w/o: 38684.15/15497.46/7729.23) lat (ms,95%): 4.74 err/s: 0.00 reconn/s: 0.00
[ 16s ] thds: 16 tps: 4397.51 qps: 70346.16 (r/w/o: 43973.10/17578.04/8795.02) lat (ms,95%): 4.65 err/s: 0.00 reconn/s: 0.00
[ 17s ] thds: 16 tps: 4459.01 qps: 71247.20 (r/w/o: 44532.12/17800.05/8915.02) lat (ms,95%): 4.74 err/s: 0.00 reconn/s: 0.00
[ 18s ] thds: 16 tps: 4422.03 qps: 70751.44 (r/w/o: 44218.28/17687.11/8846.06) lat (ms,95%): 4.49 err/s: 0.00 reconn/s: 0.00
[ 19s ] thds: 16 tps: 4549.94 qps: 72826.05 (r/w/o: 45525.40/18199.76/9100.88) lat (ms,95%): 3.96 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 16 tps: 4559.01 qps: 72966.21 (r/w/o: 45609.13/18239.05/9118.03) lat (ms,95%): 4.25 err/s: 0.00 reconn/s: 0.00
[ 21s ] thds: 16 tps: 4574.89 qps: 73210.27 (r/w/o: 45770.92/18289.57/9149.78) lat (ms,95%): 4.18 err/s: 0.00 reconn/s: 0.00
[ 22s ] thds: 16 tps: 4912.16 qps: 78562.55 (r/w/o: 49082.59/19656.64/9823.32) lat (ms,95%): 4.33 err/s: 0.00 reconn/s: 0.00
[ 23s ] thds: 16 tps: 4473.89 qps: 71617.19 (r/w/o: 44746.87/17922.55/8947.77) lat (ms,95%): 4.10 err/s: 0.00 reconn/s: 0.00
[ 24s ] thds: 16 tps: 5098.12 qps: 81534.85 (r/w/o: 50968.16/20371.46/10195.23) lat (ms,95%): 4.03 err/s: 0.00 reconn/s: 0.00
[ 25s ] thds: 16 tps: 4527.00 qps: 72411.03 (r/w/o: 45256.02/18100.01/9055.00) lat (ms,95%): 3.96 err/s: 0.00 reconn/s: 0.00
[ 26s ] thds: 16 tps: 4405.06 qps: 70596.95 (r/w/o: 44111.59/17674.24/8811.12) lat (ms,95%): 4.03 err/s: 0.00 reconn/s: 0.00
[ 27s ] thds: 16 tps: 4642.94 qps: 74203.01 (r/w/o: 46389.38/18528.75/9284.88) lat (ms,95%): 3.96 err/s: 0.00 reconn/s: 0.00
[ 28s ] thds: 16 tps: 4541.00 qps: 72648.98 (r/w/o: 45409.99/18155.99/9083.00) lat (ms,95%): 4.03 err/s: 0.00 reconn/s: 0.00
[ 29s ] thds: 16 tps: 4578.82 qps: 73255.17 (r/w/o: 45783.23/18314.29/9157.65) lat (ms,95%): 3.89 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 16 tps: 5010.16 qps: 80160.54 (r/w/o: 50097.59/20042.63/10020.32) lat (ms,95%): 4.03 err/s: 0.00 reconn/s: 0.00
[ 31s ] thds: 16 tps: 4486.93 qps: 71831.87 (r/w/o: 44896.30/17961.72/8973.86) lat (ms,95%): 4.18 err/s: 0.00 reconn/s: 0.00
[ 32s ] thds: 16 tps: 4420.07 qps: 70692.12 (r/w/o: 44191.70/17661.28/8839.14) lat (ms,95%): 4.18 err/s: 0.00 reconn/s: 0.00
[ 33s ] thds: 16 tps: 4599.99 qps: 73597.87 (r/w/o: 45978.92/18418.97/9199.98) lat (ms,95%): 3.96 err/s: 0.00 reconn/s: 0.00
[ 34s ] thds: 16 tps: 4611.03 qps: 73776.43 (r/w/o: 46119.27/18434.11/9223.05) lat (ms,95%): 3.82 err/s: 0.00 reconn/s: 0.00
[ 35s ] thds: 16 tps: 4583.98 qps: 73309.61 (r/w/o: 45809.76/18332.90/9166.95) lat (ms,95%): 3.82 err/s: 0.00 reconn/s: 0.00
[ 36s ] thds: 16 tps: 4651.03 qps: 74539.45 (r/w/o: 46583.28/18653.11/9303.06) lat (ms,95%): 3.68 err/s: 0.00 reconn/s: 0.00
[ 37s ] thds: 16 tps: 4612.99 qps: 73727.85 (r/w/o: 46092.91/18409.96/9224.98) lat (ms,95%): 3.82 err/s: 0.00 reconn/s: 0.00
[ 38s ] thds: 16 tps: 4475.00 qps: 71567.04 (r/w/o: 44731.02/17885.01/8951.00) lat (ms,95%): 3.96 err/s: 0.00 reconn/s: 0.00
[ 39s ] thds: 16 tps: 4556.02 qps: 72928.25 (r/w/o: 45585.16/18231.06/9112.03) lat (ms,95%): 4.03 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 16 tps: 4675.97 qps: 74808.50 (r/w/o: 46743.69/18712.88/9351.94) lat (ms,95%): 3.68 err/s: 0.00 reconn/s: 0.00
[ 41s ] thds: 16 tps: 4608.98 qps: 73753.74 (r/w/o: 46102.84/18433.94/9216.97) lat (ms,95%): 3.82 err/s: 0.00 reconn/s: 0.00
[ 42s ] thds: 16 tps: 4670.02 qps: 74724.34 (r/w/o: 46701.21/18683.09/9340.04) lat (ms,95%): 3.68 err/s: 0.00 reconn/s: 0.00
[ 43s ] thds: 16 tps: 4620.00 qps: 73910.03 (r/w/o: 46201.02/18468.01/9241.00) lat (ms,95%): 3.82 err/s: 0.00 reconn/s: 0.00
[ 44s ] thds: 16 tps: 4648.01 qps: 74333.08 (r/w/o: 46439.05/18599.02/9295.01) lat (ms,95%): 3.75 err/s: 0.00 reconn/s: 0.00
[ 45s ] thds: 16 tps: 4675.02 qps: 74772.27 (r/w/o: 46730.17/18693.07/9349.03) lat (ms,95%): 3.75 err/s: 0.00 reconn/s: 0.00
[ 46s ] thds: 16 tps: 4631.98 qps: 74159.73 (r/w/o: 46366.83/18526.93/9265.97) lat (ms,95%): 3.75 err/s: 0.00 reconn/s: 0.00
[ 47s ] thds: 16 tps: 4616.91 qps: 73842.60 (r/w/o: 46142.12/18466.65/9233.82) lat (ms,95%): 3.75 err/s: 0.00 reconn/s: 0.00
[ 48s ] thds: 16 tps: 4656.11 qps: 74624.74 (r/w/o: 46634.09/18678.44/9312.22) lat (ms,95%): 3.62 err/s: 0.00 reconn/s: 0.00
[ 49s ] thds: 16 tps: 5076.98 qps: 81231.65 (r/w/o: 50769.78/20307.91/10153.96) lat (ms,95%): 3.68 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 16 tps: 4770.95 qps: 76230.16 (r/w/o: 47653.47/19035.79/9540.89) lat (ms,95%): 3.62 err/s: 0.00 reconn/s: 0.00
[ 51s ] thds: 16 tps: 4658.06 qps: 74545.02 (r/w/o: 46602.64/18625.26/9317.13) lat (ms,95%): 3.68 err/s: 0.00 reconn/s: 0.00
[ 52s ] thds: 16 tps: 4722.98 qps: 75530.67 (r/w/o: 47195.80/18890.92/9443.96) lat (ms,95%): 3.68 err/s: 0.00 reconn/s: 0.00
[ 53s ] thds: 16 tps: 4598.96 qps: 73603.37 (r/w/o: 45996.61/18406.84/9199.92) lat (ms,95%): 3.75 err/s: 0.00 reconn/s: 0.00
[ 54s ] thds: 16 tps: 4656.04 qps: 74475.69 (r/w/o: 46553.43/18611.17/9311.09) lat (ms,95%): 3.82 err/s: 0.00 reconn/s: 0.00
[ 55s ] thds: 16 tps: 4699.94 qps: 75230.99 (r/w/o: 47014.37/18815.75/9400.87) lat (ms,95%): 3.68 err/s: 0.00 reconn/s: 0.00
[ 56s ] thds: 16 tps: 4632.06 qps: 74131.01 (r/w/o: 46339.63/18528.25/9263.13) lat (ms,95%): 3.68 err/s: 0.00 reconn/s: 0.00
[ 57s ] thds: 16 tps: 4653.00 qps: 74394.99 (r/w/o: 46492.99/18596.00/9306.00) lat (ms,95%): 3.68 err/s: 0.00 reconn/s: 0.00
[ 58s ] thds: 16 tps: 5144.99 qps: 82352.82 (r/w/o: 51469.89/20591.96/10290.98) lat (ms,95%): 3.68 err/s: 0.00 reconn/s: 0.00
[ 59s ] thds: 16 tps: 4681.99 qps: 74897.82 (r/w/o: 46806.89/18726.96/9363.98) lat (ms,95%): 3.82 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 16 tps: 4633.98 qps: 74152.69 (r/w/o: 46349.81/18536.92/9265.96) lat (ms,95%): 3.75 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            2673220
        write:                           1069288
        other:                           534644
        total:                           4277152
    transactions:                        267322 (4455.15 per sec.)
    queries:                             4277152 (71282.38 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0027s
    total number of events:              267322

Latency (ms):
         min:                                    1.25
         avg:                                    3.59
         max:                                  158.51
         95th percentile:                        4.25
         sum:                               959835.91

Threads fairness:
    events (avg/stddev):           16707.6250/15.34
    execution time (avg/stddev):   59.9897/0.00

Here are some answers to your questions

  • What model is the SSD in the mac? (some are faster/slower)
    • Apple SSD AP0512R 494GB
  • What is the interface to the SSD? (PCIe, SATA, etcetera)
    • PCIe both
  • Are both instances of the MySQL from the official repos, or did you compile them yourself? (if so, what compile flags, etcereta. If not, where did you get the bins?)
    • From official repos (Oracle)
  • What is the filesystem used by mac? Is it BTRFS? IDK, I don’t use mac.
    • APFS
5

Huh. That’s weird. So doesn’t APFS do journalling that works with barrier or something? Or are the barriers just disabled on the mac version? I’m not sure I have a helpful answer any more. Sorry.

Though I saw here for answers on disabling barrier, and the “Nitty Gritty Details About The nobarrier Option” section in here for some more notes.

EDIT:
Actually, check out the answers here. Apparently APFS doesn’t need journalling due to redirect-on-write.

EDIT^2:
Actually you might consider Btrfs for your DB drive, which also doesn’t require journalling. This should give a fairer, though not equal, comparison against APFS.

6

Added apple-silicon, hardware

7

Disabling barrier is generally not a good idea if you value data integrity.

Ext4 should be very performant out of the box with barrier enabled. Also Btrfs shouldn’t normally give a better performance than Ext4.

I ran a quick benchmark (KDiskMark), and my SSD seems fine

What do you mean by this? That the SSD performance is as expected, but that only Mysql is underperforming? This would be an important notion, and would shft the focus towards Mysql rather than the SSD itself.

In general, for the linux machine it might be good to check if the right block drivers are loaded and if the interface is set to AHCI in the bios. It’s also good to check if the SSD is placed in the right motherboard slot, since sometimes only one slot will support full throughput.

It may also help to do a drive check just in case with smartctl to rule out any issues with the Samsung SSD.

1 Like
8

Makes sense. Let me provide more detailed benchmarks. I’ll set MySQL aside for now and focus on file operations.

I have two disks:

  • Samsung 990 PRO 2TB (NVMe)
  • Samsung 870 EVO 4TB (SATA)

I ran the following sysbench command on both disks:

sysbench fileio --file-total-size=32G --file-test-mode=rndrw --threads=16 --time=60 --max-requests=0 prepare
sysbench fileio --file-total-size=32G --file-test-mode=rndrw --threads=16 --time=60 --max-requests=0 run
sysbench fileio --file-total-size=32G --file-test-mode=rndrw --threads=16 --time=60 --max-requests=0 cleanup

Results for the 990 PRO:

Results for the 870 EVO:

I’ve noticed better file operation performance on the 870 EVO, but I’m not very familiar with filesystems and SSDs. The 990 PRO is installed on a CPU bus, so it should be in the correct slot.

This is my /etc/fstab

#
# /etc/fstab
# Created by anaconda on Sun Nov 10 16:23:57 2024
#
# Accessible filesystems, by reference, are maintained under '/dev/disk/'.
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info.
#
# After editing this file, run 'systemctl daemon-reload' to update systemd
# units generated from this file.
#
UUID=<some_uuid>  /                       ext4    defaults        1 1
UUID=<some_uuid>  /boot/efi               vfat     umask=0077,shortname=winnt 0 2
UUID=<some_uuid>  /home                   ext4    defaults        1 2
LABEL=870EVO      /mnt/870EVO             auto nosuid,nodev,nofail,x-gvfs-show 0 0
9

The speeds of the 870 are not great, SATA itself is not very fast but i suspect you should be able to get better speeds than this.

The performance of the 990 seems very good. Even though it’s below the advertised maximum, you should easily be able to match the M1’s 400MB/s write operations in MySQL.

I assume you are only using the 990 for Mysql right? If you would inadvertently use the 870 then it would explain the low performance.

It doesn’t appear there is much wrong with the 990, so perhaps it would be good to focus a bit more on Mysql in that case. Have you compared the my.cnf file between the two computers already? Perhaps there are different memory or concurrency settings in the Mac configuration than in the linux version?

10

In the sysbench results, the 990 performs worse than the 870 on file operations, if I’m not mistaken. Do you think that’s normal? I’ve compared the MySQL variables between the two systems, and there are some minor differences.

my.cnf diff

11

You’re right it isn’t very fast in the random r/w test, it’s possible that there is also a penalty there for the fsyncs that the test does.

How full is the 990? If it reaches 80% capacity or so it may get significantly slower on those r/w tests.

For what it’s worth, i ran the test on my system with M2 SSD and it’s not very fast either in this area.

I’m afraid i don’t have any other suggestions at this point…

12

I’m not on my desktop workstation right now, but it’s a fresh installation of Fedora 41—nothing else installed yet. Thanks for helping me triage the issue.

13

I’m surprised nobody has mentioned this, but EXT4 does not support concurrent read/writes.

XFS does. I am unsure about BTRFS.

Also, your NVME may have 4k sectors. If so, you could massively improve performance by changing the sectors from 512 to 4k.

Another thing nobody has mentioned is that Mac’s have insane RAM bandwidth and your workstation has like 64GB/s with DDR5. Which could cause your database read/write speeds to be slower.

14

Do you have a good reference for changing sector size in Fedora?

15

You would need to use something like nvme. However, this would also format the nvme, so… hopefully its not your boot drive.

You can see if it supports 4k with the following. If it does it will be output like so:

sudo nvme id-ns -H /dev/nvme0n1 | grep "Relative Performance"
LBA Format  0 : Metadata Size: 0   bytes - Data Size: 512 bytes - Relative Performance: 0x2 Good 
LBA Format  1 : Metadata Size: 0   bytes - Data Size: 4096 bytes - Relative Performance: 0x1 Better (in use)
16

I only see

LBA Format  0 : Metadata Size: 0   bytes - Data Size: 512 bytes - Relative Performance: 0 Best (in use)

I assume Samsung 990 PRO doesn’t support 4k.

17

Yep, that would unfortunately be the case if its missing from the output :slight_smile:

18

This is a pretty good find, the 512/4096 conversion could adversely impact performance greatly for many small read/write operations. What does:

$ stat -f /

give back? (assuming that the 990 is your root disk)

4k is the default ext4 blocksize.

19

Here is the result for / and /home

samet@bequiet:~$ stat -f /
  File: "/"
    ID: cd150dbe23fb726c Namelen: 255     Type: ext2/ext3
Block size: 4096       Fundamental block size: 4096
Blocks: Total: 16369547   Free: 14195816   Available: 13352860
Inodes: Total: 4194304    Free: 3944365

samet@bequiet:~$ stat -f /home
  File: "/home"
    ID: 8a17243752c56fbc Namelen: 255     Type: ext2/ext3
Block size: 4096       Fundamental block size: 4096
Blocks: Total: 463659224  Free: 457536096  Available: 433965050
Inodes: Total: 117841920  Free: 117526413

samet@bequiet:~$ sudo nvme id-ns -H /dev/nvme0n1 | grep "Relative Performance"
LBA Format  0 : Metadata Size: 0   bytes - Data Size: 512 bytes - Relative Performance: 0 Best (in use)
20

IIRC that may just mean the drive is faking 4k sectors.

Either way, if nvme id-ns doesn’t show 4k, it doesn’t support native 4k.