DB2临时表空间膨胀10G拖垮整库：pool_table_latch竞争排查与解决

简介这份文档面向DB2数据库运维DBA与性能调优学习者记录某银行DB2系统SQL执行时间变长的真实排查案例核心聚焦系统临时表空间TEMPSPACE1异常膨胀至10GB所引发的性能问题。资源包共1个doc文件约607KB内容涵盖性能问题初步确认、LATCH分析方法、STACK收集与分析技巧以及用db2trc工具暂停实例抓取诊断信息等实操手段并给出SQL优化与参数调整的解决思路。已有5903人学习下载适合希望积累真实故障定位经验、补充临时表空间与闩锁竞争排查方法的中高级读者参考。1. 临时表空间为什么一个没人管的 10G 文件能拖垮整库一个 OLTP 库的活动会话数平时不到 10某天开始频繁冲到 50 以上SQL 平均执行时间从毫秒级涨到 3~6 秒。CPU 正常、内存正常、I/O 正常、缓冲池命中率正常、没有锁等待——常规排查一圈下来全是绿灯。最后把矛头指向一个几乎没人关心的对象系统临时表空间 TEMPSPACE1它悄悄涨到了 10G。这件事反直觉的地方在于临时表空间大本身不直接消耗 CPU也不直接抢内存它甚至不在大多数巡检脚本的重点名单里。但它能通过一条非常隐蔽的链路——表空间元数据操作持有内部 LATCH、LATCH 阻塞临时表创建、临时表创建又卡住查询——把并发能力整体压下去。搞懂这条链路等于拿到了一把分析 DB2 内部竞争的钥匙你会明白监控指标为什么看起来都正常也会明白 db2pd、STACK、db2trc 这些工具在什么场景下才真正发挥作用。2. 从 ACTIVE SESSION 异常到 LATCH 等待监控指标怎么读才不跑偏2.1 ActSess 高不等于业务变忙先看会话状态分布ACTIVE SESSIONActSess是 DB2 监控里最直观的指标之一db2top 默认 2 秒刷新一次交互指令d进入数据库概览就能看到。它的定义是当前正在处理 SQL 请求的会话数但关键在于编译中Compiling、执行中UOW Executing、锁等待Lock Waiting、提交中Commit Active、WLM 排队Queued这些状态都计入 ActSess而事务中两条语句之间的空闲态UOW Waiting不计入。所以看到 ActSess 冲高第一步不是扩容而是进 db2top 的l小写 L会话页看状态分布。本次案例里活动会话全部处于 UOW Executing——说明语句确实在跑只是跑得慢而不是卡在锁上或排队上。再结合业务方确认访问量没有增加就能把业务变忙这个分支排除掉锁定为数据库侧性能退化。这里有个容易被忽略的推理OLTP 系统 ActSess 平时个位数、突然频繁几十且业务量不变那基本只有一种解释——单条语句变慢了同样的并发压力下会话堆积。这个结论决定了后面所有排查的方向找慢 SQL而不是找资源瓶颈。2.2 快速排除法哪些指标正常反而说明问题在别处常规排除顺序是 CPU、内存、I/O、锁等待、缓冲池命中率。db2top 界面右下角四个参数很实用AvgPRdTime平均物理读时间、AvgDRdTime平均直接读时间、AvgPWrTime平均物理写时间、AvgDWrTime平均直接写时间它们能快速反映 I/O 是否异常。本次四项都正常HitRatio 正常没有 LOCK WAIT。注意 Deadlocks 显示 218 这类累计值不要误判——它是历史累计不代表当前有死锁在拖性能。SortOvf排序溢出为 10 略高但应用 SQL 本身排序多属于可解释范围不是主因。快速排除法的价值不在于找到问题而在于划掉一大片区域。当所有资源类指标都正常、锁也正常剩下的可能性就收敛到两类SQL 本身写得差或者 DB2 内部存在某种串行化竞争。这两类的排查工具完全不同前者看执行计划后者看 LATCH 和 STACK。2.3 抓慢 SQLMON_CURRENT_SQL 与 MON_GET_PKG_CACHE_STMT 的分工抓当前正在执行的语句用SYSIBMADM.MON_CURRENT_SQL按 ELAPSED_TIME_SEC 倒序能同时看到动态 SQL 和静态 SQLSELECT COORD_MEMBER MEMBER, VARCHAR(SESSION_AUTH_ID,10) USER, VARCHAR(CLIENT_APPLNAME,10) APPNAME, ELAPSED_TIME_SEC ELAPSEDTIME, VARCHAR(ACTIVITY_STATE,10) STATE, TOTAL_CPU_TIME, ROWS_READ, ROWS_RETURNED, QUERY_COST_ESTIMATE, DIRECT_READS, DIRECT_WRITES, VARCHAR(STMT_TEXT,2000) as STMT_TEXT FROM SYSIBMADM.MON_CURRENT_SQL ORDER BY ELAPSED_TIME_SEC DESC这个视图抓的是快照语句没跑完时 ELAPSED_TIME 比完整执行时间短所以往往要执行多次才能撞上高峰时刻。它适合回答现在谁在跑、跑到哪一步。要分析时间花在哪用表函数MON_GET_PKG_CACHE_STMT它返回包缓存中每条语句的累计统计可以算平均值和各项等待占比SELECT MEMBER, NUM_EXECUTIONS, STMT_EXEC_TIME, decimal((TOTAL_ACT_WAIT_TIME / double(STMT_EXEC_TIME)) * 100, 5, 2) as pct_wait, decimal((POOL_READ_TIME / double(STMT_EXEC_TIME)) * 100, 5, 2) as pct_read, decimal((LOCK_WAIT_TIME / double(STMT_EXEC_TIME)) * 100, 5, 2) as pct_lock, decimal((LOG_DISK_WAIT_TIME / double(STMT_EXEC_TIME)) * 100, 5, 2) as pct_log, decimal((TOTAL_SECTION_SORT_TIME / double(STMT_EXEC_TIME)) * 100, 5, 2) as pct_sort, CAST(FLOAT(STMT_EXEC_TIME)/FLOAT(NUM_EXECUTIONS) as decimal(10,3)) as AVG_EXEC_TIME, VARCHAR(STMT_TEXT,500) AS STMT_TEXT FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-2)) WHERE NUM_EXECUTIONS 1 and STMT_EXEC_TIME 0参数-2表示取所有成员上的语句。这里的关键读法是看百分比如果 pct_wait 高达 95% 以上而 pct_read、pct_lock、pct_log、pct_sort 都很低说明时间既没花在读数据、也没花在锁和日志上——那它在等什么答案是把TOTAL_EXTENDED_LATCH_WAIT_TIME加进查询字段后才会浮现的等 LATCH。本次案例正是这个形态5 条 with 开头的语句 pct_wait 超过 95%等待对象是 LATCH另有 5 条调存储过程的语句时间分布看不出来。到这里方向已经从SQL 写得差转向DB2 内部有串行化竞争。3. pool_table_latch一个内部轻量锁如何卡住临时表创建3.1 LATCH 是什么DB2 多线程访问共享内存的协调机制DB2 文档里几乎找不到对 LATCH 的系统介绍但可以这样理解它是保证 DB2 多线程访问内部共享数据结构一致性的轻量锁。举个具体例子内存里有个预取队列PREFETCH QUEUE用户请求的 EDU 往里加任务多个 PREFETCHER EDU 从里取任务两边访问前都得先拿 LATCH拿不到就等。和数据库锁LOCK的区别在于LOCK 保护的是用户数据行、表有事务语义、会进锁列表、能用 db2pd -wlocks 看LATCH 保护的是 DB2 内部内存结构生命周期极短通常微秒到毫秒级不参与事务也不出现在锁监控里。正因为短平时几乎感知不到可一旦某个持有者因为做了一件慢操作而长时间不释放等待者就会成片堆积——这就是本次问题的物理形态。db2pd -latches -rep 2每隔 2 秒输出一次 LATCH 状态。第一次快速排查时没发现异常是因为等待窗口太短、采样没撞上。持续观察后抓到EDU 11665 持有pool_table_latch同时有 23 个 EDU 在等它。这意味着单这一刻至少 24 个活动会话被这一个 LATCH 串起来了——ActSess 冲高到 50 的谜底基本揭开了一半。3.2 从 LATCH 名字到代码位置STACK 收集怎么定位持有者LATCH 名字pool_table_latch字面意思是池表锁但光看名字猜不出它保护什么。真正的突破口是 STACKdb2pd 在收集调用栈的同时会把每个 EDU 当时的 LATCH 和 LOCK 信息一并写进文件所以栈文件里既有谁持有也有谁在等。cd /tmp mkdir stack0531 db2pd -stack all dumpdir/tmp/stack0531 -rep 3 10-stack all收集所有 EDU 的栈dumpdir指定输出目录默认在 DIAGPATH-rep 3 10表示每 3 秒收一次、连收 10 次——因为 LATCH 等待是间歇性的单次采样大概率扑空。收集完用 grep 过滤cd /tmp/stack0531 grep pool_table_latch *.stack.txt输出里会区分Holding Latch type和Waiting on latch type并带上地址、源码行号和文件名。本次抓到持有者在sqlmonss.C第 9032 行等待者在sqlbpool.C第 11384 行。同一个 LATCH 地址0x7eeebae94750把两边对上说明确实是同一把锁的争用。提示生产库繁忙时收集 STACK 会明显消耗资源、影响性能务必在可控窗口内做控制收集次数和间隔。3.3 读栈技巧靠函数名和调用关系还原操作语义栈里全是 C 修饰过的符号看着吓人但函数名的字面意思加上调用链足以还原操作类型。持有者栈里能看到sqlofindn2 SQLB_FIND_BUF::next sqlbMonSMSGetContainerPages sqlbSnapshotTablespace sqlmonssagnt sqlmPdbRequestRouter从sqlbSnapshotTablespace - sqlbMonSMSGetContainerPages这条链可以判断这个 EDU 正在对表空间做快照SNAPSHOT具体是在监控 SMS 表空间时逐个容器统计数据页。等待者栈里则是SQLO_SLATCH_CAS64::getConflict sqlbGetSysTempDatapool sqldInternalCreateTable sqldTableCreateTemp sqlritt2 sqldEvalDataPred sqlsfetcsqldTableCreateTemp - sqldInternalCreateTable - sqlbGetSysTempDatapool这条链说明等待者正在创建内部临时表、需要拿系统临时表空间的数据池再往下sqldEvalDataPred谓词求值、sqlsfetcfetch说明这是一条查询语句在跑中途要用临时表。两边一拼因果链就出来了监控代理定期对表空间做快照快照要遍历 SMS 表空间的容器页遍历期间持有pool_table_latch而查询语句创建临时表也要拿同一把 LATCH于是被堵住。临时表空间越大容器页越多快照遍历越久LATCH 持有时间越长被堵的查询就越多——这就是临时表空间过大传导到SQL 变慢的完整机制。3.4 analyzestack把几十个栈文件聚合成一张竞争图手工 grep 只能看个案几十上百个栈文件需要聚合。DB2 在sqllib/pd目录下带了一个 Perl 脚本analyzestack原本是支持部门的内部工具后来随产品发布但官方文档里查不到脚本头部也声明as-is、无担保、无支持。perl ~/sqllib/pd/analyzestack -d 50 -i /tmp/stack -l-d 50指定对比栈时比较的函数个数决定聚合粒度-i指定栈文件目录或具体文件-l开启 LATCH 分析。运行后生成LatchAnalysis.out和StackAnalysis.out两个文件前者汇总谁持有、谁等待、等待多久的配对关系后者把相同调用栈归并计数让你一眼看出哪条代码路径出现频率最高。对于间歇性竞争这个聚合视角比单次 db2pd 输出有用得多——它把偶发变成了统计显著。4. 临时表空间膨胀TDA 文件为什么不会自己消失4.1 TEMPSPACE1 的 10G 是怎么来的一次瞬时高并发留下的遗产检查表空间时发现一个更基础的问题db2 LIST TABLESPACES SHOW DETAIL本身都要跑好几秒。结果 TEMPSPACE1 总页数 354661、页大小 32768 字节合计约 10G且 Used pages 等于 Total pages。正常情况下系统临时表空间应该很小这个体量明显异常。翻监控历史曲线发现某个时间点 TEMPSPACE1 从 0 直接暴涨到 10G。对应的事件是那一刻活动会话数瞬间冲到 800 多大量 SQL 同时使用临时表空间做排序和临时表。数据库没崩、语句也都执行成功了但事后在容器目录里留下了一堆 TDA 文件ls -al /db2data/.../T0000001/C0000003.TMP -rw------- 1 db2oap dbadm 1048576 SQL04313.MEMBER0000.TDA -rw------- 1 db2oap dbadm 1048576 SQL04314.MEMBER0000.TDA ...这些 TDAtemporary data文件是 SMS 临时表空间的容器扩展产物。SMS 表空间按需向文件系统申请空间高并发时大量临时段同时扩张文件被创建出来并发退去后这些文件在特定条件下不会被立即回收而是留在表空间里继续占位。这是设计行为work as designed不是 bug——但对运维来说它就是一个延迟引爆的雷表空间名义大小不回落后续每次表空间快照都要遍历这些容器页。4.2 为什么大会变成慢容器页遍历与 LATCH 持有时间的正相关把第 3 章的机制和第 4 章的现象接起来链路就完整了环节发生了什么后果瞬时高并发大量临时段扩张生成 TDA 文件TEMPSPACE1 涨到 10G监控代理周期快照遍历 SMS 表空间所有容器页持有 pool_table_latch 时间变长查询创建临时表需要同一把 pool_table_latch等待UOW Executing 堆积ActSess 冲高会话无法推进业务感知为系统变慢关键洞察是临时表空间大小和 LATCH 持有时间成正比。容器越多、页越多sqlbMonSMSGetContainerPages遍历越久LATCH 被占用的窗口越长撞上它的查询就越多。这解释了为什么常规资源指标全正常——瓶颈不在 CPU、内存、磁盘带宽而在一把内部锁的持有时间。4.3 释放方式为什么重启实例没用必须 deactivate/activate找到根因后处理动作本身很简单但有个坑只重启数据库实例无法释放这些临时表空间文件。必须对数据库做 deactivate/activatedb2 deactivate db dbname db2 activate db dbname原因是临时表空间的生命周期绑定在数据库激活状态上deactivate 会让数据库释放所有临时段和容器文件activate 后重新按需创建。执行后确认 TEMPSPACE1 回落到正常大小LIST TABLESPACES SHOW DETAIL恢复秒级返回持续监控一段时间 ActSess 回到个位数性能问题消失。注意deactivate 会断开所有连接必须在维护窗口执行并确认没有长事务在跑。5. 避坑与排查这类问题的常见翻车点5.1 只重启实例不 deactivate临时空间纹丝不动现象按重启大法重启了实例TEMPSPACE1 还是 10G慢 SQL 依旧。 原因实例重启不改变数据库激活状态临时表空间的容器文件不会被回收。 解决执行db2 deactivate db再db2 activate db并确认操作在维护窗口内、无活动连接。5.2 单次 db2pd -latches 采样扑空误判没有 LATCH 问题现象快速排查时跑了一次db2pd -latches结果干净于是排除了 LATCH 方向。 原因LATCH 等待是间歇性的单次采样撞上的概率很低。 解决用db2pd -latches -rep 2持续观察或直接用db2pd -stack all -rep 3 10连续收集栈靠多次采样提高命中率。5.3 把 Deadlocks 累计值当实时指标追错方向现象db2top 里 Deadlocks 显示 218以为死锁在拖性能。 原因这是自实例启动以来的累计计数不代表当前存在死锁。 解决判断实时锁竞争看 LOCK WAIT 和db2pd -wlocks累计值只作趋势参考。5.4 看到 pct_wait 高就去优化 SQL忽略等待对象现象发现慢 SQL 的等待占比 95%直接开始改 SQL、加索引效果甚微。 原因等待的不是读、锁、日志、排序而是 LATCH——SQL 写得再好也绕不开内部串行化。 解决在 MON_GET_PKG_CACHE_STMT 查询里加上TOTAL_EXTENDED_LATCH_WAIT_TIME字段先确认等待对象再决定优化方向。5.5 生产环境长时间收 STACK把慢问题变成宕机风险现象为了抓 LATCH 现场在生产库上高频、长时间收集 STACK业务明显抖动。 原因收集 STACK 需要暂停 EDU 并写大量文件资源开销大。 解决控制-rep的次数和间隔选低峰窗口收集到目标栈文件后立即停止必要时用 db2trc 的 suspend 能力做精准暂停而不是全量高频采样。6. 用 db2trc 暂停实例做验证把猜测变成可复现分析到第 4 章结论已经能自洽但表空间快照持有 pool_table_latch 阻塞临时表创建这条因果链严格说还是从栈推断出来的。要让它变成可验证的事实可以用 db2trc 做一个小实验db2trc 不仅能记录函数调用流还能在命中指定函数入口时挂起实例给你从容取现场的机会。思路是让 trace 在sqlbSMSGetContainerPages入口处 suspend——这个函数正是持有者在栈里出现的位置。一旦快照操作进入该函数实例被暂停此时另一个会话里发起的、需要创建临时表的查询必然卡住恢复 trace 后查询继续。这个暂停—观察—恢复的过程直接证明了快照与临时表创建之间的串行关系。# 终端一连接数据库并开启带 suspend 的 trace db2 connect to mydb db2trc on -crash DB2.SQLB.sqlbSMSGetContainerPages.entry -suspend # 终端二触发一次表空间快照类操作观察其是否被挂起 db2 list tablespaces show detail # 终端三在挂起期间发起需要临时表的查询确认其被阻塞 # 验证完成后恢复并关闭 trace db2trc resume db2trc off db2trc fmt db2trc.bin db2trc.flw参数含义-crash表示按函数入口匹配DB2.SQLB.sqlbSMSGetContainerPages.entry是组件.函数.事件点的写法-suspend让命中时暂停实例。db2trc fmt把二进制 trace 转成可读文本便于核对调用顺序。这个手法在排查 HANG 类问题时同样适用——它把时序竞争变成了可控实验。写到这里我想说的是这类问题的真正教训不在工具而在排查纪律当所有常规指标都正常、而现象又确实存在时不要急着去优化最显眼的那条慢 SQL而要问一句这些语句到底在等什么。我后来的习惯是任何一次性能排查只要 MON_GET_PKG_CACHE_STMT 里出现 pct_wait 异常高但各项具名等待都很低的情况就强制走一遍 LATCH 检查加 STACK 收集哪怕第一次采样是干净的。临时表空间这种平时没人看的对象也进了我的定期巡检清单——不是因为它常出问题而是因为它出问题时所有常规指标都会骗你。希望帮到你。本文还有配套的精品资源点击获取