全部产品
存储与CDN 数据库 安全 应用服务 数加·人工智能 数加·大数据基础服务 互联网中间件 视频服务 开发者工具 解决方案 物联网 钉钉智能硬件
分布式关系型数据库 DRDS

统计信息查询类语句

更新时间:2018-01-17 21:17:04

统计信息查询类语句

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

SHOW [FULL] STATS

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

重要列说明:

  • QPS:应用到 DRDS 的 QPS,通常称为逻辑 QPS;
  • RDS_QPS:DRDS 到 RDS 的 QPS,通常称为物理 QPS;
  • ERROR_PER_SECOND:每秒的错误数,包含 SQL 语法错误,主键冲突,系统错误,连通性错误等各类错误总和;
  • VIOLATION_PER_SECOND:每秒的主键或者唯一键冲突;
  • MERGE_QUERY_PER_SECCOND:通过分库分表,从多表中进行的查询;
  • ACTIVE_CONNECTIONS:正在使用的连接;
  • CONNECTION_CREATE_PER_SECCOND:每秒创建的连接数;
  • RT(MS):应用到 DRDS 的响应时间,通常称为逻辑 RT(响应时间);
  • RDS_RT(MS):DRDS 到 RDS/MySQL 的响应时间,通常称为物理 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 DB 中所有分库容量的总和;
  • SIZE_IN_MB: 分库中数据占用的空间,单位为 MB;
  • RATIO: 单个分库数据量在当前 DRDS DB 总数据量中的占比;
  • 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. | 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}]

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

重要列说明:

  • NAME: 代表一个 DRDS DB。此处显示的是 DRDS 内部标记,与 DRDS DB 名称不同;
  • CONNECTION_STRING: 分库的连接信息;
  • PHYSICAL_DB:分库名称,TOTAL 行代表经过 LIKE 关键字筛选后得到的分库容量的总和。如果没有 LIKE,则为全部分库容量的总和;
  • PHYSICAL_TABLE:分表名称,TOTAL 行代表经过 LIKE 关键字筛选后得到的分表容量的总和。如果没有LIKE,则为全部分表容量的总和;
  • SIZE_IN_MB: 分表中数据占用的空间,单位为 MB;
  • RATIO: 单个分表数据量在当前筛选出的分表总数据量中的占比;
  • THREAD_RUNNING: 物理数据库实例当前正在执行的线程情况,含义与 MySQL SHOW GLOBAL STATUS 指令返回值的含义相同。详情请参考 MySQL 文档

示例:

  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. +------+---------------------------+--------------------+-------------------+----------------+------------+--------+----------------+
本文导读目录