Database Server Memory Areas
The database server memory requirements include the following components:
The server code requires usually a few Mb, but it depends on the platform.
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.
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:
- Whenever main memory is available, it should be allocated, if possible, to the bufferpool.
- Ensure that the bufferpool is not subject to system paging or swapping, since the paging algorithms used by Mimer SQL and the operating system usually differ, and forced cooperation between the two will often detract considerably from Mimer SQL's performance.
- If more than about 2% of all Mimer SQL page requests result in a page fault, the bufferpool is too small. Statistics for page requests and faults are presented in the Performance report, see MIMINFO - System Information.
- It is important to take note of the page fault statistics for each region in the bufferpool to ensure that the most appropriate allocation has been made in each.
The Mimer SQL system decides which page size is most appropriate for each task to be performed. For example, 16K pages are currently used for transaction data (this may change in the future) and therefore allocating too few 16K pages may currently adversely affect performance even though generous allocations have been made in the other bufferpool regions.
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.
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
MaxSQLPoolcontrols the maximum size (in Kb) of the SQLPOOL.
The value for
MaxSQLPoolis 2000*(Users+RequestThreads) by default.
The SQLPOOL area is never locked in physical memory. This allows the SQLPOOL to grow dynamically and it may become larger than the physical memory allocated to 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.
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40