You are viewing an older version of this section. View current production version.
Join the MemSQL Community Today
Get expert advice, develop skills, and connect with others.

SHOW DATABASE STATUS

Shows MemSQL database status.

Syntax

[USING db_name] SHOW DATABASE STATUS

Remarks

In case of cluster replication, the viewpoints of the primary and secondary clusters may differ. In this case, the primary cluster is unaware of the secondary cluster, whereas the secondary cluster is aware of the primary cluster. Hence, the command may return different results when run.

The following table provides information on the metrics displayed in the output:

Key Description
database Name of the database in the MemSQL instance
role Database replication role
state Current state of the database (See Database States for more information)
commit_count Number of committed transactions. (This field is deprecated)
provision_file Position (of the snapshot) from which the database has been provisioned
oldest_living_snapshot Position of the oldest available snapshot (for the database) on the disk
newest_snapshot Position of the most recent snapshot (for the database) on the disk
log_type Durability type of the log, sequential or paged
commit_lsn Position before which all pages are committed
hardened_lsn Position before which all pages have been written
replay_lsn Position before which all pages have been replayed. Applies only to secondary databases
tail_lsn Highest position of all the pages
replicated_committed_lsn (primary) Position before which all commits are replicated to the secondary databases
term The term of the database, used to uniquely identify which node was the master of a specific database
replay_fileid Used in combination with replay_offset to find the current replay position (This field is deprecated)
sync_offset This field is deprecated
state_machine_stage Provides the current stage of the replication state machine
state_machine_error Provides the error associated with the replication state machine
state_machine_throttling Provides the current state of the replication state machine
replay_stuck_on_low_memory Specifies if the replay is stalling because of low memory
replay_offset (secondary) Used in combination with replay_fileid to find the current replay position. (This field is deprecated)
network_fileid (secondary) Used in combination with network_offset to find the position in log file that has been replicated. (This field is deprecated)
network_offset (secondary) Used in combination with network_fileid to find the position before which the log file has been replicated. (This field is deprecated)
is_connected_to_master (secondary) Specifies if the secondary partition is connected to the primary partition
replication_type (secondary) Specifies the replication type (async or sync). This metric is local to the secondary database, and can be different from the distributed database’s replication state
master_host (secondary) The host component (of the primary database) in the host:port/database_name URI format
master_port (secondary) The port component (of the primary database) in the host:port/database_name URI format
master_user (secondary) Name of the user
master_database (secondary) The name of the primary database
auto_reprovision (secondary) This field is deprecated
partitions The number of partitions. This value is non-zero for reference databases and zero for partitions and other databases

Note: LSN stands for Log Sequence Number, and represents the position in the log file.

Examples

The following example shows the output when the command is run on the primary cluster.

USE memsql_demo;

SHOW DATABASE STATUS;
****
+--------------------------+--------+
| Key                      | Value  |
+--------------------------+--------+
| database                 | x_db   |
| role                     | master |
| state                    | online |
| commit_count             | 0      |
| provision_file           | 0      |
| oldest_living_snapshot   | 0      |
| newest_snapshot          | 0      |
| log_type                 | paged  |
| commit_lsn               | 0:2    |
| hardened_lsn             | 0:2    |
| replay_lsn               | 0:0    |
| tail_lsn                 | 0:2    |
| replicated_committed_lsn | 0:1    |
| term                     | 2      |
| workload_throttled       | no     |
| partitions               | 6      |
+--------------------------+--------+

The following example displays the output from the secondary cluster’s viewpoint.

USE memsql_demo;

SHOW DATABASE STATUS;
****
+----------------------------+-------------+
| Key                        | Value       |
+----------------------------+-------------+
| database                   | x_db        |
| role                       | slave       |
| state                      | replicating |
| commit_count               | 0           |
| provision_file             | 0           |
| oldest_living_snapshot     | 0           |
| newest_snapshot            | 0           |
| log_type                   | paged       |
| commit_lsn                 | 0:1         |
| hardened_lsn               | 0:2         |
| replay_lsn                 | 0:1         |
| tail_lsn                   | 0:2         |
| replicated_committed_lsn   | 0:0         |
| term                       | 2           |
| is_connected_to_master     | yes         |
| replication_type           | async       |
| master_host                | 127.0.0.1   |
| master_port                | 10000       |
| master_user                | distributed |
| master_database            | x_db        |
| state_machine_stage        | packet wait |
| state_machine_state        | x_streaming |
| state_machine_error        | no          |
| state_machine_throttling   | no          |
| replay_stuck_on_low_memory | no          |
| partitions                 | 6           |
+----------------------------+-------------+