Performance optimization strategies for MySQL on Amazon RDS

Amazon Relational Database Service (Amazon RDS) for MySQL is a scalable and cost-effective solution to deploy managed MySQL servers in the cloud. While RDS handles the operational overhead of database management, many teams struggle to achieve optimal performance for their specific workloads. The default configurations, though reliable, may not deliver the speed and efficiency your applications demand. Without proper tuning, you might experience slower query response times, inefficient resource utilization, and higher costs than necessary.

AWS provides extensive documentation on MySQL parameter configuration covering performance, replication, and operational parameters. This post complements those resources by focusing on infrastructure-level optimizations, RDS-specific performance features, and database design patterns that are often overlooked but crucial for achieving optimal performance. We’ll explore storage configuration strategies, instance selection considerations, and architectural decisions that can dramatically impact your database performance before you even begin parameter tuning.

Most common infrastructure settings to improve performance

In this section, we list some of the most common settings to consider regarding the infrastructure of your deployments.

Provisioned storage

In MySQL databases, efficient use of the buffer pool, an in-memory cache for frequently accessed data, is essential for optimizing performance. By reducing the number of I/O requests to persistent storage, a well-configured buffer pool significantly improves query response times. For high-volume workloads involving frequent record insertions, updates, or extensive data querying, both a well-configured buffer pool and a high-performance storage solution are required. These workloads benefit from storage with high Input/Output Operations Per Second (IOPS) capabilities to complement the in-memory performance advantages.

When provisioning databases, you should carefully estimate both storage space and performance requirements, including IOPS, throughput, and latency. Amazon RDS offers flexible storage options to meet various workloads needs. General Purpose SSD (gp3) supporting up to 64,000 IOPS, suitable for many applications. Provisioned IOPS (io1 or io2) offer up to 256,000 IOPS with lower and more consistent latency. The io1 and io2 storage types are particularly beneficial for latency-sensitive applications. While both provide similar IOPS capabilities, io2 offers improved performance consistency with lower latency variability compared to io1, making it ideal for workloads requiring predictable performance.

For gp3 storage, which meets the needs of most workloads, the relationship between volume size and performance is important to understand. While gp3 provides a baseline of 3,000 IOPS and 125 MiB/second regardless of volume size, RDS automatically implements volume striping at 400 GiB and above. This striping utilizes four volumes instead of one, quadrupling the baseline performance to 12,000 IOPS and 500 MiB/second. Unlike gp2 where IOPS scale directly with volume size (3 IOPS per GiB), gp3 allows you to independently provision IOPS and throughput regardless of volume size—but the automatic striping at 400 GiB provides a significant performance boost without additional cost. For applications highly sensitive to latency or requiring consistent performance, consider using io1 or io2 storage types. The following image demonstrates how to modify your RDS instance’s allocated storage through the AWS Management Console to leverage automatic volume striping for enhanced performance.

Figure 1: Modifying a database allocated storage to increase performance

EBS optimized instances

The performance of your RDS for MySQL database depends not only on storage configuration but also on your choice of instance type. EC2 instance types vary in their CPU, memory, and network capabilities, all of which affect database performance. Selecting the right instance size makes sure your database has the resources it needs to operate efficiently.

EBS-optimized instances are Amazon EC2 instances that provide dedicated network capacity for Amazon EBS I/O. These instances facilitate a consistent, high-performance connection between your EC2 instance and EBS volumes, allowing your database to fully utilize the provisioned IOPS and throughput. The maximum EBS performance an instance can achieve depends on its size and family. For example, in the r6i instance family, an r6i.8xlarge instance supports up to 40,000 IOPS, while an r6i.12xlarge instance can handle up to 60,000 IOPS. This distinction is important when provisioning high-performance storage. If you configure a gp3 EBS volume with 64,000 IOPS (the maximum allowed), you should select an instance size capable of fully utilizing that capacity. In this scenario, an r6i.8xlarge instance would limit usage to only 40,000 of the 64,000 IOPS provided by the storage, potentially creating a performance bottleneck.

Amazon EC2 burstable performance instances, including the T3 and T4g families, operate with two levels of performance capacity. The first is a baseline level that can be continuously maintained over an extended period. The second is a higher maximum performance level that can only be sustained for 30 minutes within a 24-hour cycle. After that 30-minute burst period, performance reverts back to the baseline level. These instance types are designed to provide a cost-effective solution for workloads that don’t need consistently high CPU performance but may occasionally need to burst to higher performance levels.

You can use the EBSIOBalance% and EBSByteBalance% CloudWatch metrics to determine whether your instances are sized correctly or if the maximum storage performance isn’t being fully utilized because it’s limited by the instance size. Figure 2 depicts a scenario where the EBSByteBalance% has been completely consumed. Despite having a provisioned IOPS of 64,000 and a storage throughput of 4,000 MB per second, the system is experiencing storage contention and causing a degradation on the database performance. In this scenario, consider increasing the instance size, for example from r6i.large to r6i.2xlarge. The following dashboard illustrates how depleted EBSByteBalance% leads to storage performance throttling, causing increased latency and reduced throughput when burst credits are exhausted.

Figure 2: Dashboard showing storage contention because of complete use of EBSByteBalance%

Performance-Enhacing Features in Amazon RDS

Amazon RDS for MySQL offers performance-enhacing features like Optimized Reads, Optimized Writes, Dedicated Log Volumes, to address complex workload challenges.

Optimized Reads leverages NVMe-based SSD block storage physically connected to the host server. This feature is available on instance types with instance storage (those ending with “d” in their name) and can significantly improve read performance, especially for workloads with frequent complex queries generating temporary tables. When large datasets can’t fit into available memory, these temporary tables are created on disk storage. By using instance storage instead of EBS volumes for these operations, Optimized Reads can enhance read performance by up to 50%. When dealing with read-heavy workloads, monitor the created_tmp_disk_tables status variable to determine if your database frequently creates on-disk temporary tables. If this value is consistently increasing by hundreds or thousands per hour during peak operations, or represents more than 10-20% of your total temporary tables (created_tmp_tables), using an instance type with Optimized Reads could provide significant performance benefits. Note: To use Optimize Reads, make sure your MySQL version is 8.0.28 or higher.

Optimized Writes, available on instance types supporting the AWS Nitro System, enhances write performance by avoiding the need for a doublewrite buffer when writing to EBS volumes (more information here). This can lead to substantial improvements in write transaction throughput while maintaining ACID properties. You can verify if it’s enabled by checking if the innodb_doublewrite parameter is set to FALSE (0). Note that enabling this feature requires creating a new instance. For write-intensive applications, consider using DB instance classes compatible with Optimized Writes. Note: To use Optimized Writes, your MySQL version must be 8.0.30 or higher.

Dedicated Log Volumes is another performance-focused feature that separates transaction logs from data storage. By storing logs on a dedicated volume, this feature can improve I/O performance for write-intensive workloads and enhance crash recovery times.

Configuring parameters based on your workload

One benefit of using a managed database service like Amazon RDS is that certain MySQL engine parameters defined in the assigned parameter group are configured automatically according to the selected instance size. This provides you with an initially optimized configuration to take advantage of the available resources. For instance, the innodb_buffer_pool_size parameter, which determines the amount of memory allocated for caching data and indexes, has different defaults between RDS and open-source MySQL. RDS for MySQL sets this to approximately 75% of the instance’s available memory, calculated as {DBInstanceClassMemory*3/4}. This managed service optimization ensures sufficient memory remains for the operating system and monitoring processes. In contrast, open-source MySQL 8.0 defaults to 128MB regardless of available system memory, requiring manual tuning for production workloads. This RDS-optimized default typically provides good performance out-of-the-box for most workloads, though you may need to adjust it lower (to around 70%) if you’re running memory-intensive queries that require substantial temporary table space, or if you’re experiencing memory pressure from concurrent connections.

In the following section, you’ll find the most popular MySQL engine parameters that are set with a static default value that can be tuned for your specific database usage.

Temporary tables

Temporary table management involves two key storage engines, the traditional MEMORY engine and the newer TempTable engine introduced in MYSQL 8.0 with distinct configuration approach. The default values for tmp_table_size and max_heap_table_size vary between MySQL versions and AWS managed services. In MySQL 8.0 open-source, both parameters default to 16,777,216 bytes (16 MB). However, in Amazon RDS for MySQL, these defaults remain at 16 MB across versions which is often too low for complex queries involving large datasets. While AWS provides comprehensive guidance on configuring MEMORY engine parameters, MySQL 8.0’s default TempTable engine introduces additional optimization opportunities.

With MySQL 8.0, the TempTable engine has become the new default storage engine for temporary tables, offering improved performance and scalability. Key parameters for tuning the TempTable engine include:

• temptable_max_ram: Controls the maximum memory used by the TempTable engine across the server (default: 1GB in both RDS and open-source MySQL 8.0)
• temptable_use_mmap: Determines whether memory-mapped files are used for temporary tables (can improve performance for large tables). Default is ON in open-source MySQL 8.0, but RDS for MySQL sets this to OFF by default to ensure consistent performance and avoid potential memory pressure issues in managed environments.
• temptable_max_mmap: Sets the maximum memory for memory-mapped temporary tables when temptable_use_mmap is enabled (default: 1GB in MySQL 8.0; however, this parameter is only relevant when temptable_use_mmap is set to ON)

Note: RDS for MySQL may have different default values than open-source MySQL to optimize for cloud environments. Verify current parameter values in your RDS parameter group, as defaults can vary by instance class and MySQL version.

Fundamental MySQL Performance Parameters

While Amazon RDS automatically configures many parameters based on instance size, several key parameters require manual tuning based on your workload. This section focuses on practical monitoring techniques and RDS-specific configurations that complement AWS’s detailed parameter tunning guide.

RDS Specific Optimizations

Understanding how RDS differs from open-source MySQL helps explain your baseline performance:

• InnoDB Buffer Pool: RDS sets innodb_buffer_pool_size to approximately 75% of instance memory ({DBInstanceClassMemory*3/4}), compared to open-source MySQL’s static 128MB default. This managed service optimization ensures sufficient memory for OS and monitoring processes while maximizing cache efficiency.
• Table Cache Sizing: RDS uses a dynamic formula LEAST({DBInstanceClassMemory/1179121}, 6000) for table_open_cache, unlike open-source MySQL’s static 4,000. For example:
  • 8 GiB instance: ~6,000 tables (cap)
  • 2 GiB instance: ~1,740 tables

Monitoring Table Cache Efficiency

Table caching, controlled by the table_open_cache parameter, is equally important. This parameter defines the number of open tables that can be cached for all threads. Open-source MySQL 8.0 defaults to 4,000 tables, while RDS for MySQL uses a dynamic formula: LEAST({DBInstanceClassMemory/1179121}, 6000), which calculates the optimal value based on available instance memory with a maximum cap of 6,000. To optimize performance, set this parameter to a sufficiently high value that allows the system to keep the frequently accessed tables open.

mysql> show variables like 'table_open_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| table_open_cache | 4000  |
+------------------+-------+
mysql > SHOW GLOBAL STATUS LIKE 'Opened_tables';
+---------------+---------+
| Variable_name |  Value  |
+---------------+---------+
| Opened_tables |  22077  |
+---------------+---------+
mysql > select sleep(60);
+-----------+
| sleep(60) |
+-----------+
|         0 |
+-----------+
mysql > SHOW GLOBAL STATUS LIKE 'Opened_tables';
+---------------+---------+
| Variable_name |  Value  |
+---------------+---------+
| Opened_tables |  22252  |
+---------------+---------+

From these values, you can calculate the number of tables being opened per second (in this example, (22,252-22,077)/60 = 2,91), so you can gradually increase the value of table_open_cache to reduce that ratio to 1 (one table opened per second). An initial estimate for table_open_cache can be the product of the maximum number of concurrent connections and the maximum number of tables per join. Nevertheless, it’s crucial to approach this adjustment cautiously, as increasing table_open_cache can significantly impact memory usage. Monitor total memory consumption and verify sufficient free memory before making substantial increases to this or other memory-intensive parameters.

Best practices for table design

Even though Amazon RDS for MySQL is a managed service, customers are still responsible for the design of their databases and tables schemas so that it can be used and accessed optimally. Here are some of these best practices regarding table designs.

Indexing Best Practices in MySQL

Indexes are critical for optimizing database performance, but they must be designed and implemented carefully. While indexes can significantly enhance data retrieval speed, improper indexing can lead to performance degradation and increased storage overhead. Hereunder we will expose the most common challenges with indexing.

1. Redundant and Overlapping Indexes: many databases suffer from index proliferation, where multiple indexes cover similar or identical column combinations. These redundant indexes consume unnecessary storage space, slow down write operations, increase maintenance overhead and provide minimal additional query performance benefit.

   -- A redundancy example: 
   CREATE INDEX idx_order_date ON orders (order_date);
   CREATE INDEX idx_order_date_status ON orders (order_date, status);
   -- The second index makes the first unnecessary since MySQL can use the leftmost prefix of any composite index.
   -- An overlap example: 
   CREATE INDEX idx_product_category_price ON products(category_id, price);
   CREATE INDEX idx_product_category_brand ON products(category_id, brand_id);
   -- While both indexes include category_id (or state), they optimize different query patterns. The overlap increases storage and write overhead, but removing either could hurt specific query performance. Analyze your query patterns using SHOW INDEX and Performance Schema to determine if the performance benefit justifies the overhead.

2. Long Character Field Indexes: indexing long character fields can be problematic, because it can increase index size and by doing so reduce the indexing efficiency while consuming higher memory and storage leading to potential performance bottlenecks. One recommendation here is the use of prefix indexing for long character fields
3. Unnecessary Indexing: not all columns benefit from indexing. You should consider indexing only when columns frequently use ‘WHERE’ clauses, JOIN conditions, sorting (ORDER BY) operations or columns with high cardinality (many unique values)

Best Practices for Effective Indexing:

• Analyze query patterns before creating indexes
• Use composite indexes strategically
• Regularly review and remove unused indexes
• Balance read and write performance
• Consider index maintenance overhead
• Use EXPLAIN to understand query execution plans

You should regularly check index usage statistics to identify and remove unnecessary indexes. For RDS for MySQL instances, identifying unused indexes requires proper Performance Schema configuration. First, make sure Performance Schema is enabled (performance_schema = ON in your parameter group), then verify that the required statement consumers and instruments are active:

-- Verify statement consumers are enabled (usually enabled by default in RDS) 
SELECT NAME, ENABLED FROM performance_schema.setup_consumersWHERE NAME LIKE 'events_statements%';

-- Enable statement instrumentation if needed
UPDATE performance_schema.setup_instrumentsSET ENABLED = 'YES', TIMED = 'YES'WHERE NAME LIKE 'statement/%';

After Performance Schema has collected sufficient data (typically after running through your normal workload cycles), query the sys.schema_unused_indexes view to identify unused indexes. Keep in mind this view will be empty immediately after enabling Performance Schema or after a restart – it requires time to gather meaningful usage statistics. Additionally, consider seasonal workloads as an index might appear unused during normal operations but could be critical for periodic processes like month-end reporting.

With Amazon RDS, you can use Performance Insights to identify performance bottlenecks and high-load SQL queries, complementing your index analysis. Periodically reviewing and pruning unnecessary indexes based on these insights can significantly improve write performance and reduce storage costs.

Covering indexes

Additionally, to implement the correct number of indexes, you can further optimize access to your tables by using covering indexes. Covering indexes include all the columns retrieved by a query. Instead of using the index values as pointers to find the full table rows, the query returns values directly from the index structure, saving disk I/O operations.

As an example of these possible optimizations, consider a wide table with many columns:

CREATE TABLE `customers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `first_name` varchar(20) NOT NULL,
  `last_name` varchar(20) NOT NULL,
  `age` int NOT NULL,
  `email` varchar(100) NOT NULL,
  `address` varchar(200) NOT NULL,
  `city` varchar(50) NOT NULL,
  `state` varchar(20) NOT NULL,
  `zip` varchar(10) NOT NULL,
  `phone` varchar(15) NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB

If you frequently run a query that only needs a few columns:

SELECT first_name, last_name, age FROM customer WHERE last_name = ‘xxx’;

Instead of accessing the full table rows, you can create a covering index that includes just the columns needed:

CREATE INDEX idx_firstname_covering ON customers(last_name, first_name, age)

To confirm that your covering index is being used effectively, use the EXPLAIN command and look for the “Using index” indicator in the Extra column:

EXPLAIN SELECT first_name, last_name, age FROM customers WHERE last_name = 'Smith';
+----+-------------+-----------+------+----------------------
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
+----+-------------+-----------+------+----------------------
|1|SIMPLE|customers|ref| idx_lastname_covering| idx_lastname_covering|82|const|5|Using index|
+----+-------------+-----------+------+----------------------

The “Using index” in the Extra column confirms that MySQL is satisfying the query entirely from the index without accessing the table data – this is called an “index-only scan” or “covering index scan.”

If the covering index is NOT working, you would see either no value or “Using where” in the Extra column, indicating table access is required:

--Example without covering index (if we query additional columns not in the index)
EXPLAIN SELECT first_name, last_name, age, email FROM customers WHERE last_name = 'Smith';
+----+-------------+-----------+------+----------------------
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
+----+-------------+-----------+------+----------------------
|1|SIMPLE|customers|ref| idx_lastname_covering| idx_lastname_covering|82|const|5|Using index|
+----+-------------+-----------+------+----------------------

Notice the absence of “Using index” – MySQL must access the table to retrieve the email column that’s not included in our covering index. This is particularly beneficial for wide tables where each row contains much more data than what’s needed by your query. Alternatively, for a simpler table structure, you could optimize by reconsidering your primary key structure:

CREATE TABLE `customers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `first_name` varchar(20) NOT NULL,
  `last_name` varchar(20) NOT NULL,
  `age` int NOT NULL,
   PRIMARY KEY (`last_name`,`id`),
) ENGINE=InnoDB

With this structure, queries filtering on last_name would be covered by the primary key index itself.

EXPLAIN SELECT last_name, id FROM customers WHERE last_name = 'Smith';
+----+-------------+-----------+------+----------------------
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
+----+-------------+-----------+------+----------------------
|1|SIMPLE|customers|ref| PRIMARY|PRIMARY|82|const|5|Using index|
+----+-------------+-----------+------+----------------------

As a best practice, use covering indexes selectively for frequently run queries that select a small subset of columns from wider tables. This approach improves query performance by reducing I/O operations while avoiding the antipattern of duplicating most or all of your table data in secondary indexes.

Finding and tuning non-optimized queries

When aiming for peak performance, it is important to identify and fine-tune the queries that are both most frequent and resource-intensive within your workloads. For this purpose, you can apply the following recommendations.

Enable slow query logs

One way to detect queries that are candidates for optimization is a native option of the MySQL engine called slow query logs. This option is disabled by default in both RDS for MySQL and open-source MySQL (slow_query_log=0), but can be enabled by setting the parameter slow_query_log=1. In the following figure, we demonstrate how to enable slow query logs in the RDS parameter group to begin capturing queries that exceed your performance thresholds.

Figure 3: Enabling slow query logs

For optimal performance, configure MySQL to write slow query logs to files rather than tables by setting log_output=FILE.
Important: While open-source MySQL defaults to FILE, RDS for MySQL defaults to TABLE for easier log management through the AWS console. However, file-based logging is recommended for production environments to avoid the performance bottleneck that can occur with table-based logging.

When slow_query_logs is enabled, queries that take longer than the value set in the parameter long_query_time are logged. The default value is 10 seconds in both RDS and open-source MySQL. Depending on your database usage, this value might be too high for effective optimization. Consider lowering it to 2 seconds to capture more potentially optimizable queries. In the following figure, we configure the long_query_time parameter to define the threshold in seconds for capturing queries in the slow query log.

Figure 4: Setting the long_query_time value

Another related parameter to the slow query log is log_queries_not_using_indexes, which when set to 1 (default is 0 in both RDS and open-source MySQL), logs queries that don’t use indexes regardless of their execution time. This can help identify queries that might benefit from index optimization.

Note: If you need to use table-based logging for specific requirements (setting log_output=TABLE), be aware that:

• Slow queries would be written to the mysql.slow_log table (not mysql.slow_logs)
• This can create performance overhead on busy systems
• You would query it using: SELECT * FROM mysql.slow_log
• However, file-based logging is recommended for production environments to minimize performance impact

In the following figure, we enable log_queries_not_using_indexes to capture queries performing full table scans, helping identify opportunities for index optimization.

Figure 5: Setting the log_queries_not_using_indexes value

After these parameters are set, you can download the slow query log files from your RDS instance using the AWS Management Console, AWS CLI, or RDS API, and analyze them using tools like mysqldumpslow or pt-query-digest from Percona Toolkit

For more information, see RDS for MySQL slow query logs.

Cloudwatch Database Insights

Amazon RDS for MySQL integrates with Amazon CloudWatch to provide comprehensive database monitoring capabilities. CloudWatch collects and tracks metrics, collects and monitors log files, and sets alarms for your RDS instances. Through CloudWatch, you can monitor key performance metrics such as CPU utilization, database connections, read/write IOPS, and network throughput to identify performance bottlenecks in your MySQL database.

CloudWatch also enables you to analyze database activity patterns over time through its metrics retention capabilities. You can view historical data for up to 15 months, allowing you to identify trends and correlate performance issues with specific time periods. This historical analysis is particularly valuable when combined with slow query logs, as it helps you pinpoint when performance degradation occurred and focus your optimization efforts on the queries that were running during those specific timeframes.

Additionally, CloudWatch Logs Insights provides a powerful query language that allows you to analyze your MySQL slow query logs and error logs directly. You can write queries to identify the most resource-intensive SQL statements, detect patterns in database errors, and track query execution times across different time periods, making it easier to prioritize which queries need optimization.

Conclusion

The deployment settings and parameter options mentioned in this post are the most common configurations to consider to get the best performance from your RDS for MySQL instance.

Performance optimization is an ongoing process that requires monitoring and adjustment based on your specific workload patterns. To dive deeper into MySQL performance tuning strategies, explore the Amazon RDS for MySQL Documentation and the AWS Database Blog for additional best practices and real-world optimization techniques.

We’d love to hear about your experiences optimizing RDS for MySQL. Share your performance tuning tips and questions in the comments below.

About the authors