PostgreSQL VACUUM 之深入浅出 (一)[亲测有效]

PostgreSQL VACUUM 之深入浅出 (一)[亲测有效]前言 VACUUM 是 PostgreSQL MVCC (Multiversion concurrency control) 实现的核心机制之一,是 PostgreSQL 正常运行的重要保证。本文将通

PostgreSQL VACUUM 之深入浅出 (一)

前言

VACUUM 是 PostgreSQL MVCC (Multiversion concurrency control) 实现的核心机制之一,是 PostgreSQL 正常运行的重要保证。本文将通过实例演示 PostgreSQL 为什么需要做 VACUUM,以及一步一步精准触发 AUTOVACUUM, 到 VACUUM 优化实战,深入浅出,一看就懂。

测试环境准备

以下测试是在 PostgreSQL 11 中进行。

通过以下 SQL 创建:

测试用户: alvin,普通用户,非 superuser

测试数据库: alvindb,owner 是 alvin

测试 schema: alvin,owner 也是 alvin

这里采用的是 user 与 schema 同名,结合默认的 search_path(“$user”, public),这样操作对象(table, sequence, etc.)时就不需要加 schema 前缀了。

postgres=# CREATE USER alvin WITH PASSWORD "alvin";
CREATE ROLE
postgres=# CREATE DATABASE alvindb OWNER alvin;
CREATE DATABASE
postgres=# c alvindb
You are now connected to database "alvindb" as user "postgres".
alvindb=# CREATE SCHEMA alvin AUTHORIZATION alvin;
CREATE SCHEMA
alvindb=# c alvindb alvin
You are now connected to database "alvindb" as user "alvin".
alvindb=> SHOW search_path;
   search_path   
-----------------
 "$user", public
(1 row)

PostgreSQL 为什么需要做 VACUUM

这要从 PostgreSQL MVCC UPDATE/DELETE 实现讲起。

下面通过简单演示 PostgreSQL 中 UPDATE/DELETE 时底层数据变化,揭秘其 MVCC 设计的艺术。

为了方便看其底层数据,通过 superuser postgres 创建 extension pageinspect:

$ psql -d alvindb -U postgres
alvindb=# CREATE EXTENSION IF NOT EXISTS pageinspect;
CREATE EXTENSION
alvindb=# dx pageinspect
                              List of installed extensions
    Name     | Version | Schema |                      Description                      
-------------+---------+--------+-------------------------------------------------------
 pageinspect | 1.7     | public | inspect the contents of database pages at a low level
(1 row)

首先,创建测试表

$ psql -d alvindb -U alvin
alvindb=> 
CREATE TABLE tb_test_vacuum (
    test_id BIGSERIAL PRIMARY KEY,
    test_num BIGINT
);
CREATE TABLE

插入 3 条测试数据

alvindb=> INSERT INTO tb_test_vacuum(test_num) SELECT gid FROM generate_series(1,3,1) gid;
INSERT 0 3
alvindb=> SELECT * FROM tb_test_vacuum ORDER BY 1 DESC LIMIT 5;
 test_id | test_num 
---------+----------
       3 |        3
       2 |        2
       1 |        1
(3 rows)

查看其底层数据。

alvindb=> SELECT * FROM heap_page_items(get_raw_page("alvin.tb_test_vacuum", 0)) LIMIT 10;
ERROR:  must be superuser to use raw functions

可以看到底层数据只有 superuser 才可以查看,这里另打开一个窗口,用 superuser 用户 postgres 查看

psql -d alvindb -U postgres
alvindb=# SELECT * FROM heap_page_items(get_raw_page("alvin.tb_test_vacuum", 0)) LIMIT 10;

PostgreSQL VACUUM 之深入浅出 (一)[亲测有效]

这里 t_xmin 为其插入时 transaction id。

下面删除 2 条数据:

alvindb=> DELETE FROM tb_test_vacuum WHERE test_id = 2;
DELETE 1
alvindb=> DELETE FROM tb_test_vacuum WHERE test_id = 3;
DELETE 1
alvindb=> SELECT * FROM tb_test_vacuum ORDER BY 1 DESC LIMIT 5;
 test_id | test_num 
---------+----------
       1 |        1
(1 row)

此时在第二个窗口再看其底层数据

alvindb=> SELECT * FROM heap_page_items(get_raw_page("alvin.tb_test_vacuum", 0)) LIMIT 10;

PostgreSQL VACUUM 之深入浅出 (一)[亲测有效]

这时你会发现,实际数据并未被删除。只是修改了 t_xmaxt_infomask2t_infomaskt_xmax 为删除时的 transaction id,t_infomask2t_infomask 为各种标志位,这里显示的是其二进制转换后的十进制。

为什么不直接物理删除数据呢?

主要是出于以下考虑:

这些被删除的数据可能还在被其他事务访问,所以不能直接删除。这就是所谓的 MVCC 中的 multi version,即多版本,不同事务访问的可能是不同版本的数据。transaction id 可以理解为版本号。其他事务可能还在访问 t_xmax 为 15400741 或 15400742 的数据。

为什么有的其他数据库 MVCC 实现底层数据就不是这样呢?

Oracle 中将要删除数据转移到了 UNDO tablespace 中,供其他事务访问,以实现 MVCC。

PostgreSQL 为什么这么实现呢?

大家可以想一下,“转移数据” 与 “改标志位”,哪个 cost 高呢?当然是 “改标志位” 既简单又高效了!可见 PostgreSQL 设计之巧妙。

另外,PostgreSQL 这样做还有一个好处。

Oracle DBA 都非常熟悉 ORA-01555: snapshot too old,其原因是 UNDO tablespace 大小毕竟是有限的,存储的老版本数据也是有限的,Oracle 中解决 snapshot too old 一个办法就是增大 UNDO tablespace。PostgreSQL 中这样保留老版本数据,可以说磁盘有多大,“UNDO tablespace” 就有多大,就不会出现类似类似 snapshot too old 这样的问题。

但凡事都有两面性。

PostgreSQL 中这样保留老版本数据有什么弊端呢?

老版本的数据是可能有其他事务需要访问,但随着时间的推移,这些事务终将结束,对应老版本的数据终将不被需要,它们将不断占用甚至耗尽磁盘空间,使数据访问变得很慢,这就是 PostgreSQL 中的 Bloat ,即膨胀。

PostgreSQL 中的 bloat 问题如何解决呢?

就是 VACUUM。可以理解为“回收空间”。

现在对表 alvin.tb_test_vacuum 进行 VACUUM 操作。

alvindb=> VACUUM VERBOSE tb_test_vacuum;
INFO:  vacuuming "alvin.tb_test_vacuum"
INFO:  scanned index "tb_test_vacuum_pkey" to remove 2 row versions
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO:  "tb_test_vacuum": removed 2 row versions in 1 pages
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO:  index "tb_test_vacuum_pkey" now contains 1 row versions in 2 pages
DETAIL:  2 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  "tb_test_vacuum": found 2 removable, 1 nonremovable row versions in 1 out of 1 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 15400744
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM

可以看到,VACUUM 不仅针对表数据,还包括索引。即不仅表数据可造成 Bloat (膨胀),索引也会。

pageinspect extension 除了可以用 heap_page_items 看底层数据,也可以通过 bt_page_items 看其索引底层数据。在此不再查看索引底层数据,感兴趣可以执行如下 function 自行测试。

SELECT * FROM bt_page_items("index_name", 1);

在第二个窗口重新查看表底层数据:

psql -d alvindb -U postgres
alvindb=# SELECT * FROM heap_page_items(get_raw_page("alvin.tb_test_vacuum", 0)) LIMIT 10;

PostgreSQL VACUUM 之深入浅出 (一)[亲测有效]

可以看到,老版本数据已被清除。此时回收的空间新插入的数据使用,但并未返回给操作系统。

如何将回收的空间真正返回给操作系统呢?

就是 VACUUM FULL 操作:

alvindb=> VACUUM FULL VERBOSE tb_test_vacuum;
INFO:  vacuuming "alvin.tb_test_vacuum"
INFO:  "tb_test_vacuum": found 0 removable, 1 nonremovable row versions in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU: user: 0.01 s, system: 0.01 s, elapsed: 0.08 s.
VACUUM

在第二个窗口查看表底层数据:

psql -d alvindb -U postgres
alvindb=# SELECT * FROM heap_page_items(get_raw_page("alvin.tb_test_vacuum", 0)) LIMIT 10;

PostgreSQL VACUUM 之深入浅出 (一)[亲测有效]

可以看到,老版本数据已彻底回收了。

但要注意,生产环境需要谨慎使用 VACUUM FULL,因为它将在表上加 ACCESS EXCLUSIVE 锁,即连 SELECT 也不可以。除非应用端可以计划不访问该表。

上面通过 DELETE 演示了为什么需要做 VACUUM。

那么 UPDATE 在 PostgreSQL 中是如何实现的呢?它会不会产生 Bloat (膨胀) 呢?

执行 UPDATE 操作如下:

alvindb=> UPDATE tb_test_vacuum SET test_num = 1 WHERE test_id = 1;
UPDATE 1

在第二个窗口查看表底层数据:

psql -d alvindb -U postgres
alvindb=# SELECT * FROM heap_page_items(get_raw_page("alvin.tb_test_vacuum", 0)) LIMIT 10;

PostgreSQL VACUUM 之深入浅出 (一)[亲测有效]

可以看到,UPDATE 其实是 DELETE + INSERT。

为什么 PostgreSQL 如此实现 UPDATE 呢?

是因为 DELETE + INSERT 执行效率高?直接修改原数据不可以么?

因为老版本数据有可能还被其他事务需要!这是 MVCC 实现所需要的。

当然,相比 Oracle 中将老版本数据转移到 UNDO tablespace, DELETE + INSERT 中的 DELETE 减少了 I/O,因为其只修改了标志位而已。

那么只有 UPDATE 和 DELETE 会产生 Bloat (膨胀) 吗? INSERT 会吗?

INSERT 不是只插入数据吗?它怎么会产生 Bloat (膨胀) 呢?

接下来看下面的 case。

在事务中,ROLLBACK INSERT 的数据:

alvindb=> TRUNCATE tb_test_vacuum;
TRUNCATE TABLE
alvindb=> INSERT INTO tb_test_vacuum(test_num) SELECT gid FROM generate_series(1,1,1) gid;
INSERT 0 1
alvindb=> BEGIN;
BEGIN
alvindb=> INSERT INTO tb_test_vacuum(test_num) SELECT gid FROM generate_series(2,3,1) gid;
INSERT 0 2
alvindb=> ROLLBACK;
ROLLBACK
alvindb=> SELECT * FROM tb_test_vacuum ORDER BY 1 DESC LIMIT 5;
 test_id | test_num 
---------+----------
       8 |        1
(1 row)

在第二个窗口查看表底层数据:

psql -d alvindb -U postgres
alvindb=# SELECT * FROM heap_page_items(get_raw_page("alvin.tb_test_vacuum", 0)) LIMIT 10;

PostgreSQL VACUUM 之深入浅出 (一)[亲测有效]

可以看到,在事务中,PostgreSQL 中 ROLLBACK 时并未删除已 INSERT 的数据。

进一步测试 ROLLBACK UPDATE。

alvindb=> TRUNCATE tb_test_vacuum;
TRUNCATE TABLE
alvindb=> INSERT INTO tb_test_vacuum(test_num) SELECT gid FROM generate_series(1,1,1) gid;
INSERT 0 1
alvindb=> BEGIN;
BEGIN
alvindb=> SELECT * FROM tb_test_vacuum ORDER BY 1 DESC LIMIT 5;
 test_id | test_num 
---------+----------
      12 |        1
(1 row)
alvindb=> UPDATE tb_test_vacuum SET test_num = test_num + 1 WHERE test_id = 12; 
UPDATE 1
alvindb=> SELECT clock_timestamp();
        clock_timestamp        
-------------------------------
 2021-11-14 18:25:11.651518+08
(1 row)

此时在第二个窗口查看表底层数据:
PostgreSQL VACUUM 之深入浅出 (一)[亲测有效]

接下来在第一个窗口 ROLLBACK:

alvindb=> ROLLBACK;
ROLLBACK
alvindb=> SELECT clock_timestamp();
        clock_timestamp        
-------------------------------
 2021-11-14 18:25:35.948455+08
(1 row)
alvindb=> SELECT * FROM tb_test_vacuum ORDER BY 1 DESC LIMIT 5;
 test_id | test_num 
---------+----------
      12 |        1
(1 row)

再在第二个窗口查看表底层数据:
PostgreSQL VACUUM 之深入浅出 (一)[亲测有效]

如果反复测试会发现,如果 COMMIT,其会修改标志位;如果 ROLLBACK ,PostgreSQL 什么也不做,因为标志位未修改,其仍不可见,即使 t_xmax 为 0。

相比 Oracle 中的 UPDATE 先将老版本中数据转移到 UNDO,ROLLBACK 再利用 UNDO 中原数据恢复,PostgreSQL 中的 ROLLBACK 避免了两次不必要的 IO,既提高了性能,又节省了时间

根据上面实验,可以看到 UPDATE/DELETE/ROLLBACK 都有可能造成 Bloat (膨胀)。如果频繁更新的表长时间未做 VACUUM,VACUUM 完之后仍会占用很大空间,Bloat (膨胀) 仍然存在。生产又不能随便做 VACUUM FULL 回收空间 。

那么如何有效减少 Bloat (膨胀)?

在计划内大量更新数据等情况,可以根据需要手动 VACUUM,这样回收的空间可供下次大量更新数据使用,这样可以有效减少 Bloat (膨胀)。

VACUUM 除了回收空间,还有其他作用吗?

transaction id (事务 id) 是 32 位的,即最多有 2 的 32 次方,即 4294967296 个事务 id。中国人口按 14 亿算,一人也就能分配 3 个事务 id。所以 transaction id 范围是非常有限的,那么 PostgreSQL 是如何解决这个问题的呢?

从下图可以看出,PostgreSQL 是循环利用 transaction id 的,这样,transaction id 就无穷无尽的了。
PostgreSQL VACUUM 之深入浅出 (一)[亲测有效]

以当前 transaction id 是 100 为例,大于 100 的约 21 亿 个事务对事务 100 不可见,小于 100 的约 21 亿 个事务对事务 100 可见。如果 transaction id 一直没有回收,直至 transaction id 耗尽,就会产生 wraparound (回卷) 问题,原来可见的突然变得不可见了,数据就“凭空消失”了。

那么 VACUUM 是如何回收 transaction id 的?是通过 FREEZE 对所有事务可见的数据。由于篇幅有限,且实际工作中基本不需要对 FREEZE 相关参数进行优化,FREEZE 将通过另外一篇文章单独讲述,本文不对 FREEZE 展开。

应用程序一般会有频繁的更新,不断造成 Bloat (膨胀) 及消耗 transaction id,总不能都手动 VACUUM 吧?

有没有自动的方式呢?当然!

优质文章推荐

PostgreSQL VACUUM 之深入浅出

华山论剑之 PostgreSQL sequence

[PG Upgrade Series] Extract Epoch Trap

[PG Upgrade Series] Toast Dump Error

GitLab supports only PostgreSQL now

MySQL or PostgreSQL?

PostgreSQL hstore Insight

ReIndex 失败原因调查

PG 数据导入 Hive 乱码问题调查

原文地址:https://www.cnblogs.com/dbadaily/archive/2022/02/24/vacuum1.html

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
转载请注明出处: https://daima100.com/5489.html

(0)
上一篇 2023-05-07
下一篇 2023-05-07

相关推荐

  • Python 中 NoneType 的含义及使用

    Python 中 NoneType 的含义及使用在 Python 中,NoneType 是一个非常重要和常见的数据类型。它表示一个不确定的、空的或者缺失的值,通常用于表示一个没有被初始化的变量或者一个函数没有返回值的情况。在本文中,我们将从多个方面对 Python 中 NoneType 的含义及使用进行详细的阐述。

    2024-01-27
    111
  • Python Deque:高效的双向队列实现

    Python Deque:高效的双向队列实现在计算机科学中,队列是一种抽象数据类型,用于在数据结构中存储按顺序排列的元素。队列具有先进先出(FIFO)的特性,确保最先进入队列的元素也将最先被删除。而双向队列则是队列的一种变体,允许在队列的前端和后端添加和删除元素。

    2024-01-13
    108
  • 设置WSL-Ubuntu下MySQL的自启动[通俗易懂]

    设置WSL-Ubuntu下MySQL的自启动[通俗易懂]目前有多种方式可以设置MySQL在Ubuntu下的自启动,挑一种最传统的: 执行命令 update-rc.d mysql defaults # update-rc.d mysql defaults

    2023-02-03
    169
  • sql触发器的使用及语法csdn_三种触发器

    sql触发器的使用及语法csdn_三种触发器
    定义: 何为触发器?在SQL Server里面也就是对某一个表的一定的操作,触发某种条件,从而执行的一段程序。触发器是一个特殊的存储过程。常见的触发器有三种…

    2023-04-17
    145
  • 使用Python if语句多个条件判断

    使用Python if语句多个条件判断Python作为广泛应用的编程语言之一,其if语句是编程中非常重要的一部分,在判断特定条件下的程序流程上有着非常重要的作用。当需要在多个条件中进行判断时,Python if语句的多个条件判断就成为了解决问题的关键。

    2024-04-15
    82
  • Python实现电信行业词典,助力通信网络技术开发

    Python实现电信行业词典,助力通信网络技术开发随着通信网络技术的快速发展,快速准确的信息交互成为了人们生活中不可或缺的一部分。电信行业作为信息交互的重要基础,常常需要针对行业特定术语进行查阅。因此,我们有必要开发一款电信行业词典,以方便相关人员查阅术语含义,从而加快信息交互的速度。

    2024-02-12
    98
  • 大数据dds_应用用户数据怎么那么大

    大数据dds_应用用户数据怎么那么大我们前面采集的日志数据已经保存到 Kafka 中,作为日志数据的 ODS 层,从 Kafka 的ODS 层读取的日志数据分为 3 类, 页面日志、启动日志和曝光日志。这三类数据虽然都是用户行为数据,但

    2023-06-19
    150
  • 以Python配置文件为中心的管理

    以Python配置文件为中心的管理在软件开发的过程中,我们需要将程序中的各种配置单独提取出来进行管理,以便于在不同的环境中以及不同的部署情况下进行配置的修改和传递。Python作为一门广泛应用于Web开发、运维等领域的编程语言,它的配置文件管理也有诸多优化和方便的手段。本篇文章就是要探讨如何通过Python配置文件实现程序的管理与部署。

    2024-08-05
    31

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注