note: I initially asked this question on stackoverflow, but was told superuser would be a better place for it.
I'm having performance issues while loading an sql dump into a new schema on my development machine: Creating the dump is quite fast (around 30s) but loading it back in takes more than 10 minutes.
My actual use case doesn't require an actual dump, I just need to clone one database (/schema ?), on the same server, as fast as possible. Doing a dump & load is simply the only way I've found to do that for now.
Here's what I'm doing so far:
mysqldump --opt MY_DATABASE > ~/my-db-dump.sql
mysql MY_NEW_DATABASE < ~/my-db-dump.sql
(I'll eventually combine these two steps, but this helps me see what part takes time, and allows inspecting the generated SQL)
The dump is 300MB, and contains the statements I would expect from the --opt flag:
- UNIQUE_CHECKS=0
- FOREIGN_KEY_CHECKS=0
- DISABLE KEYS
etc...
I've also tried increasing innodb_buffer_pool_size and disabling innodb_doublewrite, without success:
+-------------------------+-------------+
| Variable_name | Value |
+-------------------------+-------------+
| innodb_buffer_pool_size | 17179869184 |
| innodb_doublewrite | OFF |
+-------------------------+-------------+
I asked a colleague to try loading my dump on his computer, and that took 40 seconds so I am pretty sure the issue is not on the dump side. We have essentially the same values in sql VARIABLES, apart from version_compile_machine (x86_64 vs aarch64), and a few timestamps, process IDs and file paths.
What might explain the difference between the two ? Could it be only caused by the different devices ?
Edit: As requested in a comment, here are the results of a disk benchmark.
KDiskMark (3.1.4): https://github.com/JonMagon/KDiskMark
Flexible I/O Tester (fio-3.28): https://github.com/axboe/fio
--------------------------------------------------------------------------------
* MB/s = 1,000,000 bytes/s [SATA/600 = 600,000,000 bytes/s]
* KB = 1000 bytes, KiB = 1024 bytes
[Read]
Sequential 1 MiB (Q= 8, T= 1): 3567.022 MB/s [ 3483.4 IOPS] < 2267.76 us>
Sequential 1 MiB (Q= 1, T= 1): 1117.216 MB/s [ 1091.0 IOPS] < 917.91 us>
Random 4 KiB (Q= 32, T= 1): 970.079 MB/s [ 242519.8 IOPS] < 131.70 us>
Random 4 KiB (Q= 1, T= 1): 54.364 MB/s [ 13591.2 IOPS] < 72.96 us>
[Write]
Sequential 1 MiB (Q= 8, T= 1): 3176.464 MB/s [ 3102.0 IOPS] < 2256.61 us>
Sequential 1 MiB (Q= 1, T= 1): 746.099 MB/s [ 728.6 IOPS] < 1115.53 us>
Random 4 KiB (Q= 32, T= 1): 925.396 MB/s [ 231349.2 IOPS] < 136.67 us>
Random 4 KiB (Q= 1, T= 1): 72.096 MB/s [ 18024.0 IOPS] < 52.80 us>
Profile: Default
Test: 1 GiB (x5) [Measure: 5 sec / Interval: 5 sec]
Date: 2024-11-18 10:29:32
OS: ubuntu 22.04 [linux 6.8.0-48-generic]
I've also tried copying the entire /var/lib/mysql (with 4 other similarly sized databases). Using cp, that took less than 5 seconds (I suspect there's some weird optimisation going on there). Using the file explorer, it took 33s.
- model: Dell XPS 15
- cpu: 13th Gen intel i7
- RAM: 32 GiB
- Storage: 1T nvme ssd
