1

Database dump file:

  • 6 MB size
  • 14k rows
  • 10 tables

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

  • OS: Ubuntu 20.04.1 LTS
  • MySQL version: Ver 8.0.21-0ubuntu0.20.04.4 for Linux on x86_64 ((Ubuntu))
  • 4 core, 2 GB RAM
  • 100 GB storage

Host MySQL

  • Windows server
  • MySQL version: 8.0.17 - MySQL Community Server - GPL
  • HDD Storage
  • 8 GB RAM

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:

  • mysql-server reinstall on guest machine
  • disable unique checks with UNIQUE_CHECKS=0;
  • disable foreign key checks with FOREIGN_KEY_CHECKS=0;
6
  • 1
    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. Oct 6, 2020 at 8:41
  • @SimonEpskamp Your server is dedicated or virtual?
    – user216503
    Oct 6, 2020 at 10:48
  • @X11 my servers are dedicated (where it works fine), and my dev machine dedicted as well of course. Oct 6, 2020 at 13:44
  • Did you check to see if any indexes or histograms made it onto the new server? Oct 6, 2020 at 14:44

2 Answers 2

1

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

Turn off Windows write-cache buffer

Benchmark write speed, before and after the setting.

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

Benchmark write speed, before and after the setting.

2
  • Thanks so much, your answer pointed me in the right direction. I'm posting a full answer below. 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
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.

8
  • 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. 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 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

Your Answer

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