Firebird is gradually adding new features to assist in the administration of databases. Firebird 2.1 sees the introduction of a new set of system tables through which administrators can monitor transactions and statements that are active in a database. These facilities employ a new v.2.1 DDL feature, global temporary tables to provide snapshots.

Dmitry Yemanov

Firebird 2.1 introduces the ability to monitor server-side activity happening inside a particular database. The engine offers a set of so-called “virtual” tables that provides the user with a snapshot of the current activity within the given database.

The word “virtual” means that the table data is not materialised until explicitly asked for. However, the metadata of the virtual table is stable and can be retrieved from the schema.

Note: Virtual monitoring tables exist only in ODS 11.1 (and higher) databases, so a migration via backup/restore is required in order to use this feature.

The key term of the monitoring feature is an activity snapshot. It represents the current state of the database, comprising a variety of information about the database itself, active attachments and users, transactions, prepared and running statements, and more.

A snapshot is created the first time any of the monitoring tables is being selected from in the given transaction and it is preserved until the transaction ends, in order that multiple-table queries (e.g., master-detail ones) will always return a consistent view of the data.

In other words, the monitoring tables always behave like a snapshot table stability (“consistency”) transaction, even if the host transaction has been started with a lower isolation level.

To refresh the snapshot, the current transaction should be finished and the monitoring tables should be queried in a new transaction context.

• Access to the monitoring tables is available in both DSQL and PSQL.
• Complete database monitoring is available to SYSDBA and the database owner.
• Regular users are restricted to the information about their own attachments only — other attachments are invisible to them.

See also:

Transaction options explained

D. Yemanov

Under v.2.1.0 and 2.1.1, a non-SYSDBA user with more than one attachment could monitor only the one that was CURRENT_CONNECTION. From v.2.1.2 (Improvement CORE-2233), a non-SYSDBA user with more than one attachment can monitor all its own attachments.

back to top of page

• MON$DATABASE_NAME (database pathname or alias)
• MON$PAGE_SIZE (page size)
• MON$ODS_MAJOR (major ODS version)
• MON$ODS_MINOR (minor ODS version)
• MON$OLDEST_TRANSACTION (OIT number)
• MON$OLDEST_ACTIVE (OAT number)
• MON$OLDEST_SNAPSHOT (OST number)
• MON$NEXT_TRANSACTION (next transaction number)
• MON$PAGE_BUFFERS (number of pages allocated in the cache)
• MON$SQL_DIALECT (SQL dialect of the database)
• MON$SHUTDOWN_MODE (current shutdown mode)

0: online

1: multi-user shutdown

2: single-user shutdown

3: full shutdown

• MON$SWEEP_INTERVAL (sweep interval)
• MON$READ_ONLY (read-only flag)
• MON$FORCED_WRITES (sync writes flag)
• MON$RESERVE_SPACE (reserve space flag)
• MON$CREATION_DATE@@ (creation date/time)
• MON$PAGES (number of pages allocated on disk)
• MON$BACKUP_STATE (current physical backup state)

0: normal

1: stalled

2: merge

• MON$STAT_ID (statistics ID)

back to top of page

• MON$ATTACHMENT_ID (attachment ID)
• MON$SERVER_PID (server process ID)
• MON$STATE (attachment state)

0: idle

1: active

• MON$ATTACHMENT_NAME (connection string)
• MON$USER (user name)
• MON$ROLE (role name)
• MON$REMOTE_PROTOCOL (remote protocol name)
• MON$REMOTE_ADDRESS (remote address)
• MON$REMOTE_PID (remote client process ID)
• MON$REMOTE_PROCESS (remote client process pathname)
• MON$CHARACTER_SET_ID (attachment character set)
• MON$TIMESTAMP (connection date/time)
• MON$GARBAGE_COLLECTION (garbage collection flag)
• MON$STAT_ID (statistics ID)

• columns MON$REMOTE_PID and MON$REMOTE_PROCESS contains non-NULL values only if the client library is version 2.1 or higher.
• column MON$REMOTE_PROCESS can contain a non-path name value if an application has specified a custom process name via DPB.
• column MON$GARBAGE_COLLECTION indicates whether GC is allowed for this attachment (as specified via the DPB in isc_attach_database).

back to top of page

• MON$TRANSACTION_ID (transaction ID)
• MON$ATTACHMENT_ID (attachment ID)
• MON$STATE (transaction state)

0: idle (state after prepare, until execution begins)

1: active (state during execution and fetch. Idle state returns after cursor is closed)

• MON$TIMESTAMP (transaction start date/time)
• MON$TOP_TRANSACTION (top transaction)
• MON$OLDEST_TRANSACTION (local OIT number)
• MON$OLDEST_ACTIVE (local OAT number)
• MON$ISOLATION_MODE (isolation mode)

0: consistency

1: concurrency

2: read committed record version

3: read committed no record version

• MON$LOCK_TIMEOUT (lock timeout)

-1: infinite wait

0: no wait

N: timeout N

• MON$READ_ONLY (read-only flag)
• MON$AUTO_COMMIT (auto-commit flag)
• MON$AUTO_UNDO (auto-undo flag)
• MON$STAT_ID (statistics ID)

• MON$TOP_TRANSACTION is the upper limit used by the sweeper transaction when advancing the global OIT. All transactions above this threshold are considered active. It is normally equivalent to the MON$TRANSACTION_ID but COMMIT RETAINING or ROLLBACK RETAINING will cause MON$TOP_TRANSACTION to remain unchanged (“stuck”) when the transaction ID is incremented.
• MON$AUTO_UNDO indicates the auto-undo status set for the transaction, i.e., whether a transaction-level savepoint was created. The existence of the transaction-level savepoint allows changes to be undone if ROLLBACK is called and the transaction is then just committed. If this savepoint does not exist, or it does exist but the number of changes is very large, then an actual ROLLBACK is executed and the the transaction is marked in the TIP as “dead”.

back to top of page

• MON$STATEMENT_ID (statement ID)
• MON$ATTACHMENT_ID (attachment ID)
• MON$TRANSACTION_ID (transaction ID)
• MON$STATE (statement state)

0: idle

1: active

• MON$TIMESTAMP (statement start date/time)
• MON$SQL_TEXT (statement text, if appropriate)
• MON$STAT_ID (statistics ID)

• column MON$SQL_TEXT contains NULL for GDML statements.
• columns MON$TRANSACTION_ID and MON$TIMESTAMP contain valid values for active statements only.
• The execution plan and the values of parameters are not available.

back to top of page

• MON$CALL_ID (call ID)
• MON$STATEMENT_ID (top-level DSQL statement ID)
• MON$CALLER_ID (caller request ID)
• MON$OBJECT_NAME (PSQL object name)
• MON$OBJECT_TYPE (PSQL object type)
• MON$TIMESTAMP (request start date/time)
• MON$SOURCE_LINE (SQL source line number)
• MON$SOURCE_COLUMN (SQL source column number)
• MON$STAT_ID (statistics ID)

• column MON$STATEMENT_ID groups call stacks by the top-level DSQL statement that initiated the call chain. This ID represents an active statement record in the table MON$STATEMENTS.
• columns MON$SOURCE_LINE and MON$SOURCE_COLUMN contain line/column information related to the PSQL statement currently being executed.

back to top of page

• MON$STAT_ID (statistics ID)
• MON$STAT_GROUP (statistics group)

0: database

1: attachment

2: transaction

3: statement

4: call

• MON$PAGE_READS (number of page reads)
• MON$PAGE_WRITES (number of page writes)
• MON$PAGE_FETCHES (number of page fetches)
• MON$PAGE_MARKS (number of pages with changes pending)

See also:

Firebird Database Cache Buffer: Using MON$IOSTATS

back to top of page

• MON$STAT_ID (statistics ID)
• MON$STAT_GROUP (statistics group)

0: database

1: attachment

2: transaction

3: statement

4: call

• MON$RECORD_SEQ_READS (number of records read sequentially)
• MON$RECORD_IDX_READS (number of records read via an index)
• MON$RECORD_INSERTS (number of inserted records)
• MON$RECORD_UPDATES (number of updated records)
• MON$RECORD_DELETES (number of deleted records)
• MON$RECORD_BACKOUTS (number of records where a new primary record version or a change to an existing primary record version is backed out due to rollback or savepoint undo.)
• MON$RECORD_PURGES (number of records where record version chain is being purged of versions no longer needed by OAT or younger transactions.)
• MON$RECORD_EXPUNGES (number of records where record version chain is being deleted due to deletions by transactions older than OAT.)

Note: Textual descriptions of all “state” and “mode” values can be found in the system table RDB$TYPES.

Creation of a snapshot is usually quite a fast operation, but some delay could be expected under high load (especially in the Classic Server). For guidelines to minimising the impact of monitoring under such conditions, see Performance Guidelines, below.

A valid database connection is required in order to retrieve the monitoring data. The monitoring tables return information about the attached database only. If multiple databases are being accessed on the server, each of them has to be connected to and monitored separately.

The system variables CURRENT_CONNECTION and CURRENT_TRANSACTION could be used to select data about the caller's current connection and transaction respectively. These variables correspond to the ID columns of the appropriate monitoring tables.

Examples

1. Retrieve IDs of all CS processes loading CPU at the moment

SELECT MON$SERVER_PID
  FROM MON$ATTACHMENTS
  WHERE MON$ATTACHMENT_ID <> CURRENT_CONNECTION
  AND MON$STATE = 1

2. Retrieve information about client applications

SELECT MON$USER, MON$REMOTE_ADDRESS,
  MON$REMOTE_PID,
  MON$TIMESTAMP
FROM MON$ATTACHMENTS 
WHERE MON$ATTACHMENT_ID <> CURRENT_CONNECTION

3. Get isolation level of the current transaction

SELECT MON$ISOLATION_MODE
  FROM MON$TRANSACTIONS
  WHERE MON$TRANSACTION_ID = CURRENT_TRANSACTION

4. Get statements that are currently active

SELECT ATT.MON$USER,
       ATT.MON$REMOTE_ADDRESS,
       STMT.MON$SQL_TEXT,
      STMT.MON$TIMESTAMP
FROM MON$ATTACHMENTS ATT
  JOIN MON$STATEMENTS STMT
    ON ATT.MON$ATTACHMENT_ID = STMT.MON$ATTACHMENT_ID
WHERE ATT.MON$ATTACHMENT_ID <> CURRENT_CONNECTION
  AND STMT.MON$STATE = 1

5. Retrieve call stacks for all connections

WITH RECURSIVE HEAD AS
  (
     SELECT CALL.MON$STATEMENT_ID,
            CALL.MON$CALL_ID,
            CALL.MON$OBJECT_NAME, 
            CALL.MON$OBJECT_TYPE
     FROM MON$CALL_STACK CALL
       WHERE CALL.MON$CALLER_ID IS NULL
     UNION ALL
       SELECT CALL.MON$STATEMENT_ID,
              CALL.MON$CALL_ID,
              CALL.MON$OBJECT_NAME,
              CALL.MON$OBJECT_TYPE
       FROM MON$CALL_STACK CALL
         JOIN HEAD
           ON CALL.MON$CALLER_ID = HEAD.MON$CALL_ID
)
SELECT MON$ATTACHMENT_ID,
       MON$OBJECT_NAME,
       MON$OBJECT_TYPE
FROM HEAD
 JOIN MON$STATEMENTS STMT
    ON STMT.MON$STATEMENT_ID = HEAD.MON$STATEMENT_ID
   WHERE STMT.MON$ATTACHMENT_ID <> CURRENT_CONNECTION

back to top of page

Runaway and long-running queries can now be cancelled from a separate connection.

There is no API function call directed at this feature. It will be up to the SysAdmin (SYSDBA or owner) to make use of the data available in the monitoring tables and devise an appropriate mechanism for reining in the rogue statements.

Example

As a very rough example, the following statement will kill all statements currently running in the database, other than any that belong to the separate connection that the SysAdmin is using himself:

delete from mon$statements
  where mon$attachment_id <> current_connection

back to top of page

The monitoring is implemented around two cornerstones: shared memory and notifications.

All server processes share some region of memory where the information about current activity is stored. The information consists of multiple variable-length items describing the details of various activities. All items belonging to a particular process are grouped into a single cluster, enabling them to be processed as a whole.

Collection of the monitoring information and population of the tables do not happen in real time. Instead, server processes write their data into the shared memory only when explicitly asked to. Upon writing, the newer clusters supersede the old ones. Then, when the shared memory region is being read, the reading process scans all the clusters and performs garbage collection, removing clusters that belong to dead processes and compacting the shared memory space.

Every server process has a flag that indicates its capability to react to someone's monitoring request as soon as it arrives. When some user connection runs a query against some monitoring table, the worker process of that connection sends a broadcast notification to other processes, requesting an up-to-date information. Those processes react to this request by updating their clusters inside the shared memory region and clearing their ready flags.

Once every notified process has finished its update, the requesting process reads the shared memory region, filters the necessary tags according to its user permissions, transforms the internal representation into records and fields and populates the in-memory monitoring tables cache.

Any processes that have been idle since the last monitoring exchange have their ready flag clear, indicating that they have nothing to update in the shared memory. The clear flag means they will be ignored by the notification broadcast and will be exempt from making the next round trip.

The ready flag is set as soon as something significant changes inside the process and the process starts responding to monitoring requests again.

While coordinating the write/read operations, the requester holds an exclusive lock that affects all user connections that are currently active as well as any connections with attachments in the process of being established.

Multiple simultaneous monitoring requests are serialized.

1. In a heavily loaded system running Classic, monitoring requests may take noticeable time to execute. In the meantime, other activity (both running statements and new connection attempts) may be blocked until the monitoring request completes.

Note: Improved in v.2.1.3.

2. Sometimes, an out-of-memory condition can cause monitoring requests to fail, or cause other worker processes to swap. A major source of this problem is the fact that every record in MON$STATEMENTS has a blob MON$SQL_TEXT which is created for the duration of the monitoring transaction.

In v.2.1.x, every blob occupies <page size> bytes of memory, even if its actual content is smaller. When the number of prepared statements in the system is huge, it becomes possible to exhaust memory and get this failure.

3. Another possible source of this memory exhaustion could be the temporary (in practice, very short-lived) growth of the transaction pool into which the monitoring data are cached while the clusters are being merged into a single fragment.

Note: There is no relief for this condition in v.2.1.3. However, it has been improved for v.2.5.0.

back to top of page

More context information about the server and database ('SYSTEM') is available via SELECT calls to the RDB$GET_CONTEXT function, including the engine version.

Example

SELECT RDB$GET_CONTEXT('SYSTEM', 'ENGINE_VERSION')
  FROM RDB$DATABASE

For detailed information about using these context calls, refer to the Firebird v.2.0.1 Release Notes.