How Progress uses Buffers.
To properly discuss this topic let’s first review how Progress uses Buffers:
The Progress database is made up of a series of blocks.
- All of these blocks are the same size (1K, 2K, 4K, 8K) but each has a different structure depending on its use (i.e., to store records, indexes, lobs and other control information).
- Blocks which contain records are called RM blocks, where each block contains a number of slots or spaces for records as defined by the "records per block" at database creation.
When a Progress procedure calls for a record to be read,
- Progress reads the entire block (or blocks, a record can span multiple blocks) into an area of shared memory called the Buffer Pool. By doing this, a short cut is provided for subsequent record reads as this is a shared buffer pool used by all shared-memory clients.
- If a requested record is contained in a block that is already in this buffer pool, it does not need to be read into shared memory again from disk. By using this buffer pool, Progress is saved from having to re-read every record required by the application into memory.
The size of the buffer pool
- Is controlled by the -B (Blocks in Database Buffers) parameter on the broker startup command (_mprosrv) multiplied by the database blocksize.
- The larger the buffer pool, the more record manager blocks can be loaded, the greater the chance of having the sought after record already in memory.
- There is a point of diminishing returns when the pool size is too large. Initially no further improvement in performance is observed because the improved probability of finding a record in memory is offset by an increase in the amount of time spent searching buffers for the record and eventually a rapid drop in performance when the system starts swapping.
Sizing the Buffer Pool:
How are Private Read-only Buffers used.
- Collecting metrics from the Progress Monitor (promon) > 5. Activity screen and sampling Buffer Hits over a period of usual database activity. When the "Buffer Hits" percentage exceeds 95 percent, or until the system starts paging the best buffer pool size has been ball-parked.
- A better metric is PROMON > Option 3 "Block Access" to gauge the "DB Request" to "DB Read" ratio;
- 0 DBReads : any number of DBRequests is the optimum, but unlikely.
- 1 DBRead to 20 DBRequests is considered a good ballpark, but each application "sweet point" is highly dependent on the application itself and the activity that it taking place when these metrics are base lined.
With this understanding on how Progress uses buffers, let’s look at Private Read-only Buffers.
Imagine a situation where there are many "heads down" users busy using an Order Entry Application. In this application each Order Line record that is entered, uses the Item master file for the item entered to get the current price. After the first several orders are entered, the blocks which contain the Item master records for the best selling items have been read into the Public Buffer Pool, so each additional request thereafter for these records do not need to be read again from disk, providing excellent performance.
Then suppose someone wanted to run a Customer Master Report, every customer record (every block which contains a customer record) must first be read into the Public Buffer Pool. At some point, this buffer pool is going to fill up. Normally this is not a problem since Progress uses a Least Recently Used (LRU) algorithm to pick which block to write back to the disk to make room for the incoming block. In the case where so many blocks need to be read, many (or all) of the blocks currently in the public buffer pool will have to be flushed to disk, causing degradation in performance for everyone else accessing the database. This degradation is caused when records in the buffer are flushed and need to be retrieved a second time from disk.
There is an alternative to this, using Private Buffers specified by the -Bp parameter for the read intensive user. Each session that uses a private read-only buffer reduces the number of public buffers available and creates an isolated portion of private buffer pool separate from the shared buffer pool managed by the database broker. This private buffer pool is then used by Progress for read-only operations like the Customer Master Report example. As the user reads records, if the corresponding buffers are not already in the shared buffer pool, the records are read into their Private Buffer. So, when their Private Buffer gets full, instead of reading the needed blocks into the shared buffer pool, thereby displacing all of the blocks, the buffer that was least recently used in the private pool is evicted by the user who brought them into memory - thereby greatly reducing the impact of running the report in the shared buffer pool.
As the private buffer pool (-Bp) is an isolated portion of public buffer pool (-B), it is also limited by -B. The total -Bp of all client sessions is limited to a maximum of 25% of -B. The database server startup parameter -Bpmax, was introduced in 9.1C to limit the maximum -Bp any single client connection can request. The default value is 64, which was the hard coded limit for -Bp in previous Progress versions. In turn, -Bpmax can be no more than 25% of the primary buffer pool (-B) value.
Performance problems begin when is only efficient for one user to use private buffers at a time. If two or more users have sessions using the -Bp parameter, it takes up a lot more memory and they don't get to take advantage of each other's private reads which both adds to the system overhead as well as causing more i/o operations. Incidentally, the converse is not true, users of the public buffer pool may benefit from the content of the private buffer pool reads. If another user needs a buffer that is currently in a user’s private buffer, the buffer itself is not moved in memory, but this buffer is effectively transferred to the public buffer pool by adding this buffer to the public buffer list and removing it from the private buffer list.
The biggest drawback to having private buffer pools in use, is that interactive users don't tend to only run reports, they also update records. When they want update something, the block that was read into the private buffer pool must first be copied back to the public buffer pool. If it had been updated since the first read into the private buffer, it must be read again, wasting a read and slowing performance.
Private Read-only Buffers can be useful if used carefully.
- -Bp should only be used as a startup option for a single user doing many read only operations (reports/inquiries).
- Once the reports are finished, the user should either release this memory back to the public buffer pool by logging out of the Application and starting another session without the -Bp paramete
- Private Buffers can also be initialized through 4GL at runtime using the "_MyConnection._MyConn-NumSeqBuffers' VST Table before running the report and then unset after running the report.
- When using the -RO client parameter with -B against a running database, for example during a binary load operation, the "-B" buffers will be allocated as Private Buffers. Together with the -RO this can be dangerous as it means no shared memory, database buffer pool, shared memory locks, lock table are touched.
- If the -Bp option is used for all users, there will be a substantial performance degradation, not an increase, because as soon as the public buffer is filled, it will never be replaced by data that is frequently read because all users are using their own private buffers and LRU chains and thus have to read much more from disk - which they could potentially be sharing!
- The best guideline is to test using private buffers in a controlled way. For read-intensive users, a performance enhancement will be worth using. But for many cases, the best option will be to use the memory that was going to be used by private buffers for the -B shared buffer pool or to consider using the secondary buffer pool (-B2).