title: Importing a MySQL database is slow, but no visible bottleneck
body: <p>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</p>
  
  <p>The problem, is that it's running really slowly.</p>
  
  <p>Here's my MySQL config: </p>
  
  <pre><code>[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
  </code></pre>
  
  <p>Here's the Mysqldump config from another file</p>
  
  <pre><code>[mysqldump]
  quick
  quote-names
  max_allowed_packet  = 1024M
  </code></pre>
  
  <p>This is the command I use to create the dump</p>
  
  <pre><code>mysqldump --order-by-primary --opt --max-allowed-packet=64M dbName | gzip &gt; dbDump.sql.gz"
  </code></pre>
  
  <p>And this one to import it </p>
  
  <pre><code>pv dbDump.sql.gz | { echo "set sql_log_bin=0;SET autocommit=0;SET unique_checks=0;SET foreign_key_checks=0;"; zcat; \
  &gt; echo "SET unique_checks=1;SET foreign_key_checks=1;SET autocommit=1;COMMIT;";} | mysql -uuser -ppwd awesomeDb
  </code></pre>
  
  <p>With autocommit=1 the import takes 48 minutes, with autocommit=0 it takes 53.  </p>
  
  <p>Now for the hardware.  </p>
  
  <pre><code>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.  
  </code></pre>
  
  <p>Resource usage by Mysqld (Reported by KSysGuard)</p>
  
  <pre><code>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 
  </code></pre>
  
  <p>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. </p>
  
  <p>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.</p>
  
  <p>And I have no idea where the bottleneck is. Because visibly - nothing is being maxed out.</p>
  
  <p>Edit In:<br>
  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.  </p>
  
  <p>Edit:<br>
  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. </p>