Slow MySQL Inserts from Python

Question

I'm trying to insert some data into a MySQL database using python (pymysql connector) and I'm getting really poor performance (around 10 rows inserted per second). The table is InnoDB, and I'm using a multiple values insert statement and have ensured that autocommit is turned off. Any ideas why my inserts are still so slow?

I initially thought that autocommit wasn't properly being disabled but I've added code to test that it is disabled (=0) during each connection.

Here my example code:

for i in range(1,500):
    params.append([i,i,i,i,i,i])
insertDB(params)

def insertDB(params):
    query = """INSERT INTO test (o_country_id, i_country_id,c_id,period_id,volume,date_created,date_updated)
        VALUES (%s,%s,%s,%s,%s,NOW(),NOW())
        ON DUPLICATE KEY UPDATE trade_volume = %s, date_updated = NOW();"""
    db.insert_many(query,params)

def insert_many(query,params=None):
    cur = _connection.cursor()
    try:
        _connection.autocommit(False)
        cur.executemany(query,params)
        _connection.commit()
    except pymysql.Error, e:
        print ("MySQL error %d: %s" %
            (e.args[0], e.args[1]))
    cur.close()

What else could be the issue? The above example takes an eternity of about 110 seconds to execute.

Answer 1

Not sure what is wrong, but I would try the mysqldb and/or the mysql connector modules instead and see if I get the same performance numbers.