Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-9101

Limit size of created disk temporary files and tables

Details

    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

        1. mdev9101-2.test
          60 kB
        2. mdev9101-3.test
          8 kB
        3. mdev9101-4.test
          69 kB
        4. mdev9101-5.test
          59 kB

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33680 Server hangs or assertion fails upon SELECT with limited max_tmp_space_usage

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33686 No warning is produced upon startup with incorrect values of max-total-tmp-space-usage or max-tmp-space-usage

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33715 max_tmp_space_usage description is ambiguous

          • Trivial - Cosmetic problem like misspelt words or misaligned text.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33751 Assertion `thd' failed in int temp_file_size_cb_func(tmp_file_tracking*, int)

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34016 Assertion `info->key_del_used == 0' failed in maria_close with limited tmp space

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34054 Memory leak in Window_func_runner::exec after encountering "temporary space limit reached" error

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34060 Unexpected behavior upon reading I_S.ALL_PLUGINS under limited tmp space

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34142 Server crashes in create_internal_tmp_table with low tmp space limit

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34149 Corruption-like errors are produced when temporary space limit is reached

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34150 Assertion failure in Diagnostics_area::set_error_status upon binary logging hitting tmp space limit

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34965 Wrong variable names for temporary space usage in documentation

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35541 UBSAN: runtime error: addition of unsigned offset to X overflowed to Y in my_b_flush_io_cache

          • Critical - Crashes, loss of data, severe memory leak.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35592 ERROR 1062 (23000): Duplicate entry 'NULL' for key 'key0' on CONCAT in sub-query

          • Critical - Crashes, loss of data, severe memory leak.
          • In Progress
          relates to

          New Feature - A new feature of the product, which has yet to be developed. MDEV-32616 Temporary file space monitoring

          • Major - Major loss of function.
          • Closed

          Epic - Created by Jira Software - do not edit or delete. Issue type for a big user story that needs to be broken down. MDEV-32053 New features requested by customer on 2023-08-28

          • Major - Major loss of function.
          • Open

          Activity

            People

              monty Michael Widenius
              monty Michael Widenius
              Votes:
              1 Vote for this issue
              Watchers:
              10 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.