How to enable MySQL slow query logs for Matomo?
To enable MySQL slow query logs we will need to edit the MySQL configuration file. The location of the MySQL configuration file depends on your operating system. MySQL will look at each location in order, and use the first file that it comes across. On Linux, MySQL will load the first config file found in this list:
/etc/my.cnf/etc/mysql/my.cnf$MYSQL_HOME/my.cnf
Add the following configuration options under [mysqld] in the config file my.cnf:
[mysqld]
# Threshold in seconds for a slow query to be logged
long_query_time=10
# Where to log the slow queries
slow_query_log_file=/var/log/mysql/mysql-slow.log
# Enable slow query logging
slow-query-log=1
Once you restart the MySQL server, any slow queries taking 3 seconds or more will be logged into the file /var/log/mysql/mysql-slow.log
When the time will come to analyse the slow query log file, we would use a tool call mysqldumpslow which will group all the similar queries together and print a top of the slowest queries across the log file. For example, to get the top 10 queries sorted by average query time we would type:
$ mysqldumpslow -t 10 mysql-slow.log > mysqldumpslow.out