PostgreSQL 性能优化概述（译）

原文地址：https://www.prisma.io/dataguide/postgresql/reading-and-querying-data/optimizing-postgresql 概述

在数据库作为配景支持的应用程序中，管理数据库的性能是一项连续的任务。运行缓慢的查询可能导致超时、降低用户体验、使用更多资源，甚至可能影响您的预算，详细取决于您为数据库支付的费用。这些问题使得相识数据库的性能特征非常紧张，如许您就可以识别和修复有问题的查询。在本指南中，我们将讨论识别PostgreSQL数据库中性能较差的查询的不同方法。之后，我们将讨论不同的技术，可以用来修复缓慢的查询，以保持您的PostgreSQL性能。 Checking active queries and processes

在尝试跟踪低性能查询时，首先要检查的是当前活动Session和进程的列表。PostgreSQL通过pg_stat_activity视图提供这些数据。pg_stat_activity视图是PostgreSQL累积统计系统中可用的视图之一。每个服务器进程包含一行，这有助于您查看每个进程当前正在处理的内容。要显示视图中的全部信息，输入:

1. SELECT * FROM pg_stat_activity \gx

复制代码

注意:使用\gx行停止序列代替传统的分号(;)告诉PostgreSQL对当前查询使用扩展的输出模式。这将垂直而不是程度地显示每条记载的列和相关值，这在某些情况下可以提高可读性。
在寻找较慢的查询时，输出中有许多字段可以提供资助。一些最相关的包括:

• state:进程的当前状态。active表示当前正在执行。其他状态包括等待新客户端下令的进程处于空闲状态，在事务上下文中等待下令的进程处于事务空闲状态，在语句导致错误的事务中处于事务空闲状态(中止)。
  

• query:近来执行的查询。对于活动进程，这将是当前正在执行的查询。
  

• usename:与进程关联的用户名。
  

• application_name:连接到进程的应用程序的名称。
  

• datname:用户所连接数据库的名称。
  

• wait_event:进程正在等待的变乱的名称(如果有)。如果进程处于活动状态而且存在wait_event，则表示查询当前被系统的其他部分阻塞。
  

• wait_event_type:进程正在等待的变乱的类别。
  

• pid:进程的进程号。
  

• query_start:对于活动查询，当前查询开始的时间戳。
  

• xact_start:如果进程正在执行事务，则当前事务开始的时间戳。
  

我们可以根据与当前上下文相关的任何列来过滤查询。一个有用的模式是使用age()函数来计算查询运行了多长时间。例如:

1. SELECT
2.     age(clock_timestamp(), query_start),
3.     usename,
4.     datname,
5.     query
6. FROM pg_stat_activity
7. WHERE
8.     state != 'idle'
9. AND query NOT ILIKE '%pg_stat_activity%'
10. ORDER BY age desc;

复制代码

这将显示非空闲查询的执行时间、用户名、数据库和查询语句。我们将结果从运行时间最长的倒序排序，并从结果中排除此特定查询。
类似地，你可以看到全部不是空闲的进程，但有一个等待变乱:

1. ELECT
2.     usename,
3.     datname,
4.     query,
5.     wait_event_type,
6.     wait_event
7. FROM pg_stat_activity
8. WHERE
9.     state != 'idle'
10. AND query wait_event != ''

复制代码

Check other system statistics

虽然pg_stat_activity视图可能会提供识别较慢查询所需的大部分信息，但是查看其他系齐备计信息也可以资助识别其他优化目标。Viewing database statistics

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

1. SELECT * FROM pg_stat_database \gx
3. . . .
4. [ RECORD 2 ]------------+------------------------------
5. datid                    | 13921
6. datname                  | postgres
7. numbackends              | 1
8. xact_commit              | 266
9. xact_rollback            | 9
10. blks_read                | 229
11. blks_hit                 | 11263
12. tup_returned             | 118708
13. tup_fetched              | 3563
14. tup_inserted             | 0
15. tup_updated              | 0
16. tup_deleted              | 0
17. conflicts                | 0
18. temp_files               | 0
19. temp_bytes               | 0
20. deadlocks                | 0
21. checksum_failures        |
22. checksum_last_failure    |
23. blk_read_time            | 0
24. blk_write_time           | 0
25. session_time             | 5303626.534
26. active_time              | 200.906
27. idle_in_transaction_time | 0
28. sessions                 | 2
29. sessions_abandoned       | 0
30. sessions_fatal           | 0
31. sessions_killed          | 0
32. stats_reset              | 2022-11-06 20:20:18.279798+01
33. . . .

复制代码

对我们的目标来说，一些风趣的列:

• blks_read:在数据库中读取的磁盘块数量。
  

• blks_hit:在缓冲区缓存中找到磁盘块的次数(避免从磁盘读取缓慢)。
  

• xact_commit:提交的事务数。
  

• xact_rollback:回滚的事务数。
  

正如Data Egret团队在他们的博客中所示，你可以使用这些原始值来计算风趣的统计数据，好比你的缓存掷中率:

1. SELECT
2.     datname,
3.     100 * blks_hit / (blks_hit + blks_read) as cache_hit_ratio
4. FROM
5.     pg_stat_database
6. WHERE
7.     (blks_hit + blks_read) > 0;
10.   datname  | cache_hit_ratio
11. -----------+-----------------
12.            |              99
13. postgres  |              98
14. template1 |              99
15. (3 rows)

复制代码

这是很有代价的信息，可以资助您评估是否可以从向数据库集群添加内存中获益，从而有效地缓存最常见的查询。 Viewing table statistics

另一个有用的视图家属是pg_stat_all_tables、pg_stat_user_tables和pg_stat_sys_tables。pg_stat_all_tables视图显示全部数据库的访问统计信息，而其他两个视图根据它们是用户表还是系统表来做区分。

1. SELECT * FROM pg_stat_all_tables \gx
2. . . .
3. [ RECORD 104 ]-----+------------------------
4. relid               | 1262
5. schemaname          | pg_catalog
6. relname             | pg_database
7. seq_scan            | 5168
8. seq_tup_read        | 20655
9. idx_scan            | 20539
10. idx_tup_fetch       | 20539
11. n_tup_ins           | 0
12. n_tup_upd           | 0
13. n_tup_del           | 0
14. n_tup_hot_upd       | 0
15. n_live_tup          | 0
16. n_dead_tup          | 0
17. n_mod_since_analyze | 0
18. n_ins_since_vacuum  | 0
19. last_vacuum         |
20. last_autovacuum     |
21. last_analyze        |
22. last_autoanalyze    |
23. vacuum_count        | 0
24. autovacuum_count    | 0
25. analyze_count       | 0
26. autoanalyze_count   | 0

复制代码

这些视图中一些风趣的列:

• seq_scan:在表上运行的顺序扫描次数。
  

• seq_tup_read:顺序扫描返回的行数。
  

• idx_scan:对表运行索引扫描的次数。
  

• idx_tup_fetch:通过索引检索的行数。
  

这些列中的数字可以资助您评估索引的执行情况，以及正在运行的查询是否有效地使用了索引。如果您发现您的表有许多顺序扫描，那么对最常见查询的创建相应的索引可能会有所收益。 Viewing index hits

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

1. SELECT * FROM pg_stat_all_indexes \gx
2. . . .
3. [ RECORD 6 ]-+----------------------------------------------
4. relid         | 1249
5. indexrelid    | 2659
6. schemaname    | pg_catalog
7. relname       | pg_attribute
8. indexrelname  | pg_attribute_relid_attnum_index
9. idx_scan      | 822
10. idx_tup_read  | 1670
11. idx_tup_fetch | 1670
12. . . .

复制代码

它们为您提供有关每个索引的使用频率的信息。idx_scan列显示扫描索引的次数。idx_tup_read列显示扫描返回的条目数，而idx_tup_fetch显示索引扫描返回的总行数。此信息可以资助您相识查询中没有使用的索引。一旦确定了这些索引，就可以重写查询以利用索引，也可以删除未使用的索引以提高写性能。Viewing lock information

您网络的一些关于慢速查询的信息可能指向锁定问题。您可以通过查询pg_locks视图来查找当前持有的全部锁的更多信息:

1. SELECT * FROM pg_locks \gx
2. -[ RECORD 1 ]------+----------------
3. locktype           | relation
4. database           | 13921
5. relation           | 12290
6. page               |
7. tuple              |
8. virtualxid         |
9. transactionid      |
10. classid            |
11. objid              |
12. objsubid           |
13. virtualtransaction | 3/3920
14. pid                | 967262
15. mode               | AccessShareLock
16. granted            | t
17. fastpath           | t
18. waitstart          |
19. -[ RECORD 2 ]------+----------------
20. locktype           | virtualxid
21. database           |
22. relation           |
23. page               |
24. tuple              |
25. virtualxid         | 3/3920
26. transactionid      |
27. classid            |
28. objid              |
29. objsubid           |
30. virtualtransaction | 3/3920
31. pid                | 967262
32. mode               | ExclusiveLock
33. granted            | t
34. fastpath           | t
35. waitstart          |

复制代码

输出将提供有关PostgreSQL中全部锁的信息。这可以资助您诊断当不同的进程请求对相同对象的控制时可能发生的争用问题。
可以资助您调查有问题的锁的列包括:

• locktype:可锁定对象的范例
  

• database/relation/page/tuple:锁定项的对象ID。对于database和relations，它们可以在pg_database和pg_class中交叉引用（原文是 cross-referenced ，意思是不同的数据行可能会共享一些对象）。
  

• mode:执行或请求的锁模式。
  

• granted:一个布尔值，表示锁是否被授予。
  

Enable slow query logging

更容易找到长时间运行查询信息的一种方法是启用慢速查询日记记载。启用慢速查询日记允许PostgreSQL自动记载任何执行时间超过给定时间的查询。如许就可以在事后网络并分析慢查询信息。Check if PostgreSQL is already logging slow queries

您应该做的第一件事是验证慢速查询日记的当前状态。如果已经启用了慢速查询日记记载，则无需执行任何操作。您可以通过输入以下下令来检查是否启用了慢速查询日记记载:

1. SELECT * FROM pg_settings WHERE name = 'log_min_duration_statement'\gx
2. [ RECORD 1 ]---+---------------------------------------------------------------------------
3. name            | log_min_duration_statement
4. setting         | -1
5. unit            | ms
6. category        | Reporting and Logging / When to Log
7. short_desc      | Sets the minimum execution time above which all statements will be logged.
8. extra_desc      | Zero prints all queries. -1 turns this feature off.
9. context         | superuser
10. vartype         | integer
11. source          | default
12. min_val         | -1
13. max_val         | 2147483647
14. enumvals        |
15. boot_val        | -1
16. reset_val       | -1
17. sourcefile      |
18. sourceline      |
19. pending_restart | f

复制代码

如果您检查short_desc和extra_desc列的值，您将发现允许我们评估当前是否启用了慢查询日记记载的信息。我们可以看到，当前没有启用慢速查询日记记载，由于setting列当前被设置为-1。
既然知道了当前状态，就可以根据需要对其进行更改。Configure PostgreSQL to log slow queries

在继承讨论之前，必须注意到，虽然慢速查询日记非常有用，但它可能会对性能产生额外的影响。PostgreSQL必须执行额外的操作来计时每个查询，并将结果记载到日记中。这可能会影响性能并不测地填满硬盘空间。在任何时候都记载慢速查询可能不是一个好主意。相反，应该在积极调查问题时启用该功能，并在完成调查后禁用该功能。Logging slow queries globally

考虑到这一点，您可以通过修改PostgreSQL服务器的配置文件来全局配置慢速查询日记。您也可以交互式地修改这些值，但是在配置中设置良好的默认值将使以后的交互式调整更容易。打开PostgreSQL的配置文件。您可以通过输入以下下令找到当前配置文件的位置:

1. SHOW config_file;
2.                config_file
3. -----------------------------------------
4. /etc/postgresql/14/main/postgresql.conf
5. (1 row)

复制代码

在文件中，搜索log_min_duration_statement设置。如果上面的示例输出值是从配置文件中读取的，那么它将被设置为-1，表示当前禁用了该功能。另有一些其他相关的设置，你可以根据自己的需要进行调整:

1. . . .
2. # Query logging configuration
4. #log_min_duration_statement = -1 # -1 is disabled, 0 logs all statements
5.                                  # and their durations, > 0 logs only
6.                                  # statements running at least this number
7.                                  # of milliseconds
9. #log_min_duration_sample = -1    # -1 is disabled, 0 logs a sample of statements
10.                                  # and their durations, > 0 logs only a sample of
11.                                  # statements running at least this number
12.                                  # of milliseconds;
13.                                  # sample fraction is determined by log_statement_sample_rate
15. #log_statement_sample_rate = 1.0 # fraction of logged statements exceeding
16.                                  # log_min_duration_sample to be logged;
17.                                  # 1.0 logs all such statements, 0.0 never logs
19. #log_transaction_sample_rate = 0.0 # fraction of transactions whose statements
20.                                    # are logged regardless of their duration; 1.0 logs all
21.                                    # statements from all transactions, 0.0 never logs
22. . . .

复制代码

目前，log_min_duration_statement设置被注释掉，其当前值设置为-1以表示默认值。其他设置在文件中有很好的注释，允许您对超过最小值的语句进行采样，而不是记载全部语句。最后一个设置还允许对事务中出现的语句进行抽样。
您可以通过取消log_min_duration_statement的注释并将其设置为另一个值来打开长查询日记记载。
例如，我们可以将它设置为5秒来记载任何需要超过5秒才能完成的语句:log_min_duration_statement = 5s保存文件后，你可以在PostgreSQL中重新加载你的PostgreSQL服务器，输入:

1. SELECT pg_reload_conf()

复制代码

您可以通过再次检查当前值来验证服务器是否正在使用您的新设置:

1. SELECT * FROM pg_settings WHERE name = 'log_min_duration_statement'\gx
2. [ RECORD 1 ]---+---------------------------------------------------------------------------
3. name            | log_min_duration_statement
4. setting         | 5000
5. unit            | ms
6. category        | Reporting and Logging / When to Log
7. short_desc      | Sets the minimum execution time above which all statements will be logged.
8. extra_desc      | Zero prints all queries. -1 turns this feature off.
9. context         | superuser
10. vartype         | integer
11. source          | configuration file
12. min_val         | -1
13. max_val         | 2147483647
14. enumvals        |
15. boot_val        | -1
16. reset_val       | 5000
17. sourcefile      | /etc/postgresql/14/main/postgresql.conf
18. sourceline      | 506
19. pending_restart | f

复制代码

如今，设置字段被设置为5000，单位字段被设置为ms，这表明我们设置的5秒已被转换为5000毫秒并应用。sourcefile行还确认正在从我们修改过的配置文件中读取该值
Logging slow queries per database

当尝试检测慢速查询时，另一个选项是将慢速查询日记记载限制在特定数据库中。虽然log_min_duration_statement可以全局设置(如上一节所示)，但也可以在数据库级别配置它。要打开单个数据库的慢速查询日记记载，使用ALTER database下令:

1. ALTER DATABASE helloprisma SET 'log_min_duration_statement' = 2000;
2. ALTER DATABASE

复制代码

注意，与全局设置不同，在使用ALTER DATABASE下令时，该值必须是一个以毫秒为单位表示时间的无单位整数。我们可以通过查询每个数据库的角色设置来验证设置是否已经应用:

1. \drds
2.                   List of settings
3. Role |  Database   |           Settings
4. -----+-------------+-------------------------------
5.       | helloprisma | log_min_duration_statement=2000
6. (1 row)

复制代码

我们可以验证这并没有干扰我们之前设置为5秒阈值的全局设置:

1. SELECT * FROM pg_settings WHERE name = 'log_min_duration_statement'\gx
2. [ RECORD 1 ]---+---------------------------------------------------------------------------
3. name            | log_min_duration_statement
4. setting         | 5000
5. unit            | ms
6. category        | Reporting and Logging / When to Log
7. short_desc      | Sets the minimum execution time above which all statements will be logged.
8. extra_desc      | Zero prints all queries. -1 turns this feature off.
9. context         | superuser
10. vartype         | integer
11. source          | configuration file
12. min_val         | -1
13. max_val         | 2147483647
14. enumvals        |
15. boot_val        | -1
16. reset_val       | 5000
17. sourcefile      | /etc/postgresql/14/main/postgresql.conf
18. sourceline      | 506
19. pending_restart | f

复制代码

Testing slow query logging

通过发出超过最小日记记载连续时间的语句来测试设置:

1. SELECT pg_sleep(10);
2. pg_sleep
3. ---------
4. (1 row)

复制代码

检查日记，您应该会发现指示长时间运行查询发生的语句:

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

复制代码

由于我们对全局限制和特定表有不同的阈值，因此可以通过使用触发其中一个而不触发另一个的查询时间来测试是否正确应用了这两个阈值。
例如，我们可以连接到具有较低阈值的数据库并休眠4秒，这应该会触发日记行:

1. \c helloprisma
2. SELECT pg_sleep(4);

复制代码

我们的日记显示：

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

复制代码

如今，我们可以切换到只受全局设置影响的另一个数据库。同样的sleep语句不应该触发日记行:

1. \c postgres
2. SELECT pg_sleep(4);

复制代码

没有新的日记会被记载下来

总结

在本文中，我们先容了如何查看和理解PostgreSQL提供的一些性能信息。查看这些信息可以让您深入相识系统资源、查询模式和配置设置中的不同瓶颈。当您遇到性能变慢时，您可以检查PostgreSQL提供的信息来开始调查问题的所在。
我们还讨论了如何使用慢速查询日记来精确查明哪些查询占用了系统资源，而且执行时间比预期长。记载这些数据并评估结果日记可以资助您确定哪些地方可能需要额外的索引、不同的查询结构或更有效的查询设计。
相识如何识别这些昂贵的操作是运行功能更强大的数据库支持的应用程序的第一步。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。