Mysql - can I determine or limit the amount of memory request allowed for use

Question

I run a query that creates a temporary table, but the limit is 64 MB, and I cannot change the limit due to permissions, etc. When a large date range is selected, the temporary table ends with a space and results in a mysql error.

In any case, I can determine the size or amount of memory that will be used in the request before trying to execute the request, so can I avoid this problem gracefully?

Answer 1

It is not possible to limit the size of the temp table directly, except for querying fewer rows in the underlying SQL query.

Can you clarify the error you see? MySQL temporary tables can exist in memory up to the smaller tmp_table_size and max_heap_table_size . If the temporary table is larger, MySQL will convert it to a temp table on disk.

This will make the temporary table much slower than the memory in memory, but it should not cause an error if you do not have space in your temp .

There are also many ways that MySQL uses memory , in addition to storing temporary tables. You can configure variables for many of them, but this is not the same as setting a limit on the memory that the query uses.

---

Error 1114 indicates that you have run out of free space. If it was an InnoDB table on disk, this probably means that you have an ibdata1 file without autoextend defined for the table space. For a memory table, this means that you have reached the max_heap_table_size limit.

Since you cannot change max_heap_table_size, your options reduce the number of rows that you put in the table at a time, or use a temporary table instead on disk rather than in memory.

Also be careful using the latest version of the main version of MySQL. I found error 18160 in which MySQL incorrectly calculates the table size for heap tables (which are used for temp in-memory temp tables). So, for example, make sure you use at least MySQL 5.0.23 or 5.1.10 to get a fix for this error.

Answer 2

I do not know how to do this, but you can use the information about the tables used, SHOW THE STATUS TABLE, for example, the average row size, and then calculate the number of records returned by your query using SELECT COUNT(*) ... If you really need to keep the maximum row size using column types.

Perhaps it would be easier to check the number of records that can be processed, and then either specify a fixed LIMIT clause or respond to SELECT COUNT(*) ...