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 of this point:The query result of the SHOW FULL STATS statement varies based on the PolarDB-X 1.0 instance versions.
Example:
mysql> 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 |
+------+---------+----------+-------------------+------------------+------------------------+--------------------+--------+------------+--------------+---------------+----------------+
| 1.77 | 1.68 | 0.03 | 0.03 | 0.02 | 0.00 | 7 | 157.13 | 51.14 | 134.49 | 1.48 | 1 |
+------+---------+----------+-------------------+------------------+------------------------+--------------------+--------+------------+--------------+---------------+----------------+
1 row in set (0.01 sec)
mysql> 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 number of queries per second (QPS) sent from an applicationto a PolarDB-X 1.0 instance. The QPS is the logical QPS.
- RDS_QPS:the number of QPS sent from a PolarDB-X 1.0 instance to an ApsaraDB RDS for MySQL instance.The QPS is 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 DRDS instance.
- ACTIVE_CONNECTIONS: the number of active connections.
- CONNECTION_CREATE_PER_SECCOND: the number of connections that are created per second.
- RT(MS): the time between a sent SQL query and a response. The SQL query is sent from an application toa PolarDB-X 1.0 instance. The response time (RT) is the logical RT.
- RDS_RT(MS):the time to respond to an SQL query that is sent from a PolarDB-X 1.0 instance to an ApsaraDB RDS for MySQL instance. The RT is the physical RT.
- NET_IN(KB/S):the amount of inbound traffic of a PolarDB-X 1.0 instance per second.
- NET_OUT(KB/S):the amount of outbound traffic of a PolarDB-X 1.0 instance per second.
- THREAD_RUNNING: the number of threads that are running in a DRDS 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 since a DRDS instance is started.
- AGGREGATE_QUERY_PER_SECCOND: the number of aggregate queries per second.
- AGGREGATE_QUERY_COUNT: the total number of aggregate queries. This column shows the accumulative historical data.
- TEMP_TABLE_CREATE_PER_SECCOND: the number of temporary tables that are created per second.
- TEMP_TABLE_CREATE_COUNT: the total number of temporary tables that are created since a DRDS instance is started.
- MULTI_DB_JOIN_PER_SECCOND: the number of cross-database JOIN queries per second.
- MULTI_DB_JOIN_COUNT: the total number of cross-database JOIN queries since a DRDS 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. Therefore, the returned storage may be different from the actual storage.
Example:
mysql> 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 that represents aPolarDB-X 1.0 database.PolarDB-X 1.0 The value is different from the name of the PolarDB-X 1.0 database.
- CONNECTION_STRING: the information about a connection from a DRDS instance to a database shard.
- PHYSICAL_DB: the name of a database shard. The
TOTALrow showsthe total storage of all the database shards of a PolarDB-X 1.0 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 1.0 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 FULL DB STATUS [LIKE {tablename}]
You can execute this SQL statement to query the storage and performance information about a table in a physical database in real time. The storage information is obtained from an ApsaraDB RDS for MySQL system table. Therefore, the returned storage may be different from the actual storage.
Example:
mysql> show full db status like hash_tb;
+------+---------------------------+--------------------+-------------------+----------------+------------+--------+----------------+
| ID | NAME | CONNECTION_STRING | PHYSICAL_DB | PHYSICAL_TABLE | SIZE_IN_MB | RATIO | THREAD_RUNNING |
+------+---------------------------+--------------------+-------------------+----------------+------------+--------+----------------+
| 1 | drds_db_1516187088365daui | 100.100.64.1:59077 | TOTAL | | 19.875 | 100% | 3 |
| 2 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0000 | TOTAL | 3.03125 | 15.25% | |
| 3 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0000 | hash_tb_00 | 1.515625 | 7.63% | |
| 4 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0000 | hash_tb_01 | 1.515625 | 7.63% | |
| 5 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0001 | TOTAL | 2.0 | 10.06% | |
| 6 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0001 | hash_tb_02 | 1.515625 | 7.63% | |
| 7 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0001 | hash_tb_03 | 0.484375 | 2.44% | |
| 8 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0002 | TOTAL | 3.03125 | 15.25% | |
| 9 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0002 | hash_tb_04 | 1.515625 | 7.63% | |
| 10 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0002 | hash_tb_05 | 1.515625 | 7.63% | |
| 11 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0003 | TOTAL | 1.953125 | 9.83% | |
| 12 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0003 | hash_tb_06 | 1.515625 | 7.63% | |
| 13 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0003 | hash_tb_07 | 0.4375 | 2.2% | |
| 14 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0004 | TOTAL | 3.03125 | 15.25% | |
| 15 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0004 | hash_tb_08 | 1.515625 | 7.63% | |
| 16 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0004 | hash_tb_09 | 1.515625 | 7.63% | |
| 17 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0005 | TOTAL | 1.921875 | 9.67% | |
| 18 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0005 | hash_tb_11 | 1.515625 | 7.63% | |
| 19 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0005 | hash_tb_10 | 0.40625 | 2.04% | |
| 20 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0006 | TOTAL | 3.03125 | 15.25% | |
| 21 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0006 | hash_tb_12 | 1.515625 | 7.63% | |
| 22 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0006 | hash_tb_13 | 1.515625 | 7.63% | |
| 23 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0007 | TOTAL | 1.875 | 9.43% | |
| 24 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0007 | hash_tb_14 | 1.515625 | 7.63% | |
| 25 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0007 | hash_tb_15 | 0.359375 | 1.81% | |
+------+---------------------------+--------------------+-------------------+----------------+------------+--------+----------------+
Important columns:
- NAME: the internal tag that represents aPolarDB-X 1.0 database. PolarDB-X 1.0 The value is different from the name of the PolarDB-X 1.0 database.
- CONNECTION_STRING: the information about a connection from a DRDS instance to a database shard.
- PHYSICAL_DB: the name of a database shard. If you use the LIKE keyword in a statement, the
TOTALrow shows the storage of the database shard. If you do not use the LIKE keyword in a statement, the TOTAL row shows the total storage of all the database shards. - PHYSICAL_TABLE: the name of a table shard in a database shard. If you use the LIKE keyword in a
statement, the
TOTALrow shows the storage of the table shard. If you do not use the LIKE keyword in a statement, the TOTAL row shows the total storage of all the table shards. - SIZE_IN_MB: the used storage in a table shard. Unit: MB.
- RATIO: the ratio of the data volume of a table shard to the total data volume of all the returned table shards.
- THREAD_RUNNING: the number of threads that are running on a physical database. 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 [LIKE 'pattern' | WHERE expr]
You can execute this SQL statement to query information about a table. You can use this statement to aggregate the data of all underlying physical table shards.
Example:
mysql> show table status like 'multi_db_multi_tbl';
+--------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-----------------------+-------------+------------+-----------------+----------+----------------+---------+
| 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 |
+--------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-----------------------+-------------+------------+-----------------+----------+----------------+---------+
| multi_db_multi_tbl | InnoDB | 10 | Compact | 2 | 16384 | 16384 | 0 | 16384 | 0 | 100000 | 2017-03-27 17:43:57.0 | NULL | NULL | utf8_general_ci | NULL | | |
+--------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-----------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.03 sec)
Important columns:
- NAME: the name of a table.
- ENGINE: the storage engine for a table.
- VERSION: the version of a table storage engine.
- ROW_FORMAT: the format of the rows in a table. Sample values: Dynamic, Fixed, and Compressed. The Dynamic value specifies that the length of a row is variable, 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 a 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 a table.
- INDEX_LENGTH: the used disk storage by indexes.
- CREATE_TIME: the time when a table was created.
- UPDATE_TIME: the time when a table was last updated.
- COLLATION: the default character set and collation of a table.
- CREATE_OPTIONS: the other options specified when you created a table.
You can use the SCAN hint that is provided by PolarDB-X 1.0 in the SHOW TABLE STATUS statement. This way, you can query the data volume of each physical table shard. For more information, see Hints.
mysql> /!TDDL:SCAN='multi_db_multi_tbl'*/show table status like 'multi_db_multi_tbl%';
+----------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+--------------+
| 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 | Block_format |
+----------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+--------------+
| multi_db_multi_tbl_1 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2017-03-27 17:43:57 | NULL | NULL | utf8_general_ci | NULL | | | Original |
| multi_db_multi_tbl_0 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2017-03-27 17:43:57 | NULL | NULL | utf8_general_ci | NULL | | | Original |
| multi_db_multi_tbl_1 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2017-03-27 17:43:57 | NULL | NULL | utf8_general_ci | NULL | | | Original |
| multi_db_multi_tbl_0 | InnoDB | 10 | Compact | 1 | 16384 | 16384 | 0 | 16384 | 0 | 2 | 2017-03-27 17:43:57 | NULL | NULL | utf8_general_ci | NULL | | | Original |
| multi_db_multi_tbl_1 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2017-03-27 17:43:57 | NULL | NULL | utf8_general_ci | NULL | | | Original |
| multi_db_multi_tbl_0 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2017-03-27 17:43:57 | NULL | NULL | utf8_general_ci | NULL | | | Original |
| multi_db_multi_tbl_1 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2017-03-27 17:43:57 | NULL | NULL | utf8_general_ci | NULL | | | Original |
| multi_db_multi_tbl_0 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2017-03-27 17:43:57 | NULL | NULL | utf8_general_ci | NULL | | | Original |
| multi_db_multi_tbl_1 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2017-03-27 17:43:57 | NULL | NULL | utf8_general_ci | NULL | | | Original |
| multi_db_multi_tbl_0 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2017-03-27 17:43:57 | NULL | NULL | utf8_general_ci | NULL | | | Original |
| multi_db_multi_tbl_1 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2017-03-27 17:43:57 | NULL | NULL | utf8_general_ci | NULL | | | Original |
| multi_db_multi_tbl_0 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2017-03-27 17:43:57 | NULL | NULL | utf8_general_ci | NULL | | | Original |
| multi_db_multi_tbl_1 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2017-03-27 17:43:57 | NULL | NULL | utf8_general_ci | NULL | | | Original |
| multi_db_multi_tbl_0 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2017-03-27 17:43:57 | NULL | NULL | utf8_general_ci | NULL | | | Original |
| multi_db_multi_tbl_1 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2017-03-27 17:43:57 | NULL | NULL | utf8_general_ci | NULL | | | Original |
| multi_db_multi_tbl_0 | InnoDB | 10 | Compact | 1 | 16384 | 16384 | 0 | 16384 | 0 | 3 | 2017-03-27 17:43:57 | NULL | NULL | utf8_general_ci | NULL | | | Original |
+----------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+--------------+
16 rows in set (0.04 sec)