暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

【技术文章】PostgreSQL日常维护(1)

云贝教育 2022-05-31
177

点击上方蓝字,关注我们












PG日常维护



一、事物可见性与多版本并发控制




MVCC,Multi-Version Concurrency Control,多版本并发控制。
MVCC是来处理并发的问题,提高并发的访问效率,读不阻塞写。 
PostgreSQL的特色之一是它的并发控制机制,在维护一致性和完整性的同时,尽量避免读写的堵塞。对于传统数据库,为了维护一致性和完整性,避免一个事务看到其它并发事务更新而到会不一致的数据,通常采用的是LOCK机制。这样付出的代价是,当锁请求无法被响应时,待处理的请求必须进入等候队列,甚至等待超时不被处理。
人们一般把基于锁的并发控制机制称成为悲观机制,而把MVCC机制称为乐观机制。这是因为锁机制是一种预防性的,读会阻塞写,写也会阻塞读,当锁定粒度较大,时间较长时并发性能就不会太好;而MVCC是一种后验性的,读不阻塞写,写也不阻塞读,等到提交的时候才检验是否有冲突,由于没有锁,所以读写不会相互阻塞,从而大大提升了并发性能。 
MVCC通过避开传统数据库的LOCK机制,最大限度的减少锁竞争以允许合理的多用户环境中的性能。

1、为什么需要MVCC?

数据库在并发操作下,如果数据正在写,而用户又正在读,可能会出现数据不一致的问题。比如一行数据只写入了前半部分,后半部分还没有写入,而此时用户读取这行数据时就会出现前半部分是新数据、后半部分是旧数据的现象,造成前后数据不一致。解决这个问题最简单的方法就是加读写锁,写的时候不允许读,读的时候不允许写,不过这样大大降低了数据库的并发性能,此时便引入了MVCC的概念,它的目的便是实现读写事务互不阻塞,从而提高数据库的并发性能。

2、不同的MVCC机制

实现MVCC的机制有两种:

a、写入数据时,把旧版本数据移到其他地方,如回滚段等,读取数据时,在回滚段中把数据读出来。 

b、写入数据时,保留旧版本数据,并插入新数据。像oracle、innodb数据库使用的是第一种方式,PostgreSQL使用的是第二种方式Undo 。


3、MVCC涉及的几个概念
事务id:在PostgeSQL中,每个事务都存在一个唯一的id,也称为xid,可通过txid_current()函数获取当前的事务id。
tuple:每一行数据,称为一行元组,一个tuple
ctid:tuple中的隐藏字段,代表tuple的物理位置
xmin:tuple中的隐藏字段,在创建一个tuple时,记录此值为当前的事务id
xmax:tuple中的隐藏字段,默认为0,在删除tuple时,记录此值为当前的事务id
cmin/cmax:tuple中的隐藏字段,标识同一个事务中多个语句的顺序,从0开始。
clog : 当前事务的提交状态
  • IN_PROGRESS 0 正在进行中

  • COMMITTED 1 已提交

  • ABORTED 2 中止

  • SUB_COMMITTED 3 子事物






4、数据的修改过程

a. 首先 backend 开启是一个事务,获得一个事务号 XID;

b. 在这个事务中对数据的任意修改,都被 XID 标记。

c. 其他 backend 在扫描数据时,会看到被这个 XID 修改过的数据,根据当前的隔离级别,选择对这些数据是否可见(默认的读已提交隔离级别看不到这些数据)。

d. 只有当此 XID 最后被标记成 commit (写 WAL commit log 和写 clog xact)后,其他的 backend 才能看到这个 XID 修改的数据。


5、PostgreSQL中的MVCC优点和缺点
优点:
  • 使用MVCC,读操作不会阻塞写,写操作也不会阻塞读,提高了并发访问下的性能

  • 事务的回滚可立即完成,无论事务进行了多少操作

  • 数据可以进行大量更新,不段像MySQL innodb引擎和Oracle那样需要保证回滚段不会被耗尽


缺点:
  • 事务ID个数有限制 2 32

  • 事务ID由32位数保存,而事务ID递增,当事务ID用完时,会出现wraparound问题。

  • PostgreSQL通过VACUUM机制来解决该问题。对于事务ID,PostgreSQL有三个事务ID有特殊意义:

  • 0代表invalid 无效的事务号

  • 1代表bootstrap事务号

  • 2代表frozon事务。frozon transaction id比任何事务都要老

  • 可用的有效最小事务ID为3。VACUUM时将所有已提交的事务ID均设置为2,即frozon。之后所有的事务都比frozon事务新,因此VACUUM之前的所有已提交的数据都对之后的事务可见。PostgreSQL通过这种方式实现了事务ID的循环利用。


6、MVCC的工作机制

PostgreSQL中的MVCC就是通过以上几个隐藏字段协同实现的,下面举几个具体的例子来看下工作机制首先我们开启事务插入一条数据,其中ctid代表数据的物理位置,xmin为当前事务id,xmax为0继续在上个事务中再插入一条数据,因为在同一个事务中,可以看到cmin,cmax按顺序增长修改id为1的数据name为c,此时id为1的ctid变为了(0,3),同时开启另外一个窗口,可以看到id为1的xmax标识为修改数据时的事务id,即代表此条tuple已删除。





二、自动分析




1、概述
统计信息主要记录的就是表的行数页面以及不同列不同值的分布关系。表和索引的行数、页面数记录在系统表pg_class中,不同列不同值的分布关系在pg_statistic中。
PgStat子进程是专门的统计信息收集器进程。ANALYZE命令可以收集的统计信息,ANALYZE命令可以作为VACUUM的一个可选步骤。
可以由autovacuum后台进程触发一次统计信息的更新操作。也可以手工通过ANALYZE命令收集统计信息在某个表上,甚至某个列上。最好规划一个数据库范围内的ANALYZE,然后每天在系统不太繁忙的时候运行一次。

2、自动收集

自动收集统计信息是依赖AUTOVACUUM定时触发analyze:

触发 vacuum analyze的条件,表上(insert,update,delte 记录) >= 

autovacuum_analyze_scale_factor* reltuples(表上记录数) + autovacuum_analyze_threshold


3、手动收集

手动收集统计信息的命令是analyze命令,analyze的语法格式:

    analyze [verbose] [table[(column[,..])]]  
    复制
    • verbose:显示处理的进度,以及表的一些统计信息

    • table:要分析的表名,如果不指定,则对整个数据库中的所有表作分析 

    • column:要分析的特定字段的名字默认是分析所有字段

    • analyze 命令 会在表上加读锁


    4、谁来更新统计信息?

    统计信息是有autovacuum进程更新,PG使用MVCC机制进行数据库的并发控制,因此同样需要一组后台进程进行过期版本的清理。 

    autovacuum进程不止负责对过期元组进行清理,同时也负责定期更新表的统计信息。

     

    4.1 为什么要把这两个操作放在一起?

    PG的MVCC机制数据和数据的旧版本是统一存放在表文件上的,在清理时要进行全表扫描的操作,而统计信息的收集也是需要读取表文件的,这两个操作放在一起做可以在一定程度上节省IO;清理废旧元组和更新统计信息都是通过收集表的元组变更数据来触发的,共享一套机制,因此放在一起处理也比较方便;


    4.2 何时触发统计信息(when)

    统计信息有两种触发方式:

    a、用户使用analyze命令手动触发(analyze/vacuum analyze)

    在PG中,事务提交/回滚时会发消息给进程pgstat,pgstat会汇总这份信息并记录到文件中,autovacuum launcher会定期读取文件,获得,当某个表的改动超过阈值时便会触发一次统计信息的更新操作。

    需要注意的是autovacuum worker也会给pgstat发消息,但实际上这个消息是通知pgstat统计更新/清理已经完成,可以清理统计信息了。因此stat file只有pgstat更新,launcher只是读取,不涉及并发写冲突。


    5、收集进程及目录存放

    PG用于收集统计信息的进程   

      stats collector process 源码路径 :"src/backend/postmaster/pgstat.c"
      复制

      统计信息的存放地启动时, 读入已存在的统计文件, 或初始化0. 数据库运行过程中存储在内存和temp文件 postgresql.conf ->stats_temp_directory , 推荐配置在高速磁盘或内存文件系统中,正常关闭时会把统计信息从tmp目录拷贝到$PGDATA/pg_stat目录中,确保统计信息不会丢失。




      三、备份与恢复




      1、逻辑备份恢复

        pg_dump   可以选择一个数据库或部分表进行备份
        pg_dumpall 备份集簇服务所有数据库
        copy 导入导出表数据
        COPY 命令是SQL命令,\copy 命令是元命令。
        复制

        COPY 命令 \copy 元命令的区别:

        • COPY 命令必须具有SUPERUSER超级权限(将数据通过stdin/stdout方式导入导出情况除外),而 \copy 元命令不需要SUPERUSER权限。

        • COPY 命令读取或写入数据库服务端主机上的文件,而 \copy 元命令是从psql客户端主机读取或写入文件。

        • 从性能方面看, 大数据量导出文件或大文件数据导入数据库,COPY 比 \copy 性能高。


        2、物理备份恢复

        • 冷备份及恢复(文件系统复制)

        • 热备份及恢复(基于时间点的备份恢复)


        3、pg_basebackup 简介

        pg_basebackup是从postgresql 9.1版本开始提供的一个方便基础备份的工具。它会把整个数据库实例的数据都拷贝出来,而不只是把实例中的部分(如某个数据库或表)单独备份。
        注意:归档日志需要单独备份


        3.1  pg_basebackup工作原理

        1)创建检查点,打开FPW,创建备份标签(存储检查点位置,时间等信息)
        2)通过流复制协议与数据库建立连接,WAL Sender进程向pg_basebackup发送数据库物理文件。
        3)pg_basebackup接收到文件后写入目标位置(压缩或不压缩)。

         

        3.2 pg_basebackup 参数说明

        可以通过pg_basebackup --help 详细查看
        • -h 指定连接的数据库的主机名或IP地址,这里就是主库的ip。

        • -U 指定连接的用户名,此处是我们刚才创建的专门负责流复制的repl用户。

        • -F 指定了输出的格式,支持p(原样输出)或者t(tar格式输出)。

        • -x 表示备份开始后,启动另一个流复制连接从主库接收WAL日志。

        • -P 表示允许在备份过程中实时的打印备份的进度。

        • -R 表示会在备份结束后自动生成recovery.conf文件,这样也就避免了手动创建。

        • -D 指定把备份写到哪个目录,这里尤其要注意一点就是做基础备份之前从库的数据目录($PGDATA)目录需要手动清空。

        • -l 表示指定一个备份的标识


        Pgbackrest作为PostgreSQL数据库的备份和还原工具,在软件设计上综合考虑了功能和性能,包括完整性、一致性、安全性、数据压缩、异步并行等方面,其主要技术特性主要包括如下内容,如图所示:

        • S3兼容:支持备份存储S3和Azure兼容对象存储上,如对象存储Minio。

        • 压缩技术:支持gzip, bzip, lz4, zstd的压缩算法,并提供不同压缩等级

        • 异步、并行:在异步模式下通过并行推送和获取以加快处理速度,提高备份与还原处理效率。

        • 校验和:采用checksum 算法,验证备份期间复制的每个文件的校验和。在完全备份期间,将验证所有页面的校验和,而在差异备份和增量式备份期间,将验证已更改文件的校验和。

        • 加密:通过定义加密类型及密码,来保证备份数据的存储安全性



        在技术特性基础上,pgbackrest实现了备份、还原、保留策略、云端备份等相关功能,如图所示:

        • 保留策略:支持保留的备份数量,可以指定全量、差异备份的具体数量。

        • 还原方式:支持全量、增量、差异还原以及时间点还原,还同时支持还原指定数据库、表空间和链接支持等。

        • 异步归档:支持异步的推送和获取,利用pgbackrest并发特性,提升备份和还原的性能,同时在处理过程中对重新数据进行校验和处理,保证备份和还原数据正确性。

        • 检查:检查命令验证pgBackRest和archive_command设置是否已正确配置以进行归档和备份。

        • 监控:支持通过SQL查询方式,在PostgreSQL中创建函数查询检查信息,也可以通过Jq从Json中提取数据来进行查询。

        • 加密:基于用户提供的密码及加密类型对存储库进行加密,防止未经授权访问存储库中的数据。

        • 压缩:根据用户设定的压缩算法及压缩等级,将压缩后的数据存储到存储库中。

        注:pgbackrest目前支持Postgresql 8.3以上版本


        4、备份方式

        全量备份:将数据库集群的全部内容复制到备份中。数据库集群的第一个备份始终是全量备份。始终能够直接还原全量备份。全量备份不依赖于完整备份之外的任何文件来保持一致性。
        差异备份:仅复制自上次全量备份以来已更改的那些数据库集群文件。通过复制所选差异备份中的所有文件以及先前全量备份中的相应未更改文件来还原差异备份。差异备份的优点在于,与全量备份相比,它需要的磁盘空间更少,但是,差异备份和全量备份都必须有效才能还原差异备份。
        增量备份:仅复制自上次备份(可以是另一个增量备份,差异备份或全量备份)以来发生更改的那些数据库集群文件。由于增量备份仅包括自上次备份以来已更改的文件,因此它们通常比全量备份或差异备份小得多。与差异备份一样,增量备份依赖于其他备份才能有效还原增量备份。由于增量备份仅包括自上次备份以来的那些文件,因此,所有先前的增量备份(回到先前的差异备份),先前的差异备份和先前的全量备份都必须有效,才能执行增量备份的还原。如果不存在差异备份,则所有先前的所有增量备份都将还原为必须存在的先前的完整备份,并且完整备份本身必须有效才能还原增量备份。


        点击下面的链接,查看历史文章


        【技术文章】闲话数据库硬件


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


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

        文章转载自云贝教育,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

        评论