title: Pandas to_sql() performance - why is it so slow?
body: <p>I am running into performance issues with Pandas and writing DataFrames to an SQL DB. In order to be as fast as possible I use <a href="http://www.memsql.com/" rel="noreferrer">memSQL</a> (it's like MySQL in code, so I don't have to do anything). I benchmarked my instance just now:</p>
  
  <pre><code>docker run --rm -it --link=memsql:memsql memsql/quickstart simple-benchmark
  Creating database simple_benchmark
  Warming up workload
  Launching 10 workers
  Workload will take approximately 30 seconds.
  Stopping workload
  42985000 rows inserted using 10 threads
  1432833.3 rows per second
  </code></pre>
  
  <p>That isn't glorious, and it's just my local laptop. I know... I am also using the root user, but it's a throw-away Docker container.</p>
  
  <p>Here is the code which writes my DataFrame to the DB:</p>
  
  <pre><code>    import MySQLdb
  
      import mysql.connector
      from sqlalchemy import create_engine
      from pandas.util.testing import test_parallel
  
      engine = create_engine('mysql+mysqlconnector://root@localhost:3306/netflow_test', echo=False)
      # max_allowed_packet = 1000M in mysql.conf
      # no effect
  
      # @test_parallel(num_threads=8)
      def commit_flows(netflow_df2):
          % time netflow_df2.to_sql(name='netflow_ids', con=engine, if_exists = 'append', index=False, chunksize=500)
      commit_flows(netflow_df2)
  </code></pre>
  
  <p>Below is the <code>%time</code> measurement of the function. </p>
  
  <p><a href="https://www.continuum.io/content/pandas-releasing-gil" rel="noreferrer">Multi-threading</a> does not make this faster. It remains within 7000 - 8000 rows/s. </p>
  
  <blockquote>
    <p>CPU times: user 2min 6s, sys: 1.69 s, total: 2min 8s Wall time: 2min
    18s</p>
  </blockquote>
  
  <p>Screenshot:
  <a href="https://i.stack.imgur.com/Atgdo.png" rel="noreferrer"><img src="https://i.stack.imgur.com/Atgdo.png" alt="memSQL shows the speed"></a></p>
  
  <p>I also increased the <code>max_allowed_packet</code> size to commit in bulk, with a larger chunk size. Still not faster. </p>
  
  <p>Here is the shape of the DataFrame:</p>
  
  <pre><code>netflow_df2.shape
  (1015391, 20)
  </code></pre>
  
  <p>Does anyone know how I can make this faster?</p>