当前位置: fuhua-pet->PostgreSQL > PostgreSQL技术大讲堂 - 第32讲:数据库参数调整

PostgreSQL技术大讲堂 - 第32讲:数据库参数调整

2023-11-20作者:fuhua-pet来源:www.fuhua-pet.com

PostgreSQL从小白到专家,是从入门逐渐能力提升的一个系列教程,内容包括对PG基础的认知、包括安装使用、包括角色权限、包括维护管理、、等内容,希望对热爱PG、学习PG的同学们有帮助,欢迎持续关注CUUG PG技术大讲堂。


第32讲:数据库参数调整


第32讲:11月04日(周六)19:30-20:30,往期文档及视频,联系CUUG

内容 : 数据库常用参数调整:shared_buffers、wal_buffer、effective_cache_size、等等


shared_buffers

· PostgreSQL使用自己的缓冲区,也使用操作系统缓冲IO。这意味着数据存储在内存中两次,首先是PostgreSQL缓冲区,然后是操作系统缓冲区。

· 与其他数据库不同,PostgreSQL不提供直接IO。这称为双缓冲。

· PostgreSQL缓冲区称为shared_buffers,它是大多数操作系统最有效的可调参数。

· PostgreSQL将用shared_buffers参数缓存如下数据:

表数据

索引

执行计划

· 初始化参考值:物理内存1/4


wal_buffer

· PostgreSQL将其WAL(预写日志)记录写入缓冲区,然后将这些缓冲区刷新到磁盘。

· 缓冲区的默认大小,由wal_buffers定义,但如果您有大量并发连接,则较高的值可以提供更好的性能。

· 该缓冲区的作用是临时存放redo log,所以分配太大不会对性能有好处,一般10MB左右。


effective_cache_size

· 该effective_cache_size提供了可以用于磁盘缓存存储器的估计。

· 它只是一个指导原则,而不是确切分配的内存或缓存大小。

· 它不分配实际内存,而是告诉优化器内核中可用的缓存量。

· 如果将此值设置得太低,查询计划程序可以决定不使用某些索引,即使它们有用。

· 因此,设置较大的值总是有益的。

· 建议使用默认值。


work_mem

· 指定在写入磁盘上的临时文件之前,ORDER BY,DISTINCT,JOIN和哈希表的内部操作将使用的内存量。

· 此配置用于复杂排序,如果必须进行复杂排序,则增加work_mem的值以获得良好结果。内存中的排序比溢出到磁盘的排序快得多。

· 设置非常高的值可能会导致部署环境出现内存瓶颈,因为此参数是按用户排序操作。

· 如果您有许多用户尝试执行排序操作,系统将为所有用户分配 work_mem * 总排序操作 。

· 全局设置此参数可能会导致内存使用率过高,强烈建议在会话级别修改它。

· postgres=# SET work_mem=“2MB”; (会话级配置)


maintenance_work_mem

· maintenance_work_mem是用于维护任务的内存设置。默认值为64MB。本参数可以针对每个session设置。

· 设置较大的值有助于执行VACUUM,RESTORE,CREATE INDEX,ADD FOREIGN KEY和ALTER TABLE等任务。

· 由于会话中只能同时执行其中一个操作,并且通常没有多个同时运行,因此它可能比work_mem大。

· 较大的配置可以提高VACUUM和数据库还原的性能。

· 执行autovacuum时,或者配置autovacuum_work_mem参数来单独管理它。


FSYNC

· 如果启用了fsync,PostgreSQL将尝试确保将更新写入物理磁盘,会延长响应时间对性能有一定影响。

· 这可确保在操作系统或硬件崩溃后可以将数据库群集恢复到一致状态。

· 禁用fsync通常可以提高性能,但在发生电源故障或系统崩溃时可能会导致数据丢失。

· 从外部数据重新创建整个数据库,则建议停用fsync。


synchronous_commit

· 指定在命令向客户端返回“成功”指示之前,事务提交是否将等待WAL记录写入磁盘。这是性能和可靠性之间的权衡。默认设置为“on”。

· 可能的值包括:“on”,“remote_apply”,“remote_write”,“local”和“off”。

· 与fsync不同,禁用此参数不会产生任何数据库不一致的风险:操作系统或数据库崩溃可能导致丢失一些最近发生的可能提交的事务,但数据库的状态将与这些事务完全相同,未提交的将被抛弃。

· 当性能比事务持久性更重要时,停用synchronous_commit可能是一个有用的替代方法。

· 这意味着成功状态与保证写入磁盘之间会存在时间差。在服务器崩溃的情况下,即使客户端在提交时收到成功消息,数据也可能丢失。在这种情况下,事务提交非常快,因为它不会等待刷新WAL文件,但可靠性受到损害。


checkpoint_timeout

· checkpoint_timeout:检查点启动的时间间隔

· 将此设置得太低会减少崩溃恢复时间,因为更多数据会写入磁盘,但由于每个检查点都会占用宝贵的系统资源,因此也会损害性能。高频率的检查点可能会影响性能。实例崩溃的机率与长时间运行的性能相比,实例崩溃所占的比重要小的多,该值设置为实例崩溃后客户允许恢复的时间。

· 检查点进程将数据刷新到数据文件中。

· 发生CHECKPOINT时完成此活动。这是一项昂贵的操作,可能会导致大量的IO。 整个过程涉及昂贵的磁盘读/写操作。

· checkpoint_completion_target衡量检查点完成的时间长度。


checkpoint_completion_target

· 数据库中一个至关重要的参数,主要与参数checkpoint_timeout(checkpoint_timeout)配合使用,值越小意味着检查点要越快完成,要求写得要快。

· 控制每次检查点发生时i/o的吞吐量,值越高,则i/o占用的资源越少,数据库性能越好;值越低,则i/o占用的资源越多,影响数据库性能,但是提高检查点完成速度。

其它常见参数

· max_connections

确定与数据库同时连接的最大数量。因为每个客户端都可以配置内存资源,因此,客户机的最大数量表明使用的内存的最大数量。

· superuser_reserved_connections

在达到max_connection限制的情况下,这些连接保留给超级用户。

· temp_buffers

设置每个会话使用的最大临时缓冲区数。 这些是仅用于访问临时表的本地会话缓冲区。 会话将根据需要分配临时缓冲区,直到temp_buffers给出的限制。

· max_wal_size

允许WAL日志所在目录使用的最大尺寸,默认为1GB。

该参数与wal_segment_size相关,默认是16MB,允许存放64个wal段文件。

  • 《PostgreSQL 数据库在国内的发展前景》
  • 7月26日,工信部人才交流中心PostgreSQL认证【纸质证书】到了!
  • PostgreSQL PG夜话(第21期):数据库老陈和德哥 聊一聊“Data+AI”
  • PostgreSQL PG夜话(第22期):数据库老陈 与 德哥 聊“DBA的发展”
  • 国家支持!是时候考一个PostgreSQL数据库管理员认证了!
  • 国内备受好评PostgreSQL数据库性能如何?
  • 聊一聊PostgreSQL数据库,以及PostgreSQL认证体系
  • ocm认证考试费用多少钱,Oracle OCM考几科
  • OCP认证没有含金量了?来看看Oracle OCP 证书的用处!
  • 2024云栖大会,9月19杭州见:云启智跃·产业蝶变
  • OCM认证烂大街了吗OCM认证还值得投资吗
  • 旧版本的Oracle OCM证书怎么升级到最新版本?
  • 企业还会高薪招聘OCM证书获得者吗?
  • 腾讯云数据库认证官方的考试费是多少钱?
  • PostgreSQL技术大讲堂 - 第63讲:duckdb数据库盛宴
  • 金蝶集团信创PostgreSQL数据库认证开班啦!!!
  • 万“象”更新 - 看PostgreSQL数据库的前世与今生
  • 炙手可热!信创时代下的PostgreSQL数据库
  • Oracle 19c OCP 认证考试 082 题库(第18题)- 2024年修正版
  • Oracle 19c OCP 认证考试 082 题库(第19题)- 2024年修正版
  • Oracle 19c OCP 认证考试 082 题库(第20题)- 2024年修正版
  • Oracle 19c OCP 认证考试 082 题库(第22题)- 2024年修正版
  • Oracle 19c OCP 认证考试 082 题库(第23题)- 2024年修正版
  • Oracle 19c OCP 认证考试 082 题库(第24题)- 2024年修正版
  • Oracle 19c OCP 认证考试 082 题库(第26题)- 2025年修正版
  • 考前须知:Oracle OCP考试流程和准备
  • 你还在犹豫要不要考个OCP认证吗
  • PostgreSQL技术大讲堂 - 第66讲:PG数据库参数调整
  • 信创领域认证,来自工信部人才交流中心的PostgreSQL培训班
  • “多图警告” - 带你快速浏览2024杭州云栖现场
  • 汇集众多AI硬科技 9月19日 阿里云栖大会即将到来
  • 腾讯云TDSQL数据库认证值得考吗?来看看TDSQL证书有什么用
  • PostgreSQL技术大讲堂 - 第64讲:给pg插上翅膀-pg_quack
  • 如何考取PostgreSQL认证证书?
  • 有序推进合作互补,共建PostgreSQL良好生态环境
  • PostgreSQL技术大讲堂 - 第62讲:TXID的冻结-拆弹专家2
  • 【优技教育】Oracle 19c OCP 082认证考试题库(第5题)- 2024年修正版
  • 【优技教育】Oracle 19c OCP 082题库(第1题)- 2024年修正版
  • 【优技教育】Oracle 19c OCP 082题库(第3题)- 2024年修正版
  • 【优技教育】Oracle 19c OCP 082题库(第8题)- 2024年修正版
  • 【优技教育】Oracle 19c OCP 082题库(第13题)- 2024年修正版
  • 【优技教育】Oracle 19c OCP 082题库(第14题)- 2024年修正版
  • Oracle 19c OCP 082认证考试题库(第6题)- 2024年修正版
  • Oracle 19c OCP 082认证考试题库(第7题)- 2024年修正版
  • 8月26日,恭喜CUUG 肖同学获得19c OCM证书!
  • 报名啦!PolarDB数据库创新设计赛(天池杯)等你来战
  • 相约华中科技大学,移动云技术论坛 第四期:打造数智时代新型数据底座
  • PostgreSQL技术大讲堂 - 第65讲:pg与Oracle的绑定变量窥视对比
  • 【优技教育】Oracle 19c OCP 082题库(第16题)- 2024年修正版
  • 单考一个OCP认证?还是OCP和OCM认证都拿到手?