DB2短时间内产生大量归档日志、且每个日志都很小的原因
来源:互联网 发布:网络攻击追踪溯源 pdf 编辑:程序博客网 时间:2024/04/28 09:12
问题现象:
为什么我的DB2归档日志目录里,短时间内产生了大量的归档日志,而且每个归档日志都很小,远达不到设定的日志大小?
原因分析:
如果有应用使用过活动日志,当数据库取消激活的时候,就会对日志进行归档。如果有大量的短连接,那么就会频繁地归档。这里的短连接是指连接上数据库,只使用了少量的日志,然后就断开。
数据库激活的时候会分配相应的资源,比如缓冲池等,有显式激活和隐式激活两种:显式激活即使用 "db2 activate db <dbname>";隐式激活就是第一个连接连到数据库时,就会隐式地激活数据库。数据库是显式激活还是隐式激活的,是可以通过db2diag.log看出来的:
显式地激活数据库:
$ db2 activate db sample
DB20000I The ACTIVATE DATABASE command completed successfully.
db2diag.log中FristConnect会显示ACTIVDATED: YES,表示是显式激活的
2017-07-06-17.51.38.577456-420 E3547E503 LEVEL: Event
PID : 1860 TID : 139692209071872 PROC : db2sysc 0
INSTANCE: inst105 NODE : 000 DB : SAMPLE
APPHDL : 0-7 APPID: *LOCAL.inst105.170707005136
AUTHID : INST105 HOSTNAME: db2a
EDUID : 18 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, base sys utilities, sqeLocalDatabase::FirstConnect, probe:1000
START : DATABASE: SAMPLE : ACTIVATED: YES
如果数据库是显式激活的,必须显式地取消激活,否则数据库一直外于active状态:
$ db2 deactivate db sample
DB20000I The DEACTIVATE DATABASE command completed successfully.
2017-07-06-17.53.36.693974-420 E7927E517 LEVEL: Event
PID : 1860 TID : 139692104214272 PROC : db2sysc 0
INSTANCE: inst105 NODE : 000 DB : SAMPLE
APPHDL : 0-22 APPID: *LOCAL.inst105.170707005336
AUTHID : INST105 HOSTNAME: db2a
EDUID : 43 EDUNAME: db2agent (idle) 0
FUNCTION: DB2 UDB, base sys utilities, sqeLocalDatabase::FreeResourcesOnDBShutdown, probe:15579
STOP : DATABASE: SAMPLE : DEACTIVATED: YES
隐式地激活数据库:
$ db2 connect to sample
Database Connection Information
Database server = DB2/LINUXX8664 10.5.8
SQL authorization ID = INST105
Local database alias = SAMPLE
db2diag.log中FristConnect会显示ACTIVDATED: NO,表示是隐式激活的:
2017-07-06-17.53.57.490708-420 E9864E502 LEVEL: Event
PID : 1860 TID : 139692104214272 PROC : db2sysc 0
INSTANCE: inst105 NODE : 000 DB : SAMPLE
APPHDL : 0-23 APPID: *LOCAL.inst105.170707005357
AUTHID : INST105 HOSTNAME: db2a
EDUID : 43 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, base sys utilities, sqeLocalDatabase::FirstConnect, probe:1000
START : DATABASE: SAMPLE : ACTIVATED: NO
最后一个连接断开的时候,(如果是隐式激活的)数据库会取消激活
$ db2 terminate
DB20000I The TERMINATE command completed successfully.
2017-07-06-17.54.51.948604-420 E11499E516 LEVEL: Event
PID : 1860 TID : 139692104214272 PROC : db2sysc 0
INSTANCE: inst105 NODE : 000 DB : SAMPLE
APPHDL : 0-23 APPID: *LOCAL.inst105.170707005357
AUTHID : INST105 HOSTNAME: db2a
EDUID : 43 EDUNAME: db2agent (idle) 0
FUNCTION: DB2 UDB, base sys utilities, sqeLocalDatabase::FreeResourcesOnDBShutdown, probe:15579
STOP : DATABASE: SAMPLE : DEACTIVATED: NO
问题重现:
下面使用这个脚本来模拟大量的“短连接”$ cat connect.sh
#!/bin/bash
for i in {1..100}
do
db2 "connect to sample"
db2 "insert into t1 values($i)"
db2 connect reset
done
执行完这个脚本之后,查看归档日志目录,发现有100个12K的日志
$ ls -lh
total 1.2M
-rw-r----- 1 inst105 db2iadm 20K Jul 6 18:03 S0000000.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000001.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000002.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000003.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000004.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000005.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000006.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000007.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000008.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000009.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000010.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000011.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000012.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000013.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000014.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000015.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000016.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000017.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000018.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000019.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000020.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000021.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000022.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000023.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000024.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000025.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000026.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000027.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000028.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000029.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000030.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000031.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000032.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000033.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000034.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000035.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000036.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000037.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000038.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000039.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000040.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000041.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000042.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000043.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000044.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000045.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000046.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000047.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000048.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000049.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000050.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000051.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000052.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000053.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000054.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000055.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000056.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000057.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000058.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000059.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000060.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000061.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000062.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000063.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000064.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000065.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000066.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000067.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000068.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000069.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000070.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000071.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000072.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000073.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000074.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000075.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000076.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000077.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000078.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000079.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000080.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000081.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000082.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000083.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000084.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000085.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000086.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000087.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000088.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000089.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000090.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000091.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000092.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000093.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000094.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000095.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000096.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000097.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000098.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000099.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000100.LOG
但实际上配置的LOGFILSIZ是4000KB
$ db2 get db cfg for sample | grep -i logfilsiz
Log file size (4KB) (LOGFILSIZ) = 1000
至此,重现了问题,如果我们连上数据库,进行一些“正常”的操作,产生了一些日志,查看大小,确实是4.0M
-rw-r----- 1 inst105 db2iadm 4.0M Jul 6 18:13 S0000101.LOG
-rw-r----- 1 inst105 db2iadm 4.0M Jul 6 18:13 S0000102.LOG
-rw-r----- 1 inst105 db2iadm 4.0M Jul 6 18:13 S0000103.LOG
-rw-r----- 1 inst105 db2iadm 4.0M Jul 6 18:13 S0000104.LOG
-rw-r----- 1 inst105 db2iadm 4.0M Jul 6 18:13 S0000105.LOG
-rw-r----- 1 inst105 db2iadm 4.0M Jul 6 18:13 S0000106.LOG
-rw-r----- 1 inst105 db2iadm 4.0M Jul 6 18:13 S0000107.LOG
解决方法:
显式地激活数据库,这样最后一个连接断开的时候,就不会取消激活数据库,也就不会归档。
阅读全文
0 0
- DB2短时间内产生大量归档日志、且每个日志都很小的原因
- 清理DB2的归档日志
- db2归档日志
- db2 事务日志和归档的管理
- db2 开启归档日志的功能
- 设置产生的归档日志保存位置
- 如何关闭hibernate产生的大量日志
- 如何关闭hibernate产生的大量日志
- oracle 日志切换归档模式查看归档文件的产生
- shrink space产生大量日志
- DB2数据库归档日志的管理方案简介(转)
- DB2备份集与归档日志的保留或删除
- 某个联机日志不能归档了,导致后面的联机日志都不能归档
- 物化视图comlete刷新会产生大量的日志
- 小机db2 日志修改归档模式
- DB2日志基本概念及归档日志模式设置
- 每个男人的空间里都应该有这篇日志
- 每个男人的空间里都应该有这篇日志
- sensor----HAL分析
- java多线程处理
- Doing Homework again
- ERROR 1062 (23000) at line 1: Duplicate entry '1332883220' for key 'group_key'
- 一种星期计算方法和由此开始的有趣事情
- DB2短时间内产生大量归档日志、且每个日志都很小的原因
- c语言strtok用法
- for循环回顾,随机输出一个奇数得到菱形(双for嵌套得到菱形方法的延伸)
- 如何提高英雄联盟FPS
- 2017.7.17
- springmvc Controller请求中参数
- c++primer5第二章
- Eclipse实现hibernate反向工程:从数据库逆向生成实体类和hbm文件
- CSS选择器2