GaussDB(for MySQL)的临时盘使用说明

临时盘占满风险说明

• SQL会执行失败，无法正常返回结果。
• SQL长时间占有锁资源，阻塞其他SQL，进一步导致连接数上升甚至打满，影响其他业务。
• Binlog Cache临时文件达到上限，引发数据库发生crash，且恢复过程会持续较长时间，极大增加业务中断时间。

常见临时盘占满场景和排查方法

1. 用户显式创建磁盘临时表
   • 场景

     用户可以通过create temporary table语句显式创建磁盘临时表，存储引擎为Innodb的临时表会缓存在Buffer Pool中，并由刷脏线程进行落盘。

     在GaussDB(for MySQL)中，磁盘临时表的数据保存在Session临时表空间中（路径由参数“innodb_temp_tablespaces_dir”控制），undo log则保存在Global临时表空间中（路径由参数“innodb_temp_data_file_path”控制）。

     防止磁盘临时表占用过多磁盘空间，建议用户即时删除不再需要使用的磁盘临时表，或断开不必要的数据库连接。

     

     • Session临时表空间：在数据库连接释放的时候被回收。
     • Global临时表空间：需要重启数据库才能被回收。
   • 排查方法
     1. 查看InnoDB中用户创建的临时表信息

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

     2. 查看InnoDB临时表文件的使用情况

        在该表中，ID列表示正在使用此临时表文件的session id，如果该值为0则表示此ibt文件未被使用；SIZE列表示此ibt文件的大小，会依据使用情况自动扩大，随会话结束回收； PURPOSE列如果为INTRINSIC表示是隐式临时表，如果为USER则表示是显式临时表在使用。

        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. 查询隐式创建磁盘临时表或临时文件
   • 场景

     查询优化器为查询选择执行计划时可能会使用临时表进行辅助，优先使用内存临时表，当超过设置的内存临时表大小阈值（取决于参数tmp_table_size和max_heap_table_size两者的最小值）时会使用磁盘临时表。

     磁盘临时表是由查询隐式创建的，与显式创建的磁盘临时表数据保持一致，均保存在Session临时表空间中。对于大表上的复杂查询，包括但不限于含有UNION、GROUP BY、ORDER BY等关键词，就有可能产生磁盘临时表。除此之外，当查询涉及排序操作，如果sort buffer中无法存下所有数据（大小由参数sort_buffer_size设置），就会使用磁盘临时文件进行辅助排序。在大部分场景下，隐式创建的磁盘临时表是磁盘占满的主要原因，用户可以通过定位复杂查询或长事务，优化查询语句、添加合理的索引、拆分长事务等方法解决此类问题。

   • 排查方法
     1. 查看SQL是否使用了临时表或文件排序。

        在Extra列中出现Using temporary表示使用了临时表，Using filesort表示使用了文件排序。

        

     2. 查询隐式磁盘临时表使用情况的方法同上述显式磁盘临时表。
3. 长事务产生大量Binlog
   • 场景

     Binlog是二进制格式文件，记录了用户对数据库的改动事件，如DDL、DCL、DML（除去SELECT）等。InnoDB在事务未提交前会先把Binlog缓存在内存中，提交时才会把将其写入磁盘。内存中允许的每个连接的Binlog大小由参数“binlog_cache_size”控制，当事务记录的Binlog超出该大小时，会将其写入磁盘临时文件。长事务可能会导致Binlog很大，进而导致磁盘上的Binlog临时文件很大，容易将磁盘打满，因此建议用户合理控制事务大小，尝试将长事务进行拆分，或者调整合适的“binlog_cache_size”。

   • 排查方法
     1. 查看Binlog是否开启

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

     2. 查看Binlog Cache使用情况

        Binlog_cache_disk_use表示由于binlog_cache_size设置的内存不足导致缓存Binlog使用了磁盘临时文件的次数，当该值较大时说明多次调用了磁盘临时文件缓存Binlog。

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

4. DDL产生临时文件
   • 场景

     在对表进行DDL操作的过程中，也有一些环节会产生磁盘临时文件：

     • 有时需要对原表进行表空间重建，涉及表上索引B+树的重建。如果表的数据量很大，sort buffer中无法存储所有数据，需要创建临时文件来辅助排序。
     • 部分online DDL虽然支持在原表上进行DML操作，但不能直接对原表进行修改，需要将修改记录在online log上，DDL操作结束后再应用到新表上。online log优先保存在内存中，大小由参数“innodb_sort_buffer_size”控制，超过上限则需要暂存到临时文件。
     • 对表执行OPTIMIZE TABLE命令时，需要重新组织聚簇索引的保存的数据，可能会产生临时文件。
   • 排查方法
     • 通过SHOW PROCESSLIST查看是否有执行时间过长的DDL语句。
     • 大表DDL前需要注意保留出足够的空间。