Performance Monitoring

The FairCom Server tracks and reports a wealth of performance-oriented statistics. The server’s Performance Snapshot capability enables the capture of performance monitoring data using a combination of configuration file options and the SnapShot c-tree API function.

Performance Monitoring Overview

By using these performance monitoring tools, you can more easily identify bottlenecks to understand whether performance problems are related to application issues, CPU constraints, or other areas. As there may be some overhead involved with the monitoring, FairCom recommends honing your monitoring needs to precise areas rather than monitoring every possible area.

You may want to establish baselines for your particular application in a QA lab so that you can later compare these results with data that comes in from the field to be able to more easily identify problems. Monitoring can also be beneficial to identify trends related to user activity- whether there are particular times of day when activity is highest, and whether particular operations can be scheduled at a later time of day to offset these peaks.

The performance data can be captured automatically at specified intervals or on demand in the following ways:

• The ctstat utility provides a command-line interface to the SnapShot API function, supporting output of FairCom Server statistics at the FairCom Server system, user, file, and function levels.
• Configuration file options support automatic performance snapshots by specifying the interval between automatic snapshots and the contents of the snapshots. The performance data captured by automatic snapshots are written to the FairCom Server system event log (SYSLOG) files.
• Use DIAGNOSTICS options to capture the automatic system snapshot data to the human-readable SNAPSHOT.FCS file.
• The SnapShot API function can control automatic snapshots, overriding configuration options (if any), and can capture on-demand performance data:
  • To either the SYSLOG files or to the human-readable SNAPSHOT.FCS file.
  • As a return to the calling program.

The following sections discuss how to use the performance monitoring abilities of the FairCom Server.

Performance Monitoring Using the ctstat Utility

The ctstat utility is a client utility used to display statistics collected by FairCom DB. It is found in the client folder of the installation and demonstrates use of the SnapShot() function. This utility is described in ctstat - Statistics Utility (ctstat - Statistics Utility, ctstat Statistics Utility) in the FairCom DB Utilities chapter.

Performance Monitoring Using Server Keywords

This section describes automatic performance snapshot logging using FairCom Server configuration keywords.

Automatically Logging Performance Snapshots

The FairCom Server supports automatically logging performance snapshots to the SYSLOG files and to the human-readable file SNAPSHOT.FCS. The following sections describe how to enable automatic snapshots.

Automatic Logging to the Server System Event Log

The SNAPSHOT_INTERVAL keyword enables automatic snapshots at specified intervals:

SNAPSHOT_INTERVAL   <minutes>

By default, only the system snapshot is captured. To add user or file-specific snapshots to the data captured, use one or more of the following configuration entries:

SNAPSHOT_USERID     <user ID>

SNAPSHOT_FILENAME   <file name>

Files and users added to the snapshots are said to be activated. Users and files may be activated whether or not the automatic snapshots are turned on in the configuration file. However, the activation has no effect until snapshots are written to the SYSLOG files.

The <user ID> and <file name> arguments may include wildcard matching characters: ‘*’ matches an arbitrary number of any characters, and ‘?’ matches exactly one of any character. A pattern of simply ‘*’ matches any user or file name. For example, the following keywords activate all users, any file ending in .dat, and the file journal.idx:

SNAPSHOT_USERID     *

SNAPSHOT_FILENAME   *.dat

SNAPSHOT_FILENAME   journal.idx

User IDs are not case sensitive. File name case sensitivity depends on the platform. For example, Windows is case insensitive and Unix is case sensitive. The file names activated must match the file name used to first open the file. In particular, paths used in the activation list and during the call to open the file must match.

Automatic Logging to SNAPSHOT.FCS

Write system snapshots to the human-readable SNAPSHOT.FCS text file with the following DIAGNOSTICS options:

DIAGNOSTICS   SNAPSHOT_SHUTDOWN

DIAGNOSTICS   SNAPSHOT_AUTOMATIC

DIAGNOSTICS SNAPSHOT_SHUTDOWN writes a system snapshot to SNAPSHOT.FCS at the start of the server shutdown process. DIAGNOSTICS SNAPSHOT_AUTOMATIC writes any automatic snapshots to SNAPSHOT.FCS instead of to the SYSLOG files. However, only the system snapshot is written. Snapshots for activated users and/or files are ignored.

Performance Monitoring Using the SnapShot API

FairCom Server configuration options provide an easy way to enable automatic performance snapshots. Additional flexibility and access to the complete set of statistics maintained by the FairCom Server is available programmatically through the SnapShot c-tree API function. This API function:

• Controls automatic snapshots, overriding configuration options (if any).
• Writes snapshots to the SYSLOG files on demand, whether or not automatic snapshots are active.
• Writes system snapshots in human-readable form to SNAPSHOT.FCS.
• Returns snapshot data on-demand to the calling application program.

Tips for Performance Monitoring with SnapShot

The SnapShot API provides a wealth of detailed information concerning may aspects of the system. The following system counters (ctGSMS) are particularly useful for overall performance monitoring.

ISAM counters

The ISAM counters provide a good indication of how much work is being requested (unless there are lots of low-level API calls in your application). Higher level APIs, such as FairCom DB API and SQL, are also reflected here.

• sctismredcnt - ISAM record read count
• sctismupdcnt - ISAM record update count
• sctismaddcnt - ISAM record add count
• sctismdelcnt - ISAM record delete count

Current lock counts

The current lock counts show the lock contention at any given instant. Similar counters are available with cumulative counts.

• LONG8sctlokcur; - current count of locks held
• LONG8sctblkcur; - current count of blocked requests

Cache counters

The cache counters are a good indication of how much CPU work c-tree is doing. A single ISAM record search by key must find several index cache pages and at least one data cache page. Likewise, an ISAM add must find at least 1 data cache page and at least 1 index cache page per associated index. requests - hits = cache misses, which means that system I/O call(s) were needed.

Indices

For the indices, these only counts cache searches (locating the desired cache page). Some operations (NextKey or NextRecord) can sometimes skip the cache search.

• LONG8sct_dbrqs; - data buffer requests
• LONG8sct_dbhit; - data buffer hits
• LONG8sct_ibrqs; - index buffer requests
• LONG8sct_ibhit; - index buffer hits

System I/O counters

These following counters monitor the overall system I/O. In the default configuration, I/O on the data and index files is cached by the file system, so you have to look at the system to get real I/O information (various keywords affect this behavior).

• LONG8sct_rdops; - number of read operations
• LONG8sct_rdbyt; - bytes read
• LONG8sct_wrops; - number of write operations
• LONG8sct_wrbyt; - bytes written

Transaction activity

The following counters give a good idea of transaction activity:

• LONG8sct_trbeg; - number of transaction begins
• LONG8sct_trend; - number of transaction ends
• LONG8sct_trabt; - number of transaction aborts
• LONG8sct_trsav; - number of transaction savepoints

Log writes

The log writes default (with COMPATIBILITY LOG_WRITETHRU) to real disk I/O requests by the system.

• LONG8sctwlgops; - number of log write operations
• LONG8sctwlgbyt; - bytes written to log file

See also

• SnapShot (SnapShot, SnapShot) - The SnapShot function provides full information about usage and the information that is returned.

SnapShot API Function Usage

The snapshot function is prototyped as follows:

ctCONV NINT ctDECL SnapShot(NINT opcode, pTEXT rqstdesc, pVOID snapbufr, VRLEN buflen);

SnapShot returns NO_ERROR (0) on success, or an error code on failure. opcode specifies the action to be taken by the snapshot function. rqstdesc points to an ASCII string containing the input information. snapbufr points to an output buffer of length buflen. Different opcode's require different combinations of input and output parameters ranging from using neither of them to both of them.

For information about how to use the SnapShot API function to collect system statistics, see SnapShot in the FairCom DB Programmer's Reference.

Automatic Snapshot Support

The SnapShot API function supports starting, stopping, and resuming automatic snapshots. The following table shows the parameters to pass to SnapShot to perform these operations.

Function Timing Support

The SnapShot API function supports profiling of c-tree API function calls (function timing) and timing of user-defined operations. The supported operations include:

• Starting and stopping the collection of c-tree API function timing statistics.
• Resetting the function timing statistics.
• Accumulating timings in user-defined timing baskets. Users may define up to 8 different timing baskets. A begin mark call establishes a high resolution starting time for the specified basket. An end mark call causes the specified basket counter to be incremented, and the elapsed time is added to the timing basket. The baskets are numbered from 0 to 7.

Note: Function timings require a significant number of calls to the high-resolution timer, and are more consistent with diagnostic or testing modes of operation.

The following table shows the parameters to pass to SnapShot to perform these operations.

System Snapshot Support

The SnapShot API function supports logging FairCom Server system statistics. For a listing of the system statistics the FairCom Server collects, refer to the ctGSMS structure in the Snapshot Contents section below. The supported operations include:

User Snapshot Support

The SnapShot API function supports logging FairCom Server user statistics. For a listing of the user statistics the FairCom Server collects, refer to the ctGUMS structure in the Snapshot Contents section below. The supported operations include:

File Snapshot Support

The SnapShot API function supports logging FairCom Server file statistics. For a listing of the file statistics the FairCom Server collects, refer to the ctGFMS structure in the Snapshot Contents section below. The supported operations include:

Transaction Statistics Support

The SnapShot API function supports logging FairCom Server transaction statistics. For a listing of the transaction statistics the FairCom Server collects, refer to the ctPSStransaction structure in the Snapshot Contents section. The supported operations include:

Snapshot support for FairCom Server compiled without transaction control

In V11 and later, support has been added for using the SnapShot feature when FairCom DB Server is compiled without transaction control (UNIFRMAT server, for example). (ctSNAPSHOT API and utilities such as ctstat and FairCom DB Monitor use this feature.)

Prior to this modification, FairCom DB Monitor had limited functionality on a server that did not support transaction processing: the Files Stats, System Snapshot, User Snapshot, Snapshot Favorites, and Function Timing tabs could not be used (typical error = 454, not supported).

Note: Although the snapshot API is now supported by a server, some functions (such as replication and transaction information) are not supported.

Replication State Snapshot Support

The SnapShot API function supports logging information about the FairCom Server replication state. For a listing of the transaction statistics the FairCom Server collects, refer to the ctPSSreplread structure in the Snapshot Contents section below. The supported operations include:

Combined Snapshot Support

The SnapShot API function supports logging combined snapshots containing FairCom Server system statistics and user and file statistics for the specified activated users and files. This ability is supported by the following operation:

Snapshot Contents

Each snapshot is composed of a structure (ctGSMS for system info, ctGUMS for user info and ctGFMS for file info) followed optionally by a variable region. Each of the three snapshot structures contains information about the size of the structure and the amount of variable information, if any, following the structure. Further, if the output buffer is too small to hold all the variable information, the contents member of the main structure will have the ctSNAPSHOTtruncated bit turned on if information had to be truncated.

Note: These structure definitions may vary depending on the version of the FairCom Server and FairCom DB SDK you are using. Please consult the c-tree headers for the actual structure definition that is in use. Differences in definitions between a given ctstat client and FairCom Server may make ctstat incompatible with that server, in which case the ctstat utility displays an error message indicating structure incompatibility.

The only variable information consists of an array of function timing results for the overall system or for a user. The array is composed of ctWRKSTT structures. Function timings are described below, but these timings are only for direct calls from c-tree clients.

Most of the elapsed times accumulated in the snapshots are based on high-resolution timers that report in numbers of ticks and are stored in eight-byte integers. The description following each structure member contains a ‘^’ to indicate results based on high-resolution timers. To convert such times to seconds, divide by the ticks per second, a system dependent value stored in the scthrbastim member of each snapshot structure.

System Snapshot Structure

Note: System snapshots may include an optional function-timing array of ctWRKSTT structures as described in the Function Timing section below.

/* system snapshot versions */

#define ctGSMS_VERSION_FILEOPS 21 /* minimum snapshot version that includes fileops fields */

#define ctGSMS_VERSION_CTREPFLSBG 22 /* minimum snapshot version that includes replication log flush by background thread */

#define ctGSMS_VERSION_COMPFLG5 23 /* minimum snapshot version that includes 5th compatibility flag */

#define ctGSMS_VERSION_BLKOPEN 24 /* minimum snapshot version that includes blocking open values */

#define ctGSMS_VERSION_SPLCHEPCT 25 /* minimum snapshot version that includes special cache % limit */

#define ctGSMS_VERSION_USERMEMORYTRIM 26 /* minimum snapshot version that includes user memory trim counter */

#define ctGSMS_VERSION_DATAHASH 27 /* minimum snapshot version that includes data cache hash collision counter */

#define ctGSMS_VERSION_IDXHASH 28 /* minimum snapshot version that includes index cache hash collision counter */

#define ctGSMS_VERSION_SHMEM_MAX_SPIN 29 /* minimum snapshot version that includes shared memory max spin setting */

#define ctGSMS_VERSION_DIAGFLG4 30 /* minimum snapshot version that includes 4th diagnostic flag */

#define ctGSMS_VERSION_LOGFLSTIMEDETAIL 31 /* minimum snapshot version that includes transaction log flush time detail */

#define ctGSMSvern ctGSMS_VERSION_LOGFLSTIMEDETAIL /* ctGSMS (system snapshot) version # */

typedef struct ctgsms {

ULONG client_ver; /* client version of structure */

ULONG server_ver; /* server version of structure */

ULONG fixlen; /* length of fixed portion of snapshot */

ULONG varlen; /* length of variable region (if any) */

ULONG contents; /* bit map of var len contents */

ULONG logsets; /* number of multiple log sets */

LONG8 snapshottm; /* snapshot time stamp: seconds since 70*/

LONG8 sctdnd_red; /* delete node thread queue reads */

LONG8 sctdnd_wrt; /* delete node thread queue writes */

LONG8 sctdnd_rwt; /* delete node thread queue rewrites */

LONG8 sctdnd_abn; /* delete node thread queue abandons */

LONG8 sctdnd_rmv; /* delete node thread queue removals (no longer used) */

LONG8 sctdnd_non; /* delete node thread queue no action */

LONG8 sctloktry; /* count of lock attempts */

LONG8 sctlokhlk; /* subcount of hdr lock attempts */

LONG8 sctlokdny; /* count of locks denied */

LONG8 sctlokblk; /* count of locks blocked */

LONG8 sctlokhbk; /* subcount of header blocks */

LONG8 sctlokdlk; /* count of dead locks */

LONG8 sctlokfre; /* count of locks freed */

LONG8 sctlokrel; /* count of blocks released */

LONG8 sctlokcur; /* current count of locks held */

LONG8 sctblkcur; /* current count of blocked requests */

LONG8 scttrntim; /* cumulative transaction time^ */

LONG8 scttrncnt; /* cumulative transaction count */

LONG8 scttrnmax; /* maximum elapsed tran time^ */

ULONG sct_compflg3; /* 3rd compatibility word */

LONG sctrqtmonint; /* request time monitor interval */

LONG8 scttot_call; /* system-wide c-tree calls */

LONG8 scttot_recv; /* system-wide time waiting for request^*/

LONG8 scttot_work; /* system-wide time performing requests^*/

LONG8 scttot_send; /* system-wide time to send request^ */

LONG8 sctmemhgh; /* system memory highwater mark */

LONG8 sctmemsum; /* current aggregate sum */

#ifdef ctFeatATOMICop64

volatile

AULONG8 sct_dbrqs; /* data buffer requests */

volatile

AULONG8 sct_dbhit; /* data buffer hits */

#else

LONG8 sct_dbrqs; /* data buffer requests */

LONG8 sct_dbhit; /* data buffer hits */

#endif

#ifdef ctFeatATOMICop64

volatile

AULONG8 sct_ibrqs; /* index buffer requests */

volatile

AULONG8 sct_ibhit; /* index buffer hits */

#else

LONG8 sct_ibrqs; /* index buffer requests */

LONG8 sct_ibhit; /* index buffer hits */

#endif

LONG8 sct_rdops; /* number of read operations */

LONG8 sct_rdbyt; /* bytes read */

LONG8 sct_wrops; /* number of write operations */

LONG8 sct_wrbyt; /* bytes written */

LONG8 sct_rcops; /* number of comm read operations */

LONG8 sct_rcbyt; /* comm bytes read */

LONG8 sct_wcops; /* number of comm write operations */

LONG8 sct_wcbyt; /* comm bytes written */

LONG8 sctwlgops; /* number of log write operations */

LONG8 sctwlgbyt; /* bytes written to log file */

LONG8 sctrlgops; /* number of log read operations */

LONG8 sctrlgbyt; /* bytes read from log file */

LONG8 sctxlgops; /* number of log extension operations */

LONG8 sctxlgbyt; /* log file extension bytes */

LONG8 sct_trbeg; /* # transaction begins */

LONG8 sct_trend; /* # transaction ends */

LONG8 sct_trabt; /* # transaction aborts */

LONG8 sct_trsav; /* # transaction savepoints */

LONG8 sct_trrst; /* # transaction restores */

LONG8 sct_trfls; /* # transaction log flush writes */

LONG8 sctsync_dosfls; /* DOSFLUSH sync calls */

LONG8 sctsync_logfil; /* transaction log sync calls */

LONG8 sctsync_regfil; /* c-tree file sync calls */

LONG8 scthrtimbas; /* high res timer ticks per sec^ */

LONG8 sctchkpnttim; /* cumulative checkpoint time^ */

LONG8 sctchkpntsiz; /* cumulative checkpoint size */

LONG8 sctchkpnt[ctCHKPdetail];

/* intermediate checkpoint times^ */

LONG8 sctcmtdlytry; /* cmtdly eligible count */

LONG8 sctcmtdlycnt; /* cmtdly concurrent trans exist */

LONG8 sctcmtdlycoh; /* cmtdly cohorts exist (success) */

LONG8 sctcmtcohmax; /* cmtdly max cohort count */

LONG8 sctcmtdlyclr; /* cmtdly cohort appeared (success) */

LONG8 sctcmtlopclr; /* cmtdly appeared loop count */

LONG8 sctcmtdlynot; /* cmtdly direct flush count */

LONG8 sctcmtlopfls; /* cmtdly log flush loop count */

LONG8 sctcmtdlydfr; /* cmtdly loop defer count */

ULONG sctcmtlopmax; /* max loop defer count */

ULONG sctcmtmaxadp; /* max adaptive LFW loop counter */

LONG8 sctcmtavgadp; /* avg adaptive LFW loop counter */

LONG8 sctcmtlopfls2; /* cmtdly log flush2 loop count */

LONG8 stimchk[8]; /* diagnostic time ct_udefer check */

LONG8 selapwrktim; /* elapsed time func timings turned on */

LONG8 selapsrvtim; /* elapsed server operation time */

LONG8 sctcmtblktim; /* accum commit delay block time (requested) */

LONG8 sctcmtclrtim; /* accum commit delay clear time (requested) */

LONG8 stimchktarget; /* ct_udefer expected result */

LONG8 stimchkconsec; /* time between consecutive hrt in usec */

LONG8 stimchkavg; /* avg time ofr hrtimer call in usec */

LONG8 sctcmtdlyfls; /* cmtdly log flushed anyway (success) */

LONG8 sctcmtfls; /* commit/abort tran log flush rqst */

LONG8 sctbegfls; /*        begin tran log flush rqst */

LONG8 sctidxfls; /*       LOGIDX tran log flush rqst */

LONG8 sctupdfls; /* file 1st upd tran log flush rqst */

LONG8 sctcmtavgdfr; /* accum commit delay block time (actual) */

LONG8 sctcmtmaxdfr; /* max commit delay block time */

LONG8 sctcmtdlyfls2; /* dmtdly log flushed anyway2 (success) */

LONG8 scttrnbox; /* width of cttrndet boxes^ */

LONG8 sctlogfls; /* log flush count */

LONG8 sctlogtim; /* log flush time */

#ifdef ctdbMEMSUM

LONG8 sctSQLmemsum; /* current aggregate sum for SQL */

LONG8 sctDBmemsum; /* current aggregate sum for CTDB-STUB  */

#else

LONG8 available81[2]; /* available for future use */

#endif

LONG8 sctredcmtlpc; /* RED_CMTLOK loop count */

LONG8 sctrepfls; /* replication log flush rqst */

LONG8 sctchkbufwrt; /* checkpoint index buffer writes */

LONG8 sctchkchewrt; /* checkpoint data cache writes */

ULONG scttrndet[ctTRNTdetail];

/* tran time detail */

LONG sctactfil; /* # open physical files */

LONG scttotfil; /* # open logical files */

LONG scttotblk; /* # c-tree FCBs in use */

LONG sctactfilx; /* max physical files opened */

LONG scttotfilx; /* max logical files opened */

LONG scttotblkx; /* max c-tree FCBs in use */

ULONG sctnusers; /* number of users */

ULONG sctnusersx; /* max number of users */

#ifdef ctFeatATOMICop64

volatile

AULONG sctloknm; /* net locks over unlocks */

volatile

AULONG sctloknmx; /* max net locks over unlocks */

#else

LONG sctloknm; /* net locks over unlocks */

LONG sctloknmx; /* max net locks over unlocks */

#endif

ULONG sct_mxbuf; /* number of index buffer pages */

ULONG sctbufcnt; /* index buffer pages in use */

ULONG sctbufhgh; /* max index buffers in use */

ULONG sct_dxbuf; /* available data cache pages */

ULONG sctdatcnt; /* data cache pages in use */

ULONG sctdathgh; /* max data cache pages in use */

ULONG scmtblkseq; /* not flow through cmtdly block */

ULONG sct_compflg; /* compatibility flag */

ULONG sct_compflg2; /* 2nd compatibility word */

ULONG sct_diagflg; /* diagnostic flag */

ULONG sct_diagflg2; /* 2nd diagnostic flag */

LONG sct_cmtdly; /* commit delay */

LONG sct_chkdly; /* checkpoint tranac_cnt delay */

ULONG sct_cmtscl; /* cmtdlytim block to clear ratio */

ULONG sct_cmtbas; /* cmtdlytim cohort size measure */

ULONG stimchkusec; /* ct_udefer test interval in usec's */

ULONG stimchkiter; /* ct_udefer test iterations */

ULONG sct_udefer_thld;/* ct_udefer usec threshold */

ULONG sct_udefer_64yd;/* sleep duration for 64 yields (usec) */

ULONG sctITIMretry; /* ITIM_ERR auto retry count */

ULONG sctTPNDretry; /* TPND_ERR auto retry count */

ULONG sctITIMlimit; /* ITIM_ERR auto retry limit */

ULONG sctITIMfailed; /* ITIM_ERR auto retry failed */

ULONG sctredcmtdfr; /* RED_CMTLOK loop defer time millisecs */

ULONG sctredcmtlpr; /* RED_CMTLOK loopers */

ULONG sctITIMdefer; /* ITIM_ERR auto retry defer */

LONG8 sctismaddcnt; /* ISAM record add count */

LONG8 sctismdelcnt; /* ISAM record delete count */

ULONG sct_dxspllmt; /* avail   data file special cache pages*/

ULONG sct_dxsplcnt; /* actual  data file special cache pages*/

ULONG sct_dxsplhgh; /* maximum data file special cache pages*/

ULONG sctcpcnt; /* check point count */

LONG sct_numvfil; /* number of virtual files open */

LONG sct_avlfil; /* # available file control blocks */

#ifdef ctFeatATOMICop

volatile

AULONG sctactusr; /* number of threads active in foregrnd */

#else

LONG sctactusr; /* number of threads active in foregrnd */

#endif

LONG sct_nutcnt; /* # of index buffers on upd list (tran)*/

LONG sct_nupcnt; /* # of index buffers on upd list */

LONG sct_dutcnt; /* # of data caches on upd list (tran) */

LONG sct_dupcnt; /* # of data caches on upd list */

LONG sctwatshtdwn; /* wait on shut down in seconds */

LONG8 sct_wrtcmp; /* calls to ctwrtlog */

LONG8 sct_no_cmp; /* ctwrtlog below compression threshold */

LONG8 sct_tp_cmp; /* trantyp not compressed (DIFIMAGE) */

LONG8 sct_rj_cmp; /* ctwrtlg compression not effective */

LONG8 sct_bytcmp; /* total bytes saved by log compression */

LONG sctlogcmpfloor; /* no log compression below this size */

LONG sctlogcmpfactor;/* out-size must not > this % of in-size*/

COUNT sct_mxfil; /* total number of c-tree FCBs */

COUNT sct_ndsec; /* # of sectors per node */

UTEXT sflvr; /* server byte order */

UTEXT salgn; /* server alignment */

UTEXT spntr; /* server pointer size */

UTEXT maxdhashcoll; /* max data cache hash collisions (expect < 20) */

LONG8 sctlokkil; /* count of killed locks */

LONG8 sctismupdcnt; /* ISAM record update count */

LONG8 sctismredcnt; /* ISAM record read count */

LONG8 sctloktim; /* cumulative lock wait time^ */

LONG8 sctlokcnt; /* cumulative lock wait count */

LONG8 sctlokmax; /* maximum elapsed lock wait time^ */

LONG8 sctlokbox; /* width of detail boxes^ */

ULONG sctlokdet[ctLOCKdetail];

/* lock block time details */

LONG8 sctxlktim; /* cumulative idx lock wait time^ */

LONG8 sctxlkcnt; /* cumulative idx lock wait count */

LONG8 sctxlkmax; /* maximum elapsed idx lock wait time^ */

LONG8 sctxlkbox; /* width of detail boxes^ */

ULONG sctxlkdet[ctXLOKdetail];

/* idx lock block time details */

TEXT description[128];/* optional text description */

#ifdef ctFeatATOMICop

volatile

AULONG sctcurusr; /* threads in use total (ctaddwork) */

volatile

AULONG sctmaxusr; /* max threads in use   (ctaddwork) */

#else

LONG sctcurusr; /* threads in use total (ctaddwork) */

LONG sctmaxusr; /* max threads in use   (ctaddwork) */

#endif

ULONG scti_svvr; /* server internal version number */

ULONG sctioblkretry; /* I/O error auto retry count */

ULONG sct_compflg4; /* 4th compatibility word */

ULONG sct_dxscan; /* avail   scan cache pages */

ULONG sct_dxscancnt; /* actual  scan cache pages */

ULONG sct_dxscanhgh; /* maximum scan cache pages */

ULONG sct_hbTbuf; /* index hash bin Threshold */

ULONG sct_hbRbuf; /* index hash bin Reset limit */

ULONG sct_hbTcac; /*  data hash bin Threshold */

ULONG sct_hbRcac; /*  data hash bin Reset limit */

ULONG sct_diagflg3; /* 3rd diagnostic flag */

ULONG sctmldmsec; /* DELAYED_DURABILITY (in msecs) */

LONG sarplogint; /* auto restore point log interval */

LONG sarptimout; /* auto restore point tran timeout */

#ifdef ctFeatATOMICop64

volatile

AULONG8 sctdatflschk; /*     data cache tran log checks */ 

#else

LONG8 sctdatflschk; /*     data cache tran log checks */ 

#endif

LONG8 sctdatfls; /* data cache tran log flush rqst */

LONG8 sctmldfls; /*   MAX LOG DEFER log flush rqst */

LONG sarpoptions; /* auto restore point options */

LONG sarplastlog; /* auto restore point last log written */

LONG8 sphyopncnt; /* number of physical file opens */

LONG8 sphyclscnt; /* number of physical file closes */

BGFLSS bgflss[2]; /* background flush state vars */

LONG8 slogopncnt; /* number of logical file opens */

LONG8 slogclscnt; /* number of logical file closes */

LONG8 sfilcrecnt; /* number of file creates */

LONG8 sfilrencnt; /* number of file renames */

LONG8 sfildelcnt; /* number of file deletes */

LONG8 sctrepflsbg; /* replication log flush by background thread */

LONG8 susrmemtrim; /* number of user memory trim operations */

#ifdef ctFeatATOMICop64

volatile AULONG8 susrmemtrimtot; /* total bytes of user memory trimmed */

#else

ULONG8 susrmemtrimtot; /* total bytes of user memory trimmed */

#endif

LONG sct_mxfil32; /* total number of c-tree FCBs */

ULONG sct_compflg5; /* 5th compatibility word */

ULONG sblockingOpenRetryLimit; /* BLOCKING_OPEN_RETRY_LIMIT setting */

ULONG sblockingOpenRetryDefer; /* BLOCKING_OPEN_RETRY_DEFER setting */

ULONG sct_dxspllmtpct; /* data file special cache pages % limit */

ULONG sct_dxscanpct; /* avail   scan cache pages pct */

UTEXT maxihashcoll;   /* max index cache hash collisions */

/* 241215: Solaris Padding Work Around */

#if ( (defined(ctPortSOLARIS10) || defined(ctPortSOLARIS_V5_9_OR_BEFORE) ) && defined(ctPortSOL386) && !defined(ctPortSOLARIS_64))

UTEXT   tavail[4]; /* <-- available for use */

#else

UTEXT   tavail[3]; /* <-- available for use */

#endif

LONG shmem_max_spin; /* current SHMEM_MAX_SPIN setting */

ULONG sct_diagflg4; /* 4th diagnostic flag */

ULONG sctTranLogFlushDetailsFixed[ctTRNLdetail]; /* tran log flush time fixed detail */

ULONG sctTranLogFlushDetailsAdaptive[ctTRNLdetail]; /* tran log flush time adaptive detail */

ULONG sctTranLogFlushDetailsLow; /* log flush low count since last checkpoint */

ULONG sctTranLogFlushDetailsHigh; /* log flush high count since last checkpoint */

LONG8 sctTranLogFlushBoxFixed; /* width of fixed detail boxes^ */

LONG8 sctTranLogFlushBoxAdaptive; /* width of adaptive detail boxes^ */

LONG8 sctTranLogFlushMaxTime; /* maximum elapsed tran log flush time^ */

LONG8 sctlogtimAtChkpnt; /* log flush time at last checkpoint^ */

LONG8 sctlogflsAtChkpnt; /* log flush count at last checkpoint */

LONG8 sctTranLogFlushFixedLastClearedTime; /* time of last clearing of fixed histogram box width (Unix time value) */

LONG8 sctTranLogFlushAdaptiveLastAdjustmentTime; /* time of last adjustment to adaptive histogram box width (Unix time value) */

ULONG sctTranLogFlushAdaptiveAdjustments; /* number of times adaptive histogram box width has been adjusted */

ULONG savail4; /* <-- available for use */

LONG8 savail8[2]; /* <-- available for use */

} ctGSMS, ctMEM * pctGSMS;

User Snapshot Structure

Users may define up to 8 different timing baskets. A begin mark call establishes a high resolution starting time for the specified basket. An end mark call increments the specified basket counter, adds the elapsed time to the timing basket, and clears the starting time. Attempting to begin a mark that is not clear or end a mark that has not begun results in an error. The baskets are numbered from 0 to 7. Their results appear in the user snapshot structure in the smarkcnt and smarktim arrays.

User snapshots may include an optional function-timing array of ctWRKSTT structures as described in the Function Timing section below.

(Version 2 of ctgums SNAPSHOT structure)

typedef struct ctgums {

     ULONG   client_ver;     /* client version of structure          */

     ULONG   server_ver;     /* server version of structure          */

     ULONG   fixlen;         /* length of fixed portion of snapshot  */

     ULONG   varlen;         /* length of variable region (if any)   */

     ULONG   contents;       /* bit map of var len contents          */

     ULONG   unused;         /* available for use                    */

     LONG8   snapshottm;     /* snapshot time stamp: seconds since 70*/

     LONG8   strntsum;       /* user trntime sum^                    */

     LONG8   strntmax;       /* user trntime max^                    */

     LONG8   scthrtimbas;    /* high res timer ticks per sec^        */

     LONG8   avail8[9];      /* available for use                    */

     LONG8   smarktim[8];    /* user mark elapsed times^             */

     ULONG   smarkcnt[8];    /* user mark counts                     */

     ULONG   slOWNR;         /* thread handle (OWNER)                */

     ULONG   sisolev;        /* transaction isolation level          */

     ULONG   sctops;         /* set operation state bit mask         */

     ULONG   sctstate;       /* internal state bit mask              */

     ULONG   sabnlog;        /* begtran log for abandoned tran       */

     ULONG   strntcnt;       /* user trntime count                   */

     ULONG   sctutrbeg;      /* user tran begin count                */

     ULONG   sctutrend;      /* user tran end count                  */

     ULONG   sctutrabt;      /* user tran abort count                */

     ULONG   snbrfile;       /* number of files in use               */

     ULONG   slogtime;       /* logon time                           */

     ULONG   srqstime;       /* last request time                    */

     ULONG   srqsfunc;       /* last request function #              */

     ULONG   srqssubf;       /* last request sub-function #          */

     ULONG   sactflag;       /* active / inactive flag               */

     ULONG   sct_trdflg;     /* thread operation flag                */

     ULONG   sabnflg;        /* stage of abandon tran processing     */

     ULONG   sctusrprf;      /* user profile word                    */

     ULONG   sctumxfil;      /* max files for user                   */

     ULONG   scthghfil;      /* highest file number limit            */

     ULONG   sloktry;        /*  total lock attempts                 */

     ULONG   slokhlk;        /* header lock attempts                 */

     ULONG   slokblk;        /*  total lock wait count               */

     ULONG   slokhbk;        /* header lock wait count               */

     ULONG   slokdlk;        /* deadlocks                            */

     ULONG   slokdny;        /*  total locks denied                  */

     ULONG   slokfre;        /*  total locks freed                   */

     ULONG   slokrel;        /*  total blocks released               */

     ULONG   slokcur;        /* current count of locks held          */

     ULONG   sblkcur;        /* current count of blocked requests    */

  ULONG   sctstat2;       /* internal state bit mask 2      */

  ULONG   slokkil;       /* killed: removed from lock wait list  */

  ULONG   sipaddr;       /* client IP address      */

  ULONG   avail4[7];

     UTEXT   clflvr;         /* user flavor                          */

     UTEXT   clalgn;         /* user alignment                       */

     UTEXT   clpntr;         /* user pointer size                    */

     UTEXT   sflvr;          /* server byte order                    */

     UTEXT   salgn;          /* server alignment                     */

     UTEXT   spntr;          /* server pointer size                  */

     UTEXT   availt[26];

     TEXT    snodname[32];   /* node name                            */

     TEXT    sct_userid[32]; /* user id                              */

} ctGUMS, ctMEM * pctGUMS;

File Snapshot Structure

(Version 2 of ctgfms SNAPSHOT structure)

typedef struct ctgfms {

  ULONG   client_ver;     /* client version of structure          */

  ULONG   server_ver;     /* server version of structure          */

  ULONG   fixlen;         /* length of fixed portion of snapshot  */

  ULONG   varlen;         /* length of variable region (if any)   */

  LONG8   snapshottm;     /* snapshot time stamp: seconds since 70*/

  LONG8   phyrec;         /* physical file size                   */

  LONG8   numrec;         /* logical file size                    */

  LONG8   sernum;         /* serial number                        */

  LONG8   nument;         /* active entries                       */

  LONG8   hghtrn;         /* tran# high water mark for idx        */

  LONG8   tstamp;         /* update time stamp                    */

  LONG8   mxfilz;         /* max file size high word              */

  LONG8   fredops;        /* file specific # of read ops          */

  LONG8   fredbyt;        /* file specific # of bytes read        */

  LONG8   fwrtops;        /* file specific # of write ops         */

  LONG8   fwrtbyt;        /* file specific # of bytes written     */

  LONG8   scthrtimbas;    /* high res timer ticks per sec^        */

  LONG8   mhghbyt;        /* memory file high water bytes         */

  LONG8   fredtim;        /* file specific disk read call time    */

  LONG8   fwrttim;        /* file specific disk write call time   */

  LONG8  avail8[6];

  ULONG   idxhgt;         /* index height                         */

  ULONG   fileid;         /* unique file id                       */

  ULONG   servid;         /* unique server id                     */

  ULONG   timeid;         /* time id#                             */

  ULONG   nodsiz;         /* node size                            */

  ULONG   reclen;         /* fixed data record length             */

  ULONG   logtyp;         /* permanent components of file mode    */

  ULONG   maxkbl;         /* maximum key bytes leaf-var           */

  ULONG   maxkbn;         /* maximum key bytes non leaf-var       */

  ULONG   filtyp;         /* flag for file type                   */

  ULONG   keylen;         /* key length                           */

  ULONG   kmem;           /* key member #                         */

  ULONG   nmem;           /* number of members                    */

  ULONG   suptyp;         /* super file type                      */

  ULONG   maxmrk;         /* maximum exc mark entries per leaf    */

  ULONG   hdrseq;         /* wrthdr sequence #                    */

  ULONG   floktry;        /*  total lock attempts                 */

  ULONG   flokhlk;        /*  header lock attempts                */

  ULONG   flokblk;        /*  total lock wait count               */

  ULONG   flokhbk;        /*  header lock wait count              */

  ULONG   flokdlk;        /*  deadlocks                           */

  ULONG   flokdny;        /*  total locks denied                  */

  ULONG   flokfre;        /*  total locks freed                   */

  ULONG   flokrel;        /*  total blocks released               */

  ULONG   flokcur;        /* current count of locks held          */

  ULONG   fblkcur;        /* current count of blocked requests    */

  ULONG   datlmt;         /* max number of special cache pages    */

  ULONG   datspl;         /* actual number of special cache pages */

  ULONG   bufcnt;         /* number of buffer pages               */

  ULONG   datcnt;         /* number of data cache pages           */

  ULONG   numchn;         /* number of channels                   */

  ULONG   usrcnt;         /* number of users with file open       */

  ULONG   memcnt;         /* current # of memory records          */

  ULONG   hghcnt;         /* high water # of memory recors        */

  ULONG   flokkil;        /* killed locks                         */

  ULONG   avail4[5];

  ULONG   contents;       /* bit map of var len contents          */

  LONG    segmax;         /* maximum # of segments                */

  LONG    seglst;         /* # activated segments                 */

  UTEXT   updflg;         /* update (corrupt) flag                */

  UTEXT   ktype;          /* file type flag                       */

  UTEXT   autodup;        /* duplicate flag                       */

  UTEXT   deltyp;         /* flag for type of idx delete          */

  UTEXT   keypad;         /* padding byte                         */

  UTEXT   flflvr;         /* file flavor                          */

  UTEXT   flalgn;         /* file alignment                       */

  UTEXT   flpntr;         /* file pointer size                    */

  UTEXT   sflvr;          /* server byte order                    */

  UTEXT   salgn;          /* server alignment                     */

  UTEXT   spntr;          /* server pointer size                  */

  TEXT    availt[9];

  TEXT    filename[256];  /* file name                            */

} ctGFMS, ctMEM * pctGFMS;

SQL Snapshot Structure

(Version 2 of ctsqls SNAPSHOT structure)

typedef struct ctsqls {

ULONG client_ver; /* client version of structure */

ULONG server_ver; /* server version of structure */

ULONG fixlen; /* length of fixed portion of snapshot */

ULONG varlen; /* length of variable region (if any) */

ULONG contents; /* bit map of var len contents */

ULONG unused; /* available for use */

LONG8 snapshottm; /* snapshot time stamp: seconds since 70*/

LONG8 sctmemhgh; /* SQL memory highwater mark */

LONG8 sctmemsum; /* current aggregate sum */

LONG8 sctdscrqs; /* dynamic statement cache requests */

LONG8 sctdschit; /* dynamic statement cache hits */

LONG8 sctsscrqs; /* static statement cache requests */

LONG8 sctsschit; /* static statement cache hits */

LONG sctmemcnt; /* current net SQL memory allocations */

LONG sctdsctot; /* current dynamic stmt cache entries */

LONG sctdschgh; /* highest dynamic stmt cache entries */

LONG sctdscmax; /* maximum dynamic stmt cache entries */

LONG sctssctot; /* current static stmt cache entries */

LONG sctsschgh; /* highest static stmt cache entries */

LONG sctsscmax; /* maximum static stmt cache entries */

UTEXT sflvr; /* server byte order */

UTEXT salgn; /* server alignment */

UTEXT spntr; /* server pointer size */

TEXT availt; /* available for use */

} ctSQLS, ctMEM * pctSQLS;

Function Timing

If function timings are enabled by the DIAGNOSTIC SNAPSHOT_WORKTIME option or by the ctPSStimeWorkOn opcode, system and user snapshots have a variable region comprised of an array of ctWRKSTT structures:

typedef struct wrkstt {

   ULONG   fnc;          /* function # used during compression of stats */

   ULONG   cnt;          /* function call count */

   LONG8   tim;          /* high resolution elapsed function time^ */

}  ctWRKSTT, ctMEM * pctWRKSTT;

The first such structure in the array is a summary: its fnc member is zero, and the cnt member indicates the number of actual functions that had non-zero call counts, and the tim member has the total of all the elapsed times for all the functions. Even if the output buffer (for an on-demand snapshot with results returned directly) is too small to hold all the function-timing results, the summary is “complete.” The varlen member of the main structure indicates how many bytes of variable information have been returned. The contents member of the main structure will have the ctSNAPSHOTtruncated bit turned on if information had to be truncated.

Each subsequent entry in the array holds a function number, a call count, and a total elapsed time in the function.

Transaction Statistics

(ctPSStransaction SNAPSHOT structure)

The following values are returned:

LONG8  tranno;    /* next transaction number                    */

LONG   lowlog;    /* oldest active transaction log number       */

LONG   curlog;    /* current transaction log number             */

LONG   lstpnt;    /* last byte position written to log          */

LONG   lstent;    /* last entry on disk or in log buffer        */

LONG   lstsuc;    /* byte position of last SUCTRAN entry        */

LONG   tfil;      /* next transaction file number               */

ctstat -vat can be used to view the transaction statistics. Sample output is shown below:

   lowlog    curlog       lstent       lstpnt       lstsuc          tranno         tfil

        1         4      2702686      2702092      2697872           51806        17298

where:

loglow is the server's lowest-numbered active log.

curlog is the server's current log.

lstent is the offset in the current log where the last entry was written (which may still be in the in-memory log buffer).

lstpnt is the last byte position written to the log file on disk.

lstsuc is the offset of the last SUCTRAN or CLSTRAN entry in the log.

tranno is the next available transaction number.

tfil is the next available transaction file number.

Replication State Statistics

(ctPSSreplread SNAPSHOT structure)

The following values are returned:

name                               lowlog  curlog       curpos

MASTER(SERVER)                          1       4      2702686

REPLAGENT02                             2       3      3830328

REPLAGENT                               4       4      2702092

where:

lowlog is the lowest numbered active transaction log.

curlog and curpos are the current log number and offset.

The first entry is always the source server's transaction log state. An additional entry is displayed for each Replication Agent that has registered with the source server.

For a Replication Agent, lowlog indicates the lowest-numbered transaction log that the agent requires to exist in the event that it needs to restart its scan from its current position. curlog and curpos indicate the Replication Agent's current position in reading the logs.

In the above example, note that the Replication Agent called REPLAGENT can be considered "up-to-date" because its current log and current position (curlog and curpos) match the last byte offset written to the log, curlog and lstpnt in the ctstat -vat output shown below:

   lowlog    curlog       lstent       lstpnt       lstsuc          tranno         tfil

        1         4      2702686      2702092      2697872           51806        17298