What Slows Down an InnoDB Database That Is Fixed by Re-Importing It?

innodbMySQLmysql-5.6performancequery-performance

I recently noted a performance degradation on a MySQL 5.6 (InnoDB) database with several million records. This particular query was taking around 20 seconds on the first run (apparently data was loaded from disk) and 4 seconds on subsequent runs (apparently data was loaded from memory):

SELECT
COUNT(DISTINCT `t3`.`node_id`) AS '__value'
FROM `hd2_ont_class_url` AS `t3`
INNER JOIN `hd2_ont_link` AS `t4` ON ((`t4`.`passive`=`t3`.`node_id`) AND (`t4`.`linktype`='tagset'))
INNER JOIN `hd2_ont_rel` AS `t5` ON ((`t5`.`passive`=`t4`.`active`) AND (`t5`.`active`='28530') AND (`t5`.`linktype`='tagup'))
WHERE
(`t3`.`deleted` = 0) AND
(MATCH (`t3`.`title`) AGAINST ('+php*' IN BOOLEAN MODE));

Same query on the same database in another environment was taking only around 0.02 seconds, so I started investigating where the hold up was coming from. Tried different versions, configs — all the same. The EXPLAIN output was also identical everywhere.

In the end of it I just dumped and re-imported back the original database. And that was it! Now the query is taking no more than 0.05 seconds!

So here are the questions:

  1. What caused the performance degradation? How to prevent it?
  2. Is there some sort of maintenance procedure that would fix the issue like the re-import did?

Hint: the records in the database are rotated often — every day about 20% of them are deleted and new ones are created instead.

Best Answer

  1. Performance degradation was most likely caused by your table getting more and more fragmented by inserting and deleting many rows every day.

  2. Dumping your table, dropping it, and reloading after many deletes will compact the space on disk and reduce the size of the .ibd file, assuming that it uses its own tablespace.

You could accomplish the same thing with a null alter, like this:

ALTER TABLE sometable FORCE;

which will rebuild the table.

Related Question