3

I'm wondering if running the OPTIMIZE command via cronjob would be an intelligent move? Sometimes I forget to run optimize for months at a time.

My database is frequently using select, insert, update. It has about 45,000,000 rows.

Select uses the dateChecked column and update uses the URL column.

enter image description here

enter image description here

  • Which is the engine of that table: InnoDB or MyISAM? And which mysql version? – Nuno Pereira Feb 16 '16 at 0:51
3

The compete and correct answer depends on which Mysql version you have and on which engine is the table.

Fast answer: no.

MyISAM

Short answer Only if you can have some downtime and want to take some risks (see below).

Long answer During the optimize, the table becomes I inaccessible, and if you need to use that table (selects, inserts, etc), then it's not a good idea to perform an scheduled downtime.

InnoDB

Short answer If you have mysql 5.6.17 or above, than you may do it without downtime, but I wouldn't recommend it.

Long answer Since mysql 5.6.17 that InnoDB can perform an optimize table with Online DDL, which means that selects, and even inserts, deletes and updates can be performed on the table while the optimize is running, if that is your case. With that you can schedule an optimize on the table knowing that your system continues to run.

But it may not be safe to do it like that, as things may go wrong, and you're not with your eyes on the database to fix it ASAP. For example, you may not have free disk space for the optimize to run.

For 5.6.16 or lower, applies what I've said about myISAM.

Conclusion

Optimize table may lock your database users from accessing the database, if your mysql version doesn't support online ddl. And even if it supports, performing an operation like that automatically needs to be done with extreme care.

Maybe create a cron that sends you an email to remember to do the optimize?

  • 1
    For InnoDB the answer should be don't bother, it's not gonna help anyway. – jkavalik Feb 16 '16 at 6:36
  • What did you do to get that much "Overhead"? Delete a lot of rows? Or is this a PARTITIONed table, and the number won't go away with OPTIMIZE? What tool shows that "Overhead"? – Rick James Feb 17 '16 at 6:29
  • @RickJames, better you ask to the user "User", who made the question. – Nuno Pereira Feb 17 '16 at 18:10
  • But why do you say that, @jkavalik? – Nuno Pereira Feb 17 '16 at 18:44
  • 1
    InnoDB usually takes care of keeping its BTrees reasonably balanced, hence OPTIMIZE rarely does anything useful for InnoDB tables. Search this forum for [mysql] [optimize] -- more details are given in many answers. – Rick James Feb 17 '16 at 19:22
0

The statements about:

no need for InnoDB

... in previous comments is misleading.

I had a database (MySQL 5.7) with more than 12'000 tables and 150 GB disk usage. The server was running out of space. Running a global OPTIMIZE (with mysqlcheck -o --all-databases) reclaimed 27 GB (!) of free space.

  • Welcome to DBA.SE and thanks for your contribution. If you could support your observation with some form of documentation, then this would greatly increase the acceptance of your answer. Otherwise your statement is just an observation which might be based on unknown factors. Also feel free to roll back the edit I conducted to increase the readability of your answer. Good luck with your journey on DBA.SE. It's a fantastic place to be once you have managed to grasp how it works. – hot2use Sep 11 at 13:12

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.