Mimer SQL on Windows
Setting the Size of the Bufferpool

The bufferpool is the main memory cache used by the Mimer SQL database management system. It is divided into sections with different page sizes. There is a section for 4K pages, a section for 32K pages and a section for 128K pages.

Whenever the bufferpool is full and access to a new page is required, space is released from it by deleting the least-recently used page. Frequent page replacement operations will detract from database performance. In general, maximizing the bufferpool size will give the best database performance.

In practice, however, it is necessary to find a suitable compromise between allocating as much memory to the bufferpool as possible, and keeping enough memory available for applications and operating system tasks.

If there is insufficient memory available for applications and operating system tasks, the resulting virtual memory swapping will also adversely affect performance.

The compromise between allocating memory to the bufferpool or leaving it generally available will be influenced by factors such as: the predominant type of application being run, the number of concurrent database users and the demands of the operating system.

Fine-tuning of the size of the bufferpool should be done when the database is fully set up and operational and should be repeated whenever there is a significant change in the computer workload distribution. The MIMINFO utility should be used regularly to gather bufferpool statistics for tuning purposes.

Whenever there is unused memory available, it is generally a good idea to allocate this to the bufferpool. If more than 5% of all Mimer SQL page requests result in a page fault, the bufferpool size is too small and should be increased.

Care should be taken to ensure that the bufferpool is never subjected to system paging because forced co-operation between the system and Mimer SQL paging algorithms will generally detract considerably from database performance.

If the statistics from MIMINFO show that some pages in the bufferpool are never used, Mimer SQL database performance is not being limited by the bufferpool size. However, performance may still appear adversely affected if insufficient memory is available for applications and the operating system because it is, perhaps unnecessarily, allocated to the bufferpool. In situations like this, the size of the bufferpool should be reduced.

See Also