-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
Improve this question
3
  • looks like this command does the trick mysqlcheck --auto-repair -o --all-databases
    – FlyingZebra1
    Commented Nov 26, 2020 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
    Commented Nov 27, 2020 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).
    – FlyingZebra1
    Commented Nov 28, 2020 at 19:24

2 Answers 2

Reset to default
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.

Improve this answer
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.

Improve this answer
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?
    – FlyingZebra1
    Commented Nov 26, 2020 at 21:43
  • It’s in my answer
    – RolandoMySQLDBA
    Commented Nov 26, 2020 at 22:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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