We noticed load on one of our production servers recently and had to enable slow query log for our MariaDB server. Since this was a server in production, we feared that if we tried restarting the MariaDB server that it won’t come back online or might take longer than acceptable. Diagnosing with the help of the slow query log can also help speed up your website.
So we went in and enabled the slow query log without restarting the server, this is how we did it.
Start by logging into your server via SSH and make sure the folder for the log file is created, if not then do this:
Next, log into your MySQL server as root or a user with superuser privileges:
Set the log file location:
Set the slow query duration, how long should a query run to be logged. For our purposes we’re setting it at 2 seconds, so a query which takes longer than 2 seconds to process will be logged
Next, we enable our new settings
Head over to /var/log/mysql and if you should see queries in the log file taking longer than 2 seconds to complete.
These settings will not survive a restart, to make them permanent edit the mysql config file.
On our server, it was at /etc/my.cnf.d/server.conf, locate and add the following in the [mysql] section
That’s it, you’re done!