mysql - can I determine or limit the amount of memory query is allowed to use

Question

I am running a query that creates a temporary table however the limit is 64mb and I am not able to change the limit due to access permissions etc. When a large date range is selected the temporary table runs out of space and results in a mysql error.

Is there anyway I can determine the size or amount of memory the query will use before attempting to run the query, so I can avoid the above problem gracefully?

Answer 1

There's no way to limit the size of the temp table directly, except by querying for a smaller number of rows in your base SQL query.

Can you be more specific about the error you're seeing? MySQL temporary tables can exist in memory up to the lesser of tmp_table_size and max_heap_table_size. If the temp table is larger, MySQL converts it to an on-disk temp table.

This will make the temp table a lot slower than in-memory storage, but it shouldn't result in an error unless you have no space available in your temp directory.

There's also a lot of ways MySQL uses memory besides temp table storage. You can tune variables for many of these, but it's not the same as placing a limit on the memory a query uses.

---

The error 1114 indicates that you've run out of space. If it were an InnoDB table on disk, this probably means you have an ibdata1 file without autoextend defined for the tablespace. For a memory table, it means you're hitting the limit of max_heap_table_size.

Since you can't change max_heap_table_size, your options are to reduce the number of rows you put into the table at a time, or else use an on-disk temp table instead of in memory.

Also be careful about using the most current release of the major version of MySQL. I found bug 18160 that reports MySQL calculating table size incorrectly for heap tables (which are used for in-memory temp tables). So for example make certain you're using at least MySQL 5.0.23 or 5.1.10 to get the fix for that bug.