Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'

ErrorERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sql161_17a011_a' is full

Short answer

In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114. Raise temptable_max_ram and temptable_max_mmap in the parameter group, keep the combined size below local storage, and size the instance for the workload.

  • Applies to Aurora MySQL version 3 (MySQL 8.0 compatible)
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Confirm which instance ran the query and read the current limits. Writers fall back to on-disk InnoDB temporary tables; readers cannot, which is why the error appears on readers first. The last two values are in bytes.

    SELECT @@innodb_read_only, @@aurora_version, @@temptable_max_ram, @@temptable_max_mmap;
  2. 2Match what you see, then jump to its fix

    1. 1@@innodb_read_only is 1 and a large GROUP BY, sort, CTE or UNION failsThe shared TempTable pool is full on a reader instanceFix
    2. 2aurora_tmptable_enable_per_table_limit is ON and a single query fails on a readerThe per-table limit is switched on and tmp_table_size is smallFix
    3. 3The error stays after you raised the limits to the sensible maximum, and the FreeLocalStorage metric drops to near zero during the queryThe instance class has too little local storage for the temporary dataFix

What does the error look like?

  • ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sql161_17a011_a' is full
  • ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full

Cause 1 of 3

The shared TempTable pool is full on a reader instance

1Check

TempTable keeps data in RAM up to temptable_max_ram, then overflows into memory-mapped files on local storage up to temptable_max_mmap, which defaults to 1 GiB. When that global limit is reached, a writer converts the table to InnoDB on disk and a reader ends the query with 1114.

SELECT event_name, current_alloc, high_alloc
FROM sys.memory_global_by_current_bytes WHERE event_name LIKE 'memory/temptable/%';

2Fix

Set both parameters in the DB parameter group so they fit your biggest queries. Example from the docs: 2 GiB in memory and 20 GiB overflow means temptable_max_ram 2147483648 and temptable_max_mmap 21474836480. A common start for the overflow value is 80 percent of the instance's local storage. Do not set temptable_max_ram too high, or the instance can run out of memory.

# DB instance parameter group values (bytes)
temptable_max_ram  = 2147483648
temptable_max_mmap = 21474836480

3Verify

Run the failing query again on the reader and watch the same sys view.

SELECT @@temptable_max_ram, @@temptable_max_mmap;

Confirmed by docs.aws.amazon.com

Did this fix it?

Cause 2 of 3

The per-table limit is switched on and tmp_table_size is small

1Check

From Aurora MySQL 3.04, when aurora_tmptable_enable_per_table_limit is ON, tmp_table_size caps each TempTable table. A reader that hits it ends the query with 1114 even though the global pool still has room. The docs also say raising the global values does not help in this mode.

SELECT @@aurora_tmptable_enable_per_table_limit, @@tmp_table_size;

2Fix

Raise tmp_table_size in the parameter group, or set aurora_tmptable_enable_per_table_limit back to OFF (its default) so only the global limits apply.

# DB instance parameter group
aurora_tmptable_enable_per_table_limit = 0

3Verify

Repeat the query on the reader.

SELECT @@aurora_tmptable_enable_per_table_limit;

Confirmed by docs.aws.amazon.com

Did this fix it?

Cause 3 of 3

The instance class has too little local storage for the temporary data

1Check

Overflow files live on the instance's local storage, which depends on the instance class: the docs list 32 GiB for a large and 160 GiB for a 2xlarge in the r6g family, and say the real amount is a little lower. Watch the FreeLocalStorage CloudWatch metric for the instance while the query runs.

# RDS console > the instance > Monitoring > FreeLocalStorage, while the query runs

2Fix

Keep temptable_max_mmap below the local storage size, and move the reporting workload to an instance class with more local storage. Rewriting the query to sort or group less data helps as well.

aws rds modify-db-instance --db-instance-identifier my-reader \
  --db-instance-class db.r6g.2xlarge --apply-immediately

3Verify

Run the heaviest query again and check that FreeLocalStorage stays above zero.

SELECT @@temptable_max_mmap;

Confirmed by docs.aws.amazon.com

Did this fix it?

How do I stop it coming back?

  • Send long reporting queries to a dedicated reader, and size that reader for its local storage.
  • Alert on FreeLocalStorage so you see the pressure before a query fails.
  • Add an index or a narrower SELECT list to queries that sort or group big result sets.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.