0

I have a nightly cron job which deletes all rows of a particular table that are older than 30 days. This table sees a lot of churn and varies greatly in its size. After deleting those rows, I also call OPTIMIZE TABLE which in turn recreates the table since its an InnoDB table.

Occasionally, I run into an issue where the server runs out of disk space because it needs to make a copy of the data in order to recreate the table.

Would switching that particular table to MyISAM whilst keeping the rest of my database as InnoDB be more effective and prevent my server from running out of disk space? Or in other words, would OPTIMIZE TABLE act more as a trim on that particular table if it was in MyISAM instead of InnoDB?

jscarle
  • 1,045
  • 9
  • 17
  • 3
    A better method would be to use a partitioned table. Partition it by month, then remove the oldest partition every month and add a new one. – Barmar May 31 '21 at 21:36
  • 3
    Even if it does, it doesn't make using MyISAM a good idea. MyISAM doesn't support any of the properties of an ACID database. See [my answer to MyISAM versus InnoDB](https://stackoverflow.com/questions/20148/myisam-versus-innodb/17706717#17706717) – Bill Karwin May 31 '21 at 22:05

1 Answers1

2

Both MyISAM and InnoDB have the "flaw" that the disk space grows but does not shrink. Both reuse the freed space, so it is not that bad a situation.

MyISAM deletes leave holes in the table. These holes are chained together. Newly inserted rows will use these holes even if it means breaking a row into pieces. That leads to one of the few needs in MySQL for OPTIMIZE TABLE.

InnoDB, on the other hand, mostly takes care of the holes, thereby leading to (usually) no need for OPTIMIZE. I suggest you stop doing the OPTMIZE.

It is folly to allow a dataset to become so big that there is not enough disk space to ALTER the largest table. You seem to be in that position. A MyISAM table is very likely to take only half the disk space compared to InnoDB.

Instead of monthly deleting, try daily. That will let the table be 29 day's worth smaller at its peak. More tips on big deletes: http://mysql.rjweb.org/doc.php/deletebig

More on PARTITIONing, especially for a time series like yours: http://mysql.rjweb.org/doc.php/partitionmaint

If you would like to share the SHOW CREATE TABLE; we may have other tips on decreasing its disk footprint.

(I, too, do not recommend switching to MyISAM. It is not allowed on clustered systems, and it may be removed from MySQL in the near future.)

Rick James
  • 135,179
  • 13
  • 127
  • 222