Managing RDS MySQL logs – RDS needs DBA -1

RDS is popularly known as AWS managed service but we would like to call it as CloudDBA managed service . We are starting this series “RDS – Managed by CloudDBA” to focus on the need for DBA in the cloud era . The cloud does not negate the need of DBAs ; They are very much needed to optimize the database environments in cloud

Managing AWS RDS logs :

Managing RDS logs is different from Native MySQL in many ways . Below are different types of logs in RDS MySQL :

  • Slow log and General log (general log not enabled mostly)
  • Error log
  • Binary log
  • Audit log

How to view the logs that are currently enabled and available ?

Below cli command can be executed to list the available log files for a given instance

aws --output text rds describe-db-log-files --db-instance-identifier database-1  --region us-east-2
DESCRIBEDBLOGFILES      1600823400000   error/mysql-error-running.log   136
DESCRIBEDBLOGFILES      1600821900000   error/mysql-error-running.log.1 3689
DESCRIBEDBLOGFILES      1600739100000   error/mysql-error-running.log.2 4092
DESCRIBEDBLOGFILES      1600744800000   error/mysql-error-running.log.4 136
DESCRIBEDBLOGFILES      1600823700000   error/mysql-error.log   0
DESCRIBEDBLOGFILES      1599843227000   mysqlUpgrade    1013
DESCRIBEDBLOGFILES      1600822800000   slowquery/mysql-slowquery.log   0
DESCRIBEDBLOGFILES      1600821683000   slowquery/mysql-slowquery.log.1 1561
DESCRIBEDBLOGFILES      1600748310000   slowquery/mysql-slowquery.log.2 2006

Error logs and RDS - How are they different in RDS ?

There are three variants of error log you would see :

1) mysql-error.log

2) mysql-error-running.log and mysql-error-running.log.x

Mysql-error.log : This log is rotated every 5 minutes and appended to mysql-error-running.log

Mysql-error-running.log : This log is rotated every hour and logs upto 24 hours are retained

Also the hour in current time is added to the name of the error log . For example 4 in “error/mysql-error-running.log.4” indicates 4th hour in UTC timezone

CloudDBA In Action : What if we want to look at error logs for the last month or so ? If you use the default settings you will not be able to do that . What if there is a production issue ; error logs are crucial for evidence gathering and they are not available now ? If you do not have dedicated DBA in cloud things like this can happen in production . A Cloud DBA/DBE can continuously fetch logs from RDS and save it to S3 or other storage with a proper retention interval matching your needs .

Please refer to our repo  https://github.com/klouddb/klouddb_tools For a sample script that can be used to download logs continuously

Slow logs and RDS - How are they different in RDS ?

By default slow logs are not enabled and you can enable it by changing slow_query_log to 1 , long_query_time(Threshold to capture the queries . E.g if you want to capture all the queries that take more than 2 seconds then this should be set to 2) . Also general log can be enabled by setting general_log to 1

Notice that log_output can be TABLE or FILE and the default is TABLE which stores all query records in a table .

CloudDBA In Action : Below is one of the recommendations by AWS to use FILE. By default it uses TABLE mode and then it complains about using it and recommends to convert it to FILE based. Only a good CloudDBA can tune the parameters needed to optimize query logging : 1) What is the best value for long_query_time ? Use FILE or TABLE for our use case ? Archive old slow logs to cheap storage to save costs ? These are some of the decisions that can only be made by experienced CloudDBA

Recommendation from AWS :

Setting logging output to TABLE uses more storage than setting this parameter to FILE. To avoid reaching the storage limit, we recommend setting the parameter to FILE

How does the pruning occur for slow logs then ?

For FILE format logs are rotated every 1 hour and any logs older than 24 hours are deleted . Second trigger for deletion is when the space used by logs exceeds 2% of the total db instance allocated storage . Largest files are automatically deleted when it exceeds 2% usage

For TABLE format log records rotation can happen every 24 hours . Second criteria for deletion is triggered when the space used exceeds 20% of allocated storage or if the largest file exceeds 10 GB in size . This threshold is further reduced when the database instance is short of storage . When the overall used percentage for database instance reaches 90% or above, log records are truncated until it reduces to 10% of allocated storage or the largest file reduces to 5 GB

When the log records are rotated they are written to a backup table mysql.slow_log_backup. You also can use mysql.rds_rotate_slow_log procedure to rotate the logs on demand

Binary logs and RDS - How are they different in RDS ?

Binary logs are very important for point in time recovery . Amazon RDS normally purges a binary log as soon as possible.It is advised that you change log retention value with a command like “call mysql.rds_set_configuration(‘binlog retention hours’, 24);”

If you leave the default value you may not be able to predict the pruning interval and storage growth on your instance

CloudDBA In Action : If you do not have DBA/DBE , you may not be able to optimize binary log retention, format etc.. Also A DBA/DBE will be able to download binary logs to s3 automatically using mysqlbinlog utility . He will be able to save old log files to Glacier storage or other inexpensive storage to save costs.

How to use our Python script to download log files on a continuous basis ?

You can fetch error log , slow log , general log via RDS CLI . But inorder to fetch binary logs you need to use something like below. Our script fetches error log, general log , slow log and binary logs and sorts them by timestamp and RDS instance . Script can be further modified to suit your requirements

Please refer to our repo https://github.com/klouddb/klouddb_tools

Below is a sample set of files that you can retrieve from RDS MySQL . NOTE : You do not see binary logs here . Also slow query and general log are only available once you enable them

aws --output text rds describe-db-log-files --db-instance-identifier database-1  --region us-east-2
DESCRIBEDBLOGFILES      1600823400000   error/mysql-error-running.log   136
DESCRIBEDBLOGFILES      1600821900000   error/mysql-error-running.log.1 3689
DESCRIBEDBLOGFILES      1600739100000   error/mysql-error-running.log.2 4092
DESCRIBEDBLOGFILES      1600744800000   error/mysql-error-running.log.4 136
DESCRIBEDBLOGFILES      1600823700000   error/mysql-error.log   0
DESCRIBEDBLOGFILES      1599843227000   mysqlUpgrade    1013
DESCRIBEDBLOGFILES      1600822800000   slowquery/mysql-slowquery.log   0
DESCRIBEDBLOGFILES      1600821683000   slowquery/mysql-slowquery.log.1 1561
DESCRIBEDBLOGFILES      1600748310000   slowquery/mysql-slowquery.log.2 2006

For binary log retrieval we use something like below

mysqlbinlog \
    --read-from-remote-server \
    --host=MySQL56Instance1.cg034hpkmmjt.region.rds.amazonaws.com \
    --port=3306  \
    --user ReplUser \
    --password \
    --raw \
    --result-file=/tmp/ \
    binlog.00098

How does it store the backups in S3

We have a separate directory per instance . For example in below example we have separate directory for instance database-1

Under the instance directory , we have a folder/directory by date

Under the date directory , we have a folder by timestamp(timestamp of the run)

Under the timestamp directory , we have separate folders for each log type : slow log , general log , error log , binary logs

Share this Post :