Why is MySQL InnoDB insert so slow?

I am using large random numbers as keys (coming in from another system). Inserts and updates on fairly-small (as in a few million rows) tables are taking much longer than I think is reasonable.

I have distilled a very simple test to illustrate. In the test table I've tried to make it as simple as possible; my real code does not have such a simple layout and has relations and additional indices and such. However, a simpler setup shows equivalent performance.

Here are the results:

creating the MyISAM table took 0.000 seconds
creating 1024000 rows of test data took 1.243 seconds
inserting the test data took 6.335 seconds
selecting 1023742 rows of test data took 1.435 seconds
fetching 1023742 batches of test data took 0.037 seconds
dropping the table took 0.089 seconds
creating the InnoDB table took 0.276 seconds
creating 1024000 rows of test data took 1.165 seconds
inserting the test data took 3433.268 seconds
selecting 1023748 rows of test data took 4.220 seconds
fetching 1023748 batches of test data took 0.037 seconds
dropping the table took 0.288 seconds

Inserting 1M rows into MyISAM takes 6 seconds; into InnoDB takes 3433 seconds!

What am I doing wrong? What is misconfigured? (MySQL is a normal Ubuntu installation with defaults)

Here's the test code:

import sys, time, random
import MySQLdb as db

# usage: python script db_username db_password database_name

db = db.connect(host="127.0.0.1",port=3306,user=sys.argv[1],passwd=sys.argv[2],db=sys.argv[3]).cursor()

def test(engine):

    start = time.time() # fine for this purpose
    db.execute("""
CREATE TEMPORARY TABLE Testing123 (
k INTEGER PRIMARY KEY NOT NULL,
v VARCHAR(255) NOT NULL
) ENGINE=%s;"""%engine)
    duration = time.time()-start
    print "creating the %s table took %0.3f seconds"%(engine,duration)
    
    start = time.time()
    # 1 million rows in 100 chunks of 10K
    data = [[(str(random.getrandbits(48)) if a&1 else int(random.getrandbits(31))) for a in xrange(10*1024*2)] for b in xrange(100)]
    duration = time.time()-start
    print "creating %d rows of test data took %0.3f seconds"%(sum(len(rows)/2 for rows in data),duration)
    
    sql = "REPLACE INTO Testing123 (k,v) VALUES %s;"%("(%s,%s),"*(10*1024))[:-1]
    start = time.time()
    for rows in data:
        db.execute(sql,rows)
    duration = time.time()-start
    print "inserting the test data took %0.3f seconds"%duration
    
    # execute the query
    start = time.time()
    query = db.execute("SELECT k,v FROM Testing123;")
    duration = time.time()-start
    print "selecting %d rows of test data took %0.3f seconds"%(query,duration)
    
    # get the rows in chunks of 10K
    rows = 0
    start = time.time()
    while query:
        batch = min(query,10*1024)
        query -= batch
        rows += len(db.fetchmany(batch))
    duration = time.time()-start
    print "fetching %d batches of test data took %0.3f seconds"%(rows,duration)
    
    # drop the table
    start = time.time()
    db.execute("DROP TABLE Testing123;")
    duration = time.time()-start
    print "dropping the table took %0.3f seconds"%duration
    
    
test("MyISAM")
test("InnoDB")

InnoDB has transaction support, you're not using explicit transactions so innoDB has to do a commit after each statement ("performs a log flush to disk for every insert").

Execute this command before your loop:

START TRANSACTION

and this after you loop

COMMIT

InnoDB doesn't cope well with 'random' primary keys. Try a sequential key or auto-increment, and I believe you'll see better performance. Your 'real' key field could still be indexed, but for a bulk insert you might be better off dropping and recreating that index in one hit after the insert in complete. Would be interested to see your benchmarks for that!

Some related questions

• Slow INSERT into InnoDB table with random PRIMARY KEY column's value
• Why do MySQL InnoDB inserts / updates on large tables get very slow when there are a few indexes?
• InnoDB inserts very slow and slowing down

I've needed to do testing of an insert-heavy application in both MyISAM and InnoDB simultaneously. There was a single setting that resolved the speed issues I was having. Try setting the following:

innodb_flush_log_at_trx_commit = 2

Make sure you understand the risks by reading about the setting here.

Also see https://dba.stackexchange.com/questions/12611/is-it-safe-to-use-innodb-flush-log-at-trx-commit-2/12612 and https://dba.stackexchange.com/a/29974/9405

The default value for InnoDB is actually pretty bad. InnoDB is very RAM dependent, you might find better result if you tweak the settings. Here's a guide that I used InnoDB optimization basic

I get very different results on my system, but this is not using the defaults. You are likely bottlenecked on innodb-log-file-size, which is 5M by default. At innodb-log-file-size=100M I get results like this (all numbers are in seconds):

                             MyISAM     InnoDB
create table                  0.001      0.276
create 1024000 rows           2.441      2.228
insert test data             13.717     21.577
select 1023751 rows           2.958      2.394
fetch 1023751 batches         0.043      0.038
drop table                    0.132      0.305

Increasing the innodb-log-file-size will speed this up by a few seconds. Dropping the durability guarantees by setting innodb-flush-log-at-trx-commit=2 or 0 will improve the insert numbers somewhat as well.

What's your innodb buffer-pool size? Make sure you've set it to 75% of your RAM. Usually inserts are better when in primary key order for InnoDB. But with a big pool-size, you should see good speeds.

Solution

1. Create new UNIQUE key that is identical to your current PRIMARY key
2. Add new column id is unsigned integer, auto_increment
3. Create primary key on new id column

Bam, immediate 10x+ insert improvement.

things that speed up the inserts:

• i had removed all keys from a table before large insert into empty table
• then found i had a problem that the index did not fit in memory.
• also found i had sync_binlog=0 (should be 1) even if binlog is not used.
• also found i did not set innodb_buffer_pool_instances

This is an old topic but frequently searched. So long as you are aware of risks (as stated by @philip Koshy above) of losing committed transactions in the last one second or so, before massive updates, you may set these global parameters

innodb_flush_log_at_trx_commit=0
sync_binlog=0

then turn then back on (if so desired) after update is complete.

innodb_flush_log_at_trx_commit=1
sync_binlog=1

for full ACID compliance.

There is a huge difference in write/update performance when both of these are turned off and on. In my experience, other stuff discussed above makes some difference but only marginal.

One other thing that impacts update/insert greatly is full text index. In one case, a table with two text fields having full text index, inserting 2mil rows took 6 hours and the same took only 10 min after full text index was removed. More indexes, more time. So search indexes other than unique and primary key may be removed prior to massive inserts/updates.