0

I want to run ANALYZE TABLE and OPTIMIZE TABLE on a large production MySQL database (v 5.0) to increase performance.

But as this would lock tables causing downtime for an unknown amount of time, I want to test it on a separate instance first. I want to run some test queries on a test database, then optimize it, then run the test queries again.

Is it possible to copy a MySQL database while keeping the same un-optimized physical storage of table and index data?

Improve this question

2 Answers 2

Reset to default
1

No. Do not expect much, if any, performance boost with OPTIMIZE TABLE.

ANALYZE TABLE is slow on MyISAM, the engine you should be moving away from. It is fast in InnoDB. It is almost never needed in either engine. Only if you have a particular query that defies rational analysis by a human, should you consider ANALYZE.

OPTIMIZE TABLE will rebuild the table. That requires blocking all writes to it for the duration.

Back to your final question... Dumping the data (not the files) will provide you with something that can be loaded into another server for the testing you suggest. However, the dump, itself, involves some degree of locking. This depends on which engine you are using and whether you need a "consistent" dump.

SHOW TABLE STATUS will give you a clue of Data_free. For MyISAM, that is how much space can be recouped with OPTIMIZE. But even a large number there does not necessarily mean much performance boost. For InnoDB, Data_free has less meaning and its meaning depends on the value of innodb_file_per_table when the table was created.

Improve this answer
2
  • That surprises me, because on a similar project I had a job that took 30 minutes to run, and after I ran ANALYZE TABLE and OPTIMIZE TABLE the same job ran in 2 minutes.
    – Liam
    Commented Jan 15, 2016 at 16:53
  • 1
    One case in a thousand hits an edge case like that. Or the data was cached in RAM after the OPTIMIZE, so it ran faster. If you remember the details, I will try to figure out which it was.
    – Rick James
    Commented Jan 15, 2016 at 17:00
0

if your tables are innodb you can use https://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html which will optimize the table without blocking it.

It copies the table in chunks to aovid locking and sets trigger that update the copy table in all the changes that happen while optimizing.

You can run dry-runs to make sure it works before doing it live.

Improve this answer

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.