Printable View
INFORMATION
Article Number 000012531
Environment
Progress 9.x
OpenEdge 10.x
IBM AIX
UNIX
Enterprise Database License
OpenEdge Category: Configuration
OpenEdge Category: Database
OpenEdge Database Category: Monitoring/Promon
OpenEdge Database Category: Performance
Question/Problem Description
Database hanging periodically
Overall performance of the databases decreases
It takes a long time to login in the application when users already logged in experience delays in the aplication
Database appears to be hanging.
Periodically, user processes will all of a sudden seem to lock up or hang.
truss output produced from these processes indicate that these processes appear to be doing something with a semaphore call (semop).
Hang will be cleared up if DBA terminates all processes holding or waiting for a latch.
Promon shows blocked users
Blocked clients waiting on BHT (Buffer Hash Table)
Promon -> Status -> All Processes screen is showing multiple users waiting on the LKT (Lock Table latch)
Promon -> Status -> All Processes screen is showing multiple users waiting for a BUF (Buffer)
Promon shows high Waiting on semaphores
Cannot modify or tune -spin parameter within promon because the database was not started with -spin.
Clarifying Information
Operating System does not indicate a bottleneck on system resources (like disk IO or memory)
CPU is almost 100% idle
Machine where database is running has multiple CPUs.
Not making use of the -semsets database startup parameter.
Users can log into the database.
Error Message
Defect/Enhancement Number
Cause
Not making use of the -spin parameter, so all processes are using the semaphore set for locking.
Resolution
1. Modify database startup parameters to make use of the -spin parameter. Set -spin to 6000 or 10000 per CPU.
Example: If system contains 2 CPUs, set -spin either 12000 or 20000 and tune as needed.
2. Add -semsets parameter to database startup parameters. Set -semsets parameter to 1 semaphore set for every 50+ users in the database.
Example: 100 users, set -semsets 2
Workaround
Notes
Attachment
Yes, you must dump & load in order to reconfigure to type 2 storage. The linked PPT has lots more details. At a minimum I would create 3 type 2 storage areas — 1 for data, 1 for indexes and 1 for LOBs (if you have any). If you have any word indexes put those in their own storage area also. NOTHING EXCEPT SCHEMA should be in the schema area. In order for an area to be type 2 it must have a blocks per cluster of at least 8. If you have large tables (more than 1GB) they could go in an area with a cluster size of 512.
Http://pugchallenge.org/2012PPT/sos.pptx