.
Eloquence B.08.20 contact contact

Documentation / Database Server Statistics

Database Server Statistics

 
.
 

Contents


Overview

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.


[server] StatFile

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.

Example:

 [Server]
 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:

 timestamp: 1172193450
 server_load_perc: 6
 client_request_sec: 110
 transaction_commit_sec: 0
 disk_read_request_sec: 441
 disk_write_request_sec: 0
 disk_sync_request_sec: 0
 server_session_cnt: 1

The following values are defined:

timestamp
Time when entry was written (in seconds)
server_load_perc
Server utilization in percent.
client_request_sec
Number of client requests per second
transaction_commit_sec
Number of committed transactions per second
disk_read_request_sec
Number of disk read requests per second
disk_write_request_sec
Number of disk write requests per second
disk_sync_request_sec
Number of disk sync requests per second
server_session_cnt
Number of concurrent client connections

The values are averages over the last 60 seconds.


[server] StatFileFlags

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 present.
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:

 [Server]
 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.


[server] SessionStatFile

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.

Example:

 [Server]
 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:

timestamp
The timestamp (UTC) the entry was added
TID
The id of the database thread
Type
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 had expired
OSuser
Operating system account used by the application
DBuser
database login (most recently) used by the application
ConnTime
Connect time (in ISO format YYYY-MM-DD HH:MM:SS)
ConnSec
Number of seconds elapsed since connecting
Stats
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:

IO_READ
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
DBFIND calls.
DBGET
DBGET calls (single record).
DBGETB
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
DBPUT calls
DBUPDATE
DBUPDATE calls
DBDELETE
DBDELETE calls
DBLOCK
DBLOCK calls; count1 specifies the number of unconditional DBLOCK calls that could not be granted immediately but were blocked by a competing lock.
DBUNLOCK
DBUNLOCK calls
TXBEGIN
Begin Transaction
TXCOMMIT
Commit Transaction
TXROLLBACK
Transaction Rollback
OTHER
other db calls
IP
IP Address and port number (separated by a colon) used to connect to the database
AppEnv
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):

1172196823|9|E|mike|public|2007-02-22 16:54:39|4|11|11|751|0|0
|0|0|2|163|45|6|10591|0|0|0|0|0|0|0|0|0|0|1|8879|0|1|6|0|0|0|0
|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.


[server] SessionStatMode

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.

Example:

 [Server]
 SessionStatMode = 1

Note: This option may also be controlled dynamically with dbctl sessionstatmode.


 
 
.
 
 
  Privacy | Webmaster | Terms of use | Impressum Revision:  2013-04-19  
  Copyright © 2006-2013 Marxmeier Software AG