优化你的PgSQl(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%
推荐阅读:
时延 latency(亦称为延
在虚拟机的日常使用和开发中,我
之前我们通过几个概念简单的介绍