说米网

每当PostgreSQL数据库中的表中的行被更新或删除时，死亡行会被遗留下来。VACUUM则会把它们除掉来使空间能被从头运用。假如一个表没有被清空，它会变得臃肿，糟蹋磁盘空间并且会降低顺序表扫描的速度，并且在较小范围内也会降低索引扫描的速度。

VACUUM指令只能够移除这些不再被需求的行版本（也被称为元组）。假如被删除业务的业务ID（存储在xmax系统列中）比仍然活泼在PostgreSQL数据库（或许同享表的整个集群）中最老的业务（xmin边界）更老，那么这个元组将不再被需求。

注意以下三种状况就能够按捺PostgreSQL集群中的xmin边界

1、 查找长期运行的业务

咱们能够查找长期运行的业务，然后运用pg_terminate_backend()函数去停止阻挠VACUUM指令的数据库会话。

2、 查找仿制槽

仿制槽是一种数据结构，它使PostgreSQL服务器免于丢掉备用服务器仍然需求的信息。假如仿制被推迟或许备用服务器被关闭，仿制槽就会阻挠VACUUM指令删除旧的行。

仿制槽提供了一种主动化的方法来确保免费ftp服务器不移除WAL块直到它们被一切的从服务器接纳。并且主服务器即使当从服务器断开衔接时也不移除或许导致康复冲突的行。

仿制槽只保留已知所需数量的WAL块而不是多于所需数量。

运用仿制槽能够避免这个问题：在从服务器未衔接的任意时刻段内不提供保护。

咱们能够运用pg_drop_replication_slot()函数去丢掉不需求的仿制槽。

这种状况只会发生在当hot_standby_feedback参数设置为on时的物理仿制中。假如是逻辑仿制，那么会有一个相似的危险，可是只有系统目录会被影响。

3、查找准备好的业务

二阶段提交协议是一种原子性确认协议。它是一种分布式算法，用来协调参加分布式原子业务的一切进程，确定是否提交或许停止（回滚）这个业务。

在二阶段提交过程中，一个分布式业务首先运用PREPARE TRANSACTION，为二阶段提交准备当时业务。假如由于任何原因PREPARE TRANSACTION 指令失利，会变成ROLLBACK，而当时业务则会被取消。

然后咱们运用COMMIT PREPARED，提交一个之前为两阶段提交准备的业务。

一旦一个业务被准备好，它会一向坚持一种“游荡”状况直到被提交或许间断。通常状况下，业务不会在准备状况中坚持很长期，但有时会出现错误所以业务有必要被办理员手动移除。

咱们也能够运用ROLLBACK PREPARED，取消一个之前为两阶段提交准备好的业务。

弥补：postgresql vacuum操作

PostgreSQL数据库办理工作中,定时vacuum是一个重要的工作.

vacuum的作用

1.1开释,再运用 更新/删除的行所占有的磁盘空间.

1.2更新POSTGRESQL查询方案中运用的统计数据

1.3避免因业务ID的重置而使非常老的数据丢失。

第一点的原因是PostgreSQL数据的插入,更新,删除操作并不是真正放到数据库空间.假如不定时开释空间的话,由于数据太多,查询速度会巨降.

第二点的原因是PostgreSQL在做查询处理的时候,为了是查询速度进步,会根据统计数据来确定履行方案.假如不及时更新的话,查询的作用或许不如预期.

第三点的原因是PostgreSQL中每一个业务都会发生一个业务ID,但这个数字是有上限的. 当业务ID达到最大值后,会从头从最小值开端循环.这样假如不及时把曾经的数据开释掉的话,原来的老数据会由于业务ID的丢失而丢失掉.

虽然在新版本的Postgresql中有主动的vacuum，可是假如是大批量的数据IO或许会导致主动履行很慢，需求合作手动履行以及自己的脚原本整理数据库。

1. vacuumdb 是 SQL 指令 VACUUM的封装

所以用vacuumdb和vacuum来整理数据库都能够，作用是相同的。

2.vacuumdb 中的几个重要参数

能够用vacuumdb –help查询。

-a/–all vacuum一切的数据库

-d dbname 只vacuum dbname这个数据库

-f/–full 履行full的vacuum

-t table 只vacuum table这个数据表

1-z/–analyze Calculate statistics for use by the optimizer

3. 切换到postgres用户下

vacuumdb -d yourdbname -f -z -v 来整理你的数据库。

或许加到conrtab中15 1 * * * postgres vacuumdb -d mydb -f -z -v >> /tmp/vacuumdb.log

每天的一点一刻开端进行整理。

4. 如何查询我的XID是否接近临界值的指令：

1select age(datfrozenxid) from pg_database;

或许：

1select max(age(datfrozenxid)) from pg_database;

5. 但是咱们关怀的是哪一个大的表组要真正的vacuum

1

2SELECT relname, age(relfrozenxid) as xid_age, pg_size_pretty(pg_table_size(oid)) as table_size FROM pg_class WHERE relkind = ‘r’ and pg_table_size(oid) > 1073741824ORDER BY age(relfrozenxid) DESC LIMIT 20;

这个指令是查询按照最老的XID排序，检查大于1G并且是排名前20的表。

下面是一个比如：

relname | xid_age | table_size
————————+———–+————
postgres_log | 199785216 | 12 GB
statements | 4551790 | 1271 MB
normal_statement_times | 31 | 12 GB

然后你能够独自每个表进行vacuum：

1vacuumdb –analyze –verbose –table ‘postgres_log’ mydb