How Buffer Pools Help
Buffer pools speed up database access by keeping frequently used blocks in memory. Memory access is generally hundreds of times faster than disk drives. Database writes are also optimized by enabling the database to write multiple times to the same block before it is actually written to disk.
In OpenEdge versions 10.2B or greater you can assign database objects to two different memory based pools (Primary and Alternate). If you are running a version earlier than 10.2B you can only use the Primary buffer pool. Implementing the alternate buffer pool properly can give your application enormous performance gains.
Things That Hurt Buffer Pools
There are a few things that will defeat even properly sized buffer pools. Most of these will only apply to the primary buffer pool, assuming your alternate buffer pool is properly sized.
Frequent database shutdowns
The only times your database should be shut down is when you are performing maintenance that absolutely requires the database to be offline. Every time the database is shut down the buffer pools must be reloaded from disk. OpenEdge online backups are extremely reliable and when set up properly will not flush the buffer pools.
Online backups without private buffers
By default the online backup will read database blocks just like any other client. Meaning that the contents of the buffer pool will be replaced with the most recently read blocks. In the case of a backup you will be reading many blocks that are seldom used by your application.
Simply add -Bp 64 to your probkup command and OpenEdge will allocate 64 blocks from the primary buffer pool for exclusive use by the online backup. This will prevent the backup from flushing your entire buffer pool.
Improperly indexed code
Application code that should be reading a few records but instead reads an excessive number of records will defeat any buffer pool. Even if you have enough memory to cache the entire database the overhead of reading the extra records will still slow down your application.
Large reports or data extracts
Sometimes your application must read an excessive amount of data. It could be a data extract or a report that spans several years worth of data. If you know that a certain batch job will read historical data that could flush out the buffer pool, simply add -Bp 64 to your startup script.
If the application function is not run from an isolated batch job you can also adjust the number of private buffers from within OpenEdge itself. By updating the _MyConnect VST you can control the number of private buffers for your session. The only record in the VST will be specific to your database session.
DO FOR _MyConnect TRANSACTION: FIND FIRST _MyConnect EXCLUSIVE-LOCK. ASSIGN _MyConnect._MyConnect-NumSeqBuffers = 64. END.
To revert to the default behavior simply assign _MyConnect._MyConnect-NumSeqBuffers back to 0.
Improper storage area configuration
If you skipped the main database page which discusses the core storage concepts I strongly suggest you go back and read it before making drastic changes to your buffer pools. Improper storage setup can waste large percentages of your buffer pool. Many times I have been able to improve buffer pool usage by correcting the underlying storage issues.
What I mean by table scatter in this context is the number of blocks that your working set of data could be stored in compared to the number of blocks they are actually stored in. This is extremely common in Type I areas because there is no segregation of data between tables that share the same storage area.
This can also happen to tables in Type II areas if the following are true:
- The working set of table data is a reasonably small percentage of the total table data.
- The criteria used to identify your working set is not time based in a manner consistent with the order of record creation.
A common example would be a table where the working set filter is a status code column("Open") where recently added records can hold that status as well as much older records.
Primary Buffer Pool Myths
There are a few common misconceptions about tuning and sizing the primary buffer pool that I would like to clear up. People often present these as "rules of thumb" or starting points, but in some cases they are presented as best practices to be followed to the letter.
Make Your Buffer Pool X Percent Of Your Database Size
The proper buffer pool settings will be based on the specifics of your application and how well or how poorly it is written, not some arbitrary number plucked from the sky. Of course you need to start somewhere and 10% is the number usually thrown around.
Before you increase the size you should at least make an effort to identify and correct poorly indexed code. As long as you have problem code that reads an excessive amount of records even very large buffer pools may not be sufficient.
When do you increase the buffer pool it should be done in dramatic fashion and not by some small percentage. For example there is little if any difference between a -B setting of 200,000 and 210,000. Just be careful not to over-commit the memory on your OS and start swapping memory pages to disk.
Your Hit Ratio Needs To Be At Least X Percent
While you should obviously aim to cache as many blocks as possible, aiming for an arbitrary number serves no real purpose. You can have a high hit ratio and poor performance. You can also have a lower hit ratio and excellent performance.
It it easy enough to get a high buffer hit ratio by reading the same small table over and over again. Your buffer hits will appear "perfect" but this has nothing to do with the overall performance of your application.
You need to examine the table reads for your database and make sure they are within the realm of logical possibility. This of course requires you to know about your application and table structures.
Alternate Buffer Pool
The size of the alternate buffer pool is determined by the -B2 database startup parameter. The -B2 parameter accepts a number of database blocks as an option. If your database block size is 8K and use -B2 100000 you will end up with an alternate buffer pool of 800,000K or 781.25MB.
When sizing the alternate pool you need to make it large enough to hold every object that is assigned to the alternate pool, plus some percentage for future growth of those objects. If the pool is not sized properly OpenEdge will enable the LRU mechanism to evict database blocks from the pool. This will degrade performance and eliminate many of the benefits of the alternate buffer pool.
Which objects should go in the alternate pool?
In most cases the alternate buffer pool should be used to store heavily read tables and any indexes used to access those tables. Tables with high levels of update activity should usually be placed in the primary buffer pool.
If you are using Type II areas you can assign each object individually to the alternate pool. If you are using Type I areas you must assign the entire storage area to the alternate buffer pool. For best results make sure your tables and indexes are stored in Type II areas.