MySQL Performance – Slow Importing on Hyper-V Virtual Server Compared to Host

MySQLmysql-8.0Ubuntu

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;

Best Answer

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.

Related Question