# ANALYZE TABLE affecting queries

**URL:** <https://forums.percona.com/t/analyze-table-affecting-queries/10019>\
**Category:** Percona Distribution for MySQL\
**Created:** [April 8, 2021, 2:27pm UTC](https://forums.percona.com/t/analyze-table-affecting-queries/10019 "2021-04-08T14:27:10Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jiri\_Sula](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/jiri_sula/32/4770_2.png) [@Jiri\_Sula](https://forums.percona.com/u/Jiri_Sula)\
**Post date:** [April 8, 2021, 2:27pm UTC](https://forums.percona.com/t/analyze-table-affecting-queries/10019/1 "2021-04-08T14:27:11Z")

</div>

Dear Percona users,  
I have very trivial question, although resolution is beyond my knowledge.  
How to perform super-safely ANALYZE TABLE in a very busy environment?  
My experience = during this process few queries get stucked in a “executing” State until I kill them. And we can’t afford it.  
I have read tons of articles how ANALYZE TABLE is safe, not affecting anything… obviously it’ s not true and it affects running queries pretty badly. Tested on 8.0.20, 8.0.22.  
**Any tips highly appreciated!** (I’m writing script including pt-kill to check those snowflakes stucked queries).

---

<div class="post-metadata">

**Author:** ![Ivan\_Groenewold](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ivan_groenewold/32/6299_2.png) [@Ivan\_Groenewold](https://forums.percona.com/u/Ivan_Groenewold)\
**Post date:** [April 13, 2021, 2:50pm UTC](https://forums.percona.com/t/analyze-table-affecting-queries/10019/2 "2021-04-13T14:50:54Z")

</div>

Hi, that might not be possible to do without impact on a busy server. Analyze removes the table from the table definition cache which will cause a lot of contention if the table is heavily accessed. Safest thing would be to run it on a replica and promote to primary after.  
You might try checking if there are long queries in advance of the analyze and kill them preemptively, but still might experience contention.

---

<div class="post-metadata">

**Author:** ![vadimtk](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vadimtk/32/9528_2.png) [@vadimtk](https://forums.percona.com/u/vadimtk)\
**Post date:** [April 13, 2021, 4:48pm UTC](https://forums.percona.com/t/analyze-table-affecting-queries/10019/3 "2021-04-13T16:48:21Z")

</div>

I wonder what is the reason you need to run ANALYZE TABLE regularly ?

---

<div class="post-metadata">

**Author:** ![Jiri\_Sula](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/jiri_sula/32/4770_2.png) [@Jiri\_Sula](https://forums.percona.com/u/Jiri_Sula)\
**Post date:** [April 15, 2021, 9:21am UTC](https://forums.percona.com/t/analyze-table-affecting-queries/10019/4 "2021-04-15T09:21:39Z")

</div>

Hello @Ivan_Groenewold, thank you! This example was taken from one of our slaves. Those servers are very busy, as our business is 24/7/365 and between Percona servers and tons of customers is just (mem)cache layer. I will analyze an option to throw server(one-by-one) out of traffic and after scraping fresh statistics, allow traffic again. Probably one of the most clean solution.

Good day @vadimtk! Our workload is very specific and we’re unable to rely on automatic statistics(innodb\_stats\_auto\_recalc) In our case even 3-4% rows changes can lead to very bad execution plan. Also we are using high innodb\_stats\_persistent\_sample\_pages (400) because of sensitivity of few types of queries. This make process of taking fresh statistics pretty demanding (~3 minutes for our fastest servers) and that was a reason for periodic cron-job (in a night where traffic is a bit lower).