The Top 10 Performance Tuning Tips For The Progress DatabaseOriginally written by Gus Bjorklund, 20 February 2003.Recently reviewed with some additional comments made by Progress Technical Support engineers during the period 2016 - 2018.
This is not an exhaustive list. Some are "quick and easy" to do tips which should get "the most bang for the buck" without doing too much work.
You may not be able to follow all of these suggestions if your hardware configuration doesn't allow it, or if you don't have the Progress Enterprise database.
1. Make sure you have the right indexes for your application.
No amount of database tuning will make up for a poorly written application so do not neglect to look into it. But if you are in a bind, do the obvious database tuning first and then come back to the application. The PRO of having the appropriate index allows the database to pick the right records with the minimal amount of disk I/O. The CON of having too many indices is that ever update of the record will require updates to those indices what chart the fields of the record which have been updated, therefore one record update might be multiple I/O operations.
2. Stripe data extents on as many separate spindles as possible.
One disk drive can do one data transfer at a time. Two disk drives can do two transfers at a time. The more the better and you want the IO load evenly balanced over the available drives. The most effective way to balance the load across multiple drives is to create a stripe set that combines all the drives into one logical drive with the data evenly spread across them. But if you lose one drive, you lose all so you have to combine striping with mirroring to get reliability.
Another way is to create data extents that each contains a piece of the total database. For example, if you have four drives, create 16 extents and put four on each drive. Put extent 1 on the first drive, extent 2 on the second, extent 3 on the third, extent 4 on the fourth, extent 5 on the first, and so on.
A drawback to this "manual striping" is that as the database grows, the balance is disturbed when you add extents.
3. Use a database block size of 8 kb.
Larger block sizes provide greater IO efficiency and more efficient use of storage. Many UNIX file systems have a fundamental block size or page size that is 4 kb or 8 kb. You get the best performance when the database block size matches the file system's page size or is a multiple of the file systems page size.
On Linux 4 kb should be used if the kernel version is less than 2.6, which came out in December of 2003. In kernel versions prior to 2.6, the Linux virtual memory architecture did not allow for larger page sizes. 8kb may be used with kernel versions 2.6 and newer.
On Windows, you should use 4 kb.
4. Set BI cluster size to 16 MB.
Larger bi cluster sizes increase the duration of checkpoints allowing more time for modified database blocks to be written to disk in an orderly fashion by the page writers. Duration for the last 8 checkpoints are displayed in promon's Checkpoints display. If they are at least a minute long, you are fine. Longer is ok but not needed. If they are shorter than a minute, you should increase the cluster size.
If you have the Workgroup database, keep the cluster size small. 512 k or less will be better than large cluster sizes because there will not be any page writers to write modified database blocks to disk.
5. Set BI block size to 8 kb (Usually the default value).
Allows for more efficient writing of the bi, which is always done using synchronous writes. The default BI block size (8K) is sufficient for applications with low transaction rates. However, if performance monitoring indicates that BI writes are a performance bottleneck and your platform's I/O subsystem can take advantage of larger writes, increasing the BI block size might improve performance.
When using OpenEdge Replication it is advisable to set the BI block size to 16 kb to keep it in sync with the AI blocksize for improved performance of OpenEdge replication.
6. Set AI block size to the same as BI block size.
7. Set -bibufs to 25.
8. Always run the before-image writer (BIW).
The before-image writer's job is to write filled bi buffers so the server does not have to do it. This gives the server more time to do useful work. With self-service clients, the server and the client are in the same process, but you still want the server to do useful work.
9. If you use after-image journaling (you should, but not for performance reasons), run the after-image writer (AIW).
The after-image writer's job is to write filled AI buffers so the server does not have to do it. This gives the server more time to do useful work. With self-service clients, the server and the client are in the same process, but you still want the server to do useful work.
10. Always run at least one asynchronous page writer (APW).
The asynchronous page writer's job is to write modified database blocks to disk in an orderly fashion so the server does not have to do it and so that the modified blocks do not have to all be written to disk at the very end of a checkpoint. Promon reports these writes as "buffers flushed" in several places. You want that number to be less than 10 for almost every checkpoint.
11. I lied. I have more than ten.
12. Set -spin to 50,000
Finding the optimal value for spin is hard. With newish fast systems, I use the number of processors times 20,000 as a "rule of thumb". But 50,000 is easier to remember and a good place to start. I have seen one case where a value of 2,000,000 worked pretty well, but that is unusual.
13. Put BI extent on separate drive from data extents if possible.
You want writing to the bi extent to be as efficient as possible and no interference from other activity. If you have many databases on the same machine, then you should put the bi extent in with the data extents. You did stripe the data extents, didn't you. If using a RAID array there is no separate drive unless there are multiple RAID sets on the system in which case put it on a different RAID set on the same system.
14. Use two drives for AI extents, with extents alternating between them.
The purpose of after-image journaling is to provide for a way to recover if the drive(s) holding your database fail. Therefore you MUST NOT store any AI extents on the same drives as the data extents Alternating between two drives allows filled extents to be archived without slowing down writing of the current extent. If you do not have enough drives, put all the AI extents on the same drive. If using a RAID array there is no separate drive unless there are multiple RAID sets on the system in which case put the AI files on a different RAID set on the same system.
15. DO NOT USE RAID 5 OR RAID 6.
RAID 5 and RAID 6 systems have improved over time and reliability has increased dramatically. RAID 5 and 6 are great for read oriented operations.
RAID 5 and 6 can experience a degradation of performance for high volume write operations if the volume of data being written exceeds the cache memory available to the RAID controller. The degree of performance loss could be significant
Gus has observed that it is highly likely to experience a 45 percent performance loss in normal operations and more when doing maintenance or recovery operations, regardless of whether RAID 5 or RAID 6 is implemented via software, or in the hardware when the RAID controller cache memory is saturated.
16. Do not run other stuff on the database server machine.
The more stuff you run on the machine that has the database on it, the more resources you take away from database performance. That means no print serving, file serving, mail serving, screen savers, Microsoft Office, and so on.
17. Do not run on variable database extents.
Variable database extents take time to extend whenever database needs to write more blocks onto disk. Use fixed database extents, which use pre-allocated space, so that database doesn't need to lose time extending them on disk. Also, variable database extents are more likely to get very fragmented over time, whereas fixed database extents may allocate more continuous disk space (whenever possible).
18. Perform database dump and load.
Depending on the database structure, it is possible that over time records will get fragmented within database extent files, especially if there are many disparate tables located in the same area. Run database analysis to determine records' fragmentation. All large tables with Scatter Factor greater than 2 will benefit from dump and load, or they could be moved into their own data area (with the appropriate records-per-block factor).
