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.
How useful is Analyze with Innodb?
Brian,
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.
Does ANALYZE on Innodb initialize the auto-increment counter?
http://dev.mysql.com/doc/refman/5.0/en/innodb-auto-increment-handling.html
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?
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.
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.
Rob,
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.
Sheeri,
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.
Pat,
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.
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.)
Tobias,
No there is no such command though being able to reset the stats would be helpful for some cases indeed.
is it ok to kill the analyze query when it is running state??? pls guide urgnt
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.