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。对于数据库和关系，这些可以在 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);

不应记录新的日志行。