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

            Ascending order - Click to sort in descending order
            View 10 older comments
            elenst Elena Stepanova added a comment - - edited 

            --source include/have_log_bin.inc
            		 
             
            		 
            SET max_tmp_space_usage= 64*1024;
            		 
             
            		 
            CREATE TABLE t (
            		 
              a varchar(1024), b varchar(1024), c varchar(1024), d varchar(1024), e varchar(1024), f varchar(1024), g varchar(1024)
            		 
            ) ENGINE=MyISAM;
            		 
             
            		 
            INSERT INTO t VALUES
            		 
            (REPEAT('x',1024),REPEAT('x',1024),REPEAT('x',1024),REPEAT('x',1024),REPEAT('x',1024),REPEAT('x',1024),REPEAT('x',1024)),
            		 
            (REPEAT('x',1024),REPEAT('x',1024),REPEAT('x',1024),REPEAT('x',1024),REPEAT('x',1024),REPEAT('x',1024),REPEAT('x',1024)),
            		 
            (REPEAT('x',1024),REPEAT('x',1024),REPEAT('x',1024),REPEAT('x',1024),REPEAT('x',1024),REPEAT('x',1024),REPEAT('x',1024)),
            		 
            (REPEAT('x',1024),REPEAT('x',1024),REPEAT('x',1024),REPEAT('x',1024),REPEAT('x',1024),REPEAT('x',1024),REPEAT('x',1024)),
            		 
            (REPEAT('x',1024),REPEAT('x',1024),REPEAT('x',1024),REPEAT('x',1024),REPEAT('x',1024),REPEAT('x',1024),REPEAT('x',1024)),
            		 
            (REPEAT('x',1024),REPEAT('x',1024),REPEAT('x',1024),REPEAT('x',1024),REPEAT('x',1024),REPEAT('x',1024),REPEAT('x',1024)),
            		 
            ('x','x','x','x','x','x','x');
            		 
             
            		 
            UPDATE t SET a = '' LIMIT 100;
            		 
             
            		 
            # Cleanup
            		 
            DROP TABLE t;

            bb-11.5-monty 7393f849bfd48ec82e91469c194227a04cdcd58d

            		 
            mariadbd: /src/sql/log.cc:8753: int MYSQL_BIN_LOG::write_transaction_or_stmt(group_commit_entry*, uint64): Assertion `!(entry->using_stmt_cache && !mngr->stmt_cache.empty() && mngr->get_binlog_cache_log((0))->error)' failed.
            		 
            240110 22:14:44 [ERROR] mysqld got signal 6 ;
            		 
             
            		 
            #9  0x00007fac5e853e32 in __GI___assert_fail (assertion=0x5596694ddda0 "!(entry->using_stmt_cache && !mngr->stmt_cache.empty() && mngr->get_binlog_cache_log((0))->error)", file=0x5596694d2420 "/src/sql/log.cc", line=8753, function=0x5596694ddc00 "int MYSQL_BIN_LOG::write_transaction_or_stmt(group_commit_entry*, uint64)") at ./assert/assert.c:101
            		 
            #10 0x0000559667aa01a0 in MYSQL_BIN_LOG::write_transaction_or_stmt (this=0x55966b3e4600 <mysql_bin_log>, entry=0x7fac53ebd010, commit_id=0) at /src/sql/log.cc:8753
            		 
            #11 0x0000559667a9da11 in MYSQL_BIN_LOG::trx_group_commit_leader (this=0x55966b3e4600 <mysql_bin_log>, leader=0x7fac53ebd010) at /src/sql/log.cc:8490
            		 
            #12 0x0000559667a9c515 in MYSQL_BIN_LOG::write_transaction_to_binlog_events (this=0x55966b3e4600 <mysql_bin_log>, entry=0x7fac53ebd010) at /src/sql/log.cc:8282
            		 
            #13 0x0000559667a9a383 in MYSQL_BIN_LOG::write_transaction_to_binlog (this=0x55966b3e4600 <mysql_bin_log>, thd=0x62c0000d0218, cache_mngr=0x61b000015098, end_ev=0x7fac53ebd2f0, all=false, using_stmt_cache=true, using_trx_cache=false, is_ro_1pc=false) at /src/sql/log.cc:7879
            		 
            #14 0x0000559667a73083 in binlog_flush_cache (thd=0x62c0000d0218, cache_mngr=0x61b000015098, end_ev=0x7fac53ebd2f0, all=false, using_stmt=true, using_trx=false, is_ro_1pc=false) at /src/sql/log.cc:1706
            		 
            #15 0x0000559667a73792 in binlog_commit_flush_stmt_cache (thd=0x62c0000d0218, all=false, cache_mngr=0x61b000015098) at /src/sql/log.cc:1759
            		 
            #16 0x0000559667a7716b in binlog_rollback (hton=0x615000001998, thd=0x62c0000d0218, all=false) at /src/sql/log.cc:2219
            		 
            #17 0x0000559667736cd5 in ha_rollback_trans (thd=0x62c0000d0218, all=false) at /src/sql/handler.cc:2274
            		 
            #18 0x00005596672fb72e in trans_rollback_stmt (thd=0x62c0000d0218) at /src/sql/transaction.cc:535
            		 
            #19 0x0000559666e0150f in mysql_execute_command (thd=0x62c0000d0218, is_called_from_prepared_stmt=false) at /src/sql/sql_parse.cc:5868
            		 
            #20 0x0000559666e0e277 in mysql_parse (thd=0x62c0000d0218, rawbuf=0x6290000e6238 "UPDATE t SET a = '' LIMIT 100", length=29, parser_state=0x7fac53ebe9f0) at /src/sql/sql_parse.cc:7800
            		 
            #21 0x0000559666de5880 in dispatch_command (command=COM_QUERY, thd=0x62c0000d0218, packet=0x629000249219 "UPDATE t SET a = '' LIMIT 100", packet_length=29, blocking=true) at /src/sql/sql_parse.cc:1893
            		 
            #22 0x0000559666de25b8 in do_command (thd=0x62c0000d0218, blocking=true) at /src/sql/sql_parse.cc:1406
            		 
            #23 0x00005596672b4637 in do_handle_one_connection (connect=0x608000002eb8, put_in_cache=true) at /src/sql/sql_connect.cc:1419
            		 
            #24 0x00005596672b3ff8 in handle_one_connection (arg=0x608000002e38) at /src/sql/sql_connect.cc:1321
            		 
            #25 0x0000559667edcc76 in pfs_spawn_thread (arg=0x617000006298) at /src/storage/perfschema/pfs.cc:2201
            		 
            #26 0x00007fac5e8a8044 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
            		 
            #27 0x00007fac5e92861c in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81

            Update: The assertion failure was fixed, the UPDATE query fails with (41): Local temporary space limit reached instead.

            elenst Elena Stepanova added a comment - - edited --source include/have_log_bin.inc   SET max_tmp_space_usage= 64*1024;   CREATE TABLE t ( a varchar (1024), b varchar (1024), c varchar (1024), d varchar (1024), e varchar (1024), f varchar (1024), g varchar (1024) ) ENGINE=MyISAM;   INSERT INTO t VALUES (REPEAT( 'x' ,1024),REPEAT( 'x' ,1024),REPEAT( 'x' ,1024),REPEAT( 'x' ,1024),REPEAT( 'x' ,1024),REPEAT( 'x' ,1024),REPEAT( 'x' ,1024)), (REPEAT( 'x' ,1024),REPEAT( 'x' ,1024),REPEAT( 'x' ,1024),REPEAT( 'x' ,1024),REPEAT( 'x' ,1024),REPEAT( 'x' ,1024),REPEAT( 'x' ,1024)), (REPEAT( 'x' ,1024),REPEAT( 'x' ,1024),REPEAT( 'x' ,1024),REPEAT( 'x' ,1024),REPEAT( 'x' ,1024),REPEAT( 'x' ,1024),REPEAT( 'x' ,1024)), (REPEAT( 'x' ,1024),REPEAT( 'x' ,1024),REPEAT( 'x' ,1024),REPEAT( 'x' ,1024),REPEAT( 'x' ,1024),REPEAT( 'x' ,1024),REPEAT( 'x' ,1024)), (REPEAT( 'x' ,1024),REPEAT( 'x' ,1024),REPEAT( 'x' ,1024),REPEAT( 'x' ,1024),REPEAT( 'x' ,1024),REPEAT( 'x' ,1024),REPEAT( 'x' ,1024)), (REPEAT( 'x' ,1024),REPEAT( 'x' ,1024),REPEAT( 'x' ,1024),REPEAT( 'x' ,1024),REPEAT( 'x' ,1024),REPEAT( 'x' ,1024),REPEAT( 'x' ,1024)), ( 'x' , 'x' , 'x' , 'x' , 'x' , 'x' , 'x' );   UPDATE t SET a = '' LIMIT 100;   # Cleanup DROP TABLE t; bb-11.5-monty 7393f849bfd48ec82e91469c194227a04cdcd58d mariadbd: /src/sql/log.cc:8753: int MYSQL_BIN_LOG::write_transaction_or_stmt(group_commit_entry*, uint64): Assertion `!(entry->using_stmt_cache && !mngr->stmt_cache.empty() && mngr->get_binlog_cache_log((0))->error)' failed. 240110 22:14:44 [ERROR] mysqld got signal 6 ;   #9 0x00007fac5e853e32 in __GI___assert_fail (assertion=0x5596694ddda0 "!(entry->using_stmt_cache && !mngr->stmt_cache.empty() && mngr->get_binlog_cache_log((0))->error)", file=0x5596694d2420 "/src/sql/log.cc", line=8753, function=0x5596694ddc00 "int MYSQL_BIN_LOG::write_transaction_or_stmt(group_commit_entry*, uint64)") at ./assert/assert.c:101 #10 0x0000559667aa01a0 in MYSQL_BIN_LOG::write_transaction_or_stmt (this=0x55966b3e4600 <mysql_bin_log>, entry=0x7fac53ebd010, commit_id=0) at /src/sql/log.cc:8753 #11 0x0000559667a9da11 in MYSQL_BIN_LOG::trx_group_commit_leader (this=0x55966b3e4600 <mysql_bin_log>, leader=0x7fac53ebd010) at /src/sql/log.cc:8490 #12 0x0000559667a9c515 in MYSQL_BIN_LOG::write_transaction_to_binlog_events (this=0x55966b3e4600 <mysql_bin_log>, entry=0x7fac53ebd010) at /src/sql/log.cc:8282 #13 0x0000559667a9a383 in MYSQL_BIN_LOG::write_transaction_to_binlog (this=0x55966b3e4600 <mysql_bin_log>, thd=0x62c0000d0218, cache_mngr=0x61b000015098, end_ev=0x7fac53ebd2f0, all=false, using_stmt_cache=true, using_trx_cache=false, is_ro_1pc=false) at /src/sql/log.cc:7879 #14 0x0000559667a73083 in binlog_flush_cache (thd=0x62c0000d0218, cache_mngr=0x61b000015098, end_ev=0x7fac53ebd2f0, all=false, using_stmt=true, using_trx=false, is_ro_1pc=false) at /src/sql/log.cc:1706 #15 0x0000559667a73792 in binlog_commit_flush_stmt_cache (thd=0x62c0000d0218, all=false, cache_mngr=0x61b000015098) at /src/sql/log.cc:1759 #16 0x0000559667a7716b in binlog_rollback (hton=0x615000001998, thd=0x62c0000d0218, all=false) at /src/sql/log.cc:2219 #17 0x0000559667736cd5 in ha_rollback_trans (thd=0x62c0000d0218, all=false) at /src/sql/handler.cc:2274 #18 0x00005596672fb72e in trans_rollback_stmt (thd=0x62c0000d0218) at /src/sql/transaction.cc:535 #19 0x0000559666e0150f in mysql_execute_command (thd=0x62c0000d0218, is_called_from_prepared_stmt=false) at /src/sql/sql_parse.cc:5868 #20 0x0000559666e0e277 in mysql_parse (thd=0x62c0000d0218, rawbuf=0x6290000e6238 "UPDATE t SET a = '' LIMIT 100", length=29, parser_state=0x7fac53ebe9f0) at /src/sql/sql_parse.cc:7800 #21 0x0000559666de5880 in dispatch_command (command=COM_QUERY, thd=0x62c0000d0218, packet=0x629000249219 "UPDATE t SET a = '' LIMIT 100", packet_length=29, blocking=true) at /src/sql/sql_parse.cc:1893 #22 0x0000559666de25b8 in do_command (thd=0x62c0000d0218, blocking=true) at /src/sql/sql_parse.cc:1406 #23 0x00005596672b4637 in do_handle_one_connection (connect=0x608000002eb8, put_in_cache=true) at /src/sql/sql_connect.cc:1419 #24 0x00005596672b3ff8 in handle_one_connection (arg=0x608000002e38) at /src/sql/sql_connect.cc:1321 #25 0x0000559667edcc76 in pfs_spawn_thread (arg=0x617000006298) at /src/storage/perfschema/pfs.cc:2201 #26 0x00007fac5e8a8044 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442 #27 0x00007fac5e92861c in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81 Update: The assertion failure was fixed, the UPDATE query fails with (41): Local temporary space limit reached instead.
            monty Michael Widenius added a comment -

            Change state to reviewer

            monty Michael Widenius added a comment - Change state to reviewer
            serg Sergei Golubchik added a comment -

            To test this feature separately, please use bb-11.5-MDEV-9101-max-tmp-space-used

            serg Sergei Golubchik added a comment - To test this feature separately, please use bb-11.5- MDEV-9101 -max-tmp-space-used
            serg Sergei Golubchik added a comment -

            review sent per email.

            https://lists.mariadb.org/hyperkitty/list/developers@lists.mariadb.org/thread/3BAGDS53ZQQ62L5APQGIE5IXAWUG4LUX/

            serg Sergei Golubchik added a comment - review sent per email. https://lists.mariadb.org/hyperkitty/list/developers@lists.mariadb.org/thread/3BAGDS53ZQQ62L5APQGIE5IXAWUG4LUX/
            elenst Elena Stepanova added a comment - - edited

            The final variable names are

            MariaDB [(none)]> show variables like 'max_tmp%';
            		 
            +-----------------------------+---------------+
            		 
            | Variable_name               | Value         |
            		 
            +-----------------------------+---------------+
            		 
            | max_tmp_session_space_usage | 1099511627776 |
            		 
            | max_tmp_total_space_usage   | 1099511627776 |
            		 
            +-----------------------------+---------------+

            It is also documented incorrectly in the KB (MDEV-33715).

            elenst Elena Stepanova added a comment - - edited The final variable names are MariaDB [(none)]> show variables like 'max_tmp%' ; + -----------------------------+---------------+ | Variable_name | Value | + -----------------------------+---------------+ | max_tmp_session_space_usage | 1099511627776 | | max_tmp_total_space_usage | 1099511627776 | + -----------------------------+---------------+ It is also documented incorrectly in the KB ( MDEV-33715 ).

            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.