Progress OpenEdge Database Performancetuning tips and tricks

Progress OpenEdge Database Performancetuning tips and tricks

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.

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 log cluster size to 16 MB. 
Larger bi log 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 log block size to 8 kb.
Allows for more efficient writing of the bi log, which is always done using synchronous writes.

On Linux, use 4K.

On Windows, use 4K.

6. Set AI log block size same as bi log 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 log buffers so the server does not have to do it.  This gives the server more t.ime to do useful work.  With self-serving 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 journalling (you should, but not for performance reasons), run the after-image writer (AIW).
The after-image writer’s job is to write filled AI log buffers so the server does not have to do it.  This gives the server more time to do useful work. With self-serving 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. 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.

12  Put bi log on separate drive from data extents if possible.
You want writing the bi log 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 logs in with the data extents. You did stripe the data extents, didn’t you.

13.  Use two drives for AI extents, with extents alternating between them.
The purpose of after-image journalling 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.

You are highly likely to achieve a 45 percent performance loss in normal operations and more when doing maintenance or recovery operations, regardless of whether RAID 5 is implemented via software, or in the hardware.

15. 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.

16. Get an Enterprise Database License.
If you want to tune your database to the max, you should buy an Enterprise Database License. Make use of APW’s BIW and AIW and tune the -spin parameter online.


source: Gus Bjorklund, Progress Knowledgebase