Debian Codegraph @

doc/README.monitoring_tables

raw
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.