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.
Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.
Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.