Suggestions to resolve RDS (MariaDB) instance CPU hikes?
Hi all
We've observed recent CPU spikes on our db.t3.xlarge MariaDB RDS instance. These spikes appear to correlate with queries that retrieve large datasets from the database.
Our initial considerations for addressing this issue include:
- Migrating to a memory-optimized instance type, such as db.r7g.xlarge.
- Implementing comprehensive query optimization.
To effectively resolve this, we would appreciate your insights on the following:
- Are there any established best practices or general guidance for right-sizing RDS instances?
- Beyond the two points mentioned, what other general strategies or solutions would you recommend to mitigate these CPU spikes?
Thank you for your time and expertise.
- Topics
- Database
- Language
- English
asked a year ago227 views
- Newest
- Most votes
- Most comments
Is this a production database? If so, it is better not to use t3 as you will get performance hits when the database is heavily used. To identify the instance type, you need to use performance insights from the RDS itself. MariaDB does not support AWS Compute Optimizer like other RDS databases, therefore, you need to rely on the metrics provided by RDS itself.
Also, another suggestion would be to use read replicas on the MariaDB for the heavy queries, that way, your primary database is not impacted.
When dealing with CPU spikes on your MariaDB RDS instance, especially those correlated with large dataset queries, there are several approaches you can consider:
For your proposed solutions:
- Migrating to a memory-optimized instance like db.r7g.xlarge is a valid approach, particularly if your workload is memory-intensive. This would provide more memory resources for query processing.
- Query optimization is essential and should be prioritized before scaling up hardware.
Additional recommendations:
Analyze and optimize SQL queries:
- Identify the specific SQL statements causing high CPU usage using Performance Insights
- Use EXPLAIN to analyze query execution plans
- Ensure proper indexing for frequently used queries
- Consider breaking down large queries into smaller ones
Consider RDS Optimized Reads:
- If you're using large temporary tables or analytical queries, consider instance types with instance store support (like db.m5d or db.m6gd)
- This offloads temporary object operations to the instance store, reducing pressure on persistent storage
Workload management strategies:
- Redirect read-only workloads to reader nodes if possible
- Split bulk transactions into smaller ones to reduce resource consumption
- If specific queries can't be optimized further, consider scheduling them during off-peak hours
Monitor and tune database parameters:
- Monitor FreeLocalStorage and other relevant CloudWatch metrics
- Adjust binlog_cache_size if binlog entries are consuming disk space
- Configure idle_in_transaction_session_timeout to end sessions that remain idle in transactions
Connection management:
- Review application connection pool settings to avoid frequent allocation of new connections
- Check for connection storms that might be causing CPU spikes
If after optimization your CPU utilization remains high, then scaling up to a larger instance or switching to a different instance family would be appropriate. Monitor both CPU and memory utilization to determine the most suitable instance type for your workload patterns.
Sources
Key concepts for DevOps Guru for RDS - Amazon DevOps Guru
cpu - Amazon Aurora
Improving query performance for RDS for MariaDB with Amazon RDS Optimized Reads - Amazon Relational Database Service
Troubleshoot high CPU utilization | AWS re:Post
answered a year ago
Relevant content
asked 6 months ago
asked 3 years ago
asked 2 years ago
