Home / server / Questions / 405131
Accepted
garconcn
Asked: 2012-07-06 09:46:49 +0800 CST

Is it safe to repair and optimize mysql tables?

  • 772

I use mysqltuner to tune the mysql server. It recommends to "Run OPTIMIZE TABLE to defragment tables for better performance". I had done this on a single database before and I don't see any problem. Now, I have several client's databases on this server, I am hesitate to use optimize and repair on all databases. Is it safe to do this? Should I optimize their databases or ask them to do it? Thanks.

mysql

3 Answers

  1. Best Answer

    I'd rather leave it to customers if only for the reason OPTIMIZE TABLE locks the table for the duration of the operation.

    • 5

  2. I've never had a problem with it. I'd say it's about as safe as anything else in MySQL.

    • 2

  3. I'd definitely schedule this for a quiet/off-peak time, and do a database dump beforehand and immediately after, and compare if possible.

    of course, you might want to consider whether there's a reason the DB performance is dropping off and look at the mysql slow query log to see if a new table layout would prevent problems happening.

    • 0