GoldenDB TEMP Tablespace(#innodb_temp)导致文件系统使用率高
最近，我注意到一位朋友在处理一个以G字开头的MySQL系国产数据库时遇到了一些问题。具体来说，该数据库占用了超过1TB的临时文件，导致文件系统告警，之前kill session并且未能释放。这是由于在杀掉dbproxy上的session后，db node上的session仍然存在。这样的情况可能是非原生分布式数据库的一个风险点，因为每个data node都是一个独立的MySQL实例。最终，我们通过登录到data node并手动kill session解决了问题。
由于这个厂家总是要求删除相关帖子，所以我就不点名了。今天，我在另一款国产MySQL系分布式数据库GoldenDB上进行了类似的模拟测试，仅记录处理方法。
GoldenDB的分布式架构
分布式数据库其实都有DB proxy，就存在2阶段的session. 在Ocenabase中一样客户端到db proxy的client session ，和proxy 到 observer之前的session session。在GoldenDB一样也是2段session，那考验通知机制和超时机制了.
GoldenDB temp使用
在oracle中v$tempseg_usage可以查询会话的temp使用，使用独立的TEMP TABLESPAE, 在MySQL系InnoDB使用会话临时表空间和全局临时表空间。用户会话SQL是不可控的，所以使用不当可能会使话临时表空间把操作系统文件系统耗尽。 下面记录一下现象
# session 1 $ mysql -uanbob -p........... Your MySQL connection id is 201083 Server version: 8.9.99 Source distribution GoldenDB V_ALL-DBV6.1.03.07SP1 (Build 2024-04-26 18:41:37) (Commit 6dfaecde4) .. MySQL [anbob]> create table test(id int,pid int,name varchar2(20)); Query OK, 0 rows affected (0.03 sec) MySQL [anbob]> insert into test select rownum r,rownum+1,'anbob'||rownum from dual connect by rownum<=1000000; Query OK, 1000000 rows affected (8.05 sec) Records: 1000000 Duplicates: 0 Warnings: 0 MySQL [anbob]> select id,name from test connect by prior pid=id; ... waiting ... # session 2 MySQL [anbob]> select id,SPACE,PATH,round(SIZE/1024/1024,1) size_mb,STATE,PURPOSE from INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES order by SIZE; +---------+------------+----------------------------+---------+----------+-----------+ | ID | SPACE | PATH | SIZE_MB | STATE | PURPOSE | +---------+------------+----------------------------+---------+----------+-----------+ | 5231734 | 4243767289 | ./#innodb_temp/temp_9.ibt | 0.3 | ACTIVE | INTRINSIC | | 0 | 4243767281 | ./#innodb_temp/temp_1.ibt | 0.3 | INACTIVE | NONE | | 0 | 4243767282 | ./#innodb_temp/temp_2.ibt | 0.3 | INACTIVE | NONE | | 0 | 4243767283 | ./#innodb_temp/temp_3.ibt | 0.3 | INACTIVE | NONE | | 0 | 4243767284 | ./#innodb_temp/temp_4.ibt | 0.3 | INACTIVE | NONE | | 0 | 4243767285 | ./#innodb_temp/temp_5.ibt | 0.3 | INACTIVE | NONE | | 0 | 4243767286 | ./#innodb_temp/temp_6.ibt | 0.3 | INACTIVE | NONE | | 0 | 4243767287 | ./#innodb_temp/temp_7.ibt | 0.3 | INACTIVE | NONE | | 13 | 4243767290 | ./#innodb_temp/temp_10.ibt | 0.4 | ACTIVE | INTRINSIC | | 5231824 | 4243767288 | ./#innodb_temp/temp_8.ibt | 496 | ACTIVE | INTRINSIC | +---------+------------+----------------------------+---------+----------+-----------+ 10 rows in set (0.00 sec) MySQL [(none)]> select * from information_schema.processlist where COMMAND!='Sleep' order by TIME desc \G *************************** 1. row *************************** ID: 239 USER: repl HOST: 172.20.22.108:10982 DB: NULL COMMAND: Binlog Dump GTID TIME: 3468026 STATE: Master has sent all binlog to slave; waiting for more updates INFO: NULL *************************** 2. row *************************** ID: 5231824 USER: dbproxy HOST: 172.20.22.107:62680 DB: anbob COMMAND: Query TIME: 125 STATE: executing INFO: select id,name from test connect by prior pid=id *************************** 3. row *************************** ID: 5232427 USER: dbproxy HOST: 172.20.22.107:20360 DB: NULL COMMAND: Query TIME: 0 STATE: executing INFO: select * from information_schema.processlist where COMMAND!='Sleep' order by TIME desc 3 rows in set (0.00 sec) MySQL [(none)]> select id,SPACE,PATH,round(SIZE/1024/1024,1) size_mb,STATE,PURPOSE from INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES order by SIZE; +---------+------------+----------------------------+---------+----------+-----------+ | ID | SPACE | PATH | SIZE_MB | STATE | PURPOSE | +---------+------------+----------------------------+---------+----------+-----------+ | 0 | 4243767281 | ./#innodb_temp/temp_1.ibt | 0.3 | INACTIVE | NONE | | 0 | 4243767282 | ./#innodb_temp/temp_2.ibt | 0.3 | INACTIVE | NONE | | 0 | 4243767283 | ./#innodb_temp/temp_3.ibt | 0.3 | INACTIVE | NONE | | 0 | 4243767284 | ./#innodb_temp/temp_4.ibt | 0.3 | INACTIVE | NONE | | 0 | 4243767285 | ./#innodb_temp/temp_5.ibt | 0.3 | INACTIVE | NONE | | 0 | 4243767286 | ./#innodb_temp/temp_6.ibt | 0.3 | INACTIVE | NONE | | 0 | 4243767287 | ./#innodb_temp/temp_7.ibt | 0.3 | INACTIVE | NONE | | 13 | 4243767290 | ./#innodb_temp/temp_10.ibt | 0.4 | ACTIVE | INTRINSIC | | 5232427 | 4243767289 | ./#innodb_temp/temp_9.ibt | 0.4 | ACTIVE | INTRINSIC | | 5231824 | 4243767288 | ./#innodb_temp/temp_8.ibt | 2912 | ACTIVE | INTRINSIC | +---------+------------+----------------------------+---------+----------+-----------+ 10 rows in set (0.00 sec) MySQL [(none)]> kill 5231824; Query OK, 0 rows affected (0.00 sec) MySQL [(none)]> select id,SPACE,PATH,round(SIZE/1024/1024,1) size_mb,STATE,PURPOSE from INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES order by SIZE; +---------+------------+----------------------------+---------+----------+-----------+ | ID | SPACE | PATH | SIZE_MB | STATE | PURPOSE | +---------+------------+----------------------------+---------+----------+-----------+ | 0 | 4243767281 | ./#innodb_temp/temp_1.ibt | 0.3 | INACTIVE | NONE | | 0 | 4243767282 | ./#innodb_temp/temp_2.ibt | 0.3 | INACTIVE | NONE | | 0 | 4243767283 | ./#innodb_temp/temp_3.ibt | 0.3 | INACTIVE | NONE | | 0 | 4243767284 | ./#innodb_temp/temp_4.ibt | 0.3 | INACTIVE | NONE | | 0 | 4243767285 | ./#innodb_temp/temp_5.ibt | 0.3 | INACTIVE | NONE | | 0 | 4243767286 | ./#innodb_temp/temp_6.ibt | 0.3 | INACTIVE | NONE | | 0 | 4243767287 | ./#innodb_temp/temp_7.ibt | 0.3 | INACTIVE | NONE | | 0 | 4243767288 | ./#innodb_temp/temp_8.ibt | 0.3 | INACTIVE | NONE | | 13 | 4243767290 | ./#innodb_temp/temp_10.ibt | 0.4 | ACTIVE | INTRINSIC | | 5232427 | 4243767289 | ./#innodb_temp/temp_9.ibt | 0.4 | ACTIVE | INTRINSIC | +---------+------------+----------------------------+---------+----------+-----------+ 10 rows in set (0.00 sec)
Note:
kill session后temp 直接释放。
文件系统绝对路径
如果一个主机多个实例，需要从DB内部确认一下文件的绝对路径
MySQL [information_schema]> show variables like '%HOME%';
+---------------------------+--------------------------------+
| VARIABLE_NAME | VALUE |
+---------------------------+--------------------------------+
| innodb_data_home_dir | /home/goldendb/zxdb2/data/data |
| innodb_log_group_home_dir | /home/goldendb/zxdb2/data/redo |
+---------------------------+--------------------------------+
2 rows in set (0.01 sec)
[root@ANBOB1 ~]# cd /home/goldendb/zxdb2/data/data
[root@ANBOB1 data]# ls
anbob ca.pem dbagent gdb_stat_c1 #ib_65536_0.dblwr ibtmp1 mysql private_key.pem server-cert.pem
auto.cnf client-cert.pem enmo gdb_stat_c2 #ib_65536_1.dblwr #innodb_temp mysql.ibd public_key.pem server-key.pem
ca-key.pem client-key.pem gdb_prc heartbeat_info ibdata1 keyring performance_schema recyclebin sys
[root@ANBOB1 data]# cd \#innodb_temp/
[root@ANBOB1 #innodb_temp]# pwd
/home/goldendb/zxdb2/data/data/#innodb_temp
[root@ANBOB1 #innodb_temp]# ls -lh
总用量 962M
-rw-r----- 1 zxdb2 goldendb 384K 7月 23 18:16 temp_10.ibt
-rw-r----- 1 zxdb2 goldendb 320K 6月 13 14:25 temp_1.ibt
-rw-r----- 1 zxdb2 goldendb 320K 6月 13 14:25 temp_2.ibt
-rw-r----- 1 zxdb2 goldendb 320K 6月 13 14:25 temp_3.ibt
-rw-r----- 1 zxdb2 goldendb 320K 6月 13 14:25 temp_4.ibt
-rw-r----- 1 zxdb2 goldendb 320K 6月 13 14:25 temp_5.ibt
-rw-r----- 1 zxdb2 goldendb 320K 6月 13 14:25 temp_6.ibt
-rw-r----- 1 zxdb2 goldendb 320K 7月 23 18:15 temp_7.ibt
-rw-r----- 1 zxdb2 goldendb 960M 7月 23 18:16 temp_8.ibt
-rw-r----- 1 zxdb2 goldendb 384K 7月 23 18:01 temp_9.ibt
mysql> show variables like '%temp%';
+---------------------------------+-----------------------+
| VARIABLE_NAME | VALUE |
+---------------------------------+-----------------------+
| avoid_temporal_upgrade | OFF |
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
| innodb_temp_tablespace_max_size | 104857600 |
| innodb_temp_tablespaces_dir | ./#innodb_temp/ |
| show_old_temporals | OFF |
| temptable_max_mmap | 1073741824 |
| temptable_max_ram | 1073741824 |
| temptable_use_mmap | OFF |
+---------------------------------+-----------------------+
8 rows in set (0.00 sec)
Note:
如果限制temp文件的使用可以配置innodb_temp_tablespace_max_size 参数.
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。