Postgres 14的早期展望：性能和监控的改进
By Lukas Fittl, May 21, 2021
即将发布的Postgres 14版本的第一个测试版已于昨天发布。在这篇文章中，我们将首先看一下测试版中的内容，重点是一个主要的性能改进，以及三个引起我们注意的监控改进。
在我们开始之前，我想强调的是，我一直认为Postgres的一个重要的独特方面。与其他大多数开源数据库系统相比，Postgres并不是一家公司的项目，而是许多人共同致力于新版本的开发，年复一年。这包括每个试用测试版的人，以及向Postgres项目报告错误。我们希望这篇文章能激励你做你自己的测试和基准测试。
现在，我个人对Postgres 14中**更好的连接扩展感到非常兴奋。在这篇文章中，我们对Postgres 13.3和14 beta1进行了详细的基准比较（注意，连接数是对数比例）。
- Postgres 14中改进的活动和空闲连接扩展
- 使用pg_backend_memory_contexts深入了解内存使用情况
- 用pg_stat_wal跟踪WAL活动
- 用内置的Postgres query_id监控查询
- 以及Postgres 14版本中的其他200多项改进！
- 结论
在Postgres 14中改进了活动和闲置连接的缩放功能
Postgres 14为我们这些需要大量数据库连接的人带来了重大改进。Postgres的连接模型依赖于进程而不是线程。这有一些重要的好处，但在大的连接数上也有开销。在这个新的版本中，活动和空闲连接的扩展已经得到了明显的改善，对于要求最严格的应用来说，这将是一个重大的改进。
在我们的测试中，我们使用了两个96 vCore AWS实例（c5.24xlarge），一个运行Postgres 13.3，一个运行Postgres 14 beta1。这两个都使用Ubuntu 20.04，使用默认的系统设置，但Postgres的连接限制已经增加到11000个连接。
我们使用pgbench来测试活动连接的连接扩展。开始时，我们用pgbench的规模因子200来初始化数据库。
# Postgres 13.3
$ pgbench -i -s 200
...
在127.71秒内完成（drop tables 0.02 s, create tables 0.02 s, client-side generate 81.74 s, vacuum 2.63 s, primary keys 43.30 s）。
# Postgres 14 beta1
$ pgbench -i -s 200
...
在77.33秒内完成（删除表0.02秒，创建表0.02秒，客户端生成48.19秒，真空2.70秒，主键26.40秒）。
从这里我们已经可以看出，Postgres 14在初始数据加载方面做得更好。
我们现在用一组不同的活动连接启动只读的pgbench，显示5000个并发连接作为一个非常活跃的工作负载的例子。
# Postgres 13.3
$ pgbench -S -c 5000 -j 96 -M prepared -T30
...
tps = 417847.658491 (不包括建立连接)
# Postgres 14 beta1
$ pgbench -S -c 5000 -j 96 -M prepared -T30
...
tps = 495108.316805 (不包括初始连接时间)
正如你所看到的，Postgres 14在5000个活动连接时的吞吐量高出约20%。在10000个活动连接时，比Postgres 13提高了50%，在更低的连接数下，你也可以看到一致的改进。
请注意，当连接数超过CPU的数量时，你通常会看到明显的TPS下降，这很可能是由于CPU调度的开销，而不是Postgres本身的限制。现在，大多数工作负载实际上并没有这么多的活动连接，而是有大量的闲置连接。
这项工作的原作者Andres Freund对单个活动查询的吞吐量进行了基准测试，同时还运行了10,000个空闲连接。该查询从15,000 TPS上升到近35,000 TPS--这比Postgres 13好了2倍多。你可以在**Andres Freund介绍这些改进的原始帖子**中找到所有细节。
使用pg_backend_memory_contexts深入了解内存使用情况
你是否曾好奇为什么某个Postgres连接占用了较多的内存？通过新的pg_backend_memory_contexts视图，你可以仔细查看为某个Postgres进程分配的具体内容。
首先，我们可以计算出当前连接总共使用了多少内存。
SELECT pg_size_pretty(SUM(used_bytes)) FROM pg_backend_memory_contexts;
pg_size_pretty
----------------
939 kB
(1行)
现在，让我们更深入地了解一下。当我们按内存使用量查询该表的前5条时，你会发现其实有很多详细信息。
SELECT * FROM pg_backend_memory_contexts ORDER BY used_bytes DESC LIMIT 5;
name | ident | parent | level | total_bytes | total_nblocks | free_bytes | free_chunks | used_bytes
-------------------------+-------+------------------+-------+-------------+---------------+------------+-------------+------------
CacheMemoryContext | | TopMemoryContext | 1 | 524288 | 7 | 64176 | 0 | 460112
时区 | | TOPMemoryContext | 1 | 104120 | 2 | 2616 | 0 | 101504
| | | 0 | 68704 | 5 | 13952 | 12 | 54752
WAL记录的构建 | | TopMemoryContext | 1 | 49768 | 2 | 6360 | 0 | 43408
MessageContext | | | TopMemoryContext | 1 | 65536 | 4 | 22824 | 0 | 42712
(5行)
Postgres的内存上下文是一个内存区域，用于分配支持查询计划或查询执行等活动。一旦Postgres完成了上下文中的工作，整个上下文就可以被释放，从而简化了内存处理。通过使用内存上下文，Postgres的源代码实际上在大部分情况下避免了手动的 "free "调用（尽管它是用C语言编写的），而是依靠内存上下文来分组清理内存。这里的顶级内存上下文，CacheMemoryContext是用于Postgres中许多长期缓存的。
我们可以通过在一个新的表上运行查询，然后再次查询视图来说明将额外的表加载到一个连接中的影响。
SELECT * FROM test3;
SELECT * FROM pg_backend_memory_contexts ORDER BY used_bytes DESC LIMIT 5;
name | ident | parent | level | total_bytes | total_nblocks | free_bytes | free_chunks | used_bytes
-------------------------+-------+------------------+-------+-------------+---------------+------------+-------------+------------
CacheMemoryContext | | TopMemoryContext | 1 | 524288 | 7 | 61680 | 1 | 462608
...
正如你所看到的，新的视图说明了在这个连接上简单地查询了一个表，即使在查询结束后也会保留大约2kb的内存。这种对表信息的缓存是为了加快未来的查询速度，但对于有许多不同模式的多租户数据库来说，有时会造成惊人的内存占用。现在你可以通过这个新的监控视图轻松地说明此类问题。
如果你想访问除当前进程以外的其他进程的信息，你可以使用新的pg_log_backend_memory_contexts函数，这将导致指定的进程向Postgres日志输出自己的内存消耗。
SELECT pg_log_backend_memory_contexts(10377);
LOG：记录PID 10377的内存上下文
陈述。 SELECT pg_log_backend_memory_contexts(pg_backend_pid())。
LOG: level: 0; TopMemoryContext: 6个块中共有80800个；14432个空闲（5个块）；66368个已使用
LOG: level: 1; pgstat TabStatusArray lookup hash table: 8192 总数在 1 个块中；1408 空闲（0 个块）；6784 已用
LOG: level: 1; TopTransactionContext: 在1个区块中共有8192个；7720个空闲（1个块）；472个已使用
LOG: level: 1; RowDescriptionContext: 8192 总数在 1 个块中；6880 空闲（0 个块）；1312 已用
LOG: level: 1; MessageContext: 16384 总数在 2 个块中；5152 空闲（0 个块）；11232 已用
LOG: level: 1; Operator class cache: 总共8192个，在1个块中；512个空闲（0个块）；7680个已用
LOG: level: 1; smgr关系表。在2个块中共有16384个；4544个空闲（3个块）；11840个已用
LOG: level: 1; TransactionAbortContext: 32768块中的总量；32504空闲（0块）；264已用
...
LOG: level: 1; ErrorContext: 8192 总数在 1 个块中；7928 空闲（3 个块）；264 已使用
LOG: 总计：1651920字节在201个块中；622360空闲（88个块）；1029560已用
使用pg_stat_wal跟踪WAL活动
在Postgres 13的WAL监控功能的基础上，新版本带来了一个新的服务器范围内的WAL信息汇总视图，称为 "pg_stat_wal"。
你可以用它来更容易地监测WAL的写入情况。
SELECT * FROM pg_stat_wal;
-[ RECORD 1 ]----+------------------------------
Wal_records | 3334645
Wal_fpi | 8480
读取的数据量是多少？
缓存满了 | 799
读取和写入 | 429769
读取时间 | 428912
写入时间 | 0
读取时间 | 0
2021-05-21 07:33:22.941452+00
通过这个新的视图，我们可以得到摘要信息，比如有多少全页图像（FPI）被写入WAL，这可以让你了解Postgres何时由于检查点产生了大量的WAL记录。其次，你可以使用新的wal_buffers_full计数器来快速查看wal_buffers设置是否过低，这可能导致不必要的I/O，可以通过提高wal_buffers的值来防止。
你也可以通过启用可选的track_wal_io_timing设置来获得更多关于WAL写入的I/O影响的细节，然后给你提供WAL写入和WAL文件同步到磁盘的确切I/O时间。注意这个设置会有明显的开销，所以除非需要，最好关闭（默认）。
[！下载免费电子书：如何获得3倍的Postgres速度](pganalyze.com/ebooks/opti…)
用内置的Postgres query_id来监控查询
在最近TimescaleDB在2021年3月和4月所做的调查中，pg_stat_statements扩展被评为被调查用户群使用的Postgres的前三个扩展之一。pg_stat_statements与Postgres捆绑在一起，在Postgres 14中，扩展的一个重要功能被并入Postgres核心。
计算 "query_id"，它是一个查询的唯一标识，同时忽略了常量值。因此，如果你再次运行相同的查询，它将有相同的query_id'，使你能够识别数据库的工作负载模式。以前，这些信息只能通过pg_stat_statements获得，它显示了关于已经执行完毕的查询的汇总统计数据，但现在这些信息可以通过pg_stat_activity`以及日志文件获得。
首先，我们必须启用新的compute_query_id设置，并在之后重启Postgres。
ALTER SYSTEM SET compute_query_id = 'on';
如果你使用pg_stat_statements，查询ID将被自动计算，通过默认的compute_query_id设置为auto。
启用查询ID后，我们可以在pgbench运行期间查看pg_stat_activity，看看为什么这比只看查询文本有帮助。
SELECT query, query_id FROM pg_stat_activity WHERE backend_type = 'client backend' LIMIT 5;
query | query_id
------------------------------------------------------------------------+--------------------
UPDATE pgbench_tellers SET tbalance = tbalance + -4416 WHERE tid = 3; | 885704527939071629
UPDATE pgbench_tellers SET tbalance = tbalance + -2979 WHERE tid = 10; | 885704527939071629
UPDATE pgbench_tellers SET tbalance = tbalance + 2560 WHERE tid = 6; | 885704527939071629
UPDATE pgbench_tellers SET tbalance = tbalance + -65 WHERE tid = 7; | 885704527939071629
UPDATE pgbench_tellers SET tbalance = tbalance + -136 WHERE tid = 9; | 885704527939071629
(5行)
从应用的角度来看，所有这些查询都是一样的，但是它们的文本略有不同，这使得我们很难在工作负载中找到模式。然而，通过查询ID，我们可以清楚地识别某些类型的查询的数量，并更容易评估性能问题。例如，我们可以通过查询ID进行分组，看看是什么在让数据库忙碌。
``sql
SELECT COUNT(*), state, query_id FROM pg_stat_activity WHERE backend_type = 'client backend' GROUP BY 2, 3;
count | state | query_id
-------+--------+----------------------
40 | 活动 | 885704527939071629
9 | 活跃 | 7660508830961861980
1 | 活跃 | -7810315603562552972
1 | 活跃 | -3907106720789821134
(4行)
当你在自己的系统上运行时，你可能会发现查询的ID与这里显示的不同。这是由于查询ID依赖于Postgres查询的内部表示，这可能与架构有关，同时也考虑了表的内部ID而不是表的名称。
查询ID信息也可以通过新的%Q选项在`log_line_prefix`中获得，这使得获得与查询相关的自动解释输出更加容易。
```text
2021-05-21 08:18:02.949 UTC [7176] [user=postgres,db=postgres,app=pgbench,query=885704527939071629] LOG: duration: 59.827 ms 计划。
查询文本。UPDATE pgbench_tellers SET tbalance = tbalance + -1902 WHERE tid = 6;
更新 pgbench_tellers (cost=4.14...8.16 rows=0 width=0) (实际时间=59.825...59.826 rows=0 loops=1)
-> Bitmap Heap Scan on pgbench_tellers (cost=4.14. 8.16 rows=1 width=10) (actual time=0.009. 0.011 rows=1 loops=1)
重新检查条件。(tid = 6)
堆块: exact=1
-> 对pgbench_tellers_pkey进行位图索引扫描(cost=0.00...4.14 rows=1 width=0) (实际时间=0.003...0.004 rows=1 loops=1)
索引条件。(tid = 6)
**想连接auto_explain和pg_stat_statements，又等不及Postgres 14？
我们建立了自己的开源查询指纹机制，根据查询的文本进行唯一的识别。这在pganalyze中用于将EXPLAIN计划与查询相匹配，你也可以在你自己的脚本中使用它，适用于任何Postgres版本。
在Postgres 14版本中还有其他200多项改进!
这些只是Postgres新版本中众多改进中的一部分。你可以在发行说明中找到更多的新内容，例如。
- 新的预定义角色
pg_read_all_data/pg_write_all_data给予全局的读或写权限 - 如果客户端断开连接，自动取消长期运行的查询
- 当可移除的索引条目数量不多时，Vacuum现在会跳过索引吸尘。
- 每个索引的信息现在包括在自动吸尘的日志输出中
- 现在可以通过 "ALTER TABLE ... "以非阻塞的方式分离分区。脱离分区... CONCURRENTLY
还有更多。现在是帮助测试的时候了！
从官方软件包库下载beta1，或者从源码构建它。我们都可以为使Postgres 14在几个月后成为一个稳定的版本作出贡献。
结语
在pganalyze，我们对Postgres 14感到很兴奋，希望这篇文章也能引起你的兴趣! Postgres再次展示了许多小的改进使它成为一个稳定的、值得信赖的数据库，它是由社区建立的，为社区服务的。
