Using InterBase Performance Monitor

You can use the InterBase Performance Monitor to view the status and activities of a database, such as:

A summary of database threads, procedures, transactions and memory use.
Memory use by graph, pool or heap.
A list of all the tables and views in the database.
A list of all recent transactions.
A list of all recent procedures and triggers.
The interbase log file and statistics list.

To open the InterBase Performance monitor, do one of the following:

Select a database in the left pane of IBConsole and choose Database > Performance Monitor...
Use right-click on a databse in the left pane of IBConsole and choose Performance Monitor... in the context menu.

Logging

Logging means saving data from InterBase temporary system tables into normal tables at regular intervals, along with a time stamp 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 while logging was active.

Logging entries are only snapshots, and only show what was happening at the time the snapshot was taken. For example:

If you log statements, you do not see statements which start and finish in between snapshots.
If you log users, you see users who were logged in at the time a snapshot was taken.

Setting up Logging

Choose Logging > Setup Logging from the main menu.
Select items that you want to log. To select an item, mark the corresponding check box.
- Optional. Modify table and generator names. If you want, you can leave the defaul names.
- If logging memory, you can choose to summarize the data (log one row for each memory block type) or not summarize the data (copy each row from TMP$POOLS into the logging table).
Select a logging interval.
Create the required metadata (InterBase Performance Monitor prompts you if this is necessary).
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 to as high 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.

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 is 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 the InterBase Performance Monitor prompts you before creating the generator.

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 need to complete the setup dialog again before you are able to start logging. There are a couple reasons for this behavior:

InterBase Performance Monitor needs to check out the metadata to make sure that all required tables and the generator exist.
It is a conscious design decision to force users to specify which items they want to log every time logging is setup. This is to encourage users to log as little as possible, in order to reduce the impact on the server.

Using the logged data

Use Logging > Log Visualization to browse logged data by date range or snapshot number.

Reporting on the logged data

You can write your own reports. For example, using any InterBase-compatible reporting tool, or simply by querying 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. See System Temporary Tables for more information about the layout of the tables and Querying System Temporary Tables for sample queries. You can use these queries 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:

Column	Description
`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.

Monitoring

Monitoring (in the context of the InterBase Performance Monitor) means getting information about the state of a database, the InterBase server, and the users connected to it, and refreshing this information regularly so that it is always up to date.

InterBase Performance monitor is designed to use as little server resources as possible while monitoring is running.

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. For example:

You could tell that there was a long-running transaction by looking at the database header statistics.
You could not tell whose it the transaction was or what the transaction was doing.
The DBA could not preemptively end the transaction.

With InterBase 7 and higher, InterBase 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 is possible to get lost in the particulars of InterBase internals.

The biggest limitation of the monitoring is that it always functions in the context of a single database. For example, there is no way to get a list of users connected to a single server (if these users are not all using the same database). This issue may be a limitation for DBAs, but it really does not affect developers.

Terminology

As you examine the performance monitoring tables, you may see terms you may not be familiar with. The following table explains some of those terms:

Term	Description
Clone number	InterBase may keep more than one copy of a stored procedure or a trigger in memory. Two or more copies of the same procedure 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. The garbage collector in InterBase disposes of obsolete record versions. Obsolete record versions are those that are not visible to any active transaction.
GC backouts	Occur when a transaction is rolled back. If a record version that the rollback created is encountered, it is marked for garbage collection and the prior (committed) version of the record is brought forward into the primary slot on the data page.
GC expunges	Occur 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 can be reused. If the record is still visible to older snapshot transactions, the expunge cannot 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 database page (regardelss if 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 to disk.
Page reads	A page read occurs when the page is read from the disk (because it cannot be found in the page cache - buffer).
Page writes	A page write occurs when the page is written to disk.
Quantum	Quantum is a measure of how much work the server has to do to complete a request. The work may be CPU cycles, HDD reads, etc. The higher the quantum the bigger the load on the server.
Request	The internal, compiled version of a query.

Security

The security on the temporary system tables works the same as for any other InterBase table. By default, only the database owner or SYSDBA can read or edit temporary system tables. You can change that by using the GRANT statement. This means that unless you GRANT privileges to the TMP$ tables to a user, that user is not able to use InterBase Performance Monitor.

However, normally you do not want to give users full rights to temporary system tables. For example, if you GRANT a user the right to UPDATE TMP$ATTACHMENTS, they are able to kick any attachment from another user off of the server. Even granting the privileges to SELECT from that table may be a security compromise.

Performance Monitoring Counters

The performance monitoring counters are scaled up from 32-bit to 64-bit values in InterBase XE7.

There are some ODS-related differences to be aware of:

ODS <= 15 continues to have 32-bit INTEGER counters as before (for both SQL dialect 1 and SQL dialect 3 databases).
ODS >= 16 has the counters defined as "double precision" data type for SQL dialect 1 databases.
ODS >= 16 has the counters defined as NUMERIC(18,0) data type for SQL dialect 3 databases.

For SQL dialect 3, the Performance Monitoring data counters are updated to 64-bit Integer type. Since SQL dialect 1 does not support 64-bit Integer type, a 64-bit Integer type is internally converted to type "double precision" as it is the same size (8 bytes) and also accommodates the large values for 64-bit addresses and counter values.

The best way to identify the differences between SQL dialect 1 and SQL dialect 3 is to check field definitions for the result of the SQL query against your database.

For example, if you run the following query: SELECT RDB$FIELD_NAME FROM RDB$FIELDS WHERE RDB$FIELD_LENGTH=8 AND RDB$FIELD_TYPE=16;, the results show as follows:

In SQL dialect 3 database: FIELD_TYPE 16 indicates NUMERIC(18,0).
In SQL dialect 1 database: You see no such field type.

Backward Compatibility

With the support of 64-bit Integer data counters for Performance Monitoring, backward compatibility is maintained for servers working with ODS 15 databases where such counters are 32-bit Integers.

To do this, enable gpre with the -ods <major_ods_number> command-line option that allows gpre to limit its parsing of GDML/ESQL programs to relations/tables that are only valid until that major ODS.

Syntax:

gpre [-ods <number>] ... <foo.e>

Example:

gpre -ods 15 tmp.e