Measuring Memory Usage of MySQL Internal Temporary Tables

Experiment

We first created a test database.

Then we loaded data into it.

We used a subquery with UNION so that the execution plan would employ an internal temporary table, which can be seen in the EXPLAIN output.

In a separate session we opened performance_schema to monitor memory usage.

In the main session we identified the connection and thread IDs.

We checked the initial memory allocation statistics in performance_schema .

After executing the SQL statement that triggers the internal temporary table, we queried performance_schema again and observed that more than 4 MB of memory had been allocated for the temporary table.

We then viewed the memory allocation details, confirming the >4 MB usage.

Next we created an explicit MEMORY engine table, inserted the same data, and observed via performance_schema that the resident memory size matched the amount used by the internal temporary table.

Conclusion

By monitoring the MEMORY engine with performance_schema , we inferred the memory consumption of MySQL's internal temporary tables. The information_schema tables such as INNODB_TEMP_TABLE_INFO do not expose this information.

Note that the MEMORY engine allocates considerably more space than the raw data size; in our example 300 025 rows × 4 bytes ≈ 1.2 MB of data resulted in over 4 MB of allocated memory, so a larger scaling factor is needed for accurate estimation.

We also mention the use of dbdeployer (originating from mysql‑sandbox by Giuseppe Maxia) for quickly setting up MySQL test environments, and that future experiments will continue to rely on it or MySQL containers.

Feel free to leave comments or questions about MySQL technical topics.