Importing a MySQL database is slow, but no visible bottleneck

Question

I'm importing a 20 GB database into my MySQL 5.7 server. The Dump was made on the same server. The OS is Ubuntu 16.04

The problem, is that it's running really slowly.

Here's my MySQL config:

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /nvme/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

innodb_buffer_pool_size = 18120M
innodb_lock_wait_timeout= 99999999
innodb_change_buffering=all
innodb_flush_log_at_trx_commit=0
innodb_log_file_size=1G
innodb_autoinc_lock_mode=2

#key_buffer     = 4048M
max_allowed_packet  = 1024M
thread_stack        = 256M
thread_cache_size       = 1024
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
query_cache_limit   = 4M
query_cache_size    = 512M

log_error = /var/log/mysql/error.log
expire_logs_days    = 10
max_binlog_size   = 1G

Here's the Mysqldump config from another file

[mysqldump]
quick
quote-names
max_allowed_packet  = 1024M

This is the command I use to create the dump

mysqldump --order-by-primary --opt --max-allowed-packet=64M dbName | gzip > dbDump.sql.gz"

And this one to import it

pv dbDump.sql.gz | { echo "set sql_log_bin=0;SET autocommit=0;SET unique_checks=0;SET foreign_key_checks=0;"; zcat; \
> echo "SET unique_checks=1;SET foreign_key_checks=1;SET autocommit=1;COMMIT;";} | mysql -uuser -ppwd awesomeDb

With autocommit=1 the import takes 48 minutes, with autocommit=0 it takes 53.

Now for the hardware.

I'm running an Intel 4690K on 3.5Ghz , 4 cores
32 gigs of RAM.
The Dump is located on a Samsung 850 SSD.
The Database has a dedicated m.2 NVMe SM961 128 drive. Write speeds in Crystalmark for randomized writes are ~200-300MBps.  

Resource usage by Mysqld (Reported by KSysGuard)

Ram is constantly on 20.5GB  
CPU is 15-19%, so it's not using a whole core. 
The NVMe drive is being written on at 10-40 MiBPS. (had the same speeds on the 850 250GB). It's performing 50-200 read accesses per second 

I tried all the solutions from the MySQL server docs.. Tried adding set sql_log_bin=0;SET autocommit=0;SET unique_checks=0;SET foreign_key_checks=0; before the dump. But the DB is still taking too long to import.

I know that flat out copying the files from the server is the fastest way. But my goal is to speed up the dump import.

And I have no idea where the bottleneck is. Because visibly - nothing is being maxed out.

Edit In:
The DB Schema is ~200 tables with 386 foreign keys (and 200 indices). No table has more than 10M rows, and the sizes of the biggest tables is 2.3,2.0,1.3,1.3,0.9 GBs.

Edit:
The query cache was always disabled during testing. Also, Today I imported a 12gb DB (same DB, just smaller). And it took 34 minutes. While the DB itself was almost 2x smaller.

Answer 1

Restoring a mysqldump file naturally uses only one thread, and imports data serially, one table after the other.

I'd suggest to use mysqldump --tab (with other options) which dumps each table into two files: one .sql file for schema definition and one text file for raw data.

You can then import the data files (after creating all the tables) with mysqlimport --use-threads=4 (with other options to address all the files) so you can load multiple tables concurrently.

This method also skips all use of SQL for the import, which reduces a lot of SQL-parsing overhead.

You can also try using mydumper & myloader, which are open-source community tools that support parallel operation for both dump and restore. I don't know if it's currently available as a pre-built binary, you may have to build it from source to get the most recent version.

The fastest restore solution is to make physical backups with Percona XtraBackup. Restoring doesn't require importing at all, you just put the files in place and start up the MySQL Server. But importing data to an existing MySQL Server is not possible. Restoring a physical backup requires shutting down the MySQL Server and overwriting any data that was already there.

Answer 2

query_cache_size    = 512M

is really bad. For every INSERT coming from the dump, the Query cache must be purged of any entries for the table in question. Sure, there aren't any, but the code is too dumb to realize it.

Either turn off the QC for the load, or lower the size to no more than 50M.

If you are using MyISAM, don't. Move to InnoDB.