文字内容来自于 postgresqlopen 2019 Mistaken And Ignored Parameters While Optimizing A PostgreSQL Database 的部分内容，分2期来完成.
下为译文
如果要优化POSTGRESQL主要从两个部分入手,系统层面和数据库层面
1 OS metrics
将数据与日志的目录分离，是否都放在一个物理磁盘中
CPU 是否有瓶颈
cache drops and swapping (LINUX 系统）
2 Database
连接池和连接数量
常用的查询数据在CACHE 中的稳定性
全表扫描和索引扫描
表膨胀
大表分区的问题
在不同表空间移动表的问题
但今天讨论的都不是这些，今天讨论的是错误的配置参数的问题
1 shared_buffer what does this parameter mean?
1️⃣、 多少物理内存应该分配给shared buffers
2️⃣、 被请求的页面和被修改的页面
3️⃣、根据LRU 策略多少数据应该从这个位置flush
怎么设置正确share_buffers 就变成一个重要的问题 , 大致上我们听到的都是设置为总内存的25%给share_buffers,但事实上是这样的吗? 前几年的会上有人提出8G就够了的理论, 那给了90%的内存不是应该获得更好的性能?
所以我们不应该注意数字是多少,而是明白其中的原理. 关键的问题在于postgresql 不能 O_DIRECT , (MYSQL 可以O_DIRECT吗? )
例如你运行了一个查询,你需要的数据页面在磁盘上,你需要将数据页面从OS层面提交到你的share_buffers 在ORACLE ,MYSQL中也是这样操作的, 但POSTGRESQL 是不能直接从OS DISK上将数据提交到内存中,而是将需要的数据告知系统,从系统的层面来将页面缓存到系统的CACHE 中,在灌入到BUFFER_CACHE中. 所以这也就清楚了,为什么POSTGRESQL 和其他的数据库不同的点,以及为什么要将内存优先给OS Cache的原因.
但实际上25% 和 给8G 内存给POSTGRESQL 在全部时间这样做是不正确的,
你应该了解到底你的系统需要多少BUFFER CACHE 进行有效的供给, 例如通过pg_buffercache 扩展来了解你系统当前的HOT DATA 有多少在BUFFER CACHE 中. 来进行更有效的调整.
2 work_mem
order by distinct , merge joins ,hash join 等操作使用的内存配置,为什么要使用这个设置主要是避免将这些数据写入到磁盘,而是在内存中处理,我见过的最大的work_mem的设置叨叨 256MB 的WORK_MEM, 为了提高大数据量的排序操作,但实际上我们要注意你的最大的连接数和你的WORK_MEM之间的关系,你可以通过pg_stat 来定时snapshot 信息来分析到底需要杜少work_mem ,并且也不能满足所有的查询都要通过内存来进行排序的操作的需求,因为这样很容易让你的系统 OUT OF MEMORY
3 Track_counts
对于数据库的状态的追踪,例如insert, updates 和 删除等操作的跟踪,Autovacuum 将使用这些信息来做对表做vacuum 和 analyze等操作,
4 Autovacuum_vacuum_scale_factor & autovacuum_vacuum_threshold
要谈这两个参数就需要继续探讨 autovacuum , autovacuum主要的作用在于清理死TUPLE , 多版本的行信息存在POSTGRESQL 每一个表中,所以autovacuum要在每个表中进行dead tuple的清理.
例如 autovacuum_vacuum_scale_factor = 0.2 的意思在于如果这张表有1000行, autovacuum_vacuum_threshold = 50 则意味着, 当这张表的DML操作影响的行数达到250行则就触发了autovacuum 操作, 而另外一个参数,autovacuum_naptime 作为一个轮训的周期. 同时也与autovacuum_max_workers.
为什么要调整autovacuum threshold 的设置,主要原因在于
1 全局的参数可能使用在某些特殊情况不适合,例如有的表只有10条记录,有的表有上百万的记录,在同一个数据库里
2 让autovacuum 更有效的针对需要的表进行操作,尽量少浪费资源
3 使用表级别针对性的调整 autovacuum 替代通用参数,有利于以上2点
例如
alter table test1 set (autovacuum_vacuum_scale_factor=0,autovacuum_vacuum_threshold=1000);
这个就是针对test1 表属于基础表,不会更改的很频繁,并且表的行数也比较少,所以我们就可以不考虑scale_factor 仅仅设置一个较大的初始值.
实际上postgresql.conf本身针对整体也有类似的配置
alter table test1 set (autovacuum_vacuum_scale_factor=0,autovacuum_vacuum_threshold=1000);
同时可以通过快捷命令来查看表中的设置
