Postgresql踩坑-磁盘空间不足

引入

在工作项目的服务器中，用的是postgresql数据库，一直以来并没有报过磁盘空间不足，但是今天早上回来发现ovirt管理端登录不上，排查发现pgsql服务没有了，没有人动过服务器，于是重新开启了pgsql服务，一切正常。但是为啥pgsql会自己关掉？去查看了pgsql的日志文件，发现它报了一个磁盘空间不足的错误。

相关资料

百度以下发现这问题还是有记录的

背景：
加压测试过程中发现插入数据过程中报错：could not write to hash-join temporary file: 设备上没有空间。但是查看服务器还有很多空闲空间，是什么导致这样的错误呢？
可以看出该sql执行时临时文件会不断的增大，直至占满空间报错，当sql报错后临时文件大部分被清空，磁盘空间又将得到释放，所以开始看到的磁盘空间并没有满，但是报错却是磁盘空间满了。
原因：
据了解查询要使用的内存超出work_mem的大小时（包括排序，DISTINCT，MERGE JOIN，HASH JOIN，笛卡尔积，哈希聚合，分组聚合，递归查询）等操作时会使用临时文件来存储中间过程的数据。如果频繁的进行上述操作，临时文件将会快速增长。只有重启能够解决该问题，重启后将清空所有临时文件。
结论：
1.回到最开始的sql，这些临时表可以在数据插入表后建立索引，然后再执行最后的抽数，这样效率会高一点，嵌套循环耗费cpu，磁盘io，以及临时文件占用高
2.abase为了提高执行效率一些操作会使用内存代替临时存储，当内存不足时就会使用临时文件存储中间数据。
3.可酌情设置temp_file_limit 为磁盘空间的10%，当临时文件占用磁盘过高，自动取消该查询，记录查询语句
4.一般查询如果耗费大量的临时文件，有可能是没有索引导致

在一般的PostgreSQL操作里， 那些已经DELETE的行或者被UPDATE过后过时的行并没有从它们所属的表中物理删除； 在完成VACUUM之前它们仍然存在。
VACUUM回收频繁更新和已删除行占据的存储空间。

AUTOVACUUM

在查资料过程中一些文章提到了AUTOVACUUM,它是一个pgsql集成好的一个守护进程，就是用来进行一些清理管理工作的

AutoVacuum守护进程
由于vacuum是数据库系统的一个例行性维护，所以系统启动后，会开启一个autovacuum守护进程专门对此处理。autovacuum清理进程一般用vacuum而避免使用vacuum full。理由是，vacuum尽管可以保持表的最小尺寸，但是如果表还有继续增长的空间，这么做就意义不大了，特别是对于频繁写入的表。而且vacuum在执行效率上也比vacuum full好。
面所说的autovacuum进程是一个launcher进程，它不进行vacuum操作，它负责启动vacuum worker进程，然后由vacuum worker进程进行相应的vacuum和analyze操作。

开启AUTOVACUUM

在pgsql的配置文件中，可以直接配置AUTOVACUUM的参数

autovacuum = on // 是否开起autovacuum。当需要冻结xid(事务ID)时,值为off,PG也会进行vacuum操作。 

autovacuum_max_workers = 5 // 线程数，根据服务器处理器合理分配 

autovacuum_naptime = 50min // 两次vacuum操作间隔时间

autovacuum_vacuum_threshold = 50 // vacuum触发条件:某表DML操作的最小行数

autovacuum_analyze_threshold = 50 // analyze触发条件:某表DML操作的最小行数

这里还有一个参数我认为非常需要修改，就是work_mem。因为很多临时文件的产生就是因为工作内存的溢出，而work_mem默认配置为4MB，通常不够用。很多情况下比较常见的配置为64MB

work_mem = 64MB   # min 64kB