Server stalled with "closing tables" state, 5.7.29

MySQL & MariaDB Percona Server for MySQL 5.7
1

Hello community!

Is there a chance anyone met similar symptoms?

We have standalone server (5.7.29) running for some time (weeks,months) smoothly. From time to time very innocent query ( UPDATE table_name SET col1 = 1.37, col2 = NOW(), col3 = ‘y’, col4 = ‘y’, col5 = NULL , col6 = NULL , col7 = NULL , col8 = 229767515 , col9 = ‘481x1x0x152.50’ WHERE id = 364416672 ) is still “running”, stalling whole server. Even backup process running 10 000 seconds after this query was not succeeded (stucked on flush tables with read lock → I’m expecting consequence of still running query).

There is no related deadlock(show engine innodb status), performance_schema.metadata_locks is empty, 98 records in performance_schema.table_handles having both internal and external locks column NULL for all records.

Any ideas are highly appreciated! Any trace I missed and I’m supposed to follow, anything… Thanks!

Jiri

2

Hello,

I assume id is a primary key on that table?

Does it always happen when backup is running or w/o it as well? If that’s only when backup is running, what exact backup command is it?

In general, without basic status outputs to look at, like show processlist and show engine innodb status, it’s hard to say what was really happening in your case.

3

Hello Przemek,

This stucking state appears randomly, we are unable to identify what is causing triggering it. So no connection with backup at all.

Yes, id is PK. We have avoid traffic into this server, so it’s still in that weird state = I’m posting some useful information in attachments.

processlist.txt

show_create_table.txt

show_engine_innodb_status.txt

show_global_variables.txt

.