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 DRDS 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 DRDS instance. The QPS is the logical QPS.
  • RDS_QPS:the number of QPS sent from a DRDS 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 DRDS 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 DRDS 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 DRDS instance per second.
  • NET_OUT(KB/S):the amount of outbound traffic of a DRDS 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 aDRDS database.DRDS The value is different from the name of the DRDS 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 TOTAL row showsthe total storage of all the database shards of a DRDS 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 DRDS 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 STATUS statement 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 aDRDS database. DRDS The value is different from the name of the DRDS 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 TOTAL row 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 TOTAL row 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 STATUS statement 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 DRDS 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)