Kbase P8479: What factors affect the performance of my Progress database
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/15/2008 |
|
Status: Verified
GOAL:
What factors affect the performance of my Progress database
GOAL:
Why my Progress database runs slow
FIX:
Determining the best performance for a database is an everyday task; databases grow and shrink its data and indexes, affecting the performance delivered to the client applications.
Some of the factors that may affect the performance of the database are outlined here.
- Slow disks
This factor is one of the main bottlenecks when doing intensive readings and writings, to avoid this problem you may need to obtain new hardware to improve the access speed to the data.
- Extents distributed badly among disks
Investigate how the file systems or partitions are defined across the disks and try to balance the I/O load among all the disks on the system.
- RAID 5
RAID 5 has shown a nice behavior for high amounts of readings and also less disks space is wasted, however is not a good idea for highly loaded transaction systems, try to use other level of RAID. Refer to Progress Solution 21152 for further details on RAID 5.
- Disk fragmentation
A highly fragmented disk means that multiples pieces of the same file are distributed across the disk; this causes intensive activity of the head over the disk surface. To avoid this problem get a defragmenting utility and run it against the disks in your machine.
- Data fragmentation
Inside the database the data may also be fragmented; the best way to eliminate this fragmentation is performing a dump and load. Refer to Progress Solution 15479 for further details on dump and load.
- Indexes space utilization issues
To avoid problems with indexes you should rebuild the indexes once in a while. This will prevent delays in the search of information. You can use the idxbuild utility or idxcompact.
Refer to Progress Solution 11242, Progress Solution P55740 and Progress Solution 19829 for further details.
- Table distribution over storage areas
If all of the tables reside in the Schema Area the performance will be the same as having a single volume database; try creating new areas distributing all the extents among the available disks in the system, then use tablemove and indexmove to relocate the tables and indexes to an specific area.
- Always use -B
Allocating memory for your database will allow you to have a good rate of buffers hits (a buffer hit occurs when a database block is found already in memory rather than having to read it from disk); if you don't set the -B the default value is a very low amount that can but result in poor performance. Use promon to find out your current buffer hit and try to have it always bewteen 90% and 95%.
A -B set to 10% of the data on disk is a good amount to start with, and then you can increase it if needed. But always remember that the system may also need resources for other processes and if you allocate too much memory for a database the system might start swapping and this will cause a performance loss that will annul the performance gained by having a larger -B.
- Avoid high load process running in the same box
If you have some other applications running in the same box where the database is, all the resources (memory, cpu, disk, etc..) will be shared among all these applications and our database processes; if the other processes demand too much resources the performance of the database will be affected, so try to avoid running other high load processes to guarantee Progress will have the resources when it needs them.
- Use PROMON
This tool will show us the main indicators of performance issues in our environment, try to use it as much as you can to find all the information related to performance in your database like the buffer hit ratio or the amount of buffers flushed at checkpoints, etc..