全部产品
分布式关系型数据库 DRDS

统计信息查询类语句

更新时间:2017-08-07 13:42:56   分享:   

统计信息查询类语句

DRDS 提供以下语句用于查询实时统计信息。

SHOW [FULL] STATS

查看整体的统计信息,这些信息都是瞬时值。注意不同版本的 DRDS SHOW FULL STATS的结果是有区别的。

重要列说明:

  • QPS:逻辑 QPS;
  • RDS_QPS:物理 QPS;
  • ERROR_PER_SECOND:每秒的错误数,包含语法错误,主键冲突等等所有异常;
  • VIOLATION_PER_SECOND:每秒的主键或者唯一键冲突;
  • MERGE_QUERY_PER_SECCOND:通过分库分表,从多表中进行的查询;
  • ACTIVE_CONNECTIONS:正在使用的连接;
  • CONNECTION_CREATE_PER_SECCOND:每秒创建的连接数;
  • RT(MS):逻辑 RT(响应时间);
  • RDS_RT(MS):物理 RT;
  • NET_IN(KB/S):DRDS 收到的网络流量;
  • NET_OUT(KB/S):DRDS 输出的网络流量;
  • THREAD_RUNNING:正在运行的线程数;
  • HINT_USED_PER_SECOND:每秒带 HINT 的查询的数量;
  • HINT_USED_COUNT:启动到现在带 HINT 的查询总量;
  • AGGREGATE_QUERY_PER_SECCOND:每秒聚合查询的频次;
  • AGGREGATE_QUERY_COUNT:聚合查询总数(历史累计数据);
  • TEMP_TABLE_CREATE_PER_SECCOND:每秒创建的临时表的数量;
  • TEMP_TABLE_CREATE_COUNT:启动到现在创建的临时表总数量;
  • MULTI_DB_JOIN_PER_SECCOND:每秒跨库 JOIN 的数量;
  • MULTI_DB_JOIN_COUNT:启动到现在跨库 JOIN 的总量。

示例:

  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

用于查看物理库容量/性能信息,所有返回值为实时信息。 容量信息通过 MySQL 系统表获得,与真实容量情况可能有差异。

重要列说明:

  • NAME: 代表一个 DRDS DB,此处显示的是 DRDS 内部标记,与 DRDS DB 名称不同;
  • CONNECTION_STRING: 分库的连接信息;
  • PHYSICAL_DB: 分库名称,TOTAL 行代表一个数据库实例下所有 DRDS 分库容量的总和;
  • SIZE_IN_MB: 分库中数据占用的空间,单位为 MB;
  • RATIO: 单个分库数据量在实例上所有分库总数据量中的占比;
  • THREAD_RUNNING: 物理数据库实例当前正在执行的线程情况,各个参数含义与 MySQL SHOW GLOBAL STATUS 指令返回值的含义相同,详情请参考 MySQL 文档

示例:

  1. mysql> show db status;
  2. +------+---------------------------------------+--------------------------------------------------+-------------------------------+------------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  3. | ID | NAME | CONNECTION_STRING | PHYSICAL_DB | SIZE_IN_MB | RATIO | THREAD_RUNNING |
  4. +------+---------------------------------------+--------------------------------------------------+-------------------------------+------------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | 0 | show_db_status_test_1500019889503ljqa | rm-bp1j51trg3xq2w597.mysql.rds.aliyuncs.com:3306 | TOTAL | 41.875 | 100% | {"Threadpool_idle_threads":"0","Threadpool_threads":"0","Threads_cached":"78","Threads_connected":"24","Threads_created":"262","Threads_rejected":"0","Threads_running":"11"} |
  6. | 1 | show_db_status_test_1500019889503ljqa | rm-bp1j51trg3xq2w597.mysql.rds.aliyuncs.com:3306 | show_db_status_test_ilpx_0000 | 26.1875 | 62.54% | |
  7. | 2 | show_db_status_test_1500019889503ljqa | rm-bp1j51trg3xq2w597.mysql.rds.aliyuncs.com:3306 | show_db_status_test_ilpx_0001 | 5.5625 | 13.28% | |
  8. | 3 | show_db_status_test_1500019889503ljqa | rm-bp1j51trg3xq2w597.mysql.rds.aliyuncs.com:3306 | show_db_status_test_ilpx_0002 | 5.5625 | 13.28% | |
  9. | 4 | show_db_status_test_1500019889503ljqa | rm-bp1j51trg3xq2w597.mysql.rds.aliyuncs.com:3306 | show_db_status_test_ilpx_0003 | 4.5625 | 10.9% | |
  10. | 5 | show_db_status_test_1500019889503ljqa | rm-bp1urq0n46mn5k7vt.mysql.rds.aliyuncs.com:3306 | TOTAL | 16.25 | 100% | {"Threadpool_idle_threads":"0","Threadpool_threads":"0","Threads_cached":"11","Threads_connected":"110","Threads_created":"2616","Threads_rejected":"0","Threads_running":"7"} |
  11. | 6 | show_db_status_test_1500019889503ljqa | rm-bp1urq0n46mn5k7vt.mysql.rds.aliyuncs.com:3306 | show_db_status_test_ilpx_0004 | 4.5625 | 28.08% | |
  12. | 7 | show_db_status_test_1500019889503ljqa | rm-bp1urq0n46mn5k7vt.mysql.rds.aliyuncs.com:3306 | show_db_status_test_ilpx_0005 | 3.5625 | 21.92% | |
  13. | 8 | show_db_status_test_1500019889503ljqa | rm-bp1urq0n46mn5k7vt.mysql.rds.aliyuncs.com:3306 | show_db_status_test_ilpx_0006 | 4.5625 | 28.08% | |
  14. | 9 | show_db_status_test_1500019889503ljqa | rm-bp1urq0n46mn5k7vt.mysql.rds.aliyuncs.com:3306 | show_db_status_test_ilpx_0007 | 3.5625 | 21.92% | |
  15. +------+---------------------------------------+--------------------------------------------------+-------------------------------+------------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  16. 10 rows in set (0.05 sec)
本文导读目录
本文导读目录
以上内容是否对您有帮助?