Kbase P7142: -B and -L Database startup parameter values recommendations.
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
9/9/2005 |
|
Status: Verified
GOAL:
-B and -L Database startup parameter values recommendation
GOAL:
How to analyze the values of -B and -L
GOAL:
How to evaluate the values of -B and -L
GOAL:
How to plan the values of -B and -L
GOAL:
What is the recommended value of -B
GOAL:
What is the recommended value of -L
FIX:
1) For the -B:
Go to Promon
R&D
Option 2.Activity Displays
Option 1.Summary
look at the field: Buffer Hits, 90% is usually considered a good percentage under typical load. A ballpark figure is 10% of the database size, operating system and available memory constraints nonwithstanding.
Blindly experimenting with -B settings can cause a process to crash with " ** stget: out of storage. (6498)." This is because we can never define -B large enough to accommodate your largest table, for example. There is also a "sweet point" for -B, unique to every environment + application running, whereby increasing -B any further has little effect and then rapidly starts dropping because the buffer pool is so large that there is not enough remaining memory for all the other "stuff" and the system starts paging. When the buffer pool is paged, things become very bad very quickly.
Whatever -B is allocated, we need to ensure that it's going to be available for THIS process for the duration of the execution of the process. If for example, there is an_other memory intensive process running at the same time, then these processes will compete for memory. If there's enough memory available, this does not present a problem. If there's not, there's a whole lot of swapping going on. In essence, increasing -B too high /for the system/ will result in process termination with ** stget: out of storage. (6498), your request has used too much memory. So when tuning -B, keep an eye on swap in parallel.
2)For the -L:
Go to Promon
R&D
Option 1 Status Displays
Option 13 Shared Resources
Review lock table entries in use out of the total lock table entries
Next
Go to Promon
R&D
Option 2.Activity Displays
Option 7.Lock Table
The goal is to ensure that users are getting the correct number of locks for their queries and that the number of lock being upgraded is minimal. Record contention could also show up here.
The lines to focus on are, Exclusive Waits, Rec Get Requests, Rec Get Waits,Share Waits,Upgrade Requests, and Upgrade Waits.
Points to Consider:
Any values in the Wait lines are an indication that users are waiting to get record locks. These fields are Exclusive Waits, Rec Get Waits, and Share Waits.
Upgrade Waits are an indication that a user has gotten a record to update and has to wait for another user to release the record.
Upgrade Requests are an indication that you may not be retrieving the record appropriately in the first place. These are only request to upgrade from a share lock to an exclusive lock.
Increasing -L will not help at all having fewer lock contentions. Lock contentions are an application-related problem. A higher -L only implies that more users will be able to lock more records.