postgresql内存关键参数调优（work_mem）

work_mem 参数调优

work_mem：在pgsql 8.0之前叫做sort_mem。postgresql在执⾏排序操作时，会根据work_mem的⼤⼩决定是否将⼀个⼤的结果集拆分为⼏个⼩的和work_mem查不多⼤⼩的临时⽂件。显然拆分的结果是降低了排序的速度。因此增加work_mem有助于提⾼排序的速度。通常设置为实际RAM的2% -4%，根据需要排序结果集的⼤⼩⽽定，⽐如81920(80M)。备注：以上的官⽅的描述。但在实际的业务中会有所不同，如纯粹的交易系统（oltp-交

易多为⼏⾏内操作，但⽐较频繁）这样的系统⼏乎不涉及到排序操作，或者说涉及的排序操作数据也是相当的少（如⼗、百条数据排序），这样就没有必要去调整该参数。如业务⽩天是oltp ，⽽晚间是olap（olap-分析系统）。还有些系统只为数据分析⽽是⽤。他们的使⽤还是有点区别。如果排序处理的不合理，很有可能造成服务器利⽤率降低。

排序操作：在平时的sql语句中有好多sql都是有排序的操作，最典型的有group by ORDER BY，DISTINCT，有些连接操作，CREATE INDE X要⽤到排序操作

测试案例：服务器：内存24G (该参数只和内存有关，如在同⼀太服务器上测试，其他指标不⽤关⼼)

sql语句(该语句是业务真实语句)：

insert into dw_analyse_file ( minserid ,rowcount,fname ,imagepathcon,actioncon,filefullpathcon,filepathcon, sourcefilecon ,newfilenamecon )

select min(serid) as minserial,count(*) as rowcount,fname , imagepathcon,actioncon,filefullpathcon,filepathcon, sourcefilecon, newfilenameconfrom source_analyse_file

group by fname , imagepathcon,actioncon,filefullpathcon,filepathcon, sourcefilecon , newfilenamecon说明：source_analyse_file据表⼤⼩6508 MB 、⾏数(已计数) 24031104执⾏第⼀组语句：

TRUNCATE TABLE dw_analyse_file; --清空表记录set work_mem='8000MB'; --更改参数⼤⼩8G

insert into dw_analyse_file ( minserid , rowcount, fname , imagepathcon,actioncon,filefullpathcon,filepathcon, sourcefilecon ,newfilenamecon )

select min(serid) as minserial,count(*) as rowcount,fname , imagepathcon,actioncon,filefullpathcon,filepathcon, sourcefilecon, newfilenameconfrom source_analyse_file

group by fname , imagepathcon,actioncon,filefullpathcon,filepathcon, sourcefilecon , newfilenamecon执⾏结果：

--查询成功: 共计8959657 ⾏受到影响，耗时: 202020 毫秒(ms)。cpu ⼀般在70以下执⾏第⼆组语句：

TRUNCATE TABLE dw_analyse_file; --清空表记录set work_mem='100MB'; --更改参数⼤⼩100MB

insert into dw_analyse_file ( minserid , rowcount, fname , imagepathcon,actioncon,filefullpathcon,filepathcon, sourcefilecon ,newfilenamecon )

select min(serid) as minserial,count(*) as rowcount,fname , imagepathcon,actioncon,filefullpathcon,filepathcon, sourcefilecon, newfilenameconfrom source_analyse_file

group by fname , imagepathcon,actioncon,filefullpathcon,filepathcon, sourcefilecon , newfilenamecon

注意观察数据⽬录/opt/pgsql9.1/datathw/base/pgsql_tmp 下有临时⽂件产⽣。这些⽂件就为临时排序⽂件，如果work_mem的⼤⼩设置⾜够⼤就不会出现临时⽂件，他就会在内存中排序了。如图

执⾏结果：

--查询成功: 共计8959657 ⾏受到影响，耗时: 1330200 毫秒(ms)。cpu ⼀般都保持在99-100总结: work_mem 参数的调整可以节省数倍的时间和降低cpu的使⽤率。特别提醒：

1）不要在数据库中同时执⾏多个类似的sql语句，因为每个sql语句都会占⽤很多的内存。内存设置的⼤⼩和表的⼤⼩相近即可（⽆where条件时）。

2）该参数⼀般会在⼤批量数据处理时使⽤。尽量在前台设置参数，尽量不要在参数⽂件中去配置（⽐较危险）。