When running bufferpool snaphots in an environment that requires
lots of connects/reconnects it can cause slowdown connections
due to a latch contention.
In v9.1 this is the sequence of steps for the snapshot:
snapshot
>> collect bp data
>> do application traversal
>> do agent traversal
/*Traverse internal hidden bufferpools to complete accounting
*/
>> collect bp data
>> do application traversal
>> do agent traversal
end snapshot
In v8, we don't read the hidden bufferpools, hence in V9.1 we do
more work that could result in latch contention.
This is an example of an agent waiting on a dblatch during
connect/reconnect (t7502.000) while the latch is being hold by
the snapshot.
t7816.000
timestamp: 2008-10-20-22.32.28.477377
Holding Latch type: (SQLO_LT_SQLE_DBCB__dblatch) - Address:
(200274248), Line: 352, File: sqlmutil.C
__1cTsqm_collect_bp_data6FpnIsqledbcb_pknJSQdDLB_BPCB_kipnQsqlm_
bp_int_info__i_(0x1b0,0x4e0,0x5d0,0xffffffff7e44ad27,0xffffffff7
e44ad32,0x3998) + 0x760
__1cZsqm_gen_bufferpool_stream6FpknQsqm_agent_entity_kipnIsqledb
cb_pkckIpnTsqm_snapshot_buffer_pnQsqlm_header_
info_6pnIsqlo_gmt_C_i_(0xffffffff6bc14289,0x16,0x200270080,0xfff
fffff7f091ce0,0x8,0xffffffff7ffe1f10) + 0x150
__1cMsqlmonssagnt6FInNsqm_entity_id_pnGsqlmai_IpvpnOsqlm_collect
ed_HHpnFsqlca__v_(0x36,0xffffffff7ffe1f10,0x203ad1200,0xffffffff
6bc0f4bc,0x0,0x259ee0150) + 0xd3c
__1cPsqlmonssbackend6FpnKsqle_db2ra__I_(0x0,0xffffffff7311b3c0,0
x0,0x3d,0x0,0x0) + 0x106c
:
timestamp: 2008-10-20-22.40.27.628794
Holding Latch type: (SQLO_LT_SQLE_DBCB__dblatch) - Address:
(200274248), Line: 352, File: sqlmutil.C
__1cTsqm_collect_bp_data6FpnIsqledbcb_pknJSQdDLB_BPCB_kipnQsqlm_
bp_int_info__i_(0x1b0,0x4e0,0x5d0,0xffffffff7e44ad27,0xffffffff7
e44ad32,0x3998) + 0x760
__1cZsqm_gen_bufferpool_stream6FpknQsqm_agent_entity_kipnIsqledb
cb_pkckIpnTsqm_snapshot_buffer_pnQsqlm_header_
info_6pnIsqlo_gmt_C_i_(0xffffffff6bc191df,0x2c,0x200270080,0xfff
fffff7f091ce0,0x8,0xffffffff7ffe1f10) + 0x150
__1cMsqlmonssagnt6FInNsqm_entity_id_pnGsqlmai_IpvpnOsqlm_collect
ed_HHpnFsqlca__v_(0x36,0xffffffff7ffe1f10,0x20
3ad1200,0xffffffff6bc0f4bc,0x0,0x259ee0200) + 0xd3c
:
timestamp: 2008-10-20-22.51.03.129686
Holding Latch type: (SQLO_LT_SQLE_DBCB__dblatch) - Address:
(200274248), Line: 352, File: sqlmutil.C
#### Object: /usr/lib/sparcv9/libc.so.1
__systemcall(0xffffffff7ffe05b0,0x34,0x2,0x900000000,0x0,0x0) +
0x34
shmdt(0x900000000,0x0,0x0,0x0,0x0,0x0) + 0x10
##### Object:
/apps/opt/db2udb/product/ibm/db2/V9.1/lib64/libdb2e.so.1
sqlodshr(0x900000000,0x1,0x820f0000,0x820f0002,0x0,0x0) + 0x79c
sqlocshr2(0x204cb4d98,0x4,0x3,0x13800,0xffffffff7e849530,0x139a0
) + 0x50c
sqlodshr(0x204cb4d98,0xc,0x1000000,0x82750,0xc000,0x30) + 0x140
__1cZsqleGetNextAppForDatabase6FpnIsqledbcb_ppnSsqle_master_app_
cb__i_(0xffffffff7e849530,0x1,0x204c97f00,0x39
90,0x39ac,0x39a2) + 0x844
__1cWsqm_collect_ts_bp_info6FpnIsqledbcb_kIpnQsqlm_bp_int_info__
i_(0x144,0x4e0,0x6bb,0x6ca,0xffffffff7e44ad69,
0x3998) + 0xa24
:
t7502.000
timestamp: 2008-10-20-22.40.13.449063
Waiting on latch type: (SQLO_LT_SQLE_DBCB__dblatch) - Address:
(200274248), Line: 4842, File: sqleserl.C
_systemcall(0xffffffff7ffe54e0,0x35,0x2,0x6e090d,0xffffffff7ffe5
660,0x1) + 0x34
semop(0x6e090d,0xffffffff7ffe5660,0x1,0x0,0x2,0x0) + 0x18
##### Object:
/apps/opt/db2udb/product/ibm/db2/V9.1/lib64/libdb2e.so.1
__1cRSQdDLO_SLATCH_CAS64SgetConflictComplex6MkL_v_(0x1,0x2a0a28,
0x200274248,0x1ffff,0x2300453b8,0x20000) + 0x4
6c
__1cSsqloxltc_new_track6FkpnOsqlo_xsemlatch_kpkckLknOSQdDLO_LT_V
ALUES__i_(0xffffffff7f0abe80,0xffffffff7e41b1f
c,0x12ea,0x1,0xffff7fff,0x1) + 0x9c
__1cIsqleserl6FpnHsqlebwa_pnNsqle_agent_cb_rccpnIsqlo_gmt__i_(0x
1000,0x202c8e900,0x200270080,0x3800,0xffffffff
7f0abe80,0xffffffff7e849530) + 0x7f0
__1cIsqlesrsu6FpnHsqlebwa_pnNsqle_agent_cb_ccpnFsqlca_pc_i_(0x0,
0x202c8e900,0xffffffff7f091ca0,0x0,0xffffffff7
311b3c0,0x0) + 0x450
:
timestamp: 2008-10-20-22.41.13.462060
Waiting on latch type: (SQLO_LT_SQLE_DBCB__dblatch) - Address:
(200274248), Line: 1221, File: sqlescln.C
__systemcall(0xffffffff7ffe6710,0x35,0x2,0x6e090d,0xffffffff7ffe
6890,0x1) + 0x34
semop(0x6e090d,0xffffffff7ffe6890,0x1,0x0,0x2,0x0) + 0x18
##### Object:
/apps/opt/db2udb/product/ibm/db2/V9.1/lib64/libdb2e.so.1
__1cRSQdDLO_SLATCH_CAS64SgetConflictComplex6MkL_v_(0x1,0x2a0a28,
0x200274248,0x1ffff,0x2300453b8,0x20000) + 0x46c
__1cRsqleTermDbConnect6FpnNsqle_agent_cb_pnIsqledbcb_pnFsqlca_pn
VsqleTermConnectStatus__i_(0x202c8e900,0x3998,0x9033b0080,0x39ac
,0x200270080,0xffffffff7e849530) + 0x754
__1cIsqlesrsp6FpnUsqle_agent_privatecb_CpnFsqlca_i_i_(0xffffffff
7311b1e0,0x9033b1638,0xffffffff7f2034f0,0x202c
8e900,0xffffffff7f0801f8,0xffffffff7e849530) + 0x16a8
__1cLsqlesrspWrp6FpnOdb2UCinterface__i_(0xffffffff7311d5a0,0x19f
0,0x2,0xffffffff7311d320,0xffffffff7f091e18,0x
ffffffff7f1e33d8) + 0x1c8
sqleUCagentConnectReset(0xffffffff7311d320,0x0,0x0,0xffffffff731
10088,0x0,0xffffffff7f091e18) + 0xe8
__1cRsqljsDrdaAsDriver6FpnRsqlcc_init_struct__i_(0x2000000,0xfff
fffff7e849530,0x2035ad980,0xffffffff7311d320,0
x202c8e900,0xffffffff7f2034f0) + 0x634
__1cMsqleRunAgent6FpcI_v_(0x2,0x202c8e900,0x20,0xffffffff7f2034f
0,0x52b,0xffffffff7e41d046) + 0x1db0
__1cNsqloCreateEDU6FpFpcI_v0LpnNSQdDLO_EDU_INFO_pi_i_(0xffffffff
7bbc7a48,0xffffffff7fff85f8,0x8,0xffffffff7f20
34f0,0xffffffff7fff85b0,0xffffffff7fff85b8) + 0x228
sqloSpawnEDU(0xffffffff7bbc7a48,0xffffffff7fff85f8,0x8,0xfffffff
f7fff85b8,0xffffffff7fff85b0,0x28c8db0) + 0x1d0
:
t13216.000
timestamp: 2008-10-20-22.40.26.288902
Waiting on latch type: (SQLO_LT_SQLE_DBCB__dblatch) - Address:
(200274248), Line: 352, File: sqlmutil.C
__systemcall(0xffffffff7ffe0900,0x35,0x2,0x7a0e70,0xffffffff7ffe
0a80,0x1) + 0x34
semop(0x7a0e70,0xffffffff7ffe0a80,0x1,0x0,0x3,0xffffffffffffa6a0
) + 0x18
##### Object:
/apps/opt/db2udb/product/ibm/db2/V9.1/lib64/libdb2e.so.1
__1cRSQdDLO_SLATCH_CAS64SgetConflictComplex6MkL_v_(0x1,0x288a28,
0x200274248,0x1ffff,0x230062bb0,0x20000) + 0x46c
__1cRsqm_get_next_dbcb6FbppnNdbcb_use_list_ppnIsqledbcb_b_v_(0x1
,0x200270080,0x10000,0x1e22,0xffffffff7e849530
,0xf) + 0x4c0
__1cMsqlmonssagnt6FInNsqm_entity_id_pnGsqlmai_IpvpnOsqlm_collect
ed_HHpnFsqlca__v_(0x1,0xffffffff7ffe1f10,0x204
833080,0x0,0x0,0x0) + 0x890
:
timestamp: 2008-10-20-22.41.14.066082
Holding Latch type: (SQLO_LT_SQLE_DBCB__dblatch) - Address:
(200274248), Line: 352, File: sqlmutil.C
__1cTinit_sqlm_collected6FpknQsqm_agent_entity_kIrknIsqlo_gmt_pn
Osqlm_collected_pnTsqm_snapshot_buffer_b_i_(0x
201d30dc0,0x9035c1648,0x4,0xffffffff7ffe1ec4,0xffffffff71102ed0,
0x0) + 0x7648
__1cTinit_sqlm_collected6FpknQsqm_agent_entity_kIrknIsqlo_gmt_pn
Osqlm_collected_pnTsqm_snapshot_buffer_b_i_(0x6c,0x3800,0x1,0x39
a2,0x1,0xffffffff7e448646) + 0x2118
__1cMsqlmonssagnt6FInNsqm_entity_id_pnGsqlmai_IpvpnOsqlm_collect
ed_HHpnFsqlca__v_(0x0,0xffffffff7ffe1f10,0x204
833080,0x0,0x0,0x19d8) + 0xb38
__1cPsqlmonssbackend6FpnKsqle_db2ra__I_(0x0,0xffffffff7311b3c0,0
x0,0x4,0x0,0x0) + 0x106c
Holding Latch type: (SQLO_LT_SQLE_APP_GROUP_CB__appGroupLatch) -
Address: (204cb4dc8), Line: 460, File: sqledb
ap.C
t7703.000
timestamp: 2008-10-20-22.50.55.000909
Waiting on latch type: (SQLO_LT_SQLE_DBCB__dblatch) - Address:
(200274248), Line: 352, File: sqlmutil.C
<StackTrace>
##### Object: /usr/lib/sparcv9/libc.so.1
__systemcall(0xffffffff7ffe0900,0x35,0x2,0xc09c6,0xffffffff7ffe0
a80,0x1) + 0x34
semop(0xc09c6,0xffffffff7ffe0a80,0x1,0x0,0x3,0xffffffffffffa916)
+ 0x18
##### Object:
/apps/opt/db2udb/product/ibm/db2/V9.1/lib64/libdb2e.so.1
__1cRSQdDLO_SLATCH_CAS64SgetConflictComplex6MkL_v_(0x1,0x288a28,
0x200274248,0x1ffff,0x230049350,0x20000) + 0x46c
__1cRsqm_get_next_dbcb6FbppnNdbcb_use_list_ppnIsqledbcb_b_v_(0x1
,0x200270080,0x10000,0x1e22,0xffffffff7e849530
,0xf) + 0x4c0
__1cMsqlmonssagnt6FInNsqm_entity_id_pnGsqlmai_IpvpnOsqlm_collect
ed_HHpnFsqlca__v_(0x1,0xffffffff7ffe1f10,0x2035d9900,0x0,0x0,0x0
) + 0x890
:
t7428.000
timestamp: 2008-10-20-22.50.45.679305
Waiting on latch type: (SQLO_LT_SQLE_DBCB__dblatch) - Address:
(200274248), Line: 352, File: sqlmutil.C
__systemcall(0xffffffff7ffe6460,0x35,0x2,0x5b08ce,0xffffffff7ffe
65e0,0x1) + 0x34
semop(0x5b08ce,0xffffffff7ffe65e0,0x1,0x0,0x2,0x0) + 0x18
##### Object:
/apps/opt/db2udb/product/ibm/db2/V9.1/lib64/libdb2e.so.1
__1cRSQdDLO_SLATCH_CAS64SgetConflictComplex6MkL_v_(0x1,0x2a0a28,
0x200274248,0x1ffff,0x230043e10,0x20000) + 0x46c
__1cRsqm_get_next_dbcb6FbppnNdbcb_use_list_ppnIsqledbcb_b_v_(0x1
,0x200270080,0x10000,0x1e22,0xffffffff7e849530
,0xf) + 0x4c0
__1cQsqm_agent_entityRturn_off_switches6MpI_v_(0x204f75d00,0xfff
fffff7ffe6b14,0x204f75d08,0x7b,0xffffffff7ffe6
b18,0x7b) + 0x28c |
