PostgreSQL 膨胀的说明
我已经与 Postgres 合作多年，最近在 Percona 担任顾问，为那些因从 Oracle 或其他一些遗留数据库引擎迁移而新实施 Postgres 的公司提供服务。在某些情况下，这些是财富 100 强公司，有许多人才为他们工作。但是，并非所有数据库的工作方式都相同，我在为客户审查 Postgres 环境时最常见的观察之一是表膨胀、索引膨胀的数量，以及缺乏对其对性能的影响以及如何解决它的理解.
几年前我写了一篇关于这个话题的博客，在它发表后从未考虑过。但是，由于大量公司出于显而易见的原因迁移到 Postgres，并且缺乏支持相当大的数据库所需的真正 Postgres 数据库管理技能，我想我会重写这篇博客，并以某种清晰的方式让它重新焕发生机，以帮助一个人了解膨胀及其发生的原因。
什么导致膨胀？
在 PostgreSQL 中，罪魁祸首是多版本并发控制，通常称为 MVCC。
MVCC 确保针对数据库的事务将仅在快照中返回已提交的数据，即使其他进程正在尝试修改该数据。
想象一个表中有数百万行的数据库。每当您更新或删除一行时，Postgres 必须根据事务 ID 跟踪该行。例如，您可能正在运行一个事务 ID 为 100 的长查询，而名为 John 的人刚刚使用事务 ID 101 更新了同一个表。此时，由于您仍在事务 100 中，就您的事务而言，它早于 101就查询而言，John 在事务 ID 101 中所做的更改与您的查询无关或不可见。在数据发生变化之前，您就处于自己的个人数据泡沫中。您或任何其他交易 ID 大于 101 的人的任何新查询都将看到 John 在交易 101 中所做的更改。毕竟，新交易 ID 大于 101，这意味着当前没有其他交易 ID 小于 101您在事务 ID 100 中看到的数据将不再被数据库需要，并且将被视为已死但并未消失。因此，膨胀！
在高层次上，清理用于释放表中的死行，以便它们可以重用。它还可以帮助您避免交易 ID 回绕。
让我们通过几个步骤来说明这一切是如何发生的
为了让 Postgres 知道哪些事务数据应该在查询的结果集中，快照会记录事务信息。
本质上，如果您的交易 ID 为 100，您将只能看到来自所有交易 ID 的数据，直到 100。如上所述，您将看不到来自交易 ID 101 或更大的数据。
设置示例
让我们首先为我们的示例创建一个简单的表，称为 percona：
percona=# CREATE TABLE percona ( col1 int );
CREATE TABLE
percona=# INSERT INTO percona values (1);
INSERT 0 1
percona=# INSERT INTO percona values (2);
INSERT 0 1
percona=# INSERT INTO percona values (3);
INSERT 0 1
percona=# INSERT INTO percona values (4);
INSERT 0 1
percona=# INSERT INTO percona values (5);
INSERT 0 1
您可以使用 BEGIN 和 COMMIT 将多个插入包装到单个事务中：
percona=# BEGIN;
BEGIN
percona=*# INSERT INTO percona SELECT generate_series(6,10);
INSERT 0 5
percona=*# COMMIT;
COMMIT
在这里，我们可以看到我们插入到表中的 10 行，以及一些隐藏的系统列：
percona=# SELECT xmin, xmax, * FROM percona;
xmin | xmax | col1
----------+------+------
69099597 | 0 | 1
69099609 | 0 | 2
69099627 | 0 | 3
69099655 | 0 | 4
69099662 | 0 | 5
69099778 | 0 | 6
69099778 | 0 | 7
69099778 | 0 | 8
69099778 | 0 | 9
69099778 | 0 | 10
(10 rows)
如您所见，值 1 到 5（在 col1 列中）具有唯一的事务 ID（在 xmin 列中表示）——它们是单个 INSERT 语句的结果，一个接一个。 值为 6 到 10 的行共享相同的事务 ID 6909978； 它们都是我们使用 BEGIN 和 COMMIT 语句创建的一个事务的一部分。
此时，您可能会问自己这与真空或自动真空有什么关系。 我们会到达那里。 首先，您需要了解事务 id 逻辑并直观地看到它，以便更好地理解，如上所示。
表格怎么膨胀？
在 Postgres 中，堆是一个包含可变大小记录列表的文件，没有特定的顺序，它指向页面中行的位置。 （Postgres 页面大小为 8k）。 指向该位置的指针称为 CTID。
要查看堆而不需要从文件中读取原始数据，我们需要在数据库中创建以下扩展：
CREATE extension pageinspect;
现在我们可以检查我们新创建的表和行的堆：
percona=# SELECT * FROM heap_page_items(get_raw_page('percona',0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+----------+--------+----------+--------+-------------+------------+--------+--------+-------+------------
1 | 8160 | 1 | 28 | 69099597 | 0 | 0 | (0,1) | 1 | 2304 | 24 | | | \x01000000
2 | 8128 | 1 | 28 | 69099609 | 0 | 0 | (0,2) | 1 | 2304 | 24 | | | \x02000000
3 | 8096 | 1 | 28 | 69099627 | 0 | 0 | (0,3) | 1 | 2304 | 24 | | | \x03000000
4 | 8064 | 1 | 28 | 69099655 | 0 | 0 | (0,4) | 1 | 2304 | 24 | | | \x04000000
5 | 8032 | 1 | 28 | 69099662 | 0 | 0 | (0,5) | 1 | 2304 | 24 | | | \x05000000
6 | 8000 | 1 | 28 | 69099778 | 0 | 0 | (0,6) | 1 | 2304 | 24 | | | \x06000000
7 | 7968 | 1 | 28 | 69099778 | 0 | 0 | (0,7) | 1 | 2304 | 24 | | | \x07000000
8 | 7936 | 1 | 28 | 69099778 | 0 | 0 | (0,8) | 1 | 2304 | 24 | | | \x08000000
9 | 7904 | 1 | 28 | 69099778 | 0 | 0 | (0,9) | 1 | 2304 | 24 | | | \x09000000
10 | 7872 | 1 | 28 | 69099778 | 0 | 0 | (0,10) | 1 | 2304 | 24 | | | \x0a000000
(10 rows)
上表显示了 10 个条目，其中包含几列：
- lp 是行/元组的 ID
- t_xmin 是交易ID
- t_ctid 是指针
- t_data 是实际数据
目前，每一行的指针都指向自身，由表单 (page,tupleid) 确定。 很简单。
现在，让我们对特定行执行一些更新。 让我们将 5 的值更改为 20，然后更改为 30，最后再更改为 5。
percona=# UPDATE percona SET col1 = 20 WHERE col1 = 5;
UPDATE 1
percona=# UPDATE percona SET col1 = 30 WHERE col1 = 20;
UPDATE 1
percona=# UPDATE percona SET col1 = 5 WHERE col1 = 30;
UPDATE 1
这三个变化发生在三个不同的交易中。
这是什么意思？ 我们将列的值更改了 3 次，但从未添加或删除任何行。 所以我们应该还有 10 行，对吧？
percona=# SELECT COUNT(*) FROM percona;
count
-------
10
(1 row)
看起来和预期的一样。 可是等等！ 现在让我们看看堆。 磁盘上的真实数据。
percona=# SELECT * FROM heap_page_items(get_raw_page('percona',0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+----------+----------+----------+--------+-------------+------------+--------+--------+-------+------------
1 | 8160 | 1 | 28 | 69099597 | 0 | 0 | (0,1) | 1 | 2304 | 24 | | | \x01000000
2 | 8128 | 1 | 28 | 69099609 | 0 | 0 | (0,2) | 1 | 2304 | 24 | | | \x02000000
3 | 8096 | 1 | 28 | 69099627 | 0 | 0 | (0,3) | 1 | 2304 | 24 | | | \x03000000
4 | 8064 | 1 | 28 | 69099655 | 0 | 0 | (0,4) | 1 | 2304 | 24 | | | \x04000000
5 | 8032 | 1 | 28 | 69099662 | 69103876 | 0 | (0,11) | 16385 | 1280 | 24 | | | \x05000000
6 | 8000 | 1 | 28 | 69099778 | 0 | 0 | (0,6) | 1 | 2304 | 24 | | | \x06000000
7 | 7968 | 1 | 28 | 69099778 | 0 | 0 | (0,7) | 1 | 2304 | 24 | | | \x07000000
8 | 7936 | 1 | 28 | 69099778 | 0 | 0 | (0,8) | 1 | 2304 | 24 | | | \x08000000
9 | 7904 | 1 | 28 | 69099778 | 0 | 0 | (0,9) | 1 | 2304 | 24 | | | \x09000000
10 | 7872 | 1 | 28 | 69099778 | 0 | 0 | (0,10) | 1 | 2304 | 24 | | | \x0a000000
11 | 7840 | 1 | 28 | 69103876 | 69103916 | 0 | (0,12) | 49153 | 9472 | 24 | | | \x14000000
12 | 7808 | 1 | 28 | 69103916 | 69103962 | 0 | (0,13) | 49153 | 9472 | 24 | | | \x1e000000
13 | 7776 | 1 | 28 | 69103962 | 0 | 0 | (0,13) | 32769 | 10496 | 24 | | | \x05000000
(13 rows)
我们有 13 行，而不是 10 行。到底发生了什么？
让我们检查一下我们的三个单独的更新事务（69103876、69103916、69103962），看看堆发生了什么：
t_xmin (691103876)
- 更新 percona SET col1 = 20 WHERE col1 = 5;
- 逻辑删除元组 ID 5
- 物理插入元组 ID 11
- 更新元组 ID 5 指针 (t_tcid) 以指向元组 ID 11
当元组 ID 5 的 t_xmax 设置为由事务 691103876 启动的新事务 ID 时，元组 ID 变为死排。
t_xmin (69103916)
- 更新 percona SET col1 = 30 WHERE col1 = 20;
- 逻辑删除元组 ID 11
- 物理插入元组 ID 12
- 更新元组 ID 11 指针 (t_tcid) 以指向元组 ID 12
再次，当元组 ID 11 的 t_xmax 设置为由事务 69103916 启动的新事务 ID 时，元组 ID 11 成为死行。
t_xmin (69103962)
- 更新 percona SET col1 = 5 WHERE col1 = 30;
- 逻辑删除元组 ID 12
- 物理插入元组 ID 13
- 更新元组 ID 12 指针 (t_tcid) 以指向元组 ID 13
元组 ID 13 是实时的并且对其他事务可见。它没有 t_xmax 并且 t_ctid (0,13) 指向它自己。
从中得出的关键结论是我们没有在表中添加或删除行。我们仍然在计数中看到 10，但我们的堆已通过另外三个正在执行的事务增加到 13。
在非常高的层次上，这就是 PostgreSQL 实现 MVCC 的方式以及为什么我们的堆中有表膨胀的原因。本质上，对数据的更改会产生一个反映数据最新状态的新行。为了提高效率，需要清理或重复使用旧行。
对表进行真空处理
处理表膨胀的方法是对表进行真空处理：
percona=# vacuum percona;
VACUUM
现在，让我们再次检查堆：
percona=# SELECT * FROM heap_page_items(get_raw_page('percona',0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+----------+--------+----------+--------+-------------+------------+--------+--------+-------+------------
1 | 8160 | 1 | 28 | 69099597 | 0 | 0 | (0,1) | 1 | 2304 | 24 | | | \x01000000
2 | 8128 | 1 | 28 | 69099609 | 0 | 0 | (0,2) | 1 | 2304 | 24 | | | \x02000000
3 | 8096 | 1 | 28 | 69099627 | 0 | 0 | (0,3) | 1 | 2304 | 24 | | | \x03000000
4 | 8064 | 1 | 28 | 69099655 | 0 | 0 | (0,4) | 1 | 2304 | 24 | | | \x04000000
5 | 13 | 2 | 0 | | | | | | | | | |
6 | 8032 | 1 | 28 | 69099778 | 0 | 0 | (0,6) | 1 | 2304 | 24 | | | \x06000000
7 | 8000 | 1 | 28 | 69099778 | 0 | 0 | (0,7) | 1 | 2304 | 24 | | | \x07000000
8 | 7968 | 1 | 28 | 69099778 | 0 | 0 | (0,8) | 1 | 2304 | 24 | | | \x08000000
9 | 7936 | 1 | 28 | 69099778 | 0 | 0 | (0,9) | 1 | 2304 | 24 | | | \x09000000
10 | 7904 | 1 | 28 | 69099778 | 0 | 0 | (0,10) | 1 | 2304 | 24 | | | \x0a000000
11 | 0 | 0 | 0 | | | | | | | | | |
12 | 0 | 0 | 0 | | | | | | | | | |
13 | 7872 | 1 | 28 | 69103962 | 0 | 0 | (0,13) | 32769 | 10496 | 24 | | | \x05000000
(13 rows)
清空表格后，第 5 行、第 11 行和第 12 行现在可以再次自由使用。
所以让我们插入另一行，值为 11，看看会发生什么：
percona=# INSERT INTO percona values (11);
INSERT 0 1
让我们再次检查堆：
percona=# SELECT * FROM heap_page_items(get_raw_page('percona',0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+----------+--------+----------+--------+-------------+------------+--------+--------+-------+------------
1 | 8160 | 1 | 28 | 69099597 | 0 | 0 | (0,1) | 1 | 2304 | 24 | | | \x01000000
2 | 8128 | 1 | 28 | 69099609 | 0 | 0 | (0,2) | 1 | 2304 | 24 | | | \x02000000
3 | 8096 | 1 | 28 | 69099627 | 0 | 0 | (0,3) | 1 | 2304 | 24 | | | \x03000000
4 | 8064 | 1 | 28 | 69099655 | 0 | 0 | (0,4) | 1 | 2304 | 24 | | | \x04000000
5 | 13 | 2 | 0 | | | | | | | | | |
6 | 8032 | 1 | 28 | 69099778 | 0 | 0 | (0,6) | 1 | 2304 | 24 | | | \x06000000
7 | 8000 | 1 | 28 | 69099778 | 0 | 0 | (0,7) | 1 | 2304 | 24 | | | \x07000000
8 | 7968 | 1 | 28 | 69099778 | 0 | 0 | (0,8) | 1 | 2304 | 24 | | | \x08000000
9 | 7936 | 1 | 28 | 69099778 | 0 | 0 | (0,9) | 1 | 2304 | 24 | | | \x09000000
10 | 7904 | 1 | 28 | 69099778 | 0 | 0 | (0,10) | 1 | 2304 | 24 | | | \x0a000000
11 | 7840 | 1 | 28 | 69750201 | 0 | 0 | (0,11) | 1 | 2048 | 24 | | | \x0b000000
12 | 0 | 0 | 0 | | | | | | | | | |
13 | 7872 | 1 | 28 | 69103962 | 0 | 0 | (0,13) | 32769 | 10496 | 24 | | | \x05000000
(13 rows)
我们的新元组（事务 ID 为 69750201）重用了元组 11，现在元组 11 的指针 (0,11) 指向自身。
如您所见，堆没有增长以容纳新行。 当我们清理表以释放死行（在事务中将不再可见的行）时，它为新行重用了一个开放块。
你有它。 分步说明 PostgreSQL 中的膨胀是如何发生的！
原文标题：An Illustration of PostgreSQL Bloat
原文作者：Jorge Torralba
原文地址：https://www.percona.com/blog/illustration-of-postgresql-bloat/
免责声明：
1、本站资源由自动抓取工具收集整理于网络。
2、本站不承担由于内容的合法性及真实性所引起的一切争议和法律责任。
3、电子书、小说等仅供网友预览使用，书籍版权归作者或出版社所有。
4、如作者、出版社认为资源涉及侵权，请联系本站，本站将在收到通知书后尽快删除您认为侵权的作品。
5、如果您喜欢本资源，请您支持作者，购买正版内容。
6、资源失效，请下方留言，欢迎分享资源链接
文章评论