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、合理安排数据库备份时段,尽量选择业务空闲时间,避免大批量数据写入、更新、删除操作;