The database server may be configured to write some of its internal
performance statistics to dedicated files. There are two sets of metrics
available, each stored in its own file.
One set of metrics provides performance data for the server as a whole;
for example, the server utilization (percentage) and resulting disk read
and write rates (requests per second).
The other set of metrics provides data for individual client sessions;
for example, audit info like logon and program name, connect time and
duration, as well as summarized data like number of database calls (dbget,
dbupdate, dbput, etc) and time spent in those calls during the lifetime
of the client session.
These server and session statistics are also available through the
HTTP Status interface, if the ServiceHttp option is configured in the
eloqdb.cfg configuration file. However, the web pages only show
server metrics for the most recent 10 minutes and session metrics
as long as a given client thread is still running.
To capture these server and session statistics for long-term storage,
the StatFile and SessionStatFile features can be enabled in the server
configuration file as described below. The settings can also be changed
with the dbctl utility while the database server is running.
StatFile specifies a file name to be used for logging the server
utilization. If enabled, this file is updated once a minute. As
the file is re-opened each time it is updated, it may be moved or
deleted freely. The config file must specify an absolute file name.
This is consistent with the corresponding dbctl command.
StatFile = /tmp/eloqdb-server.status
Note: This option may also be controlled dynamically with dbctl statfile.
The format of this output file is controlled by the StatFileFlags
option. By default, the server creates or rewrites multi-line output
similar to the following example:
The following values are defined:
- Time when entry was written (in seconds)
- Server utilization in percent.
- Number of client requests per second
- Number of committed transactions per second
- Number of disk read requests per second
- Number of disk write requests per second
- Number of disk sync requests per second
- Number of concurrent client connections
The values are averages over the last 60 seconds.
StatFileFlags specifies options that influence the StatFile format.
By default (StatFileFlags not set) the file content is replaced
each time it is updated. Also, the content is formatted with
multiple lines, each containing a descriptive text and the actual
value, separated by a colon.
The following flags are supported:
- s - (single line)
- causes the values to be formatted into a single
line. Values are separated by a space and no descriptive text is
- a - (append)
- causes additional values to be appended to the file
instead of replacing the previous content.
- t - (local timezone)
- causes the timestamp to include the offset
of the local timezone from UTC. If not present, the timestamp
value denotes UTC. This flag allows to use the timestamp value
with DSI (MeasureWare) on HP-UX without requiring a conversion.
These flags may be also combined, for example:
StatFileFlags = sat
Note: This option may also be controlled dynamically with dbctl statfileflags.
Example output (single line format, appending):
1172193450 6 110 0 441 0 0 1
1172193510 5 102 0 398 0 0 1
1172193570 7 120 0 477 0 0 1
A small GNU awk script
may be useful for formatting such StatFile lines.
If specified, SessionStatFile is used for logging session
utilization information. Depending on the SessionStatMode
setting, information is logged when a session ends or after
the next database call after the specified interval expires.
This file is opened on the first event and kept open until a
new value is specified with dbctl SessionStatFile or the
SessionStatMode is changed through dbctl. The config file must
specify an absolute path name. This is consistent with the
corresponding dbctl command.
SessionStatFile = /tmp/eloqdb-session.status
Note: This option may also be controlled dynamically with dbctl sessionstatfile.
The information logged to SessionStatFile is substantially
similar to session details provided in the HTTP status and
may be used to evaluate performance or behaviour of an application
after it has completed.
Every entry in SessionStatFile consists of a single line,
fields are separated by a vertical bar (|) character.
The following information is provided in SessionStatFile:
- The timestamp (UTC) the entry was added
- The id of the database thread
- Type of entry (E or U character);
E ("end") specifies the entry was logged when the thread had completed
(application disconnected from the database), U ("update") specifies the
entry was logged after the interval specified in SessionStatMode
- Operating system account used by the application
- database login (most recently) used by the application
- Connect time (in ISO format YYYY-MM-DD HH:MM:SS)
- Number of seconds elapsed since connecting
- Three numerical values for each monitored database
activity (a subset of database activity typically called
from applications). The values specify two counters and
the wall time for the sum of all calls of a category.
The first counter (count1) may specify a count related to the
particular call (see below), the second counter (count2) specifies
the number of database calls (from the client library, may be
different from application calls if client side caching is used).
The wall time is specified in microseconds (1 mio per second).
The following database activities are monitored:
- Disk reads accounted to the session.
This includes both reading activity as well as any
disk reads required for writing activity. The count2 field
specifies the number of IO requests, the count1 field
specifies the number of pages (8K units).
- DBFIND calls.
- DBGET calls (single record).
- Multi-record DBGET calls. These are used internally
by the client library if client side caching is used.
The count1 field specifies the number of records obtained.
- DBPUT calls
- DBUPDATE calls
- DBDELETE calls
- DBLOCK calls; count1 specifies the number of
unconditional DBLOCK calls that could not be granted
immediately but were blocked by a competing lock.
- DBUNLOCK calls
- Begin Transaction
- Commit Transaction
- Transaction Rollback
- other db calls
- IP Address and port number (separated by a colon) used
to connect to the database
- Information collected from the application environment,
such as process ID, operating system specific user id,
(subset of the) command line, EQ_AUDIT_INFO content
Note: The content of the SessionStatFile is subject to change without notice.
Example output (single line):
|0|0|0|0|0|0|0|0|127.0.0.1:64169|uid=102 pid=4812 pname=query3k
Note that the above example is actually a single line in the SessionStatFile;
the line wrapping has just been added to fit the long line into this web page.
A small GNU awk script
may be useful for reviewing SessionStatFile lines.
SessionStatMode is a numeric value that specifies when an
entry is logged to the SessionStatFile.
The following values are supported:
- 0 - (zero)
- The SessionStatFile is disabled
- 1 - (one)
- A log entry is written to the SessionStatFile when
a session ends.
Any other value is understood to specify an interval (in seconds)
after which an entry is logged to the SessionStatFile in addition
to the entry that is logged after the session ends. The specified
value must be at least 60 seconds.
SessionStatMode = 1
Note: This option may also be controlled dynamically with dbctl sessionstatmode.