Last week, my team faced a serious issue with our RDS when the CPU usage spiked dramatically, causing significant disruptions to our system and impacting our clients. After investigating, we identified inefficient SQL queries as one of the primary cause. Here are the five key actions we took to improve performance.

First thing first: identifying the cause

The issue was clearly tied to SQL, but we needed to pinpoint the exact source—whether it was a specific cluster, database, or instance (master or slave). Fortunately, AWS provides robust tools that allowed us to track down the most resource-intensive queries.

The Approach

1. Efficient Use of Indexes

   • We conducted a thorough review of all indexes on the affected tables. This involved analyzing the query execution plans to identify any missing or unused indexes. We then updated the DDL (Data Definition Language) to add indexes where necessary and removed those that were redundant. This helped in speeding up data retrieval by allowing the database to locate records more quickly.

2. Optimizing Joins

   • We scrutinized the queries involving multiple table joins. By examining the relationships and data flow between tables, we identified opportunities to reduce the number of joins, particularly in cases where certain joins were unnecessary or could be replaced with more efficient subqueries. We also ensured that the joined columns were properly indexed to improve join performance.

3. Limiting Data Retrieval

   • We noticed that several queries were fetching large volumes of data, much of which was unnecessary. To mitigate this, we used LIMIT clauses to restrict the number of rows returned by queries. We also optimized SELECT statements to retrieve only the specific columns needed, rather than using SELECT *, which retrieves all columns and can lead to inefficiencies.

4. Implementing Query Caching

   • For queries that were frequently executed with the same parameters, we enabled query caching. This allowed the database to store the result set of these queries, so subsequent executions could be served from the cache instead of re-running the entire query. We carefully configured the cache expiration and invalidation settings to ensure data freshness while maximizing performance gains.

5. Optimizing Database Design

   • We revisited the overall database design, focusing on areas that were causing bottlenecks. This included normalizing certain tables to reduce redundancy and improve data integrity, as well as denormalizing where appropriate to reduce the need for complex joins. We also scaled our architecture by adding read replicas (slave instances) to offload read operations from the master database, balancing the load and improving performance.

The Outcome

• After implementing these optimizations, we saw a 20% reduction in CPUUtilization, leading to a more stable and efficient system.