How do I enable the MySQL slow query log?

Step-by-step guide: Cracking open the slow query shell

The objective here is to shed light on how to unravel the mystery of slow_query_log within MySQL. This guide takes on a detailed approach in defining the steps required to enable and manage this feature within your MySQL database.

Embrace your MySQL

Recognise your MySQL version, as the behavior of the slow query log varies:

• For MySQL 5.1.6 and later, settings apply at runtime.
• For versions before 5.1.6, settings will be defined within the my.cnf or my.ini configuration file.

Rewind back to MySQL < 5.1.6 (my.cnf)

If you are operating on an earlier version of MySQL, you'll need to incorporate the slow query log in the configuration file in following way:

[mysqld]
log-slow-queries = /var/log/mysql/slow-query.log
long_query_time = 2
log_queries_not_using_indexes = 1


Restart your MySQL service after editing my.cnf file for changes to apply.

sudo systemctl restart mysql


With this, you're like a time traveler changing history (or at least your MySQL version's history)!

Push play for MySQL ≥ 5.1.6 (runtime)

But if you're operating on MySQL 5.1.6 or a later version, you can make changes on the fly! Below command lines will perform the magic within MySQL shell:

SET GLOBAL slow_query_log = 'ON'; -- I said "ON", not "NO"! Do not mistake my words.
SET GLOBAL slow_query_log_file = '/REPLACEMENT_PATH/slow-query.log'; -- I'm a pathfinder, aiding you to locate the log.
SET GLOBAL long_query_time = 2; -- I'm not "too" slow, I'm just "two"! Get it?


Mysterious terms unveiled

Let's decipher the code now:

• slow_query_log: Switches the logging feature ON (1/ON) or OFF (0/OFF).
• slow_query_log_file: Marks the path to the log file. Making a map to the treasure!
• long_query_time: Determines the magic number in seconds that a query must take to consider it being slow.

Keep the changes intact

Runtime changes are temporary, much like sand sculptures! To make them hold, add them to your my.cnf file.

Validation: Mirror, mirror on the wall...

Finally, always verify that the changes are reflected accurately. Execute the following SQL query to see all global variables related to 'slow':

SHOW VARIABLES LIKE '%slow%'; -- Looking for the slowpokes... I mean, slow queries.


Jedi tricks with slow query log

Decode the log

Over time, the log file will serve as a chest filled with slow queries, which you can analyze using tools like mysqldumpslow or pt-query-digest. It's like the golden snitch in Quidditch, hard to catch but worth it for performance.

Adjust to your advantage

Not all slow queries avoid indexes, so to capture those detective queries, turn on log_queries_not_using_indexes.

Watch your step (or size)

Keep an eye on the log file size. It can grow faster than you can say "MySQL", hogging up disk space and slowing down performance.

No harm, no foul

Careful when using the slow query log on a production server because, like an overzealous fan blowing a vuvuzela during a golf swing, it can create performance issues. Always best to test it on a development or staging server first.