R
Rob Fitzpatrick
Guest
When you say "minimum 1 lac and maximum up to 5 lacs", does "lac" mean 100,000, i.e. 10^5? If so, those are not the minimum or maximum values for either -L or -B. If not, can you please clarify? Regarding -L, I tend not to change it from the default as it is sufficient for my needs. However if your application has many users or your application code has transaction-scoping issues then your needs may be greater. If you see errors like "Lock table overflow, increase -L on server (915)", then you need to increase -L (and possibly also fix your code). Regarding -B, as Paul said it is difficult to say what is right for you, or even what is possible given your version of Progress and your application. In short, you want to allocate the largest amount of buffer pool that you can that contains the working set of your application. The working set would be the portion of the data in your database that your application frequently reads, i.e. the data that your application would benefit from having memory-resident. You could have a large database with a large working set (your application reads a lot of that data) or a small working set (your application reads only a small portion of it). The maximum size of the working set is the size of the database, so you gain no benefit from allocating a buffer pool larger than the size of your database. The size of your buffer pool in bytes is the value of -B times the database block size. Also, be aware of your limits. Using a 32-bit RDBMS license you are limited to the allocation of less than 2 GB of buffer pool. And if you have a federated application (i.e. several databases) then you are further constrained, as any shared-memory client must be able to map all of the shared memory segments of all the attached databases into its virtual memory address space. With a 64-bit RDBMS license you don't have that practical Progress limitation but you could still be affected by the OS shared memory limit. In Unix, check ipcs -l for limits on total shared memory size and individual segment size. How do you know if you have enough buffer pool, i.e. if adding more won't help you? One metric to look at is empty buffers in the promon Buffer Cache Status screen (promon r&d | 1 | 7). If your application has been running long enough to have completed a typical business cycle (however you define that) and you still have empty buffers then increasing the size of -B probably won't help. I say "probably" because someone could always run a new or different program that changes the working set of the application, which in turn changes your caching needs. Note that some database utilities read a large portion of your database, or even all of it, like probkup online. This means all of that data gets loaded into the buffer pool, whether it is later useful for your application or not. For utilities like this I allocate a small amount of private buffers (e.g. -Bp 10) so the utility doesn't "foul" the buffer pool and evict data that is useful to the application. Finally, bear in mind that buffer hit percentage is a very coarse metric and doesn't tell you everything you need to know about caching. A low number is definitely bad and tells you that you would benefit from having more cache, but a high number doesn't necessarily tell you that all is well. For example, a 99% hit percentage tells you that one out of every 100 reads is a cache miss and results in a disk read. Although 99% sounds very good, if your total reads is a very large number then 1% of that (your cache misses) is also a very large number. In other words, even with a very high buffer hit percentage you may still benefit from increasing the size of the buffer pool.
Continue reading...
Continue reading...