AWS RDS Best Practices
AWS recommends best practices for RDS in terms of Performance, Monitoring, and Security
Amazon RDS Basic Operational Guides
MonitoringMemory, CPU and storage usage should all be monitored.
CloudWatch can be set up to notify you when usage patterns change, or when the deployment capacity is approaching. This will ensure that system performance and availability are maintained.
ScalingScale the DB instance as storage capacity limits approach.
Storage and memory should have enough buffer to allow for unexpected increases in demand.
BackupsEnable Automatic Backups and set the backup window so that it occurs during WriteIOPS’s daily low.
Do not create more than 10,000 tables with Provisioned IOPS on a MySQL DB instance or more than 1000 tables using standard storage. Large tables can significantly slow down database recovery after a database crash or failover. If you need to create more tables than recommended, set the innodb_file_per_table parameter to 0.
It is important to not allow tables in the database to grow too large. Provisioned storage limits limit the MySQL table file size to 6 TB. Instead, partition large tables so that file size is well below the limit of 6 TB. This can improve performance and recovery times.
Performance If the database workload is more demanding than what is provisioned, recovery following a failover will be slow.
Migrate to a DB class with high I/O capacity to increase the I/O capability of a DB instance.
Convert from standard storage storage to Provisioned-IOPS storage and use a DB instance that is optimized for Provisioned IIOPS.
Provisioned IOPS storage requires additional throughput capacity.
Multi-AZ & FailoverDeploy apps in all Availability zones, so if one AZ goes down, other AZs applications will still be possible.
Amazon RDS DB events can be used to monitor failovers.
If the client application is cached the DNS data of the DB instance, set a TTL of 30 seconds or less. Caching DNS data for a long time can cause connection failures if an application tries to connect with an IP address that is no longer in service.
Multi-AZ requires that transaction logging feature be enabled. Do not use features such as Simple recover mode, offline mode, or Read-only mode that turn off transaction logging.
To reduce failover times, ensure that you have sufficient Provisioned IOPS for your workload. Inadequate I/O could increase failover times. Database recovery requires I/O.
Use smaller transactions. Database recovery depends on transactions. Therefore, break up large transactions into smaller transactions to reduce failover time
Recommendations for DB Instance RAM
Amazon RDS performance best practices include allocating enough RAM to ensure that the working set is almost entirely in memory.
The value of ReadIOPS should not be large and should be stable.
You can check the ReadIOPS metric using AWS CloudWatch while the DB instance under load to see if the working set has almost all of its memory.
Scaling up the DB instance class with more RAM results in a dramatic drop of ReadIOPS. However, the working set wasn’t nearly fully in memory.
Continue scaling up until ReadIOPS does not drop dramatically after scaling or ReadIOPS is reduced significantly.
You should not use AWS root credentials for Amazon RDS resources management; IAM users should be created and shared with everyone.
Each user should be granted the minimum number of permissions necessary to perform his orher duties.
To manage permissions for multiple users effectively, use IAM groups
Rotate your IAM credentials frequently. Enhanced Monitoring to Identify Op