更新时间:2020-08-17 15:03
PolarDB-X提供以下语句用于查询实时统计信息。
查看整体的统计信息,这些信息都是瞬时值。注意不同版本的 PolarDB-X SHOW FULL STATS
的结果是有区别的。
重要列说明:
示例:
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)
用于查看物理库容量/性能信息,所有返回值为实时信息。 容量信息通过 MySQL 系统表获得,与真实容量情况可能有差异。
重要列说明:
TOTAL
行代表一个 PolarDB-X DB 中所有分库容量的总和;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% | |
+------+---------------------------+--------------------+-------------------+------------+--------+----------------+
用于查看物理库表容量和性能信息,所有返回值为实时信息。 容量信息通过 MySQL 系统表获得,与真实容量情况可能有差异。
重要列说明:
TOTAL
行代表经过 LIKE 关键字筛选后得到的分库容量的总和。如果没有 LIKE,则为全部分库容量的总和;TOTAL
行代表经过 LIKE 关键字筛选后得到的分表容量的总和。如果没有LIKE,则为全部分表容量的总和;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% | |
+------+---------------------------+--------------------+-------------------+----------------+------------+--------+----------------+
获取表的信息,该指令聚合了底层各个物理分表的数据。
重要列详解:
mysql> show table status like 'multi_db_multi_tbl';
+--------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-----------------------+-------------+------------+-----------------+----------+----------------+---------+
| 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 |
+--------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-----------------------+-------------+------------+-----------------+----------+----------------+---------+
| 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 | | |
+--------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-----------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.03 sec)
和 PolarDB-X 的 SCAN HINT 结合,还可以查看每个物理分表的数据量。详情请参见HINT 语法。
mysql> /!TDDL:SCAN='multi_db_multi_tbl'*/show table status like 'multi_db_multi_tbl';
+----------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+--------------+
| 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 |
+----------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+--------------+
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
+----------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+--------------+
16 rows in set (0.04 sec)
在文档使用中是否遇到以下问题
更多建议
匿名提交