3 minutes to import 327Mo database We do database import a lot of times per day on a server with those specs :


Xeon E5-1620v2 4c/8t @ 3,8Ghz
32 Go DDR3 ECC 1600MHz
Hard Drive   2x 3 To SATA3
MariaDB 10.14 with XtraDB


Given a 327Mb Database stored on the same server that will do the import, the import will take 3 minutes which is unusually long for this.

My my.cnf :

[mysqld]
character-set-server=latin1
innodb_file_per_table=1
max_allowed_packet=64M
skip-external-locking

innodb_buffer_pool_size=15G
innodb_log_file_size=2G
transaction-isolation=READ-COMMITTED
innodb_autoinc_lock_mode=2

max-connect-errors=100000
max-connections=500


I'm importing the database using the MySQL ansible import module

What could improve import performance without altering the backup database file
mysqldump, or .sql files, which is what that module uses is probably the least efficient way to import a database (the only less efficient way I can think of is to import and commit each row at once). If you want to speedup the import process, you should change the method.

There are several things that you can do in the MySQL configuration that will speed up the import, but you will be missing the most important one: parallel import. If you can import using the same file in 4-8 threads, you will not get a linear performance grow, but it will scale without problems. Split the file in different tables/partitions and you will get almost a linear speedup. With specialized tools like mydumper you can get 5-10x speedup. With raw backup tools you would probably get almost-infinite speedup.

Regarding configuration, which is probably the only thing you are interested in, you can set:


innodb_flush_log_at_trx_commit = 2. This is the most important suggestion. Makes the writes inconsistent with disk, but provides a great improvement if you are not using a hardware cache. It dependes on how slow is your disk, but can give you a 10x speedup. Not safe for normal operation, many people use it for unimportant data.
innodb_doublewrite = 0. Disables corruption checks. Very unsafe, and likely to corrupt your database in case of a crash. Can give you in your case around 2x speedup. Do not run MySQL never on this mode except for the import.
checksums = none (the syntax may have changed in 10). Disables page checksum. it wont't be very useful, as your CPU is not the bottleneck
performance_schema = off- not sure the default in Maria 10, probably can be disabled for the import without problems, but it won't give you a large speedup
innodb_buffer_pool_size and innodb_log_file_size they are already large on your setup -way larger than your data-, so no need to change in your case)


There are other things, but they require changing the file, like creating the secondary indexes afterwards.