##xtstat## can be used to monitor all internal activity of [[PBXT]].
##xtstat## polls the ##INFORMATION_SCHEMA.PBXT_STATISTICS## table. The poll interval can be set using the <<code>>--delay<</code>> option, and is 1 second by default.
For most statistics, ##xtstat## will display the difference in values between the current and previous polls. For example, if bytes written current value is 1000, and on the previous call it was 800, then ##xtstat## will display 200. This means that 200 bytes were written to disk in the intervening period.
== Using ##xtstat## ==
Invoke xtstat as follows:
<<code lang=sh inline=false>>
$ xtstat [ options ]
<</code>>
For example, to poll every 10 seconds:
<<code lang=sh inline=false>>
xtstat -D10
<</code>>
Note that statistic counters are never reset, even if a rollback occurs. For example, if an <<sql>>UPDATE<</sql>> statement is rolled back, ##xtstat## will still indicate that one write statement (see stat-write below) was executed.
If MariaDB shuts down or crashes, ##xtstat## will attempt to reconnect. ##xtstat## can be terminated any time using the ##CTRL-C## key cimbination.
Before [[PBXT]] has recovered, not all statistics are available. In particular, the statistics relating to PBXT background threads are not available (including the ##sweep## and ##chkpnt## statistics).
=== Command line options ===
##xtstat## options are as follows:
|=Option<<code>> <</code>>|=Description|
|<<code>>-?, --help<</code>>|Prints help text.|
|<<code>>-h, --host=value<</code>>|Connect to host.|
|<<code>>-u, --user=value<</code>>|User for login if not current user.|
|<<code>>-p, --password[=value]<</code>>|Password to use when connecting to server. If password is not given it's asked from the tty.|
|<<code>>-d, --database=value<</code>>|Database to be used (<<code>>pbxt<</code>> or <<code>>information_schema<</code>> required), default is <<code>>information_schema<</code>>|
|<<code>>-P, --port=value<</code>>|Port number to use for connection.|
|<<code>>-S, --socket=value<</code>>|Socket file to use for connection.|
|<<code>>-D, --delay=value<</code>>|Delay in seconds between polls of the database.|
|<<code>>--protocol=value<</code>>|Connection protocol to use: <<code>>default/tcp/socket/pipe/memory<</code>>|
|<<code>>--display=value<</code>>|Columns to display: use short names separated by {{{|}}} (the pipe character), partial match allowed. Use <<code>>--display=all<</code>> to display all columns available.|
Connection options will also be taken from the MySQL config file if available.
==== Size indicators
Values displayed by ##xtstat## are either a time in milliseconds, a value in bytes, or a counter. If these values are too large to be displayed then the value is rounded and a size indicator is added.
The following size indicators are used:
|<<code>>K<</code>>|:|Kilobytes //(1,024 bytes)//|
|<<code>>M<</code>>|:|Megabytes //(1,048,576 bytes)//|
|<<code>>G<</code>>|:|Gigabytes //(1,073,741,024 bytes)//|
|<<code>>T<</code>>|:|Terabytes //(1,099,511,627,776 bytes)//|
|<<code>>t<</code>>|:|thousands //(1,000s)//|
|<<code>>m<</code>>|:|millions //(1,000,000s)//|
|<<code>>b<</code>>|:|billions //(1,000,000,000s)//|
=== Statistics
The following is a list of the statistics displayed by ##xtstat##. Each statistic as a two-part display name. The first part is the category and the second part is the type.
You can select categories and types for display, as you require. For example <<code>>--display=read<</code>> will display all read activity, <<code>>--display=xact|stat<</code>> will display transaction and statement activity.
Note, for diagnostics it is best to capture all statistics. The reason is because you never now where a problem might turn up, so without certain statistics you may not be able to identify the problem.
|=Display<<entity>>nbsp<</entity>>name|=Name|=Description|
|<<code>>time-curr<</code>>|Current Time|The current time in seconds|
|<<code>>time-msec<</code>>|Time<<entity>>nbsp<</entity>>Since<<entity>>nbsp<</entity>>Last<<entity>>nbsp<</entity>>Call|Time passed in milliseconds since last statistics call|
|<<code>>xact-commt<</code>>|Commit Count|Number of transactions committed|
|<<code>>xact-rollb<</code>>|Rollback Count|Number of transactions rolled back|
|<<code>>xact-waits<</code>>|Wait for Xact Count|Number of times waited for another transaction|
|<<code>>xact-dirty<</code>>|Dirty Xact Count|Number of transactions still to be cleaned up. This also includes all the currently running transactions. Cleanup means that the Sweeper thread must still scan the transcation and collect/mark any "garbage" left by the transaction. Garbage is, for example, versions of rows that are no longer visiable by any transaction.|
|<<code>>stat-read<</code>>|Read Statements|Number of SELECT statements|
|<<code>>stat-write<</code>>|Write Statements|Number of UPDATE/INSERT/DELETE statements|
|<<code>>rec-in<</code>>|Record Bytes Read|Bytes read from the record/row files|
|<<code>>rec-out<</code>>|Record Bytes Written|Bytes written to the record/row files. This data is transfered from the transaction logs to the handle data (xtd) and the row index files (xtr).|
|<<code>>rec-syncs/ms<</code>>|Record File Flushes|2 values separated by a '/': the number of flushes to data handle (.xtd) and row index (.xtr) files and the time taken in milliseconds to perform the flush operations.|
|<<code>>rec-hits<</code>>|Record Cache Hits|Hits when accessing the record cache. The record cache caches the data handle (.xtd) and row index (.xtr) files.|
|<<code>>rec-miss<</code>>|Record Cache Misses|Misses when accessing the record cache|
|<<code>>rec-frees<</code>>|Record Cache Frees|Number of record cache pages freed|
|<<code>>rec-%use<</code>>|Record Cache Usage|Percentage of record cache in use. This value is displayed by xtstat as a percentage of the total cache available, but the value returned by PBXT_STATISTICS table is in bytes used.|
|<<code>>ind-in<</code>>|Index Bytes Read|Bytes read from the index files|
|<<code>>ind-out<</code>>|Index<<entity>>nbsp<</entity>>Bytes<<entity>>nbsp<</entity>>Written|Bytes written to the index files. This data is transfered from the index log files (ilog) to the index files (xti), during a consistent flush of the index.|
|<<code>>ind-syncs/ms<</code>>|Index File Flushes|2 values separated by a '/': the number of flushes to index files and the time taken for the flush operations in milliseconds.|
|<<code>>ind-hits<</code>>|Index Cache Hits|Hits when accessing the index cache|
|<<code>>ind-miss<</code>>|Index Cache Misses|Misses when accessing the index cache|
|<<code>>ind-%use<</code>>|Index Cache Usage|Percentage of index cache used. This value is displayed by xtstat as a percentage of the total cache available, but the value returned by PBXT_STATISTICS table is in bytes used.|
|<<code>>ilog-in<</code>>|Index Log Bytes In|Bytes read from the index log files|
|<<code>>ilog-out<</code>>|Index Log Bytes Out|Bytes written to the index log files. This data is transfered from the index cache in main memory to the index log files (ilog) during a consistent flush of the index.|
|<<code>>ilog-syncs/ms<</code>>|Index Log File Syncs|2 values separated by a '/': the number of flushes to index log files and the time taken for the flush operations in milliseconds|
|<<code>>xlog-in<</code>>|Xact Log Bytes In|Bytes read from the transaction log files|
|<<code>>xlog-out<</code>>|Xact Log Bytes Out|Bytes written to the transaction log files. This is data transfered from the transaction log buffer (pbxt_transaction_buffer_size) to the transaction log files (.xlog). This transfer occurs on commit or when the transaction log buffer is full.|
|<<code>>xlog-syncs<</code>>|Xact Log File Syncs|Number of flushes to transaction log files|
|<<code>>xlog-msec<</code>>|Xact Log Sync Time|The time in milliseconds to flush transaction log files|
|<<code>>xlog-hits<</code>>|Xact Log Cache Hits|Hits when accessing the transaction log cache|
|<<code>>xlog-miss<</code>>|Xact Log Cache Misses|Misses when accessing the transaction log cache|
|<<code>>xlog-%use<</code>>|Xact Log Cache Usage|Percentage of transaction log cache used. This value is displayed by xtstat as a percentage of the total cache available, but the value returned by PBXT_STATISTICS table is in bytes used.|
|<<code>>data-in<</code>>|Data Log Bytes In|Bytes read from the data log files|
|<<code>>data-out<</code>>|Data Log Bytes Out|Bytes written to the data log files. This data is transfered from the data log buffer (pbxt_log_buffer_size) to the data log files (.dlog), when the buffer is full, or on commit.|
|<<code>>data-syncs<</code>>|Data Log File Syncs|Number of flushes to data log files|
|<<code>>data-msec<</code>>|Data Log Sync Time|The time in milliseconds spent flushing data log files|
|<<code>>to-chkpt<</code>>|Bytes to Checkpoint|Bytes written to the transaction log since the last checkpoint|
|<<code>>to-write<</code>>|Log Bytes to Write|Bytes written to the transaction log, still to be written to the database|
|<<code>>to-sweep<</code>>|Log Bytes to Sweep|Bytes written to the transaction log, still to be read by the Sweeper thread|
|<<code>>sweep-waits<</code>>|Sweeper<<entity>>nbsp<</entity>>Wait<<entity>>nbsp<</entity>>on<<entity>>nbsp<</entity>>Xact<<entity>>nbsp<</entity>><<entity>>nbsp<</entity>>|Attempts to cleanup a transaction|
|<<code>>scan-index<</code>>|Index Scan Count|Number of index scans|
|<<code>>scan-table<</code>>|Table Scan Count|Number of table scans|
|<<code>>row-sel<</code>>|Select Row Count|Number of rows selected|
|<<code>>row-ins<</code>>|Insert Row Count|Number of rows inserted|
|<<code>>row-upd<</code>>|Update Row Count|Number of rows updated|
|<<code>>row-del<</code>>|Delete Row Count|Number of rows deleted|
== More Information ==
Documentation on this page is based on the [[http://primebase.org/documentation/index.php#xtstat|xtstat documentation]] on the PrimeBase website.
Paul McCullagh's presentation from the 2010 User's Conference has some usage examples: http://www.primebase.org/download/pbxt-uc-2010.pdf
