4

So we have an old database that was fragmented until we ran OPTIMIZE on all tables.

The database/tables are about 100GB in size. Tables are InnoDB with heavy read/write activity.

There are so many different queries we decided to disable query_cache due to over 1 million+ queries being pruned daily, even with 512MB cache size (with 4mb min).

Would ANALYZE actually improve performance?

2 Answers 2

1

There are a few situations where running ANALYZE TABLE is beneficial

  1. When you have innodb_stats_auto_update=0 set
  2. When your statistics are already "messed up" and need a fixin'

If your table statistics frequently get into a "messed up" state (which would cause bad or strange query plans), as can be the case when a table has nightly purge or bulk insert jobs, you should consider setting innodb_stats_auto_update=0 and running a periodic manual ANALYZE TABLE. Otherwise, let sleeping dogs lay and don't run it.

One caveat about ANALYZE TABLE is that it can cause problems if you have long running queries against the table because it flushes the table. This means that, if you run ANALYZE TABLE while a query is running, no other tables will be able to access the table until the first query finishes. I wrote ab-analyze-innodb to help address this problem by searching the process list to identify whether or not the table is actively being accessed.

1
  • It seems on MySQL 5.6 the option is called innodb_stats_auto_recalc. Jul 29, 2016 at 12:51
0

Bottom line: Don't bother running ANALYZE on InnoDB tables.

Sure, sometimes ANALYZE will improve the "statistics". But sometimes it will make things worse. Hence, a "regular" ANALYZE is probably a waste.

Rolando talks about the "random dives", which is the root of the statistics going bad. ANALYZE simply does the dives.

Percona's Xtradb and Oracles 5.6 have some extra tunables -- none of them really 'solve' the problem of statistics being sometimes bad.

All that said -- When a performance problem is identified, it is only 1 time in 1000 that ANALYZE is the main 'fix'.

MyISAM is a different matter. A table with a lot of churn should probably be OPTIMIZEd monthly. That's more for cleaning up the fragmentation, and secondarily for doing the ANALYZE. (This applies to maybe 1 table in 10,000.)

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.