log_output TABLE vs FILE inside Amazon RDS

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

[js]
Parameter Name : general_log
Value : set the value to 1. Default is 0.
[/js]

2) Enable Slowquery Log

[js]
Parameter Name : slow_query_log
Value : set the value to 1. Default is 0.
[/js]

Once you enable these logs, Amazon RDS starts generating logs.

Amazon RDS defines two output format ( TABLE or FILE )for logs storage :

[js] Parameter : log_output [/js]

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

[js]
mysql> SELECT * FROM slow_log ORDER BY query_time DESC;
[/js]

–> 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.

[js]
mysql> CALL mysql.rds_rotate_slow_log;
mysql> CALL mysql.rds_rotate_general_log;
[/js]

Note : To remove logs completely and free the disk space, call the above command twice.

2) FILE.

–> 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.

[js]

aws rds download-db-log-file-portion –db-instance-identifier testing-RDS –log-file-name slowquery/mysql-slowquery.log –output text > file.txt

[/js]

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.