Mysql – Slow query log is not storing slow queries

MySQLmysql-5.6performancequery-performance

Using 5.6.35 MySQL Community Server (GPL). I have setup the local MySQL instance as follows

show variables like '%slow%query%log%'

slow_query_log  ON
slow_query_log_file /usr/local/mysql/data/MacBook-Pro-slow.log

general_log ON

but I don't see any queries being logged in 

SELECT * FROM mysql.slow_log;

SELECT * FROM mysql.general_log

Both the tables are empty. I am running queries that are running for more than 20 seconds, and my threshold is set fine. Any advice?

Best Answer

There are two ways to collect general and slow logs:

  • File (this is the default)
  • Table (this needs to be specifically enabled)

Based on the info you provided, it looks like MySQL is using the default value for log_output which is "Log output goes to file(s) defined by general_log_file and slow_query_log_file". You can verify this by looking at these files (look in folder /usr/local/mysql/data/ and see what *.log files are there)

If you want the logs to go to database, on a MySQL client when logged in as a privileged user e.g. root:

  • SET GLOBAL log_output='TABLE';
  • SET GLOBAL general_log=ON;
  • SET GLOBAL slow_query_log=ON;
  • SET GLOBAL long_query_time=0;

The above will send global and slow logs to mysql.general_log and mysql.slow_log tables.

Note that I have set long_query_time to 0 seconds i.e. every query goes to slow_log(that is lot of output into the table). You can change this to another value that suits your use case.

Also, general_log produces a huge amounts of logs so you should only enable it for certain amount of time.

Related Question