| 1 |
----------------- |
| 2 |
Monitoring tables |
| 3 |
----------------- |
| 4 |
|
| 5 |
Function: |
| 6 |
Allow to monitor server-side activity happening inside a particular database. |
| 7 |
|
| 8 |
Concept: |
| 9 |
The engine offers a set of so called "virtual" tables that provides the user |
| 10 |
with a snapshot of the current activity within the given database. The word |
| 11 |
"virtual" means that the table data doesn't exist until explicitly asked for. |
| 12 |
However, its metadata is stable and can be retrieved from the schema. Virtual |
| 13 |
monitoring tables exist only in ODS 11.1 (and higher) databases, so a |
| 14 |
migration via backup/restore is required in order to use this feature. |
| 15 |
|
| 16 |
The key term of the monitoring feature is an activity snapshot. It represents |
| 17 |
the current state of the database, consisting of various information about |
| 18 |
the database itself, active attachments and users, transactions, prepared and |
| 19 |
running statements, etc. A snapshot is created the first time any of the |
| 20 |
monitoring tables is being selected from in the given transaction and it's |
| 21 |
preserved until the transaction ends, so multiple queries (e.g. master-detail |
| 22 |
ones) will always return the consistent view of the data. In other words, the |
| 23 |
monitoring tables always behave like a snapshot (aka consistency) transaction, |
| 24 |
even if the host transaction has been started with another isolation level. |
| 25 |
To refresh the snapshot, the current transaction should be finished and the |
| 26 |
monitoring tables should be queried in the new transaction context. Creation |
| 27 |
of a snapshot is usually quite fast operation, but some delay should be |
| 28 |
expected under high load (especially in the Classic Server). |
| 29 |
|
| 30 |
A valid database connection is required in order to retrieve the monitoring |
| 31 |
data. The monitoring tables return information about the attached database |
| 32 |
only. If multiple databases are being accessed on the server, each of them |
| 33 |
has to be connected to and monitored separately. |
| 34 |
|
| 35 |
System variables CURRENT_CONNECTION and CURRENT_TRANSACTION could be used |
| 36 |
to select data about the current (for the caller) connection and transaction |
| 37 |
respectively. These variables correspond to the ID columns of the appropriate |
| 38 |
monitoring tables. |
| 39 |
|
| 40 |
Security: |
| 41 |
Complete database monitoring is available to SYSDBA and a database owner. |
| 42 |
Regular users are restricted to the information about their own attachments |
| 43 |
only (other attachments are invisible for them). |
| 44 |
|
| 45 |
Author: |
| 46 |
Dmitry Yemanov <dimitr at firebirdsql dot org> |
| 47 |
|
| 48 |
Scope: |
| 49 |
DSQL and PSQL |
| 50 |
|
| 51 |
MON$DATABASE (connected database) |
| 52 |
- MON$DATABASE_NAME (database pathname or alias) |
| 53 |
- MON$PAGE_SIZE (page size) |
| 54 |
- MON$ODS_MAJOR (major ODS version) |
| 55 |
- MON$ODS_MINOR (minor ODS version) |
| 56 |
- MON$OLDEST_TRANSACTION (OIT number) |
| 57 |
- MON$OLDEST_ACTIVE (OAT number) |
| 58 |
- MON$OLDEST_SNAPSHOT (OST number) |
| 59 |
- MON$NEXT_TRANSACTION (next transaction number) |
| 60 |
- MON$PAGE_BUFFERS (number of pages allocated in the cache) |
| 61 |
- MON$SQL_DIALECT (SQL dialect of the database) |
| 62 |
- MON$SHUTDOWN_MODE (current shutdown mode) |
| 63 |
0: online |
| 64 |
1: multi-user shutdown |
| 65 |
2: single-user shutdown |
| 66 |
3: full shutdown |
| 67 |
- MON$SWEEP_INTERVAL (sweep interval) |
| 68 |
- MON$READ_ONLY (read-only flag) |
| 69 |
- MON$FORCED_WRITES (sync writes flag) |
| 70 |
- MON$RESERVE_SPACE (reserve space flag) |
| 71 |
- MON$CREATION_DATE (creation date/time) |
| 72 |
- MON$PAGES (number of pages allocated on disk) |
| 73 |
- MON$STAT_ID (statistics ID) |
| 74 |
- MON$BACKUP_STATE (current physical backup state) |
| 75 |
0: normal |
| 76 |
1: stalled |
| 77 |
2: merge |
| 78 |
- MON$CRYPT_PAGE (number of page being encrypted) |
| 79 |
- MON$OWNER (database owner name) |
| 80 |
|
| 81 |
MON$ATTACHMENTS (connected attachments) |
| 82 |
- MON$ATTACHMENT_ID (attachment ID) |
| 83 |
- MON$SERVER_PID (server process ID) |
| 84 |
- MON$STATE (attachment state) |
| 85 |
0: idle |
| 86 |
1: active |
| 87 |
- MON$ATTACHMENT_NAME (connection string) |
| 88 |
- MON$USER (user name) |
| 89 |
- MON$ROLE (role name) |
| 90 |
- MON$REMOTE_PROTOCOL (remote protocol name) |
| 91 |
- MON$REMOTE_ADDRESS (remote address) |
| 92 |
- MON$REMOTE_PID (remote client process ID) |
| 93 |
- MON$REMOTE_PROCESS (remote client process pathname) |
| 94 |
- MON$CHARACTER_SET_ID (attachment character set) |
| 95 |
- MON$TIMESTAMP (connection date/time) |
| 96 |
- MON$GARBAGE_COLLECTION (garbage collection flag) |
| 97 |
- MON$STAT_ID (statistics ID) |
| 98 |
- MON$CLIENT_VERSION (version of the client library) |
| 99 |
- MON$REMOTE_VERSION (version of the remote protocol) |
| 100 |
- MON$REMOTE_HOST (remote host name) |
| 101 |
- MON$REMOTE_OS_USER (remote OS user name) |
| 102 |
- MON$AUTH_METHOD (authentication method used for connection) |
| 103 |
- MON$SYSTEM_FLAG (system flag) |
| 104 |
0: user attachment |
| 105 |
1: system attachment |
| 106 |
|
| 107 |
MON$TRANSACTIONS (started transactions) |
| 108 |
- MON$TRANSACTION_ID (transaction ID) |
| 109 |
- MON$ATTACHMENT_ID (attachment ID) |
| 110 |
- MON$STATE (transaction state) |
| 111 |
0: idle |
| 112 |
1: active |
| 113 |
- MON$TIMESTAMP (transaction start date/time) |
| 114 |
- MON$TOP_TRANSACTION (top transaction) |
| 115 |
- MON$OLDEST_TRANSACTION (local OIT number) |
| 116 |
- MON$OLDEST_ACTIVE (local OAT number) |
| 117 |
- MON$ISOLATION_MODE (isolation mode) |
| 118 |
0: consistency |
| 119 |
1: concurrency |
| 120 |
2: read committed record version |
| 121 |
3: read committed no record version |
| 122 |
- MON$LOCK_TIMEOUT (lock timeout) |
| 123 |
-1: infinite wait |
| 124 |
0: no wait |
| 125 |
N: timeout N |
| 126 |
- MON$READ_ONLY (read-only flag) |
| 127 |
- MON$AUTO_COMMIT (auto-commit flag) |
| 128 |
- MON$AUTO_UNDO (auto-undo flag) |
| 129 |
- MON$STAT_ID (statistics ID) |
| 130 |
|
| 131 |
MON$STATEMENTS (prepared statements) |
| 132 |
- MON$STATEMENT_ID (statement ID) |
| 133 |
- MON$ATTACHMENT_ID (attachment ID) |
| 134 |
- MON$TRANSACTION_ID (transaction ID) |
| 135 |
- MON$STATE (statement state) |
| 136 |
0: idle |
| 137 |
1: active |
| 138 |
- MON$TIMESTAMP (statement start date/time) |
| 139 |
- MON$SQL_TEXT (statement text, if appropriate) |
| 140 |
- MON$STAT_ID (statistics ID) |
| 141 |
|
| 142 |
MON$CALL_STACK (call stack of active PSQL requests) |
| 143 |
- MON$CALL_ID (call ID) |
| 144 |
- MON$STATEMENT_ID (top-level DSQL statement ID) |
| 145 |
- MON$CALLER_ID (caller request ID) |
| 146 |
- MON$OBJECT_NAME (PSQL object name) |
| 147 |
- MON$OBJECT_TYPE (PSQL object type) |
| 148 |
- MON$TIMESTAMP (request start date/time) |
| 149 |
- MON$SOURCE_LINE (SQL source line number) |
| 150 |
- MON$SOURCE_COLUMN (SQL source column number) |
| 151 |
- MON$STAT_ID (statistics ID) |
| 152 |
- MON$PACKAGE_NAME (PSQL object package name) |
| 153 |
|
| 154 |
MON$IO_STATS (I/O statistics) |
| 155 |
- MON$STAT_ID (statistics ID) |
| 156 |
- MON$STAT_GROUP (statistics group) |
| 157 |
0: database |
| 158 |
1: attachment |
| 159 |
2: transaction |
| 160 |
3: statement |
| 161 |
4: call |
| 162 |
- MON$PAGE_READS (number of page reads) |
| 163 |
- MON$PAGE_WRITES (number of page writes) |
| 164 |
- MON$PAGE_FETCHES (number of page fetches) |
| 165 |
- MON$PAGE_MARKS (number of page marks) |
| 166 |
|
| 167 |
MON$RECORD_STATS (record-level statistics) |
| 168 |
- MON$STAT_ID (statistics ID) |
| 169 |
- MON$STAT_GROUP (statistics group) |
| 170 |
0: database |
| 171 |
1: attachment |
| 172 |
2: transaction |
| 173 |
3: statement |
| 174 |
4: call |
| 175 |
- MON$RECORD_SEQ_READS (number of records read sequentially) |
| 176 |
- MON$RECORD_IDX_READS (number of records read via an index) |
| 177 |
- MON$RECORD_INSERTS (number of inserted records) |
| 178 |
- MON$RECORD_UPDATES (number of updated records) |
| 179 |
- MON$RECORD_DELETES (number of deleted records) |
| 180 |
- MON$RECORD_BACKOUTS (number of backed out records) |
| 181 |
- MON$RECORD_PURGES (number of purged records) |
| 182 |
- MON$RECORD_EXPUNGES (number of expunged records) |
| 183 |
|
| 184 |
MON$MEMORY_USAGE (current memory usage) |
| 185 |
- MON$STAT_ID (statistics ID) |
| 186 |
- MON$STAT_GROUP (statistics group) |
| 187 |
0: database |
| 188 |
1: attachment |
| 189 |
2: transaction |
| 190 |
3: statement |
| 191 |
4: call |
| 192 |
- MON$MEMORY_USED (number of bytes currently in use) |
| 193 |
- MON$MEMORY_ALLOCATED (number of bytes currently allocated at the OS level) |
| 194 |
- MON$MAX_MEMORY_USED (maximum number of bytes used by this object) |
| 195 |
- MON$MAX_MEMORY_ALLOCATED (maximum number of bytes allocated from OS by this object) |
| 196 |
|
| 197 |
MON$CONTEXT_VARIABLES (known context variables) |
| 198 |
- MON$ATTACHMENT_ID (attachment ID) |
| 199 |
- MON$TRANSACTION_ID (transaction ID) |
| 200 |
- MON$VARIABLE_NAME (name of context variable) |
| 201 |
- MON$VARIABLE_VALUE (value of context variable) |
| 202 |
|
| 203 |
Notes: |
| 204 |
1) Textual descriptions of all "state" and "mode" values can be found |
| 205 |
in the system table RDB$TYPES |
| 206 |
|
| 207 |
2) For table MON$ATTACHMENTS: |
| 208 |
- columns MON$REMOTE_PID and MON$REMOTE_PROCESS contains non-NULL values |
| 209 |
only if the client library has version 2.1 or higher |
| 210 |
- column MON$REMOTE_PROCESS can contain a non-pathname value |
| 211 |
if an application has specified a custom process name via DPB |
| 212 |
|
| 213 |
3) For table MON$STATEMENTS: |
| 214 |
- column MON$SQL_TEXT contains NULL for GDML statements |
| 215 |
- columns MON$TRANSACTION_ID and MON$TIMESTAMP contain valid values |
| 216 |
for active statements only |
| 217 |
|
| 218 |
4) For table MON$CALL_STACK: |
| 219 |
- column MON$STATEMENT_ID groups call stacks by the top-level DSQL statement |
| 220 |
that initiated the call chain. This ID represents an active statement |
| 221 |
record in the table MON$STATEMENTS. |
| 222 |
- columns MON$SOURCE_LINE and MON$SOURCE_COLUMN contain line/column information |
| 223 |
related to the PSQL statement being currently executed |
| 224 |
|
| 225 |
5) For table MON$MEMORY_USAGE: |
| 226 |
- the "used" values represent high-level memory allocations, i.e. the ones |
| 227 |
performed by the engine from its pools. They are useful to investigate unexpected |
| 228 |
memory consumptions and find the "guilty" objects (attachments, procedures, etc), |
| 229 |
as well as trace memory leaks. |
| 230 |
- the "allocated" values represent low-level memory allocations, i.e. the ones |
| 231 |
performed by the Firebird memory manager. This means bytes really allocated from OS, |
| 232 |
thus allowing to monitor the physical memory consumption. Please note that not every |
| 233 |
record has these columns populated with non-zero values. Small allocations don't go |
| 234 |
to the OS level, they're redirected to the database memory pool instead. So usually |
| 235 |
only MON$DATABASE and memory-bound objects point to non-zero "allocated" values. |
| 236 |
- the counter set linked to a record in MON$DATABASE reports the memory shared among |
| 237 |
all attachments. In Classic and SuperClassic, these counters are zero meaning no |
| 238 |
shared cache in these architectures. |
| 239 |
|
| 240 |
6) For table MON$CONTEXT_VARIABLES: |
| 241 |
- column MON$ATTACHMENT_ID contains a valid ID only for session-level context variables. |
| 242 |
Transaction-level ones have this field set to NULL. |
| 243 |
- column MON$TRANSACTION_ID contains a valid ID only for transaction-level context variables. |
| 244 |
Session-level ones have this field set to NULL. |
| 245 |
|
| 246 |
Example(s): |
| 247 |
1) Retrieve IDs of all CS processes loading CPU at the moment: |
| 248 |
SELECT MON$SERVER_PID |
| 249 |
FROM MON$ATTACHMENTS |
| 250 |
WHERE MON$ATTACHMENT_ID <> CURRENT_CONNECTION |
| 251 |
AND MON$STATE = 1 |
| 252 |
|
| 253 |
2) Retrieve information about client applications: |
| 254 |
SELECT MON$USER, MON$REMOTE_ADDRESS, MON$REMOTE_PID, MON$TIMESTAMP |
| 255 |
FROM MON$ATTACHMENTS |
| 256 |
WHERE MON$ATTACHMENT_ID <> CURRENT_CONNECTION |
| 257 |
|
| 258 |
3) Get isolation level of the current transaction: |
| 259 |
SELECT MON$ISOLATION_MODE |
| 260 |
FROM MON$TRANSACTIONS |
| 261 |
WHERE MON$TRANSACTION_ID = CURRENT_TRANSACTION |
| 262 |
|
| 263 |
4) Get statements that are currently active: |
| 264 |
SELECT ATT.MON$USER, ATT.MON$REMOTE_ADDRESS, STMT.MON$SQL_TEXT, STMT.MON$TIMESTAMP |
| 265 |
FROM MON$ATTACHMENTS ATT |
| 266 |
JOIN MON$STATEMENTS STMT ON ATT.MON$ATTACHMENT_ID = STMT.MON$ATTACHMENT_ID |
| 267 |
WHERE ATT.MON$ATTACHMENT_ID <> CURRENT_CONNECTION |
| 268 |
AND STMT.MON$STATE = 1 |
| 269 |
|
| 270 |
5) Retrieve call stacks for all connections: |
| 271 |
WITH RECURSIVE |
| 272 |
HEAD AS |
| 273 |
( |
| 274 |
SELECT CALL.MON$STATEMENT_ID, CALL.MON$CALL_ID, CALL.MON$OBJECT_NAME, CALL.MON$OBJECT_TYPE |
| 275 |
FROM MON$CALL_STACK CALL |
| 276 |
WHERE CALL.MON$CALLER_ID IS NULL |
| 277 |
UNION ALL |
| 278 |
SELECT CALL.MON$STATEMENT_ID, CALL.MON$CALL_ID, CALL.MON$OBJECT_NAME, CALL.MON$OBJECT_TYPE |
| 279 |
FROM MON$CALL_STACK CALL |
| 280 |
JOIN HEAD ON CALL.MON$CALLER_ID = HEAD.MON$CALL_ID |
| 281 |
) |
| 282 |
SELECT MON$ATTACHMENT_ID, MON$OBJECT_NAME, MON$OBJECT_TYPE |
| 283 |
FROM HEAD |
| 284 |
JOIN MON$STATEMENTS STMT ON STMT.MON$STATEMENT_ID = HEAD.MON$STATEMENT_ID |
| 285 |
WHERE STMT.MON$ATTACHMENT_ID <> CURRENT_CONNECTION |
| 286 |
|
| 287 |
6) Enumerate all session-level context variables for the current connection: |
| 288 |
SELECT VAR.MON$VARIABLE_NAME, VAR.MON$VARIABLE_VALUE |
| 289 |
FROM MON$CONTEXT_VARIABLES VAR |
| 290 |
WHERE VAR.MON$ATTACHMENT_ID = CURRENT_CONNECTION |
| 291 |
|
| 292 |
7) Report top 10 statements ranked by their memory usage: |
| 293 |
SELECT FIRST 10 STMT.MON$ATTACHMENT_ID, STMT.MON$SQL_TEXT, MEM.MON$MEMORY_USED |
| 294 |
FROM MON$MEMORY_USAGE MEM |
| 295 |
NATURAL JOIN MON$STATEMENTS STMT |
| 296 |
ORDER BY MEM.MON$MEMORY_USED DESC |
| 297 |
|
| 298 |
-------------------------------------- |
| 299 |
Modifications of the monitoring tables |
| 300 |
-------------------------------------- |
| 301 |
|
| 302 |
Monitoring tables also allow some special administration activities, in particular: |
| 303 |
cancelling running statements and terminating client sessions. This is done via deletes |
| 304 |
from tables MON$STATEMENTS and MON$ATTACHMENTS respectively. Deletes from other tables, |
| 305 |
as well as inserts/updates issued against them, are prohibited. |
| 306 |
|
| 307 |
Notes: |
| 308 |
1) If there are no statements currently running by the client, then the cancellation |
| 309 |
attempt becomes a void operation. Once cancelled, the execute/fetch API call returns |
| 310 |
the isc_cancelled error code. Any subsequent operations are allowed. |
| 311 |
|
| 312 |
2) If there are active transactions in the connection being terminated, their activity |
| 313 |
is immediately cancelled and they're rolled back. Once terminated, the client session |
| 314 |
receives the isc_att_shutdown error code. Subsequent attempts to use this connection |
| 315 |
handle will cause network read/write errors. |
| 316 |
|
| 317 |
3) System attachment can not be cancelled, so engine silently skip system attachments |
| 318 |
affected by DELETE FROM MON$ATTACHMENTS statements |
| 319 |
|
| 320 |
Example(s): |
| 321 |
1) Cancel current activity of connection #32: |
| 322 |
DELETE FROM MON$STATEMENTS WHERE MON$ATTACHMENT_ID = 32 |
| 323 |
|
| 324 |
2) Disconnect everybody but ourselves: |
| 325 |
DELETE FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID <> CURRENT_CONNECTION |
| 326 |
|
| 327 |
|
| 328 |
-------------- |
| 329 |
Under the hood |
| 330 |
-------------- |
| 331 |
|
| 332 |
The monitoring implementation is built around two corner stones: shared memory and |
| 333 |
notifications. |
| 334 |
|
| 335 |
All server processes share some region of memory where the current activity information |
| 336 |
is stored. This information consists of multiple variable-length items describing the |
| 337 |
various activity details. All items that belong to the same process are grouped into a |
| 338 |
single cluster, so that they can be processed as a whole. |
| 339 |
|
| 340 |
The monitoring information is not populated/collected in real time. Instead, server |
| 341 |
processes write their data into the shared memory only when explicitly asked to. When doing |
| 342 |
so, the old clusters are being replaced with newer ones. When the shared memory region is |
| 343 |
being read, the reading process scans all the clusters and performs the garbage collection: |
| 344 |
clusters that belong to dead processes are removed and the shared memory space is compacted. |
| 345 |
|
| 346 |
Every server process has a flag that indicates its ability to react to someone's monitoring |
| 347 |
request as soon as it arrives. When some user connection runs a query against some |
| 348 |
monitoring table, the worker process of that connection sends a broadcast notification to |
| 349 |
other processes requesting an up-to-date information. Those processes react to this request |
| 350 |
by updating their clusters inside the shared memory region and clearing their "ready" flags. |
| 351 |
Once the every notified process has finished, the requesting one reads the shared memory |
| 352 |
region, filters the necessary tags based on its user permissions, transforms the internal |
| 353 |
representation into records and fields and populates the in-memory monitoring tables cache. |
| 354 |
|
| 355 |
Processes that were idle since the last monitoring exchange have their "ready" flag clear, |
| 356 |
thus indicating that they have nothing to update in the shared memory. This way they're |
| 357 |
excluded from the next roundtrip. As soon as something significant changed inside the |
| 358 |
process, the flag is set and this process starts responding to the monitoring requests |
| 359 |
again. |
| 360 |
|
| 361 |
The requester holds an exclusive lock while coordinating the write/read operations. This lock |
| 362 |
affects the currently active user connections as well as the connections being established. |
| 363 |
Multiple simultaneous monitoring requests are serialized. |
| 364 |
|
| 365 |
|
| 366 |
---------------------------- |
| 367 |
Limitations and known issues |
| 368 |
---------------------------- |
| 369 |
|
| 370 |
1) In a heavily loaded system running Classic, monitoring requests may take noticeable time |
| 371 |
to execute. In the meantime, other activity (both running statements and new connection |
| 372 |
attempts) may be blocked until the monitoring request completes. |
| 373 |
|
| 374 |
Improved since FB v2.1.2. |
| 375 |
|
| 376 |
2) Monitoring requests may sometimes fail due to the out-of-memory condition, or cause other |
| 377 |
worker processes to swap. This is caused by the fact that the every record in MON$STATEMENTS |
| 378 |
has a blob MON$SQL_TEXT which is created for the duration of the monitoring transaction. |
| 379 |
Prior to FB v2.5, every blob occupied <page size> bytes of memory even if its contents is |
| 380 |
in fact smaller. So, with a huge number of prepared statements in the system, it becomes |
| 381 |
possible to get this failure. |
| 382 |
|
| 383 |
Another possible reason could be the temporary (very short in practice) growth of the |
| 384 |
transaction pool which caches the monitoring data while merging the clusters into a single |
| 385 |
fragment. |
| 386 |
|
| 387 |
Improved since FB v2.5.0. |