诊断调优

数据库诊断调优是指对数据库系统进行检测、分析和优化，以提高其性能、稳定性和可维护性的过程。这个过程通常包括监视数据库的运行状况、识别性能问题的根本原因，以及采取相应的措施来改进数据库的性能，例如创建合适的索引结构，收集必要的统计信息等。

通常情况下，当用户的查询出现不符合预期的性能问题，典型表现为在预定时间内没有完成，我们称这类查询为慢 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)等。