Improve your AWS RDS MySQL Database
There are sometimes that our database feels slow, that have a direct impact in our application, so, here is a quick guide of a couple of things that you can do to improve the performance an do a better monitoring of your database.
Create a Read Replica, create a Read Replica to reduce the charge of your production database is a really good idea for consulting data for example, you should could in some cases use your production database mainly for CRUDS.
So here is what we did to improve our database performance and reduce the CPU Average usage from 60% to no more than 15%.
1- Enable Param Group
Go to your RDS console on AWS and in the left panel you will see the Parameter Groups options, there you can create new parameter groups for your database based on some default parameter groups that are created when you for example create a new database.
Note: You can’t modify a parameter group that is already attached to your database.
So, in the parameter group we can enable a lot of log options, one that will do the job for us is the slow_query_log, so click it and change the value to 1. You can choose also where your logs and how will be registered.
2- Enable Performance Insights
The next step is to Enable Performance Insights, you can do this selecting the database and clicking on the modify option, this will allow you to seek for the querys or operations, hosts, users, etc that are having a higher impact on your database and by impact I mean, time, CPU, etc.
Here we can choose the retention time.
Note: Everytime that you modify something on your database, for example the ParameterGroup or Performance Insights, you will be asked when to do it (Immediately as the operations of the database allows it) or on the next maintanence window.
3- Search for the queries with mayor AAS
So once the Performance Insights are activated, the next step is to look up for those querys that are having a major impact in our database (Load by waits (AAS)), (here is a link in 3.1 about the AAS and other metrics for your RDS that was posted by AWS)
So this basically tell us wich queries are having a higher impact, with this we can do some business decisions about how our application is working or handling some information, this
3.1- What is AAS
4- Create Indexes on Key Tables
You need to identify and map your queries, so probably there are some that maybe don’t have an index created, create an index improve a lot your queries. So this will have a direct impact on your Database Performance and of course your CPU Usage.
5- Set Alarms for CPU Usage
It is important to always enable some alarms on key metrics like CPU this will allow you to make a better monitoring of your Database and identify some time of the day or a special query that is affecting your performance.
6- The Results
Here are some charts of how our CPU Usage average decreased, improving a lot our application, there are some high picks in blue due to some index creations, that of course creates a delay because for security the database locks the metadata if you create a new index, that’s basically because there are some sessions for example that are using that table in particular.
About Me
If you can, please share this post and feel free to contact me by LinkedIn
I currently work as CTO at Rindegastos. I completed a Diploma in Strategic Leadership and a Master in Data Science.
Passionate about technology, developer in Typescript, Javascript and Python. I work with MongoDB and MySQL.
Currently developing Microservices with the NestJS framework in the back-end and Angular in the front-end, supported with Docker and AWS. I develop in Ionic v5 and also Serverless with NestJS.
