香港服务器上的PostgreSQL查询变慢,如何从慢查询与磁盘I/O定位瓶颈?

发布人:A5数据发布时间:2026-10-12 10:04阅读量:153

CPU只有30%,并不意味着PostgreSQL还有充足的处理余量;磁盘%util接近100%,也不能单独证明存储已经达到吞吐上限。查询变慢时,真正需要确认的是:同一时间窗口内,哪些SQL耗时增加,增加的时间花在执行、锁等待、连接排队还是数据读取上,以及操作系统是否出现对应的I/O延迟。

定位香港服务器上的PostgreSQL瓶颈,应把慢查询、执行计划、数据库等待事件与磁盘指标放在同一条时间轴上。尤其当应用位于其他地区、数据库位于香港时,还要把跨地域网络耗时与服务器内部执行耗时分开,避免把线路波动误判为数据库性能下降。

A5数据提供中国香港及美国、日本、新加坡等地区的物理服务器,香港方案覆盖Xeon Gold、AMD EPYC等平台,并配备SSD或NVMe存储及不同内存配置,可作为PostgreSQL数据库、业务后台和接口服务的部署资源。针对应用与数据库的地域分布,A5数据还提供CN2与国际带宽选项,结合多地区服务器资源,为查询负载、数据存储和跨地域访问构建相应的硬件与网络基础。

建立观察窗口:先把“变慢”拆成几段时间

一次请求的响应时间,可以近似拆成连接池等待、网络往返、数据库处理和结果传输几部分。它们可能重叠,也可能在重试后被重复计算,因此不能简单把各项P95相加,但这个拆分有助于明确采集位置。

观察层级应采集的指标主要回答的问题
应用入口请求量、响应时间P50/P95/P99、错误率、超时率是否整体变慢,还是少数请求拖长尾部
连接池活跃连接、空闲连接、等待请求数、获取连接耗时请求是否还没进入数据库就开始排队
PostgreSQLSQL调用次数、执行耗时、块读取、临时块、等待事件哪类查询变慢,时间可能花在哪里
Linux主机CPU各类时间、可用内存、换页、磁盘延迟与队列数据库之外是否存在资源压力
网络链路应用到数据库RTT、丢包或重传、结果集大小是否受跨地域通信或传输影响

选择一个覆盖异常前后各十几分钟的窗口,将应用、数据库和主机监控统一到同一时区,并确认系统时间同步。主机指标可以按1~5秒采样,数据库累计统计则按固定间隔取差值;不要拿一小时平均CPU与某一秒的慢查询直接比较。

对于香港服务器,还应明确实际部署位置:如果应用与PostgreSQL都在香港同一私有网络内,公网访问延迟通常不是数据库内部慢查询的直接解释;如果应用位于其他地区,则连接建立、频繁的小查询往返和大结果集传输都可能拉长接口响应。

先确认版本、统计能力和权限

以下示例面向Linux上的PostgreSQL 14及以上版本。系统命令以已经安装sysstat等监控工具为前提,不涉及重启服务。

SELECT version();

SELECT extname, extversion
FROM pg_extension
WHERE extname = 'pg_stat_statements';

SHOW track_io_timing;
SHOW block_size;

pg_stat_statements需要相应扩展和预加载配置。没有启用时,不应在故障高峰直接修改配置并重启数据库;可以先使用已有慢查询日志、应用追踪和pg_stat_activity缩小范围,再安排统计能力建设。

开启track_io_timing后,相关统计才会记录I/O计时;未开启期间的零值不能理解成“没有I/O等待”。它也有平台相关的采集开销,启用前应评估,记录原值并保留恢复方式。查看其他会话和完整SQL通常还需要适当的监控权限,不宜为排障直接授予超级用户权限。

指标A变化:找到真正贡献延迟的SQL

慢查询日志能指出某次执行很慢,pg_stat_statements则更适合观察一类归一化SQL在一段时间内的变化。两者互补,但都不能独立解释磁盘瓶颈。

慢查询日志包含参数和业务信息时,应限制访问与保留周期。日志阈值也应与接口延迟目标匹配:一个目标响应时间为200毫秒的接口,不能只靠“超过1秒才记录”的日志定位所有性能问题。

累计排行榜只能初筛,窗口增量才适合归因

下面的查询适合寻找候选SQL,但输出是统计累计值,不是本次异常窗口的结果。扩展安装在非默认模式时,需要补充对应模式名。

SELECT
    dbid,
    userid,
    queryid,
    calls,
    total_exec_time,
    mean_exec_time,
    rows,
    shared_blks_hit,
    shared_blks_read,
    temp_blks_read,
    temp_blks_written,
    blk_read_time,
    blk_write_time,
    left(query, 300) AS query_sample
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

total_exec_time、mean_exec_time和I/O计时的单位为毫秒。块数需要结合block_size换算,不能直接当成磁盘读取字节数。

应在窗口起点和终点各保存一次快照,按相同的dbid、userid、queryid匹配,计算:

  • 窗口平均执行时间:执行时间增量 ÷ 调用次数增量。
  • 每次共享块读取数:共享块读取增量 ÷ 调用次数增量。
  • 每次临时块写入数:临时块写入增量 ÷ 调用次数增量。
  • 窗口总耗时占比:该SQL执行时间增量 ÷ 候选SQL总执行时间增量。

期间发生统计重置、条目淘汰或数据库重启时,差值可能失效。不要为了计算方便清空生产统计,这会破坏其他监控的基线。

慢SQL的优先级不只取决于单次耗时,还取决于调用频率、窗口总耗时和业务影响。 一个偶发的10秒报表,与每秒执行几十次、从20毫秒变成150毫秒的接口查询,可能需要不同的处理顺序。

还要注意,数据库执行统计不等于完整接口延迟。连接池排队、网络往返和应用处理都可能发生在数据库统计之外;执行均值也不能代替P95或P99。

用一组模拟变化建立因果候选

下面是一组用于解释分析方法的模拟数据,并非A5IDC实测。两个窗口均为5分钟,请求量和业务类型接近。

指标正常窗口变慢窗口
应用请求量约80次/秒约78次/秒
接口响应时间P9585毫秒1400毫秒
目标SQL窗口平均执行时间60毫秒330毫秒
目标SQL每次共享块读取40块600块
连接池获取连接P95低于5毫秒210毫秒
CPU用户态与系统态占比24%29%
CPU iowait2%17%
磁盘读延迟r_await3毫秒18毫秒
平均I/O队列aqu-sz1.228
可用内存5.8 GiB5.5 GiB
采样期间换入、换出接近0接近0

这组变化支持一个候选解释:查询读取需求增加,磁盘请求排队,数据库处理时间拉长,随后连接占用时间增加,连接池也开始排队。

指标A变化:找到真正贡献延迟的SQL/用一组模拟变化建立因果候选配图

但它还不是结论。共享块读取增加不一定等于物理磁盘读取增加,磁盘排队也可能由备份或其他进程引起。下一步需要补齐两者之间的联系。

指标B联动:确认查询是否真的在等待存储

PostgreSQL的“读块”与磁盘的“读数据”不是同一件事

shared_blks_hit表示数据块在PostgreSQL共享缓冲区中找到;shared_blks_read表示需要从共享缓冲区之外读取。这些读取可能被Linux文件页缓存满足,不一定触发物理磁盘访问。

以8 KiB块大小为例,每次查询读取600块,对应约4.69 MiB的数据进入共享缓冲区。这个数值描述数据库侧读取量,不能直接认定磁盘每次读取了4.69 MiB。

因此,判断存储瓶颈至少需要三层证据:

从SQL到共享缓冲区、Linux文件页缓存再到磁盘的分层读取路径,区分两级缓存命中与实际设备访问;旁侧对应SQL读取统计、瞬时等待事件和磁盘监控

  1. 目标SQL的读取量或I/O计时增加。
  2. 执行期间出现与读取相关的等待事件。
  3. 对应磁盘的延迟、队列或吞吐同步变化。

可以在异常期间多次采样活跃会话:

SELECT
    pid,
    application_name,
    now() - query_start AS elapsed,
    wait_event_type,
    wait_event,
    left(query, 200) AS query_sample
FROM pg_stat_activity
WHERE state = 'active'
  AND pid <> pg_backend_pid()
ORDER BY query_start;

多次观察到wait_event_type = 'IO'且事件为DataFileRead,说明这些采样时刻确实在等待数据文件读取。一次没有捕捉到等待,不代表整个执行过程没有I/O瓶颈;等待事件是瞬时状态,短等待很容易被低频采样漏掉。

PostgreSQL 16及以上还可以查看pg_stat_io,按后端类型和I/O上下文辅助区分业务读取、后台写入等活动。但它不是按SQL归因的统计,仍需与查询级数据和系统指标一起使用。

对准数据库所在设备,而不是任意一块盘

Linux侧可以使用:

iostat -xz -y 1 60
vmstat 1 60
pidstat -d -p ALL 1 60

iostat重点看r_await、w_await、aqu-sz和读写吞吐;vmstat用于观察换页与CPU状态;pidstat帮助识别同机其他进程的I/O活动。vmstat首行通常是启动以来的平均值,不应当作当前一秒的数据。

数据库数据目录、WAL目录和表空间可能位于不同设备上,应分别确认挂载关系。虚拟服务器还可能存在逻辑卷或虚拟块设备,多个层级的数据不能简单相加。

判断时要避免两个常见误区:

  • 低吞吐不等于磁盘空闲。 大量小块随机读取可能先耗尽IOPS能力,此时每秒读取量并不高,但延迟和队列已经明显上升。
  • 高%util不等于达到所有性能上限。 在多队列设备、NVMe或虚拟存储上,它不能完整表示剩余并行能力,应结合延迟、队列和业务响应判断。

同样,iowait高只能提供主机存在I/O等待的线索,不能直接指出哪个查询慢,也不能代替数据库等待事件。

排除替代解释:同一组现象可能有不同起点

多指标联动的价值,不只是支持某个解释,也在于排除看起来相似的原因。

连接池排队是起因,还是查询变慢后的结果?

如果SQL执行时间基本不变,磁盘延迟也稳定,但获取连接耗时先上升,应检查连接池是否过小、连接是否泄漏,以及长事务是否长期占用连接。

如果数据库执行时间和磁盘队列先上升,连接池等待随后增加,连接池更可能是下游症状。此时扩大连接池可能让更多查询同时争抢磁盘,进一步拉长尾部延迟。

当数据库已经出现持续I/O排队时,增加连接数不等于增加处理能力。 应先降低每次查询的读取成本,或控制并发,再评估连接池容量。

左右两个同维度面板,各以连接池等待、SQL执行时间、磁盘延迟与队列三条状态带表达先后变化;仅展示定性顺序,不伪造实测时间和数值

连接池大小也不能只按应用实例单独设置:多个实例的连接上限相加,才是数据库面对的潜在并发。

锁等待会把“运行很久”伪装成慢执行

会话执行时间长,但wait_event_type主要为Lock时,应先查阻塞关系:

SELECT
    pid,
    now() - query_start AS elapsed,
    pg_blocking_pids(pid) AS blocking_pids,
    left(query, 200) AS query_sample
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

这种情况下,磁盘指标可能只是同时发生的背景现象。继续增加索引或内存,未必能解除阻塞。

不要直接终止所有长会话。取消查询和终止连接对事务、业务重试及回滚时间的影响不同,应先确认阻塞者的业务身份和事务状态,再由有权限的人员处理。

排序落盘与内存压力也会制造I/O

如果temp_blks_written明显增加,执行计划出现外部排序或哈希分批,磁盘忙可能主要来自临时文件,而不是表数据读取。

work_mem不是“每个连接固定使用这么多内存”,而是执行节点的内存预算,并可能被多个节点、并发查询及并行工作进程重复使用。全局调大后,单条SQL可能更快,整机却可能开始换页。

应优先减少参与排序、聚合和连接的数据量,再考虑针对具体会话调整,并记录原值、测试后恢复。若vmstat中的si、so持续非零,还应结合数据库进程内存和容器限制分析,不能只看主机剩余内存。

网络、后台任务和虚拟化资源也要放进时间轴

香港服务器的地区属性影响链路,不会直接决定SQL执行计划。如果数据库执行时间稳定,而应用侧耗时增长,且跨地域RTT、重传或结果传输时间同步增加,应先调查网络侧。

如果多个不同SQL同时变慢,且磁盘写入突增,应检查备份、批量导入、检查点、自动清理和同机其他进程。不要因为看到自动清理运行就直接关闭它,长期关闭会带来表膨胀和维护风险。

若CPU steal明显增加,则需要考虑虚拟化环境中的CPU争用。云盘或其他虚拟存储还可能受IOPS、吞吐额度和突发能力影响,应核对对应产品规格及平台监控,不能凭“SSD”或“NVMe”名称推断持续性能。

形成判断:用执行计划区分“读得太多”与“存储太慢”

确认目标SQL与I/O变化有关后,下一步不是立即升级磁盘,而是判断读取量为什么增加。

先看估算计划,再受控获取实际计划

下面用订单查询举例,表名、字段和条件都需要替换为真实业务对象:

EXPLAIN
SELECT id, created_at, total_amount
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
  AND created_at >= TIMESTAMP '2025-01-01 00:00:00'
ORDER BY created_at DESC
LIMIT 100;

普通EXPLAIN不执行目标SQL,可以先观察扫描方式、过滤条件和排序节点。需要验证实际行数与读取量时,再使用EXPLAIN ANALYZE。

它会真正执行SQL,不能无条件在生产高峰运行。应优先在具有相近数据分布的测试环境验证;生产采样仅限确认无写入副作用的普通查询,并设置超时。涉及写入SQL时,不应将只读查询的做法直接套用。

BEGIN READ ONLY;

SET LOCAL statement_timeout = '5s';
SET LOCAL lock_timeout = '1s';

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT id, created_at, total_amount
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
  AND created_at >= TIMESTAMP '2025-01-01 00:00:00'
ORDER BY created_at DESC
LIMIT 100;

ROLLBACK;

这个示例的参数只在当前事务内生效,结束后恢复;如果查询超时,事务会进入失败状态,需要执行ROLLBACK。只读事务不代表零成本,执行仍可能产生读取压力并改变缓存状态。

将计划证据对应到优化动作

重点看估算行数与实际行数的偏差、扫描后丢弃的行数、共享块读取、临时块,以及排序或哈希是否落盘。嵌套节点中的缓冲区统计可能包含子节点贡献,不应把每一行机械相加。

联动证据更可能的解释优先处理方向
返回很少,扫描很多;块读取上升访问路径不合适,读取放大改写条件或设计匹配查询的索引
估算行数与实际行数差距明显统计信息或数据分布问题检查分析维护,必要时评估扩展统计
临时块增加,排序或哈希落盘中间结果过大或内存预算不足先减少中间数据,再局部评估内存参数
读取量接近不变,I/O延迟与队列上升存储争用或能力不足排查后台任务、限额和存储规格
数据库耗时稳定,应用耗时增加连接池、网络或应用侧问题沿数据库之外的时间段继续定位

对上面的订单查询,如果计划需要读取大量某租户订单,再过滤状态并排序,可以评估(tenant_id, status, created_at DESC)索引。它同时服务于等值过滤和时间排序,但是否值得建立,还取决于查询频率、已有索引、数据分布及写入成本。

示例DDL如下,不能不经核对直接用于生产:

CREATE INDEX CONCURRENTLY idx_orders_tenant_status_created
ON orders (tenant_id, status, created_at DESC);

生产执行前应确认备份可恢复、磁盘空间足够、没有功能重复的索引,并安排低峰窗口。并发创建不能放在事务块中;它仍会消耗CPU和I/O,并可能等待旧事务,失败后还可能留下无效索引。

回退时应确认没有约束或其他依赖,再使用:

DROP INDEX CONCURRENTLY idx_orders_tenant_status_created;

删除同样不能放在事务块中,且只应针对本次新增、确认可撤销的索引。

不宜为了追求索引扫描而强行关闭顺序扫描,也不能把所有返回列都加入覆盖索引。索引会增加存储、WAL和写入维护成本;索引仅扫描能否减少堆访问,还取决于可见性映射等条件。

如果已确认访问路径合理、每次读取量没有异常,而存储延迟在稳定负载下持续升高,才更有依据评估存储升级。判断依据应是持续IOPS、吞吐、延迟和队列,而不是盘型名称。

复测:验证改善发生在哪一层

每次只改变一个主要因素,并保留变更前的计划、窗口统计和配置。索引、查询改写、连接池大小与内存参数同时调整,即使最终变快,也很难知道哪个动作有效、哪个引入了风险。

复测应尽量保持相近的请求量、参数分布、并发和后台任务。一次手工执行变快,可能只是数据已进入缓存;至少要观察连续多个窗口,并覆盖主要参数类型。

沿用前面的模拟场景,索引调整后的有效证据可以是:

观察项变更前复测窗口示例
接口P951400毫秒130毫秒
目标SQL平均执行时间330毫秒45毫秒
每次共享块读取600块12块
磁盘读延迟18毫秒5毫秒
平均I/O队列282
连接池获取连接P95210毫秒低于10毫秒

这些数值只是示例,不构成效果承诺。真正有解释力的是变化方向:查询读取量减少,磁盘排队下降,连接占用时间缩短,接口长尾随之改善。还应同时确认错误率、超时率和写入延迟没有恶化,避免只优化读查询却影响订单写入等核心业务。

如果SQL执行时间下降,但接口P95几乎不变,应继续检查连接池、网络和结果传输;如果接口暂时改善,但磁盘队列仍持续偏高,则可能只是暂时避开了高峰,不能认定容量问题已经解决。

下一次遇到香港服务器上的PostgreSQL查询变慢,建议同时打开三组指标:

  • SQL与等待:调用次数增量、执行时间增量、每次块读取、临时块、锁与I/O等待事件。
  • 存储与主机:读写延迟、队列、IOPS与吞吐、可用内存、换页、CPU steal及后台任务。
  • 用户体验与排队:接口P95/P99、连接池等待、请求量、错误率,以及应用到数据库的网络变化。

当这三组指标在同一时间窗口内指向同一条因果链,才能较有把握地区分:是SQL制造了过多读取,还是存储无法及时完成合理读取,或者真正的延迟根本发生在数据库之外。

本文由A5数据原创发布,转载请注明出处:https://www.a5idc.com/helpcontent/3323.html

相关推荐