ANALYZE TABLE affecting queries

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).

1 Like

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.

1 Like

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

1 Like