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

客服微信

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

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

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优点和缺点
优点:

缺点:


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[,..])]] 

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 元命令的区别:


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 详细查看

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

图片



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

图片

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


4、备份方式

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