首页 » MySQL, 其它国产库 » GoldenDB TEMP Tablespace(#innodb_temp)导致文件系统使用率高

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 参数.

打赏

, ,

目前这篇文章还没有评论(Rss)

我要评论

上一篇：

下一篇：