How can I limit the size of temporary tables?

Question

I have largish (InnoDB) tables in a database; apparently the users are capable of making SELECTs with JOINs that result in temporary, large (and thus on-disk) tables. Sometimes, those are so large that they exhaust disk space, leading to all sorts of weird issues.

Is there a way to limit temp table maximum size for an on-disk table, so that the table doesn't overgrow the disk? tmp_table_size only applies to in-memory tables, despite the name. I haven't found anything relevant in the documentation.

Answer 1

MariaDB 10.2.7 added the option tmp_disk_table_size, which is in the documentation described as:

Max size for data for an internal temporary on-disk MyISAM or Aria table. These tables are created as part of complex queries when the result doesn't fit into the memory engine. You can set this variable if you want to limit the size of temporary tables created in your temporary directory tmpdir.

Do not let that mislead you into thinking that it doesn't apply if you are working with InnoDB (or other engine) tables. On-disk internal temporary tables are always Aria (or MyISAM as a compile-time option) as explained here:

Internal temporary tablespaces, (that is, temporary tables that cannot be kept in memory) use either Aria or MyISAM, depending on the aria_used_for_temp_tables system variable.

Interestingly, MySQL uses InnoDB according to this:

MySQL 8.4 uses only the InnoDB storage engine for on-disk internal temporary tables. (The MYISAM storage engine is no longer supported for this purpose.)

I did not find an option for MySQL that would be equivalent to MariaDB's tmp_disk_table_size.

Answer 2

While it does not answer the question for MySQL, MariaDB has tmp_disk_table_size and potentially also useful max_join_size settings. However, tmp_disk_table_size is only for MyISAM or Aria tables, not for InnoDB. Also, max_join_size works only on the estimated row count of the join, not the actual row count. On the bright side, the error is issued almost immediately.

Answer 3

There's no option for this in MariaDB and MySQL. I ran into the same issue as you some months ago, I searched a lot and I finally partially solved it by creating a special storage area on the NAS for themporary datasets.

Create a folder on your NAS or a partition on an internal HDD, it will be by definition limited in size, then mount it, and in the mysql ini, assign the temporary storage to this drive: (choose either windows/linux)

tmpdir="mnt/DBtmp/"
tmpdir="T:\"

mysql service should be restarted after this change.

With this approach, once the drive is full, you still have "weird issues" with on-disk queries, but the other issues are gone.

Answer 4

There was a discussion about an option disk-tmp-table-size, but it looks like the commit did not make it through review or got lost for some other reason (at least the option does not exist in the current code base anymore).

I guess your next best try (besides increasing storage) is to tune MySQL to not make on-disk temp tables. There are some tips for this on DBA. Another attempt could be to create a ramdisk for the storage of the "on-disk" temp tables, if you have enough RAM and only lack disk storage.