统计信息查询类语句
统计信息查询类语句
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 的总量。
示例:
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)
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 文档。
示例:
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% | |
+------+---------------------------+--------------------+-------------------+------------+--------+----------------+
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 文档。
示例:
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% | |
+------+---------------------------+--------------------+-------------------+----------------+------------+--------+----------------+
版权声明
本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。
评论