title: Optimising MySQL for parallel Import of massive data files. 1 Connection Per Table
body: <p>I'm doing some preparatory work for a large website migration. </p>
  
  <p>The database is around the 10GB in size and several tables contain > 15 Million records. Unfortunately, this only comes in a large single mysqldump file in SQL format due to client relations outside my remit, but you know how that goes. My goal is to minimize downtime and hence import the data as fast as possible.</p>
  
  <p>I have attempted to use the standard MySQL CLI interface like so:</p>
  
  <pre><code>$mysql database_name &lt; superhuge_sql_file -u username -p
  </code></pre>
  
  <p>This is however, super slow. </p>
  
  <p>To try and speed things up I've used awk to split the file in to chunks for each table with associated data, and have built a little shell script to try and import the tables in parallel, like so;</p>
  
  <pre><code>#!/bin/sh
  
  awk '/DROP TABLE/{f=0 ;n++; print &gt;(file="out_" n); close("out_" n-1)} f{ print &gt; file}; /DROP TABLE/{f=1}'  superhuge.sql
  
  for (( i = 1; i &lt;= 95; i++ )) 
  do
      mysql -u admin --password=thepassword database_name &lt; /path/to/out_$i &amp;
  done
  </code></pre>
  
  <p>It's worth mentioning that this is a "use once and destroy" script (passwords in scripts etc...).</p>
  
  <p>Now, this works, but still takes over 3 hours to complete on a quad core server doing nothing else at present. The tables do import in parallel but not all of them at once, and trying to get MySQL server information through the CLI is very slow during the process. I'm not sure why but trying to access tables using the same mysql user account hangs while this is in process. max_user_connections is unlimited.</p>
  
  <p>I have set max connections to 500 in my.cnf but have otherwise not configured MySQL on this server.</p>
  
  <p>I've had a good hunt around but was wondering if there are any MySQL config options that will help speed this process up, or any other methods I have missed that will be quicker.</p>