performance - Why do MySQL InnoDB inserts / updates on large tables get very slow when there are a few indexes?

Question

We have a series of tables that have grown organically to several million rows, in production doing an insert or update can take up to two seconds. However if I dump the table and recreate it from the dump queries are lightning fast.

We have rebuilt one of the tables by creating a copy rebuilding the indexes and then doing a rename switch and copying over any new rows, this worked because that table is only ever appended to. Doing this made the inserts and updates lightning quick.

My questions:

Why do inserts get slow over time? Why does recreating the table and doing an import fix this? Is there any way that I can rebuild indexes without locking a table for updates?

Answer 1

It sounds like it's either

• Index unbalancing over time
• Disk fragmentation
• Internal innodb datafile(s) fragmentation

You could try analyze table foo which doesn't take locks, just a few index dives and takes a few seconds.

If this doesn't fix it, you can use

mysql> SET PROFILING=1;
mysql> INSERT INTO foo ($testdata);
mysql> show profile for QUERY 1;

and you should see where most of the time is spent.

Apparently innodb performs better when inserts are done in PK order, is this your case?