I have searched the forums for slow query log and find many references to turning it on and then reading the log file, but none that I have read sofar say exactly where to set the logging to “on” and where to then read the file.
I am having some very slow actions when doing anything that hits the database and I want to start tracking down the source of the problem.
Having already increased the gunicorn workers, background workers, and the buffer pool size to the optimum for my server hardware, I still need to find the source of the slow-down.
Any help or pointers to posts that detail how to setup the slow query log would be greatly appreciated.
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.cnf file. 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
Okay, before I tried to turn on the “slow_query_log” I tried the “mysqldumpslow” command and found that a log file already existed.
I dumped this to a text file and tried reading it. There is a bunch of data in it but I cannot find any indications of when on of the events occurred.
How do I know if the data in the log file is current or ancient?
Is there a way to reset the contents of the file so that any new activity is what will be recorded?
I went to the directory where the system indicates the log file should exist, but there is no such file. In fact when I look in the directory all I see is the following:
erp_bkm@goofyhost:~$ sudo ls -lha /var/log/mysql
total 16K
drwxr-s--- 2 mysql adm 4.0K Jan 31 21:06 .
drwxrwxr-x 12 root syslog 4.0K Apr 6 2018 ..
-rw-rw---- 1 mysql adm 160 Jan 31 21:06 mariadb-bin.index
-rw-rw---- 1 mysql adm 6 Jan 22 2018 mariadb-bin.state
erp_bkm@goofyhost:~$
Is the log file somehow encrypted into these two files?
Anyway…
Do I need to clear the data in the log so I can see only the stuff from the next 24 to 48 hrs when I use the command:
SET GLOBAL slow_query_log=1;
This is more confusing than it needs to be. I just want to get the information for the next 24 to 48 hours so I can determine exactly what is causing my performance issues. I just have no idea how to get rid of the existing log data because I cannot find any real log file!!
Any Ideas?
Here is the information from the my.cnf file that led me to the log file data directory:
(Even though the dump of the slow_query_log file shows data. I have no idea where it is actually stored because the my.cnf file has me confused. I see no such file.)
# Enable the slow query log to see queries with especially long duration
#slow_query_log[={0|1}]
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 10
#log_slow_rate_limit = 1000
log_slow_verbosity = query_plan
#log-queries-not-using-indexes
#log_slow_admin_statements
#
You are correct, it might really be overkill. However, once I get this problem behind me I might investigate setting up something like that article describes to have a sort of continuous monitor on the health of the database.
Too much additional headache to try right now though. Installing anything else in an already slow system is not the approach I want to take right now.
When I actually enter the mysql command interface and run this command:
SELECT @@datadir;
The answer is “/var/lib/mysql/mysql-slow.log”
This file actually has todays date on it so it appears to be correct. It is also over 24mb in size! To make it even more difficult to work on it is owned by the root user and root group and is set to be unreadable in the permissions.
So, I have found the right file, but I have no idea how to clear it or reset it so that I can track only the next 2 days of alerts.
BTW…
I have already tried the command:
FLUSH SLOW LOGS;
and it had no affect on the log file. The response was “0 lines affected”
I was logged into mysql as the root user but the log did not flush.
Next, execute whatever Forms/Reports that are being slow. You could also wait 24-48 hours. But during that time, the file could grow quite large. Which makes it harder to sift through.
Regardless, whenever it’s time for analysis, make a copy of the log. That way you can examine at your convenience, without it growing further or being touched.
It turns out it is safe to delete the file and then restart mysql service to start a fresh version of the file. After many hours of google searches I stumbled cross that as a viable solution. Tried it on my test server and it worked fine.
So, I waited until all users were logged off the live production server and did the same thing. In the morning (after a few hours of regular use) I will take a copy of the file for review.