24PostgreSQL 日常维护和巡检 | 学习笔记（一）

开发者学堂课程【PostgreSQL 快速入门：24PostgreSQL 日常维护和巡检】学习笔记（一），与课程紧密连接，让用户快速学习知识。

课程地址：https://developer.aliyun.com/learning/course/16/detail/83

24 PostgreSQL 日常维护和巡检

 

内容介绍：

一、简介

二、日常维护

三、日常巡检

四、总结

 

一、简介

1.日常维护

日常维护：

http://www.postgresqlorg/docs/93/static/maintenance.html，手册中有简单的描述。

除此之外，再补充一些内容。

2.垃圾回收

比如日常的垃圾回收，专业术语叫vacuuming Basics。在postgre删除、更新记录时，会生成新的版本。即在数据库事务提交后，比如已经删掉或者更新的数据，会变成data  top。所以经常去做垃圾回收。

垃圾回收，postgre 可以自动完成。通过配置auto vacuum，然后在表的垃圾数据超过一定的阀值的时候，它会自动触发，进行垃圾回收。

3.统计信息的收集

updating planner statistics 日常的统计信息的收集，包括visibility  map ，visibility  map 可以减少垃圾回收扫描数据块的文件。比如表包含1000个数据块。做完多次的DML之后，实际上它被变更的dead tup数据块可能只占其中的一小部分。而大部分数据块里面都没有data tup，在做垃圾回收的时候，是不需要去扫描的，只需要扫描有data took 的数据块。这是visibility  map所承担的工作。

也是 all  the  welcome会去着重更新的内容。包括日常的DML都会去更新visibility  map。

prevent transaction ID wraparound failureas 因为在 post grade 里面，它的事务号是32位的，很容易达到它的上限。如果事务号达到上限之后，比如事务号是1用到，然后往后新增，输入事务号是2的时候，那么1肯定提交，是可以看到它的数据。

但是如果当前获得的事务号是一1时候，不能看到2的数据。事务号是2提交的数据，如果是repeatable read，看不到数据。当ID达到上限溢出后，重新变成小的。

比如变成1，比1数号大的事务是更好的事务。但是如果不去做freeze，循环使用XID，就会出现可能某些已经提交的数据未来看不到。为防止这种事情的发生，postgre要不停的去做feeeze，把事务号降低到可允许的范围，不会出现已经提交，但是在未来可能又变成未提交的状态，或者已经diss。

后面会做模拟事物消失的试验。

后面会做模拟事物消失的试验

二、日常维护

1. vacuum

日常的维护，是全局自动vacuum。在参数里面配置，要把它打开。如果不把它打开，就不会自动垃圾回收。查看有没有打开，可以通过show能够看到。

输入: Last login: sun Mar 11:08:14 2014 fron 10.0.0.64

[root@db-172-16-3-150 ~]# su -pg93

pg93@db-172-16-3-150-> psq1

psql(9.3.3)

Type "help" for help.

digoal # show autovacuum;

autovacuum

表及计划性的vacuum，比如auto  vacuum它有worker进程，比如总共有六条数据库，除掉postgres之外，除掉postgres、 temperature0、temperate1默认存在的三个数据库。

2.自动垃圾回收

worker 进程默认三个，也同时有三个自动垃圾回收的进程出现。其实是从launcher 进程产生的，叫worker 进程。也如果在整个集群当中，输入很多或者表很多的情况下，经常有更新或者删除操作，那么垃圾会很多，经常要做垃圾回收。或者是数据库的吞吐量很大，更新或者删除的吞吐量很大，可能垃圾回收无法满足需求，三个进程可能不够，那么可以多配几个。

这里有 maintenance _work_memo。每个 worker 进程都会消耗掉512M的内存。比如三个进程，那么同时会消耗掉1.5G 的内存。如果十个进程，消耗5G的内存，也是需要注意的。

这里有 maintenance _work_memo。每

另外自动垃圾回收没有匹配产生垃圾的速率时，可能要去做规划的手工vacuum。可以去查数据库里面。比如data数据库里可能需要多少做垃圾回收，触发垃圾回收的地方如下图。

比如默认是0.2，20%，也数据变更超过20%加threshold， datatop超过它的实际行的20%加50条的时候，就会触发autovacuum。

表到底要不要做以及有多少垃圾数据,可以去看 data  total。通过pg_stat_all_tables可以去看。

必须打开check count。如果不打开check  count ，根本跟踪不到多少垃圾数据。比如看到有两个表，test和t1两个表，data top 是0。

输入updata t1 set。会产生一样多的垃圾数据。比如原来是10万条数据，那时候它就会产生十万条数据。

已经做autovacuum，data top 会回收掉，已经看到它在自动的做autovacuum。如果没有做或者没有来得及做autovacuum，使用下列语句，就能找出来哪个垃圾数据比较多。

digoal=#select relname,n_live_tup,n_dead_tup from pg_stat_all_tables_where n_dead_tup<>0 order by n_dead_tup desc;

比如系统表有十条垃圾数据，总共有37条数据。其实总共它有47条可能超过，因为方案datatup是可能被回收掉的。

也能够看出来要不要做web，把垃圾行记为可用空间。如果不标记为可用空间，会占用空间。并且后面做insert  plate 的时候，这十条空间不会被利用。

垃圾回收不会降低表的水位，除非垃圾回收的行是在表的末尾的页面里面。页面指的是数据块。每个数据块存储不固定，主要看行。比如数据块存100条数据。

总共有十个数据块，垃圾数据如果在中间，block ID是0到9，垃圾数据如果在第八个块、第七个块、第六块或者是第九个块里面，即不是所有的都是垃圾数据，垃圾回收，表还是占原来的十个数据块，不能够把区块降低。

可以来举个例子。创建表叫T表，往里面插入10万条记录。

可以去看ID和行号，它总共占用111个数据块，从零开始标记。其中第111个数据块，总共有十行，ID肯定是10万。

如果删除后面的数据块，比如delete from t where id>90000其实就删掉1万。此时去做vacuum t。

如果删除后面的数据块，比如delete from t where id

其实现在就变成100个数据块，原来是111个数据块，现在只有100个数据块，因为删的是末尾的数据。如果删除中间数据，后面都保留，是不会降低水位的。

比如改>为<，数据表只剩下9万一条记录。

此时去做vacuum，并且做analyze ，它还剩100个数据块，因为删的数据是末尾的数据块，所以简单的vacuum，不能把它的数据回收，除非使用vacuum full，它相当于重组表，再去查就剩下零个数据库。其实是一个数据块，因为它有一条记录。

rapid 统计有问题，其实是有一个数据块。

普通的垃圾回收，是可以把data tup它收成空闲空间。空闲空间不是从物理磁盘文件清掉，而只是把数据释放回free space map 的。free space指的是空闲空间，往里面插数据的时候，就可以用到这些空闲空间。

普通的垃圾回收，是可以把data tup它收成空闲空间。空闲空间不是从物理磁盘文件清掉，而只是把数据释放回free space map 的。free space指的是空闲空间，往里面插数据的时候，就可以用到这些空闲空间。

如果要做真正的垃圾回收，除非数据块是整个的数据块，末尾的数据块都已经空闲，里面没有live took 。那么水位可以降低，文件变小。如果不是末尾的数据块，只能把垃圾回收，变成空闲空间。但是不能从磁盘上把环节收掉。

计划性的vacant，它的data tup如果超过默认的20%加50，表可能要手工的vacant。因为auto vacuum可能来不及操作，特别是在表很大的情况下。

计划性的vacant，它的data tup如果超过默认的20%加50，表可能要手工的vacant。因为auto vacuum可能来不及

3.维护膨胀表

另外膨胀表的维护。膨胀表比如只有一条记录，但是它却占用100个数据块，因为它曾经可能做过大批量的数据update或者数据的删除，不是表的末尾数据块，相当于末尾的数据块还占有数据。这样就会出现表的膨胀。超过20%垃圾时候可能要去做vacuum full，相当于是要做整个表的排它锁，最高级别的锁，查询都不能做。这种情况下，可以使用另外的插件来做重组，可以减少排它锁的时间。

先来看如何判断表有没有膨胀。

输入#select oid::regciass,(pgstattuple(oid)).* from pg_class where relkind='r' order by free_space desc limit 1 offset o;

通过安装插件就可以来实现查询剩余空间。安装pgstattuple插件，然后去查询。比如查询pg_class，order by free _space 。

这里查出比如pg_rewrite表，有86%是被真正的物理行给占用掉。free space空间有12260字节，然后占12.47%。也就是剩余空间12.47%。如果free space很大，说明水位很高。比如99%空闲空间，但是普通的vacuum 又不能回收掉空闲的空间，还占用磁盘。此时要使用 vacuum full 或者是使用pg_reorg来重组表。

4.插件

介绍插件的使用。注意 acuum full 是会做完全的排查锁的。

这个工具很简单

http://pgfoundry.org/frs/download.php/3558/pg_reorg-1.1.9.tar.g网站里面下载，之后去安装。安装的时候注意需要配置的pgfoundry 路径，安装好之后，同样是动态编辑库，放到.postgres 的目录里面。

h

使用它很简单，pg_reorganize，指定表。比如加-o就示它做class，按照某键值来做物理上的paper，跟区域索引做物理上的排序。即物理表的存储顺序跟betray 的顺序一样。当然可以不使用-o，那只使用-T，重组表就可以。

使用

重组能够降低排它锁的时间，使用中间表，然后后面去改动。

这里有很详细的介绍。比如打开debug，在做重组表的时候实际上做哪些操作。比如reorganize  one  table，表名叫test。它会创建类型，然后再创建log 表，log 表用来放临时产生一些数据。然后有时还会创建触发器。

这里有很详细的介绍。比如

所有对表的操作insert  data ，delete 都会塞到临时log 表里。再有一步是把log 表合并到最后重组掉的表里面去。也只有在合并的时候才会加锁，不合并的时候不会加锁。

所有对表的操作insert  data ，delete 都会塞到临时log 表

重组表的时间长短，加锁时间的长短跟有关，跟表正在做的DML语句有关。如果DML很频繁，在重组的时候，第二阶段也会有比较长的时间的排它锁。因为要做数据的合并，此时也是要把表锁住。

重组表的时间长短，加锁时间的长短跟有关，跟表正在做的DML语句有关。如果DML很频繁，在重组的时候，第二阶段也会有比较长的时间的排它

这是postgresq的debug输出。在postgresq的日志里面也能够看到这部分信息。比如看到日志postgresq，重组的时候，它其实打开好几个事物。事物是read  committee 隔离级别短暂,给test加access exclusive mode，为了创建触发器。

这是postgresq的debug输出。在postgresq的日志里面也能够看到这部分信息。比如看到日志postgresq，重组的时候，它其实打开好几个事物。事物是read  committee 隔离级别短暂

然后再创建主键类型的中间表，创建中间日志表记录重组过程test 产生的DML。创建触发器负责将重组过程产生的BM2写入中间日志表。禁止中间表做自动的vacant。不允许做自动vacuum，因为要做重组，然后结束事务。完成之后，开启事务，注意事物是变成串行隔离级别的，因为要把test 的表的结果引入中间表。然后清除中间列表的截止当前已提交的ML的信息，然后马上把test 表写入到中间表。把test 表的数据导入中间表，注意不是中间的日志表，是中间表。

然后再创建

中间表会通过修改方案录的形式，会把它变成重组之后的原来的test表。索引定义，包括索引全部都会改掉,相当于是改final。最后一步是开启事物再次锁定，然后去做同步表，增量同步以及表的交换。

此时相当于要把它锁住，也是锁住access exclusive mode。然后做增量的中间表的数据同步。把中间日志表的数据同步到中间表里面去。最后再做一次交换，相当于是交换final 的。底层的文件去做交换。完成后结束。

它整个的锁，一次是做交换的时候，还有一次是创建触发器的时候。时间很短，使用pg_reorg可以减少vacuum full长时间持锁，持有excessive  lock 的冲突。否则，做vacuum时都不能查询使用。如果表膨胀很厉害，建议使用插件去降低水位，而不要使用vacuum full。使用vacuum full，除非表不会被查询或者是不会被DML操作的。建议使用插件可以减少锁的时间。

除表会膨胀，其实索引也会膨胀。索引膨胀之后，查询效率就会下降。查询索引输入#select oid::regciass,(pgstattuple(oid)).* from pg_class where ISindex='r' order by free_space desc limit 1 offset o;

去看它的free space percentage。如果大于20%，比如可能已经产生高水位，那么就可以去重新创建同类型的索引。那么创建索引的时候，加上congratulatory参数，不会去产生DML的堵塞，否则直接放进索引，是会跟DML堵塞的。表可以查询，但是不能做insert update，delete操作。所以建索引的时候，一定要加上并行参数，这样就不会DML堵塞。

去看它的free space percentage。如果大于20%，比如可能已经产生高水位，那么就可以去重新创建同类型的索引。那么创建索引的时候，加上congratulatory参数，不会去产生DML的堵塞，否则直接放进索引，是会跟DML堵塞的。表可以查询，但是不能做insert update，delete

来看前面提到的prevent xid wrapped。事务ID最大是32位的0XFFFF。所以它需要循环使用。为了不出现事物消失的情况，必须要做vacuum。

比如输入select cmin,cmax,xmin,xmax,* from t能够看到这条记录插入进去的事务号，xmax 、xmin 、cmin、cmax的事务号，更新就变成下一个事物。比如输入update t set id=id更新，发现它已经变成新的事物。在另外会话里面去查，查到的是一条老的数据。通过这种方式，包括事务提交日志去看的，比如这是老的记录，它的xmin还在，但是xmax只能变成事务号3262933848 。

在提交之后，看到那条新的记录，这条记录已经变成0.2。那原来那条记录是0.1，要不停的做XID变成freeze ，XID才可以去重复的利用它。当然这里看到xmin是int8的类型，实际上存储的是32位的类型。

可以看表的年龄。输入select age<relfrozenxid>，表的年龄通过relfrozenxid字段查看，字段存储的是表里面最老的数，年龄是十六，那当前为年龄是十六，因为当前的事务号跟它相减是16个数。比如减去xmax，其实是减的是上面那条数据。因为行还在，所以它的年龄应该是减前面的。如果把它freeze，年龄就会变成0。输入vacuum freeze t  ,再去查表的年龄，它就变成零，查记录会发现它的xmin变成2。2是frozen  transaction  ID，只要xmin是2，是就是frozen  transaction  ID。

可以看

如果现在又申请新的事物，比如使用函数会产生事物。再去查它的年龄，它的年龄会随着事物的使用也会增长。即使存储的是2，它也是随着年龄的速度进行增长，随着事物的请求发生增长。

如果现在又申请新的事物，比如使用函数会产生事物。再去查它的年龄，它的年龄会随着事物的使用也会增长。即使存储的

所以在被freeze 的表过一段时间之后，如果产生很多个事物，比如又产生20个事务，那么它的年龄也就变成20。如果产生1亿个数之后，它的年龄就会变成1亿。所以到一定时候，表又要去触发freeze 操作。

在postgres里面，随着数据库的事务的申请、事务的使用，时间的流逝，表不断的去做freeze 操作，如果不去做freeze ，年龄还是会增长。比如不断的申请事物、产生事物，表的年龄会增长。再去vacuum，表年龄又会降下来，变成零。

也就是要查到记录，年龄必须是要小于当前事物的。如果当前事物的年龄超越它，就会出现数据里面disappear的现象。这是要做那freeze 的缘由。

那么和freeze 动作相关的这几个参数，

autovacuum_freeze_max_ age 参数，如果数据库的auto vacuum 的参数没有打开，表就不会去自动的垃圾回收。数据库其实有强制做vacant的参数。当表的年龄大于所设置的值的，年龄看pg_class.relfrozenxid。年龄如果大于它，即使没有开启autovacuum，它也会自动强制对表执行vacuum freeze操作，从而来降低表的年龄。

autovacuum_freeze_max_ age 参数

XID是循环使用，所以必须要降低它的年龄才能够达到目的。这是应该来是数据库自动垃圾回收的最后防线。还有其它两个参数，是vacuum_freeze_min_age和vacuum_freeze_table_age。vacuum_freeze_min_age 意思是在做vacuum 或者是auto  vacuum 的时候，vacuum 会去扫描数据块。数据块里面它的记录允许保留的数XID的年龄。比如去做vacuum，表年龄是会自动的成freeze 的ID，变为零。如果它不变freeze ，其实还是保持原来的XID，相当于是扫描到的记录的年龄如果比它还要大，那么就会把它变成frozen  XID，相当于上面不记录事务ID的信息，直接就把它变成二。如果是比事物年龄小的记录，就不会把这条记录freeze 掉。

XID是循环使用，所以必须要降低它的年龄才能够达到目的。这是应该来是数据库自动

vacuum_freeze_table_age意思如果表的年龄大于它所设置的值，那么vacuum 操作将会扫描全表，可以降低表的年龄。整个的表都会扫描，所扫描到的行的年龄如果是大于它，就会把这条行的XID信息更改为frozen  XID，从而来降低表的年龄。因为只有扫描整个表，才能知道年龄能不能降低。如果只扫描一部分，另外一部分没有扫描。比如前面提到的VM文件，VN文件的VSPCTmap。在做普通扫描的时候，其实是对于没有data tup 的块扫描。所以普通扫描是不能降低表的年龄的，只有全面扫描才能降低年龄，或者是直接vacuum freeze。直接vacuum_freeze也是强制扫描整个数据表，后面会有试验来更加清晰的表明问题。

加深印象来做几个实验。

digoal # truncate tbl_freeze _ test ;

TRUNCATE TABLE

digoal # insert into tbl _ freeze _ test select generate _ series (1,100000);

INSERT 0 100000

digoal =# set vacuum _ freeze _ min _ age -10000000;

SET

直接使用，再往里面插入十万条记录。第一次vacuum，因为没有文件，它会做全面扫描。这里因为已经有文件，不会做全标识。

看到我扫描111个数据块，111个数据块里面没有可以移除的数据块，因为里面没有垃圾数据。此时会产生vm 文件，为减少vacuum进程的扫描开销的。然后再去扫描的时候，其实没有扫描任何数据块。先来看它的xmin和x max。

xmin是2，xmax是当前的事物号。同时现在有vacuum的VN文件，如果把freeze_mim_age把它改成0，其实不会发生任何事情。它的年龄也不会降成0，因为其实只要小于年龄，年龄比零大的，都会把它变成freeze XID。vacuum没有扫描任何的数据块，所以它的年龄还是原样。只有当把它改成零的时候，它才会进行下面操作。

如果改成0，vacuum_freeze_table_age 等于零，做vacuum 的时候，只要表的年龄大于它，就要做全表扫描。那可以忽略掉那个VN文件，现在它扫描111个数据块，那再去看它的xid，全部都降成二，因为前面设置min age等于0，全表扫描能找到任何行年龄大于零的，都会把它变成frozen xid。

这是前面讲的那几个参数的重要作用。

这是前面讲的那几个参数的重要作用。

5.空闲时段

接下来讲空闲时段，空闲的时段可以人为去干预freeze的操作。因为如果在自动触发做降低年龄的操作是需要扫描全表的。

当表很大，然后表年龄也很大，达到auto freeze  max  age，会自动触发它，去做全表的vacuum freeze。

为防止这种在繁忙时发生这种事情，要在表的年龄到达auto  vacuum要在表到达年龄之前，人为干预，把它的年龄降到数据之下，数据库就不会自动的在繁忙的时候触发，因为这种操作是会直接忽略VN文件，要扫描全表，表越大带来的危害性越大。

那么查找年龄较老的表，查找它的age relfrozenxid。当它的年龄小于前面提到的参数但是又接近参数的时候，就要在空闲的时候去vacuum freeze。最后一道防线是autovacuum freeze ,

这也是数据库自动的预防freeze 的一道防线。

当数据库确实非常繁忙，或者是由于某种原因，做这种vacuum freeze并没有把年龄降下来,它的年龄会持续的增长，继续增长，

增长到允许的最大的年龄减去1000万的时候,它就会发出告警，赶紧去做vacant freeze，当数据库的年龄只剩下100万个事务号，此时不允许做任何数据，数据库只能查询不能提交。

当发生这种事情的时候，就只能进到单用户里面去操作。使用命令postgres-single-E$dbname进到单用户，通过去查出来数据库里面到底哪个表的年龄最老，然后去做vacuum freeze，把整个数据库的年龄降下来，把整个数据库年龄降下来之后，离最大年龄超过100万个事务的时候，还能提交，但是必须要尽快的把年龄降到合理的范围。其实不是1000万，是900万。因为到100万的时候，不允许做任何事物，然后100万和1000万，在源代码里面是可以去改的。比如改成5000万和1000万。

到5000万的时候，会在日志文件里面打出报警。1000万的时候，它就不能提交任何事务，这样也可以起到保护作用。

这里日常的维护有脚本，可以把脚本可以放到系统里面。年龄大于某个值的时候，并且数据库的大小页面大于多少的时候，手工去做vacuum，然后时间输入1 12**1/home/ pestgres / seript / vacuumda.sh >/dev / null2>&1，放在对数据库比较空闲的时候。

XID是循环使用的。所以当出现某种情况的时候，数据可能就会消失。比如数据库没有auto vacuum 机制，人为也没有把表的年龄给降下来，可能会出现数据消失的情况。

6.模拟数据消失

这里来模拟数据是如何消失的。

数据消失，需要用到pg_resetxlog来模拟。

比如创建这样的表，输入create table resetxlog_test (id int)

往里面插入一些记录,输入 resetxlog_test values，这些记录分别以单个事物插进去的。每一条记录都是新的事物号。插入后，使用pg_resetxlog去更改它的事物号，来模拟数据消失的情况。

postgrezedb5-> pe_ctl stop

waiting for server to shut down..done

server stopped

postgresedb5-> pod.atroldata

Ps_control version nunber:903

Catalog version nunber201105231

Database systen identifier5652407581121182719

Database cluster stateshut down

pEcontrol last nodifiedFri 30 Sep 2011 04:25:26 PM CS

Latest checkpoint location2/40000020

Prior checkpoint location2/3C000020

Latest checkpoint's REDO location2/40000020

Latest checkpoint's TimeLineID

Latest checkpoint's NextxiD0/1952

Latest checkpoint's NextorD24723

把数据库停掉。看到事务号是0/326293874，在环境里面，它的next是1952

然后新插入的XID1948，1949，1950。比如把XID改成3262933867。输入指令：pg_resetxlog-x 3262933867，指定PGDATA，数据做起来。然后去查select*  from resetxlog_test，看到四条线。然后再过一会看到其它的记录，因为库里面有一些定制任务，它会消耗一些事物号。来它的x min是设置的67。

因为此时事务是显示已经提交。

比如现在要消费一些事物，输入select txid_current<>,变成68，再去查ID出来。因为只能看到已经提交事务,但是未来的事物不能够看到的，所以再消费能够看到新的一条产生。因为在postgres 里面，XID是循环使用的，它会去判断事物，是之前提交，还是之后提交。如果之后提交，看不到之后提交的数据。