when do i usually optimize an innodb table?

Question

I have a table that's always used 24/7. There is always a stream of update and/or insert of 10-20 rows per 30 to 60 seconds.

As stated on the MYSQL documentation: "After doing substantial insert, update, or delete operations on an InnoDB table that has its own .ibd file because it was created with the innodb_file_per_table option enabled. The table and indexes are reorganized, and disk space can be reclaimed for use by the operating system."

I am not sure if what im doing with the table is "substantial" enough for me to optimize it regulary.

Also, what is the impact on performance with doing optimize table regulary?

Answer 1

In almost all situations, OPTIMIZE TABLE is a waste of time and effort. So, in my opinion, "substantial" is really, really, big.

10-20 changes per minute -- not very busy. Not substantial.