Log to disk all queries that waited for a lock over a period of time I manage a mariadb 10.4.17 server, and I'm having the issues with a table, it seems randomly I will get lots of queries &quot;waiting for metadata lock&quot; causing my entire database to be useless.
I usually have to restart my server to get the database running again.
I'm looking for methods that can consistently log to disk all the queries who can use problems, without affecting performance too much.
I like something like this

watch -n 0.5 'mysqladmin -u root -ppassword &quot;processlist&quot;' &gt; log.txt

But I don't know how to order by state. Anyway I'm open to any ideas. Looking for something I can look at to see what happened in the past because when the issue happens I just want to restart the db to get back online asap and don't have time to dig the root of the issue
You can do this by setting long_query_time as follows:
If you want it done permanently set it in mysql.cnf in the [server] section.
long_query_time=123.123 (down to millisecond precision)
From here - you can also set it dynamically.
Issue the command (from the mysql CLI (Command Line Interface) client):
SHOW VARIABLES LIKE '%long%' 

or similar and set it. You can find out and/or set the destination file by issuing;
SHOW VARIABLES LIKE '%slow%'

Check out the db&lt;&gt;fiddle here!