The database server enables one or more users to access a database. Each database may have one database server running against it and the server runs on the machine where the database resides.
The parameters which control a database server and which can be tuned to optimize performance are specified as part of the definition of a local database.
The MIMCONTROL functionality provides facilities for managing the operation of a database server (e.g. starting, controlled shutdown, etc.).
The MIMINFO functionality provides facilities for getting system management information from a Mimer SQL database server, such as:
•listing details for the users on the system
•monitoring performance parameters
•dumping data for trouble-shooting analysis by Mimer support personnel.
•listing SQLPOOL parameters
Operational and error messages generated by a database server are recorded in the database server log, see Database Server Log.
Mimer SQL Database Servers
A database, with its set of databank files, can be operated by different types of database servers. The database server most commonly used is the standard Mimer SQL Experience database server called mimexper. The following are some server types that may be available, depending on the Mimer SQL product installed:
•mimexper - Standard Mimer SQL database server program.
•miminm - In-memory Mimer SQL database server program.
To select database server for a specific database, the ServerType parameter found in the database configuration option is used (see The Local Database).
Mimer SQL Experience Database Server
The Mimer SQL Experience server is the standard version for the new generation of Mimer SQL database servers with many innovative and fundamental improvements. See Release Notes provided with each distribution and the Technical Description for Mimer SQL version 11 (or later), found on the Mimer SQL developer site, for the details.
Mimer SQL In-memory Database Server
For systems where a relational database and extreme performance is looked for, the Mimer SQL In-memory database server is the choice. This database server works in memory only, thus providing a huge throughput.
To save a database state to continue from at a later stage an online backup (see Online Backup Commands) can be executed. An online backup will, while the system is running, produce a complete and consistent database file setup written to disk - a set of files that the in-memory database server can use to start from at next start-up.
Please note that because all data is stored and managed exclusively in main memory, all data will be lost when the server is stopped, and upon a process or server failure. Thus it is recommended having a procedure of doing Mimer SQL Online Backups if start-up data should be maintained.
In a Mimer SQL system there are a number of system-wide parameters that have a major impact on the overall system performance.
The most important parameters are the bufferpool size and the number of request and background threads.
The database server memory requirements include the following components:
•Code
•Data and thread stacks
•Bufferpool
•Communication buffers
•SQLPOOL
The server code requires usually a few Mb, but it depends on the platform.
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 Pages4K, Pages32K and Pages128K 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 = Pages4K*4 + Pages32K*32 + Pages128K*128
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, 32K pages are currently used for transaction data (this may change in the future) and therefore allocating too few 32K 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 MaxSQLPool controls the maximum size (in Kb) of the SQLPOOL.
The value for MaxSQLPool is 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.
The Mimer SQL database server process supports a number of separate request threads and background threads, running simultaneously under the operating system.
The amount of concurrency that the database server can support is dependent 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. Increasing the number of request threads in a situation like this may improve performance.
The number of request threads in a database server is defined at system start-up. A change to the number of request threads requires that the system be stopped and re-started.
The maximum number of concurrent request threads is limited by the size of the bufferpool.
The background threads in a Mimer SQL database server perform tasks such as:
•Recording transactions in LOGDB.
•Updating master and shadow databanks.
•Securing data on disk.
•Online backup.
See Background Threads for information relevant to fine-tuning the number of background threads.
With network encryption enabled, Mimer SQL uses AES-GCM (Advanced Encryption Standard with Galois/Counter Mode) to encrypt network communication between the database server and its clients. Communication over TCP/IP is encrypted, while communication using other protocols is unaffected.
AES-GCM provides authenticated encryption (confidentiality and authentication) by encrypting the network communication packages and by creating a MAC tag over the encrypted data. The authenticity of the data is provided by the MAC tag, which ensures that the data has not been altered or tampered with during transmission.
Network encryption is enabled by setting a parameter named NetworkEncryption in the database server.
|
Linux + VMS:NetworkEncryption is a MULTIDEFS parameter. For details, see MULTIDEFS Parameters. |
|
Win:The NetworkEncryption parameter can be set in Mimer Administrator. |
Network encryption is available from version 11. In the case of an older Mimer SQL database server being upgraded to version 11, a password change is needed for IDENTS created before the version 11 upgrade, to be able to use encrypted communication over TCP/IP.
The command miminfo -V can be used to see the network encryption status of connected clients.
Database Server System Requirements
From the point of view of the operating system, a database server requires the system resources described in the following sections.
The amount of physical memory used by the database server process is determined by parameters in the local database definition, see The Local Database, whose initial default values are determined by looking at the amount of installed memory.
|
VMS:For a database server running on an OpenVMS node the amount of physical memory used by the database server process will vary between the OpenVMS process parameters WSQUOTA and WSEXTENT. The WSQUOTA parameter is calculated by MIMCONTROL and is set large enough to include the bufferpool, initial SQLPOOL, communication buffers, code, and stack data. The WSEXTENT parameter is set to the SYSGEN parameter WSMAX (the maximum amount of physical memory a single process may have). For large buffer pools, it is recommended that a resident memory reservation created in VMS. Please see the Mimer SQL OpenVMS Guide. |
The amount of virtual memory that the database server process can use is limited by the operating system.
|
Linux:The virtual memory handling on Linux platforms is platform specific – refer to the documentation for the specific Linux operating system you are using. (Often a paging file used). |
|
VMS:The MIMCONTROL command sets the paging file quota of the database server so that it is large enough to contain all memory areas, including the bufferpool and an SQLPOOL that has grown to MaxSQLPool kilobytes. It may be appropriate to create larger page files to increase the amount of virtual memory available to the database server. Note that if the buffer pool does not use the page files if it is placed in a resident memory reservation. |
|
Win:If you get a message saying the system is running out of virtual memory you may need to increase the size of your paging file. This done by using the Virtual Memory option in the Performance section of the System control panel. |
|
VMS:The database server creates a global section for its communication buffers. This global section resides on the page file. The amount of memory a global section may take from a page file is generally controlled by an operating system parameter. If this limit set by the operating system is exceeded, the MIMCONTROL/START command will fail with the message: %SYSTEM-E-EXGBLPAGFIL, exceeded global page file limit
If this happens, the OpenVMS SYSGEN parameter called GBLPAGFIL, which limits the amount of memory that global sections may take from the page files, should be increased. |
MIMCONTROL - Controlling the Database Server
MIMCONTROL functionality is supplied on all platforms as a complete administration tool for managing database servers.
|
Linux:The database servers on a Linux node can be controlled using the mimadmin command (see the mimadmin man-page). This command invokes the MIMCONTROL program, and other programs, as required. The MIMCONTROL command can also be used directly under Linux. A database server on Linux can be administered by the owner of it or by the superuser root. To change ownership of a database the mimdbfiles command is used (see the mimdbfiles man-page). When a database server on a Linux machine is started for the first time, MIMCONTROL will create a default multidefs file containing appropriate default parameter values, based on the amount of memory installed on the machine. Refer to The MULTIDEFS Parameter File for details. Database server performance can be fine-tuned later by adjusting the parameters as required. |
|
VMS:The database servers for the local databases on an OpenVMS node are controlled by using the MIMCONTROL command directly (as described in this section). When a database server on an OpenVMS machine is started for the first time, MIMCONTROL will create a default MULTIDEFS file containing appropriate default parameter values, based on the amount of memory installed on the machine. Refer to The MULTIDEFS Parameter File for details. Database server performance can be fine-tuned later by adjusting the parameters as required. |
|
Win:The Mimer Administrator can be used to control database servers on Windows platforms. Database servers are also controlled by using the Mimer Controller utility. Refer to the Windows help provided with the Mimer Controller for further details. You must belong to the administrators group to control database servers. It is also possible to use the Windows commands NET START, NET STOP, etc. to control database server processes. |
MIMCONTROL is controlled by flagged information specified on the command-line.
The overall syntax for MIMCONTROL (expressed in short form Unix-style) is:
mimcontrol [-bcdegkAL] [-l chan] [-s [secs]] [-t [secs]] [-w [secs]] [-r type]
[database]
mimcontrol [--dcl] [--status] [--disable|--enable] [--generate] [--kill]
[--mcs] [--logout chan] [--start[=secs]] [--stop[=secs]] [--wait[=secs]]
[--dump] [--report=type] [database]
mimcontrol [-v|--version] | [-?|--help]
If MIMCONTROL is invoked without any options, it displays help options on the command-line.
|
Unix-style |
VMS-style |
Function |
|---|---|---|
|
-b --dcl |
/STATUS/DCL |
Output status information about the specified database server as a single-line list suitable for use in a script. For details about the output string resulting from this option, see Database Server Status. |
|
-c --status |
/STATUS |
Output status information about the specified database server. This option can be combined with the |
|
-d --disable |
/DISABLE |
Disable new user connections to the database server. Users already connected are not affected. This option can be combined with the |
|
-e --enable |
/ENABLE |
Enable new user connections to the database server. |
|
-? --help |
/HELP |
Show help text. |
|
-k --kill |
/KILL |
Kill the database server immediately. This should only be used in emergency situations when a normal stop using the -t option does not work. The next time the database is started, all databanks that were open at the time the server was killed will be automatically checked. Connected users will receive an error the next time they attempt to access the database. |
|
-l chan --logout=chan |
/LOGOUT=chan |
Force logout of the specified channel number. Use channel numbers displayed by the USERS option of the MIMINFO command, see The Users List. |
|
-g --generate |
/GENERATE |
Generate a default multidefs file. Win:This switch is not available in the Windows environment. |
|
-s [timeout] --start[=timeout] |
/START[=timeout] |
Start the database server. If the server does not become operational within the specified number of seconds, the server will be killed. Default timeout is 600 seconds. This option can be combined with the |
|
-t [timeout] --stop[=timeout] |
/STOP[=timeout] |
Stop a database server. Any remaining users will be logged out. If the server does not stop within the specified number of seconds, the server will be killed. The default timeout is 120 seconds. This option can be combined with the |
|
-w [timeout] --wait[=timeout] |
/WAIT[=timeout] |
Wait for all connected users to log out. If there are still users connected after the timeout period expires, the command fails. The timeout period should be given in seconds. If no timeout period is specified wait will be performed without any timeout. This option can be combined with the |
|
-A --dump |
/DUMP |
Create a dump directory and produce dumps of all internal database server areas to files in that directory. The files produced can be examined by using the MIMINFO -f command, see MIMINFO - System Information. |
|
-h --hold |
/HOLD |
VMS:The command MIMCONTROL/START starts a database server. Adding the /HOLD qualifier will cause the MIMCONTROL command to wait for the started server to stop before returning control to the DCL prompt. This simplifies writing scripts for automatic server restart. When the /HOLD qualifier is used, the MIMCONTROL command will exit with the final status code of the stopped database server process. |
|
[database] |
[database] |
Specifies the name of the database to access. If a database name is not specified, the default database will be controlled. The default database is determined by the setting of the MIMER_DATABASE environment variable. The DEFAULT setting in SQLHOSTS is not used for MIMCONTROL. |
|
Linux:The Unix-style command-line flags must be used on a Linux machine. Both short form switches (e.g. -s), and long form switches (e.g. --start) are supported. |
|
VMS:Either the Unix-style (short or long form) or the VMS-style command-line flags may be used on an OpenVMS machine – see the Mimer SQL VMS Guide for more details. |
|
Win:The Unix-style command-line flags can be used from a Command Prompt window. Both short form switches (e.g. -s), and long form switches (e.g. --start) are supported. |
The mimcontrol -b (MIMCONTROL/STATUS/DCL) command is a special form of the mimcontrol -c (MIMCONTROL/STATUS) command which returns the database server status information in the form of a single string containing a comma-separated list which is useful when writing scripts.
|
Linux:On Linux, the string returned from mimcontrol -b can be piped and processed as required. The Linux command cut can be used to extract the list elements, e.g. the following command which will print the second list element: # mimcontrol -b | cut -f2 -d ',' |
|
VMS:On OpenVMS, the MIMCONTROL command is silent and sets the DCL symbol MIMER_STATUS to the value of the status string. The lexical function F$ELEMENT() can be used to extract the list elements, e.g. the following command will extract the second list element: $LOGINS=F$ELEMENT(1,”,”,MIMER_STATUS) |
|
Win:On Windows, the string returned from mimcontrol -b can be piped and processed as required, depending on the script language being used. |
Status String Components
The status string has the following components:
server-state,logins,db-directory,connections,server-pid,start-date,bpool-size
Each component is described below:
|
Component |
Description |
|---|---|
|
server-state |
The server-state value shows the state of the database server. The value is one of: stopped, starting, running, stopping or crashing. |
|
logins |
The logins value is either: enabled, if new logins are permitted, or disabled, if the server has been ordered to reject new logins. |
|
db-directory |
The db-directory field shows the home directory for the database. |
|
connections |
The connections field shows the number of clients connected to the server. |
|
server-pid |
The server-pid field gives the process id of the database server process. |
|
start-date |
The start-date field gives the date and time when the database server was started. |
|
bpool-size |
The size of the buffer pool (in bytes). |
Note:If the database server is not operational, the status string will contain empty fields.
The parameter options can be combined in the following ways, examples are given in both VMS-style and Unix-style:
•Start a database server, but disallow new logins immediately:
MIMCONTROL/START/DISABLE db_server_name
mimcontrol -sd db_server_name
mimcontrol --start --disable db_server_name
•Start a database server and output a status message for the newly started server:
MIMCONTROL/START/STATUS db_server_name
mimcontrol -sc db_server_name
mimcontrol -start --status db_server_name
•Disable new user connections, then wait for up to three minutes for all users to log out.:
MIMCONTROL/DISABLE/WAIT=180 db_server_name
mimcontrol -dw 180 db_server_name
mimcontrol --disable --wait=180 db_server_name
The exit code from the MIMCONTROL command is success if all users logged out within the three minute timeout period.
If the timeout period expires and there are still users logged in on the system, the MIMCONTROL command will exit with a warning status code.
•The following command will wait for all users to log out of the system:
MIMCONTROL/WAIT/STOP db_server_name
mimcontrol -wt db_server_name
mimcontrol --wait --stop db_server_name
When all users are logged out, the system will be stopped. If the wait timeout period expires, the MIMCONTROL command will exit with a warning status code without stopping the system.
•The following command is similar to the previous one, but will ensure that no new users log in to the system while waiting for all users to log out:
MIMCONTROL/DISABLE/WAIT/STOP db_server_name
mimcontrol -dwt db_server_name
mimcontrol --disable --wait --stop db_server_name
MIMCONTROL returns a status code to the environment executing the command. The status code can be examined by scripts.
|
VMS:On OpenVMS, the status codes correspond to the OpenVMS condition code severity levels. Use the $SEVERITY symbol in DCL command procedures. |
The following return codes are used:
|
Linux/Windows |
VMS |
Usage |
|---|---|---|
|
0 (success) |
1 (success) |
This code is used when the MIMCONTROL command has executed all options with no problems. |
|
1 (warning) |
0 (warning) |
The warning code is used when there was a timeout in one of the options. The complete sequence of options may not have been executed. |
|
> 1 (error) |
2 (error) |
The error code is used when the specified command could not be executed at all. For instance, if there was an illegal combination of options, or if the specified database name was not found. If an error status code is returned, an informational error message will also be produced. |
MIMINFO is used to obtain information from a Mimer SQL database server which is useful for system control, system tuning and trouble-shooting analysis.
Information can be generated from an active Mimer SQL database server as well as from the SQLPOOL and bufferpool dump files produced by using MIMCONTROL, see MIMCONTROL - Controlling the Database Server.
The output from MIMINFO can be displayed on the screen and may also be directed to a file.
The following reports may be obtained from MIMINFO (further details on each report can be found in the sub-sections that follow):
|
Report |
Description |
|---|---|
|
this lists details of all the users currently connected. |
|
|
this provides information useful for monitoring performance parameters (MIMSERV). |
|
|
this produces a report which is useful to Mimer SQL support personnel when investigating system problems (MIMDUMP). |
|
|
displays SQLPOOL parameters |
|
|
displays version related information for a started server and its client connections |
The MIMINFO program is controlled by flagged information specified on the command-line.
The overall syntax for MIMINFO (expressed in short form Unix-style) is:
miminfo [-o file] [-m [-b bcbs]] | -p | -s | -u [-f | database]
miminfo [--output=file] [--mimdump [--bcblimit=bcbs]] | --performance |
--sqlpool | --users [--file | database]
miminfo [-v|--version] | [-?|--help
|
Unix-style |
VMS-style |
Function |
|
-b bcbs --bcblimit=bcbs |
/BCBLIMIT=bcbs |
Limits the displayed bcb list. Used together with the --mimdump option. |
|
-f --file |
/FILE |
Take information from a dump file (for a users list, a dump file called sqlpool.mdmp is expected to exist otherwise a dump file called bpool.mdmp is expected to exist) |
|
-m --mimdump |
/MIMDUMP |
Produce Bufferpool report (MIMDUMP) |
|
-o file --output=file |
/OUTPUT=file |
Send output to the specified file instead of to the screen |
|
-p --performance |
/PERFORMANCE |
Produce Performance report (MIMSERV) |
|
-s --sqlpool |
/SQLPOOL |
Display SQLPOOL parameters |
|
-u --users |
/USERS |
Display users list |
|
-V --version |
/VERSION |
List version information. |
|
-? --help |
/HELP |
Show help text. |
|
database |
database |
Take information from the specified database. If a database name is not specified, the default database will be accessed, see The Default Database. |
|
Linux:The Unix-style command-line flags must be used on a Linux machine. Both short form arguments (e.g. -u), and long form arguments (e.g. --users) are supported. |
|
VMS:Either the Unix-style or the VMS-style command-line flags may be used on an OpenVMS machine – see the Mimer SQL VMS Guide for more details. |
|
Win:The Unix-style command-line flags can be used if the miminfo program is run from a Command Prompt window. Both short form arguments (e.g. -u), and long form arguments (e.g. --users) are supported. The shortcut Mimer Info can also be used to run the program and interactive selections can then be made in the program. |
A detailed description of each of the MIMINFO reports follows.
A users list can be generated from an active database or from a dump file produced using MIMCONTROL.
miminfo [--output=file] --users [database] | --file
The users list shows the name of each ident connected to the database, the channel number used by the connection, the state of the connection, transaction number, the name of the operating user, the network communication protocol (or ‘local’) and node identification information for connected machine.
The channel number may be used in conjunction with MIMCONTROL to kill a user.
The following is an example of a users list report:
Username Channel State Trans. no OS user Prot From
============ ========== ===== ========== ============ ====== ====
SYSADM 16387 Busy 3 TCP 204.71.200.67
SYSADM 16388 Busy STELLA Local 00019120
Total of 2 users
The performance report can be used by the system administrator to monitor performance parameters during Mimer SQL use. The Performance report can be generated from an active database or from a dump file produced using MIMCONTROL.
miminfo [--output=file] --performance [database] | --file
The performance report presents five kinds of statistical information which may be useful for system tuning (statistics for page management, transactions, background threads, databank and table usage).
Note:When a performance report is used as an aid to system tuning, it is important that the report is generated when the database is in full use. The output from several executions over a period of a few hours or days can provide valuable information on fluctuations in system usage.
The performance report contains the following information:
•General Statistics
•Page Management Statistics
•Transaction Management Statistics
•Background Threads
•Databank Statistics
•Table Statistics
The following table lists the general statistics information available. Where applicable, we have provided a detailed description
|
Statistics |
Description |
|---|---|
|
Current date and time |
When the statistics was generated. |
|
Current MIMER/DB version |
Mimer server version. |
|
Starting date and time |
When the Mimer server was started. |
|
Current hardware and operating system |
Computer hardware and OS information. |
|
System status |
If the database server is in an error state, a database dump is usually made automatically. It can be made manually by using the -A option with MIMCONTROL. The dump directory created should be saved for use by Mimer SQL support personnel. The database server can then be restarted. The database server log, see Database Server Log, should also be inspected to help find the cause of the failure. |
|
Error count |
Number of errors that have been written to the database server log. This value should normally be zero. |
|
No. of request threads |
Number of request threads started, see Number of Request Threads. |
|
No. of background threads |
Number of background threads started, see Number of Background Threads. |
|
No. of I/O threads |
Number of I/O threads (typically zero on most machines where separate threads are not needed for I/O processing). |
|
Statistics |
Description |
|---|---|
|
No. of pages written to disk |
An indication of the frequency of disk update operations. |
|
No. of file extend operations |
The total number of times databank files have been dynamically extended since the latest startup. The value should preferably be as low as possible for performance reasons. It is possible to check databank size usage with the DESCRIBE command in Mimer BSQL. A databank can be extended by using the commands: |
|
Buffer size 4K (32K, 128K) |
The bufferpool is divided into a region with 4K buffers, one region with 32K buffers, and one region with 128K buffers. |
The following information is given for each region:
|
Statistics |
Description |
|---|---|
|
No. of page buffers |
This is the number of page buffers allocated to this bufferpool region. |
|
No. of page buffers per sorter |
Total number of Mimer SQL pages that a request thread performing a sort operation may utilize. |
|
No. of remaining sorters |
The initial value specifies the number of concurrent sort/merge steps that are allowed. |
|
No. of page partitions |
Each region in the bufferpool is divided into separate partitions. Each partition can be accessed concurrently by the Mimer SQL request threads. In tightly coupled multi-processor systems it is desirable, for performance reasons, to have at least as many partitions as there are CPUs. The number of partitions may be increased by increasing the region size. |
|
No. of page requests |
Total number of access operations to pages in the buffer region since the latest system start-up. |
|
No. of page faults |
Total number of page access requests that resulted in disk read operations. If this value is more than about 2% of the total number of page requests, performance may be improved significantly by increasing the bufferpool size. |
|
No. of pages swapped out |
Total number of pages which were written to disk when they were swapped out of the buffer region. |
Transaction Management Statistics
|
Statistics |
Description |
|---|---|
|
No. of transaction commits |
Total number of successful read/write transaction commits since the latest system start-up. |
|
No. of read commits |
Total number of successful read-only transaction commits since the latest system start-up. |
|
No. of transaction checks |
A high proportion of transaction checks in relation to the total number of transactions may indicate ill-designed application programs, with long transactions that are more likely to give rise to transaction conflicts. |
|
No. of transaction aborts |
Total number of transactions aborted by the optimistic concurrency protocol since the latest system start-up. User requested transaction aborts are not counted. |
|
No. of pending restarts |
This is an indication of how much information is stored in TRANSDB. Number of restarts is counted for each databank used in a transaction. This figure grows larger when shadows are set offline. If all databanks have been accessed and there are no offline shadows there should not be any pending restarts. |
|
Statistics |
Description |
|---|---|
|
SWA |
Background thread identifier. |
|
State |
State of the background thread. If the background thread is currently working with a transaction, active is displayed. If the background thread is not doing anything, inactive is displayed. I/O processing means that the background thread is flushing one or more transactions to disk. unused means that the background thread is allocated but not currently running (i.e. the thread is not started or closed down). |
|
Trans-no |
The number of the transaction currently being processed. |
|
Trans-count |
The number of transactions processed by the background thread. |
|
Pending background thread requests |
This indicates how many transactions have not yet been processed by the background threads. If there are too few background threads this value will grow. |
|
Application waiting for trans-no |
For certain operations (SET DATABANK OFFLINE, for example) the application has to wait for the background threads to complete their operations. If there are too few background threads, it may take some time before this operation is complete. By comparing this trans-no with the trans-no being handled by the background threads it is possible to see how many transactions are left before the operation is completed. |
|
Statistics |
Description |
|---|---|
|
Name |
The name of the databank or shadow. |
|
DBANKID, SEQNO |
Databank identification. These two values correspond to the columns DATABANK_SYSID and DATABANK_SEQNO in the data dictionary table SYSTEM.DATABANKS. |
|
Type |
The databank option WORK, TRANSACTION, LOG, or READ ONLY. See Re-creating TRANSDB, LOGDB and SQLDB. The SQLDB databank has the type TEMPORARY, and shadows have the type SHADOW. |
|
Users |
Internal user count. |
|
Access |
Access mode by which the databank was opened. The possible values are: Read, Write, Shared and Exclusive. If the databank is open but not referenced by any active statement, None is displayed. |
|
DB-Check |
The DB-Check field indicates the progress of a databank check. The possible values are Init, Working (foreground processing, typically index check), Wait B. (foreground ready, waiting for background entrance), Backgr. (background processing), Aborting, Aborted or Complete. After the DB-Check field, a field for additional information may show up. The values here can be the shadow state: offline, or the online backup states: backup in progress or backup completion. |
|
No. of databanks currently open |
A count of both databanks and shadows opened in the system. |
|
Max number of databanks open concurrently |
This is defined by a parameter in the local database definition, see The Local Database, or possibly by a limit in the operating system. |
|
Databank verification count |
Databank verification is automatically performed when a databank is re-opened without having been correctly closed. Each time this happens a log entry is written to the database server log. When a databank is verified the databank verification count is incremented. The count is cleared when the system is started, and a databank is only verified once per session. |
|
Running background verifications… |
Indicates the number of active databank verifications. |
|
Pending background verifications… |
Indicates the number of active databank verifications. |
|
Databank verification is only done on index pages… |
Indicates the databank verification mode as defined in the local database definition, see The Local Database |
|
Databank verification is performed on all pages… |
Indicates the databank verification mode as defined in the local database definition, see The Local Database |
|
Statistics |
Description |
|---|---|
|
No. of tables currently open |
This shows the number of tables open in both master and shadow databanks. Also included are the read and write sets used by each user. |
|
Max number of tables open concurrently |
This number is set as a parameter in the local database definition, The Local Database. |
The Bufferpool report is used by Mimer SQL support personnel for trouble-shooting when database problems are reported by customers.
miminfo [--output=file] --mimdump --file
SQL pool memory allocated is the amount of memory allocated from the operating system for the SQLPOOL. A part of that memory is in use by the server and is displayed on the row SQL pool memory used.
miminfo [--output=file] --sqlpool [database] | --file
The following is an example of an SQLPOOL report:
SQLPOOL report
==============
SQL pool memory allocated (KB): 1656
SQL pool memory used (KB): 554
A version report can be generated from an active database or from a dump file produced using MIMCONTROL. The report is generated using the following command:
miminfo [--output=file] --version [database] | --file
The version report displays information about the server and about each connected client.
The server information includes server type, version and platform.
The client information includes channel number, database API, version, network encryption and node identification information for the connected machine.
The channel number can be used to identify the connection when it appears in other reports.
The following is an example of a version report:
miminfo --version
M I M E R / M I M I N F O
Version 11.0.4A Sep 5 2020
Server type: Mimer SQL Experience
Server version: 11.0.4A
Server platform: Windows x64
Channel Client interface Version Platform Encrypt Prot From
======= ================ ======= ======== ======= ===== ====
1327109 ODBC 11.0.4A Windows x64 none Local 00001E2C
32772 Embedded SQL 11.0.4A VMS Itanium AES/GCM TCP Freke
Total of 2 users
The database server log lists startup and shutdown messages for the database server. It may also contain warning and error messages if such situations have been detected by the database server.
|
Linux + VMS:A log file called mimer.log is created when the database server is started for the first time. This file is located in the database home directory. In addition, you can set the Oper parameter in the MULTIDEFS file to send e-mails containing serious database server messages to relevant people. These messages always go to the system log as well. For more information, see The MULTIDEFS Parameter File. |
|
Win:Database server events are logged in the EventLog which may be examined using the Windows event viewer. |
Several Installations on One Machine
|
Linux:Under Linux, a host computer may have several Mimer SQL installations, of the same and different versions, installed simultaneously. If several Mimer SQL version 10 installations are available, only one of them can be linked to /usr/lib and /usr/bin at the same time. To access an installation that is not linked to these locations, the environment variables PATH and LD_LIBRARY_PATH (or corresponding to located shared libraries) must be used explicitly. For more information on environment variables, see Mimer SQL - Getting Started on Linux. |
|
VMS:Under OpenVMS, a host computer may have several Mimer SQL installations, of the same and different versions, installed simultaneously. |
|
Win:Only one Mimer SQL installation can exist on a computer running Windows. |