I recently got into a bit of a debate about standardizing MySQL 8.4’s internal temporary table configuration. We wanted to cap memory usage efficiently, but relying on “rules of thumb” isn’t enough when production stability is at stake.
The main confusion was about how per-query limits fight with global limits. So, rather than guessing, I decided to break a sandbox environment to see exactly where the bytes go—and confirm the findings against the official documentation.
The Theory: Individual vs. Collective
Before I run the scripts, let’s establish the rules based on the MySQL 8.4 Reference Manual. There is a critical distinction between “individual” and “collective” limits.
1. tmp_table_size (The Cup)
This is the limit for a single table. According to the documentation:
“Defines the maximum size of any individual in-memory internal temporary table created using the TempTable storage engine.”
If a single query tries to exceed this:
“When the limit determined by tmp_table_size is reached, MySQL automatically converts the in-memory internal temporary table to an InnoDB on-disk internal temporary table.”
This confirms the first premise: this variable protects us from individual bad queries.
2. temptable_max_ram (The Bucket)
This is the global limit. The documentation states:
“Defines the maximum amount of RAM that can be used by the TempTable storage engine before it starts allocating space from memory-mapped files or before MySQL starts using InnoDB on-disk internal temporary tables…”
It also clarifies the relationship between the two:
“The tmp_table_size limit is intended to prevent individual queries from consuming an inordinate amount of global TempTable resources…”
The “Cup vs. Bucket” Interaction: The manual explicitly defines what happens when these two interact:
“If tmp_table_size is less than temptable_max_ram, it is not possible for an in-memory temporary table to use more than tmp_table_size.”
The Theory: If we set the global bucket (temptable_max_ram) to 2GB and the individual cup (tmp_table_size) to 256MB, queries shouldn’t go beyond that 2GB of RAM when using temp space collectively.
The Reality: If you have 10 queries running, and each needs ~200MB, they fit in RAM (Total: 2GB). But if an 11th query shows up, the global temptable_max_ram limit is hit.
Once that global limit is hit, the behavior depends on your configuration.
The Setup
To test this without needing terabytes of RAM, I set the limits ridiculously low to force the behavior immediately.
The Config:
|
1 2 3 4 5 6 7 8 9 10 11 |
-- 1. Set the storage engine to TempTable (default in 8.4) SET GLOBAL internal_tmp_mem_storage_engine = TempTable; -- 2. Set the INDIVIDUAL table limit to 16MB SET GLOBAL tmp_table_size = 16777216; -- 16MB -- 3. Set the GLOBAL RAM limit to 32MB (So only ~2 maxed out tables can fit) SET GLOBAL temptable_max_ram = 33554432; -- 32MB -- 4. Turn OFF mmap to test the default behavior first SET GLOBAL temptable_use_mmap = OFF; |
The Query: I used a CTE to generate some data (just enough to overflow the 16MB individual limit) and forced a temp table using ORDER BY RAND().
|
1 2 3 4 5 6 7 8 9 |
SELECT * FROM ( WITH RECURSIVE cte (n, str) AS ( SELECT 1, REPEAT('X', 1000) UNION ALL SELECT n + 1, REPEAT('X', 1000) FROM cte WHERE n < 20000 ) SELECT * FROM cte ) AS t1 ORDER BY RAND(); |
I ran this in parallel 50 times:
|
1 2 3 |
for i in {1..50}; do ( mysql -e "source script.sql" > /dev/null ) & done |
The Results
Checking the Performance Schema confirmed the behavior.
|
1 2 |
SELECT * FROM performance_schema.global_status WHERE VARIABLE_NAME LIKE 'Created_tmp%'; |
Output:
|
1 2 3 4 5 6 7 |
+-------------------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +-------------------------+----------------+ | Created_tmp_disk_tables | 39 | | Created_tmp_files | 120 | | Created_tmp_tables | 64 | +-------------------------+----------------+ |
The Created_tmp_disk_tables count went up. This aligns with the docs:
“When an internal temporary table is created on disk, the server increments the Created_tmp_disk_tables value.”
The “Invisible File” Mystery
Next, I wanted to test temptable_use_mmap = ON.
According to the documentation, this setting changes the overflow mechanism:
“Controls whether the TempTable storage engine allocates space from memory-mapped files or MySQL uses InnoDB on-disk internal temporary tables when the limit determined by temptable_max_ram is exceeded.”
I enabled it and ran the test again. But here is the catch: You can’t see the files. The documentation explains exactly why ls -la /tmp shows nothing:
“Temporary files are deleted immediately after they are created and opened, and therefore do not remain visible in the tmpdir directory. The space occupied by temporary files is held by the operating system while temporary files are open.”
Furthermore, standard metrics fail us here:
“Due to a known limitation, Created_tmp_disk_tables does not count on-disk temporary tables created in memory-mapped files.”
To prove this was actually working, I had to use the Performance Schema instruments recommended by the manual (memory/temptable/physical_disk) and write a Linux-level script to monitor /proc file descriptors.
The Monitor Script:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
PID=4056647 TMP_PATH="sandboxes/msb_8_4_6/tmp" echo "Monitoring PID $PID for temp files in $TMP_PATH..." while true; do # 1. Find all File Descriptors (FD) pointing to deleted files in your tmpdir for fd_path in $(find /proc/$PID/fd -lname "*$TMP_PATH*deleted*" 2>/dev/null); do # Get the actual filename file_target=$(readlink "$fd_path") # Skip if the file is already gone (race condition) if [ -z "$file_target" ]; then continue; fi clean_name=$(echo "$file_target" | sed 's/ (deleted)//') # 2. Check if this specific file exists in the process's memory map if grep -Fq "$clean_name" /proc/$PID/maps; then echo "[$(date +%T)] FOUND: $clean_name is MEMORY MAPPED (mmap ON)" else echo "[$(date +%T)] FOUND: $clean_name is STANDARD DISK I/O (InnoDB/mmap OFF)" fi done sleep 0.5 done |
The Logs:
|
1 2 3 4 |
[18:06:34] FOUND: [temp_file_handle] is MEMORY MAPPED (mmap ON) [18:06:34] FOUND: [temp_file_handle] is MEMORY MAPPED (mmap ON) [18:06:35] FOUND: /home/.../tmp/#32506356 is STANDARD DISK I/O (InnoDB/mmap OFF) [18:06:35] FOUND: /home/.../tmp/#32506359 is STANDARD DISK I/O (InnoDB/mmap OFF) |
This output confirms the hybrid behavior we expected:
-
Standard Disk I/O: Queries exceeding
tmp_table_size(16MB) were booted to InnoDB. -
Memory Mapped: Queries that fit individually but hit the Global limit (32MB) spilled to mmap files.
The Verdict
We confirmed that temptable_use_mmap does exactly what it says—it changes the overflow mechanism for the global limit.
However, you probably shouldn’t use it. The documentation is blunt:
“temptable_use_mmap is deprecated; expect support for it to be removed in a future version of MySQL.”
Since it’s deprecated, standardization is a bad idea.
The recommendation:
-
Keep
temptable_max_ramhigh (1GB+): The docs define the default as “3% of the total memory available on the server, with a minimum of 1 GB”. Don’t starve the global pool. -
Tune
tmp_table_sizeindividually: If you need to control memory, do it here. Let the bad queries spill to disk (InnoDB) individually without punishing the rest of the server.
Wrapping Up
The biggest insight here isn’t just about memory settings—it’s about understanding the scope of your tools. We proved that temptable_max_ram acts as a safety valve for the entire server, while tmp_table_size is your per-query disciplinarian. The recommendation is to stick to the defaults for the engine, keep your global limits healthy (1GB+), and focus on tuning your bad queries.
See you next time!
The floor is yours.
0 comments · Moderated · civil & on-topicFirst comment appears here once approved. Questions, corrections, and counterpoints welcome — just no self-promotion.