db2日志占用过高问题分析脚本

db2日志占用过高问题分析脚本

问题描述

最近数据库经常备份失败，查看诊断日志，发现备份失败时，存在日志已满回滚现象。
数据库备份和日志有什么关系？数据库备份时需要写日志吗？

分析脚本

为了分析上述问题，编写以下监控脚本来分析失败的可能原因：

/* 日志使用率*/
SELECT LOG_UTILIZATION_PERCENT AS "日志使用率" FROM SYSIBMADM.MON_TRANSACTION_LOG_UTILIZATION;

/* 客户端列表*/
SELECT substr(appl_id,1,13) AS "客户端IP", client_nname,primary_auth_id,count(1) AS sl FROM SYSIBMADM.SNAPAPPL_INFO GROUP BY substr(appl_id,1,13),client_nname,primary_auth_id;

/*备份进度*/
SELECT a.SNAPSHOT_TIMESTAMP, a.UTILITY_TYPE, a.UTILITY_DBNAME, a.UTILITY_START_TIME
, CAST(b.PROGRESS_COMPLETED_UNITS *100.0/  b.PROGRESS_TOTAL_UNITS AS NUMERIC(18,1)) AS "备份完成百分比"
, CAST(b.PROGRESS_COMPLETED_UNITS*1.0 /1024/1024/1024 AS NUMERIC(18,1)) AS "完成备份(GB)"
, CAST(b.PROGRESS_TOTAL_UNITS*1.0 /1024/1024/1024 AS NUMERIC(18,1)) AS "总量(GB)"
FROM SYSIBMADM.SNAPUTIL a, SYSIBMADM.SNAPUTIL_PROGRESS b
WHERE a.UTILITY_ID = b.UTILITY_ID;

/*锁等待列表*/
SELECT * FROM SYSIBMADM.SNAPLOCKWAIT;
/*锁等待相关应用列表*/
SELECT * FROM SYSIBMADM.SNAPAPPL_INFO a WHERE EXISTS(SELECT * FROM SYSIBMADM.SNAPLOCKWAIT b WHERE a.AGENT_ID = b.agent_id OR a.AGENT_ID = b.AGENT_ID_HOLDING_LK);

/*查看锁等待相关锁列表 */
SELECT * FROM SYSIBMADM.SNAPLOCK  a WHERE EXISTS(SELECT * FROM SYSIBMADM.SNAPLOCKWAIT b WHERE a.AGENT_ID = b.agent_id OR a.AGENT_ID = b.AGENT_ID_HOLDING_LK);

/*查看锁统计信息*/
SELECT agent_id,LOCK_OBJECT_TYPE, LOCK_MODE, TABNAME, TABSCHEMA, TBSP_NAME, LOCK_ATTRIBUTES,count(1) AS sl 
FROM SYSIBMADM.SNAPLOCK
GROUP BY agent_id,LOCK_OBJECT_TYPE, LOCK_MODE, TABNAME, TABSCHEMA, TBSP_NAME, LOCK_ATTRIBUTES;

监控结果

• 备份任务执行到85%时，发现日志利用率已达到49%；
• 发生锁等待时，是由于备份进程（BACKUP）持有锁:
• AUTORESIZE_LOCK 自动调整大小锁
• AUTOSTORAGE_LOCK 自动存储锁
• TABLESPACE_LOCK 表空间锁

问题分析

综上，数据库备份时，需要锁定表空间，不允许自动扩展存储空间；
如果此时存在大量数据写入、更新、删除操作，将占用大量日志空间；
如果备份时间过长（比如超过5小时），表空间剩余空间被占满，将出现日志空间已满，最终导致备份失败的问题。

解决办法

针对上述问题，可以通过如下方法解决：
1、增加日志容量，建议容量为正式数据库的10%；
2、增加多个表空间，将业务表拆分为分区表，提高备份并行度，缩短备份时间；
3、增加表空间自动扩展容量，减少数据库备份时扩展表空间；
4、合理安排数据库备份时段，尽量选择业务空闲时间，避免大批量数据写入、更新、删除操作；