Enabling the Slow Query Log
There are 2 different methods:
- The easiest is to connect to MariaDB using a client tool. Then execute this SQL statement:
SET GLOBAL slow_query_log=1;
The only disadvantage is this most-likely would not persist if you restarted the MariaDB service (or restarted your machine)
- You can edit your MariaDB
my.cnffile. Find the section block `[mariadb]. And add this:
[mariadb]
...
slow_query_log
Note: After making any edits to my.cnf, you need to restart the MariaDB service.
Finding the Log File
By default, the log file is located in the datadir directory of MariaDB. Often this directory is /var/lib/mysql. But not always!
To know for certain your datadir, execute the following SQL statement:
SELECT @@datadir;
Once you know the directory name, the Slow Query Log file name is this:
../datadir/${hostname}-slow.log
There are many different ways of changing the default log name. You can even change it, so instead of writing to a file, it writes the log to SQL tables instead. However, given you have a real-world problem you need to track down, using the defaults may be good enough.
You can examine the Slow Query Log with your preferred text editor.
Alternately, MariaDB provides a CLI tool specifically designed for parsing the Log data: mysqldumpslow
Happy query-hunting!