Kbase P32184: What is a good extent size to use for database extents?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
04/03/2009 |
|
Status: Verified
GOAL:
What is the best database extent size?
GOAL:
What is a good extent size to use for database extents?
GOAL:
What extent sizes are recommend when building a multi-volume database?
GOAL:
What is the optimum or recommended extent size for my OS?
FACT(s) (Environment):
Progress 7.x
Progress 8.x
Progress 9.x
OpenEdge 10.x
All Supported Operating Systems
FIX:
There is no simple answer for the optimum extent size. It will depend on what you are trying to achieve with the resources you have available. The actual extent size is not terribly important as far as performance is concerned on modern filesystems.
In general, fewer files is better because less system resources, such as file handles, are needed, and from an administrative point of view, it is easier to keep track of fewer files, especially if you use something other than the Progress backup utility for your backups. Also, the fewer files there are, the less time it takes to open the database.
A default recommendation is to use extent sizes of just below 2 gigabytes (size of 2000000 in the .st file). Of course, if your database is smaller than 2 GB, a smaller extent or a single variable size data extent could be used.
However:
To get good I/O performance, the database should be stored on multiple disk drives. /Drives/, not /partitions/. The more drives, the greater the system's IO capacity. To balance the load, the database should be evenly divided over the available drives. There are several different ways this can be accomplished.
1. Use a striped sets
This mechanism combines the available drives together into one large logical filesystem. Space is allocated by the operating system within the stripe set on a "round-robin" basis so that all the member drives get equal amounts of data. A stripe size of 1 megabyte or less is recommended. I have had good results with 64 kb. The optimum size will depend somewhat on the exact system. Unfortunately I don't have extensive test data. The data I do have, on Linux, shows that 256 mb stripe size gives better throughput than 16k by about 25 percent.
With a striped disk array, the number of data extents is not important. One large file would suffice if the database were of fixed size. Usually it is not, so one needs several extents to allow for growth. The growth rate depends on the application of course. So I recommend extents of 2 gigabytes. For databases larger than 50 gigabytes, larger extents are advisable to keep the number of files down.
But you also need to worry about reliability and the disks should be mirrored as well as striped so that a single drive failure will not cause the entire stripe set to be lost.
If possible, for best performance, you should have the bi log on a separate disk, apart from the stripe set that contains the data extents. With the bi log on the same stripe set, you may see up to 25 to 30 percent lower performance, depending on the workload the application generates.
Do NOT store the ai extents on the stripe set with the data extents. That completely defeats the purpose of after-image journalling.
2. Use "poor mans striping".
While not recommended, it does work, but requires much more effort for the initial setup as well as on an ongoing basis when the database grows and new extents have to be added. The goal is to equalize the disk load by dividing the database into pieces such that several pieces can be stored on each drive. 4 per drive might be a reasonable number. In that case, take the database size and divide by 4 x number of drives to get an extent size.
3. For databases where performance is not critical, use one or a few large extents.
4. The Workgroup and Personal databases do not allow extents larger than 2 GB. The enterprise database does, except on Linux. The limit is based mainly on what the operating system allows. Most system's limit is less than 64 GB per file and some are much higher. Consult your hardware and OS manuals for specific environment information.