helpinghand
search
needassistance
 
Features
Tuning Parameters in Mimer SQL
Categories: Database administration, Database features
Introduction

One of the Mimer SQL characteristics is automatic self-tuning. This means that the number of tuning parameters in Mimer SQL is, intentionally, very limited.

The two most important parameters are the size of the database cache, referred to as the buffer-pool, and the number of request and background threads.

Initially, the default value for the buffer-pool size for a database server, set at system generation, is based on the memory available on the machine. And the default number of request and background threads depends on of the number of connections authorized by the license key.

You fine-tune the buffer-pool manually by adjusting the parameters in the local multidefs (or Mimer Administrator in Windows) after the Mimer SQL system is fully installed and has been functional for a period of time. You should fine tune whenever there is a significant change in the computer workload distribution.

Mimer SQL compiler optimizes access paths for SQL statements using statistical information collected from the database. Mimer SQL collects basic statistics for each table whenever the table is opened. In many situations, these statistics are sufficient for maintaining high performance. If you want optimal performance for an application, use the SQL Update statistics statements to collect detailed information.

Function

Buffer-pool

The size of the buffer-pool is fixed and defined at system start-up, so, whenever the buffer-pool is full and access to a new page is required, space is released in the buffer-pool by swapping out the least-recently-used resident page. The best Mimer SQL performance is thus obtained by having as large a buffer-pool as possible without exceeding the amount of main memory available.

The size of the buffer-pool depends on the parameters Pages2K, Pages16K and Pages64K which are specified as part of the local database definition in the multidefs file on Unix and VMS or Mimer Administrator on Windows.

The buffer-pool also contains other information; therefore the total buffer-pool size will be at least 10% greater than the space needed for the database buffers. If more than about 2% of all Mimer SQL page requests in a buffer-pool partition result in a page fault, the corresponding buffer-pool is too small. Check the Performance report for statistics on page requests and faults.

Number of request threads

The amount of concurrency the database server can support depends on the number of available request threads. If there are more concurrent requests than threads, the database server will start scheduling requests to improve response times. In such a situation, increasing the number of request threads may improve performance.

The number of request threads in a database server is defined at system start-up. If you want to change to the number of request threads, you must stop and re-start the system.

The maximum number of concurrent request threads is limited by the size of the buffer-pool.

Number of background threads

The background threads in Mimer SQL perform tasks such as recording transactions in LOGDB, updating master and shadow databanks, securing data on disk.

The parameters “Transaction count” and “Pending background thread requests” in the “Background threads” section of the Performance report give relevant information for fine-tuning the number of background threads.

SQL Optimizer

If an application has complex SQL queries, and queries using views, you may be able to improve performance by executing the UPDATE STATISTICS statement that will guide the SQL compiler in optimizing search paths. You should update statistics in the following cases:

  • If the size of a table has changed significantly.
  • If the maximum/minimum limits on values in a table have altered significantly.
  • If a databank has been altered from having the NULL option to having the TRANS or LOG option and contains secondary indexes.
  • If a databank with the TRANS or LOG option contains secondary indexes and has just been upgraded from an older version.


The SQL optimization is performed at run time, meaning that changes in large dynamic databases are automatically catered for.

We recommend that you use the UPDATE STATISTICS command in SQL on a regular basis.

Techniques

The MIMINFO functionality provides facilities for getting system management information from a Mimer SQL database server in the form of reports. The buffer-pool report (previously called MIMDUMP) produces a report that is useful to Mimer Support personnel when investigating system problems. The Performance report (previously called MIMSERV) provides information useful for monitoring performance parameters.

You can also monitor the performance of your local or remote Mimer SQL databases with the Windows/NT Performance Monitor, a graphical tool. For this purpose, you need to install a Mimer SQL database server on Windows/NT.

Benefits

Database administration with Mimer SQL is characterized by being greatly simplified. Indeed, in most cases, normal operational control takes care of the database automatically, thereby minimizing the amount of specialist knowledge required and significantly reducing the maintenance costs for Mimer SQL-based systems.

Links

Mimer SQL Documentation Set, PDF-file. (See the System Management Handbook.)

Mimer SQL Documentation Set, html navigation. (See the System Management Handbook.)


Last updated: 2002-08-21

 

Powered by Mimer SQL

Powered by Mimer SQL