当前位置： 首页 > news >正文

PostgreSQL 高负载 Load Average 暴涨 | BufferMapping LWLock 锁竞争 完整排查优化实战

news 2026/7/6 23:02:45

文章目录

  • 一、故障现场全景呈现
    • 1. 服务器整体资源异常（top监控）
    • 3. 磁盘IO详细监控
    • 4. 数据库内部等待事件定位
    • 5. 数据库原始内存参数配置
  • 二、相关技术概念说明
  • 三、根本原因分析
  • 四、解决方案与优化建议
  • 五、优化效果验证

一、故障现场全景呈现

本次故障发生在16GB内存的PostgreSQL数据库服务器，核心表现为系统负载飙升、CPU满载，结合top、vmstat、iostat三大系统监控工具及数据库内部等待事件，形成完整故障链路，精准定位问题根源。

1. 服务器整体资源异常（top监控）

执行top命令获取系统整体状态，关键指标异常明显：

op-13:49:56 up137days,23:10,2users, load average:82.05,75.69,74.35Tasks:464total,30running,434sleeping,0stopped,0zombie %Cpu(s):89.7us,9.3sy,0.0ni,0.6id,0.1wa,0.0hi,0.2si,0.0st MiB Mem:15954.5total,250.1free,1457.2used,14247.1buff/cache MiB Swap:8192.0total,7892.5free,299.5used.12526.8avail Mem PIDUSERPR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND1455217postgres20019126161.2g1.1g R10.67.70:22.66 postgres1457456postgres2001759928768768751728R10.64.70:12.48 postgres1457863postgres20018781361.2g1.1g R10.37.50:20.52 postgres1446316postgres2001754100811572808976R10.05.02:00.04 postgres1457835postgres2001756072500896486136R10.03.10:34.81 postgres1467978postgres200189628412875279464R9.60.80:02.25 postgres1468383postgres2001742660557008554028S9.33.40:00.60 postgres1467989postgres200188394411195274200R9.00.70:02.10 postgres1427874postgres20018793401.1g1.1g R8.37.32:26.32 postgres1467990postgres200188393611183274076R8.30.70:02.09 postgres1468053postgres2001742628559984557076S8.03.40:01.48 postgres1468236postgres2001742584556476553640R8.03.40:00.88 postgres1468432postgres2001742588556912554112S8.03.40:00.29 postgres1468434postgres2001743160558856555948R8.03.40:00.30 postgres1468468postgres2001742568557184554356S8.03.40:00.26 postgres1468225postgres2001741516437524435456S7.62.70:00.85 postgres1468328postgres2001742584506188503388R7.63.10:00.43 postgres1468329postgres2001741260437312435296S7.62.70:00.42 postgres
  • 系统负载（load average）长期维持在74~82，远超服务器8核CPU的合理负载（正常负载≈CPU核心数），系统严重过载。
  • CPU用户态占用（us）高达89.7%，空闲CPU（id）仅0.6%，几乎所有CPU资源被数据库进程耗尽。
  • 大量postgres进程处于R（运行）状态，进程排队竞争CPU资源，壅塞严重。
  • 内存整体使用率不高，但绝大多数内存被系统缓存（buff/cache）占用，PostgreSQL自身共享缓存配置严重不足，无法有效缓存热数据。
  • Swap分区已使用299.5MB，后续监控显示进一步攀升至538MB，说明物理内存已出现紧张迹象。
  1. 系统资源连续监控（vmstat 1 2000）
  2. 执行vmstat 1 2000命令，持续监控系统进程、内存、IO及CPU状态，截取关键数据如下：
vmstat12000

输出：

procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu----- r b swpdfreebuff cache si so bi boincs us syidwa st960303136254836122041468108810182441184002664720011833031362402281220414682896002276885518488985150001121303136247200122041468639600241652588754948515100680303136260732122041467592000203264601056948414200301303136255804122041467866400200858069746555851410011913031362405361221214680136001576612662763078712100115130313624484412220146851881601464109255685203841510068030313626105212220146851320010645649374867851500011813031362487601222014687728001872572589754848613100

vmstat 分析:

  • CPU执行队列（r）：23~39，远高于8核CPU的合理值（8左右），大量进程排队等待CPU，是系统负载暴高的直接原因。
  • 阻塞进程（b）：9-17，看似偏高，但结合wat值（0%~1%）可明确：进程阻塞并非因硬盘IO缓慢，而是因数据库内部锁竞争（与后续数据库等待事件完全吻合）。
  • 磁盘读（bi）：瞬間冲高至28192，说明数据库频繁从硬盘加载数据页，缓存缺页现象严重。
  • CPU用户态（us）：96%~97%满载，几乎所有CPU资源被PostgreSQL查询耗尽，空闲CPU（id）趋近于0。
  • Swap使用（swpd）：538MB，物理内存紧张，进一步挤压数据库共享缓存的可用空间。

3. 磁盘IO详细监控

执行iostat -x 1 2000命令，深入分析磁盘IO性能，排除IO瓶颈

iostat-x12000

截取关键监控片段如下：

Linux5.15.0-91-generic(PCIPGDBPCAGLCY-P)04/23/2026 _x86_64_(8CPU)avg-cpu: %user %nice %system %iowait %steal %idle26.010.006.3820.180.0047.42Device r/s rkB/s rrqm/s %rrqm r_await rareq-sz w/s wkB/s wrqm/s %wrqm w_await wareq-sz d/s dkB/s drqm/s %drqm d_await dareq-sz f/s f_await aqu-sz %util sda15.65215.139.6038.035.5913.758.02170.8133.9480.892.3121.300.004.350.0042.790.854302.850.000.000.116.92sdb1563.3919265.343.610.230.1412.3261.701299.263.375.184.2021.060.03181.880.0243.761.117236.250.000.000.112.81avg-cpu: %user %nice %system %iowait %steal %idle92.880.006.120.000.001.00Device r/s rkB/s rrqm/s %rrqm r_await rareq-sz w/s wkB/s wrqm/s %wrqm w_await wareq-sz d/s dkB/s drqm/s %drqm d_await dareq-sz f/s f_await aqu-sz %util sda0.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.00sdb0.000.000.000.000.000.001.008.000.000.000.008.000.000.000.000.000.000.000.000.000.000.40avg-cpu: %user %nice %system %iowait %steal %idle91.490.006.630.000.001.88Device r/s rkB/s rrqm/s %rrqm r_await rareq-sz w/s wkB/s wrqm/s %wrqm w_await wareq-sz d/s dkB/s drqm/s %drqm d_await dareq-sz f/s f_await aqu-sz %util sda0.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.00sdb7.9279.210.000.007.3810.0020.79186.140.000.000.148.950.000.000.000.000.000.000.000.000.0611.09

iostat 分析:

  • 磁盘使用率（%util）：sda最高6.92%，sdb最高11.09%，均远低于100%，磁盘IO无饱和现象。
  • 读等待时间（r_await）：sdb最高7.38ms，sda最高5.59ms，均处于正常范围（一般<20ms），磁盘读响应正常。
  • %iowait：仅第一组数据为20.18%，后续两组均为0%，结合磁盘%util可知，短暂的iowait是因数据库集中读数据（bi冲高），并非磁盘性能不足，且持续时间极短，不构成IO瓶颈。
  • 核心佐证：CPU满载（us 91%~93%）时，磁盘IO处于低负载状态，进一步说明系统瓶颈在CPU和数据库锁竞争，而非磁盘IO。

4. 数据库内部等待事件定位

通过筛选高CPU进程PID，查询PostgreSQL系统视图pg_stat_activity，定位数据库内部阻塞原因，执行命令：

ps-eopid,pcpu,pmem,cmd--sort=-pcpu|greppostgres|head-n20|awk'{print $1}'|paste-sd","-|xargs-I{}psql-c" SELECT pid, usename, now()-query_start duration,datname, state, wait_event,wait_event_type,substr(query,1,20) query FROM pg_stat_activity WHERE pid IN ({});"

查询结果（关键片段）：

pid|usename|duration|datname|state|wait_event|wait_event_type|query ---------+-----------------+-----------------+-----------------+--------+---------------+-----------------+----------------------1460734|pcishoeleg_5010|00:00:03.423004|pcishoeleg_5010|active|BufferMapping|LWLock|WITH fact AS(\r +|||||||1460732|pcishoeleg_5010|00:00:04.349096|pcishoeleg_5010|active|BufferMapping|LWLock|WITH fact AS(\r +|||||||1460843|pcishoeleg_5010|00:00:02.941622|pcishoeleg_5010|active|BufferMapping|LWLock|WITH fact AS(\r +|||||||1460865|pcishoeleg_5010|00:00:00.323677|pcishoeleg_5010|active|BufferMapping|LWLock|WITH fact AS(\r +|||||||1460828|pcishoeleg_5010|00:00:02.909692|pcishoeleg_5010|active|BufferMapping|LWLock|WITH fact AS(\r +|||||||1460834|pcishoeleg_5010|00:00:01.168062|pcishoeleg_5010|active|BufferMapping|LWLock|SELECT Sum(time_yiel1460774|pcishoeleg_5010|00:00:02.185091|pcishoeleg_5010|active|BufferMapping|LWLock|WITH fact AS(\r +

核心现象：

  • 大量postgres会话（进程）统一阻塞在wait_event_type = LWLock、wait_event = BufferMapping，说明所有进程都在竞争同一把轻量级锁。
  • 阻塞会话执行的SQL均为报表类聚合查询，包含WITH子查询、SUM汇总、DISTINCT去重等需要扫描大量数据页的操作。

5. 数据库原始内存参数配置

系统实际内存状况：

free-mtotal usedfreeshared buff/cache available Mem:159545111836217152598886Swap:81911808011

数据库内存参数：

postgres=#show shared_buffers;shared_buffers196608#1.5GBpostgres=#show effective_cache_size;effective_cache_size524288# 4G

两个参数均配置失当：shared_buffers 严重偏低导致频繁缓存置换，effective_cache_size 低估导致优化器选择次优执行计划，两者叠加共同放大了 BufferMapping LWLock 的争用强度。

二、相关技术概念说明

  1. LWLock 轻量锁
    LWLock（Lightweight Lock）是 PostgreSQL 内核自行实现的内存级细粒度锁机制，与事务层面的表锁、行锁完全独立，专门用于保护数据库内部的共享内存数据结构。
  • 整个数据库内部有许多把不同用途的 LWLock，例如 WAL 写入锁、Clog 锁、Buffer Content 锁等
  • BufferMapping LWLock 只是众多 LWLock 实例中的一把具体的锁，专门用来保护 Shared Buffers 的全局哈希表
LWLock（机制） ↓ BufferMapping LWLock（具体实例）

其核心特点是：

  • 仅支持共享读（Shared） 和 独占写（Exclusive） 两种模式
  • 加锁与释放均在内存层面完成，开销极低、速度极快
  • 不涉及任何磁盘 I/O 操作
  1. BufferMapping 缓存映射锁
    PostgreSQL 的共享缓冲池（Shared Buffers）依赖一张全局哈希表（Buffer Tag Map） 来维护磁盘数据页与内存缓冲区块之间的映射关系。
    所有涉及缓冲区的操作——包括数据页加载入内存、缓冲区命中查找、缓冲区淘汰置换——都必须先获取这把全局 BufferMapping LWLock，才能对哈希表进行读写。
  2. 本次等待事件的本质
    在高并发场景下，大量进程同时竞争同一把全局锁，导致：
大量并发请求 ↓ 全局 BufferMapping LWLock 争用 ↓ 请求被迫串行化排队 ↓ CPU 运行队列堆积 → 系统负载飙升 → 所有查询延迟上升

三、根本原因分析

  1. 核心根本原因：shared_buffers 配置严重不足
  • 本次故障的根源在于内存配置与实际业务负载严重不匹配。服务器拥有 16GB 物理内存，但 shared_buffers 仅配置了 1.5GB，导致热点数据无法充分驻留内存。
  • 当报表类大查询持续扫描大量冷数据页时，缓冲区容量不足以容纳所需数据，触发频繁的缺页与缓存置换。每一次置换都必须反复操作全局 Buffer Tag Map，进而引发剧烈的 BufferMapping LWLock 争用，最终导致全局串行化排队。

  1. 业务触发原因：高并发报表查询叠加
    主库同时并发执行大量包含 WITH 临时表、SUM 聚合、DISTINCT 的报表 SQL，每条查询均需扫描大量物理数据页，大幅放大了缓存置换频率，进一步加剧了锁排队拥塞。

  2. 辅助原因：优化器缓存预估参数不合理
    effective_cache_size 配置过小，导致 PostgreSQL 查询优化器低估可用内存资源，在执行计划选择上倾向于全表顺序扫描而非索引扫描，读取的数据页数量持续暴增，恶化了缓存压力。

  3. 系统层面辅助因素：操作系统内存挤压
    操作系统占用了绝大部分内存作为系统缓存，数据库可用内存空间被进一步压缩，与 shared_buffers 配置不足形成叠加效应，共同恶化了缓存不足的问题。

shared_buffers 严重不足 ↓ 热点数据无法驻留内存，频繁缺页 ↓ 缓存置换反复操作全局 Buffer Tag Map ↓ BufferMapping LWLock 高度争用 ↓ 大量并发报表查询叠加（业务触发） effective_cache_size 低估导致全表扫描（放大效应） 操作系统内存挤压（辅助恶化） ↓ 全局串行化排队 → CPU 队列堆积 → 负载飙升 → 所有查询延迟上升

四、解决方案与优化建议

  1. shared_buffers 调整
    这是本次故障的根本修复点，建议调整为物理内存的 25%：
参数调整前调整后预期效果
shared_buffers1.5G4G热点数据充分驻留内存，缓存置换频率大幅下降
effective_cache_size4G10G优化器倾向选择索引扫描，减少全表扫描

2.SQL优化

  • 使用pg_profile产生报告并进行分析，如何使用pg_profile,请参考这篇文章–使用 pg_profile 在 Postgres 中生成性能报告

  • 主要关注Top SQL by execution time,截图如下：
    主要怎对红框中top4的SQL进行优化，添加了合适的索引（过程略）

五、优化效果验证

  • 调整shared_buffers与effective_cache_size后，LWLock、BufferMapping消失
  • 针对上图中红框中的高频执行的4条SQL,添加合适的索引后，cpu负载开始大幅下降
http://www.cnnetsun.cn/news/2114422.html

相关文章：

  • reactor
  • 2026年Geo专家综合能力与执行效果深度评测：AI时代下的信任重构
  • 深度解析开源自动化框架：从图像识别到黑盒测试的完整技术实践
  • Trippy网络诊断工具深度解析：现代网络故障排查的专业利器
  • Outfit字体：现代设计的无声革命者
  • VS Code Dev Containers 优化终极指南（2024年Linux/macOS/WSL3实测版）：从22秒冷启动到3.1秒的全链路调优路径
  • Unity GPU 波前（Wavefront / Warp）对 Shader 设计的影响
  • 终极指南：3步快速解密QQ聊天记录数据库
  • 智能办公新方式：OpenClaw 一键部署与配置
  • 3分钟快速部署i茅台自动预约系统：告别手动抢购的Java神器
  • 语义分割调参避坑：你的ASPP模块dilation rate选对了吗？PyTorch实验对比告诉你答案
  • Radeon ProRender Blender插件实战指南：三步实现专业级渲染效果
  • PitchDetect：基于Web Audio API的实时音高检测完整解决方案
  • 只要中一个，就说明你已经找到了对抗加班文化的正确方法
  • VTube Studio终极指南：如何通过API打造个性化虚拟主播体验
  • KUKA iiwa机器人FRI控制实战：手把手教你用Java在Sunrise Workbench 1.7里写第一个实时运动程序
  • 基于MCP协议的AI智能体工具集成平台：Klavis AI架构与应用实践
  • Go语言构建高可用分布式任务调度框架：从Cron到Copaw的实践
  • 5分钟终极指南：如何用untrunc免费快速修复损坏的MP4/MOV视频文件
  • Open WebUI本地部署：构建企业级AI对话平台的技术实践
  • 机器学习入门指南：从零基础到实战应用
  • 3个核心问题，1个终极解决方案：G-Helper让华硕笔记本性能管理变得简单
  • 大语言模型智能体的记忆安全攻防实践
  • Elasticsearch高级搜索实战：多字段相关性得分融合技巧全解
  • 从零构建CNN模型解决CIFAR-10图像分类实战指南
  • 3步掌握Akagi：AI麻将助手让你的雀魂水平突飞猛进
  • 自建AI智能体指挥中心：OpenClaw Dashboard架构与实战
  • 音乐人做编曲伴奏没思路？2026年度甄选5款AI编曲软件总结，解决歌曲的告高质量编曲伴奏的创作难题
  • Cursor Free VIP终极指南：3步永久免费解锁AI编程助手Pro功能
  • 鸣潮120帧解锁终极指南：WaveTools工具箱让游戏体验丝滑如飞