Statistics queries
This topic describes how to execute the SHOW statements to query real-time statistics.
SHOW [FULL] STATS
You can execute this SQL statement to query the overall statistics. The statistics are instantaneous values. Take note that the returned results of SHOW FULL STATS vary based on the versions of PolarDB-X instances.
Examples:
SHOW STATS;
+------+---------+----------+-------------------+------------------+------------------------+--------------------+--------+------------+--------------+---------------+----------------+---------------+---------------+--------------+
| QPS | RDS_QPS | SLOW_QPS | PHYSICAL_SLOW_QPS | ERROR_PER_SECOND | MERGE_QUERY_PER_SECOND | ACTIVE_CONNECTIONS | RT(MS) | RDS_RT(MS) | NET_IN(KB/S) | NET_OUT(KB/S) | THREAD_RUNNING | DDL_JOB_COUNT | BACKFILL_ROWS | CHECKED_ROWS |
+------+---------+----------+-------------------+------------------+------------------------+--------------------+--------+------------+--------------+---------------+----------------+---------------+---------------+--------------+
| 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1 | 0.00 | 0.00 | 0.00 | 0.00 | 1 | 0 | 0 | 0 |
+------+---------+----------+-------------------+------------------+------------------------+--------------------+--------+------------+--------------+---------------+----------------+---------------+---------------+--------------+
show full stats;
+------+---------+----------+-------------------+------------------+----------------------+------------------------+--------------------+------------------------------+--------+------------+--------------+---------------+----------------+----------------------+-----------------+----------------------------+-----------------------+------------------------------+-------------------------+--------------------------+---------------------+-------+---------+-------------+------------+
| QPS | RDS_QPS | SLOW_QPS | PHYSICAL_SLOW_QPS | ERROR_PER_SECOND | VIOLATION_PER_SECOND | MERGE_QUERY_PER_SECOND | ACTIVE_CONNECTIONS | CONNECTION_CREATE_PER_SECOND | RT(MS) | RDS_RT(MS) | NET_IN(KB/S) | NET_OUT(KB/S) | THREAD_RUNNING | HINT_USED_PER_SECOND | HINT_USED_COUNT | AGGREGATE_QUERY_PER_SECOND | AGGREGATE_QUERY_COUNT | TEMP_TABLE_CREATE_PER_SECOND | TEMP_TABLE_CREATE_COUNT | MULTI_DB_JOIN_PER_SECOND | MULTI_DB_JOIN_COUNT | CPU | FREEMEM | FULLGCCOUNT | FULLGCTIME |
+------+---------+----------+-------------------+------------------+----------------------+------------------------+--------------------+------------------------------+--------+------------+--------------+---------------+----------------+----------------------+-----------------+----------------------------+-----------------------+------------------------------+-------------------------+--------------------------+---------------------+-------+---------+-------------+------------+
| 1.63 | 1.68 | 0.03 | 0.03 | 0.02 | 0.00 | 0.00 | 6 | 0.01 | 157.13 | 51.14 | 134.33 | 1.21 | 1 | 0.00 | 54 | 0.00 | 663 | 0.00 | 512 | 0.00 | 516 | 0.09% | 6.96% | 76446 | 21326906 |
+------+---------+----------+-------------------+------------------+----------------------+------------------------+--------------------+------------------------------+--------+------------+--------------+---------------+----------------+----------------------+-----------------+----------------------------+-----------------------+------------------------------+-------------------------+--------------------------+---------------------+-------+---------+-------------+------------+
1 row in set (0.01 sec)
Important columns:
QPS : the queries per second (QPS) sent from an application to a PolarDB-X instance. The QPS is known as the logical QPS.
RDS_QPS : the QPS sent from a PolarDB-X instance to an ApsaraDB RDS for MySQL instance. The QPS is known as the physical QPS.
ERROR_PER_SECOND : the total number of errors that occur per second. These errors include SQL syntax errors, primary key conflicts, system errors, and connectivity errors.
VIOLATION_PER_SECOND : the number of primary key conflicts or unique key conflicts per second.
MERGE_QUERY_PER_SECOND : the number of queries on tables per second. Sharding is enabled for the database instance.
ACTIVE_CONNECTIONS : the number of active connections.
CONNECTION_CREATE_PER_SECOND : the number of connections that are created per second.
RT(MS) : the response time (RT) for an SQL query that is sent from an application to a PolarDB-X instance. The RT is known as the logical RT.
RDS_RT(MS) : the RT for an SQL query that is sent from a PolarDB-X instance to an ApsaraDB RDS for MySQL instance. The RT is known as the physical RT.
NET_IN(KB/S) : the inbound traffic of a PolarDB-X instance per second.
NET_OUT(KB/S) : the outbound traffic of a PolarDB-X instance per second.
THREAD_RUNNING : the number of threads that are running in a database instance.
HINT_USED_PER_SECOND : the number of SQL queries that contain hints per second.
HINT_USED_COUNT : the total number of SQL queries that contain hints after the database instance is started.
AGGREGATE_QUERY_PER_SECOND : the number of aggregate queries per second.
AGGREGATE_QUERY_COUNT : the total number of aggregate queries. The column shows the historical data.
TEMP_TABLE_CREATE_PER_SECOND : the number of temporary tables that are created per second.
TEMP_TABLE_CREATE_COUNT : the total number of temporary tables that are created after the database instance is started.
MULTI_DB_JOIN_PER_SECOND : the number of cross-database JOIN queries per second.
MULTI_DB_JOIN_COUNT : the total number of cross-database JOIN queries after the database instance is started.
SHOW DB STATUS
You can execute this SQL statement to query the storage and performance information about a physical database in real time. The storage information is obtained from an ApsaraDB RDS for MySQL system table. The returned storage information may be different from the actual storage information.
Example:
SHOW DB STATUS;
+------+---------------------------+--------------------+-------------------+------------+--------+----------------+
| ID | NAME | CONNECTION_STRING | PHYSICAL_DB | SIZE_IN_MB | RATIO | THREAD_RUNNING |
+------+---------------------------+--------------------+-------------------+------------+--------+----------------+
| 1 | drds_db_1516187088365daui | 100.100.64.1:59077 | TOTAL | 13.109375 | 100% | 3 |
| 2 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0000 | 1.578125 | 12.04% | |
| 3 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0001 | 1.4375 | 10.97% | |
| 4 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0002 | 1.4375 | 10.97% | |
| 5 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0003 | 1.4375 | 10.97% | |
| 6 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0004 | 1.734375 | 13.23% | |
| 7 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0005 | 1.734375 | 13.23% | |
| 8 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0006 | 2.015625 | 15.38% | |
| 9 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0007 | 1.734375 | 13.23% | |
+------+---------------------------+--------------------+-------------------+------------+--------+----------------+
Important columns:
NAME : the internal tag of a PolarDB-X database. Each tag identifies a PolarDB-X database. The tag is different from the name of the PolarDB-X database.
CONNECTION_STRING : the information about a connection from an instance to a database shard.
PHYSICAL_DB : the name of a database shard. The
TOTALrow indicates the total storage of all database shards in a PolarDB-X database.SIZE_IN_MB : the used storage in a database shard. Unit: MB.
RATIO : the ratio of the data volume of a database shard to the total data volume of the PolarDB-X database.
THREAD_RUNNING : the number of threads that are running on a physical database instance. The value of the THREAD_RUNNING parameter is the same as that of the Threads_running parameter returned by the
SHOW GLOBAL STATUSstatement in MySQL. For more information, see MySQL official documentation.
SHOW TABLE STATUS
You can execute this SQL statement to query the information about a table. This statement aggregates the data of all physical table shards.
Example:
SHOW TABLE STATUS;
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| NAME | ENGINE | VERSION | ROW_FORMAT | ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | COLLATION | CHECKSUM | CREATE_OPTIONS | COMMENT |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| sbtest1 | InnoDB | 10 | Dynamic | 0 | 0 | 1310720 | 0 | 0 | 0 | 0 | 2021-07-20 15:39:37 | NULL | NULL | utf8mb4_general_ci | NULL | | |
| t1 | InnoDB | 10 | Dynamic | 0 | 0 | 2621440 | 0 | 2621440 | 0 | 200000 | 2021-07-26 20:11:15 | NULL | NULL | utf8mb4_general_ci | NULL | | |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
Important columns:
NAME : the name of the table.
ENGINE : the storage engine for the table.
VERSION : the version of the table storage engine.
ROW_FORMAT : the format of the rows in the table. Sample values: Dynamic, Fixed, and Compressed. The Dynamic value specifies that the length of a row varies, for example, a row of the VARCHAR or BLOB type. The Fixed value specifies that the length of a row is constant, for example, a row of the CHAR or INTEGER type.
ROWS : the number of rows in the table.
AVG_ROW_LENGTH : the average number of bytes in each row.
DATA_LENGTH : the data volume of a full table. Unit: byte.
MAX_DATA_LENGTH : the maximum volume of data that can be stored in the table.
INDEX_LENGTH : the disk storage that is consumed by indexes.
CREATE_TIME : the time when the table was created.
UPDATE_TIME : the time when the table was last updated.
COLLATION : the default character set and collation of the table.
CREATE_OPTIONS : the other options specified when you created the table.
SHOW TABLE INFO [name]
You can execute this statement to obtain the data volume of each table shard.
Example:
SHOW TABLE INFO SBTEST1;
+----+--------------+-----------------+------------+
| ID | GROUP_NAME | TABLE_NAME | SIZE_IN_MB |
+----+--------------+-----------------+------------+
| 0 | test1_000000 | sbtest1_wo5k_00 | 0.01562500 |
| 1 | test1_000000 | sbtest1_wo5k_01 | 0.01562500 |
| 2 | test1_000005 | sbtest1_wo5k_10 | 0.01562500 |
| 3 | test1_000005 | sbtest1_wo5k_11 | 0.01562500 |
| 4 | test1_000010 | sbtest1_wo5k_20 | 0.01562500 |
| 5 | test1_000010 | sbtest1_wo5k_21 | 0.01562500 |
| 6 | test1_000015 | sbtest1_wo5k_30 | 0.01562500 |
| 7 | test1_000015 | sbtest1_wo5k_31 | 0.01562500 |
| 8 | test1_000020 | sbtest1_wo5k_40 | 0.01562500 |
| 9 | test1_000020 | sbtest1_wo5k_41 | 0.01562500 |
| 10 | test1_000025 | sbtest1_wo5k_50 | 0.01562500 |
| 11 | test1_000025 | sbtest1_wo5k_51 | 0.01562500 |
| 12 | test1_000030 | sbtest1_wo5k_60 | 0.01562500 |
| 13 | test1_000030 | sbtest1_wo5k_61 | 0.01562500 |
| 14 | test1_000035 | sbtest1_wo5k_70 | 0.01562500 |
| 15 | test1_000035 | sbtest1_wo5k_71 | 0.01562500 |
+----+--------------+-----------------+------------+
Important columns:
ID : the ID.
GROUP_NAME : the name of the database shard.
TABLE_NAME : the name of the physical table shard.
SIZE_IN_MB : the size of the table.