Specifying DBMS Buffers By Stream (DBMS only)

DATABASE
ADMINISTRATION

Specifying DBMS Buffers By Stream (DBMS only)
TECHNICAL ARTICLES -> PERFORMANCE ARTICLES
[ Back ] [ Next ]

Q:

We have an application that uses several streams (attaches to several databases simultaneously). We noticed that when some of these processes run, they use an excessive amount of system resources, and tend to page-fault heavily. We’ve tried increasing their working set sizes, and although this has helped the page faulting somewhat, we wonder why this application is such a resource hog.

A:

To understand where your system resources are going you have to understand a little about how DBMS handles multiple streams. Each stream is a separate connect to the database – each has its own UWA (User Work Area), and each maintains its own set of buffers. Thus, if your application contains five (5) streams, each of which holds 100 buffers of 10 pages each, each process is effectively using 2.5mb (5*100*10*512) of virtual memory for buffering. In addition, you can figure an additional 15% to handle other data structures needed to manage these buffers. First, evaluate the need for so many steams – if each stream is accessing a different database, then you have no choice, and must continue to use a separate stream for each database. If they are accessing the same database, then you may want to combine some of the streams. A second alternative is to use different buffering strategies for each stream. There are two ways to accomplish this.

1. Modify the default buffer parameters on each database to match the exact requirements of that database. For example, those databases that tend to be accessed randomly should have many small buffers – databases with significant sequential access should use fewer large buffers. In choosing the default parameters, your primary concern should be with the buffer length since the buffer length cannot be changed dynamically at run time.

2. The second approach is to make use of the logical name, DBM$BIND_BUFFERS, to dynamically adjust the buffer count (number of buffers) for each stream. To use this approach, you will have to write a routine that will define this logical name to be the appropriate value before the first bind on each stream. Using this technique, it is possible for each stream in your application to use a different number of buffers. You may want to consider designing this routine to use a logical to determine the appropriate number of buffers to use for this stream. SCI has implemented this design at various client sites, where the appropriate number of buffers is determined at run time based on the current image and stream name.

If you are running DBMS v4.3 or later, you can take advantage of global buffering. This allows multiple processes (or streams) to share physical memory for database buffering.

Top

Was this article helpful?
This information and more was originally published in SCI's technical newsletter, DBAdvisor.
Past issues are available online; you may also fill out a subscription form.

If you have a technical question about this article or about any other
CODASYL DBMS or Rdb topic, then ask our experts.

How would you rate this article?