Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site

http://developer.mimer.com


Database Server Memory Areas


The database server memory requirements include the following components:

Code

The server code requires about 4 Mb (perhaps less on some platforms).

Data and Thread Stacks

As a rough guideline, assume about 500 Kb data plus 400 Kb for each thread started (the total number of threads started is the number of background threads plus the number of request threads), the actual figures, however, depend on the operating system being used.

Bufferpool

The bufferpool is the main primary memory cache used by the basic data access routines in the Mimer SQL database management and contains data pages from the databank files. It is a local memory area in the database server process.

The bufferpool does not grow dynamically, so whenever the bufferpool is full and access to a new page is required, space is released in the bufferpool by swapping out the least-recently-used resident page.

Frequent page replacement operations detract from the overall system performance since access to disk is relatively slow. The best Mimer SQL performance is thus obtained by having as large a bufferpool as possible without exceeding the amount of main memory available. In practice, it is always necessary to find a suitable compromise between allocation of memory to the Mimer SQL bufferpool and keeping memory available for user applications and operating system tasks.

The size of the bufferpool depends on the parameters Pages2K, Pages16K and Pages64K which are specified as part of the local database definition, see The Local Database.

The amount of memory used by the database buffers can be calculated by:

buffer space in kilobytes = Pages2K*2 + Pages16K*16 + Pages64K*64

Note: The bufferpool contains a variety of other data, therefore the total bufferpool size will be at least 10% greater than the space needed for the database buffers.

The default initial bufferpool size for a database server is based on the memory available on the machine.

Fine tuning of the bufferpool is performed manually by adjusting the parameters in the local database definition, see The Local Database, after the Mimer SQL system is fully installed and has been functional for a period of time. The fine tuning should be repeated whenever there is a significant change in the computer workload distribution.

Since the Mimer SQL bufferpool size affects the performance of both Mimer SQL and other applications (because it reserves memory for a Mimer SQL database server), it is advisable to perform regular routine checks on the bufferpool statistics in an operational system by generating a Performance report, see The Performance Report.

Note: The Windows NT performance monitor can also be used to monitor a database server running on any platform. Refer to the documentation supplied by Microsoft for the Windows NT operating system for details.

Bufferpool Tuning Guidelines

Some general guidelines for bufferpool tuning are:

Communication Buffers

Each communication buffer is about 70 Kb, it varies slightly depending on platform.

There is one communication buffer for each user as defined by the Users parameter in the local database definition, see The Local Database.

All communication buffers reside in shared memory.

SQLPOOL

The SQLPOOL area contains information about opened tables and databanks, compiled SQL programs, etc.

The initial size (in Kb) of the SQLPOOL is determined by the SQLPool parameter in the local database definition, see The Local Database.

The SQLPOOL area grows dynamically when the database server needs more space. The local database parameter MaxSQLPool controls the maximum size (in Kb) of the SQLPOOL.

The value for MaxSQLPool is 2000*(Users+RequestThreads) by default.

The SQLPOOL area is not locked in physical memory. This allows the SQLPOOL to grow dynamically and it may become larger than the physical memory of the server process. The operating system generally manages this situation by page-faulting. The page-faults will not affect bufferpool performance if that area is locked in physical memory.

If the amount of operating system page-faulting observed in a database server becomes excessive, it is an indication that the memory required by the server process is much greater than the amount of physical memory allocated to it. In this case, either more memory must be installed on the machine or the local database parameters controlling memory allocation must be adjusted to reduce the memory required by the database server process.



Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se
Mimer SQL Documentation TOC PREV NEXT INDEX