2

Recently my database started, several times a day, to get stuck with a lot of queries in a Waiting for table flush state. Usually there's a very long query in execution that when killed unlock the other queries. We don't execute automatically ANALYZE, OPTIMIZE, RENAME, ALTER, REPAIR and FLUSH commands that could cause a flush.

The tables are all InnoDB and the version of MySQL is 5.7.39-1.

There are hundreds of client connected all of the time (372 at the time I'm writing this question).

The database is managed, so I don't have the PROCESS privilege required to execute the query

SELECT BLOCKING_TRX_ID FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

that could be used to further debug the issue.

Can you help me to debug this issue?

Improve this question
5
  • 1
    "Managed"? That probably includes periodic backups. What is the schedule for such? If that does not lead to an answer, get them do provide PROCESSLIST and SHOW ENGINE=INNODB STATUS.
    – Rick James
    Commented Sep 19, 2022 at 14:16
  • @RickJames The backups are executed daily in the morning, and to my knowlege should require under a day to execute, and the queries get stuck even in the evening. I'll try to get confirmations of this from our hosting company, thanks.
    – Dege
    Commented Sep 19, 2022 at 14:57
  • If your database is "managed", then whoever "manages" it should take care of the problem. If they won't, you should stop paying for such "service" and either find a properly managed DBaaS, or manage it yourself.
    – mustaccio
    Commented Sep 19, 2022 at 22:01
  • I agree with @mustaccio. Processlist is required for further analysis.
    – JYOTI RAJAI
    Commented Sep 20, 2022 at 9:44
  • 1
    Possibly related feature request: bugs.mysql.com/bug.php?id=87065 I know you said you don't run ANALYZE TABLE, but this could be a case when similar work is being done automatically by periodic statistics refresh.
    – Bill Karwin
    Commented Sep 21, 2022 at 7:08

1 Answer 1

Reset to default
2

After further debugging the issue with the provider, as suggested by @RickJames , I found out that they executed an hourly cron that analyzed every table and was the cause of the flushes.

I wasn't able to view this queries in the PROCESSLIST because they were executed with an administrative account.

Thanks all for the help.

Improve this answer
1
  • 1
    It sounds like you need to switch to a competent managed database service provider. :-( Your experience is, unfortunately, far from unique.
    – Gordan Bobić
    Commented Sep 24, 2022 at 9:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.