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