Monitoring Improvements

Dmitry Yemanov

Extended Access for Ordinary Users
New MON$ Metadata for ODS 11.2 Databases
Usage Notes

Firebird 2.5 sees the enhancement of the “MON$” database monitoring features introduced in V.2.1, with new tables delivering data about context variables and memory usage in ODS 11.2 and higher databases. Also, in these databases, it becomes possible to terminate a client connection from another connection through the MON$ structures.

Extended Access for Ordinary Users

The original design allowed non-privileged database users to see monitoring information pertaining only to their CURRENT_CONNECTION. Now they can request information for any attachment that was authenticated using the same user name.

Tracker reference CORE-2233.

Notes

For an application architecture that entails a middleware tier logging in multiple times concurrently with the same user name on behalf of different end users, consideration should be given to the impact on performance and privacy of exposing the monitoring features to the end users.
The same extension was implemented in V.2.1.2.

New MON$ Metadata for ODS 11.2 Databases

Note

For the ODS 11.1 metadata please refer to the V.2.1 documentation.

Character Set Change for MON$ Metadata

The system domain RDB$FILE_NAME2, that is used to define those columns in the MON$ tables that pertain to file specifications has been altered from CHARACTER SET NONE to CHARACTER SET UNICODE_FSS. The columns currently affected are MON$DATABASE_NAME, MON$ATTACHMENT_NAME and MON$REMOTE_PROCESS. This change makes the affected data consistent with the updated v.2.5 handling of filespec and other character parameter items in the DPB.

(Tracker entry CORE-2551, A. dos Santos Fernandes)

MON$MEMORY_USAGE (current memory usage):       - MON$STAT_ID (statistics ID)
      - MON$STAT_GROUP (statistics group)
          0: database
          1: attachment
          2: transaction
          3: statement
          4: call
      - MON$MEMORY_USED (number of bytes currently in use)
          High-level memory allocations performed by the engine from its pools.
          Can be useful for tracing memory leaks and for investigating unusual
          memory consumption and the attachments, procedures, etc. that might
          be responsible for it.
      - MON$MEMORY_ALLOCATED (number of bytes currently allocated at the OS level)
          Low-level memory allocations performed by the Firebird memory manager.
          These are bytes actually allocated by the operating system, so it enables
          the physical memory consumption to be monitored.


Note

Not all records have non-zero values. On the whole, only MON$DATABASE and memory-bound objects point to non-zero “allocated” values. Small allocations are not allocated at this level, being redirected to the database memory pool instead.

      - MON$MAX_MEMORY_USED (maximum number of bytes used by this object)
      - MON$MAX_MEMORY_ALLOCATED (maximum number of bytes allocated from
          the operating system by this object)
MON$CONTEXT_VARIABLES (known context variables):       - MON$ATTACHMENT_ID (attachment ID)
          Contains a valid ID only for session-level context variables.
          Transaction-level variables have this field set to NULL.
      - MON$TRANSACTION_ID (transaction ID)
          Contains a valid ID only for transaction-level context variables.
          Session-level variables have this field set to NULL.
      - MON$VARIABLE_NAME (name of context variable)
      - MON$VARIABLE_VALUE (value of context variable)

Memory Usage in MON$STATEMENTS and MON$STATE

Memory usage statistics in MON$STATEMENTS and MON$STATE represent actual CPU consumption.

Tracker reference: CORE-1583)

Usage Notes

Examples

“Top 10” statements ranked according to their memory usage:

SELECT FIRST 10
  STMT.MON$ATTACHMENT_ID,
  STMT.MON$SQL_TEXT,
  MEM.MON$MEMORY_USED
FROM MON$MEMORY_USAGE MEM
  NATURAL JOIN MON$STATEMENTS STMT
  ORDER BY MEM.MON$MEMORY_USED DESC

To enumerate all session-level context variables for the current connection:

SELECT
  VAR.MON$VARIABLE_NAME,
  VAR.MON$VARIABLE_VALUE
FROM MON$CONTEXT_VARIABLES VAR
  WHERE VAR.MON$ATTACHMENT_ID = CURRENT_CONNECTION

Terminating a Client

The MON$ structures are, by design, read-only. Thus, user DML operations on them are prohibited. However, a mechanism is built in to allow deleting (only) of records in the MON$STATEMENTS and MON$ATTACHMENTS tables. The effect of this mechanism is to make it possible, respectively, to cancel running statements and, for ODS 11.2 databases, to terminate client sessions.

To cancel all current activity for a specified connection:

DELETE FROM MON$STATEMENTS
  WHERE MON$ATTACHMENT_ID = 32

To disconnect all clients except the “Me” connection:

DELETE FROM MON$ATTACHMENTS
  WHERE MON$ATTACHMENT_ID <> CURRENT_CONNECTION

Note

- A statement cancellation attempt becomes a void operation (“no-op”) if the client has no statements currently running.
- Upon cancellation, the execute/fetch API call returns the isc_cancelled error code.
- Subsequent operations are allowed.
- Any active transactions in the connection being terminated will have their activities cancelled immediately and they are rolled back.
- Once terminated, the client session receives the isc_att_shutdown error code.
- Subsequent attempts to use this connection handle will cause network read/write errors.