前言
不久前有前端人员咨询过一个问题,为什么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