了解Postgres 14新功能：性能和监控改进

即将发布的Postgres 14版本的第一个Beta版本已于昨天发布。在本文中，我们将首先了解Beta中的功能，重点是一项主要的性能改进以及三项引起我们注意的监视改进。
在开始之前，我想强调一下作为Postgres一个重要的独特方面一直令我印象深刻的地方：与大多数其他开源数据库系统相比，Postgres不是单个公司的项目，而是许多个人一起工作一年又一年的新版本。这包括尝试试用beta版本并将错误报告给Postgres项目的每个人。我们希望这篇文章能激励您进行自己的测试和基准测试。
现在，我个人对Postgres 14中更好的连接扩展感到非常兴奋。对于这篇文章，我们运行了一个详细的基准测试，将Postgres 13.3与14 beta1进行了比较（请注意，连接计数是对数刻度）。
 
Postgres 14中改进的主动和空闲连接扩展
对于需要大量数据库连接的我们来说，Postgres 14带来了重大改进。Postgres连接模型依赖于进程而不是线程。这具有一些重要的好处，但在连接数量较大时也有开销。通过此新版本，扩展活动和空闲连接的性能已显着提高，对于要求最苛刻的应用程序而言，这将是一项重大改进。
在我们的测试中，我们使用了两个96个vCore AWS实例（c5.24xlarge），一个实例运行Postgres 13.3，另一个实例运行Postgres 14 beta1。这两个都使用默认系统设置的Ubuntu 20.04，但是Postgres连接限制已增加到11,000个。
我们使用pgbench测试活动连接的连接比例。首先，我们使用pgbench比例因子200初始化数据库：

# Postgres 13.3
$ pgbench -i -s 200
...
done in 127.71 s (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
...
done in 77.33 s (drop tables 0.02 s, create tables 0.02 s, client-side generate 48.19 s, vacuum 2.70 s, primary keys 26.40 s).

我们已经在这里看到Postgres 14在初始数据加载方面做得更好。
现在，我们启动具有不同活动连接集的只读pgbench，以非常活跃的工作负载为例显示5,000个并发连接：

# Postgres 13.3
$ pgbench -S -c 5000 -j 96 -M prepared -T30
...
tps = 417847.658491 (excluding connections establishing)
# Postgres 14 beta1
$ pgbench -S -c 5000 -j 96 -M prepared -T30
...
tps = 495108.316805 (without initial connection time)

如您所见，具有5000个活动连接的Postgres 14的吞吐量大约高20％。在10,000个活动连接的情况下，与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 row)

现在，让我们进一步深入。当我们按内存使用量查询表中前5个条目时，您会注意到实际上有很多详细信息：

SELECT * FROM pg_backend_memory_contexts ORDER BY used_bytes DESC LIMIT 5;

SELECT * FROM pg_stat_wal;

-[ RECORD 1 ]----+------------------------------
wal_records      | 3334645
wal_fpi          | 8480
wal_bytes        | 282414530
wal_buffers_full | 799
wal_write        | 429769
wal_sync         | 428912
wal_write_time   | 0
wal_sync_time    | 0
stats_reset      | 2021-05-21 07:33:22.941452+00

通过这种新视图，我们可以获得摘要信息，例如向WAL中写入了多少张全页图像（FPI），从而可以洞悉Postgres何时由于检查点而生成大量WAL记录。其次，您可以使用新的wal_buffers_full计数器快速查看何时将该wal_buffers设置设置得太低，这可能会导致不必要的I / O，可以通过将wal_buffers增大为一个更高的值来防止此情况。
通过启用可选track_wal_io_timing设置，您还可以获取有关WAL写入的I / O影响的更多详细信息，该设置为您提供WAL写入的准确I / O时间，以及WAL文件同步到磁盘的时间。请注意，此设置可能会有明显的开销，因此，除非需要，否则最好将其关闭（默认设置）。
 
使用内置的Postgres query_id监视查询
在TimescaleDB于2021年3月和4月进行的一项最新调查中，pg_stat_statements扩展为被调查用户群与Postgres一起使用的三大扩展之一。pg_stat_statements与Postgres捆绑在一起，并且与Postgres 14捆绑在一起，扩展的重要功能之一已合并到核心Postgres中：
首先，我们必须启用新compute_query_id设置，然后重新启动Postgres：

ALTER SYSTEM SET compute_query_id = 'on';

SELECT query, query_id FROM pg_stat_activity WHERE backend_type = 'client backend' LIMIT 5;

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

从应用程序角度来看，所有这些查询都是相同的，但是它们的文本略有不同，因此很难在工作负载中找到模式。但是，使用查询ID，我们可以清楚地识别某些类型的查询的数量，并更轻松地评估性能问题。例如，我们可以按查询ID分组，以查看使数据库繁忙的原因：

SELECT COUNT(*), state, query_id FROM pg_stat_activity WHERE backend_type = 'client backend' GROUP BY 2, 3;

count | state  |       query_id       
-------+--------+----------------------
  40 | active |   885704527939071629
  9 | active |  7660508830961861980
  1 | active | -7810315603562552972
  1 | active | -3907106720789821134

在自己的系统上运行此命令时，您可能会发现query ID与此处显示的ID不同。这是由于query ID依赖于Postgres查询的内部表示，而Postgres查询可能依赖于体系结构，并且还考虑了表的内部ID而不是其名称。
还可以通过新的％Q选项从log_line_prefix获得query ID信息，从而更轻松地获得链接到查询的auto_explain输出：

2021-05-21 08:18:02.949 UTC [7176] [user=postgres,db=postgres,app=pgbench,query=885704527939071629] LOG:  duration: 59.827 ms  plan:
    Query Text: UPDATE pgbench_tellers SET tbalance = tbalance + -1902 WHERE tid = 6;
    Update on pgbench_tellers  (cost=4.14..8.16 rows=0 width=0) (actual time=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)
            Recheck Cond: (tid = 6)
            Heap Blocks: exact=1
            ->  Bitmap Index Scan on pgbench_tellers_pkey  (cost=0.00..4.14 rows=1 width=0) (actual time=0.003..0.004 rows=1 loops=1)
                  Index Cond: (tid = 6)
想链接auto_explain和pg_stat_statements，并且等不及Postgres 14？
我们建立了自己的开源query fingerprint mechanism，机制是根据其文本唯一地标识查询。这用于pganalyze中，以将EXPLAIN计划与查询进行匹配，并且您也可以在自己的脚本中使用此版本，与任何Postgres版本一起使用。
 
在Postgres 14版本中还有200多项其他改进！
这些只是新的Postgres版本中许多改进中的一部分。您可以在发行说明中找到更多有关新功能的信息，例如：

• 新的预定义角色pg_read_all_data/pg_write_all_data赋予全局读或写访问权限
• 如果客户端断开连接，则自动取消长期运行的查询
• 现在，当可移动索引条目的数量微不足道时，吸尘会跳过索引吸尘
• 现在，索引索引信息已包含在自动真空日志记录输出中
• 现在可以使用以下非阻塞方式分离分区： ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY