-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
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
FlyingZebra1
  • 113
  • 6
  • looks like this command does the trick mysqlcheck --auto-repair -o --all-databases – FlyingZebra1 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 02: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 Nov 28 '20 at 19:24

2 Answers2

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.

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
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.

Rick James
  • 78,038
  • 5
  • 47
  • 113