AWS RDS MySQL 5.6调优求助：写入IOPS不稳定及性能问题

Hey there, let's break down each of your questions based on my hands-on experience with MySQL migrations to AWS RDS:

1. Is splitting large queries into temporary tables reasonable? What factors (other than indexes) cause queries to use temporary files? #

Reasonability of splitting large queries into temporary tables #

It depends on your specific scenario:

• If your large queries are causing excessive memory usage, long lock times, or repeated expensive computations (like complex aggregations across big datasets), splitting them into temporary tables can be a smart move. For example, you can first pull filtered data into a temporary table (with appropriate indexes) and then run smaller, targeted queries against it. This reduces the load on the database for single query executions and can make complex logic easier to debug.
• That said, overusing disk-based temporary tables (like MyISAM) can introduce extra IO overhead from table creation, writing, and deletion. If your application has frequent bursty query patterns, this might exacerbate WriteIOPS fluctuations (which ties into your second question).

Factors causing temporary file usage (beyond missing indexes) #

• Exceeding memory limits for temporary tables: If your query's intermediate result exceeds tmp_table_size or max_heap_table_size (MySQL uses the smaller of the two), in-memory temporary tables (MEMORY engine) will be converted to disk-based ones. If you've set default_tmp_storage_engine to MyISAM, all temporary tables start on disk regardless.
• Aggregation/sorting operations: Queries using GROUP BY, ORDER BY, or DISTINCT often require temporary tables to process results. Even with indexes, if the sorted/aggregated dataset is too large to fit in memory, disk-based temporary files are used.
• UNION operations: Unlike UNION ALL (which skips deduplication), UNION creates a temporary table to merge and deduplicate results, which may spill to disk if the merged set is too big.
• BLOB/TEXT columns: The MEMORY engine doesn't support these data types, so any query using them in temporary table operations will automatically fall back to disk-based temporary tables.
• Complex multi-table joins: When joining multiple large tables, the intermediate join results can outgrow memory, forcing MySQL to use disk temporary tables.

2. How to interpret stable, low ReadIOPS but fluctuating WriteIOPS? #

Fluctuating WriteIOPS (with low, steady ReadIOPS) usually points to bursty write workloads rather than ongoing read-heavy operations. Here are the most likely culprits and how to read them:

• Temporary table churn: If your application is creating/dropping a lot of disk-based temporary tables (like the MyISAM ones you're using), each table's creation, data write, and deletion will generate write IO. Bursts of complex queries (e.g., scheduled reports, batch processing) will spike WriteIOPS, which drops once the queries finish.
• InnoDB log and buffer pool behavior:
  • If innodb_flush_log_at_trx_commit is set to 1 (the default ACID-compliant setting), every transaction commit triggers a disk write. Bursts of short transactions (common in web apps) will cause WriteIOPS to spike.
  • Small innodb_log_file_size values force frequent log file rotations, which also create periodic write IO spikes.
• Bursty application writes: If your app has scheduled tasks (like bulk data imports, old record cleanup, or batch updates), these will drive sudden write IO increases that die down once the task completes.
• RDS storage characteristics: For GP2 storage, IOPS are tied to your volume size—if you exhaust burst credits during high-write periods, WriteIOPS will drop abruptly once credits are replenished, creating a sawtooth pattern. GP3 storage (fixed IOPS) will still show fluctuations if your workload is inherently bursty.

To dig deeper, enable slow query logs and monitor metrics like Com_create_table/Com_drop_table (track temporary table churn) and check SHOW ENGINE INNODB STATUS to see log flush activity during spikes.

3. RDS parameters differ greatly from self-hosted; is 10~12% disk-based temporary tables too high? Parameter tuning guidance #

Is 10~12% disk-based temporary tables too high? #

This isn't an extreme number, but it's high enough to warrant optimization. Typically, we aim to keep disk-based temporary tables below 5-10% if possible, as each disk temporary table adds IO overhead and can slow down query execution. If this 10-12% is tied to your slow queries or WriteIOPS fluctuations, it's definitely worth addressing.

Parameter tuning guidance #

Let's focus on the most impactful adjustments for your scenario:

1. Clean up unused MyISAM parameters:

   • Since you only use InnoDB tables, myisam_sort_buffer_size is irrelevant (it's used for MyISAM table sorts like ALTER TABLE). Lower this to a reasonable value (e.g., 64M or matching your old self-hosted setting) to free up memory for InnoDB.

2. Optimize temporary table behavior:

   • Set tmp_table_size and max_heap_table_size to the same value (MySQL uses the smaller one). Start with 64M-128M (adjust based on your instance's available memory—avoid setting so high that it causes swap). This lets more temporary tables stay in memory, reducing disk writes.
   • If you don't have a specific reason to use MyISAM for temporary tables, revert default_tmp_storage_engine to MEMORY. This way, MySQL will try in-memory first, only using disk when necessary. (If you saw improvements with MyISAM, it might mean your memory was too constrained—balance memory allocation with IO costs.)

3. InnoDB core optimizations:

   • innodb_buffer_pool_size: The single most important parameter—set it to 70-80% of your RDS instance's memory (for dedicated instances like m5/t3). This keeps more data/indexes in memory, reducing read IO and the need for large temporary tables.
   • innodb_log_file_size: Increase to 1G-4G (don't exceed 1/4 of the buffer pool size). This reduces log rotation frequency, smoothing out WriteIOPS fluctuations.
   • innodb_flush_log_at_trx_commit: If your application can tolerate minimal data loss (e.g., non-financial systems), set this to 2 instead of 1. This flushes logs to disk every second instead of on every commit, drastically reducing write IO.

4. Query cache and table management:

   • Disable query_cache_size (set to 0) if you have frequent writes. The query cache is notoriously inefficient for write-heavy workloads, as writes invalidate cache entries and cause lock contention.
   • Increase table_open_cache to handle frequent temporary table creation/destruction without repeated table open/close overhead.

Relevant references #

• MySQL 5.6 Official Tuning Guide (focus on InnoDB and temporary table sections)
• AWS RDS MySQL Parameter Best Practices
• High Performance MySQL (3rd Edition) (covers MySQL 5.6-specific optimizations and RDS considerations)

内容的提问来源于stack exchange，提问作者Cedric Simon