优化 PostgreSQL 性能简介

查看数据库统计信息

pg_stat_database 表包含有关每个数据库的统计信息

SELECT * FROM pg_stat_database \gx

. . .
-[ RECORD 2 ]------------+------------------------------
datid                    | 13921
datname                  | postgres
numbackends              | 1
xact_commit              | 266
xact_rollback            | 9
blks_read                | 229
blks_hit                 | 11263
tup_returned             | 118708
tup_fetched              | 3563
tup_inserted             | 0
tup_updated              | 0
tup_deleted              | 0
conflicts                | 0
temp_files               | 0
temp_bytes               | 0
deadlocks                | 0
checksum_failures        |
checksum_last_failure    |
blk_read_time            | 0
blk_write_time           | 0
session_time             | 5303626.534
active_time              | 200.906
idle_in_transaction_time | 0
sessions                 | 2
sessions_abandoned       | 0
sessions_fatal           | 0
sessions_killed          | 0
stats_reset              | 2022-11-06 20:20:18.279798+01
. . .

一些对我们目的而言有趣的列包括

• blks_read: 数据库中读取的磁盘块数。
• blks_hit: 磁盘块在缓冲区缓存中找到的次数（避免从磁盘进行缓慢读取）。
• xact_commit: 提交的事务数。
• xact_rollback: 回滚的事务数。

正如 Data Egret 团队在其博客中展示的那样，您可以使用这些原始值计算有趣的统计数据，例如缓存命中率

SELECT
    datname,
    100 * blks_hit / (blks_hit + blks_read) as cache_hit_ratio
FROM
    pg_stat_database
WHERE
    (blks_hit + blks_read) > 0;

  datname  | cache_hit_ratio
-----------+-----------------
           |              99
 postgres  |              98
 template1 |              99
(3 rows)

这可以提供宝贵的信息，帮助您评估将 RAM 添加到数据库集群中是否会有益，以便有效地缓存最常见的查询。

查看表统计信息

另一个有用的视图系列是 pg_stat_all_tables、pg_stat_user_tables 和 pg_stat_sys_tables。 pg_stat_all_tables 视图显示所有数据库的访问统计信息，而另外两个视图根据表是用户表还是系统表来筛选表。

SELECT * FROM pg_stat_all_tables \gx

. . .
-[ RECORD 104 ]-----+------------------------
relid               | 1262
schemaname          | pg_catalog
relname             | pg_database
seq_scan            | 5168
seq_tup_read        | 20655
idx_scan            | 20539
idx_tup_fetch       | 20539
n_tup_ins           | 0
n_tup_upd           | 0
n_tup_del           | 0
n_tup_hot_upd       | 0
n_live_tup          | 0
n_dead_tup          | 0
n_mod_since_analyze | 0
n_ins_since_vacuum  | 0
last_vacuum         |
last_autovacuum     |
last_analyze        |
last_autoanalyze    |
vacuum_count        | 0
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 0

这些视图中一些有趣的列包括

• seq_scan: 对表执行的顺序扫描次数。
• seq_tup_read: 从顺序扫描返回的行数。
• idx_scan: 对表执行的索引扫描次数。
• idx_tup_fetch: 通过索引检索的行数。

这些列中的数字可以帮助您评估索引的性能以及索引是否被您运行的查询有效使用。如果您发现表存在许多顺序扫描，您可能需要创建其他索引，以便被最常见的查询使用。

查看索引命中次数

如果您需要有关当前索引的更多信息，可以查看 pg_stat_all_indexes、pg_stat_user_indexes 和 pg_stat_sys_indexes 视图

SELECT * FROM pg_stat_all_indexes \gx

. . .
-[ RECORD 6 ]-+----------------------------------------------
relid         | 1249
indexrelid    | 2659
schemaname    | pg_catalog
relname       | pg_attribute
indexrelname  | pg_attribute_relid_attnum_index
idx_scan      | 822
idx_tup_read  | 1670
idx_tup_fetch | 1670
. . .

这些视图为您提供有关每个索引的使用频率的信息。 idx_scan 列显示索引扫描的次数。 idx_tup_read 列显示扫描返回的条目数，而 idx_tup_fetch 列显示索引扫描返回的总行数。

此信息有助于您了解何时存在未被查询使用的索引。确定这些索引后，您可以重写查询以利用索引，也可以删除未使用的索引以提高写入性能。

查看锁定信息

您收集的有关缓慢查询的一些信息可能指向锁定问题。您可以通过查询 pg_locks 视图来获取有关当前持有的所有锁的更多信息

SELECT * FROM pg_locks \gx

-[ RECORD 1 ]------+----------------
locktype           | relation
database           | 13921
relation           | 12290
page               |
tuple              |
virtualxid         |
transactionid      |
classid            |
objid              |
objsubid           |
virtualtransaction | 3/3920
pid                | 967262
mode               | AccessShareLock
granted            | t
fastpath           | t
waitstart          |
-[ RECORD 2 ]------+----------------
locktype           | virtualxid
database           |
relation           |
page               |
tuple              |
virtualxid         | 3/3920
transactionid      |
classid            |
objid              |
objsubid           |
virtualtransaction | 3/3920
pid                | 967262
mode               | ExclusiveLock
granted            | t
fastpath           | t
waitstart          |

输出将提供有关 PostgreSQL 中所有锁的信息。这可以帮助您诊断在不同进程请求对同一对象的控制时可能发生的争用问题。

一些可能有助于您调查有问题的锁的列包括

• locktype: 可锁定对象 的类型
• database/relation/page/tuple: 锁定项目的 ID。对于数据库和关系，这些 ID 可以与 pg_database 和 pg_class 进行交叉引用。
• mode: 实现或请求的 锁模式。
• granted: 表示是否授予锁的布尔值。

检查 PostgreSQL 是否已在记录慢查询

您应该做的第一件事是验证慢查询日志记录的当前状态。如果慢查询日志记录已启用，则无需执行任何操作。

您可以通过键入以下命令来检查是否启用了慢查询日志记录

SELECT * FROM pg_settings WHERE name = 'log_min_duration_statement'\gx

-[ RECORD 1 ]---+---------------------------------------------------------------------------
name            | log_min_duration_statement
setting         | -1
unit            | ms
category        | Reporting and Logging / When to Log
short_desc      | Sets the minimum execution time above which all statements will be logged.
extra_desc      | Zero prints all queries. -1 turns this feature off.
context         | superuser
vartype         | integer
source          | default
min_val         | -1
max_val         | 2147483647
enumvals        |
boot_val        | -1
reset_val       | -1
sourcefile      |
sourceline      |
pending_restart | f

如果您检查 short_desc 和 extra_desc 列的值，您将找到允许我们评估日志记录当前是否启用的信息。我们可以看到，慢查询日志记录当前未启用，因为 setting 列当前设置为 -1。

既然您了解当前状态，可以根据需要更改它。

配置 PostgreSQL 以记录慢查询

在继续之前，请注意，尽管慢查询日志记录非常有用，但它可能会对性能产生额外的影响。PostgreSQL 必须执行其他操作来计时每个查询并将结果记录到日志中。这可能会影响性能并意外地填满硬盘空间。

不建议始终记录慢查询。相反，在您积极调查问题时启用该功能，并在完成调查后禁用它。

SHOW config_file;

               config_file
-----------------------------------------
 /etc/postgresql/14/main/postgresql.conf
(1 row)

. . .
# Query logging configuration

#log_min_duration_statement = -1 # -1 is disabled, 0 logs all statements
                                 # and their durations, > 0 logs only
                                 # statements running at least this number
                                 # of milliseconds

#log_min_duration_sample = -1    # -1 is disabled, 0 logs a sample of statements
                                 # and their durations, > 0 logs only a sample of
                                 # statements running at least this number
                                 # of milliseconds;
                                 # sample fraction is determined by log_statement_sample_rate

#log_statement_sample_rate = 1.0 # fraction of logged statements exceeding
                                 # log_min_duration_sample to be logged;
                                 # 1.0 logs all such statements, 0.0 never logs

#log_transaction_sample_rate = 0.0 # fraction of transactions whose statements
                                   # are logged regardless of their duration; 1.0 logs all
                                   # statements from all transactions, 0.0 never logs
. . .

log_min_duration_statement = 5s

SELECT pg_reload_conf();

SELECT * FROM pg_settings WHERE name = 'log_min_duration_statement'\gx

-[ RECORD 1 ]---+---------------------------------------------------------------------------
name            | log_min_duration_statement
setting         | 5000
unit            | ms
category        | Reporting and Logging / When to Log
short_desc      | Sets the minimum execution time above which all statements will be logged.
extra_desc      | Zero prints all queries. -1 turns this feature off.
context         | superuser
vartype         | integer
source          | configuration file
min_val         | -1
max_val         | 2147483647
enumvals        |
boot_val        | -1
reset_val       | 5000
sourcefile      | /etc/postgresql/14/main/postgresql.conf
sourceline      | 506
pending_restart | f

ALTER DATABASE helloprisma SET 'log_min_duration_statement' = 2000;

ALTER DATABASE

\drds

                  List of settings
 Role |  Database   |           Settings
------+-------------+-------------------------------
      | helloprisma | log_min_duration_statement=2000
(1 row)

SELECT * FROM pg_settings WHERE name = 'log_min_duration_statement'\gx

-[ RECORD 1 ]---+---------------------------------------------------------------------------
name            | log_min_duration_statement
setting         | 5000
unit            | ms
category        | Reporting and Logging / When to Log
short_desc      | Sets the minimum execution time above which all statements will be logged.
extra_desc      | Zero prints all queries. -1 turns this feature off.
context         | superuser
vartype         | integer
source          | configuration file
min_val         | -1
max_val         | 2147483647
enumvals        |
boot_val        | -1
reset_val       | 5000
sourcefile      | /etc/postgresql/14/main/postgresql.conf
sourceline      | 506
pending_restart | f

测试慢查询日志记录

通过发出超过最小日志记录持续时间的语句来测试设置

SELECT pg_sleep(10);

 pg_sleep
----------

(1 row)

检查日志，你应该会找到表明长时间运行的查询发生的语句

2022-11-11 17:58:04.719 CET [1121088] postgres@postgres STATEMENT:  select sleep(10);
2022-11-11 17:58:42.635 CET [1121088] postgres@postgres LOG:  duration: 10017.171 ms  statement: select pg_sleep(10);

由于我们对全局限制和特定表有不同的阈值，我们可以使用应该触发一个阈值但不触发另一个阈值的查询时间来测试每个阈值是否正在正确应用。

例如，我们可以连接到具有较低阈值的数据库并在其中休眠 4 秒，这应该会触发一条日志行

\c helloprisma
SELECT pg_sleep(4);

我们的日志显示

2022-11-13 14:46:07.361 CET [1252789] postgres@helloprisma STATEMENT:  alter database helloprisma set log_min_duration_statement=2s;
2022-11-13 14:53:05.027 CET [1309069] postgres@helloprisma LOG:  duration: 4022.546 ms  statement: select pg_sleep(4);

现在，我们可以切换到一个应该只受全局设置影响的不同数据库。相同的休眠语句不应该触发日志行

\c postgres
SELECT pg_sleep(4);

不应记录新的日志行。