设置 temp_file_limit ，防止磁盘空间被撑爆（一大堆排序SQL）
问题
空间被撑爆，简单的看了一下，空间主要增长是临时文件。
[postgresql@133e0e148e21 base]$ du -sm *8 18 135908 13591120125 16444271460 pgsql_tmp
临时文件的产生一般都是大型的排序、去重、hash等操作导致的，我们通过pg_stat_activity视图查看当前活动会话的情况。
select pid,wait_event,wait_event_type,query from pg_catalog.pg_stat_activity；
可以看到当前主要活动的一些会话，都在执行一个order by分页查询的SQL。
通过查询这个order的表。发现它居然高达54GB。
zys=> \dt+ zys_man_oltaccess_push
于是赶紧联系了应用，把这一批SQL杀掉之后，系统就恢复了正常。
zys=> select ‘pg_catalog.pg_terminate_backend(’||pid||’)’ from pg_catalog.pg_stat_activity where query like ‘%offset%’;
这个故障并不复杂，问题是在于这套库我们并没有对这方面做任何控制。
如果是Oracle数据库，我们一般都会建个Temp表空间，然后设置为30-60GB，一旦出现问题，像应用搞了大的SQL，会导致临时文件撑爆，最终导致执行的SQL失败。并不会导致像我们这种整个目录空间撑爆的问题。
所以PostgreSQL应该怎么像Oracle一样进行限制，防止大的垃圾SQL把空间撑爆了？
答案是使用temp_file_limit参数进行限制。该参数的默认值为-1（不限制），最大值是2147483647kB，换算过来大概是2TB。
我搜了一下，建议是把该值的大小设置成为整个目录空间的10%。我这里是1TB，那么我先暂时设置成50GB测试一下。
zys=# alter system set temp_file_limit=52428800;
ALTER SYSTEM
zys=# select pg_reload_conf();
再次执行查询
zys=> select * from zys_man_oltaccess_push order by prod_inst_id limit 10000 offset 5850000;
这次在达到我们设置的50GB，就不再增长。而且结果也能顺利的查询出来。
但是这样设置也会问题，就是一个进程能创造的最大文件是50GB，但是进程一旦并发起来，比如10个进程同时run，还是会占用500GB，这仍然是一个很大的占用。
如果我们把这个值设置的足够小。比如5GB，我们再次执行这个查询，这次就会报temporary file size exceeds temp_file_limit (5242880kB)
zys=# alter system set temp_file_limit=5242880;
ALTER SYSTEM
zys=# select pg_reload_conf();
zys=> select * from zys_man_oltaccess_push order by prod_inst_id limit 10000 offset 5830000;
ERROR: temporary file size exceeds temp_file_limit (5242880kB)CONTEXT: parallel worker
应用程序执行的SQL语句就会报错。
所以暂时没什么好的办法，只能加强监控，通过设置log_temp_files，默认值是-1。可以将排序，哈希等操作创建的临时文件进行跟踪记录。然后就可以知道应用的这些SQL需要搞多少的临时文件了。然后再根据它们的并发合理设置一个值。
pg_stat_database有两个字段，分别是temp_files和temp_bytes，它们都是累计值。
temp_files代表数据库创建的临时文件数，temp_bytes代表数据库创建的临时文件空间总大小。
zys=> select datname,temp_files,temp_bytes from pg_stat_database where datname=‘zys’;
我们可以通过prometheus对文件产生的速率进行监控。
