How to overcome Throttling and Rate Exceeded Errors in DownloadDBLogFilePortion

I was attempting to download the MySQL slow query logs to perform a slow query review. In this blog we will explore the issue I faced while downloading the slow logs and my workaround to solve it. I am also looking for better approaches that you take to perform similar operations.

Downloading Slow Logs for MySQL from AWS

If you’re logging RDS slow logs in MySQL table read, Downloading slow logs from RDS

For MySQL databases hosted on AWS RDS, AWS CLI provides commands to download slow query logs for in-depth analysis. With simple commands, such as “describe-db-log-files” and “download-db-log-file-portion,” administrators can efficiently retrieve and review slow logs, gaining valuable insights into database performance and query optimization.

I began by using the AWS CLI command to describe the database log files and extract the slow log file names.

aws rds describe-db-log-files --db-instance-identifier rds_slow_log_test | grep 'FileName' | grep slow | awk -F'"' '{print $4}'

Further extended the script to share the parsed list

for slowlog in $(aws rds describe-db-log-files --db-instance-identifier rds_slow_log_test | grep 'FileName' | grep slow | awk -F'"' '{print $4}'); do echo "Downloading $slowlog"; aws rds download-db-log-file-portion --db-instance-identifier rds_slow_log_test --starting-token 0 --output text --log-file-name $slowlog > $slowlog; done;

Throttling error about Rate exceeded

Though while executing the download-db-log-file-portion command, I received following errors:

An error occurred (Throttling) when calling the DownloadDBLogFilePortion operation (reached max retries: 2): Rate exceeded
Downloading slowquery/mysql-slowquery.log.2023-07-23.15

This caused the incomplete download of slow query logs.

-rw-rw-r--. 1 centos centos 100M Jul 24 05:13 mysql-slowquery.log.2023-07-23.15
-rw-rw-r--. 1 centos centos 30M  Jul 24 05:25 mysql-slowquery.log.2023-07-23.16
-rw-rw-r--. 1 centos centos 150M Jul 24 05:38 mysql-slowquery.log.2023-07-23.17
-rw-rw-r--. 1 centos centos 110M Jul 24 05:49 mysql-slowquery.log.2023-07-23.18
-rw-rw-r--. 1 centos centos 210M Jul 24 05:58 mysql-slowquery.log.2023-07-23.19

The slow logs were not fully downloaded due to the error mentioned above. In the re:POST, AWS talks a bit about rate exceeded exception

Fixing throttling, rate exceeded error

To overcome the Throttling error, AWS suggests implementing error retries and exponential backoff when making API calls. One of the key environment variables to configure is AWS_MAX_ATTEMPTS, which specifies the maximum number of attempts to make on a request.

The environment variable AWS_MAX_ATTEMPTS specifies the maximum number attempts to make on a request. The error mentions that those retries were only “2” earlier which can be tweaked. So, I rerun the following command after exporting AWS_MAX_ATTEMPTS to a some-what larger number.

export AWS_MAX_ATTEMPTS=100;
for slowlog in $(aws rds describe-db-log-files --db-instance-identifier rds_slow_log_test | grep 'FileName' | grep slow | awk -F'"' '{print $4}'); do echo "Downloading $slowlog"; aws rds download-db-log-file-portion --db-instance-identifier rds_slow_log_test --starting-token 0 --output text --log-file-name $slowlog > $slowlog; done;

and the slow logs were fully downloaded

-rw-rw-r--. 1 centos centos 5.1G Jul 24 06:23 mysql-slowquery.log.2023-07-23.15
-rw-rw-r--. 1 centos centos 5.5G Jul 24 06:45 mysql-slowquery.log.2023-07-23.16
-rw-rw-r--. 1 centos centos 5.3G Jul 24 07:07 mysql-slowquery.log.2023-07-23.17
-rw-rw-r--. 1 centos centos 5.1G Jul 24 07:28 mysql-slowquery.log.2023-07-23.18
-rw-rw-r--. 1 centos centos 5.2G Jul 24 07:49 mysql-slowquery.log.2023-07-23.19

Git script to download MySQL slow logs

I also have this old script uses the same aws command to download-db-log-file-portion which is updated with change to avoid rate throttling and rate exceeded errors: get_rds_slow_log.

Conclusion

The journey to download large slow query logs can be fraught with rate limit challenges. However, by implementing AWS’s recommended adjusting AWS_MAX_ATTEMPTS, I successfully overcame the Throttling and Rate Exceeded errors. This experience has proven valuable, and I hope this blog helps others facing a similar roadblock. Feel free to share your own strategies for tackling this issue and optimizing slow query log reviews.

References