Kbase P19806: The Top 10 Performance Tuning Tips For The Progress Database
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
26/04/2011 |
|
Status: Verified
GOAL:
The Top 10 Performance Tuning Tips For The Progress Database
GOAL:
How to improve database performance
GOAL:
Checklist for Database Performance Fine Tuning
FACT(s) (Environment):
Products / Versions
All Supported Operating Systems
FIX:
The Top 10 Performance Tuning Tips For The Progress Database
By Gus Bjorklund, 20 February, 2003
This is not an exhaustive list. These are "quick and dirty", mostly easy to do tips that should get you the most bang for the Euro without doing too much work or too much thinking.
As Ted Williams said, "If you don't think too good, don't think too much". But even if you do think well, it is work to do so and extra work can be avoided.
You may not be able to do all of these things if your hardware configuration does not allow it, or if you have the Progress Workgroup database. Do as much as you can.
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 you should use 4 kb. The Linux virtual memory architecture does not allow for larger page sizes. That will be fixed in the 2.6 kernel but we don't have that yet.
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. 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 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.
14. 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.
15. DO NOT USE RAID 5.
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.
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. .