银行
事务日志问题
环境介绍：AIX5.3+DB2 (DPF 32nodes) 每个节点有46个主日志文件，大小是1G，采用循环日志。相关数据：1.db2top Application Delta ...显示全部
环境介绍：AIX5.3+DB2 (DPF 32nodes)
每个节点有46个主日志文件，大小是1G，采用循环日志。
相关数据：
1.db2top
Application Delta Sess Assoc. Paral. Lockwait Locks Sorts Log Delta Fetch
Handle(Stat) TQr+w/s Memory Agents Degree (sec) Held (sec) Used RowsSelect/s Count(Stmt)
------------ -------------- -------- ------ ------ -------- ---------- -------- ------- -------------- --------------
1115773(*) 0 19.4M 33 1 47 98 3917 10.6G 0 0
可以看到db2top里显示这个事务1115773占用 的事务日志是10.6G 。
2.db2pd
hostname:/path#db2pd -d dbname -tran|grep -i 1115773
0x078000017E4C7480 1115773 [017-01661] 168 5 WRITE 0x00000000 0x00000000 0xE577182F438A 0xE57EFB4E6CE3 48341878 306393088 0x00030D0841E5 1 n/a
hostname:/path#db2pd -d dbname -log|more
Database Partition 1 -- Database dbname -- Active -- Up 39 days 18:25:06 -- Date 2013-02-19 12:47:47
Logs:
Current Log Number 41
Pages Written 243133
Method 1 Archive Status n/a
Method 1 Next Log to Archive n/a
Method 1 First Failure n/a
Method 2 Archive Status n/a
Method 2 Next Log to Archive n/a
Method 2 First Failure n/a
Address StartLSN State Size Pages Filename
0x0780000050008F78 0xE57701388000 0x00000000 262144 262144 S0000010.LOG
0x078000005001AF58 0xE57741388000 0x00000000 262144 262144 S0000011.LOG
0x078000005001AFF8 0xE57781388000 0x00000000 262144 262144 S0000012.LOG
0x078000005001B098 0xE577C1388000 0x00000000 262144 262144 S0000013.LOG
0x078000005001B138 0xE57801388000 0x00000000 262144 262144 S0000014.LOG
0x078000005001B1D8 0xE57841388000 0x00000000 262144 262144 S0000015.LOG
0x078000005001B278 0xE57881388000 0x00000000 262144 262144 S0000016.LOG
0x078000005001B318 0xE578C1388000 0x00000000 262144 262144 S0000017.LOG
0x078000005001B3B8 0xE57901388000 0x00000000 262144 262144 S0000018.LOG
0x078000005001B458 0xE57941388000 0x00000000 262144 262144 S0000019.LOG
0x078000005001B4F8 0xE57981388000 0x00000000 262144 262144 S0000020.LOG
0x078000005001B598 0xE579C1388000 0x00000000 262144 262144 S0000021.LOG
0x078000005001B638 0xE57A01388000 0x00000000 262144 262144 S0000022.LOG
0x078000005001B6D8 0xE57A41388000 0x00000000 262144 262144 S0000023.LOG
0x078000005001B778 0xE57A81388000 0x00000000 262144 262144 S0000024.LOG
0x078000005001B818 0xE57AC1388000 0x00000000 262144 262144 S0000025.LOG
0x078000005001B8B8 0xE57B01388000 0x00000000 262144 262144 S0000026.LOG
0x078000005001B958 0xE57B41388000 0x00000000 262144 262144 S0000027.LOG
0x078000005001B9F8 0xE57B81388000 0x00000000 262144 262144 S0000028.LOG
0x078000005001BA98 0xE57BC1388000 0x00000000 262144 262144 S0000029.LOG
0x078000005001BB38 0xE57C01388000 0x00000000 262144 262144 S0000030.LOG
0x078000005001BBD8 0xE57C41388000 0x00000000 262144 262144 S0000031.LOG
0x078000005001BC78 0xE57C81388000 0x00000000 262144 262144 S0000032.LOG
0x078000005001BD18 0xE57CC1388000 0x00000000 262144 262144 S0000033.LOG
0x078000005001BDB8 0xE57D01388000 0x00000000 262144 262144 S0000034.LOG
0x078000005001BE58 0xE57D41388000 0x00000000 262144 262144 S0000035.LOG
0x078000005001BEF8 0xE57D81388000 0x00000000 262144 262144 S0000036.LOG
0x078000005001BF98 0xE57DC1388000 0x00000000 262144 262144 S0000037.LOG
0x07800000500095F8 0xE57E01388000 0x00000000 262144 262144 S0000038.LOG
0x0780000050009698 0xE57E41388000 0x00000000 262144 262144 S0000039.LOG
0x0780000050009738 0xE57E81388000 0x00000000 262144 262144 S0000040.LOG
0x07800000500097D8 0xE57EC1388000 0x00000000 262144 262144 S0000041.LOG
0x0780000050009878 0xE57F01388000 0x00000000 262144 262144 S0000042.LOG
0x0780000050009918 0xE57F41388000 0x00000000 262144 262144 S0000043.LOG
0x07800000500099B8 0xE57F81388000 0x00000000 262144 262144 S0000044.LOG
0x0780000050009A58 0xE57FC1388000 0x00000000 262144 262144 S0000045.LOG
0x078000017A1BFF98 0xE58001388000 0x00000000 262144 262144 S0000000.LOG
0x07800000500089D8 0xE58041388000 0x00000000 262144 262144 S0000001.LOG
0x0780000050008A78 0xE58081388000 0x00000000 262144 262144 S0000002.LOG
0x0780000050008B18 0xE580C1388000 0x00000000 262144 262144 S0000003.LOG
0x0780000050008BB8 0xE58101388000 0x00000000 262144 262144 S0000004.LOG
0x0780000050008C58 0xE58141388000 0x00000000 262144 262144 S0000005.LOG
0x0780000050008CF8 0xE58181388000 0x00000000 262144 262144 S0000006.LOG
0x0780000050008D98 0xE581C1388000 0x00000000 262144 262144 S0000007.LOG
0x0780000050008E38 0xE58201388000 0x00000000 262144 262144 S0000008.LOG
0x0780000050008ED8 0xE58241388000 0x00000000 262144 262144 S0000009.LOG
通过db2pd工具可以看到这个事物占用的事务日志是从S0000010.LOG到SQL0000041.LOG。
3.这个事物在运行发现系统的事务日志使用率在80%左右，db2top看到这个事务的大小才10G左右，决定将这个事务force掉。
force后，回滚了很久。。13点开始回滚，到14点26回滚结束，但是第17节点在这个过程中并没有回滚。14：26后第17节点才开始回滚，到现在
还没有回滚完。
4.有下面的疑问：
1.10G左右的事务，平时回滚时间很短的，基本10分钟这个量级。而这个却回滚了2个半小时才快完,和这个事物跨越的事务日志文件数量多少有关么？
2.为什么回滚的时候不是32个节点并发的进行，一开始其他节点在回滚时，17节点上显示是WAITING. 第17节点要等到其他节点滚完才开始回滚？收起
每个节点有46个主日志文件，大小是1G，采用循环日志。
相关数据：
1.db2top
Application Delta Sess Assoc. Paral. Lockwait Locks Sorts Log Delta Fetch
Handle(Stat) TQr+w/s Memory Agents Degree (sec) Held (sec) Used RowsSelect/s Count(Stmt)
------------ -------------- -------- ------ ------ -------- ---------- -------- ------- -------------- --------------
1115773(*) 0 19.4M 33 1 47 98 3917 10.6G 0 0
可以看到db2top里显示这个事务1115773占用 的事务日志是10.6G 。
2.db2pd
hostname:/path#db2pd -d dbname -tran|grep -i 1115773
0x078000017E4C7480 1115773 [017-01661] 168 5 WRITE 0x00000000 0x00000000 0xE577182F438A 0xE57EFB4E6CE3 48341878 306393088 0x00030D0841E5 1 n/a
hostname:/path#db2pd -d dbname -log|more
Database Partition 1 -- Database dbname -- Active -- Up 39 days 18:25:06 -- Date 2013-02-19 12:47:47
Logs:
Current Log Number 41
Pages Written 243133
Method 1 Archive Status n/a
Method 1 Next Log to Archive n/a
Method 1 First Failure n/a
Method 2 Archive Status n/a
Method 2 Next Log to Archive n/a
Method 2 First Failure n/a
Address StartLSN State Size Pages Filename
0x0780000050008F78 0xE57701388000 0x00000000 262144 262144 S0000010.LOG
0x078000005001AF58 0xE57741388000 0x00000000 262144 262144 S0000011.LOG
0x078000005001AFF8 0xE57781388000 0x00000000 262144 262144 S0000012.LOG
0x078000005001B098 0xE577C1388000 0x00000000 262144 262144 S0000013.LOG
0x078000005001B138 0xE57801388000 0x00000000 262144 262144 S0000014.LOG
0x078000005001B1D8 0xE57841388000 0x00000000 262144 262144 S0000015.LOG
0x078000005001B278 0xE57881388000 0x00000000 262144 262144 S0000016.LOG
0x078000005001B318 0xE578C1388000 0x00000000 262144 262144 S0000017.LOG
0x078000005001B3B8 0xE57901388000 0x00000000 262144 262144 S0000018.LOG
0x078000005001B458 0xE57941388000 0x00000000 262144 262144 S0000019.LOG
0x078000005001B4F8 0xE57981388000 0x00000000 262144 262144 S0000020.LOG
0x078000005001B598 0xE579C1388000 0x00000000 262144 262144 S0000021.LOG
0x078000005001B638 0xE57A01388000 0x00000000 262144 262144 S0000022.LOG
0x078000005001B6D8 0xE57A41388000 0x00000000 262144 262144 S0000023.LOG
0x078000005001B778 0xE57A81388000 0x00000000 262144 262144 S0000024.LOG
0x078000005001B818 0xE57AC1388000 0x00000000 262144 262144 S0000025.LOG
0x078000005001B8B8 0xE57B01388000 0x00000000 262144 262144 S0000026.LOG
0x078000005001B958 0xE57B41388000 0x00000000 262144 262144 S0000027.LOG
0x078000005001B9F8 0xE57B81388000 0x00000000 262144 262144 S0000028.LOG
0x078000005001BA98 0xE57BC1388000 0x00000000 262144 262144 S0000029.LOG
0x078000005001BB38 0xE57C01388000 0x00000000 262144 262144 S0000030.LOG
0x078000005001BBD8 0xE57C41388000 0x00000000 262144 262144 S0000031.LOG
0x078000005001BC78 0xE57C81388000 0x00000000 262144 262144 S0000032.LOG
0x078000005001BD18 0xE57CC1388000 0x00000000 262144 262144 S0000033.LOG
0x078000005001BDB8 0xE57D01388000 0x00000000 262144 262144 S0000034.LOG
0x078000005001BE58 0xE57D41388000 0x00000000 262144 262144 S0000035.LOG
0x078000005001BEF8 0xE57D81388000 0x00000000 262144 262144 S0000036.LOG
0x078000005001BF98 0xE57DC1388000 0x00000000 262144 262144 S0000037.LOG
0x07800000500095F8 0xE57E01388000 0x00000000 262144 262144 S0000038.LOG
0x0780000050009698 0xE57E41388000 0x00000000 262144 262144 S0000039.LOG
0x0780000050009738 0xE57E81388000 0x00000000 262144 262144 S0000040.LOG
0x07800000500097D8 0xE57EC1388000 0x00000000 262144 262144 S0000041.LOG
0x0780000050009878 0xE57F01388000 0x00000000 262144 262144 S0000042.LOG
0x0780000050009918 0xE57F41388000 0x00000000 262144 262144 S0000043.LOG
0x07800000500099B8 0xE57F81388000 0x00000000 262144 262144 S0000044.LOG
0x0780000050009A58 0xE57FC1388000 0x00000000 262144 262144 S0000045.LOG
0x078000017A1BFF98 0xE58001388000 0x00000000 262144 262144 S0000000.LOG
0x07800000500089D8 0xE58041388000 0x00000000 262144 262144 S0000001.LOG
0x0780000050008A78 0xE58081388000 0x00000000 262144 262144 S0000002.LOG
0x0780000050008B18 0xE580C1388000 0x00000000 262144 262144 S0000003.LOG
0x0780000050008BB8 0xE58101388000 0x00000000 262144 262144 S0000004.LOG
0x0780000050008C58 0xE58141388000 0x00000000 262144 262144 S0000005.LOG
0x0780000050008CF8 0xE58181388000 0x00000000 262144 262144 S0000006.LOG
0x0780000050008D98 0xE581C1388000 0x00000000 262144 262144 S0000007.LOG
0x0780000050008E38 0xE58201388000 0x00000000 262144 262144 S0000008.LOG
0x0780000050008ED8 0xE58241388000 0x00000000 262144 262144 S0000009.LOG
通过db2pd工具可以看到这个事物占用的事务日志是从S0000010.LOG到SQL0000041.LOG。
3.这个事物在运行发现系统的事务日志使用率在80%左右，db2top看到这个事务的大小才10G左右，决定将这个事务force掉。
force后，回滚了很久。。13点开始回滚，到14点26回滚结束，但是第17节点在这个过程中并没有回滚。14：26后第17节点才开始回滚，到现在
还没有回滚完。
4.有下面的疑问：
1.10G左右的事务，平时回滚时间很短的，基本10分钟这个量级。而这个却回滚了2个半小时才快完,和这个事物跨越的事务日志文件数量多少有关么？
2.为什么回滚的时候不是32个节点并发的进行，一开始其他节点在回滚时，17节点上显示是WAITING. 第17节点要等到其他节点滚完才开始回滚？收起