MySQL slow importing on hyper-v virtual server compared to host

Question

Database dump file:

Server specs (Ubuntu 20.04, virtual (hyper-v)):

Host MySQL

Importing dump to the database on the host machine took 1-3 seconds, importing on the guest server 5-6 minutes.

iotop reports maximum write speed 5 MB/s while importing.

Refreshing database on phpmyadmin, i can see all row is imported after 1 minutes, the rest of the time is just altering table:

ALTER TABLE `table`
   MODIFY `ID` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

Import command:

mysql -u user -p db < /home/user/dump.sql

tried:

check the mysql error log of your new server, see if there are problems. Did you chekcn the my.cnf file if the caches tmp file and so on are similiar — nbk, Oct 1, 2020 at 13:07
Got the exact same problem, mysql import very slow on my dev machine after upgrade to 20.04. Some servers are fine after upgrade, and I compared versions & configs: they are EXACTLY the same!? Also, no errors in syslog or mysql log. — Simon Epskamp, Oct 6, 2020 at 8:41
@X11 my servers are dedicated (where it works fine), and my dev machine dedicted as well of course. — Simon Epskamp, Oct 6, 2020 at 13:44
Did you check to see if any indexes or histograms made it onto the new server? — Dave Stokes, Oct 6, 2020 at 14:44

score 1 · Accepted Answer · 2020-10-07 14:50:36Z

1

Turning on: "Turn off Windows write-cache buffer" solved the issue for me. (no restart required for me)

Benchmark write speed, before and after the setting.

dd if=/dev/zero of=/tmp/test2.img bs=512 count=1000 oflag=dsync

answered Oct 7, 2020 at 14:45

user216503

Thanks so much, your answer pointed me in the right direction. I'm posting a full answer below.
– Simon Epskamp
Oct 9, 2020 at 7:48
1

This is not solving the problem - it is hiding the problem behind another one. Did you bother READING the warning? You basically invite data corruption if power EVER fails.
– TomTom
Oct 9, 2020 at 9:18

Add a comment |

Simon Epskamp · Accepted Answer · 2020-10-09 07:54:53Z

0

Test SSD speed to see if that's the problem

Follow this guide:

dd if=/dev/zero of=/tmp/test2.img bs=512 count=1000 oflag=dsync

For me (Samsung evo 860 SSD) the results before the fix were around 80 kB/s.

Fix SSD speed if it's slow

Follow this guide. One of the fixes suggested is to trim all SSDs:

sudo fstrim -av

You will it trim the amount equal to the free space on your disk. Follow up by rebooting the system

After reboot, my writespeeds were up to 280 kB/s, and mysql import was at a normal speed again.

answered Oct 9, 2020 at 7:54

Simon Epskamp

1012 bronze badges

280 kB/s is too slow i think, the benchmark gives me 1.1 MB/s tested on 970 evo plus.
– user216503
Oct 9, 2020 at 8:29
It's a VM under windows.
– user216503
Oct 9, 2020 at 8:45
I think you're right, now my performance has dropped again. I'll have to investigate further, perhaps upgrade my ssd firmware.
– Simon Epskamp
Oct 9, 2020 at 8:47
@X11 after looking at some other results online, I think the perf is quite normal. The difference with you may be due to the VM / the windows settings you made. See: google.com/search?q=%22bs%3D512+count%3D1000+oflag%3Ddsync%22
– Simon Epskamp
Oct 9, 2020 at 9:17
YOu should not, never ever, use an EVO ssd in virtualization - they are VERY read oriented with a VERY pathetic endurance for writes. Which means you burn it out basically by patching VM's alone. They are meant for "very read centric" things - like the boot system of ONE OS and then storing games or other large files. This may be totally alone part of the problem.
– TomTom
Oct 9, 2020 at 9:17

| Show 3 more comments

2 Answers 2