mysqldatabaseinnodbdatabase-optimization

How to optimize an "optimize" MYSQL query that takes a lot of time

I have a table (innodb) with 1 million new inserts (20GB) a week. I only need the data for 1 week, so I delete it after 7 days, so each day we delete around 3GB and insert 3GB new. That table is already in a separate database from the rest.

The problem is that disk space is only freed after an optimize query, so we run it every few weeks at night. It works, but it takes 30 minutes and freezes the whole database server that time, not just the particular database.

Is there any way to opimize faster?

If we run an optimize everytime we delete the data, will it be faster than running the optimize every few weeks instead? I thought it might be faster to run it when just 3GB of deleted rows need to be removed from disk, if we run it after 20 days it's 60GB. Is that right? and is there another way to optmize the optimmize?

Solution

  • Instead of worrying about speeding up OPTIMIZE TABLE, let's get rid of the need for it.

    PARTITION BY RANGE(TO_DAYS(...)) ...

    Then DROP PARTITION nightly; this is much faster than using DELETE, and avoids the need for OPTIMIZE.

    Be sure to have innodb_file_per_table=ON.

    Also nightly, use REORGANIZE PARTITION to turn the future partition into tomorrow's partition and a new, empty, partition.

    Details here: http://mysql.rjweb.org/doc.php/partitionmaint

    Note that each PARTITION is effectively a separate table so DROP PARTITION is effectively a drop table.

    There should be 10 partitions: