0

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 "waiting for metadata lock" 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 "processlist"' > 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

1
  • I've added a db<>fiddle to my answer !
    – Vérace
    Commented Jan 17, 2021 at 11:46

1 Answer 1

0

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<>fiddle here!

5
  • How is this going to log to my disk to help track the issue AFTER it happened and I restarted the db?
    – Freedo
    Commented Jan 17, 2021 at 10:52
  • In a word, (well, two words), it can't! Sorry, I missed the past bit of the question - that's like asking how do I close the stable door after the horse had bolted (and have the horse still inside). This is why monitoring tools are there - so you can monitor for the rainy day when problems occur! Backing up, monitoring and logging are the boring, mundane tasks that people avoid but shouldn't. Just start logging and monitoring from now. What OS are you using?
    – Vérace
    Commented Jan 17, 2021 at 11:01
  • I can't sit and watch the db 24/7 and when the issue happens, I Just want to restart the db so i'm back online asap and not spend time debugging stuff..
    – Freedo
    Commented Jan 17, 2021 at 11:22
  • that's why I need historical data, logged on disk
    – Freedo
    Commented Jan 17, 2021 at 11:22
  • Well then, for starters, enable the slow query log (or whatever it's called) - if your OS is Linux or similar - then use the Sysstat suite to monitor your entire system (can't praise this tool highly enough!) and then compare normal running with when your system fails. Check out time of day, day of week and of month (don't know what your system does) and then NEXT time, you'll be able to perform diagnostics - but you can't look at the past without having recorded (i.e. monitored and logged) it in some way!
    – Vérace
    Commented Jan 17, 2021 at 11:29

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.