Kbase P121187: Client sessions pausing logging into database - LKT being heavily hit
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
01/05/2009 |
|
Status: Verified
SYMPTOM(s):
Performance is slow when users are attempting to connect to the database.
Local users are hanging for a period of time when accessing the database.
Users are finally then able to connect and work without a problem.
Once user connects, if they disconnect and attempt a reconnect, they get in quicker.
Kill -8 on hung user process connecting to the database shows we are napping trying to acquire a latch.
Stack trace from _progres reads:
_select
latnap
latSleep
latXlock
Lock Table is set to 200,000
Promon shows very few writes occurring at the time of the pause.
Promon activity showing large amount of record reads for a specific user.
Promon gather information shows 1 user monopolizing the Lock table.
Latch Statistics shows lots of napping on the LKT (Lock Hash Table).
Sample latch output over 3 10 second intervals:
--- Locks --- --- Busy --- Naps ------ Spins -------
Owner Total /Sec /Sec Pct /Sec /Sec /Lock /Busy
LKT -- 1048725 104872 4306 4.1 19 3277607 31 761
LKT 749 1086335 108633 4839 4.4 20 2549444 23 526
LKT -- 1108935 110893 6017 5.4 37 3959617 35 658
FACT(s) (Environment):
9.1D04
All Supported Operating Systems
Progress 9.1D
CHANGE:
Batch job which normally runs at night time was being ran during prime production hours.
CAUSE:
One user is manipulating the lock table and all users attempting to acquire the LKT to make an entry into the Lock Table in shared memory are having to wait in an effort to acquire the LKT latch.
FIX:
Workaround for this issue was to ensure that this job does not run during key production hours.
Recommendation is to look at the scope of the transaction being ran to identify if the program being ran by the one user requires an exclusive lock on all the records being read. If not, then change the scope of the transaction.