MySQL Performance Optimization for AWS RDS: Tuning Guide 2025

RDS MySQL performance tuning has a different set of levers than self-managed MySQL — you can't change the kernel, can't tune OS parameters, and many MySQL system variables are locked by AWS. But the variables you can tune matter a lot: innodb_buffer_pool_size, max_connections, slow query log configuration, read replica routing, and instance right-sizing together account for most RDS MySQL performance problems. This guide covers the ones that actually move the needle.

Whether you're running MySQL on Amazon RDS, managing on-premise deployments, or operating hybrid database fleets, the principles of performance optimization remain consistent. This guide explores proven strategies that deliver measurable improvements in query response times, resource utilization, and overall system efficiency.

Infrastructure-Level Optimizations

Understanding Storage Performance Requirements

The foundation of database performance lies in properly provisioned storage. MySQL's buffer pool serves as an in-memory cache for frequently accessed data, but when data cannot fit in memory or when handling write-intensive workloads, storage IOPS and throughput become critical performance factors.

Storage Types and Performance Characteristics

Modern cloud environments offer multiple storage options with distinct performance profiles:

General Purpose SSD (gp3):

Baseline: 3,000 IOPS and 125 MiB/s regardless of volume size
Maximum: Up to 64,000 IOPS
Auto-striping: At 400 GiB and above, automatic volume striping across four volumes provides 12,000 IOPS and 500 MiB/s baseline without additional cost
Independent provisioning: IOPS and throughput can be configured independently of volume size

Provisioned IOPS (io1/io2):

Maximum: Up to 256,000 IOPS
Lower latency: More consistent performance with reduced latency variability
Use cases: Latency-sensitive applications requiring predictable performance
io2 advantage: Improved performance consistency over io1

Practical Storage Sizing Considerations

When provisioning storage, consider both capacity and performance requirements:

sql

-- Monitor current I/O patterns
SELECT 
    table_schema,
    table_name,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb,
    table_rows,
    ROUND(((data_length + index_length) / table_rows), 2) AS avg_row_size
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'performance_schema', 'information_schema', 'sys')
ORDER BY (data_length + index_length) DESC
LIMIT 20;

Key decision points:

For gp3 volumes, provisioning 400 GiB+ leverages automatic striping for 4x baseline performance
Unlike gp2 (3 IOPS per GiB), gp3 decouples IOPS from volume size
Monitor actual IOPS usage patterns before over-provisioning
Consider io2 for applications where 99.9th percentile latency matters

Instance Selection and EBS Optimization

Storage performance is constrained by both the EBS volume configuration and the EC2 instance capabilities. An instance must have sufficient EBS bandwidth to utilize provisioned storage performance.

Understanding Instance EBS Limits

Each instance type has maximum EBS performance limits:

Instance TypeMax IOPSMax Throughputr6i.8xlarge40,0005,000 MiB/sr6i.12xlarge60,0007,500 MiB/sr6i.16xlarge80,00010,000 MiB/s

Critical insight: If you provision gp3 storage with 64,000 IOPS on an r6i.8xlarge instance (40,000 IOPS limit), you're effectively paying for 24,000 unused IOPS. The instance becomes the bottleneck, not the storage.

Monitoring EBS Performance Metrics

CloudWatch provides critical metrics for identifying instance-level storage bottlenecks:

EBSIOBalance% - I/O burst bucket balance EBSByteBalance% - Throughput burst bucket balance

When these metrics approach zero, your instance is throttling storage performance. This manifests as:

Increased query latency
Reduced throughput even with high IOPS provisioning
Performance degradation during peak load

Resolution strategy:

Current: r6i.large (instance limit: 10,000 IOPS)
Provisioned: 64,000 IOPS gp3 storage
Problem: EBSByteBalance% = 0%, storage throttling

Solution: Upgrade to r6i.2xlarge (instance limit: 20,000 IOPS)
Result: 2x IOPS capacity, eliminated throttling

Burstable Performance Instances

T3 and T4g instance families operate differently:

Baseline performance: Continuously sustainable
Burst capacity: 30 minutes per 24-hour period
Post-burst behavior: Performance drops to baseline

When to avoid: Production workloads requiring consistent performance When to consider: Development environments, staging instances with intermittent load

Performance-Enhancing Features

Modern database platforms offer specialized features to address specific workload challenges:

Optimized Reads

Mechanism: Leverages NVMe-based SSD block storage physically attached to the host server

Performance impact:

Up to 50% improvement for read-heavy workloads
Eliminates EBS I/O for temporary table operations
Particularly beneficial for complex queries generating large temp tables

Identifying candidates for Optimized Reads:

sql

-- Check frequency of on-disk temporary tables
SHOW GLOBAL STATUS LIKE 'Created_tmp%';

-- Calculate on-disk temp table ratio
SELECT 
    ROUND(
        (Created_tmp_disk_tables / Created_tmp_tables) * 100, 2
    ) AS disk_temp_ratio_pct
FROM (
    SELECT 
        VARIABLE_VALUE AS Created_tmp_disk_tables
    FROM performance_schema.global_status 
    WHERE VARIABLE_NAME = 'Created_tmp_disk_tables'
) t1,
(
    SELECT 
        VARIABLE_VALUE AS Created_tmp_tables
    FROM performance_schema.global_status 
    WHERE VARIABLE_NAME = 'Created_tmp_tables'
) t2;

Action threshold: If disk_temp_ratio_pct > 20% or absolute numbers show hundreds/thousands per hour during peak operations, Optimized Reads provides significant benefits.

Requirements:

Instance types with instance storage (names ending in "d")
MySQL 8.0.28 or higher

Optimized Writes

Mechanism: Eliminates doublewrite buffer requirement through AWS Nitro System integration

Benefits:

Improved write transaction throughput
Maintains ACID properties
No application changes required

Verification:

sql

-- Check if Optimized Writes is enabled
SHOW VARIABLES LIKE 'innodb_doublewrite';
-- Expected value: OFF (0) when Optimized Writes is active

Requirements:

AWS Nitro System-compatible instances
MySQL 8.0.30 or higher
Requires new instance creation (not in-place upgrade)

Dedicated Log Volumes

Purpose: Separates transaction logs from data storage

Advantages:

Improved I/O performance for write-intensive workloads
Faster crash recovery
Reduced I/O contention on data volumes

Best suited for:

High transaction rate applications
Workloads with heavy write activity
Systems requiring rapid recovery time

Parameter Configuration for Workload Optimization

Default RDS Optimizations vs. Open-Source MySQL

Understanding the differences between managed service defaults and vanilla MySQL helps explain baseline performance characteristics:

Buffer Pool Configuration

Open-source MySQL 8.0:

sql

innodb_buffer_pool_size = 128MB  -- Static default

Amazon RDS for MySQL:

sql

innodb_buffer_pool_size = {DBInstanceClassMemory*3/4}  -- ~75% of instance memory

Why this matters:

Open-source MySQL requires manual tuning for production workloads
RDS automatically optimizes for instance size
Remaining 25% handles OS, monitoring, connection memory

When to adjust lower (to ~70%):

Memory-intensive queries requiring large temp table space
High concurrent connection counts
Experiencing memory pressure or OOM events

Table Cache Sizing

Open-source MySQL 8.0:

sql

table_open_cache = 4000  -- Static value

Amazon RDS for MySQL:

sql

table_open_cache = LEAST({DBInstanceClassMemory/1179121}, 6000)

Practical examples:

8 GiB instance: ~6,000 tables (capped)
2 GiB instance: ~1,740 tables
16 GiB instance: ~6,000 tables (capped)

Temporary Table Configuration

MySQL 8.0 introduced the TempTable engine as the default for in-memory temporary tables, offering improved performance over the legacy MEMORY engine.

Key Parameters for TempTable Engine

temptable_max_ram

Controls maximum memory for TempTable engine server-wide
Default: 1 GB (both RDS and open-source MySQL 8.0)
Tuning consideration: Increase for workloads with large aggregations, complex JOINs, or GROUP BY operations

temptable_use_mmap

Determines use of memory-mapped files for overflow
Open-source MySQL 8.0: ON (default)
Amazon RDS: OFF (default, to avoid memory pressure in managed environments)
Set to ON for better large temp table performance if instance has sufficient memory

temptable_max_mmap

Maximum memory for memory-mapped temp tables
Default: 1 GB
Only relevant when temptable_use_mmap = ON

Legacy MEMORY Engine Parameters

For completeness, the MEMORY engine (still used in some scenarios) is controlled by:

tmp_table_size: 16 MB (default, both RDS and open-source)
max_heap_table_size: 16 MB (default, both RDS and open-source)

Common issue: Default 16 MB is too low for complex queries with large result sets

Recommended approach:

sql

-- For workloads with complex aggregations
SET GLOBAL tmp_table_size = 256M;
SET GLOBAL max_heap_table_size = 256M;

-- For TempTable engine
SET GLOBAL temptable_max_ram = 2G;
SET GLOBAL temptable_use_mmap = ON;
SET GLOBAL temptable_max_mmap = 4G;

Monitoring Table Cache Efficiency

Proper table cache sizing prevents constant opening/closing of table files, which degrades performance.

Diagnostic Query

sql

-- Check current table_open_cache setting
SHOW VARIABLES LIKE 'table_open_cache';

-- Check how many times tables have been opened
SHOW GLOBAL STATUS LIKE 'Opened_tables';

-- Wait 60 seconds
SELECT SLEEP(60);

-- Check again
SHOW GLOBAL STATUS LIKE 'Opened_tables';

Calculating Tables Opened Per Second

Example output:
Initial: Opened_tables = 22,077
After 60s: Opened_tables = 22,252
Calculation: (22,252 - 22,077) / 60 = 2.92 tables/second

Target: Reduce to ≤ 1 table opened per second by incrementally increasing table_open_cache

Initial sizing estimate:

table_open_cache = max_connections × max_tables_per_join

Important: Monitor total memory usage before increasing. Each open table consumes memory for:

File descriptors
Table definition cache
Index statistics

Indexing Best Practices

Indexes are critical for query performance, but improper indexing creates overhead without benefits. A methodical approach to index design prevents common pitfalls.

Common Indexing Antipatterns

1. Redundant Indexes

Problem: Multiple indexes covering identical or overlapping column combinations

sql

-- ANTIPATTERN: First index is completely redundant
CREATE INDEX idx_order_date ON orders (order_date);
CREATE INDEX idx_order_date_status ON orders (order_date, status);

-- MySQL can use the leftmost prefix of idx_order_date_status
-- for queries filtering only on order_date

Why this hurts:

Increased storage consumption
Slower write operations (INSERT, UPDATE, DELETE)
Higher maintenance overhead
Minimal query performance benefit

2. Overlapping Indexes

Scenario: Indexes sharing common prefixes but serving different query patterns

sql

-- Both indexes start with category_id
CREATE INDEX idx_product_category_price ON products(category_id, price);
CREATE INDEX idx_product_category_brand ON products(category_id, brand_id);

Analysis approach:

Review query patterns using SHOW INDEX and Performance Schema
Determine if both indexes are actively used
Evaluate if performance benefit justifies storage/write overhead
Consider composite index redesign if queries can be consolidated

3. Long Character Field Indexes

Problem: Full-column indexes on VARCHAR(500) or TEXT columns

Impacts:

Massive index size growth
Reduced cache efficiency (fewer index pages fit in buffer pool)
Higher memory and storage consumption
Degraded index scan performance

Solution: Prefix Indexing

sql

-- Instead of indexing entire column
CREATE INDEX idx_description ON products(description);

-- Use prefix index
CREATE INDEX idx_description_prefix ON products(description(50));

-- Analyze prefix selectivity
SELECT 
    COUNT(DISTINCT LEFT(description, 50)) AS prefix_distinct,
    COUNT(DISTINCT description) AS full_distinct,
    COUNT(*) AS total_rows,
    ROUND(
        COUNT(DISTINCT LEFT(description, 50)) / COUNT(DISTINCT description) * 100,
        2
    ) AS selectivity_pct
FROM products;

Prefix length selection: Choose length where selectivity_pct > 90%

Identifying Unused Indexes

Performance Schema enables systematic unused index detection.

Setup Requirements

sql

-- Verify Performance Schema is enabled
SHOW VARIABLES LIKE 'performance_schema';

-- Check statement consumers (usually enabled by default in RDS)
SELECT NAME, ENABLED 
FROM performance_schema.setup_consumers
WHERE NAME LIKE 'events_statements%';

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

Finding Unused Indexes

sql

-- After sufficient data collection (days/weeks of typical workload)
SELECT * FROM sys.schema_unused_indexes;

Important considerations:

Allow time for Performance Schema to collect usage statistics
Consider seasonal workloads (month-end processing, quarterly reports)
Empty results immediately after restart or enabling Performance Schema
Review business context before dropping indexes

Covering Indexes

Covering indexes include all columns required by a query, eliminating the need to access table data.

Example: Wide Table Optimization

Table structure:

sql

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;

Frequent query:

sql

SELECT first_name, last_name, age 
FROM customers 
WHERE last_name = 'Smith';

Without covering index:

MySQL uses index on last_name (if exists) to find matching rows
Reads full table rows to retrieve first_name and age
Incurs additional I/O operations

With covering index:

sql

CREATE INDEX idx_lastname_covering 
ON customers(last_name, first_name, age);

Verification using EXPLAIN:

sql

EXPLAIN SELECT first_name, last_name, age 
FROM customers 
WHERE last_name = 'Smith';

+------+-------------+-----------+------+------------------------+
| id   | select_type | table     | type | possible_keys          |
+------+-------------+-----------+------+------------------------+
| 1    | SIMPLE      | customers | ref  | idx_lastname_covering  |
+------+-------------+-----------+------+------------------------+

+------------------------+--------+-------+------+-------------+
| key                    | key_len| ref   | rows | Extra       |
+------------------------+--------+-------+------+-------------+
| idx_lastname_covering  | 82     | const | 5    | Using index |
+------------------------+--------+-------+------+-------------+

Key indicator: Using index in the Extra column confirms index-only scan

Without covering index:

sql

-- Query selecting additional column not in index
EXPLAIN SELECT first_name, last_name, age, email 
FROM customers 
WHERE last_name = 'Smith';

-- Notice absence of "Using index" - requires table access for email column

Alternative: Primary Key Optimization

For simple cases, restructure the primary key:

sql

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;

Benefit: Queries filtering on last_name are covered by the primary key itself

Verification:

sql

EXPLAIN SELECT last_name, id 
FROM customers 
WHERE last_name = 'Smith';

-- Shows "Using index" with PRIMARY key

Index Design Best Practices

Strategic implementation:

Analyze query patterns before creating indexes
Use composite indexes for multi-column filters
Order composite index columns by selectivity (most selective first)
Regularly review and remove unused indexes
Balance read and write performance requirements

When to index:

Columns frequently used in WHERE clauses
JOIN conditions
ORDER BY operations
Columns with high cardinality (many unique values)

When NOT to index:

Columns with low cardinality (e.g., boolean, status with 2-3 values)
Columns rarely used in queries
Small tables (full table scan may be faster)
Columns frequently updated (high write overhead)

Query Performance Analysis

Enabling Slow Query Logs

Slow query logs identify queries requiring optimization.

Configuration Parameters

slow_query_log

Default: 0 (disabled) in both RDS and open-source MySQL
Set to 1 to enable logging

log_output

Default: TABLE (RDS), FILE (open-source MySQL)
Recommended: FILE for production (better performance)
When set to TABLE, queries logged to mysql.slow_log

long_query_time

Default: 10 seconds (both RDS and open-source)
Recommended: 2-5 seconds for better optimization coverage
Queries exceeding this threshold are logged

log_queries_not_using_indexes

Default: 0 (disabled)
Set to 1 to log queries performing full table scans
Helps identify missing index opportunities

Configuration Example

sql

-- Enable slow query logging
SET GLOBAL slow_query_log = 1;
SET GLOBAL log_output = 'FILE';
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = 1;

For RDS: Modify parameter group and reboot instance

Analyzing Slow Query Logs

Download logs from RDS:

AWS Management Console: RDS → Logs & events
AWS CLI: aws rds download-db-log-file-portion
RDS API

Analysis tools:

bash

# mysqldumpslow - built-in MySQL utility
mysqldumpslow -s t -t 10 /path/to/slow-query.log
# -s t: Sort by query time
# -t 10: Show top 10 queries

# pt-query-digest - Percona Toolkit (more detailed)
pt-query-digest /path/to/slow-query.log > analysis_report.txt

pt-query-digest provides:

Query patterns (normalized queries)
Execution time statistics (min, max, avg, 95th percentile)
Query frequency
Total time impact (frequency × avg execution time)
EXPLAIN plan analysis

CloudWatch Database Insights

CloudWatch complements slow query logs with infrastructure-level monitoring and historical trend analysis.

Key Performance Metrics

Resource utilization:

CPUUtilization: Sustained >80% indicates compute bottleneck
FreeableMemory: Low values suggest buffer pool pressure
DatabaseConnections: Track connection pool efficiency

I/O metrics:

ReadIOPS / WriteIOPS: Compare against provisioned limits
ReadLatency / WriteLatency: Identify storage performance issues
ReadThroughput / WriteThroughput: Monitor bandwidth utilization

Database-specific:

DiskQueueDepth: >10 consistently indicates I/O bottleneck
NetworkReceiveThroughput / NetworkTransmitThroughput: Network saturation

Historical Analysis Strategy

Correlation approach:

Identify performance degradation period in CloudWatch
Download slow query logs for that time range
Analyze query patterns during incident
Cross-reference with infrastructure metrics

Example scenario:

Timeline:
- 14:00-14:30: Spike in ReadLatency (50ms → 200ms)
- 14:05-14:25: CPUUtilization peaks at 95%
- 14:00-14:30: DiskQueueDepth increases to 25

Analysis:
- Download slow query log covering 13:45-14:45
- Filter for queries in degraded time window
- Identify: Full table scan on 50M row table
- Resolution: Add missing composite index

CloudWatch Logs Insights

Query slow query logs directly from CloudWatch:

# Find slowest queries in last hour
fields @timestamp, @message
| filter @message like /Query_time/
| parse @message /Query_time: (?<query_time>[\d.]+)/
| sort query_time desc
| limit 20

# Count queries by type
fields @timestamp, @message
| filter @message like /SELECT|UPDATE|INSERT|DELETE/
| parse @message /(?<query_type>SELECT|UPDATE|INSERT|DELETE)/
| stats count() by query_type

# Identify queries not using indexes
fields @timestamp, @message
| filter @message like /Rows_examined/
| parse @message /Rows_examined: (?<rows_examined>\d+)/
| filter rows_examined > 10000
| sort rows_examined desc

Performance Optimization Workflow

Systematic Approach

1. Infrastructure Foundation

Provision appropriate storage type and IOPS
Select instance size matching EBS requirements
Enable performance-enhancing features (Optimized Reads/Writes)
Monitor EBS balance metrics

2. Parameter Tuning

Review buffer pool configuration
Adjust table cache based on usage patterns
Configure temporary table settings for workload
Enable slow query logging

3. Index Optimization

Identify unused indexes via Performance Schema
Remove redundant and overlapping indexes
Create covering indexes for frequent queries
Implement prefix indexes for long character fields

4. Query Analysis

Analyze slow query logs systematically
Use EXPLAIN to understand query execution plans
Correlate query performance with infrastructure metrics
Prioritize optimization by total time impact (frequency × execution time)

5. Continuous Monitoring

Track performance trends over time
Alert on critical metrics (CPU, IOPS, connections)
Review Performance Schema insights regularly
Adjust configuration as workload evolves

Prioritization Framework

High impact optimizations:

Missing indexes causing full table scans on large tables
Instance EBS throttling (depleted balance metrics)
Memory pressure forcing disk-based operations
Redundant indexes slowing write operations

Medium impact: 5. Suboptimal parameter values for workload type 6. Covering index opportunities for frequent queries 7. Storage type mismatch (gp3 vs io2)

Low impact: 8. Minor parameter tweaks within optimal ranges 9. Indexing low-cardinality columns 10. Over-optimization of infrequent queries

Conclusion

MySQL performance optimization requires a comprehensive approach spanning infrastructure configuration, parameter tuning, and database design. While managed services like Amazon RDS provide optimized defaults, achieving peak performance demands understanding your specific workload characteristics and systematically addressing bottlenecks.

Key takeaways:

Infrastructure matters: Properly provisioned storage and instance sizing prevents bottlenecks that parameter tuning cannot resolve
Monitoring drives optimization: Performance Schema, slow query logs, and CloudWatch provide the data needed for informed decisions
Indexes require discipline: Strategic index creation and regular removal of unused indexes balances read and write performance
Configuration is workload-specific: Default parameters work well for many cases, but custom workloads benefit from targeted tuning
Continuous improvement: Performance optimization is ongoing—monitor trends, analyze changes, and adapt configuration as workloads evolve

For database reliability engineers managing production MySQL fleets, mastering these optimization strategies ensures applications receive the performance they require while maintaining efficient resource utilization and cost-effectiveness.

References

This blog post provides practical guidance for database reliability engineers optimizing MySQL performance in production environments. For questions or to share your optimization experiences, connect with the JusDB team.

Working with JusDB on RDS Performance

RDS MySQL performance problems are almost always solvable without upgrading to a larger instance — it's usually a configuration issue, a missing index, or a connection pool problem. We diagnose and tune RDS MySQL as part of our AWS database consulting work. Reach out for a performance review.

MySQL Performance Optimization for AWS RDS