Why is MySQL InnoDB insert so slow?
MysqlPerformanceInnodbMysql Problem Overview
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")
Mysql Solutions
Solution 1 - Mysql
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
Solution 2 - Mysql
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
Solution 3 - Mysql
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
Solution 4 - Mysql
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
Solution 5 - Mysql
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.
Solution 6 - Mysql
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 7 - Mysql
Solution
- Create new UNIQUE key that is identical to your current PRIMARY key
- Add new column
id
is unsigned integer, auto_increment - Create primary key on new
id
column
Bam, immediate 10x+ insert improvement.
Solution 8 - Mysql
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
Solution 9 - Mysql
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.