Optimizing MySQL Performance in AWS RDS — Insights by Jamaurice Holt

Optimizing MySQL Performance in AWS RDS — Insights by Jamaurice Holt

Cloud databases are the backbone of modern applications, but performance tuning often separates a sluggish app from a lightning-fast one. I’ve spent years architecting and managing large-scale AWS environments, including high-traffic SaaS platforms, financial systems, and enterprise workloads. In this post, I’ll share practical insights on tuning MySQL performance in AWS RDS that are fresh, relevant, and field-tested in 2025.

Why AWS RDS for MySQL?

Amazon RDS is the go-to for many teams because it abstracts away the heavy lifting of backups, patching, and scaling. But “managed” doesn’t mean “optimized.”

In fact, I often see teams run into:

• Slow queries due to poor indexing
• Inefficient parameter groups
• Temp table and I/O bottlenecks
• Missing observability into query execution The good news: with a few targeted adjustments, you can get 30–50% performance gains without touching hardware.

Step 1: Use Performance Insights (Properly)

Many teams turn on Performance Insights but don’t drill deeper. In 2025, AWS has expanded PI’s query digest features to include plan visualization for MySQL 8.0.

👉 What I recommend:

• Identify your Top 5 slow queries every week.
• Look for “full table scans” and “temporary table on disk” flags.
• Tie queries back to application features so you fix problems at the source, not just in the DB.

Step 2: Tune Parameters for Your Workload

AWS gives you a default parameter group, but it’s a blunt instrument. For MySQL 8.0 in RDS, I always customize:

• innodb_buffer_pool_size: Target 70–80% of available memory.
• innodb_flush_log_at_trx_commit=2: A safe trade-off for many SaaS workloads.
• max_connections: Set realistically based on app connection pool, not guesses.
• internal_tmp_mem_storage_engine=MEMORY: Keeps temp tables in RAM where possible. 📌 Pro tip: For write-heavy apps, increase innodb_log_file_size and enable parallel replication.

Step 3: Index Smarter, Not Harder

Throwing indexes at every query is a recipe for bloat. Instead, I use a 3-step test:

1. $1
2. $1
3. $1 Recently, I added a composite index on (home_loan_id, user_id, unassigned_date_time) that cut query latency from 2.4s to 120ms in production.

How MySQL 5.7 vs 8.0 Changes Indexing Strategy

One of the most overlooked performance shifts after upgrading from MySQL 5.7 to MySQL 8.0 is how the optimizer leverages indexes. Some of the biggest differences include:

🔹 Native Descending Indexes

• In 5.7: Every index was stored in ascending order, even if you declared DESC. Queries like ORDER BY created_at DESC LIMIT 10 often fell back to filesort instead of using the index.
• In 8.0: Descending indexes are stored natively. Now you can explicitly build (col DESC) indexes to avoid sort overhead. This alone fixes tons of “why is my ORDER BY slow?” complaints after migration.

🔹 Invisible Indexes (Test Without Risk)

• In 5.7: Dropping an index was permanent, and the only way to test was to remove it and hope the workload didn’t crash.
• In 8.0: You can mark indexes as INVISIBLE, meaning the optimizer ignores them, but they still exist physically. Perfect for safely testing index removal during migrations.

🔹 Functional Indexes (Generated Columns + Index)

• In 5.7: You had to create a generated column and then index that column if you wanted to index expressions (e.g., LOWER(email)). Clunky and not widely used.
• In 8.0: Functional indexes let you directly index expressions like:
• CREATE INDEX idx_lower_email ON users ((LOWER(email)));
• This reduces complexity and lets the optimizer exploit expression-based lookups.

🔹 Histograms for the Optimizer

• In 5.7: The optimizer relied almost exclusively on index statistics (cardinality estimates from index trees). Complex predicates sometimes produced poor plans.
• In 8.0: You can create histograms on columns without indexing them, improving selectivity estimates. This often reduces the number of “wrong index chosen” cases.
• ANALYZE TABLE users UPDATE HISTOGRAM ON age WITH 10 BUCKETS;

🔹 Improved Index Merge Strategy

• In 5.7: Index Merge (using multiple indexes in one query) was often inefficient, especially with OR conditions.
• In 8.0: The optimizer’s index merge logic is more advanced, and in many cases can choose smarter partial index scans to avoid full scans.

Why This Matters in AWS RDS

When running on RDS or Aurora, these differences are magnified:

• Your EXPLAIN plans may change dramatically after upgrading to MySQL 8.0. Queries that ran fine in 5.7 might suddenly prefer a different index — or drop an index usage entirely.
• Features like descending indexes and functional indexes mean you may want to revisit schema design and add new indexes that weren’t possible before.
• Invisible indexes give you a safe way to tune in production without the downtime risk you had in 5.7. In short: don’t just migrate — re-tune. MySQL 8.0’s index improvements can give you performance gains you never had access to in 5.7, but only if you use them deliberately.

⚡ Pro tip: After a 5.7 → 8.0 migration in RDS, always run:

ANALYZE TABLE <table_name>;to refresh statistics, then review Performance Insights to confirm the optimizer is actually choosing the new paths.

Step 4: Monitor Storage & Temp Tables

In RDS, you don’t control the file system, but you do control temp table behavior.

• Watch for Created_tmp_disk_tables in CloudWatch metrics.
• If the ratio is >25% of total temp tables, you’re thrashing disk.
• Solution: increase tmp_table_size, max_heap_table_size, and temptable_max_ram. In one case, bumping these limits saved a client from costly IOPS upgrades.

Step 5: Scale Out Before You Scale Up

Vertical scaling (bigger instance class) is easy — but expensive. Before spending more:

• Use read replicas for reporting workloads.
• Offload analytics to Aurora Read Replicas or Redshift Spectrum.
• Cache aggressively with ElastiCache (Redis/Memcached). Remember: AWS pricing grows steeply with instance size. Smarter architecture is almost always cheaper.

Emerging Best Practices in 2025

Here are trends I see shaping MySQL RDS performance this year:

• Aurora MySQL 3.x adoption for better query parallelism.
• AI-assisted tuning tools (like Amazon DevOps Guru for RDS).
• Serverless caching layers that auto-scale alongside RDS.
• Greater use of multi-AZ with cluster endpoints for high-availability.
• Histograms + invisible indexes becoming standard in tuning playbooks after 5.7 → 8.0 migrations.

Final Thoughts

Performance tuning is not a one-time task — it’s a practice. By combining query analysis, parameter tuning, and smart scaling, you can stretch RDS far beyond its defaults.

As I tell my clients: don’t let AWS set your performance ceiling — tune MySQL to match your business, not the other way around.

✍️ Written by Jamaurice Holt, Senior Database Architect & AWS Solutions Expert. For more insights, visit jamauriceholt.com or connect with me on LinkedIn.

• MySQL
• AWS
• Cloud Architecture
• Database Performance
• DevOps Jamaurice Holt | Senior AWS Database Administrator & Full-Stack Developer

Originally published on Medium