How Can I Use Temporary Disk of GaussDB(for MySQL)?

Risks

• SQL statements fail to be executed and no results are returned.
• SQL statements occupy lock resources for a long time and block other SQL statements. As a result, the number of connections increases or even reaches the upper limit, affecting other services.
• There are too many temporary files in the binlog cache, which can cause the database to break down. This takes a long time to restore, so services are interrupted for a long time.

Scenarios and Troubleshooting

1. Explicitly creating temporary disk tables
   • Scenario

     You can run the create temporary table statement to explicitly create temporary disk tables. The temporary tables whose storage engine is InnoDB are cached in the buffer pool and flushed to disks by dirty threads.

     In GaussDB(for MySQL), data in disk temporary tables is stored in session temporary tablespace (the path is specified by the innodb_temp_tablespaces_dir parameter), and undo logs are stored in the global temporary tablespace (the path is specified by the innodb_temp_data_file_path parameter).

     To prevent temporary disk tables from occupying too much disk space, you are advised to delete unnecessary temporary disk tables or disconnect unnecessary database connections.

     

     • Session temporary tablespace: It is reclaimed when the current database connection is released.
     • Global temporary tablespace: It is reclaimed only after the database is restarted.
   • Troubleshooting
     1. View information about the temporary tables you created in InnoDB.

        mysql> select * from information_schema.innodb_temp_table_info;
        +----------------------+---------------+--------+------------+
        | TABLE_ID             | NAME          | N_COLS | SPACE      |
        +----------------------+---------------+--------+------------+
        | 18446744069414584311 | #sqle055_24_0 |      5 | 4294502266 |
        +----------------------+---------------+--------+------------+

     2. Check the usage of InnoDB temporary table files.

        In a table, the ID column indicates the ID of the session that is using the temporary table file. If the value is 0, the ibt file is not used. The SIZE column indicates the size of the ibt file, which automatically increases based on the usage and is reclaimed when the session ends. If the value of the PURPOSE column is INTRINSIC, the table is an implicit temporary table. If the value of the PURPOSE column is USER, the table is an explicit temporary table.

        mysql> select * from information_schema.innodb_session_temp_tablespaces;
        +----+------------+----------------------------+-------+----------+-----------+
        | ID | SPACE      | PATH                       | SIZE  | STATE    | PURPOSE   |
        +----+------------+----------------------------+-------+----------+-----------+
        | 31 | 4294502265 | ./#innodb_temp/temp_9.ibt  | 81920 | ACTIVE   | INTRINSIC |
        | 36 | 4294502266 | ./#innodb_temp/temp_10.ibt | 98304 | ACTIVE   | USER      |
        | 34 | 4294502264 | ./#innodb_temp/temp_8.ibt  | 81920 | ACTIVE   | INTRINSIC |
        |  0 | 4294502257 | ./#innodb_temp/temp_1.ibt  | 81920 | INACTIVE | NONE      |
        |  0 | 4294502258 | ./#innodb_temp/temp_2.ibt  | 81920 | INACTIVE | NONE      |
        |  0 | 4294502259 | ./#innodb_temp/temp_3.ibt  | 81920 | INACTIVE | NONE      |
        |  0 | 4294502260 | ./#innodb_temp/temp_4.ibt  | 81920 | INACTIVE | NONE      |
        |  0 | 4294502261 | ./#innodb_temp/temp_5.ibt  | 81920 | INACTIVE | NONE      |
        |  0 | 4294502262 | ./#innodb_temp/temp_6.ibt  | 81920 | INACTIVE | NONE      |
        |  0 | 4294502263 | ./#innodb_temp/temp_7.ibt  | 81920 | INACTIVE | NONE      |
        +----+------------+----------------------------+-------+----------+-----------+

2. Querying disk temporary tables or temporary files implicitly created
   • Scenario

     When selecting an execution plan for a query, the query optimizer may use temporary tables. Temporary memory tables are preferentially used. When the size of temporary memory tables exceeds a certain threshold (either tmp_table_size or max_heap_table_size, whichever is smaller), temporary disk tables are used.

     Disk temporary tables are implicitly created by queries. The data between tables that are implicitly and explicitly created are the same and stored in the session temporary tablespace. If there are complex queries, including but not limited to keywords such as UNION, GROUP BY, and ORDER BY, in larger tables, temporary disk tables may be generated. In addition, when queries involve sorting operations, if the sort buffer cannot store all of the data (the buffer size is specified by sort_buffer_size), temporary disk files can be used for auxiliary sorting. In most scenarios, temporary disk tables implicitly created are the main reason disks fill up. If your disk is filling up, you can locate complex queries or long transactions, optimize query statements, add proper indexes, and split long transactions to address the issue.

   • Troubleshooting
     1. Check whether there are SQL statements using temporary tables or file sorting.

        If Using temporary is displayed in the Extra column, temporary tables are used. If Using filesort is displayed, file sorting is used.

        

     2. Query the usage of implicit temporary tables. The method is the same as that the explicit disk temporary tables.
3. Querying binlogs generated for long transactions
   • Scenario

     A binlog is a binary that records database changes, such as DDL, DCL, and DML (excluding SELECT). InnoDB caches binlogs in the memory before transactions are committed and writes binlogs to disks only after the transactions are committed. The size of the binlog file for each connection in the memory is specified by the binlog_cache_size parameter. When the size of the binlog file recorded by a transaction exceeds the value of this parameter, the binlog file is written to a temporary disk file. Long transactions may cause large binlogs. As a result, the size of temporary binlogs on the disk is large and the disk may be full. You are advised to control the transaction size, split long transactions, or change binlog_cache_size to a more appropriate value.

   • Troubleshooting
     1. Check whether binlog is enabled.

        mysql> show variables like 'log_bin';
        +---------------+-------+
        | Variable_name | Value |
        +---------------+-------+
        | log_bin       | ON    |
        +---------------+-------+

     2. View the binlog cache usage.

        Binlog_cache_disk_use indicates the number of times that temporary disk files are used for caching binlogs due to insufficient memory (specified by binlog_cache_size). If the value of binlog_cache_size is large, temporary disk files are invoked for caching binlogs multiple times.

        mysql> show global status like '%binlog_cache%';
        +-----------------------+-----------+
        | Variable_name         | Value     |
        +-----------------------+-----------+
        | Binlog_cache_disk_use | 1335006   |
        | Binlog_cache_use      | 264240359 |
        +-----------------------+-----------+

4. Checking temporary files generated by DDLs
   • Scenario

     During DDL operations on tables, temporary disk files are generated in some phases.

     • Sometimes, you need to re-create the tablespace of the original table, which involves the re-creation of the B+ tree index on the table. If a table contains a large amount of data, the sort buffer cannot store all of the data. You need to create a temporary file to assist with the sorting.
     • Although some online DDL statements support DML operations on the original table, the original table cannot be directly modified. The modification must be recorded in online logs and applied to the new table after the DDL operations are complete. Online logs are preferentially stored in the memory. The size of online logs is specified by the innodb_sort_buffer_size parameter. If the size of online logs exceeds the parameter value, online logs are temporarily stored in a temporary file.
     • When the OPTIMIZE TABLE statement is executed on a table, the data stored in the clustered index needs to be reorganized, which may generate temporary files.
   • Troubleshooting
     • Run the SHOW PROCESSLIST command to check whether there are DDL statements that have been taking a long time to execute.
     • Ensure that there is enough space before performing DDLs for large tables.