All pages
Powered by GitBook
1 of 34

Legacy Clients and Utilities

Explore legacy clients and utilities. This section provides information on older tools and their use cases, useful for understanding historical contexts or working with specific deployments.

Analyzing Tools

EXPLAIN Analyzer

The EXPLAIN Analyzer is no longer active.

The EXPLAIN Analyzer was an online tool for analyzing and optionally sharing the output of both EXPLAIN and EXPLAIN EXTENDED.

Using the Analyzer

Using the analyzer is very simple.

In the mariadb client, run EXPLAIN on a query and copy the output. For example:

EXPLAIN SELECT * FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.a=t2.a AND t2.a=t3.a;
+------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                                  |
+------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+
|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    3 |                                                        |
|    1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where; Using join buffer (flat, BNL join)        |
|    1 | SIMPLE      | t3    | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where; Using join buffer (incremental, BNL join) |
+------+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------+
3 rows in set (0.00 sec)
  1. Paste the output into the EXPLAIN Analyzer input box and click the "Analyze Explain" button.

  2. The formatted EXPLAIN will be shown. You can now click on various part to get more information about them.

Some Notes:

  • As you can see in the example above, you don't need to chop off the query line or the command prompt.

  • To save the EXPLAIN, so you can share it, or just for future reference, click the "Save Explain for analysis and sharing" button and then click the "Analyze Explain" button. You will be given a link which leads to your saved EXPLAIN. For example, the above explain can be viewed here:

  • Some of the elements in the formatted EXPLAIN are clickable. Clicking on them will show pop-up help related to that element.

Clients which integrate with the Explain Analyzer

The Analyzer has an API that client programs can use to send EXPLAINs. If you are a client application developer, see the EXPLAIN Analyzer API page for details.

The following clients have support for the EXPLAIN Analyzer built in:

HeidiSQL

HeidiSQL has a button when viewing a query that sends the query to the explain analyzer.

CC BY-SA / Gnu FDL

EXPLAIN Analyzer API

EXPLAIN Analyzer is no longer active.

The online EXPLAIN Analyzer tool has an open API to allow client applications to send it EXPLAINs.

Sending EXPLAINs to the EXPLAIN Analyzer

To send an EXPLAIN to the EXPLAIN Analyzer, simply POST or GET to the following address:

mariadb.org/explain_analyzer/api/1/?raw_explain=EXPLAIN&client=CLIENT

Replace "EXPLAIN" with the output of the EXPLAIN command and "CLIENT" with the name of your client.

Client Banner

If you like, you can have a banner promoting your client appear at the bottom of the page. Once you've added support for the EXPLAIN Analyzer to your client application, just send a logo, the name of your client, and what you want the name and logo to link to to bryan AT montyprogram DOT com.

CC BY-SA / Gnu FDL

mysqld_safe

From MariaDB 10.5, the client is called mariadbd-safe. It can still be accessed under its original mysqld_safe name via a symlink in Linux, or an alternate binary in Windows.

See mariadbd-safe for details.

This page is licensed: GPLv2

MySQL Sandbox

MySQL Sandbox has been deprecated. See dbdeployer instead.

MySQL Sandbox is a Perl Module for installing multiple versions of MariaDB and/or MySQL in isolation from each other. It is primarily used for easily testing different server versions.

Visitfor details on how to install and use it.

This page is licensed: CC BY-SA / Gnu FDL

mysql_convert_table_format

mysql-convert-table-format converts the tables in a database to use a particular storage engine (MyISAM by default).

From MariaDB 10.5, the client is called mariadb-convert-table-format. It can still be accessed under its original mysql-convert-table-format name via a symlink in Linux, or an alternate binary in Windows.

See mariadb-convert-table-format for details.

This page is licensed: CC BY-SA / Gnu FDL

mysql_embedded

From MariaDB 10.5, the client is called mariadb-embedded. It can still be accessed under its original mysql_embedded name via a symlink in Linux, or an alternate binary in Windows.

See mariadb-embedded for details.

This page is licensed: CC BY-SA / Gnu FDL

mysql_find_rows

mysql_find_rows reads files containing SQL statements and extracts statements that match a given regular expression or that contain USE db_name or SET statements.

From MariaDB 10.5, the client is called mariadb-find-rows. It can still be accessed under its original mysql_find_rows name via a symlink in Linux, or an alternate binary in Windows.

See mariadb-find-rows for details.

This page is licensed: CC BY-SA / Gnu FDL

mysql_fix_extensions

mysql_fix_extensions converts the extensions for MyISAM (or ISAM) table files to their canonical forms.

From MariaDB 10.5, the client is called mariadb-fix-extensions. It can still be accessed under its original mysql_fix_extensions name via a symlink in Linux, or an alternate binary in Windows.

See mariadb-fix-extensions for details.

This page is licensed: CC BY-SA / Gnu FDL

mysql_install_db

mysql_install_db initializes the MariaDB data directory and creates the system tables in the mysql database.

From MariaDB 10.5, the client is called mariadb-install-db. It can still be accessed under its original mysql_install_db name via a symlink in Linux, or an alternate binary in Windows.

See mariadb-install-db for details.

This page is licensed: CC BY-SA / Gnu FDL

mysql_plugin

mysql_plugin is a tool for enabling or disabling plugins.

From MariaDB 10.5, the client is called mariadb-plugin. It can still be accessed under its original mysql_plugin name via a symlink in Linux, or an alternate binary in Windows.

See mariadb-plugin for details.

This page is licensed: CC BY-SA / Gnu FDL

mysql_secure_installation

Note that many of the reasons for the existence of this script no longer apply. In particular, from MariaDB 10.4, Unix socket authentication is applied by default, and there is usually no need to create a root password. See Authentication from MariaDB 10.4.

From MariaDB 10.5, the client is called mariadb-secure-installation. It can still be accessed under its original mysql_secure_installation name via a symlink in Linux, or an alternate binary in Windows.

See mariadb-secure-installation for details.

This page is licensed: CC BY-SA / Gnu FDL

mysql_setpermission

mariadb-setpermission is a Perl script that was originally written and contributed by Luuk de Boer to help add users or databases or change passwords in MariaDB.

From MariaDB 10.5, the client is called mariadb-setpermission. It can still be accessed under its original mysql_setpermission name via a symlink in Linux, or an alternate binary in Windows.

See mariadb-setpermission for details.

This page is licensed: CC BY-SA / Gnu FDL

mysql_tzinfo_to_sql

mysql_tzinfo_to_sql is a utility used to load time zones.

From MariaDB 10.5, the client is called mariadb-tzinfo-to-sql. It can still be accessed under its original mysql_tzinfo_to_sql name via a symlink in Linux, or an alternate binary in Windows.

See mariadb-tzinfo-to-sql for details.

This page is licensed: CC BY-SA / Gnu FDL

mysql_upgrade

mysql_upgrade is a tool that checks and updates your tables to the latest version.

From MariaDB 10.5, the client is called mariadb-upgrade. It can still be accessed under its original mysql_upgrade name via a symlink in Linux, or an alternate binary in Windows.

See mariadb-upgrade for details.

This page is licensed: CC BY-SA / Gnu FDL

mysql_waitpid

mysql_waitpid is a utility for terminating processes. It runs on Unix-like systems, making use of the kill() system call.

From MariaDB 10.5, the client is called mariadb-waitpid. It can still be accessed under its original mysql_waitpid name via a symlink.

See mariadb-waitpid for details.

This page is licensed: CC BY-SA / Gnu FDL

mysql_zap

mysql_zap was removed from MariaDB. pkill can be used as an alternative.

mysql_zap kills processes that match a pattern. It uses the ps command and Unix signals, so it runs on Unix and Unix-like systems.

Invoke mysql_zap like this:

shell> mysql_zap [-signal] [-?Ift]

A process matches if its output line from the ps command contains the pattern. By default, mysql_zap asks for confirmation for each process. Respond y to kill the process, or q to exit mysql_zap. For any other response, mysql_zap does not attempt to kill the process.

If the -signal option is given, it specifies the name or number of the signal to send to each process. Otherwise, mysql_zap tries first with TERM (signal 15) and then with KILL (signal 9).

mysql_zap supports the following additional options:

Option
Description

--help, -?, -I

Display a help message and exit.

-f

Force mode. mysql_zap attempts to kill each process without confirmation.

-t

Test mode. Display information about each process but do not kill it.

Example

localhost:~# mysql_zap -t mysql
stty: standard input: unable to perform all requested operations
USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
root      4073  0.0  0.2   3804  1308 ?        S    08:51   0:00 /bin/bash /usr/bin/mysqld_safe
mysql     4258  3.3 15.7 939740 81236 ?        Sl   08:51  30:18 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306

pkill as an Alternative

pkill can be used as an alternative to mysql_zap, although an important distinction between pkill and mysql_zap is that mysql_zap kills the server 'gently' first (with signal 15) and only if the server doesn't die in a limited time then tries -9.

To use pkill in the same way, one must run it twice; pkill --signal 15 mysqld ; sleep(10) ; pkill -f --signal 9 pattern

This page is licensed: CC BY-SA / Gnu FDL

mysqlaccess

mysqlaccess is a tool for checking access privileges, developed by Yves Carlier.

From MariaDB 10.5, the client is called mariadb-access. It can still be accessed under its original mysqlaccess name via a symlink in Linux, or an alternate binary in Windows.

See mariadb-access for details.

This page is licensed: CC BY-SA / Gnu FDL

mysqladmin

mysqladmin is an administration program for the mysqld daemon.

From MariaDB 10.5, the client is called mariadb-admin. It can still be accessed under its original mysqladmin name via a symlink in Linux, or an alternate binary in Windows.

See mariadb-admin for details.

This page is licensed: CC BY-SA / Gnu FDL

mysqlcheck

mysqlcheck is a tool for checking, repairing, analyzing and optimizing tables.

From MariaDB 10.5, the client is called mariadb-check. It can still be accessed under its original mysqlcheck name via a symlink in Linux, or an alternate binary in Windows.

See mariadb-check for details.

This page is licensed: CC BY-SA / Gnu FDL

mysqld_multi

From MariaDB 10.5, the client is called mariadbd-multi. It can still be accessed under its original mysqld_multi name via a symlink in Linux, or an alternate binary in Windows.

See mariadbd-multi for details.

This page is licensed: CC BY-SA / Gnu FDL

mysqldump

mysqldump is used to dump a database or a collection of databases for backup or transfer to another database server.

From MariaDB 10.5, the client is called mariadb-dump. It can still be accessed under its original mysqldump name via a symlink in Linux, or an alternate binary in Windows.

MariaDB starting with 11.0.1

From MariaDB 11.0.1, mysqldump (the symlink) is deprecated and removed from the mariadb Docker Official Image. Use mariadb-dump instead.

See mariadb-dump for details.

This page is licensed: GPLv2

mysqldumpslow

mysqldumpslow is a tool to examine the slow query log.

From MariaDB 10.5, the client is called mariadb-dumpslow. It can still be accessed under its original mysqldumpslow name via a symlink in Linux, or an alternate binary in Windows.

See mariadb-dumpslow for details.

This page is licensed: CC BY-SA / Gnu FDL

mysqlhotcopy

mysqlhotcopy is deprecated and may be removed in a future release.

mysqlhotcopy uses FLUSH TABLES, LOCK TABLES, and cp or scp to make a database backup.

From MariaDB 10.5, the client is called mariadb-hotcopy. It can still be accessed under its original mysqlhotcopy name via a symlink in Linux, or an alternate binary in Windows.

See mariadb-hotcopy for details.

This page is licensed: GPLv2

mysqlimport

mysqlimport is used to load tables from text files in various formats

From MariaDB 10.5, the client is called mariadb-import. It can still be accessed under its original mysqlimport name via a symlink in Linux, or an alternate binary in Windows.

See mariadb-import for details.

This page is licensed: CC BY-SA / Gnu FDL

mysqlreport

mysqlreport makes a friendly report of important MariaDB status values.

From MariaDB 10.5, the client is called mariadb-report. It can still be accessed under its original mysqlreport name via a symlink in Linux, or an alternate binary in Windows.

See mariadb-report for details.

This page is licensed: CC BY-SA / Gnu FDL

mysqlshow

Shows the structure of a MariaDB database (databases, tables, columns and indexes).

From MariaDB 10.5, the client is called mariadb-show. It can still be accessed under its original mysqlshow name via a symlink in Linux, or an alternate binary in Windows.

See mariadb-show for details.

This page is licensed: CC BY-SA / Gnu FDL

mysqlslap

mysqlslap is a tool for load-testing MariaDB. It allows you to emulate multiple concurrent connections, and run a set of queries multiple times.

From MariaDB 10.5, the client is called mariadb-slap. It can still be accessed under its original mysqlslap name via a symlink in Linux, or an alternate binary in Windows.

See mariadb-slap for details.

This page is licensed: CC BY-SA / Gnu FDL

Percona XtraBackup

Open source tool for performing hot backups of MariaDB, MySQL and Percona Server databases

Percona XtraBackup is not supported in MariaDB. mariadb-backup is the recommended backup method to use instead of Percona XtraBackup. See Percona XtraBackup Overview: Compatibility with MariaDB for more information.

Percona XtraBackup Overview

Percona XtraBackup is not supported in MariaDB. mariadb-backup is the recommended backup method to use instead of Percona XtraBackup. See Percona XtraBackup Overview: Compatibility with MariaDB for more information.

Percona XtraBackup is an open source tool for performing hot backups of MariaDB, MySQL and Percona Server databases. Percona XtraBackup can perform compressed, incremental and streaming backups. It was designed to back up XtraDB/InnoDB tables but can also back up other storage engines.

mariadb-backup is a fork of Percona XtraBackup designed to work with encrypted and compressed tables and other MariaDB enhancements. There are many bug fixes, such as MDEV-13807, and some unsafe or redundant options have been removed. mariadb-backup is the recommended backup method for MariaDB servers.

Installing Percona XtraBackup

Installing with a Package Manager

Percona XtraBackup can also be installed via a package manager on Linux. In order to do so, your system needs to be configured to install from a repository that has it.

You can also configure your package manager to install it from Percona's repository by following the instructions in their documentation:

  • Installing Percona XtraBackup 2.3

  • Installing Percona XtraBackup 2.4

Installing with yum/dnf

On RHEL, CentOS, Fedora, and other similar Linux distributions, it is highly recommended to install the relevant RPM package from MariaDB's repository using yum or dnf. Starting with RHEL 8 and Fedora 22, yum has been replaced by dnf, which is the next major version of yum. However, yum commands still work on many systems that use dnf. For example, to install Percona XtraBackup 2.3:

sudo yum install percona-xtrabackup

And to install Percona XtraBackup 2.4:

sudo yum install percona-xtrabackup-24

Installing with apt-get

On Debian, Ubuntu, and other similar Linux distributions, it is highly recommended to install the relevant DEB package from MariaDB's repository using apt-get. For example, to install Percona XtraBackup 2.3:

sudo apt-get install percona-xtrabackup

And to install Percona XtraBackup 2.4:

sudo apt-get install percona-xtrabackup-24

Installing with zypper

On SLES, OpenSUSE, and other similar Linux distributions, it is highly recommended to install the relevant RPM package from MariaDB's repository using zypper. For example, to install Percona XtraBackup 2.3:

sudo zypper install percona-xtrabackup

And to install Percona XtraBackup 2.4:

sudo zypper install percona-xtrabackup-24

Using Percona XtraBackup

The command to use xtrabackup and the general syntax is:

xtrabackup <options>

or:

innobackupex <options>

Options

Options supported by Percona XtraBackup can be found on Percona's documentation.

xtrabackup options:

  • xtrabackup options - Percona XtraBackup 2.3

  • xtrabackup options - Percona XtraBackup 2.4

innobackupex options:

  • innobackupex options - Percona XtraBackup 2.3

  • innobackupex options - Percona XtraBackup 2.4

Option Files

In addition to reading options from the command-line, Percona XtraBackup can also read options from option files.

The following options relate to how MariaDB/MySQL command-line tools handles option files. They must be given as the first argument on the command-line:

Option
Description

--print-defaults

Print the program argument list and exit.

--no-defaults

Don't read default options from any option file.

--defaults-file=#

Only read default options from the given file #.

--defaults-extra-file=#

Read this file after the global files are read.

Server Option Groups

Percona XtraBackup reads server options from the following option groups from option files:

Group
Description

[xtrabackup]

Options read by mariadb-backup and Percona XtraBackup.

[mysqld]

Options read by mysqld, which includes both MariaDB Server and MySQL Server.

Client Option Groups

Percona XtraBackup reads client options from the following option groups from option files:

Group
Description

[xtrabackup]

Options read by mariadb-backup and Percona XtraBackup.

[client]

Options read by all MariaDB and MySQL client programs, which includes both MariaDB and MySQL clients. For example, mysqldump.

Authentication and Privileges

Percona XtraBackup needs to authenticate with the database server when it performs a backup operation (i.e. when the --backup option is specified). The user account that performs the backup needs to have the RELOAD , PROCESS, LOCK TABLES and REPLICATION CLIENT global privileges on the database server. For example:

CREATE USER 'xtrabackup'@'localhost' IDENTIFIED BY 'mypassword';
GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'xtrabackup'@'localhost';

The user account information can be specified with the -user and --password command-line options. For example:

$ xtrabackup --backup \
   --target-dir=/var/mariadb/backup/ \
   --user=xtrabackup --password=mypassword

The user account information can also be specified in a supported client option group in an option file. For example:

[xtrabackup]
user=xtrabackup
password=mypassword

Percona XtraBackup does not need to authenticate with the database server when preparing or restoring a backup.

File System Permissions

Percona XtraBackup has to read MariaDB's files from the file system. Therefore, when you run Percona XtraBackup as a specific operating system user, you should ensure that user account has sufficient permissions to read those files.

If you are using Linux and if you installed MariaDB with a package manager, then MariaDB's files will probably be owned by the mysql user and the mysql group.

Compatibility with MariaDB

Compatibility with MariaDB 10.3 and Later

In MariaDB 10.3 and later, mariadb-backup is the recommended backup method to use instead of Percona XtraBackup.

In MariaDB 10.3 and later, Percona XtraBackup is not supported.

This limitation is being tracked by Percona XtraBackup bug PXB-1550. However, it does not appear that there are plans to fix it.

Compatibility with MariaDB 10.2

In MariaDB 10.2, mariadb-backup is the recommended backup method to use instead of Percona XtraBackup.

In MariaDB 10.2, Percona XtraBackup 2.4 is supported in some cases if InnoDB page compression is not used, and if data at rest encryption is not used, and if innodb_page_size is set to 16k.

However, users should be aware that problems are likely due to the MySQL 5.7 undo log format incompatibility bug that was fixed in MariaDB 10.2.2 in MDEV-12289. Due to this bug, backups prepared with Percona XtraBackup 2.4 may fail to recover some transactions. Only if you ran the server with the setting innodb_undo_logs=1 this would not be a problem. Percona XtraBackup 2.4 may also fail to work entirely with MariaDB 10.2.19 and later if innodb_safe_truncate=ON is set due to changes in the redo log format introduced by MDEV-14717. In that case, you may see the following error:

InnoDB: Unsupported redo log format. The redo log was created with MariaDB 10.2.19. Please follow the instructions at http://dev.mysql.com/doc/refman/5.7/en/upgrading-downgrading.html

Compatibility with MariaDB 10.1

In MariaDB 10.1, mariadb-backup is the recommended backup method to use instead of Percona XtraBackup.

In MariaDB 10.1, Percona XtraBackup 2.3 is supported if InnoDB page compression is not used, and if data at rest encryption is not used, and if innodb_page_size is set to 16k.

Compatibility with MariaDB 10.0 and Before

In MariaDB 10.0 and before, Percona XtraBackup 2.3 is supported.

Using Percona XtraBackup for Galera SSTs

The xtrabackup-v2 SST method uses the Percona XtraBackup utility for performing SSTs. See xtrabackup-v2 SST method for more information.

See Also

  • mariadb-backup

  • mysqldump

  • Percona XtraBackup documentation

  • Percona JIRA

This page is licensed: CC BY-SA / Gnu FDL

Percona XtraBackup Build Instructions

Percona XtraBackup is not supported in MariaDB. mariadb-backup is the recommended backup method to use instead of Percona XtraBackup. See Percona XtraBackup Overview: Compatibility with MariaDB for more information.

Build instructions for Percona XtraBackup.

Solaris 10 (SunOS 5.10) notes:

Edit utils/build.sh and add -lrt -m64 to CFLAGS and CXXFLAGS.

Make sure that you're using GNU utils for building, including make, cmake, gcc, gawk, getopt, autotools, libtool, automake, autoconf and bazaar.

If you want to change MySQL version which to build against with edit one of the following lines:

MYSQL_51_VERSION=...

MYSQL_55_VERSION=...

PS_51_VERSION=...

PS_55_VERSION=..

When ready run one of the following depending on the MySQL version which you want to build with:

AUTO_DOWNLOAD="yes" ./utils/build.sh xtradb (build against XtraDB 5.1)

AUTO_DOWNLOAD="yes" ./utils/build.sh innodb51_builtin (build against built-in InnoDB in MySQL 5.1)

AUTO_DOWNLOAD="yes" ./utils/build.sh xtradb55 (build against XtraDB 5.5)

AUTO_DOWNLOAD="yes" ./utils/build.sh innodb55 (build against InnoDB in MySQL 5.5)

This page is licensed: CC BY-SA / Gnu FDL

msql2mysql

Description

Initially, the MySQL C API was developed to be very similar to that of the mSQL database system.

Because of this, mSQL programs often can be converted relatively easily for use with MySQL by changing the names of their C API functions.

The msql2mysql utility performs the conversion of mSQL C API function calls to their MySQL equivalents.

Warning: msql2mysql converts the input file in place, so make a copy of the original before converting it.

Example

shell> cp client-prog.c client-prog.c.orig
shell> msql2mysql client-prog.c
client-prog.c converted

After conversion, examine client-prog.c and make any necessary post-conversion revisions.

msql2mysql uses the replace utility to make the function name substitutions.

This page is licensed: CC BY-SA / Gnu FDL

xtstat

This tool is for PBXT, an unsupported storage engine.

xtstat can be used to monitor all internal activity of PBXT (a storage engine that is no longer supported).

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

-?, --help

Prints help text.

-h, --host=value

Connect to host.

-u, --user=value

User 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=value

Database to be used (pbxt or information_schema required), default is information_schema

-P, --port=value

Port number to use for connection.

-S, --socket=value

Socket file to use for connection.

-D, --delay=value

Delay in seconds between polls of the database.

--protocol=value

Connection protocol to use: default/tcp/socket/pipe/memory

--display=value

Columns to display: use short names separated by

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 name
Name
Description

Display name

Name

Description

time-curr

Current Time

The current time in seconds

time-msec

Time Since Last Call

Time passed in milliseconds since last statistics call

xact-commt

Commit Count

Number of transactions committed

xact-rollb

Rollback Count

Number of transactions rolled back

xact-waits

Wait for Xact Count

Number of times waited for another transaction

xact-dirty

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.

stat-read

Read Statements

Number of SELECT statements

stat-write

Write Statements

Number of UPDATE/INSERT/DELETE statements

rec-in

Record Bytes Read

Bytes read from the record/row files

rec-out

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).

rec-syncs/ms

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.

rec-hits

Record Cache Hits

Hits when accessing the record cache. The record cache caches the data handle (.xtd) and row index (.xtr) files.

rec-miss

Record Cache Misses

Misses when accessing the record cache

rec-frees

Record Cache Frees

Number of record cache pages freed

rec-%use

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.

ind-in

Index Bytes Read

Bytes read from the index files

ind-out

Index Bytes 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.

ind-syncs/ms

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.

ind-hits

Index Cache Hits

Hits when accessing the index cache

ind-miss

Index Cache Misses

Misses when accessing the index cache

ind-%use

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.

ilog-in

Index Log Bytes In

Bytes read from the index log files

ilog-out

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.

ilog-syncs/ms

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

xlog-in

Xact Log Bytes In

Bytes read from the transaction log files

xlog-out

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.

xlog-syncs

Xact Log File Syncs

Number of flushes to transaction log files

xlog-msec

Xact Log Sync Time

The time in milliseconds to flush transaction log files

xlog-hits

Xact Log Cache Hits

Hits when accessing the transaction log cache

xlog-miss

Xact Log Cache Misses

Misses when accessing the transaction log cache

xlog-%use

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.

data-in

Data Log Bytes In

Bytes read from the data log files

data-out

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.

data-syncs

Data Log File Syncs

Number of flushes to data log files

data-msec

Data Log Sync Time

The time in milliseconds spent flushing data log files

to-chkpt

Bytes to Checkpoint

Bytes written to the transaction log since the last checkpoint

to-write

Log Bytes to Write

Bytes written to the transaction log, still to be written to the database

to-sweep

Log Bytes to Sweep

Bytes written to the transaction log, still to be read by the Sweeper thread

sweep-waits

Sweeper Wait on Xact

Attempts to cleanup a transaction

scan-index

Index Scan Count

Number of index scans

scan-table

Table Scan Count

Number of table scans

row-sel

Select Row Count

Number of rows selected

row-ins

Insert Row Count

Number of rows inserted

row-upd

Update Row Count

Number of rows updated

row-del

Delete Row Count

Number 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: pbxt-uc-2010.pdf

This page is licensed: CC BY-SA / Gnu FDL