Understanding Logging

Logging means saving data from the InterBase temporary system tables to "normal" tables at regular intervals, along with a timestamp and other information.  Logging places a small load on the server, and uses disk space, but it allows you to query the tables after the fact and look back into time to see what the InterBase server was doing over the course of the time that Logging was active.

Important note: Logging entries are only snapshots, and only show what was happening at the time the snapshot was taken.  If you log statements, for example, you will not see statements which start and finish in between snapshots.  Likewise, if you log users, you will only see users who were logged in at the time a snapshot was taken.

Setting up logging

  1. Choose Logging->Setup Logging from the main menu.
  2. Select which items you want to log.
  3. Choose table and generator names, or just accept the defaults.
  4. If logging memory, you can choose to summarize the data (log one row for each memory block type -- this is how the data is displayed on the Memory tab) or not summarize the data (copy each row from TMP$POOLS into the logging table -- there are multiple rows for each memory block type).
  5. Select a logging interval.
  6. Create the required metadata -- InterBase Performance Monitor will prompt you if this is necessary.
  7. Start logging.

Conserving server resources when logging

When choosing the items to log, select as few as possible, as this saves both disk space and reduces load on the server.  Set the logging interval as long as possible.  The load created by logging is not huge, but it is substantially larger than monitoring.  In addition to disk space and CPU use, logging starts and commits a transaction each time a snapshot is taken, whereas monitoring does not (thanks to magic new features in InterBase 7).

Creating metadata for logging

InterBase Performance Monitor is fairly picky about the metadata (table structure) used by the logging function, so you must let InterBase Performance Monitor create the tables for you; it will not be possible to use existing tables.  However, you can choose the table names.  You must also specify a generator to use when inserting records into the logging tables.  You may choose an existing generator, or enter a new name and InterBase Performance Monitor will prompt you before creating it.

Starting logging

Once you have completed the setup dialog, you can start logging. As long as the connection to the current database is maintained, you can start and stop logging as many times as you like. After you disconnect and reconnect, however, you will need to complete the setup dialog again before you are able to start logging.  There are a couple reasons for this:

Using the logged data

Future versions of InterBase Performance Monitor will include ready-to-run reports to summarize logged data. In version 1.1, however, you must either write your own reports using any InterBase-compatible reporting tool, or just query the tables directly using IBConsole or ISQL.

The log tables include one column for each column in the InterBase system temporary tables, with the same column names. The layout of the tables is documented in the InterBase Language Reference, and sample queries are given in the InterBase Operations Guide. These queries can be used against the logging tables with very little modification.

There are a few extra columns in the logging tables (beyond those normally included in the system temporary tables to which the logging tables correspond) which you should be aware of:

ID
The primary key of each table. A unique identifier for the row.
TIME_STAMP
The time at which each item was logged. Do not presume that all rows or tables logged in the same snapshot will have identical TIME_STAMP values
SNAPSHOT_ID
All items logged in the same snapshot will have the same SNAPSHOT_ID.  You can therefore use values in this column to JOIN two tables together in a query.