Beware of running ANALYZE in Production

As you might know ANALYZE TABLE just quickly updates table statistics using index dives, unlike with MyISAM when it scans indexes holding table lock for long period of time.

So ANALYZE TABLE should be very fast and non intrusive operation doing just little update on the data. Right ?

Wrong! There is the bug or rather MySQL Design Feature which causes ANALYZE TABLE to block all accesses to this table while it could be flushed from the table cache.

What does this mean in practice ? If you have some long running query accessing Innodb table and you run ANALYZE TABLE you will be unable to access that table with “Waiting for table” lock until the first query completes.

For applications which run short transactions it may not be the big deal but if you mix long reporting together with real time update queries this can be the real issue.

This is generally the type of gotcha I hate the most. From the glance view there are no reasons Innodb can’t do this without locks but in practice some old mysql design artifacts result in this behavior and the bug can’t be fixed quickly.

Share this post

Comments (12)

  • Brian Aker

    How useful is Analyze with Innodb?

    September 2, 2008 at 4:55 pm
  • peter


    Analyze with Innodb is useful in two ways. First Innodb only updates stats when table is opened first time so if your MySQL server has very long uptime the statistics can become out of sync, just as with MyISAM. Second because Innodb does not have accurate stats it is possible for query plans to break after MySQL restart (because stats are way off compared to last start) and running Analyze allows to fix it. It is also possible for multiple slaves to have different stats and so plans which can be fixed same silly way – by running ANALYZE until plans are same.

    September 2, 2008 at 8:47 pm
  • Rob Wultsch

    Does ANALYZE on Innodb initialize the auto-increment counter?

    That page appears to say no, but it seem to me that something that would make sense to pull stats on when an ANALYZE is run. Any thoughts?

    September 3, 2008 at 12:41 am
  • Sheeri

    Peter, from my experience ANALYZE TABLE doesn’t do an exact count — at the very least, the stats are still approximate after an ANALYZE TABLE. That being said, it can be useful, but not as useful as some people might think.

    September 3, 2008 at 4:33 am
  • Pat

    When you get down to it, unless you have reason to believe your data changed “shape” sinificantly, there’s not usually an advantage to running stats in my experience (on innodb or any other db for that matter).

    Main thing the optimizers are looking for are cardinality values on your indexes and skewing. If neither of those changes appreciably, then neither will your plans and in the meantime you’ll have burned up some set of server resources (and a lock) gathering stats.

    September 3, 2008 at 8:32 am
  • peter


    Analyze does not really have anything to do with Auto Increment handling. Innodb may forget some values it gave out already after restart but this is design implication.

    September 3, 2008 at 10:16 am
  • peter


    Sure the usefulness of ANALYZE is often overestimated and I frequently catch people doing ANALYZE much more frequently when they need. Analyze also works differently for MyISAM and Innodb – for Innodb it is “10 random dives” so it is very approximate for MyISAM it is index scan so it is possible to have accurate data though as table can be changed the next second being 100% exact is not the goal for ANALYZE.

    September 3, 2008 at 10:22 am
  • peter


    Sure. You run ANALYZE if your data changed or may be after your loaded data with bunch of inserts (as there will be no cardinality info in the table in this case). MySQL does not use ANALYZE stats at all for many simple queries. MySQL also does not store any “skew” information which optimizer could use.

    September 3, 2008 at 10:24 am
  • Tobias

    Can you modify or delete the statistics after analyze table was running? (I only use Innodb.)
    Would be an appropriate method of taking influence on execution plan. (I’ve done several times in Oracle.)

    September 4, 2008 at 8:55 am
  • peter


    No there is no such command though being able to reset the stats would be helpful for some cases indeed.

    September 4, 2008 at 10:27 am
  • muzi

    is it ok to kill the analyze query when it is running state??? pls guide urgnt

    January 21, 2014 at 8:14 pm
  • Amit

    Hi Peter,

    Seems it is very old article ford mysql versions.
    We just tried analyzing the table which is getting used in delete statement from past 2 hours on mysql5.6 and it analyzed without waiting for lock or something else.

    November 20, 2015 at 5:39 pm

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.