-1

I have a bunch of tables (InnoDB) within several databases (MySQL 8), that I run Garbage Collect scripts on, on daily basis, removing stale records that are no longer needed. All tables do have indexes on them.

Would it make sense / be at all beneficial to optimize all tables, say every week, to increase performance? If so, is there are good command to run, to do so?

I tried doing

sudo mysqlcheck -o --all-databases

But get the following error message:

note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
3
  • looks like this command does the trick mysqlcheck --auto-repair -o --all-databases Nov 26 '20 at 23:01
  • How much does the "Garbage Collect" remove? Do you have a metric you are increasing? Is there some aspect of query performance that is poor unless you perform this mysqlcheck -o?
    – danblack
    Nov 27 '20 at 2:17
  • Hey Dan, GC rotates all of the data in the table, every week (ie - if there are 100k records, they are slowly deleted out, and new ones are written in). Nov 28 '20 at 19:24
1

Don't bother. InnoDB mostly takes care of itself. OPTIMIZE temporarily squeezes out some wasted space, but new waste occurs as inserts/etc happen. The performance benefit of OPTIMIZE is next to nil.

1

Those messages are normal for InnoDB. You can continue doing so.

The InnoDB Storage Engine performs OPTIMIZE TABLE mydb.mytable; like this

ALTER TABLE mydb.mytable ENGINE=InnoDB;
ANALYZE TABLE mydb.mytable;

I have mentioned this over the years:

I recommend doing analyze daily or weekly

sudo mysqlcheck --analyze --all-databases

Then, do the optimize once a month or once a quarter.

2
  • Thank you for the response. Is there a good way to ALTER TABLE / ANALYZE TABLE (InnoDB's equivalent of OPTIMIZE) with one command, for all tables in a database? Nov 26 '20 at 21:43
  • It’s in my answer Nov 26 '20 at 22:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.