xtstat

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 --delay 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:

$ xtstat [ options ]

For example, to poll every 10 seconds:

xtstat -D10

Note that statistic counters are never reset, even if a rollback occurs. For example, if an UPDATE 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 Description
-?, --helpPrints help text.
-h, --host=valueConnect to host.
-u, --user=valueUser for login if not current user.
-p, --password[=value]Password to use when connecting to server. If password is not given it's asked from the tty.
-d, --database=valueDatabase to be used (pbxt or information_schema required), default is information_schema
-P, --port=valuePort number to use for connection.
-S, --socket=valueSocket file to use for connection.
-D, --delay=valueDelay in seconds between polls of the database.
--protocol=valueConnection protocol to use: default/tcp/socket/pipe/memory
--display=valueColumns to display: use short names separated by | (the pipe character), partial match allowed. Use --display=all 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:

K:Kilobytes (1,024 bytes)
M:Megabytes (1,048,576 bytes)
G:Gigabytes (1,073,741,024 bytes)
T:Terabytes (1,099,511,627,776 bytes)
t:thousands (1,000s)
m:millions (1,000,000s)
b: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 --display=read will display all read activity, --display=xact|stat 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 nameNameDescription
time-currCurrent TimeThe current time in seconds
time-msecTime Since Last CallTime passed in milliseconds since last statistics call
xact-commtCommit CountNumber of transactions committed
xact-rollbRollback CountNumber of transactions rolled back
xact-waitsWait for Xact CountNumber of times waited for another transaction
xact-dirtyDirty Xact CountNumber 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.
stat-readRead StatementsNumber of SELECT statements
stat-writeWrite StatementsNumber of UPDATE/INSERT/DELETE statements
rec-inRecord Bytes ReadBytes read from the record/row files
rec-outRecord Bytes WrittenBytes 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).
rec-syncs/msRecord File Flushes2 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.
rec-hitsRecord Cache HitsHits when accessing the record cache. The record cache caches the data handle (.xtd) and row index (.xtr) files.
rec-missRecord Cache MissesMisses when accessing the record cache
rec-freesRecord Cache FreesNumber of record cache pages freed
rec-%useRecord Cache UsagePercentage 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.
ind-inIndex Bytes ReadBytes read from the index files
ind-outIndex Bytes WrittenBytes 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.
ind-syncs/msIndex File Flushes2 values separated by a '/': the number of flushes to index files and the time taken for the flush operations in milliseconds.
ind-hitsIndex Cache HitsHits when accessing the index cache
ind-missIndex Cache MissesMisses when accessing the index cache
ind-%useIndex Cache UsagePercentage 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.
ilog-inIndex Log Bytes InBytes read from the index log files
ilog-outIndex Log Bytes OutBytes 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.
ilog-syncs/msIndex Log File Syncs2 values separated by a '/': the number of flushes to index log files and the time taken for the flush operations in milliseconds
xlog-inXact Log Bytes InBytes read from the transaction log files
xlog-outXact Log Bytes OutBytes 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.
xlog-syncsXact Log File SyncsNumber of flushes to transaction log files
xlog-msecXact Log Sync TimeThe time in milliseconds to flush transaction log files
xlog-hitsXact Log Cache HitsHits when accessing the transaction log cache
xlog-missXact Log Cache MissesMisses when accessing the transaction log cache
xlog-%useXact Log Cache UsagePercentage 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.
data-inData Log Bytes InBytes read from the data log files
data-outData Log Bytes OutBytes 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.
data-syncsData Log File SyncsNumber of flushes to data log files
data-msecData Log Sync TimeThe time in milliseconds spent flushing data log files
to-chkptBytes to CheckpointBytes written to the transaction log since the last checkpoint
to-writeLog Bytes to WriteBytes written to the transaction log, still to be written to the database
to-sweepLog Bytes to SweepBytes written to the transaction log, still to be read by the Sweeper thread
sweep-waitsSweeper Wait on Xact  Attempts to cleanup a transaction
scan-indexIndex Scan CountNumber of index scans
scan-tableTable Scan CountNumber of table scans
row-selSelect Row CountNumber of rows selected
row-insInsert Row CountNumber of rows inserted
row-updUpdate Row CountNumber of rows updated
row-delDelete Row CountNumber of rows deleted

More Information

Documentation on this page is based on the 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

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.