Debugging slow MySQL and MariaDB queries

posted on 2023-02-07

Execute this on the database server to start logging all queries that take longer than 5 seconds to run:

SET GLOBAL log_output='TABLE';
SET GLOBAL slow_query_log=1;
SET GLOBAL long_query_time=5.0;

SET GLOBAL log_output='TABLE';
SET GLOBAL slow_query_log=1;
SET GLOBAL long_query_time=5.0;

Wait a bit and take a look at slow_log table in mysql database. Any slow queries will end up there:

To turn off query log in case you will execute some heavy queries manually, execute this:

SET GLOBAL slow_query_log=0;

SET GLOBAL slow_query_log=0;

To disable slow query log for a single connection, execute this:

SET SESSION slow_query_log=0;

SET SESSION slow_query_log=0;

More info about Slow Query Log on https://mariadb.com/kb/en/slow-query-log-overview

Need more help with slow systems? Hire me!

In case you need further help debugging and optimizing slow code or databases, I have experience on many different scenarios.
Please e-mail me about consultancy on the matter at brunocassol@gmail.com