All Products
Search
Document Center

Statistics queries

Last Updated: Aug 17, 2020

Distributed Relational Database Service (DRDS) provides the following statements for you to query real-time statistics:

SHOW [FULL] STATS

You can execute this SQL statement to view the overall statistics of a DRDS instance. The statistics are instantaneous values.
Note that the results of SHOW FULL STATS vary with the DRDS version.

Important columns:

  • QPS: the number of queries per second (QPS) sent from an application to the DRDS instance. These queries are logical QPS.
  • RDS_QPS: the number of QPS sent from the DRDS instance to an ApsaraDB RDS for MySQL instance. These queries are physical QPS.
  • ERROR_PER_SECOND: the total number of errors that occur on a DRDS instance per second. These errors include various errors such as SQL syntax errors, primary key conflicts, system errors, and connectivity errors.
  • VIOLATION_PER_SECOND: the number of conflicts that occur on primary keys or unique keys per second.
  • MERGE_QUERY_PER_SECCOND: the number of queries processed on multiple tables by sharding per second.
  • ACTIVE_CONNECTIONS: the number of active connections to the DRDS instance.
  • CONNECTION_CREATE_PER_SECCOND: the number of connections that are created for the DRDS instance per second.
  • RT(MS): the time to respond to an SQL query sent from an application to the DRDS instance. This response time (RT) is logical RT.
  • RDS_RT(MS): the time to respond to an SQL query sent from the DRDS instance to an ApsaraDB RDS for MySQL instance. This RT is usually called physical RT.
  • NET_IN(KB/S): the amount of inbound traffic of the DRDS instance per second.
  • NET_OUT(KB/S): the amount of outbound traffic of the DRDS instance per second.
  • THREAD_RUNNING: the number of threads that are running in the DRDS instance.
  • HINT_USED_PER_SECOND: the number of hint-contained SQL queries that are processed by the DRDS instance per second.
  • HINT_USED_COUNT: the total number of SQL queries that contain hints and have been processed by the DRDS instance since startup.
  • AGGREGATE_QUERY_PER_SECCOND: the number of aggregate SQL queries that are processed by the DRDS instance per second.
  • AGGREGATE_QUERY_COUNT: the total number of aggregate SQL queries that have been processed by the DRDS instance.
  • TEMP_TABLE_CREATE_PER_SECCOND: the number of temporary tables created in the DRDS instance per second.
  • TEMP_TABLE_CREATE_COUNT: the total number of temporary tables that have been created in the DRDS instance since startup.
  • MULTI_DB_JOIN_PER_SECCOND: the number of multi-database JOIN queries processed by the DRDS instance per second.
  • MULTI_DB_JOIN_COUNT: the number of multi-database JOIN queries that have been processed by the DRDS instance since startup.

Example:

  1. mysql> show stats;
  2. +------+---------+----------+-------------------+------------------+------------------------+--------------------+--------+------------+--------------+---------------+----------------+
  3. | 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 |
  4. +------+---------+----------+-------------------+------------------+------------------------+--------------------+--------+------------+--------------+---------------+----------------+
  5. | 1.77 | 1.68 | 0.03 | 0.03 | 0.02 | 0.00 | 7 | 157.13 | 51.14 | 134.49 | 1.48 | 1 |
  6. +------+---------+----------+-------------------+------------------+------------------------+--------------------+--------+------------+--------------+---------------+----------------+
  7. 1 row in set (0.01 sec)
  8. mysql> show full stats;
  9. +------+---------+----------+-------------------+------------------+----------------------+------------------------+--------------------+------------------------------+--------+------------+--------------+---------------+----------------+----------------------+-----------------+----------------------------+-----------------------+------------------------------+-------------------------+--------------------------+---------------------+-------+---------+-------------+------------+
  10. | 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 |
  11. +------+---------+----------+-------------------+------------------+----------------------+------------------------+--------------------+------------------------------+--------+------------+--------------+---------------+----------------+----------------------+-----------------+----------------------------+-----------------------+------------------------------+-------------------------+--------------------------+---------------------+-------+---------+-------------+------------+
  12. | 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 |
  13. +------+---------+----------+-------------------+------------------+----------------------+------------------------+--------------------+------------------------------+--------+------------+--------------+---------------+----------------+----------------------+-----------------+----------------------------+-----------------------+------------------------------+-------------------------+--------------------------+---------------------+-------+---------+-------------+------------+
  14. 1 row in set (0.01 sec)

SHOW DB STATUS

You can execute this SQL statement to view the capacity and performance information of a physical database. All the returned values indicate the real-time information. The capacity information is obtained from the ApsaraDB RDS for MySQL system table, and therefore may be different from the actual capacity information.

Important columns:

  • NAME: the internal tag that represents a logical database corresponding to the database shard. The value is different from the name of the logical database.
  • CONNECTION_STRING: the information about a connection from the DRDS instance to the database shard.
  • PHYSICAL_DB: the name of the database shard. The TOTAL row indicates the total amount of capacity of all the database shards corresponding to the logical database.
  • SIZE_IN_MB: the size of the space occupied by the data in the database shard. Unit: MB.
  • RATIO: the ratio of the data volume of the database shard to the total data volume of the current logical database.
  • THREAD_RUNNING: the number of threads that are running on the physical database. The value has the same meaning as that of SHOW GLOBAL STATUS. For more information, see the MySQL documentation.

Example:

  1. mysql> show db status;
  2. +------+---------------------------+--------------------+-------------------+------------+--------+----------------+
  3. | ID | NAME | CONNECTION_STRING | PHYSICAL_DB | SIZE_IN_MB | RATIO | THREAD_RUNNING |
  4. +------+---------------------------+--------------------+-------------------+------------+--------+----------------+
  5. | 1 | drds_db_1516187088365daui | 100.100.64.1:59077 | TOTAL | 13.109375 | 100% | 3 |
  6. | 2 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0000 | 1.578125 | 12.04% | |
  7. | 3 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0001 | 1.4375 | 10.97% | |
  8. | 4 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0002 | 1.4375 | 10.97% | |
  9. | 5 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0003 | 1.4375 | 10.97% | |
  10. | 6 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0004 | 1.734375 | 13.23% | |
  11. | 7 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0005 | 1.734375 | 13.23% | |
  12. | 8 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0006 | 2.015625 | 15.38% | |
  13. | 9 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0007 | 1.734375 | 13.23% | |
  14. +------+---------------------------+--------------------+-------------------+------------+--------+----------------+

SHOW FULL DB STATUS [LIKE {tablename}]

You can execute this SQL statement to view the capacity and performance information of a table shard in a physical database. All the returned values indicate real-time information. The capacity information is obtained from the ApsaraDB RDS for MySQL system table, and therefore may be different from the actual capacity information.

Important columns:

  • NAME: the internal tag that represents a logical database corresponding to the database shard. The value is different from the name of the logical database.
  • CONNECTION_STRING: the information about a connection from the DRDS instance to the database shard.
  • PHYSICAL_DB : the name of the database shard. If the LIKE keyword is used for filtering in the statement, the TOTAL row indicates the total amount of capacity of the database shard. If the LIKE keyword is not used, this row shows the total capacity of all the database shards.
  • PHYSICAL_TABLE : the name of the table shard in the database shard. If the LIKE keyword is used for filtering in the statement, the TOTAL row indicates the total amount of capacity of the table shard. If the LIKE keyword is not used, this row shows the total capacity of all the table shards.
  • SIZE_IN_MB: the size of the space occupied by the data in the table shard. Unit: MB.
  • RATIO: the ratio of the data volume of the table shard to the total data volume of all the table shards obtained by filtering.
  • THREAD_RUNNING: the number of threads that are running on the physical database. The value has the same meaning as that of SHOW GLOBAL STATUS. For more information, see the MySQL documentation.

Example:

  1. mysql> show full db status like hash_tb;
  2. +------+---------------------------+--------------------+-------------------+----------------+------------+--------+----------------+
  3. | ID | NAME | CONNECTION_STRING | PHYSICAL_DB | PHYSICAL_TABLE | SIZE_IN_MB | RATIO | THREAD_RUNNING |
  4. +------+---------------------------+--------------------+-------------------+----------------+------------+--------+----------------+
  5. | 1 | drds_db_1516187088365daui | 100.100.64.1:59077 | TOTAL | | 19.875 | 100% | 3 |
  6. | 2 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0000 | TOTAL | 3.03125 | 15.25% | |
  7. | 3 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0000 | hash_tb_00 | 1.515625 | 7.63% | |
  8. | 4 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0000 | hash_tb_01 | 1.515625 | 7.63% | |
  9. | 5 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0001 | TOTAL | 2.0 | 10.06% | |
  10. | 6 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0001 | hash_tb_02 | 1.515625 | 7.63% | |
  11. | 7 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0001 | hash_tb_03 | 0.484375 | 2.44% | |
  12. | 8 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0002 | TOTAL | 3.03125 | 15.25% | |
  13. | 9 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0002 | hash_tb_04 | 1.515625 | 7.63% | |
  14. | 10 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0002 | hash_tb_05 | 1.515625 | 7.63% | |
  15. | 11 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0003 | TOTAL | 1.953125 | 9.83% | |
  16. | 12 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0003 | hash_tb_06 | 1.515625 | 7.63% | |
  17. | 13 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0003 | hash_tb_07 | 0.4375 | 2.2% | |
  18. | 14 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0004 | TOTAL | 3.03125 | 15.25% | |
  19. | 15 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0004 | hash_tb_08 | 1.515625 | 7.63% | |
  20. | 16 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0004 | hash_tb_09 | 1.515625 | 7.63% | |
  21. | 17 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0005 | TOTAL | 1.921875 | 9.67% | |
  22. | 18 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0005 | hash_tb_11 | 1.515625 | 7.63% | |
  23. | 19 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0005 | hash_tb_10 | 0.40625 | 2.04% | |
  24. | 20 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0006 | TOTAL | 3.03125 | 15.25% | |
  25. | 21 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0006 | hash_tb_12 | 1.515625 | 7.63% | |
  26. | 22 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0006 | hash_tb_13 | 1.515625 | 7.63% | |
  27. | 23 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0007 | TOTAL | 1.875 | 9.43% | |
  28. | 24 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0007 | hash_tb_14 | 1.515625 | 7.63% | |
  29. | 25 | drds_db_1516187088365daui | 100.100.64.1:59077 | drds_db_xzip_0007 | hash_tb_15 | 0.359375 | 1.81% | |
  30. +------+---------------------------+--------------------+-------------------+----------------+------------+--------+----------------+

SHOW TABLE STATUS [LIKE ‘pattern’ | WHERE expr]

You can execute this SQL statement to obtain the information about a table. This command aggregates the data of all underlying table shards.

Important columns:

  • NAME: the name of the table.
  • ENGINE: the storage engine of the table.
  • VERSION: the version of the storage engine of the table.
  • ROW_FORMAT: the format of the rows in the table. Valid values include Dynamic, Fixed, and Compressed. The value Dynamic indicates that the row length is variable, for example, a VARCHAR or BLOB field. The value Fixed indicates that the row length is constant, for example, a CHAR or INTEGER field.
  • 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 the entire table. Unit: bytes.
  • MAX_DATA_LENGTH: the maximum volume of data that can be stored in the table.
  • INDEX_LENGTH: the size of the disk space occupied 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 character sorting rule of the table.
  • CREATE_OPTIONS: all the other options specified when the table was created.
  1. mysql> show table status like 'multi_db_multi_tbl';
  2. +--------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-----------------------+-------------+------------+-----------------+----------+----------------+---------+
  3. | 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 |
  4. +--------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-----------------------+-------------+------------+-----------------+----------+----------------+---------+
  5. | 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 | | |
  6. +--------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-----------------------+-------------+------------+-----------------+----------+----------------+---------+
  7. 1 row in set (0.03 sec)

This statement can be used with SCAN HINT of DRDS to display the data volume of each physical table shard. For more information, see Hint syntax.

  1. mysql> /!TDDL:SCAN='multi_db_multi_tbl'*/show table status like 'multi_db_multi_tbl';
  2. +----------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+--------------+
  3. | 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 |
  4. +----------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+--------------+
  5. | 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 |
  6. | 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 |
  7. | 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 |
  8. | 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 |
  9. | 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 |
  10. | 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 |
  11. | 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 |
  12. | 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 |
  13. | 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 |
  14. | 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 |
  15. | 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 |
  16. | 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 |
  17. | 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 |
  18. | 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 |
  19. | 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 |
  20. | 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 |
  21. +----------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+--------------+
  22. 16 rows in set (0.04 sec)