InterBase Performance Monitoring: Vision and Control

Introduction

In older versions of InterBase it was sometimes difficult to tell what was going on inside the server. This could make debugging applications very difficult. You could tell, for example, that there was a long-running transaction by looking at the DB header statistics, but you couldn't tell whose it was or what it was doing. Nor could the DBA preemptively end the transaction.

With InterBase 7 and higher, InterBase now provides insight into and control over the internal functions of the server via the temporary system tables. In some ways, InterBase developers and DBAs have a new problem: Instead of not enough information, we now have so much information and so many options for controlling the server that it's possible to get lost in the minutia of InterBase internals.

This session will focus on practical use of the feature. I'm not going to reproduce the InterBase documentation or explain every column of every table, but instead will focus on how to debug and enhance applications, and how to effectively administer an InterBase server.

Performance Monitoring Overview

The simplest way to learn what the monitoring tables can do is to look at InterBase Performance Monitor. This is a feature of IBConsole, and is also available in a standalone application. In IBConsole, right-click on a connected database and choose "Performance Monitor" from the context menu.

The biggest limitation of the monitoring feature in InterBase 7 is that it always functions in the context of a single database. In other words, there is no way to find out, for example, the list of users connected to a single server if these users are not all using the same database. The InterBase team is aware of the limitation and may address it in the future. This issue could be a serious limitation for DBAs, but it really doesn't hurt developers at all.

Terminology

As you examine the performance monitoring tables, you'll encounter some terms you may not be familiar with.

Clone number

InterBase may keep more than one copy of a stored procedure or trigger in memory. Two or more copies of the same proc are called clones. The clone number distinguishes two copies of the same procedure, and is needed to uniquely identify a record in TMP$PROCEDURES or TMP$TRIGGERS.

GC

Short for "garbage collector." InterBase's garbage collector disposes of obsolete (that is, not visible to any active transaction) record versions. For an understanding of InterBase's multi-generational architecture, read this paper.

GC backouts

This occurs when a transaction has been rolled back. If a record version which was creates by the rolled back transaction is encountered, it is marked for garbage collection and the prior committed version is brought forward into the primary slot on the data page.

GC expunges

This occurs when a record which is not visible to any active transaction is deleted and the deleting transaction commits. The deleted record and all prior committed versions of that record are removed so that the disk space they used can be reused. If the record is still visible to older snapshot transactions, of course, the expunge can't happen until after those transactions end.

GC purges

This occurs when the garbage collector removes old versions of a record which are no longer needed by any active transaction.

Page fetches

A page fetch is any reference to a DB page, whether the page is read from the page cache or from disk.

Page marks

A mark occurs whenever a page is about to be modified. The mark lets the cache manager know that the page should eventually be written too disk.

Page reads

A page read is when the page is read from the disk because it is needed and cannot be found in the page cache (buffers).

Page writes

This occurs when the page is actually written to disk.

Quantum

Quantum is a measure of how much work the server has to do to complete a request. The work might be CPU cycles, HDD reads, etc. Essentially, the higher the quantum, the bigger the load on the server.

Request

The internal, compiled version of a query. Requests are to SQL what EXEs are to source code, roughly.

Table by Table

Here is a brief summary of the tables and what you can do with them.

Attachments tab (TMP$ATTACHMENTS table)

This table contains one row for each attachment to a database.  In addition to giving information about the attachment, it can be used to preemptively disconnect an attachment or send a "ping" to make sure that the client is still alive (the latter feature is new in IB 7.1 SP 2). Items that DBAs might look for while browsing this table include excessive quantum, very long-lived attachments, large numbers of attachments from a single user, etc.

Database tab (TMP$DATABASE table)

This table contains one row, in IB 7. It provides information similar to the traditional database header page statistics, plus a lot more. This tab is the best place to get an overall view of the health of your database and server. In the InterBase 7.5 version of IB Performance Monitor, display of this tab has been enhanced to fit on one page and to make the grouping of the data more obvious. Items which a DBA might be concerned with on this page include the total number of attachments, the number of active threads, memory use, what the sweep is doing, and how, generally speaking, transactions are being used. Many of these items can be altered via server settings if necessary; see the CD-only paper for course 6106, Optimization III: How to Select and Configure InterBase Server Hardware, for details.

Memory tab (TMP$POOLS and TMP$HEAPS tables)

This table contains information on how InterBase uses memory internally. Note that InterBase Performance Monitor massages the data quite a bit for display. You can see a "more raw" output by choosing Monitor->Memory->Show Pools Grid, but even this is a summation by Type. Note also that there is another table called TMP$POOL_BLOCKS, but it seems to be unused in IB 7. The server can be instructed to use more or less memory if useful for a particular installation; see the CD-only paper referenced above for details. The Show Heaps Grid option (IB 7.5+ databases only) gives a very low-level view of InterBase's memory allocations. The heaps view is primarily useful when working with InterBase tech support, as understanding the data here requires detailed knowledge of server internals.

Procedures tab (TMP$PROCEDURES table)

This contains one row for any procedure executed since the current attachment (i.e., Performance Monitor's connection to InterBase) connected, and displays information about what the server had to do to execute the procedures. Procs do use some memory when loaded as the server caches them for reuse and in extreme cases (large number of procedure executed infrequently) could result in sub-optimal use of memory.

Statements tab (TMP$STATEMENTS table)

This table contains one row for any statement currently executing, and includes information on what the server is doing to run the statement.

Tables & Views tab (TMP$RELATIONS table)

This contains one row for any table or view referenced since the current attachment (i.e., Performance Monitor's connection to InterBase) connected, and displays information about what the server did internally while referencing those tables.

Triggers tab (TMP$TRIGGERS table -- IB 7.5+ databases only)

This table is similar to TMP$PROCEDURES, because procedures and triggers have a lot in common. The table contains one row for each trigger on each table or view displayed on the tables and views tab.

Transactions tab (TMP$TRANSACTIONS table)

This tab contains one row for each transaction which is active, precommitted, or in limbo. A precommitted transaction is one which is read-only and has read-committed isolation, and hence needs much less isolation from other transactions. The server treats them as committed internally, reducing the workload required to keep the transaction open. An in-limbo transaction is a multi-database transaction which has been committed on this database but not on another database participating in the transaction.

How Performance Monitoring Works

Temporary Tables

The system monitoring tables are temporary tables. There is a new column in the RDB$RELATIONS system table called RDB$PERSISTENT which indicates whether or not a table is temporary. If the value stored here is GLOBAL TEMPORARY then the table is temporary; anything else means it's a "normal" table. In IB 7.5 and higher you can create your own temporary tables.

Transactions

The transaction context of a temporary system table works a little differently than a "regular" InterBase relation or even a user-defined global temporary table.

First, the "data" in the system temporary tables doesn't exist until you SELECT from them. The first time you SELECT from, for example, TMP$TRANSACTIONs in the context of a certain transaction, the server looks at who is attached and stores the relevant information so that it can be read by the query execution engine to complete your SELECT statement.

Second, the "data" in the tables can't last any longer than the context of the transaction which was used to read it. When you COMMIT the transaction (or even call COMMIT RETAINING), the data is erased, and must be recreated the next time you SELECT from the table. This is the same as how a user-defined temporary-table works when you use the ON COMMIT DELETE ROWS option.

The "data" in memory in a system temporary table exists only for the attachment which started the transaction; other attachments with their own transaction contexts may see different data.

One important consequence of these facts is that in order to see up-to-the-minute information about what is happening in the database, you should call COMMIT RETAINING before you re-query a given table. The InterBase documentation recommends COMMIT RETAINING instead of COMMIT because COMMIT creates a new transaction ID. The number of transactions you can start before you must backup and restore a database is limited to (2^32 -1) / 2 -- the range of a signed integer. While two billion+ is a big number, "monitoring" applications often refresh queries on a regular basis and may run for weeks at a time, so they can burn through transaction IDs quickly if a "hard" commit is used on every refresh.

In order to provide a consistent summary of data reflecting all threads on all processors, InterBase must temporarily suspend normal query processing when generating the performance monitoring data. The performances cost of doing this on a loaded server is minimal provided that you don't query the tables more than every few seconds or so, but an application which queried them many times per second could severely hurt server performance.

Note that in InterBase 7 there is no longer a penalty for long-running transactions if you stick to certain rules; I discuss these rules in detail in this article on my web site.

In summary, you should follow the recommendation in the IB documentation and use a read-only, read-commited transaction and call COMMIT RETAINING instead of COMMIT in between SELECT statements when SELECTing from the system monitoring tables. For UPDATEs to these tables, use a read/write transaction, of course.

Snapshots

It is important to understand that the temporary system tables are giving you snapshots of what is happening in your database at the instant you execute the SELECT statement referencing them. TMP$STATEMENTS, for example, does not include every statement which an application has run since the last time you looked at the table, but rather only those which are active at the time of the SELECT. So there is no way to get a complete list of every statement run against the server using the temporary system tables.

That said, the temporary system tables are still useful for certain types of logging. If you want to know, for example, who is keeping a SNAPSHOT transaction active for hours at a time, it's fairly simple to request and store this information:

INSERT INTO MY_LOG_TABLE

  USER_NAME,

  ELAPSED_TIME

SELECT

  A.TMP$USER,

  CURRENT_TIMESTAMP - T.TMP$TIMESTAMP AS ELAPSED_TIME

FROM

  TMP$TRANSACTIONS T

  INNER JOIN TMP$ATTACHMENTS A

    ON T.TMP$ATTACHMENT_ID = A.TMP$ATTACHMENT_ID

WHERE

  (T.TMP$TYPE = 'SNAPSHOT')

  AND ((CURRENT_TIMESTAMP - T.TMP$TIMESTAMP) > (1/24/2)) > (1/24/2)) /* 1/2 hour */

Run this query once an hour or so, and it should tell you all you need to know.

Security

The security on the temporary system tables works like any other InterBase table. By default, only the DB owner or SYSDBA can read or edit them, but you can change that by using the GRANT statement.

For the most part, however, you don't want to give users full rights to these tables. If you GRANT a user the right to UPDATE TMP$ATTACHMENTS, for example, they will be able to kick any other user's attachment off of the server! Even GRANTing the right to SELECT from this table may be a security compromise.

However, you might still want to give users more limited rights -- the right to monitor and cancel their own statements, for example. The following VIEW will do the job:

CREATE VIEW CURRENT_USER_STATEMENTS

  AS

SELECT

  S.*

FROM

  TMP$STATEMENTS S

  INNER JOIN TMP$ATTACHMENTS A

    ON A.TMP$ATTACHMENT_ID = S.TMP$ATTACHMENT_ID

WHERE

  (A.TMP$USER = USER);



CREATE EXCEPTION SORRY_NO_CAN_DO 'You may only alter your own attachment.';



CREATE TRIGGER TBU_CURRENT_USER_STATEMENTS FOR CURRENT_USER_STATEMENTS  BEFORE UPDATE

  AS

BEGIN

  IF (OLD.TMP$USER  USER) THEN EXCEPTION SORRY_NO_CAN_DO;

END



GRANT SELECT, UPDATE ON CURRENT_USER_STATEMENTS TO PUBLIC;

Now any user has the ability to monitor and cancel their own statements, and nobody else's, by using CURRENT_USER_STATEMENTS instead of TMP$STATEMENTS. You can create similar VIEWs for the other system temporary tables.

SQL Tips and Tricks

The most important thing to remember about the system temporary tables is that they are just DB tables and you can and should use SQL to join them together, aggregate and analyze the data, and filter and refine your results. Any standard InterBase SQL should work well with them.

Use LIKE

You can disconnect users with UPDATE TMP$ATTACHMENTS SET TMP$STATE = 'SHUTDOWN'. You may want to use a WHERE clause so that only one attachment is affected, as the statement above will kick everyone (including the attachment running it!) off the system. You can also kick off all attachments from a single user with the appropriate WHERE clause, and you can use LIKE to drop all attachments from a certain IP subnet:

UPDATE TMP$ATTACHMENTS

SET TMP$STATE = 'SHUTDOWN'

WHERE

  TMP$USER_IP_ADDR LIKE '127.0.0.%';

Similar queries work with the other system monitoring tables, and you can of course JOIN the tables together or use subqueries to combine them and, for example, cancel the statements of a certain user which have been running for more than 10 minutes, but leave the rest of that user's statements alone.

Information not in the InterBase Documentation

The InterBase documentation omits (or relegates to Release Notes) certain details of the system monitoring tables.

New Monitoring Features in IB 7.5

New TMP$DATABASE Features

TMP$DATABASE gets one new column in IB 7.5, TMP$STATE.  Running an UPDATE statement against this column allows you to instruct the server to do several things:

• UPDATE TMP$DATABASE SET TMP$STATE = 'FLUSH'; COMMIT; Tells the OS to write the cache to disk. This is useful only if Forced Writes are turned off. You can also tell IB Server to automatically flush the cache from time to time by executing ALTER DATABASE SET FLUSH INTERVAL.
• UPDATE TMP$DATABASE SET TMP$STATE = 'RECLAIM'; COMMIT; Tells the server to reclaim memory in use by loaded procs and triggers and internal system queries. Look at TMP$POOLS to see how the server is using memory; look at TMP$PROCEDURES and TMP$TRIGGERS to see which procs/triggers are in memory at any given time.
• UPDATE TMP$DATABASE SET TMP$STATE = 'SWEEP'; COMMIT; Manually sweeps the database. You could always do this via the services API; now you can do it via SQL.

TMP$HEAPS Table

The new TMP$HEAPS table in InterBase 7.5 gives low-level information regarding InterBase's internal memory allocation. InterBase maintains two different types of memory blocks, identified in TMP$HEAPS as RANDOM and BLOCK. The former are, generally speaking, small, and of varying size, while the latter are larger and of fixed size. IB allocates these separately to avoid fragmenting the memory heap.

TMP$TRIGGERS Table

TMP$TRIGGERS is really the same as TMP$PROCEDURES except, obviously, that it lists triggers instead of stored procs. But stored procs and triggers are very similar, so if you understand the TMP$PROCEDURES table then you'll understand this one.

New Performance Monitor GUI Features

• GUI elements for all of the new IB 7.5 performance monitoring features. The TMP$DATABASE.TMP$STATE features are accessible via buttons on the toolbar when the Database tab is selected. TMP$HEAPS is shown on the Memory tab when Monitor-> Memory-> Show Heaps Grid is selected. TMP$TRIGGERS gets its own tab. These new features are only visible when connected to an IB 7.5+ server and an ODS 11.2+ database.
• Monitor->Save As allows exporting displayed data in one of several different formats.
• Multi-select is enabled for most features (such as shutdown attachment and rollback transaction).
• Monitor->Show Performance Monitor Data allows hiding internal queries, transactions, and attachments used by the Performance Monitor itself. The default view is now to hide such data and hide system tables on the tables and views and triggers tabs.
• Monitor->Show Fields allows showing columns normally hidden in the GUI display, notably IDs.
• The one second refresh option has been replaced with a 60 second refresh as the potential for Heisenberg effects was too high with the old option.
• Database tab now has two columns for reduced scrolling.
• Better support for XP themes (in standalone version).
• Improved help.
• Private profile directory rather than program file directory is used to store personal settings.
• Many bugs fixed.

New Monitoring Features in IB 7.1 Service Pack 2

The Windows TCP/IP stack has a memory leak in afd.sys, a device driver, which can cause an out of memory error due to the "keep alive" packets which InterBase sends out from time to time to ensure that client applications haven't crashed. Because of this, the keep alive packets were turned off by default in InterBase 7.1 SP 2. This BDN article has much more information on this topic. Since a crashed client application will effectively hold an InterBase simultaneous user license in use, a "ping" feature was added to the server in 7.1 SP 2 to allow system administrators to automatically detect and drop attachments created by now-dead client applications at will. This can be done with SQL:

UPDATE TMP$ATTACHMENTS SET TMP$STATE = 'KEEPALIVE' [WHERE ...]

COMMIT

...or by using the "ping" feature in InterBase Performance Monitor version...????

Also, a new memory pool type was added in this service pack. See the next section for details.

Memory Pool Types

The following list explains the meaning of the three letter codes in TMP$POOLS.TMP$TYPE In the definitions below you will see the term request used frequently. A request is the compiled form of a query.

CCH

Memory pool from which cache manager data structures are allocated. The major memory allocation is for the page buffers.

DYN

Internal request pools specifically having to do with data definition (DDL) operations.

IRQ

Registry of persistent internal requests. InterBase executes internal requests against system tables to maintain and load a database's metadata.

PRM

Permanent pool from which internal metadata structures are allocated.

REQ

Request blocks. Normal user queries which have been compiled into executable requests.

SQL

SQL statements. ???

SRT

Sort memory blocks. These are used when InterBase must sort a result set in memory. This pool type is shown only in InterBase 7.1 Service Pack 2.

TRA

Transaction manager. Transaction pools allocate data structures having to do with a transaction such as savepoints and transaction bitmaps for concurrency control.

TRG

Trigger pool from which a trigger request is allocated.

Statement States

The TMP$STATEMENTS.TMP$STATE column can have one of four values: ACTIVE, INACTIVE, STALLED, AND CANCELLED. ACTIVE means that the server is currently executing the statement or waiting for the client to fetch rows. ACTIVE statements are scheduled even if the server is waiting for the client to fetch records and thus can affect overall performance. INACTIVE means that the statement has run to completion and all records have been fetched. A statement can be INACTIVE even before it is closed. STALLED means that the statement has executed, but the client closed the statement before fetching all the rows. CANCELLED means that the client has used InterBase's asynchronous query cancellation feature or issued an UPDATE against the TMP$STATEMENTS table to cancel the query.

Bugs

The only bug I know of with regards to the system temporary tables as of IB 7.1 SP 2 is that shutting down an attachment with active statements can cause a server crash. It may be possible to reproduce the same crash without using the monitoring tables by dropping users via the Database Shutdown command while they have statements active. This is fixed in the "Security update" available from the InterBase registered users site.