诊断调优
数据库诊断调优是指对数据库系统进行检测、分析和优化，以提高其性能、稳定性和可维护性的过程。这个过程通常包括监视数据库的运行状况、识别性能问题的根本原因，以及采取相应的措施来改进数据库的性能，例如创建合适的索引结构，收集必要的统计信息等。
通常情况下，当用户的查询出现不符合预期的性能问题，典型表现为在预定时间内没有完成，我们称这类查询为慢 SQL，可以通过如下步骤进行排查慢 SQL 的原因：
- 查看系统负载：直接影响查询性能的原因可能是当前系统已经过载了，而此时新的 SQL 反而会进一步增加系统的负载，导致系统整体的性能出现较大的退化。可以管理平台查看当前系统的负载情况，除此之外还可以通过 pg_stat_activity 视图来查询当前系统中正在运行的查询状态。
- 查看慢 SQL 的执行计划：发现慢 SQL 之后，可以通过 EXPLAIN 命令来查看这个 SQL 的实际执行计划，可以判断查询是否选择了预期的 index，是否拥有足够的统计信息，是否选择合适的执行方式等。
- 查看慢 SQL 的执行统计信息：在排查 SQL 的执行计划同时，可以结合着 SQL 实际的运行信息来定位慢 SQL 的瓶颈出现在具体的哪个节点，进而采取进一步的措施来修正执行计划。
pg_stat_activity
pg_stat_activity 是 ProtonBase 数据库中的一个系统视图（system view），用于查看当前数据库中活动的会话（sessions）和查询的信息。该视图包含了有关正在执行的查询、连接状态、连接时长等信息。详细的字段信息如下表：
| 字段名 | 字段类型 | 说明 |
|---|---|---|
| datid | oid | 数据库 ID |
| datname | name | 数据库名称 |
| pid | integer | 执行 SQL 的进程 ID |
| leader_pid | integer | |
| usesysid | oid | 发起 SQL 的用户 ID |
| usename | name | 发起 SQL 的用户名 |
| application_name | text | 执行 SQL 的应用程序名称 |
| client_addr | inet | 发起 SQL 的客户端 IP 地址 |
| client_hostname | text | 发起 SQL 的客户端主机名 |
| client_port | integer | 发起 SQL 的客户端端口号 |
| backend_start | timestamp with time zone | 接受该 SQL 的服务端启动时间戳 |
| xact_start | timestamp with time zone | SQL 使用的 transaction 的启动时间戳 |
| query_start | timestamp with time zone | SQL 开始执行时间戳 |
| state_change | timestamp with time zone | SQL 的执行状态变化时间戳 |
| wait_event_type | text | SQL 当前等待的资源类型，如果没有等待则为 NULL |
| wait_event | text | SQL 等待的资源信息 |
| state | text | SQL 使用的 session 状态 |
| backend_xid | bigint | SQL 使用的 transaction ID |
| backend_xmin | bigint | |
| query_id | bigint | SQL ID |
| query | text | SQL 的文本信息 |
| backend_type | text | 当前后端类型 |
用户可以通过 SQL 查询 pg_stat_activity 视图的信息，例如可以通过该视图实现如下功能：
- 查看当前正在执行的查询
SELECT * FROM pg_stat_activity WHERE state = 'active';- 查看特定用户的活动会话
SELECT * FROM pg_stat_activity WHERE usename = 'your_username';- 查询执行时间超过 10s 的查询
SELECT * FROM pg_stat_activity WHERE now() - query_start > interval '10s';在使用 pg_stat_activity 的时候也需要了解如下注意事项：
- 权限：对于
pg_stat_activity视图，通常需要具有适当权限的用户才能够查询。确保使用具有足够权限的用户执行查询。 - 信息解释：阅读
pg_stat_activity的输出需要对 ProtonBase 的内部工作有一些了解，特别是对连接状态、查询状态等的理解。 - 性能影响：查询
pg_stat_activity本身对数据库的性能影响较小，但在繁忙的数据库上执行大量查询可能会轻微地增加系统开销。因此，在生产环境中谨慎频繁使用。
EXPLAIN
在 ProtonBase 系统中，EXPLAIN 语句用于显示查询计划，这是优化器生成的描述查询执行方式的信息。通过查看执行计划，您可以了解 ProtonBase 如何选择执行查询的路径，包括使用了哪些索引、使用何种方式进行 JOIN 等。
以下是基本的 EXPLAIN 语句的使用：
EXPLAIN
SELECT l_returnflag,
l_linestatus,
SUM(l_quantity) AS sum_qty,
SUM(l_extendedprice) AS sum_base_price,
SUM(l_extendedprice * (1 - l_discount)) AS sum_disc_price,
SUM(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,
AVG(l_quantity) AS avg_qty,
AVG(l_extendedprice) AS avg_price,
AVG(l_discount) AS avg_disc,
COUNT(*) AS count_order
FROM lineitem
WHERE l_shipdate <= date '1998-12-01' - INTERVAL ':1' DAY
GROUP BY l_returnflag, l_linestatus
ORDER BY l_returnflag, l_linestatus;
QUERY PLAN
Response/dop=1
-> Noop/dop=1 (cost=0.00..1088857.22 rows=6 width=0)
-> Sort/dop=1 (cost=0.00..1088857.12 rows=6 width=0)
" Sort Key: l_returnflag asc,l_linestatus asc"
-> Finalize Aggregate/dop=1 (cost=0.00..1088856.66 rows=6 width=0)
" Group Key: l_returnflag, l_linestatus"
-> Gather Channels:6 Method:Unordered
-> Partial Aggregate/dop=6 (cost=0.00..1088854.00 rows=15 width=0)
" Group Key: l_returnflag, l_linestatus"
-> Noop/dop=6 (cost=0.00..368708.03 rows=6001215 width=0)
-> Seq Scan on lineitem/dop=6 (cost=0.00..188671.57 rows=6001215 width=0)
Filter: l_shipdate <= '1998-11-30'当对慢查询进行性能调优时，可以首先通过 EXPLAIN 语句来查看当前使用的执行计划，再根据具体的数据分布情况判断执行计划都否足够优，例如是否选中了合适的 index，是否选择了最优的 join 算法(hash join/merge join/lookup join)、是否选择了合适的 aggregation 算法(hash/merge aggregation)等。
EXPLAIN ANALYZE
在 ProtonBase 中，EXPLAIN ANALYZE 是一种结合 EXPLAIN 和 ANALYZE 的语法，用于获取查询执行计划并执行实际查询以获取详细的性能统计信息。这个语句对于调优查询和了解实际执行的成本非常有用。它的使用方法和 EXPLAIN 语句类似，EXPLAIN ANALYZE 在展示执行计划的同时会执行一次查询，并且在执行的过程中收集运行时的统计信息。
如下展示一个查询 EXPLAIN ANALYZE 的结果
EXPLAIN ANALYZE
SELECT l_returnflag,
l_linestatus,
SUM(l_quantity) AS sum_qty,
SUM(l_extendedprice) AS sum_base_price,
SUM(l_extendedprice * (1 - l_discount)) AS sum_disc_price,
SUM(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,
AVG(l_quantity) AS avg_qty,
AVG(l_extendedprice) AS avg_price,
AVG(l_discount) AS avg_disc,
COUNT(*) AS count_order
FROM lineitem
WHERE l_shipdate <= date '1998-12-01' - INTERVAL ':1' DAY
GROUP BY l_returnflag, l_linestatus
ORDER BY l_returnflag, l_linestatus; l_linestatus;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Response/dop=1
-> Aggregate/dop=1 (cost=0.00..4107784.80 rows=6001215 width=0)
Group Key: l_returnflag, l_linestatus
Ordered Key: l_returnflag, l_linestatus
disk.vectorized.max sum:0 B avg:0 B min:0 B max:0 B
mem.vectorized.max sum:0 B avg:0 B min:0 B max:0 B
output.batches sum:1 avg:1.00 min:1 max:1
output.tuples sum:4 avg:4.00 min:4 max:4
time.execution sum:0.690s avg:0.690s min:0.690s max:0.690s
-> Noop/dop=1 (cost=0.00..3447651.13 rows=6001215 width=0)
disk.vectorized.max sum:0 B avg:0 B min:0 B max:0 B
mem.vectorized.max sum:0 B avg:0 B min:0 B max:0 B
output.batches sum:745 avg:745.00 min:745 max:745
output.tuples sum:6001197 avg:6001197.00 min:6001197 max:6001197
time.execution sum:0.065s avg:0.065s min:0.065s max:0.065s
-> Noop/dop=1 (cost=0.00..3267614.67 rows=6001215 width=0)
disk.vectorized.max sum:0 B avg:0 B min:0 B max:0 B
mem.vectorized.max sum:0 B avg:0 B min:0 B max:0 B
output.batches sum:745 avg:745.00 min:745 max:745
output.tuples sum:6001197 avg:6001197.00 min:6001197 max:6001197
time.execution sum:0.074s avg:0.074s min:0.074s max:0.074s
-> Gather Channels:6 Method:Ordered(l_returnflag asc,l_linestatus asc)
disk.vectorized.max sum:0 B avg:0 B min:0 B max:0 B
mem.vectorized.max sum:0 B avg:0 B min:0 B max:0 B
output.batches sum:733 avg:122.17 min:0 max:733
output.tuples sum:6001197 avg:1000199.50 min:0 max:6001197
time.execution sum:1.125s avg:0.188s min:0.000s max:1.122s
-> Sort/dop=6 (cost=0.00..3032367.02 rows=6001215 width=0)
Sort Key: l_returnflag asc,l_linestatus asc
disk.vectorized.max sum:0 B avg:0 B min:0 B max:0 B
mem.vectorized.max sum:668 MiB avg:111 MiB min:0 B max:668 MiB
output.batches sum:733 avg:122.17 min:0 max:733
output.tuples sum:6001197 avg:1000199.50 min:0 max:6001197
time.execution sum:1.142s avg:0.190s min:0.000s max:1.142s
-> Seq Scan on lineitem/dop=6 (cost=0.00..68647.26 rows=6001215 width=0)
Filter: l_shipdate <= '1998-11-30'
bytes.read sum:240 MiB avg:40 MiB min:240 MiB max:240 MiB
columns.read sum:5173 avg:862.17 min:5173 max:5173
disk.vectorized.max sum:0 B avg:0 B min:0 B max:0 B
hit_cache.percent sum:600 avg:100.00 min:100 max:100
hit_cache.total_count sum:49014 avg:8169.00 min:8169 max:8169
mem.vectorized.max sum:40 KiB avg:6.7 KiB min:0 B max:40 KiB
output.batches sum:739 avg:123.17 min:0 max:739
output.tuples sum:6001197 avg:1000199.50 min:0 max:6001197
row_groups.read sum:739 avg:123.17 min:739 max:739
rowgroup.itors.created sum:60 avg:10.00 min:60 max:60
rowgroup.itors.read sum:60 avg:10.00 min:60 max:60
rows.read sum:6001197 avg:1000199.50 min:6001197 max:6001197
splits.read sum:10 avg:1.67 min:10 max:10
time.cmd.eval sum:0.001s avg:0.000s min:0.000s max:0.000s
time.cmd.execution sum:0.002s avg:0.000s min:0.000s max:0.000s
time.cmd.sequence sum:0.000s avg:0.000s min:0.000s max:0.000s
time.cmd.sequence.latch sum:0.000s avg:0.000s min:0.000s max:0.000s
time.io sum:0.027s avg:0.004s min:0.000s max:0.025s
Planning Time: 2.83 ms
Execution Time: 2.58e+03 ms
(56 rows)EXPLAIN ANALYZE 的输出可能看起来有点复杂，但它是优化和调优查询的重要工具。通过分析执行计划，您可以了解查询是如何执行的，有助于识别性能问题并采取相应的优化措施。