锐客网

  1. 首页 > 睿知 > it技术 > >

--PostgreSQL怎么正确的开始POSTGRESQL 调优的活动1

IT知识postgresql

书史足自悦，安用勤与劬。这篇文章主要讲述--PostgreSQL怎么正确的开始POSTGRESQL 调优的活动1相关的知识，希望能为你提供帮助。

文字内容来自于 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 中的稳定性
全表扫描和索引扫描
表膨胀
大表分区的问题
在不同表空间移动表的问题


但今天讨论的都不是这些，今天讨论的是错误的配置参数的问题
【--PostgreSQL怎么正确的开始POSTGRESQL 调优的活动1】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);



同时可以通过快捷命令来查看表中的设置


待.....



?

    推荐阅读


    上一篇：PostgreSQL读书会一期系统目录表1

    下一篇：Git入门第二讲（创建版本库）