PostgreSQL 性能优化概述（译）
概述
在数据库作为后台支撑的应用程序中，管理数据库的性能是一项持续的任务。运行缓慢的查询可能导致超时、降低用户体验、使用更多资源，甚至可能影响您的预算，具体取决于您为数据库支付的费用。这些问题使得了解数据库的性能特征非常重要，这样您就可以识别和修复有问题的查询。
在本指南中，我们将讨论识别PostgreSQL数据库中性能较差的查询的不同方法。之后，我们将讨论不同的技术，可以用来修复缓慢的查询，以保持您的PostgreSQL性能。
Checking active queries and processes
在尝试跟踪低性能查询时，首先要检查的是当前活动Session和进程的列表。PostgreSQL通过pg_stat_activity视图提供这些数据。
pg_stat_activity视图是PostgreSQL累积统计系统中可用的视图之一。每个服务器进程包含一行，这有助于您查看每个进程当前正在处理的内容。
要显示视图中的所有信息，输入:
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()函数来计算查询运行了多长时间。例如:
SELECT age(clock_timestamp(), query_start), usename, datname, query FROM pg_stat_activity WHERE state != 'idle' AND query NOT ILIKE '%pg_stat_activity%' ORDER BY age desc;
这将显示非空闲查询的执行时间、用户名、数据库和查询语句。我们将结果从运行时间最长的倒序排序，并从结果中排除此特定查询。
类似地，你可以看到所有不是空闲的进程，但有一个等待事件:
ELECT usename, datname, query, wait_event_type, wait_event FROM pg_stat_activity WHERE state != 'idle' AND query wait_event != ''
Check other system statistics
虽然pg_stat_activity视图可能会提供识别较慢查询所需的大部分信息，但是查看其他系统统计信息也可以帮助识别其他优化目标。
Viewing database statistics
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)
这是很有价值的信息，可以帮助您评估是否可以从向数据库集群添加内存中获益，从而有效地缓存最常见的查询。
Viewing table statistics
另一个有用的视图家族是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:通过索引检索的行数。
这些列中的数字可以帮助您评估索引的执行情况，以及正在运行的查询是否有效地使用了索引。如果您发现您的表有许多顺序扫描，那么对最常见查询的创建相应的索引可能会有所收益。
Viewing index hits
如果您需要关于当前索引的更多信息，可以查看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显示索引扫描返回的总行数。
此信息可以帮助您了解查询中没有使用的索引。一旦确定了这些索引，就可以重写查询以利用索引，也可以删除未使用的索引以提高写性能。
Viewing lock information
您收集的一些关于慢速查询的信息可能指向锁定问题。您可以通过查询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。对于database和relations，它们可以在pg_database和pg_class中交叉引用（原文是 cross-referenced ，意思是不同的数据行可能会共享一些对象）。
- mode:执行或请求的锁模式。
- granted:一个布尔值，表示锁是否被授予。
Enable slow query logging
更容易找到长时间运行查询信息的一种方法是启用慢速查询日志记录。启用慢速查询日志允许PostgreSQL自动记录任何执行时间超过给定时间的查询。这样就可以在事后收集并分析慢查询信息。
Check if PostgreSQL is already logging slow queries
您应该做的第一件事是验证慢速查询日志的当前状态。如果已经启用了慢速查询日志记录，则无需执行任何操作。
您可以通过输入以下命令来检查是否启用了慢速查询日志记录:
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。
既然知道了当前状态，就可以根据需要对其进行更改。
Configure PostgreSQL to log slow queries
在继续讨论之前，必须注意到，虽然慢速查询日志非常有用，但它可能会对性能产生额外的影响。PostgreSQL必须执行额外的操作来计时每个查询，并将结果记录到日志中。这可能会影响性能并意外地填满硬盘空间。
在任何时候都记录慢速查询可能不是一个好主意。相反，应该在积极调查问题时启用该功能，并在完成调查后禁用该功能。
Logging slow queries globally
考虑到这一点，您可以通过修改PostgreSQL服务器的配置文件来全局配置慢速查询日志。您也可以交互式地修改这些值，但是在配置中设置良好的默认值将使以后的交互式调整更容易。
打开PostgreSQL的配置文件。您可以通过输入以下命令找到当前配置文件的位置:
SHOW config_file; config_file ----------------------------------------- /etc/postgresql/14/main/postgresql.conf (1 row)
在文件中，搜索log_min_duration_statement设置。如果上面的示例输出值是从配置文件中读取的，那么它将被设置为-1，表示当前禁用了该功能。还有一些其他相关的设置，你可以根据自己的需要进行调整:
. . . # 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设置被注释掉，其当前值设置为-1以表示默认值。其他设置在文件中有很好的注释，允许您对超过最小值的语句进行采样，而不是记录所有语句。最后一个设置还允许对事务中出现的语句进行抽样。
您可以通过取消log_min_duration_statement的注释并将其设置为另一个值来打开长查询日志记录。
例如，我们可以将它设置为5秒来记录任何需要超过5秒才能完成的语句:log_min_duration_statement = 5s
例如，我们可以将它设置为5秒来记录任何需要超过5秒才能完成的语句:log_min_duration_statement = 5s
保存文件后，你可以在PostgreSQL中重新加载你的PostgreSQL服务器，输入:
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
现在，设置字段被设置为5000，单位字段被设置为ms，这表明我们设置的5秒已被转换为5000毫秒并应用。sourcefile行还确认正在从我们修改过的配置文件中读取该值
Logging slow queries per database
当尝试检测慢速查询时，另一个选项是将慢速查询日志记录限制在特定数据库中。虽然log_min_duration_statement可以全局设置(如上一节所示)，但也可以在数据库级别配置它。
要打开单个数据库的慢速查询日志记录，使用ALTER database命令:
ALTER DATABASE helloprisma SET 'log_min_duration_statement' = 2000; ALTER DATABASE
注意，与全局设置不同，在使用ALTER DATABASE命令时，该值必须是一个以毫秒为单位表示时间的无单位整数。
我们可以通过查询每个数据库的角色设置来验证设置是否已经应用:
\drds List of settings Role | Database | Settings -----+-------------+------------------------------- | helloprisma | log_min_duration_statement=2000 (1 row)
我们可以验证这并没有干扰我们之前设置为5秒阈值的全局设置:
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
Testing slow query logging
通过发出超过最小日志记录持续时间的语句来测试设置:
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);
现在，我们可以切换到只受全局设置影响的另一个数据库。同样的sleep语句不应该触发日志行:
\c postgres SELECT pg_sleep(4);
没有新的日志会被记录下来
总结
在本文中，我们介绍了如何查看和理解PostgreSQL提供的一些性能信息。查看这些信息可以让您深入了解系统资源、查询模式和配置设置中的不同瓶颈。当您遇到性能变慢时，您可以检查PostgreSQL提供的信息来开始调查问题的所在。
我们还讨论了如何使用慢速查询日志来精确查明哪些查询占用了系统资源，并且执行时间比预期长。记录这些数据并评估结果日志可以帮助您确定哪些地方可能需要额外的索引、不同的查询结构或更有效的查询设计。
了解如何识别这些昂贵的操作是运行功能更强大的数据库支持的应用程序的第一步。