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

Limit size of created disk temporary files and tables

    XMLWordPrintable

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
          Elena Stepanova
        2. mdev9101-3.test
          8 kB
          Elena Stepanova
        3. mdev9101-4.test
          69 kB
          Elena Stepanova
        4. mdev9101-5.test
          59 kB
          Elena Stepanova

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              monty Michael Widenius
              Votes:
              1 Vote for this issue
              Watchers:
              9 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.