Menu
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?
214
If you update a table, the system has to maintain those indexes that are on the columns being updated. So having a lot of indexes can speed up select statements, but slow down inserts, updates, and deletes.
The InnoDB Buffer Pool caches data and index pages. MyISAM only caches index pages. Just in this area alone, MyISAM does not waste time caching data. That's because it's not designed to cache data.
Another way to get fast updates is to delay updates and then do many updates in a row later. Performing multiple updates together is much quicker than doing one at a time if you lock the table. For a MyISAM table that uses dynamic row format, updating a row to a longer total length may split the row.
MyISAM will out-perform InnoDB on large tables that require vastly more read activity versus write activity. MyISAM's readabilities outshine InnoDB because locking the entire table is quicker than figuring out which rows are locked in the table.
It sounds like it's either
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?
164
InnoDB performance is heavily dependent on RAM. If the indexes don't fit in RAM, performance can drop considerably and quickly. Rebuild the whole table improves performance because the data and indexes are now optimized.
If you are only ever inserting into the table, MyISAM is better suited for that. You won't have locking issues if only appending, since the record is added to the end of the file. MyISAM will also allow you to use MERGE tables, which are really nice for taking parts of the data offline or archiving without having to do exports and/or deletes.
20
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
