4

I am trying to enable the slow query log via RDS web console.

We are using mysql 5.6 on RDS not aurora.

I'm changing the Parameter groups values of slow_query_log to 1 long_query_time to 5 slow_query_log_file is /rdsdbdata/log/slowquery/mysql-slowquery.log

SHOW GLOBAL STATUS; shows the value of Slow_queries of 2817006 and is increasing so obviously the slow queries are present but nothing is in the log.

the downloaded log logs like

the log is created but the content of the log does not contain any sql 

/rdsdbbin/mysql/bin/mysqld, Version: 5.6.34-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
/rdsdbbin/mysql/bin/mysqld, Version: 5.6.34-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
/rdsdbbin/mysql/bin/mysqld, Version: 5.6.34-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
simonC
  • 195
  • 2
  • 12
  • What value are you setting the `long_query_time` to? Is it even a valid setting? Can you try a different value? Is it RDS? Aurora? What version? We need more details to help you. – MLu Nov 06 '18 at 22:32
  • Are you following this guide: **[Accessing the MySQL Slow Query and General Logs](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_LogAccess.Concepts.MySQL.html#USER_LogAccess.MySQL.Generallog)**? It's got step by step instructions on how to make the slow logs accessible. – MLu Nov 06 '18 at 22:29
  • @simonc Does the Parameter Group show you the 'slow_query_log_file' value? What value are your trying to set in long_query_time? Is Skype available for you? View profile, Network profile for contact info, including my Skype ID. – Wilson Hauck Nov 07 '18 at 16:15
  • thnx I have. managed to enable the slow query log, now the log is generating but does not contain any queries ... look the updated question – simonC Nov 09 '18 at 19:18
  • @simonc Please post to pastebin.com or here TEXT RESULTS of SHOW GLOBAL VARIABLES; and SHOW GLOBAL STATUS; so we can see what your operating information looks like from MySQL's perspective. – Wilson Hauck Dec 08 '18 at 18:52

2 Answers2

5

There is one parameter that need to be updated to enable the slow query log, and two that define how it works:

slow_query_log: Needs to be set to 1 to enable it.

long_query_time: Tells what long-running queries get logged.

min_examined_row_limit: If you care less about run-time and more about queries that might be table-scanning, setting this to a non-zero value will limit what gets logged to queries that touch too many rows.

sysadmin1138
  • 133,124
  • 18
  • 176
  • 300
2

You need to change log_output parameter to file as it's default value is TABLE. At the moment your slow query are going to table slow_log. You can verify it.

Select * from mysql.slow_log.

Change the parameter log_output to FILE and log will be written to file.

PRAMOD MANN
  • 21
  • 1