香港服务器上的PostgreSQL查询变慢,如何从慢查询与磁盘I/O定位瓶颈?
CPU只有30%,并不意味着PostgreSQL还有充足的处理余量;磁盘%util接近100%,也不能单独证明存储已经达到吞吐上限。查询变慢时,真正需要确认的是:同一时间窗口内,哪些SQL耗时增加,增加的时间花在执行、锁等待、连接排队还是数据读取上,以及操作系统是否出现对应的I/O延迟。
定位香港服务器上的PostgreSQL瓶颈,应把慢查询、执行计划、数据库等待事件与磁盘指标放在同一条时间轴上。尤其当应用位于其他地区、数据库位于香港时,还要把跨地域网络耗时与服务器内部执行耗时分开,避免把线路波动误判为数据库性能下降。
A5数据提供中国香港及美国、日本、新加坡等地区的物理服务器,香港方案覆盖Xeon Gold、AMD EPYC等平台,并配备SSD或NVMe存储及不同内存配置,可作为PostgreSQL数据库、业务后台和接口服务的部署资源。针对应用与数据库的地域分布,A5数据还提供CN2与国际带宽选项,结合多地区服务器资源,为查询负载、数据存储和跨地域访问构建相应的硬件与网络基础。
建立观察窗口:先把“变慢”拆成几段时间
一次请求的响应时间,可以近似拆成连接池等待、网络往返、数据库处理和结果传输几部分。它们可能重叠,也可能在重试后被重复计算,因此不能简单把各项P95相加,但这个拆分有助于明确采集位置。
| 观察层级 | 应采集的指标 | 主要回答的问题 |
|---|---|---|
| 应用入口 | 请求量、响应时间P50/P95/P99、错误率、超时率 | 是否整体变慢,还是少数请求拖长尾部 |
| 连接池 | 活跃连接、空闲连接、等待请求数、获取连接耗时 | 请求是否还没进入数据库就开始排队 |
| PostgreSQL | SQL调用次数、执行耗时、块读取、临时块、等待事件 | 哪类查询变慢,时间可能花在哪里 |
| 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次/秒 |
| 接口响应时间P95 | 85毫秒 | 1400毫秒 |
| 目标SQL窗口平均执行时间 | 60毫秒 | 330毫秒 |
| 目标SQL每次共享块读取 | 40块 | 600块 |
| 连接池获取连接P95 | 低于5毫秒 | 210毫秒 |
| CPU用户态与系统态占比 | 24% | 29% |
| CPU iowait | 2% | 17% |
磁盘读延迟r_await | 3毫秒 | 18毫秒 |
平均I/O队列aqu-sz | 1.2 | 28 |
| 可用内存 | 5.8 GiB | 5.5 GiB |
| 采样期间换入、换出 | 接近0 | 接近0 |
这组变化支持一个候选解释:查询读取需求增加,磁盘请求排队,数据库处理时间拉长,随后连接占用时间增加,连接池也开始排队。

但它还不是结论。共享块读取增加不一定等于物理磁盘读取增加,磁盘排队也可能由备份或其他进程引起。下一步需要补齐两者之间的联系。
指标B联动:确认查询是否真的在等待存储
PostgreSQL的“读块”与磁盘的“读数据”不是同一件事
shared_blks_hit表示数据块在PostgreSQL共享缓冲区中找到;shared_blks_read表示需要从共享缓冲区之外读取。这些读取可能被Linux文件页缓存满足,不一定触发物理磁盘访问。
以8 KiB块大小为例,每次查询读取600块,对应约4.69 MiB的数据进入共享缓冲区。这个数值描述数据库侧读取量,不能直接认定磁盘每次读取了4.69 MiB。
因此,判断存储瓶颈至少需要三层证据:

- 目标SQL的读取量或I/O计时增加。
- 执行期间出现与读取相关的等待事件。
- 对应磁盘的延迟、队列或吞吐同步变化。
可以在异常期间多次采样活跃会话:
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排队时,增加连接数不等于增加处理能力。 应先降低每次查询的读取成本,或控制并发,再评估连接池容量。

连接池大小也不能只按应用实例单独设置:多个实例的连接上限相加,才是数据库面对的潜在并发。
锁等待会把“运行很久”伪装成慢执行
会话执行时间长,但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、吞吐、延迟和队列,而不是盘型名称。
复测:验证改善发生在哪一层
每次只改变一个主要因素,并保留变更前的计划、窗口统计和配置。索引、查询改写、连接池大小与内存参数同时调整,即使最终变快,也很难知道哪个动作有效、哪个引入了风险。
复测应尽量保持相近的请求量、参数分布、并发和后台任务。一次手工执行变快,可能只是数据已进入缓存;至少要观察连续多个窗口,并覆盖主要参数类型。
沿用前面的模拟场景,索引调整后的有效证据可以是:
| 观察项 | 变更前 | 复测窗口示例 |
|---|---|---|
| 接口P95 | 1400毫秒 | 130毫秒 |
| 目标SQL平均执行时间 | 330毫秒 | 45毫秒 |
| 每次共享块读取 | 600块 | 12块 |
| 磁盘读延迟 | 18毫秒 | 5毫秒 |
| 平均I/O队列 | 28 | 2 |
| 连接池获取连接P95 | 210毫秒 | 低于10毫秒 |
这些数值只是示例,不构成效果承诺。真正有解释力的是变化方向:查询读取量减少,磁盘排队下降,连接占用时间缩短,接口长尾随之改善。还应同时确认错误率、超时率和写入延迟没有恶化,避免只优化读查询却影响订单写入等核心业务。
如果SQL执行时间下降,但接口P95几乎不变,应继续检查连接池、网络和结果传输;如果接口暂时改善,但磁盘队列仍持续偏高,则可能只是暂时避开了高峰,不能认定容量问题已经解决。
下一次遇到香港服务器上的PostgreSQL查询变慢,建议同时打开三组指标:
- SQL与等待:调用次数增量、执行时间增量、每次块读取、临时块、锁与I/O等待事件。
- 存储与主机:读写延迟、队列、IOPS与吞吐、可用内存、换页、CPU
steal及后台任务。 - 用户体验与排队:接口P95/P99、连接池等待、请求量、错误率,以及应用到数据库的网络变化。
当这三组指标在同一时间窗口内指向同一条因果链,才能较有把握地区分:是SQL制造了过多读取,还是存储无法及时完成合理读取,或者真正的延迟根本发生在数据库之外。
本文由A5数据原创发布,转载请注明出处:https://www.a5idc.com/helpcontent/3323.html



