Overview of RDS for MySQL database logs (original) (raw)

You can monitor the following types of RDS for MySQL log files:

• Error log
• Slow query log
• General log
• Audit log
• Instance log
• IAM database authentication error log

The RDS for MySQL error log is generated by default. You can generate the slow query and general logs by setting parameters in your DB parameter group.

Topics

• RDS for MySQL error logs
• RDS for MySQL slow query and general logs
• MySQL audit log
• Log rotation and retention for RDS for MySQL
• Size limits on redo logs

RDS for MySQL error logs

RDS for MySQL writes errors in themysql-error.log file. Each log file has the hour it was generated (in UTC) appended to its name. The log files also have a timestamp that helps you determine when the log entries were written.

RDS for MySQL writes to the error log only on startup, shutdown, and when it encounters errors. A DB instance can go hours or days without new entries being written to the error log. If you see no recent entries, it's because the server didn't encounter an error that would result in a log entry.

By design, the error logs are filtered so that only unexpected events such as errors are shown. However, the error logs also contain some additional database information, for example query progress, which isn't shown. Therefore, even without any actual errors the size of the error logs might increase because of ongoing database activities. And while you might see a certain size in bytes or kilobytes for the error logs in the AWS Management Console, they might have 0 bytes when you download them.

RDS for MySQL writesmysql-error.log to disk every 5 minutes. It appends the contents of the log tomysql-error-running.log.

RDS for MySQL rotates the mysql-error-running.log file every hour. It retains the logs generated during the last two weeks.

Note

The log retention period is different between Amazon RDS and Aurora.

RDS for MySQL slow query and general logs

You can write the RDS for MySQL slow query log and the general log to a file or a database table. To do so, set parameters in your DB parameter group. For information about creating and modifying a DB parameter group, see Parameter groups for Amazon RDS. You must set these parameters before you can view the slow query log or general log in the Amazon RDS console or by using the Amazon RDS API, Amazon RDS CLI, or AWS SDKs.

You can control RDS for MySQL logging by using the parameters in this list:

• slow_query_log: To create the slow query log, set to 1. The default is 0.
• general_log: To create the general log, set to 1. The default is 0.
• long_query_time: To prevent fast-running queries from being logged in the slow query log, specify a value for the shortest query runtime to be logged, in seconds. The default is 10 seconds; the minimum is 0. If log_output = FILE, you can specify a floating point value that goes to microsecond resolution. If log_output = TABLE, you must specify an integer value with second resolution. Only queries whose runtime exceeds the long_query_time value are logged. For example, setting long_query_time to 0.1 prevents any query that runs for less than 100 milliseconds from being logged.
• log_queries_not_using_indexes: To log all queries that do not use an index to the slow query log, set to 1. Queries that don't use an index are logged even if their runtime is less than the value of the long_query_time parameter. The default is 0.
• log_output `option`: You can specify one of the following options for the log_output parameter.
  • TABLE (default) – Write general queries to themysql.general_log table, and slow queries to the mysql.slow_log table.
  • FILE – Write both general and slow query logs to the file system.
  • NONE – Disable logging.

For slow query data to appear in Amazon CloudWatch Logs, the following conditions must be met:

• CloudWatch Logs must be configured to include slow query logs.
• slow_query_log must be enabled.
• log_output must be set to FILE.
• The query must take longer than the time configured for long_query_time.

For more information about the slow query and general logs, go to the following topics in the MySQL documentation:

• The slow query log
• The general query log

MySQL audit log

To access the audit log, the DB instance must use a custom option group with theMARIADB_AUDIT_PLUGIN option. For more information, see MariaDB Audit Plugin support for MySQL.

Log rotation and retention for RDS for MySQL

When logging is enabled, Amazon RDS rotates table logs or deletes log files at regular intervals. This measure is a precaution to reduce the possibility of a large log file either blocking database use or affecting performance. RDS for MySQL handles rotation and deletion as follows:

• The MySQL slow query log, error log, and the general log file sizes are constrained to no more than 2 percent of the allocated storage space for a DB instance. To maintain this threshold, logs are automatically rotated every hour. MySQL removes log files more than two weeks old. If the combined log file size exceeds the threshold after removing old log files, then the oldest log files are deleted until the log file size no longer exceeds the threshold.
• When FILE logging is enabled, log files are examined every hour and log files more than two weeks old are deleted. In some cases, the remaining combined log file size after the deletion might exceed the threshold of 2 percent of a DB instance's allocated space. In these cases, the oldest log files are deleted until the log file size no longer exceeds the threshold.
• When TABLE logging is enabled, in some cases log tables are rotated every 24 hours. This rotation occurs if the space used by the table logs is more than 20 percent of the allocated storage space. It also occurs if the size of all logs combined is greater than 10 GB. If the amount of space used for a DB instance is greater than 90 percent of the DB instance's allocated storage space, then the thresholds for log rotation are reduced. Log tables are then rotated if the space used by the table logs is more than 10 percent of the allocated storage space. They're also rotated if the size of all logs combined is greater than 5 GB. You can subscribe to the low storage event category to be notified when log tables are rotated to free up space. For more information, see Working with Amazon RDS event notification.
  When log tables are rotated, the current log table is first copied to a backup log table. Then the entries in the current log table are removed. If the backup log table already exists, then it is deleted before the current log table is copied to the backup. You can query the backup log table if needed. The backup log table for the mysql.general_log table is namedmysql.general_log_backup. The backup log table for themysql.slow_log table is namedmysql.slow_log_backup.
  You can rotate the mysql.general_log table by calling the mysql.rds_rotate_general_log procedure. You can rotate the mysql.slow_log table by calling the mysql.rds_rotate_slow_log procedure.
  Table logs are rotated during a database version upgrade.

To work with the logs from the Amazon RDS console, Amazon RDS API, Amazon RDS CLI, or AWS SDKs, set the log_output parameter to FILE. Like the MySQL error log, these log files are rotated hourly. The log files that were generated during the previous two weeks are retained. Note that the retention period is different between Amazon RDS and Aurora.

Size limits on redo logs

For RDS for MySQL version 8.0.32 and lower, the default value of this parameter is 256 MB. This amount is derived by multiplying the default value of theinnodb_log_file_size parameter (128 MB) by the default value of theinnodb_log_files_in_group parameter (2). For more information, seeBest practices for configuring parameters for Amazon RDS for MySQL, part 1: Parameters related to performance.

For RDS for MySQL version 8.0.33 and higher minor versions, Amazon RDS uses theinnodb_redo_log_capacity parameter instead of theinnodb_log_file_size parameter. The Amazon RDS default value of theinnodb_redo_log_capacity parameter is 2 GB. For more information, see Changes in MySQL 8.0.30 in the MySQL documentation.

Starting with MySQL 8.4, Amazon RDS enables the innodb_dedicated_server parameter by default. With the innodb_dedicated_server parameter, the database engine calculates the innodb_buffer_pool_size andinnodb_redo_log_capacity parameters. For more information, see Configuring buffer pool size and redo log capacity in MySQL 8.4.