Optimize RDS Temporary Tables: Tune tmp_table_size (2026)

tmp_table_size is a critical RDS configuration parameter, and tuning it can drastically improve the performance of queries that rely on temporary tables, especially those involving complex joins, sorting, or grouping operations.

Let’s see what happens when we tune tmp_table_size and how it impacts query execution.

Imagine a scenario where you have a query that needs to aggregate a large dataset. The database might decide to use a temporary table to store intermediate results before performing the final aggregation. If this temporary table exceeds the tmp_table_size limit, MySQL has to resort to using on-disk temporary tables, which are significantly slower due to I/O overhead.

Here’s a simplified representation of a query that might benefit from tuning tmp_table_size:

SELECT
    c.category_name,
    COUNT(p.product_id) AS product_count
FROM
    categories c
JOIN
    products p ON c.category_id = p.category_id
WHERE
    p.created_at BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY
    c.category_name
ORDER BY
    product_count DESC
LIMIT 10;

In this query, MySQL might create a temporary table to hold the product_id and category_id for products created in 2023, then join it with categories, and finally group and count. If the intermediate result set for this join is large, it could easily exceed the default tmp_table_size.

The mental model here is about how MySQL handles intermediate results for certain operations. When a query requires temporary storage for operations like GROUP BY, ORDER BY, DISTINCT, or UNION, it first attempts to create an in-memory temporary table. This is managed by two key parameters: tmp_table_size and max_heap_table_size. Both must be large enough to accommodate the in-memory table. If the temporary table’s size exceeds either of these limits, MySQL will automatically convert it to a slower on-disk temporary table.

The primary lever you control is the tmp_table_size parameter itself. You can adjust this value within your RDS instance’s parameter group. The goal is to set it high enough to keep common intermediate results in memory, but not so high that it consumes excessive RAM, starving other critical processes or leading to swapping.

Diagnosing the Need for Tuning:

The most direct way to identify if tmp_table_size is a bottleneck is by examining your slow query logs. Look for queries that are taking a long time to execute and, more importantly, have a Created_tmp_disk_tables count greater than 0 in their EXPLAIN output.

To check Created_tmp_disk_tables for a specific query, run:

EXPLAIN SELECT
    c.category_name,
    COUNT(p.product_id) AS product_count
FROM
    categories c
JOIN
    products p ON c.category_id = p.category_id
WHERE
    p.created_at BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY
    c.category_name
ORDER BY
    product_count DESC
LIMIT 10;

If the output shows Created_tmp_disk_tables: 1 (or more), then your query is resorting to disk.

You can also monitor Created_tmp_disk_tables globally using:

SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';

A consistently high or increasing number here indicates a widespread issue.

Tuning tmp_table_size:

Determine a reasonable starting point: A common recommendation is to set tmp_table_size and max_heap_table_size to a value that is a significant fraction of your instance’s available RAM, but leaves ample room for the buffer pool and other essential processes. For example, on an db.r5.xlarge instance with 16 GiB of RAM, you might consider setting both to 2G (2 gigabytes) or 4G. Never set it to the full available RAM.
Modify your RDS Parameter Group:
- Navigate to the RDS console.
- Go to "Parameter groups".
- Select your custom parameter group (you cannot modify default groups). If you don’t have one, create a copy of the default group.
- Search for tmp_table_size.
- Change its value to your desired setting (e.g., 2147483648 for 2 GiB).
- Search for max_heap_table_size. Crucially, tmp_table_size and max_heap_table_size should ideally be set to the same value. If one is smaller, that one will be the limiting factor. Set max_heap_table_size to the same value.
- Save the changes to the parameter group.
Apply the changes:
- Select your RDS instance.
- Click "Modify".
- Scroll down to "Additional configuration" and select your modified parameter group.
- Choose whether to apply the changes immediately or during the next maintenance window. For performance tuning, immediate application is usually preferred.
- Click "Continue" and then "Modify DB instance".
Re-run your query and verify: After the instance reboots (if immediate application was chosen), re-run your slow query and check its EXPLAIN output again. You should now see Created_tmp_disk_tables: 0. Also, monitor SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables'; to see if the counter has stopped increasing.

The tmp_table_size parameter dictates the maximum size of in-memory temporary tables that MySQL will create. When MySQL needs to perform operations like GROUP BY, ORDER BY, DISTINCT, or UNION, it often uses temporary tables to hold intermediate results. If the size of such a temporary table exceeds the value of tmp_table_size (or max_heap_table_size, whichever is smaller), MySQL will automatically convert the table to a much slower on-disk format. This conversion incurs significant I/O overhead, leading to poor query performance.

The next logical step after optimizing temporary table creation is often to investigate query execution plans for other potential bottlenecks, such as inefficient index usage or table scan operations.