Details
-
New Feature
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
Description
Add a variable (max-tmp-table-disk-space) to limit the total temporary disk space used by internal temporary tables created during query execution.
When the internal in-memory temporary table is oversize and converting to MyISAM/Aria table to store on disk, this option will limit the max space of tmp_dir. If a new disk temporary table will cause tmp_dir over the limitation, then this query will return an error.
As part of this task we should also add a status variable for temporary disk space usage and also add this to information_schema.processlist.
What should be monitored:
- Temporary files created by filesort and replication buffers and
sorting in engines as part of alter table, repair table, optimize table.
The first 2 are relative easy to do, the later a bit harder. - On disk internal temporary tables (MyISAM or Aria). These are relatively
easy to handle.
Explicit defined temporary tables (CREATE TEMPORARY TABLE) will
not be part of this work as this should be regarded to be a normal table.
(Any user can create and drop tables any time).
However any on disk temporary table disk space should also be possible to monitor, possible with another status variable.
First implementation:
-Two new variables:
- max_tmp_space_usage : Limits the the temporary space allowance per user
- max_total_space_file_usage: Limits the temporary space allowance for all users.
The temporary space is counted for:
- All sql level temporary files. This includes sorts for filesort, transaction temporary space, analyze etc. It does not include engine internal files used for repair, alter table, index presorting etc.
- All internal on disk temporary tables created as part of resolving a SELECT, multi-source update etc.
Attachments
Issue Links
- causes
-
MDEV-33680 Server hangs or assertion fails upon SELECT with limited max_tmp_space_usage
- Closed
-
MDEV-33686 No warning is produced upon startup with incorrect values of max-total-tmp-space-usage or max-tmp-space-usage
- Open
-
MDEV-33715 max_tmp_space_usage description is ambiguous
- Open
-
MDEV-33751 Assertion `thd' failed in int temp_file_size_cb_func(tmp_file_tracking*, int)
- Closed
-
MDEV-34016 Assertion `info->key_del_used == 0' failed in maria_close with limited tmp space
- Closed
-
MDEV-34054 Memory leak in Window_func_runner::exec after encountering "temporary space limit reached" error
- Closed
-
MDEV-34060 Unexpected behavior upon reading I_S.ALL_PLUGINS under limited tmp space
- Closed
-
MDEV-34142 Server crashes in create_internal_tmp_table with low tmp space limit
- Closed
-
MDEV-34149 Corruption-like errors are produced when temporary space limit is reached
- Closed
-
MDEV-34150 Assertion failure in Diagnostics_area::set_error_status upon binary logging hitting tmp space limit
- Closed
-
MDEV-34965 Wrong variable names for temporary space usage in documentation
- Closed
-
MDEV-35592 ERROR 1062 (23000): Duplicate entry 'NULL' for key 'key0' on CONCAT in sub-query
- Confirmed
- relates to
-
MDEV-32616 Temporary file space monitoring
- Closed
-
MDEV-32053 New features requested by customer on 2023-08-28
- Open