WL#11613: InnoDB: Reclaim disk space occupied by temporary tables online
Affects: Server-8.0 — Status: Complete
When the default internal_tmp_disk_storage_engine=InnoDB, all user and optimizer internal temporary tables use the global temporary tablespace (ibtmp1). Even after these tables are dropped, the disk space occupied is never released back to OS. The objective of this worklog is to reclaim disk space occupied by the temporary tables online.
1. FR-1: Reclaim the disk space occupied by optimizer internal and user temporary tables. 2. FR-2: Initial disk footprint for session temporary tablespaces pool should be within acceptable limits (~12M-20M?) 3. FR-3: Provide an option to store session temporary tablespaces (innodb_temp_tablespaces_dir) 4. FR-4: Provide interface to query the current usage of session temporary tablespaces 5. NFR-1: Performance shouldn't be degraded or if there is drop, it should be within acceptable limits (< 5%?) 6. NFR-2: Should support huge number of concurrent connections/sessions using optimizer internal temporary tables.
Background: ----------- Who uses temporary tablespace? ------------------------------ 1. Since 5.7, the default internal_tmp_disk_storage_engine=InnoDB, so optimizer created temporary tables (aka instrinisic tables) are stored in temporary tablespace (ibtmp1). These tables are not undo-logged and are dropped at end of query. 2. Temporary tables created by user (CREATE TEMPORARY TABLE..). These tables need rollback support, so there is undo from DMLs on temporary table. So user temp table data and their undo pages are in temporary tablespace. These tables should be explicity dropped by user or dropped automatically at end of session. What causes the growth of the global temporary tablespace? ---------------------------------------------------------- 1. Temp tablespace is a like a general tablespace. After a table in a general tablespace is dropped, the tablespace never shrinks. 2. Long running queries which create internal tables and insert huge amounts of data. The tables are dropped at end of query but the disk space is not released back to OS. 3. User created temporary tables which are huge and never dropped by user. 4. Undo from user tables also resides in ibtmp1 and undo pages can make the tablespace grow. Design: ======= On startup, a pool of temporary tablespaces will be created in innodb_temp_tablespaces_dir. These tablespaces are similar to general tablespaces. These will be called "Session temporary tablespaces". The shared tablespace ibtmp1 will be referred as "Global temporary tablespace". Each session, on the first request to create a user temporary table or optimizer internal table, will allocate a tablespace from the reserved temp tablespaces pool. The pool can expand its capacity on demand. The pool will not shrink and any file in the pool should be of its initial size (5 pages). Once a temporary tablespace is attached to a session, all temporary tables from this session would use it. A session can have at max two temporary tablespaces. One tablespace for user created temporary tables and another for optimizer created temporary tables. The reason for having two separate tablespaces is that, in the future, it is possible to reclaim the disk space occupied by optimizer tables quickly by reclaiming even before the session disconnect. On session disconnect, these two tablespaces will be truncated and released back to the pool. So the disk space occupied by the temporary tables of a session, is reclaimed. Note that the global temporary tablespace (ibtmp1) will be still used for rollback segments from changes to user temporary tables. Information Schema View ======================= An Information Schema view will be provided to see the session_id/thread_id & its tablespaces size. It displays the information of all .IBT files in the pool. ACTIVE state means the tablespace is used by Session. INACTIVE state means the tablespace is in pool and not belonging to any session. mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES; +------------+------------+----------------------------+----------+----------+ | SESSION_ID | SPACE | PATH | SIZE | STATE | +------------+------------+----------------------------+----------+----------+ | 7 | 4294566162 | ./#innodb_temp/temp_10.ibt | 933888 | ACTIVE | | 7 | 4294566161 | ./#innodb_temp/temp_9.ibt | 10485760 | ACTIVE | | 8 | 4294566160 | ./#innodb_temp/temp_8.ibt | 933888 | ACTIVE | | 8 | 4294566159 | ./#innodb_temp/temp_7.ibt | 9437184 | ACTIVE | | 0 | 4294566153 | ./#innodb_temp/temp_1.ibt | 81920 | INACTIVE | | 0 | 4294566154 | ./#innodb_temp/temp_2.ibt | 81920 | INACTIVE | | 0 | 4294566155 | ./#innodb_temp/temp_3.ibt | 81920 | INACTIVE | | 0 | 4294566156 | ./#innodb_temp/temp_4.ibt | 81920 | INACTIVE | | 0 | 4294566157 | ./#innodb_temp/temp_5.ibt | 81920 | INACTIVE | | 0 | 4294566158 | ./#innodb_temp/temp_6.ibt | 81920 | INACTIVE | +------------+------------+----------------------------+----------+----------+ 10 rows in set (0.01 sec) Session Temporary tablespaces ============================ These temporary tablespaces will have the extension ".ibt". If a directory is provided using "innodb_temp_tablespaces_dir", the session temporary tablespaces will be created under the specified directory. This variable is not dynamic. It can only be set at startup. If innodb_temp_tablespaces_dir is not provided explicitly, a directory named "#innodb_temp" will be created under datadir. If this directory already exists, InnoDB will use that directory. Under this directory, *.ibt files will be created. If relative path is used for innodb_temp_tablespaces_dir, it is relative to datadir. Session temporary tablespaces are not registered in DD nor will they be noticed or used at startup when looking for InnoDB tablespaces and undo tablespaces. Note: Like the global temporary tablespace (ibtmp1), session temporary tablespaces are always re-created on startup. A higher range of 400K space_ids are reserved for session temporary tablespaces. 400K is chosen because max_connections is 100K and we will reserve 4 space_ids per session. With this work, only 2 will be needed. We reserve more for some future purpose. So the total number of space_ids needed is 100K * 4 = 400K. This is 0.09% out of the available space_ids (4Billion). These space_ids do not survive reboot. This fixed limit does not affect future use of these space_ids since session temporary tablespaces are not redo logged and are always re-created on startup. Impact on Replication: ====================== Temporary tables are replicated only in STATEMENT mode and in ROW mode, only a "DROP TEMPORARY TABLE X IF EXISTS" is replicated. So there is no impact with binlog ROW format. With STATEMENT mode, on slave, all the temporary tables are created in one IBT file. It is only truncated at shutdown. TABLESPACE= identifier for temporary tables =========================================== This will be disallowed when innodb_strict_mode=ON. We want users to use session temporary tablespaces and get disk space reclaimed. However, with innodb_strict_mode=OFF, we will allow the syntax, throw warning and use only "session temporary tablespace".
Copyright (c) 2000, 2021, Oracle Corporation and/or its affiliates. All rights reserved.