All Products
Search
Document Center

Statistic query statements

Last Updated: Sep 01, 2020

This topic lists the statements to query real-time statistics that supported by Distributed Relational Database Service (DRDS).

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 DRDS SHOW FULL STATS vary with the version of the DRDS instance.

The following describes the meanings of important columns:

  • QPS: the number of queries per second (QPS) sent from an application to the DRDS instance. These queries are usually called logical QPS.
  • RDS_QPS: the number of QPS sent from the DRDS instance to the ApsaraDB RDS for MySQL instance. These queries are usually called physical QPS.
  • ERROR_PER_SECOND: the total number of errors that occur on the 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 through database sharding and table 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 usually called 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 SQL queries that contain hints and 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 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.

Examples

  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, which is also called a database shard. 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.

The following describes the meanings of 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 information about the threads that are currently running in the ApsaraDB RDS for MySQL instance to which the physical database belongs. The meaning of this parameter is the same as that of the SHOW GLOBAL STATUS command in MySQL. For more information, see the MySQL documentation.

Examples

  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, which is also called a database shard. 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.

The following describes the meanings of important columns:

  • NAME: indicates a DRDS database. It shows the internal mark of the database, which is different from the database name.
  • 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 for filtering in the statement, the TOTAL row indicates the total amount of capacity of all database shards.
  • PHYSICAL_TABLE: the name of the table 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 for filtering in the statement, the TOTAL row indicates the total amount of capacity of all table shards in the database shard.
  • 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 through filtering.
  • THREAD_RUNNING: the information about the threads that are currently running in the ApsaraDB RDS for MySQL instance to which the physical database belongs. The meaning of this parameter is the same as that of the SHOW GLOBAL STATUS command in MySQL. For more information, see the MySQL documentation.

Examples

  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. +------+---------------------------+--------------------+-------------------+----------------+------------+--------+----------------+