| Firebird Documentation Index → Firebird 2.1 Release Notes → Administrative Features |
|
|
|
Table of Contents
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.
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.
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.
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.
- 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)
- 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-pathname 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).
- 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”.
- 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
- 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
- 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)
- 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)
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
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
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
Get isolation level of the current transaction
SELECT MON$ISOLATION_MODE
FROM MON$TRANSACTIONS
WHERE MON$TRANSACTION_ID = CURRENT_TRANSACTION
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
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
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
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.
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.
Improved in v2.1.3.
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.
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.
There is no relief for this condition in v.2.1.3. However, it has been improved for v.2.5.0.
|
| Firebird Documentation Index → Firebird 2.1 Release Notes → Administrative Features |