1

I was trying to run analyze command on a table out of 900 tables in mysql 5.7.30. Its stuck my all db process-list and connections spike immediate and lot of commands found with state "Waiting for table flush" even our max_connection parameter reaches at 2500. We are running the analyze table command from last 3 years but from last 1 month we notice this issue 4th time. If we didn't analyze our tables then we see severe performance issues and lot of queries enter into state "statistics". Whats your thoughts on it

Irfi
  • 49
  • 2
  • 6
  • What do you gain from `ANALYZE`? It sound like `OPTIMIZE` table, which should _not_ be used on InnoDB. – Rick James Aug 10 '22 at 19:27
  • Tables don't have performance issues, queries do. Let's see one of the slow queries (and `SHOW CREATE TABLE`) there may be a simple way to speed it up. – Rick James Aug 10 '22 at 19:28
  • when we analyze table regularly then mostly our queries run perfectly. Its very common practice in DB optimization. – Irfi Aug 15 '22 at 16:58
  • Do they eventually stop running perfectly? (Other vendors may need this form of maintenance more than MySQL.) – Rick James Aug 16 '22 at 00:24

1 Answers1

1

You most definitely shouldn't be running ANALYZE regularly or automatically. It sounds like you were dodging the bullet of queries stuck in the waiting for able flush state purely because the load on your servers was sufficiently low that you didn't notice it before. You should only ever run this on a table sparingly when you have clear, definitive evidence that the index statistics on that table are sufficiently detached from reality to cause the query optimiser to regularly come up with egregiously poor execution plan.

Gordan Bobić
  • 1,748
  • 13
  • 16