陈老师:1415968548 乐老师:354331153
客服热线:
19941464235

客服微信

【技术文章】PostgreSQL配置优化(二)

作者:崔鹏
发布时间:2022-06-09 11:18
浏览量:29

01

vacuum


什么时候会触发autovacuum?

1.当update,delete的tuples数量超过 autovacuum_vacuum_scale_factor * table_size + autovacuum_vacuum_threshold

2.指定表上事务的最大年龄配置参数autovacuum_freeze_max_age,默认为2亿,达到这个阀值将触发 autovacuum进程,从而避免 wraparound 事务回卷。

每个表dead tuples的数量(包括用户表和系统表)

pg_stat_all_tables.n_dead_tup 死亡行得到估计数量
# dead/live tuples在每个表中的比率
(n_dead_tup 死亡行得到估计数量/ n_live_tup 活着的行的估计数量)

# 每一行的空间(pg_class.relpages / pg_class.reltuples)


relpages:该表磁盘表示的尺寸,以页面计(页面尺寸为BLCKSZ)。这只是一个由规划器使用的估计值。它被VACUUM、ANALYZE以及一些DDL命令(如CREATE INDEX)所更新。

reltuples:表中的存活行数。这只是一个由规划器使用的估计值。它被VACUUM、ANALYZE以及一些DDL命令(如CREATE INDEX)所更新。

其中两个参数分别为:autovacuum_vacuum_threshold = 50 #阈值autovacuum_vacuum_scale_factor = 0.2 #比例因子


死亡元组数可以认为是pg_stat_all_tables中n_dead_tup的值。

由以上公式可以看出,一般在dead tuple达到20%时,会进行自动清理,50行的阈值是为了防止非常频 繁地清理微小的表。这个默认的比例比较适用于中小表,但如果表较大时,比如10GB大小的表,dead tuple达到2GB时才清理,这在清理的过程中会严重影响性能,一般来说解决方案有两种:

  • 一是调小大表的比例因子

  • 二是放弃比例因子,调大阈值

要注意在postgresql.conf中修改这些参数会产生全局影响,尤其调大阈值或调小比例因子会影响小表的清理,不过综合全局来看,可以忽略一些小表的清理问题。


比较理想的方案:

在postgresql.conf中忽略比例因子,设置较大的阈值(例如设置autovacuum_vacuum_scale_factor = 0和autovacuum_vacuum_threshold = 10000),然后根据各个表的delete和update频繁程度以及表的数据量单独为每个表设置阈值:
ALTER TABLE test SET (autovacuum_vacuum_threshold = 100);

触发autovacuum的消耗:

autovacuum的清理过程是从数据文件中读取页面(默认8kB数据块),并检查它是否需要清理,如果没有死亡元组,页面就会被丢弃而不做任何更改,否则它被清理(死元组被删除),被标记为“脏页”并最终写出来。成本核算基于postgresql.conf定义三个参数:
  • vacuum_cost_page_hit = 1 #如果页面是从shared_buffers读取的,则计为1

  • vacuum_cost_page_miss = 10 #如果在shared_buffers找不到并且需要从操作系统中读取,

  • 则计为10(它 可能仍然从RAM提供,但我们不知道)

  • vacuum_cost_page_dirty = 20 #当清理修改一个之前干净的块时需要花费的估计代价,它表示再次把脏块刷 出到磁盘所需要的额外I/O,默认值为20


再加上另外两个参数即可计算出清理操作的成本:

比如:延迟20ms,则每秒可以清理50轮,乘以200后,即为10000的成本,那么:

shared_buffers读取是 10000/1*8KB = 80MB/s
os中读取是 10000/10*8KB = 8MB/s
vacuum写入是 10000/20*8KB = 4MB/s
可以根据硬件的配置,以及autovacuum主要是顺序读写的情况增加autovacuum_vacuum_cost_limit
参数,比如增加到1000或2000,这会使吞吐量增加5倍或10倍。当然可以调整其他参数(每页操作成本,睡眠延迟),一般来说这几个参数默认足够,如果有明显autocuum问题时,再酌情修改。



2

检查点的作用


1.将事务提交的修改写进disk(写脏数据);保证数据库的完整性和一致性。

2.缩短恢复时间,将脏页写入相应的数据文件,确保修改后的文件通过fsync()写入到磁盘。


检查点触发条件:

  • 1.checkpoint_timeout 设置的间隔时间自上一个检查点已经过去(默认间隔为 300 秒(5 分钟))。

  • 2.在 9.4 或更早版本中,为checkpoint_segments设置的 WAL 段文件的数量自上一个检查点以来已经被消耗(默认数量为 3)。

  • 3.在 9.5 或更高版本中,pg_xlog(在 10 或更高版本中为 pg_wal)中的 WAL 段文件的总大小已超过参数max_wal_size的值(默认值为 1GB(64 个文件))。

  • 4.PostgreSQL 服务器在smart或fast模式下停止。

  • 5.当超级用户手动发出 CHECKPOINT 命令时,它的进程也会这样做。

  • 6.写入WAL的数据量已达到参数max_wal_size(默认值:1GB)

  • 7.执行pg_start_backup函数时

  • 8.在进行数据库配置时(例如CREATE DATABASE / DROP DATABASE语句)


  • checkpoint_timeout (integer)

自动 WAL 检查点之间的最长时间。如果指定值时没有单位,则以秒为单位。合理的范围在 30 秒到 1 天之间。默认是 5 分钟(5min)。增加这个参数的值会增加崩溃恢复所需的时间。这个参数只能在postgresql.conf文件中或在服务器命令行上设置。


  • checkpoint_completion_target (floating point)

指定检查点完成的目标,作为检查点之间总时间的一部分。默认是 0.5。这个参数只能在postgresql.conf文件中或在服务器命令行上设置。


  • checkpoint_flush_after (integer)

当执行检查点时写入的数据量超过此数量时,就尝试强制 OS 把这些写发送到底层存储。这样做将会限制内核页面高速缓存中的脏数据数量,降低在检查点末尾发出fsync或者 OS 在后台大批量写回数据时被卡住的可能性。那常常会导致大幅度压缩的事务延迟,但是也有一些情况(特别是负载超过shared_buffers但小于 OS 页面高速缓存)的性能会降低。这种设置可能会在某些平台上没有效果。如果指定值时没有单位,则以块为单位,即为BLCKSZ 字节,通常为8kb。合法的范围在0(禁用强制写回)和2MB之间。Linux 上的默认值是256kB,其他平台上是0(如果BLCKSZ不是8kB,则默认值和最大值会按比例缩放到它)。这个参数只能在postgresql.conf文件中或者服务器命令行上设置。


  • checkpoint_warning (integer)

如果由于填充WAL段文件导致的检查点之间的间隔低于这个参数表示的时间量,那么就向服务器日志写一个消息(它建议增加max_wal_size的值)。如果指定值时没有单位,则以秒为单位。默认值是 30 秒(30s)。零则关闭警告。如果checkpoint_timeout低于checkpoint_warning,则不会有警告产生。这个参数只能在postgresql.conf文件中或在服务器命令行上设置。


  • max_wal_size (integer)

在自动 WAL 检查点之间允许 WAL 增长到的最大尺寸。这是一个软限制, 在特殊的情况下 WAL 尺寸可能会超过max_wal_size, 例如在重度负荷下、archive_command失败或者高的 wal_keep_segments设置。如果指定值时没有单位,则以兆字节为单位。默认为 1 GB。增加这个参数 可能导致崩溃恢复所需的时间。这个参数只能在postgresql.conf 或者服务器命令行中设置。


  • min_wal_size (integer)

只要 WAL 磁盘用量保持在这个设置之下,在检查点时旧的 WAL 文件总是 被回收以便未来使用,而不是直接被删除。这可以被用来确保有足够的 WAL 空间被保留来应付 WAL 使用的高峰,例如运行大型的批处理任务。如果指定值时没有单位,则以兆字节为单位。默认是 80 MB。这个参数只能在postgresql.conf 或者服务器命令行中设置。



03

PITR和WAL复制





图片







PITR模式下的PostgreSQL会在基础备份上重放归档日志中的WAL数据,从pg_start_backup创建的重做点开始,恢复到你想要的位置为止。在PostgreSQL中,想要恢复到的位置被称为恢复目标。

时间线历史文件在第二次及后续PITR过程中起着重要作用。通过尝试第二次恢复,我们将探索如何使用它。同样,假设你在12:15:00时间点又犯了一个错误,错误发生在时间线ID为2的数据库集簇上。‍




在这种情况下,为了恢复数据库集簇,需要创建一个如下所示的recovery.conf文件:


restore_command ='cp /opt/pg_arch/%f %p'  recovery_target_time = "2018-7-16 12:15:00 GMT"  recovery_target_timeline = 2 参数recovery_target_time被设置为犯下新错误的时间,而recovery_target_ timeline被设置为2,以便沿着这条时间线恢复。


图片

重启PostgreSQL服务器并进入PITR模式,数据库会沿着时间线标识2进行恢复,如下图所示。
以下操作都在postgres用户下进行:
配置归档命令mkdir /opt/archvi $PGDATA/postgresql.auto.confarchive_mode = onarchive_command = 'DATE=`date +%Y%m%d`; DIR="/opt/arch/$DATE"; (test -d $DIR || mkdir -p $DIR) && cp %p $DIR/%f'wal_level = replica 使用pg_basebackup备份数据库mkdir /home/postgres/bak/pg_basebackup -D /home/postgres/bak/ -Ft -P -R -Upostgres select * from t_rec;  更新数据,模拟宕机create table t_rec (id int,time timestamp);insert into t_rec values (1,now());insert into t_rec values (2,now());insert into t_rec values (3,now());insert into t_rec values (4,now());insert into t_rec values (5,now());select pg_switch_wal();checkpoint;select * from t_rec; 关闭实例pg_ctl -m f stop删除pg_root13数据库目录rm -rf /opt/pg_root13/* 基于时间点恢复将备份的数据文件解压到$PGDATA目录,wal日志解压放到$PGDATA/pg_wal目录mkdir /opt/pg_root13/pg_waltar -xvf /home/postgres/bak/base.tar -C /opt/pg_root13tar -xvf /home/postgres/bak/pg_wal.tar -C /opt/pg_root13/pg_walchmod 0700 /opt/pg_root13rm -f /opt/pg_root13/standby.signaltouch /opt/pg_root13/recovery.signalchmod 600 /opt/pg_root13/recovery.signal 编辑$PGDATA/postgresql.auto.conf文件restore_command = 'cp /opt/arch/20220325/%f %p'recovery_target_timeline = 'latest'启动数据库 %p 表示wal文件名$PGDATA的相对路径, 如pg_wal/00000001000000190000007D%f 表示wal文件名, 如00000001000000190000007D 启动DBpg_ctl start
postgresql.auto.conf内容参考  listen_addresses = '0.0.0.0'port = 5432max_connections = 1000shared_buffers = 410MBwal_buffers = 120MBsuperuser_reserved_connections = 20unix_socket_directories = '.'unix_socket_permissions = 0700tcp_keepalives_idle = 60tcp_keepalives_interval = 10tcp_keepalives_count = 10vacuum_cost_delay = 10bgwriter_delay = 10mssynchronous_commit = offwal_writer_delay = 10mslog_destination = 'csvlog'logging_collector = onlog_directory = 'pg_log'log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'log_file_mode = 0600log_truncate_on_rotation = onlog_rotation_age = 1dlog_rotation_size = 10MBhot_standby = onarchive_mode = onarchive_command = 'DATE=`date +%Y%m%d`; DIR="/opt/arch/$DATE"; (test -d $DIR || mkdir -p $DIR) && cp %p $DIR/%f'wal_level = replicarestore_command = 'cp /opt/arch/20220325/%f %p'recovery_target_timeline = 'latest'






图片







那流复制是怎么实现的呢?

主要涉及到几个backend辅助进程:walwriter,walsender,&&&  walreceiver,startup

当用户连接进行数据操作,产生对应的WAL日志记录后,walwriter会周期性地把产生的WAL page刷新到磁盘中,如果配置了备库,则walsender会不断将WAL page发给备库的walreceiver进程,walreceiver进程会把对应WAL page直接写到本地磁盘,同时slave上的startup辅助进程会不断地应用xlog日志,改变本地数据,实现与主库之间的数据同步。

而且,通过配置,备库是可以接受用户的只读请求。

wal_level max_wal_sendershot_standbywal_keep_segments synchronous_commit单机on 写入wal segment中Off 写入buffer就会返回成功Local 和on的类似 流复制On 本地wal落盘、备库wal落盘。remote_apply 本地wal已落盘、备库wal已落盘并且已完成重做。remote_write 需等待备库接收主库发送的日志流,并写入备节点操作系统缓存中,之后返回成功