How to reduce costs by tuning RDS Aurora logs
I was recently working on a project where there was a high cost in CloudWatch logs, checking a bit I found that it was due to the number of logs ingested per GB in the PutLogEvents API call, which costs $0.50 USD per GB per month. After doing some research I found some ways to better tune Aurora logs and thus reduce costs and I want to show you how to do it.
What do we need to log in?
When we have productive databases we must consider different factors that can determine what types of logs we need to save, for example, in this case, we had the following:
- Audit Logs: To comply with industry regulations, they had to be kept.
- Error Logs: They allow the developers and the infrastructure area to identify errors.
- Slow Query Logs: They help developers identify slow queries that affected the performance of the database.
Current Scenario
There is a productive application that is the core of the company, which uses an Aurora MySQL database with the following characteristics:
- Reader Queries: 1200 per second
- Writer Queries: 500 per second
- Size: db.r5.2xlarge
- Deployment type: Multi-Az
- Version: 5.7.mysql_aurora.2.10.2
- Storage: 1 TB
Current Costs in CloudWatch logs
In the following image, you can see the monthly cost only for the database cluster logs.
Now we are going to learn how to enable logging in to an Aurora MySQL cluster and also how to adjust it to our needs and keep only the information we need.
How to Enable logs in Aurora MySQL Cluster
To enable the audit log in Aurora MySQL cluster, you should do the following steps:
In the search bar, type RDS and select the service.
Once we are in the RDS console, we must select the cluster identifier for which we want to enable the logs. Once selected, click on the Modify button at the top right.
Go to Log exports section and select the logs you would like to enable.
Once the logs have been selected, go down to the bottom of the screen and click on Continue and then on Modify Cluster. Note that this change does not require a cluster reboot or service downtime.
How to search the RDS logs in CloudWatch logs
In the search bar, type RDS and select the service.
In the CloudWatch console, in the menu on the left, click on Logs > Log groups.
Within Log groups, we should already see the logs that we enabled in RDS with the following structure:
- Audit Logs: /aws/rds/cluster/mycluster/audit.
- Error Logs:/aws/rds/cluster/mycluster/error
- Slow Query Logs: /aws/rds/cluster/mycluster/slowquery.
If we click on any of these, we will begin to see the registered data as follows:
All those are the log streams that have all the information collected from the RDS.
How to customize RDS logs
Before going into detail, we must understand some RDS concepts such as the following:
- DB Parameter Group: acts as a container for engine configuration values that are applied to one or more DB instances.
- DB Cluster Parameter Group: DB cluster parameter groups only apply to Multi-AZ DB clusters. In a Multi-AZ DB cluster, the settings in the DB cluster parameter group are used for all the DB instances in the cluster.
- Within the parameters, there are two different ways to apply modifications, dynamic and static. Dynamic apply is applied immediately to the database without requiring a reboot, however, static apply requires a reboot of the database instances associated with the cluster.
To modify the parameter group of the cluster, select the cluster in the Configuration tab and locate where it says DB cluster parameter group, and click.
Customization in Audit Logs
Once we are inside the cluster parameter group, we are going to look for the word audit in the search bar and focus on the following values:
- Server_audit_events: defines the operations that will be logged. By default, the CONNECT, QUERY, QUERY_DCL, QUERY_DDL, QUERY_DML, TABLE operations are logged. In our scenario, we only enable the CONNECT and QUERY operations, which allows having the information on the connections and disconnections in addition to logging all the queries in plain text.
- Server_audit_incl_users: specifies the users who should be audited. For our scenario, we specified the users that we had to audit. This guarantees that you will only log the information about these users and exclude the rest.
This is how the new configuration of the parameter group looks, note that since they are dynamic parameters the change is applied immediately.
Customization in Error Logs
Once we are inside the cluster parameter group, we are going to look for the word log_error in the search bar and focus on the following values:
log_error_verbosity: To configure this parameter, you can leave values from 1 to 3, which determine the information that will be logged as follows:
- 1: Error messages.
- 2: Error and warning messages.
- 3: Error, warning, and information messages.
For our case, we only want to log the error messages, so this parameter remains with value = 1.
Customization in Slow Query Logs
Once we are inside the cluster parameter group, we are going to look for the word long_query_time in the search bar and focus on the following values:
Long_query_time: determine the time in seconds of the slow queries that will be saved in CloudWatch logs, in our case we leave the value at 30, that is, the queries that take more than 30 seconds to respond will be the ones that will be logged.
Conclusion
- In the proposed scenario, the cost of the logs was reduced by 40%, this value may change since these customizations were adjusted to the needs of our application. This saving was achieved by adjusting the audit logs as described in the article
- Remember, when creating an Aurora DB cluster, always create a custom parameter group; otherwise you won’t be able to modify the values until you change it and that requires a database restart.
- The parameters can change in each version of Aurora, so it is important to consult the official AWS Aurora or MySQL documentation for the established version
References
Using Advanced Auditing with an Amazon Aurora MySQL DB cluster.
CloudWatch Logs Documentation.
