title: MySQL restore performance
body: <p>I have what seems to be a slowing MySQL restore, and am looking for some tuning advice (I am a PostgreSQL and SQL Server guy).</p>
  
  <p>The dev server has 48GB of RAM, 8 cores, running Centos 6.2 64-bit and MySQL 5.1.61 (same as production MySQL), and 4 x 7200 RPM SAS drives in software managed RAID-10 / XFS. The only MySQL client process is the restore. The dump was taken with a plain mysqldump of all databases on the production server.</p>
  
  <p>I have applied some of the options from <a href="http://derwiki.tumblr.com/post/24490758395/loading-half-a-billion-rows-into-mysql" rel="nofollow">http://derwiki.tumblr.com/post/24490758395/loading-half-a-billion-rows-into-mysql</a>, including setting FOREIGN_KEY_CHECKS and UNIQUE_CHECKS to zero. I have included my.cnf below.</p>
  
  <p>Monitoring the restore with mytop and pv (pv backup.sql | mysql -u root -p), it appears that the INSERT INTO statements begin to progressively get slower. qps shown by mytop starts at 3, and drops to 0 at 60% through the dump file. Not sure how accurate mytop is in this case, as 3 inserts (with values) still seems slow. htop shows &lt; 10% CPU utilization on the CPU used by MySQL, and less than 8GB of the 48GB of RAM is being utilized.</p>
  
  <p>Different databases, but similar restore techniques, run about 5-10x faster on the same server using PostgreSQL.</p>
  
  <p>Ideas?</p>
  
  <pre><code>[mysqld]
  # my.cnf
  socket=/var/lib/mysql/mysql.sock
  user=mysql
  symbolic-links=0
  slow-query-log
  long_query_time = 60
  log-slow-admin-statements
  slow_query_log_file = /var/log/mysql_slow.log
  innodb_buffer_pool_size = 2G
  max_allowed_packet = 1G
  key_buffer_size = 1G
  concurrent_insert = 1
  innodb_flush_log_at_trx_commit = 2
  bulk_insert_buffer_size = 1G
  innodb_flush_method = O_DIRECT
  </code></pre>