Kbase P121754: How to change the Commit Lock Skip Limit for the TXE in promon?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
04/11/2009 |
|
Status: Verified
GOAL:
How to modify the Commit Lock Skip Limit for the TXE?
GOAL:
Can the TXE locking algorithm be altered?
FACT(s) (Environment):
All Supported Operating Systems
Progress 9.1E04 Service Pack
OpenEdge 10.0B03 Service Pack
OpenEdge 10.1x
OpenEdge 10.2x
FIX:
In 9.1E04, 10.0B03 and later, the TXE algorithm was changed in an effort to ensure that TXE Commit Locks will not be starved. The new algorithm provides for 10,000 TXE Share and TXE Update Lock Requests to be processed prior to the TXE Commit Lock Requests. The goal is to have this new algorithm auto or self tuned in the future. Currently this new algorithm can be manually tuned via promon depending upon the contention being experienced.
To determine if you are experiencing a TXE contention issue, go into promon and look at the TXE Lock Activity Screen. This can be done by doing the following:
promon dbname
Enter the following commands:
R&D
debghb
6. (Hidden Menu Option)
9. TXE Lock Activity Screen
If on this screen you see high wait percentages on Record Deletes, or Key Deletes and you also notice long queues for Update and Commit Locks, then you may benefit from setting the TXE Commit Lock Skip Limit to 0 as documented below.
Example output from the TXE Lock Activity Screen which would indicate that tuning of the TXE Commit Lock Skip Limit to 0 may be warranted.
04/03/06 Activity: TXE Lock Activity
11:20:31 04/03/06 11:20 to 04/03/06 11:20 (10 sec)
--- Requests ---- --------- Waits ---------
Total /Sec Total /Sec Pct
Record Create 546 54 5 0 0.92
Record Modify 2605 260 19 1 0.73
Record Delete 477 47 371 37 77.78 <==
Key Add 3275 327 3 0 0.09
Key Delete 2291 229 543 54 23.70 <==
Sequence Update 24 2 0 0 0.00
Other 273 &n.bsp;27 9 0 3.30
Share Locks 7941 794 25 2 0.00
Update Locks 1494 149 925 92 61.91 <==
Commit Locks 446 44 457 45 102.47<==
Excl Locks 0 0 0 0 0.00
Total 9881 988 1407 140 14.24
Upgrades Request: 383 Rate: 38 Pct: 4.82
Current Share: 1 Update: 1 Commit: 0 Excl: 0
Current Wait: 15 Wait: 41 Wait: 34 Wait: 0
Look at the two lines above which begin with Current. These lines represent the different facets of the TXE latch that can be acquired. They are: Share, Update, Commit and Excl(Exclusive).
The line beneath these facets of the TXE represent where there are waits for that specific facet. If you see a good number of waits on Update and Commit, then you could benefit from tuning the TXE Skip Commit Lock Limit down to 0.
To tune the new TXE algorithm and alter the locking behavior do the follow:
promon dbname
Enter the following commands:
R&D
debghb
6. (Hidden Menu)
10. Adjust TXE Options
3. TXE Commit Lock Skip Limit
The default value is set to 10000. When you enter 3 to adjust the TXE Commit Lock Skip limit you will receive the following prompt requesting your new setting:
Enter TXE commit lock skip limit (0 to 100000):
Enter your new value and press return.
This parameter sets the maximum SHARE/UPDATE locks that can skip the queued COMMIT locks.
Once a change is made, it occurs immediately. However, depending upon the queue or contention which had manifested itself, it may take take a while to see the benefits or .contention diminished. This is because the new locking algorithm once in place still needs to process requests already in queue. An exact time as to when the Database Engine will get through the contention cannot be identified.
The TXE Commit Lock Skip Limit should be changed under the following circumstances:
1. Set it to 0 (zero) will restore to the old TXE locking algorithm.
2. If you notice very long COMMIT or UPDATE locks queued (from the promon "Activity: TXE Lock Activity" window), and also the system performance slows down, and CPU utilization spikes then this value should be set to 0.
The new algorithm gives Record Delete Operation lower priority. If you want to increase the priority for this kind of operation, the value should be decreased or even set to 0..