Amazon RDS provides us with three different types of logs :
1) General Logs
2) Slow Query Logs
3) Error Logs
You can enable these logs by modifying the DB Parameter Group.
1) Enable General Log
Parameter Name : general_log Value : set the value to 1. Default is 0.
2) Enable Slowquery Log
Parameter Name : slow_query_log Value : set the value to 1. Default is 0.
Once you enable these logs, Amazon RDS starts generating logs.
Amazon RDS defines two output format ( TABLE or FILE )for logs storage :
Parameter : log_output
1) TABLE (default)
–> If log_output is set to TABLE, then Amazon RDS generates logs inside general log/slow log inside mysql.general_log and mysql.slow_log TABLE respectively.
–> log contents are accessible through SQL commands. e.g
mysql> SELECT * FROM slow_log ORDER BY query_time DESC;
–> logs table are not automatically rotated.
–> Enabling log_output = TABLE increases amount of data written to database which decreases performance.
–> as the log file grows, we need to manually rotate the logs by calling below commands successively.
–> you will always receive this warning “[ The MySQL general and/or slow logs of the DB Instance: rds-Instances are consuming a large amount of provisioned storage.]” under AWS RDS Events section if you use TABLE format log. As it keeps on writing logs to the databases table.
–> To manually rotate the general/slowquery log, you will have to execute the below commands.
mysql> CALL mysql.rds_rotate_slow_log; mysql> CALL mysql.rds_rotate_general_log;
Note : To remove logs completely and free the disk space, call the above command twice.
–> If log_output is set to FILE, then Amazon RDS writes the logs to the file system.
–> logs are automatically rotated every hours and logs file older than 24 hours are deleted automatically.
–> logs can be seen / downloaded directly from Amazon RDS API, Amazon RDS CLI, or AWS SDKs.
To Download logs directly from the AWS RDS Console, you have to execute the below API call.
aws rds download-db-log-file-portion --db-instance-identifier testing-RDS --log-file-name slowquery/mysql-slowquery.log --output text > file.txt
Note : It’s recommended to set the Value of Parameter “log_output” as FILE for the better performance, as it lowers the overheads of read and write on databases.