金仓数据库KingbaseES V8R6 中syssql_tmp目录说明

前言

不久前有前端人员咨询过一个问题，为什么syssql_tmp目录下会产生如此多的大文件。

针对这个目录的解释是：临时文件（用于排序超出内存容量的数据等操作）是在$KINGBASE_DATA/base/syssql_tmp中创建的，临时文件的名称形式为syssql_tmpPPP.NNN，其中PPP是拥有后端的PID，NNN区分该后端的不同临时文件。

测试产生临时文件

1、把work_mem调整成64KB

test=# set work_mem =64;
SET

test=# show work_mem;
 work_mem
----------
 64kB
(1 row)

2、对大表进行order by，发现在syssql_tmp目录下生成临时文件

test=# \d index_test
                      Table "public.index_test"
 Column |           Type            | Collation | Nullable | Default
--------+---------------------------+-----------+----------+---------
 id     | integer                   |           |          |
 t_name | character varying(6 char) |           |          |
Indexes:
    "idx_id" btree (id)



test=# select count(*) from index_test;
  count
---------
 2000000
(1 row)

test=# insert into index_test  select generate_series(2000001,6000000),'fq'||generate_series(2000001,6000000);




test=# select sys_backend_pid();
 sys_backend_pid 
----------------
          19654
(1 row)

test=#  explain analyze select id,t_name from index_test order by t_name ;
                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Gather Merge  (cost=476193.23..978300.49 rows=4303476 width=13) (actual time=18967.598..27675.141 rows=7999000 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Sort  (cost=475193.21..480572.55 rows=2151738 width=13) (actual time=16125.384..17488.483 rows=2666333 loops=3)
         Sort Key: t_name
         Sort Method: external merge  Disk: 89032kB
         Worker 0:  Sort Method: external merge  Disk: 48208kB
         Worker 1:  Sort Method: external merge  Disk: 48544kB
         ->  Parallel Seq Scan on index_test  (cost=0.00..64989.38 rows=2151738 width=13) (actual time=0.268..846.996 rows=2666333 loops=3)
 Planning Time: 0.072 ms
 Execution Time: 28138.216 ms
(11 rows)

3、查看base目录生成了syssql_tmp目录，因为设置的work_mem太小了，需要磁盘排序，所以产生了临时文件。

[[kingbase7@localhost base]$ ll
total 76
drwx------ 2 kingbase7 kingbase7  8192 Mar 27 15:00 1
drwx------ 2 kingbase7 kingbase7  8192 Feb  1 11:22 12144
drwx------ 2 kingbase7 kingbase7 12288 Mar 29 15:44 12145
drwx------ 2 kingbase7 kingbase7  8192 Feb  1 11:28 12146
drwx------ 2 kingbase7 kingbase7  8192 Mar 27 15:00 41213
drwx------ 2 kingbase7 kingbase7  8192 Mar 27 15:00 41247
drwx------ 2 kingbase7 kingbase7     6 Mar 30 14:34 syssql_tmp

在sql执行过程中生成了临时文件，19654为后端pid。在sql执行完成，再查看此目录下，临时文件已经被清空

[kingbase2@localhost syssql_tmp]$ ll
total 261952
-rw------- 1 kingbase2 kingbase2 91168768 Mar 30 15:12 syssql_tmp19654.4
-rw------- 1 kingbase2 kingbase2 49709056 Mar 30 15:12 syssql_tmp19864.0
-rw------- 1 kingbase2 kingbase2 49364992 Mar 30 15:12 syssql_tmp19865.0

[kingbase2@localhost syssql_tmp]$ ll
total 0 

总结

对于现场遇到的问题是产生了几十GB的临时文件，如果服务器内存剩余很多考虑增加work_mem,或者通过temp_file_limit控制临时文件总上限。当然根本原因还是需要优化sql。

我们可以通过参数temp_file_limit控制临时文件产生量，在控制临时文件使用量，使用个数的参数上，控制的也是Query执行过程中产生的临时文件。

可以设置参数log_temp_files=0，就会针对需要临时空间的sql记录进数据库日志。
案例中设置work_mem=64kb，所以产生大量磁盘排序，可以加大work_mem，尤其应对hash_join等需要大量排序的操作，需要说明，虽然增大work_mem可以避免过多临时文件，但是如果查询并发过大有可能造成内存溢出的严重后果。
因为work_mem参数是针对每一个服务器进程而言的。

对应临时文件限制文档请参考：https://www.cnblogs.com/kingbase/p/16404461.html 更多信息，参见https://help.kingbase.com.cn/v8/index.html