System Information - MIMINFO
The MIMINFO program 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 Controlling the Database Server - MIMCONTROL.
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):
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 Support personnel when investigating system problems (MIMDUMP). display SQLPOOL parameters
The MIMINFO program is controlled by flagged information specified on the command-line.
The overall syntax for MIMINFO (expressed in UNIX-style) is:miminfo [-o file] -u | -p | -s [database] miminfo [-o file] -u | -p | -m | -s -f
MIMINFO Command-line Arguments
Send output to the specified file instead of to the screen Display users list Produce Performance report (MIMSERV) Produce Bufferpool report (MIMDUMP) Display SQLPOOL parameters Take information from the specified database.If a database name is not specified, the default database will be accessed, see The Default Database. 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)
Win: The UNIX-style command-line flags can be used if the miminfo program is run from a Command Prompt window.
A detailed description of each of the MIMINFO reports follows.
The Users Listmiminfo [-o file] -u [database] | -f
A users list can be generated from an active database or from a dump file produced using MIMCONTROL.
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 184.108.40.206 SYSADM 16388 Busy SARA Local 00019120
The Performance Reportmiminfo [-o file] -p [database] | -f
The Performance report (MIMSERV) 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.
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
- Current date and time
- Current hardware and operating system
- Current MIMER/DB version
- Name of database
- Starting date and time
- System status
If system status is in an error state, a database dump is usually made automatically. It can be made manually by using the "-A" option in MIMCONTROL. The dump directory created should be saved for use by Mimer 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
- 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
Page Management Statistics
- No. of pages written to disk
- No. of file extend operations
- Buffer size 2K (16K, 64K)
- No. of page buffers
- No. of page buffers per sorter
- No. of remaining sorters
- 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
- 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. (For a recently started system this figure may be higher than 2% due to initial bufferpool read operations).
- No. of pages swapped out
Transaction Management Statistics
- No. of transaction commits
- No. of read commits
- 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
- No. of pending restarts
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 and "unused" means that the background thread is allocated but not currently running (i.e. the thread is not started or closed down).
- Pending background thread requests
- Application waiting for trans-no
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. (Note that the example report shown in Section 220.127.116.11 does not show this statistic because the application is not waiting for transactions to complete.)
- DBANKID, SEQNO
The databank option NULL, TRANS, or LOG. See Re-creating TRANSDB, LOGDB and SQLDB.
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.
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".
- Note: 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
- 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...
- Pending background verifications...
- Databank verification is only done on index pages...
- Databank verification is performed on all pages...
These information messages indicate the databank verification mode as defined in the local database definition, see The Local Database.
- No. of tables currently open
- Max number of tables open concurrently
This number is set as a parameter in the local database definition, The Local Database.
Performance Report Example
The following example output is from a Mimer SQL system which is not being limited by the bufferpool size (page faults, in 2K region, are only about 1% of page requests).
The number of transaction checks is low, indicating either that concurrent user update requests are infrequent or that transaction handling in application programs is well-designed.
The 64K region has not been used at all. It is possible to adjust the sizes of the different regions depending on the requirements of the system.
The error count has a value of 4 which indicates that 4 errors have been written to the database server log, see Database Server Log.
The databank SYMDB has an error status. The database server log will contain further information.
The shadow CASE5_S1 has been set offline as indicated at the end of the line describing the shadow.# miminfo -p hotel M I M E R / M I M I N F O Version 8.2.1A Oct 9 2000 *** MIMER/DB runtime statistics 2000-10-18 15:48:12 *** Hardware, operating system UNIX 32 Current MIMER/DB version is 8.2.1A Oct 9 2000 Database name: HOTEL System started at 2000-10-18 15:47:54 System status is up Error count : 4 No. of request threads : 2 No. of background threads : 2 No. of I/O threads : 0 Page management statistics ========================== No. of pages written to disk : 1774 No. of file extend operations : 0 Buffer size 2K -------------- No. of page buffers : 2614 No. of page buffers per sorter : 135 No. of remaining sorters : 2 No. of page partitions : 9 No. of page requests : 40859 No. of page faults : 432 1 % No. of pages swapped out : 0 Buffer size 16K --------------- No. of page buffers : 164 No. of page buffers per sorter : 72 No. of remaining sorters : 2 No. of page partitions : 1 No. of page requests : 44817 No. of page faults : 7 0 % No. of pages swapped out : 0 Buffer size 64K --------------- No. of page buffers : 54 No. of page buffers per sorter : 17 No. of remaining sorters : 2 No. of page partitions : 1 No. of page requests : 0 No. of page faults : 0 No. of pages swapped out : 0 Transaction management statistics ================================= No. of transaction commits : 1336 No. of read commits : 0 No. of transaction checks : 1436 No. of transaction aborts : 103 No. of pending restarts : 18 Background threads ================= SWA State Trans-no Trans-count 253 Inactive slave 0 11985 267 Inactive 0 1394 Pending background thread requests : 0 Databank statistics =================== Name DBANKID SEQNO Type Users Access DB-Check TESTDBM1 310 0 TRANS 1 Shared Complete TESTDBM2 311 0 TRANS 1 Shared Complete SYSDB 1 0 LOG 2 Shared Complete TRANSDB 2 0 TRANS 1 Shared Complete LOGDB 3 0 LOG 1 Shared Complete SQLDB 4 0 WORK 1 Shared Complete SYMDB 285 0 TRANS 1 Shared Complete Databank error status: -16142 CASE5 279 0 LOG 1 Shared Complete CASE5_S1 279 1 SHADOW 1 Shared Init Offline No. of databanks currently open : 6 Max number of databanks open concurrently: 100 Databank verification count : 0 Running background verifications : 0 Pending background verifications : 0 Databank verification is performed on all pages Table statistics ================ No. of tables currently open : 36 Max number of tables open concurrently : 4000 #
Bufferpool Reportmiminfo [-o file] -m -f
The Bufferpool report (MIMDUMP) is used by Mimer Support personnel for trouble-shooting when database problems are reported by customers.
SQLPOOL Reportmiminfo [-o file] -s [database] | -f
A SQLPOOL report is useful when the server is malfunctioning due to a memory problem. 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.
The following is an example of a SQLPOOL report:SQLPOOL report ============== SQL pool memory allocated (KB): 1656 SQL pool memory used (KB): 554
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40