Pandas to_sql() performance - why is it so slow?
<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="nofollow">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="nofollow">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="http://i.stack.imgur.com/Atgdo.png" rel="nofollow"><img src="http://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>

<p>In case someones gets a similar situation:</p>

<p>I removed SQlalchemy and used the (deprecated) MySQL flavor for Pandas' <code>to_sql()</code> function. The speedup is more than 120 %. I don't recommend to use this, but it works for me at the moment.</p>

<pre><code>import MySQLdb

import mysql.connector
from sqlalchemy import create_engine
from pandas.util.testing import test_parallel

engine = MySQLdb.connect("127.0.0.1","root","","netflow_test")

# engine = create_engine('mysql+mysqlconnector://root@localhost:3306/netflow_test', echo=False)

# @test_parallel(num_threads=8)
def commit_flows(netflow_df2):
    % time netflow_df2.to_sql(name='netflow_ids', flavor='mysql', con=engine, if_exists = 'append', index=False, chunksize=50000)
commit_flows(netflow_df2)
</code></pre>

<p>If I find out how to convince memSQL to accept a large query (similar to MySQL's <code>max_allowed_packet = 1000M</code> in mysql.conf) I will be even faster. I should be able to hit more than 50000 rows per second here.</p>

<pre><code>CPU times: user 28.7 s, sys: 797 ms, total: 29.5 s
Wall time: 38.2 s
</code></pre>

<p>126s before. 38.2s now.</p>

python performance pandas memsql