优化你的PostgreSQl（FreeBSD）

 

本文根据Pgsql手册的内容整理而来。
一直在用Pgsql，这些天有种想要将其优化到底的想法，对其做了如下改动：

FreeBSD内核的修改

首先，察看几个内核数值 ：
#sysctl -a | grep shmall
#sysctl -a | grep shmmax
#sysctl -a | grep semmni
# sysctl -a | grep semmns

shmall 为可用共享内存大小，以 4KB 页为单位计算，即最终的结果*4才是你共享内存的大小数。系统默认为8192，32M。怎么也要128以上吧？如果你收到来自shmget的类似Invalid argument这样的错误信息，那么很有可能是你已经超过这个限制了。
shmmax为最大共享内存大小，这个值至少应该大于 250kB + 8.2 kB * shared buffers + 14.2 kB *max connections

semmni知该怎么翻译好，应该是“信号灯标示符的最小值”之类的意思。由于pgsql每16个连接占据一个“信号灯”且每16个连接之后还会有一个备用的连接，故理论上这个值应该且必须大于max_connections / 16 ，系统默认非常保守，只有10。

semmns，原理同上，这个值应该设置为大于(max_connections / 16) *17+10，经过测试，这个值减去15，基本上就是max_connctions能设置的最大数值。

我的设置为(注：服务器内存256M)

kern.ipc.shmall=32768
kern.ipc.shmmax=134217728
kern.ipc.semmap=256
kern.ipc.semmni=256
kern.ipc.semmns=512
kern.ipc.semmnu=256 

需要注意的是，semmni和semmns两个参数不可通过sysctl设置，只能选择编进内核或者修改/boot/loader.conf。对于其他参数可以直接修改/etc/sysctl.conf文件或者内核配置文件中添加options的方法，考虑到日后调整方便，建议采用前一方法。修改成功后重启机器（不嫌麻烦可以手动添加）。
#reboot

以上几个数值解决了很多BSD用户碰到的修改了Pg配置而无法启动pg的尴尬

修改postgresql.conf

这里修改起来就比较直观了。
一般情况下设置maintenance_work_mem和checkpoint_segments会有不错的性能提升，但对于高负荷情况下主要就是shared buffers和max connections。这里默认的配置文件中也有比较详细的介绍。

重启pgsql:
#/usr/local/etc/rc.d/010.pgsql.sh restart

如果你还有足够的勇气时间以及金钱

1. 使用64位的操作系统、更大的内存、更快的硬盘——废话！
2. 将数据文件和系统文件放置在两个卷上（可以使用ln作连接）。
3. 开启内核的MD，将一部分内存模拟成磁盘存放数据文件。这在少量数据的情况下可以尝试，但可靠性下降，适合只读不写的数据库。
4. 适当调高WAL预写缓冲，但这意味着系统可靠性下降。
5. 修改make.conf ，添加-O3编译参数，重新编译PGSQL和kernel，当然这是在连使用-O2和所有优化参数之后仍无济于事之后。效率提升<1%