2.1.3 show命令
2.1.3.1 show @@time.current
show @@time.current;
描述：展示系统当前时间
结果：略
2.1.3.2 show @@time.startup
show @@time.startup;
描述：展示系统启动时间
结果：略
2.1.3.3 show @@version
show @@version；
描述：展示dble版本
结果：略
2.1.3.4 show @@server
show @@server；
描述：dble的当前信息
例：
mysql> show @@server;
+------------+-------------+--------------+------------+---------------------+---------------+---------+--------+
| UPTIME | USED_MEMORY | TOTAL_MEMORY | MAX_MEMORY | RELOAD_TIME | ROLLBACK_TIME | CHARSET | STATUS |
+------------+-------------+--------------+------------+---------------------+---------------+---------+--------+
| 1h 4m 47s | 17414592 | 87031808 | 1840250880 | 2017/10/17 16:42:09 | -1 | utf8 | ON |
+------------+-------------+--------------+------------+---------------------+---------------+---------+--------+
1 row in set (0.05 sec)
列描述：
UPTIME: 服务已经启动时间
USED_MEMORY: 已使用堆内存
TOTAL_MEMORY: 总共的堆内存
MAX_MEMORY: 最大可用堆内存
RELOAD_TIME: 上次config加载时间
ROLLBACK_TIME: 上次config的rollback时间
CHARSET: 字符集
STATUS: 在线状态
2.1.3.5 show @@threadpool
show @@threadpool；
描述：展示当前线程池信息
例：
mysql> show @@threadpool;
+----------------------+-----------+--------------+-----------------+----------------+------------+
| NAME | POOL_SIZE | ACTIVE_COUNT | TASK_QUEUE_SIZE | COMPLETED_TASK | TOTAL_TASK |
+----------------------+-----------+--------------+-----------------+----------------+------------+
| Timer | 1 | 0 | 0 | 22596 | 22596 |
| BusinessExecutor | 8 | 1 | 0 | 216 | 217 |
| complexQueryExecutor | 0 | 0 | 0 | 0 | 0 |
+----------------------+-----------+--------------+-----------------+----------------+------------+
3 rows in set (0.03 sec)
列描述：
NAME: 线程池名称
POOL_SIZE: 线程池大小
ACTIVE_COUNT: 活动数量
TASK_QUEUE_SIZE: 队列中的数量
COMPLETED_TASK: 已完成的任务数量
TOTAL_TASK: 总共任务数量
2.1.3.6 show @@database
show @@database;
描述：展示配置的schema名字
结果：略
2.1.3.7 show @@datanode
show @@datanode;
描述：展示配置的所有datanode信息
例：
mysql> show @@datanode;
+------+----------------+-------+--------+------+------+---------+---------------+
| NAME | DATHOST | INDEX | ACTIVE | IDLE | SIZE | EXECUTE | RECOVERY_TIME |
+------+----------------+-------+--------+------+------+---------+---------------+
| dn1 | dh1/dble_test | 0 | 0 | 0 | 1000 | 34 | -1 |
| dn2 | dh2/dble_test | 0 | 0 | 0 | 1000 | 34 | -1 |
| dn3 | dh1/dble2_test | 0 | 0 | 0 | 1000 | 26 | -1 |
| dn4 | dh2/dble2_test | 0 | 0 | 0 | 1000 | 26 | -1 |
| dn5 | dh1/nosharding | 0 | 0 | 0 | 1000 | 9 | -1 |
+------+----------------+-------+--------+------+------+---------+---------------+
5 rows in set (0.09 sec)
列描述：
NAME: 名称
DATHOST: hostName/实际schema
INDEX: 写节点的index
ACTIVE: 当前活动的后端连接数量
IDLE: 当前空闲的后端连接数量(空闲容量维护疑似bug)
SIZE: maxCon容量
EXECUTE: 有过活动的后端连接数量统计
RECOVERY_TIME: 恢复心跳还需要秒数(stop @@heartbeat 中设置)
如果要查看某个schema相关的datanode信息，执行：
show @@datanode where schema=xxx；
其中，xxx为要查看的schema的名字。
2.1.3.8 show @@datasource
show @@datasource；
描述：展示配置的所有datasource信息
例：
mysql> show @@datasource;
+--------+--------------+------+------+--------+------+------+---------+-----------+------------+
| NAME | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+--------+--------------+------+------+--------+------+------+---------+-----------+------------+
| hostM1 | 10.18x.2x.63 | 3320 | W | 0 | 0 | 1000 | 69 | 0 | 0 |
| hostM2 | 10.18x.2x.64 | 3320 | W | 0 | 0 | 1000 | 60 | 0 | 0 |
+--------+--------------+------+------+--------+------+------+---------+-----------+------------+
2 rows in set (0.05 sec)
列描述
NAME: dataSource名称
HOST: host名
PORT: 端口
W/R: 读写结点标识
ACTIVE: 当前活动的后端连接数量,按照dataSource统计
IDLE: 当前空闲的后端连接数量,按照dataSource统计(空闲容量维护疑似bug)
SIZE: maxCon容量
EXECUTE: 有过活动的后端连接数量统计,按照dataSource统计
READ_LOAD: 读负载 (请求次数)
WRITE_LOAD: 写负载 (请求次数)
如果要查看某个datanode对应的datasource信息，执行：
show @@datasource where dataNode=xxx；
其中，xxx为要查看的datanode的名字。
2.1.3.9 show @@datasource.synstatus
show @@datasource.synstatus；
描述：展示当前各datasource的同步信息
前提条件: switch_type 配置了2并且heartbeat 配置了 show slave status（参见1.2 schemal.xml）
例：
mysql> show @@datasource.synstatus;
+--------+--------------+------+--------------+-------------+-------------+-----------------------+------------------+-------------------+----------------+---------------+---------------+
| NAME | HOST | PORT | MASTER_HOST | MASTER_PORT | MASTER_USER | SECONDS_BEHIND_MASTER | SLAVE_IO_RUNNING | SLAVE_SQL_RUNNING | SLAVE_IO_STATE | CONNECT_RETRY | LAST_IO_ERROR |
+--------+--------------+------+--------------+-------------+-------------+-----------------------+------------------+-------------------+----------------+---------------+---------------+
| hostM1 | 10.18x.2x.63 | 3320 | 10.18x.2x.61 | 3320 | qrep | NULL | No | No | | 60 | |
| hostM2 | 10.18x.2x.64 | 3320 | 10.18x.2x.62 | 3320 | qrep | NULL | No | No | | 60 | |
+--------+--------------+------+--------------+-------------+-------------+-----------------------+------------------+-------------------+----------------+---------------+---------------+
2 row in set (0.00 sec)
列描述：
NAME: datahost名称
HOST: 主机名/ip
PORT: 端口
其余列含义参见mysql中show slave status的命令。
2.1.3.10 show @@datasource.syndetail where name=？
show @@datasource.syndetail where name=xxx；
其中，xxx为要查看的datasource的名字。
描述：展示24小时内各datasource的历次同步信息
例：
mysql> show @@datasource.syndetail WHERE name =hostM2;
+--------+--------------+------+--------------+-------------+-------------+---------------------+-----------------------+
| NAME | HOST | PORT | MASTER_HOST | MASTER_PORT | MASTER_USER | TIME | SECONDS_BEHIND_MASTER |
+--------+--------------+------+--------------+-------------+-------------+---------------------+-----------------------+
| hostM2 | 10.18x.2x.64 | 3320 | 10.18x.2x.62 | 3320 | qrep | 2017-10-17 18:31:27 | -1 |
| hostM2 | 10.18x.2x.64 | 3320 | 10.18x.2x.62 | 3320 | qrep | 2017-10-17 18:31:57 | -1 |
| hostM2 | 10.18x.2x.64 | 3320 | 10.18x.2x.62 | 3320 | qrep | 2017-10-17 18:32:27 | -1 |
| hostM2 | 10.18x.2x.64 | 3320 | 10.18x.2x.62 | 3320 | qrep | 2017-10-17 18:32:57 | -1 |
+--------+--------------+------+--------------+-------------+-------------+---------------------+-----------------------+
4 row in set (0.05 sec)
列描述:
NAME: datahost名称
HOST: 主机名/ip
PORT: 端口
其余列含义参见mysql中show slave status的命令。
2.1.3.11 show @@datasource.cluster
show @@datasource.cluster；
描述：展示当前cluser信息
前提条件:switch_type 配置了3 并且show status like 'wsrep%'（参见1.2 schemal.xml）
TODO:待补充
2.1.3.12 show @@processor
show @@processor；
描述：展示dble实例的processor信息
例：
mysql> show @@processor ;
+------------+--------+---------+-------------+---------+---------+--------------+--------------+------------+----------+----------+----------+
| NAME | NET_IN | NET_OUT | REACT_COUNT | R_QUEUE | W_QUEUE | FREE_BUFFER | TOTAL_BUFFER | BU_PERCENT | BU_WARNS | FC_COUNT | BC_COUNT |
+------------+--------+---------+-------------+---------+---------+--------------+--------------+------------+----------+----------+----------+
| Processor0 | 6834 | 2749 | 0 | 0 | 0 | 687194767360 | 687194767360 | 0 | 0 | 0 | 0 |
| Processor1 | 7221 | 2862 | 0 | 0 | 0 | 687194767360 | 687194767360 | 0 | 0 | 0 | 0 |
| Processor2 | 6830 | 31141 | 0 | 0 | 0 | 687194767360 | 687194767360 | 0 | 0 | 1 | 0 |
| Processor3 | 6375 | 2681 | 0 | 0 | 0 | 687194767360 | 687194767360 | 0 | 0 | 0 | 0 |
+------------+--------+---------+-------------+---------+---------+--------------+--------------+------------+----------+----------+----------+
4 rows in set (0.06 sec)
列描述：
NAME: 名称
NET_IN: 接收流量
NET_OUT: 发送流量
REACT_COUNT: 固定值0
R_QUEUE: 固定值0
W_QUEUE: 写队列大小
FREE_BUFFER: BufferPool free大小
TOTAL_BUFFER: BufferPool 总大小
BU_PERCENT: BufferPool使用率百分比
BU_WARNS: 固定值0
FC_COUNT: 前端连接数量
BC_COUNT: 后端连接数量
2.1.3.13 show @@command
show @@command；
描述：processor对各个类型的数据包的分类统计信息
例：
mysql> show @@command;
+------------+---------+-------+--------------+--------------+------------+------+------+------+-------+
| PROCESSOR | INIT_DB | QUERY | STMT_PREPARE | STMT_EXECUTE | STMT_CLOSE | PING | KILL | QUIT | OTHER |
+------------+---------+-------+--------------+--------------+------------+------+------+------+-------+
| Processor0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| Processor1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| Processor2 | 0 | 6 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| Processor3 | 0 | 4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
+------------+---------+-------+--------------+--------------+------------+------+------+------+-------+
4 rows in set (0.00 sec)
列描述 ：
PROCESSOR: processor名称
INIT_DB: COM_INIT_DB
QUERY: COM_QUERY
STMT_PREPARE: COM_STMT_PREPARE
STMT_EXECUTE: COM_STMT_EXECUTE
STMT_CLOSE: COM_STMT_CLOSE
PING: COM_PING
KILL: COM_PROCESS_KILL
QUIT: COM_QUIT
OTHER: 其余
2.1.3.14 show @@connection
show @@connection;
描述：当前活动session的前端链接信息
例：
mysql> show @@connection;
+------------+------+-----------------+------+------------+------+--------+----------------------+----------------------+-----------------------+--------+---------+---------------+-------------+------------+--------------------+------------+---------------+----------------+
| PROCESSOR | ID | HOST | PORT | LOCAL_PORT | USER | SCHEMA | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | CHARACTER_SET_RESULTS | NET_IN | NET_OUT | ALIVE_TIME(S) | RECV_BUFFER | SEND_QUEUE | TX_ISOLATION_LEVEL | AUTOCOMMIT | SYS_VARIABLES | USER_VARIABLES |
+------------+------+-----------------+------+------------+------+--------+----------------------+----------------------+-----------------------+--------+---------+---------------+-------------+------------+--------------------+------------+---------------+----------------+
| Processor2 | 1 | 0:0:0:0:0:0:0:1 | 9066 | 54761 | man | NULL | utf8 | utf8_general_ci | utf8 | 237 | 23967 | 813 | 4096 | 0 | | | | |
+------------+------+-----------------+------+------------+------+--------+----------------------+----------------------+-----------------------+--------+---------+---------------+-------------+------------+--------------------+------------+---------------+----------------+
1 row in set (0.11 sec)
结果列描述 ：
PROCESSOR: PROCESSOR名称
ID: session ID
HOST: 客户端host
PORT: 本地端口(流量或者管理)
LOCAL_PORT: 客户端端口
USER: 用户
SCHEMA: 所在的schema
CHARACTER_SET_CLIENT: 字符集信息
COLLATION_CONNECTION: 字符集信息
CHARACTER_SET_RESULTS : 字符集信息
NET_IN: 接收流量
NET_OUT: 发送流量
ALIVE_TIME(S): 连接建立时长
RECV_BUFFER: 接收缓冲区大小(字节)
SEND_QUEUE: 发送缓冲区队列大小
TX_ISOLATION_LEVEL: 隔离级别
AUTOCOMMIT: 略
SYS_VARIABLES: 系统变量
USER_VARIABLES: 用户变量
2.1.3.15 show @@cache
show @@cache；
描述：展示cache信息
例：
mysql> show @@cache;
+-----------------+-------+------+--------+------+------+-------------+----------+
| CACHE | MAX | CUR | ACCESS | HIT | PUT | LAST_ACCESS | LAST_PUT |
+-----------------+-------+------+--------+------+------+-------------+----------+
| ER_SQL2PARENTID | 1000 | 0 | 0 | 0 | 0 | | |
| SQLRouteCache | 10000 | 0 | 0 | 0 | 0 | | |
+-----------------+-------+------+--------+------+------+-------------+----------+
2 rows in set (0.09 sec)
列描述:
CACHE: cache名
MAX: 最大容量
CUR: 当前容量
ACCESS: 缓存查询次数
HIT: 命中次数
PUT: 加入缓存计数器
LAST_ACCESS:上一次查询时间戳(格式为yyyy/mm/dd hh:mm:ss)
LAST_INPUT: 上一次加入缓存时间戳(格式为yyyy/mm/dd hh:mm:ss)
2.1.3.16 show @@backend
show @@backend；
描述：当前处理线程的活动后端链接信息，可与show @@session结合使用
例：
mysql> show @@backend;
+------------+------+---------+--------------+------+----------------+--------+---------+----------------+--------+----------+------------+------------+----------------------+----------------------+-----------------------+--------------------+------------+---------------+----------------+-----------+-----------+
| processor | ID | MYSQLID | HOST | PORT | LOCAL_TCP_PORT | NET_IN | NET_OUT | ACTIVE_TIME(S) | CLOSED | BORROWED | SEND_QUEUE | SCHEMA | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | CHARACTER_SET_RESULTS | TX_ISOLATION_LEVEL | AUTOCOMMIT | SYS_VARIABLES | USER_VARIABLES | XA_STATUS | DEAD_TIME |
+------------+------+---------+--------------+------+----------------+--------+---------+----------------+--------+----------+------------+------------+----------------------+----------------------+-----------------------+--------------------+------------+---------------+----------------+-----------+-----------+
| Processor0 | 4 | 11094 | 10.18x.2x.63 | 3320 | 54734 | 600 | 171 | 166 | false | false | 0 | ares_test | utf8 | utf8_general_ci | utf8 | -1 | true | | | | |
| Processor1 | 1 | 11077 | 10.18x.2x.63 | 3320 | 54701 | 865 | 236 | 166 | false | false | 0 | mycat_test | utf8 | utf8_general_ci | utf8 | -1 | true | | | | |
| Processor2 | 2 | 11080 | 10.18x.2x.63 | 3320 | 54704 | 336 | 101 | 166 | false | false | 0 | nosharding | utf8 | utf8_general_ci | utf8 | -1 | true | | | | |
| Processor3 | 3 | 11079 | 10.18x.2x.63 | 3320 | 54703 | 336 | 101 | 166 | false | false | 0 | nosharding | utf8 | utf8_general_ci | utf8 | -1 | true | | | | |
+------------+------+---------+--------------+------+----------------+--------+---------+----------------+--------+----------+------------+------------+----------------------+----------------------+-----------------------+--------------------+------------+---------------+----------------+-----------+-----------+
4 rows in set (0.00 sec)
列描述：
processor: processor名称
ID: 连接ID
MYSQLID: mysql线程id(对应节点上的show processlist 里的ID)
HOST: 主机名
PORT: 端口
LOCAL_TCP_PORT: tcp连接的本地端口
NET_IN: 接收流量大小
NET_OUT: 发送流量大小
ACTIVE_TIME(S): 连接建立时间（单位秒）
CLOSED: 是否被关闭
BORROWED: 是否正在使用
SEND_QUEUE: 发送缓冲队列大小
SCHEMA: schema上下文
CHARACTER_SET_CLIENT: 字符集信息
COLLATION_CONNECTION: 字符集信息
CHARACTER_SET_RESULTS: 字符集信息
TX_ISOLATION_LEVEL: 隔离级别（新建未使用过的连接为-1，表示未初始化）
AUTOCOMMIT: 是否自动提交
SYS_VARIABLES: 系统变量
USER_VARIABLES: 用户变量
XA_STATUS: xa状态
DEAD_TIME： 连接池被回收的时间，连接在完成任务后也会关闭回收
2.1.3.17 show @@session
show @@session;
描述：展示当前活动前端session的后端连接信息
例：
mysql> show @@session ;
+---------+----------+----------------------------------------------------------+
| SESSION | DN_COUNT | DN_LIST |
+---------+----------+----------------------------------------------------------+
| 2 | 2 | MySQLConnection [id=59, lastTime=1508233042917 [,... ] |
+---------+----------+----------------------------------------------------------+
1 row in set (0.00 sec)
列描述：
SESSION: 前端session id
DN_COUNT: 后端链接个数：
DN_LIST: 后端连接的详情,
DN_LIST例如：
MySQLConnection [id=59, lastTime=1508233042917, user=xxxx , schema=dble_test, old schema=dble_test, borrowed=true, fromSlaveDB=false, threadId=23201,character_set_client=utf8,character_set_results=utf8,collation_connec tion=utf8_general_ci, txIsolation=3, autocommit=false, attachment=dn2{select * f rom sharding_two_node LIMIT 100}.0, respHandler=com.actiontech.dble.backend.mysq l.nio.handler.MultiNodeQueryHandler@181db802, host=10.18x.2x.64, port=3320, stat usSync=com.actiontech.dble.backend.mysql.nio.MySQLConnection$StatusSync@d7da548, writeQueue=0, modifiedSQLExecuted=false] MySQLConnection [id=58, lastTime=1508233042917, user=qrep, schema=dble_test, old schema=dble_test, borrowed=true, fromSlaveDB=false, threadId=11112,character_se t_client=utf8,character_set_results=utf8,collation_connection=utf8_general_ci, t xIsolation=3, autocommit=false, attachment=dn1{select * from sharding_two_node L IMIT 100}.0, respHandler=com.actiontech.dble.backend.mysql.nio.handler.MultiNode QueryHandler@181db802, host=10.18x.2x.63, port=3320, statusSync=com.actiontech.d ble.backend.mysql.nio.MySQLConnection$StatusSync@5882b3d, writeQueue=0, modified SQLExecuted=false]
含义:
id: 标识符
lastTime：上次读写时间戳
user: 后端连接对应的用户
schema: 后端连接对应的schema
old_schema: 上次连接被使用时的schema
borrowed: 被使用
fromSlaveDB: 是否是从数据库
threadId: 对应后端连接在数据库内的线程id（show processlist）
charset系列: 字符集
txIsolation: 隔离级别
autocommit: 自动提交
attachment: 路由结果集
respHandler: 收到回复时处理的handler类
host: host ip
port: 端口号
statusSync: 同步上下文的类
writeQueue: 写队列
modifiedSQLExecuted: 是否是增删改
2.1.3.18 show @@connection.sql
show @@connection.sql;
描述：当前活动session的前端的SQL信息
例：
mysql> show @@connection.sql;
+------+-----------------+------+--------+---------------------+--------------+-----------------------+
| ID | HOST | USER | SCHEMA | START_TIME | EXECUTE_TIME | SQL |
+------+-----------------+------+--------+---------------------+--------------+-----------------------+
| 1 | 0:0:0:0:0:0:0:1 | man | NULL | 2017/10/17 17:00:58 | 139 | show @@connection.sql |
+------+-----------------+------+--------+---------------------+--------------+-----------------------+
1 row in set (0.13 sec)
列描述:
ID: session ID
HOST: 客户端host
USER: 用户
SCHEMA: 所在的schema
START_TIME: 上次接收请求时间戳
EXECUTE_TIME: 响应时间或者未完成SQL持续时间
SQL：如果长度大于1024个字符，将会被截断为1024
2.1.3.19 show @@sql
show @@sql;
描述：展示用户近期执行完的50条sql语句(多余的每5秒清理一次)
例：
mysql> show @@sql;
+------+------+---------------------+--------------+-------------------------------------------+
| ID | USER | START_TIME | EXECUTE_TIME | SQL |
+------+------+---------------------+--------------+-------------------------------------------+
| 1 | root | 2017/10/17 17:37:22 | 381 | select * from sharding_two_node LIMIT 100 |
+------+------+---------------------+--------------+-------------------------------------------+
1 row in set (0.02 sec)
列描述:
ID: 行号
USER: 用户
START_TIME: 上次接收请求时间戳
EXECUTE_TIME: 响应时间
SQL：略
如果需要在查询后重置统计，执行：
show @@sql true;
2.1.3.20 show @@sql.high
show @@sql.high；
描述：展示各个用户的高频sql(容量1024，超过会被定期清理，清理周期5秒)
例：
mysql> show @@sql.high;
+------+------+-----------+----------+----------+----------+--------------+---------------------+-----------------------------------------+
| ID | USER | FREQUENCY | AVG_TIME | MAX_TIME | MIN_TIME | EXECUTE_TIME | LAST_TIME | SQL |
+------+------+-----------+----------+----------+----------+--------------+---------------------+-----------------------------------------+
| 1 | root | 1 | 381 | 381 | 381 | 381 | 2017/10/17 17:37:23 | SELECT * FROM sharding_two_node LIMIT ? |
+------+------+-----------+----------+----------+----------+--------------+---------------------+-----------------------------------------+
1 row in set (0.06 sec)
列描述：
ID: 行号
USER: 用户
FREQUENCY: sql曾被执行次数
AVG_TIME: 平均执行耗时
MAX_TIME: 最大执行耗时
MIN_TIME: 最小执行耗时
EXECUTE_TIME: 最近一次执行耗时
LAST_TIME: 最近一次执行时间戳
SQL：略
如果需要在查询后重置统计，执行：
show @@sql.high true；
2.1.3.21 show @@sql.slow
show @@sql.slow;
描述：展示执行时间超过给定阈值(默认100毫秒，可通过reload修改)的sql(默认10条，可以通过设置系统参数sqlRecordCount修改，多余的每5秒清理一次）
例：
mysql> show @@sql.slow;
+------+---------------------+--------------+-------------------------------------------+
| USER | START_TIME | EXECUTE_TIME | SQL |
+------+---------------------+--------------+-------------------------------------------+
| root | 2017/10/17 17:37:22 | 381 | select * from sharding_two_node LIMIT 100 |
+------+---------------------+--------------+-------------------------------------------+
1 row in set (0.07 sec)
列描述：
USER: 用户
START_TIME:上次接收请求时间戳
EXECUTE_TIME:响应时间
SQL：略
如果需要在查询后重置统计，执行：
show @@sql.slow true；
2.1.3.22 show @@sql.resultset
show @@sql.resultset;
描述：展示结果集大小超过某个阈值(默认512K，可以通过maxResultSet配置) 的sql，结果集统计信息
例：
mysql> show @@sql.resultset;
+------+------+-----------+---------------------------------+----------------+
| ID | USER | FREQUENCY | SQL | RESULTSET_SIZE |
+------+------+-----------+---------------------------------+----------------+
| 1 | root | 1 | SELECT * FROM sharding_two_node | 1048576 |
+------+------+-----------+---------------------------------+----------------+
1 row in set (0.05 sec)
列描述：
ID:行号
USER: 用户
FREQUENCY:sql曾被执行次数
SQL: 略
RESULTSET_SIZE:结果集的大小
2.1.3.23 show @@sql.sum
show @@sql.sum；
描述：展示用户的sql执行情况, 是否带.user结果是一样的.带参数true，表示查询结束后清空已经缓存的结果
例：
mysql> show @@sql.sum;
+------+------+------+------+------+------+--------+---------+--------------+--------------+---------------------+
| ID | USER | R | W | R% | MAX | NET_IN | NET_OUT | TIME_COUNT | TTL_COUNT | LAST_TIME |
+------+------+------+------+------+------+--------+---------+--------------+--------------+---------------------+
| 1 | root | 1 | 0 | 1.00 | 1 | 41 | 840 | [0, 0, 1, 0] | [0, 0, 1, 0] | 2017/10/17 17:37:23 |
+------+------+------+------+------+------+--------+---------+--------------+--------------+---------------------+
1 row in set (0.26 sec)
列描述:
ID:行号
USER:用户
R:读的次数
W:写的次数，恒为零(因为未实现统计)
R%:因为W为0，此值恒为100%
MAX:最大并发数
NET_IN:网络流入量
NET_OUT:网络流出量
TIME_COUNT:query在四个时间区间的个数分布，四个区间分别是前一天22-06 夜间,06-13 上午,13-18下午,18-22 晚间
TTL_COUNT:query耗时在四个时间级别内的个数分布，四个区间分别是10毫秒内,10 - 200毫秒内,1秒内,超过 1秒
LAST_TIME:上次SQL执行时间戳
如果需要在查询后重置统计，执行：
show @@sql.sum true；
2.1.3.24 show @@sql.sum.user
等同于：
show @@sql.sum；
如果需要在查询后重置统计，执行：
show @@sql.sum.user true；
2.1.3.25 show @@sql.sum.table
show @@sql.sum.table;
描述：展示各个表的读写情况
例：
mysql> show @@sql.sum.table;
+------+-------------------+------+------+------+-----------+-----------+---------------------+
| ID | TABLE | R | W | R% | RELATABLE | RELACOUNT | LAST_TIME |
+------+-------------------+------+------+------+-----------+-----------+---------------------+
| 1 | sharding_two_node | 1 | 0 | 1.00 | NULL | NULL | 2017/10/17 17:37:23 |
+------+-------------------+------+------+------+-----------+-----------+---------------------+
1 row in set (0.06 sec)
列描述:
ID:行号
TABLE:表名(注:解析器实现很简单,可能有bug)
R:读的次数
W:写的次数，恒为零(因为未实现统计)
R%:因为W为0，此值恒为100%
RELATABLE:关联表的名称(目前拆分表关联查询都使用查询计划树,此值为NULL)
RELACOUNT:关联表的个数(目前拆分表关联查询都使用查询计划树,此值为NULL)
LAST_TIME:上次SQL执行时间戳
如果需要在查询后重置统计，执行：
show @@sql.sum.table true；
2.1.3.26 show @@heartbeat
show @@heartbeat；
描述：展示datasource的heartbeat信息
例：
mysql> show @@heartbeat;
+--------+--------------+------+---------+-------+--------+---------+--------------+------------------+-------+
| NAME | HOST | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME | STOP |
+--------+--------------+------+---------+-------+--------+---------+--------------+------------------+-------+
| hostM1 | 10.18x.2x.63 | 3320 | 1 | 0 | idle | 0 | 8,8,8 | NULL | false |
| hostM2 | 10.18x.2x.64 | 3320 | 1 | 0 | idle | 0 | 9,9,9 | NULL | false |
+--------+--------------+------+---------+-------+--------+---------+--------------+------------------+-------+
2 rows in set (0.07 sec)
列描述：
NAME:dataHost名称
HOST:主机名/IP
PORT:端口
RS_CODE:状态码 0:初始化,1:OK,-1:ERROR,-2:TIMEOUT
RETRY:重试错误次数
STATUS:checking/idle
TIMEOUT:心跳超时阈值(始终为0，bug?)
EXECUTE_TIME:最近3个时段的平均响应时间，默认1，10，30分钟
LAST_ACTIVE_TIME:上次收到心跳回复时间戳
STOP:是否stop,和stop命令相关
2.1.3.27 show @@heartbeat.detail where name=？
show @@heartbeat.detail where name=xxx;
其中，xxx为要查询的datasource的名字。
描述：展示指定datasource的heartbeat的详细信息
前提条件:至少发生过一次心跳语句（与dataNodeHeartbeatPeriod相关）
例:
mysql> show @@heartbeat.detail where name='hostM1';
+--------+--------------+------+---------------------+--------------+
| NAME | HOST | PORT | TIME | EXECUTE_TIME |
+--------+--------------+------+---------------------+--------------+
| hostM1 | 10.18x.2x.63 | 3320 | 2017-10-17 17:31:58 | 7 |
| hostM1 | 10.18x.2x.63 | 3320 | 2017-10-17 17:32:59 | 9 |
+--------+--------------+------+---------------------+--------------+
2 row in set (0.00 sec)
列描述:
NAME:dataHost名称
HOST:主机名/IP
PORT:端口
TIME:收到心跳时间戳
EXECUTE_TIME:心跳执行耗时(毫秒)
2.1.3.28 show @@sysparam
show @@sysparam；
描述：展示sysconfig参数配置
结果：略
2.1.3.29 show @@syslog limit=?
show @@syslog limit=N；
其中，N为整数。
描述: 按时间从新到旧展示dble.log中的N条记录内容。
结果：略
2.1.3.30 show @@white
show @@white；
描述：展示配置的白名单信息
例：
mysql> show @@white;
+-----------------+---------------+
| IP | USER |
+-----------------+---------------+
| 0:0:0:0:0:0:0:1 | root,man,man2 |
| 127.0.0.1 | root |
+-----------------+---------------+
2 rows in set (0.02 sec)
列描述：
略
2.1.3.31 show @@directmemory=？
show @@directmemory=N；
其中，N有两个取值1或者2。
当N为1时：
描述：内存使用总览
结果集举例：
+---------------------+--------------------+-------------------------+
| DIRECT_MEMORY_MAXED | DIRECT_MEMORY_USED | DIRECT_MEMORY_AVAILABLE |
+---------------------+--------------------+-------------------------+
| 2GB | 8KB | 2047MB |
+---------------------+--------------------+-------------------------+
1 row in set (0.16 sec)
结果列描述 :
MDIRECT_MEMORY_MAXED:通过-XX:MaxDirectMemorySize=2048m设置的值
DIRECT_MEMORY_USED:已经使用DirectMemory内存(包括网络packet处理和合并时候使用的 )
DIRECT_MEMORY_AVAILABLE：仍然可用的DirectMemory；MaxDirectMemorySize-DIRECT_MEMORY_USED
当N为2时：
描述：directmemory内存的具体使用情况。
结果集举例：
mysql> show @@directmemory=2;
+-----------+-------------------+----------+
| THREAD_ID | MEM_USE_TYPE | SIZE |
+-----------+-------------------+----------+
| 32 | NetWorkBufferPool | 0 |
| 1 | NetWorkBufferPool | 0 |
| 33 | NetWorkBufferPool | 0 |
| 35 | NetWorkBufferPool | 0 |
| 19 | NetWorkBufferPool | 0 |
| 20 | NetWorkBufferPool | 0 |
| 21 | NetWorkBufferPool | 0 |
| 22 | NetWorkBufferPool | 0 |
| 23 | NetWorkBufferPool | 228KB |
| 24 | NetWorkBufferPool | 4KB |
| 26 | NetWorkBufferPool | 0 |
| 27 | NetWorkBufferPool | 0 |
| 28 | NetWorkBufferPool | 4KB |
| 29 | NetWorkBufferPool | 0 |
| 30 | NetWorkBufferPool | 0 |
| 31 | NetWorkBufferPool | 0 |
+-----------+-------------------+----------+
16 rows in set (0.00 sec)
结果列描述：
THREAD_ID：线程ID
MEM_USE_TYPE:MergeMemoryPool/NetWorkBufferPool
SIZE:大小
2.1.3.32 show @@command.count
show @@command.count；
描述：查询当前系统的查询数；
结果：略
2.1.3.33 show @@connection.count
show @@connection.count；
描述：查询当前的前端链接数；
结果：略
2.1.3.34 show @@backend.statistics
show @@backend.statistics；
描述：查询系统中进程的后端数据源信息；
例：
MySQL [(none)]> show @@backend.statistics;
+---------------+---------+------------+----------+
| HOST | PORT | ACTIVE | TOTAL |
+---------------+---------+------------+----------+
| 192.168.2.177 | 3307 | 0 | 10 |
| 192.168.2.177 | 3308 | 0 | 10 |
+---------------+---------+------------+----------+
2 rows in set (0.02 sec)
列描述：
HOST：数据源的ip
PORT：数据源的端口
ACTIVE：数据源正在被使用的链接数
TOTAL ：活的后端链接数。
2.1.3.35 show @@backend.old
show @@backend.old；
描述：reload @@config_all之后待回收的活动的后端链接信息
结果格式：同show @@backend
2.1.3.36 show @@binlog.status
show @@binlog.status；
描述：对server下所有节点拉一条一致性的binlog线。
例：
mysql> show @@binlog.status;
+-------------------+------------------+----------+--------------+------------------+----------------------------------------------+
| Url | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+------------------+----------+--------------+------------------+----------------------------------------------+
| 10.18x.2x.63:3320 | mysql-bin.000024 | 14128 | | | 7ad71aab-de94-11e5-9488-3a935460da28:1-67646 |
| 10.18x.2x.64:3320 | mysql-bin.000049 | 604440 | | | ba8f8b5c-debf-11e5-a87b-26b8a61f9012:1-91 |
+-------------------+------------------+----------+--------------+------------------+----------------------------------------------+
2 rows in set (0.11 sec)
列描述：
Url: 后端节点的连接Url值
其余列：等同于在对应结点上执行show master status的结果。
2.1.3.37 show @@help
show @@help；
描述：展示帮助信息
结果：略
2.1.3.38 show @@sql.large
show @@sql.large；
描述：展示各个用户的结果集超过10000行的sql(容量为10,多的会被定时清理，清理周期5秒)
例：
mysql> show @@sql.large;
+------+-------+---------------------+--------------+-----------------------------------------+
| USER | ROWS | START_TIME | EXECUTE_TIME | SQL |
+------+-------+---------------------+--------------+-----------------------------------------+
| root | 20000 |2017/10/17 17:37:23 | 381 | SELECT * FROM sharding_two_node LIMIT ? |
+------+-------+---------------------+--------------+-----------------------------------------+
1 row in set (0.06 sec)
列描述:
USER: 用户
ROWS: 该查询的行数
START_TIME:上次接收请求时间戳 EXECUTE_TIME:响应时间
SQL：略
如果需要在查询后重置统计，执行：
show @@sql.large true；
2.1.3.39 show @@sql.condition
show @@sql.condition;
描述：查询条件统计，需要配合reload @@query_cf 使用，前者设置了table&column后，运行此语句后展示sql查询条件统计信息.（最多100000条，超出后不再统计）
比如select from sharding_two_node where id =0; 和select from sharding_two_node where id =1;
例：
mysql> show @@sql.condition;
+------+---------------------------------+-------+-------+
| ID | KEY | VALUE | COUNT |
+------+---------------------------------+-------+-------+
| 2 | sharding_two_node.id | 0 | 1 |
| 3 | sharding_two_node.id | 1 | 2 |
| 2 | sharding_two_node.id.valuekey | size | 2 |
| 3 | sharding_two_node.id.valuecount | total | 3 |
+------+---------------------------------+-------+-------+
4 rows in set (0.05 sec)
列描述:
ID: 行号
KEY: schema.table 最后两行为schema.table.valuekey 和 schema.table.valuecount
VALUE: 对应key的value值
COUNT: 查询的次数
2.1.3.40 show @@cost_time;
show @@cost_time;
描述：查询query耗时统计的结果,需要在server.xml中开启useCostTimeStat选项之后才会有统计结果
例：
mysql> show @@cost_time;
+--------------+----------------------------------+-----------------------------------+
| OVER_ALL(us) | FRONT_PREPARE | BACKEND_EXECUTE |
+--------------+----------------------------------+-----------------------------------+
| 71496 | Id:9,Time:53135;Id:12,Time:54056 | Id:9,Time:16924;Id:12,Time:16006 |
| 15316 | Id:17,Time:2301;Id:11,Time:3196 | Id:17,Time:10691;Id:11,Time:11397 |
+--------------+----------------------------------+-----------------------------------+
2 rows in set (0.05 sec)
列描述:
OVER_ALL: 总耗时
FRONT_PREPARE: 前端连接以及dble中的耗时
BACKEND_EXECUTE: 后端连接执行耗时
2.1.3.41 show @@dataNodes where schema=? and table=?;
show @@dataNodes
描述：查询某具体表格的节点信息
例：
mysql> show @@dataNodes where schema=testdb and table=seqtest;
+------+----------+---------------+------+-----------------+------+----------+
| NAME | SEQUENCE | HOST | PORT | PHYSICAL_SCHEMA | USER | PASSWORD |
+------+----------+---------------+------+-----------------+------+----------+
| dn1 | 0 | 10.186.24.113 | 3309 | db1 | root | 123456 |
| dn2 | 1 | 10.186.24.113 | 3309 | db2 | root | 123456 |
+------+----------+---------------+------+-----------------+------+----------+
2 rows in set (0.05 sec)
列描述:
NAME:节点名称
SEQUENCE:节点编号
HOST:节点所在的IP
PORT：节点对应的服务端口
PHYSICAL_SCHEMA：节点所对应的物理库
USER：节点连接的用户
PASSWORD：节点连接的密码
2.1.3.42 show @@algorithm where schema=? and table=?;
show @@algorithm
描述：查询某具体表格的分片算法信息，由于不同算法会有不同的分片参数以及辅助文件及数据，所以不同算法表格的输出分片事项都不相同
例：
mysql> show @@algorithm where schema=testdb and table=seqtest;
+-----------------+----------------------------------------------------+
| KEY | VALUE |
+-----------------+----------------------------------------------------+
| TYPE | SHARDING TABLE |
| COLUMN | ID |
| CLASS | com.actiontech.dble.route.function.PartitionByLong |
| partitionCount | 2 |
| partitionLength | 1 |
+-----------------+----------------------------------------------------+
5 rows in set (0.05 sec)
行描述:
KEY:分片事项
VALUE：详细信息
2.1.3.43 show @@thread_used;
show @@thread_used;
描述：查看各个主要业务处理线程的使用状况
例：
mysql> show @@thread_used;
+-------------------------+------------------+-------------+------------------+
| THREAD_NAME | LAST_QUARTER_MIN | LAST_MINUTE | LAST_FIVE_MINUTE |
+-------------------------+------------------+-------------+------------------+
| BusinessExecutor3 | 0% | 0% | 0% |
| $_NIO_REACTOR_BACKEND-2 | 0% | 0% | 0% |
| BusinessExecutor1 | 0% | 0% | 0% |
| $_NIO_REACTOR_BACKEND-3 | 0% | 0% | 0% |
| $_NIO_REACTOR_BACKEND-0 | 0% | 0% | 0% |
| $_NIO_REACTOR_FRONT-0 | 0% | 0% | 0% |
| $_NIO_REACTOR_BACKEND-1 | 0% | 0% | 0% |
+-------------------------+------------------+-------------+------------------+
7 rows in set (0.00 sec)
行描述:
THREAD_NAME:线程名称
LAST_QUARTER_MIN：最近15秒使用率
LAST_MINUTE：最近一分钟使用率
LAST_FIVE_MINUTE：最近五分钟使用率
2.1.3.44 show @@ddl;
show @@ddl;
描述：查看正在执行，没有在dble内部释放锁的DDL
例：
mysql> show @@ddl;
+--------+--------------------+---------------------------------------------------+
| Schema | Table | Sql |
+-----------------------------+---------------------------------------------------+
| testdb | sharding_two_node | alter table sharding_two_node add column id2 int |
| mytest | sharding_four_node | drop table sharding_four_node |
+--------+--------------------+---------------------------------------------------+
2 rows in set (0.00 sec)
行描述:
Schema:Schema名称
Table：Table名称
Sql：ddl sql语句
2.1.3.45 show @@processlist;
show @@processlist;
描述：查看前端连接和后端连接对应关系，若前端连接没有对应的后端连接，显示NULL。
例：
mysql> show @@processlist;
+----------+----------+---------+------+-----------------+------+---------+------+-------+------+
| Front_Id | Datanode | BconnID | User | Front_Host | db | Command | Time | State | Info |
+----------+----------+---------+------+-----------------+------+---------+------+-------+------+
| 1 | dn2 | 2303 | root | 127.0.0.1:33222 | db2 | Sleep | 17 | | NULL |
| 2 | NULL | NULL | man1 | 127.0.0.1:34882 | NULL | NULL | 0 | | NULL |
| 3 | dn3 | 2259 | root | 127.0.0.1:33226 | db1 | Sleep | 4 | | NULL |
| 3 | dn2 | 2308 | root | 127.0.0.1:33226 | db2 | Sleep | 4 | | NULL |
| 3 | dn1 | 2304 | root | 127.0.0.1:33226 | db1 | Sleep | 4 | | NULL |
+----------+----------+---------+------+-----------------+------+---------+------+-------+------+
5 rows in set (0.05 sec)
行描述:
Front_Id：前端连接ID
Datanode：前端连接下发操作的 datanode
BconnID：后端连接对应的 mysql 线程ID
User：用户名
Front_Host：客户端主机名
db：后端连接默认数据库，来自于 mysql 'show processlist' 字段 db
Command：mysql线程正在执行的指令类型，来自于 mysql 'show processlist' 字段 Command
Time：mysql线程处于当前state的时间，来自于 mysql 'show processlist' 字段 Time
State：mysql线程执行状态，来自于 mysql 'show processlist' 字段 State
Info：mysql线程执行语句，来自于 mysql 'show processlist' 字段 Info
2.1.3.46 show @@session.xa;
show @@session.xa;
描述：查看后台重试的xa事务信息。
例：
mysql> show @@session.xa;
+---------+--------------------+-------------------------+------------------+
| SESSION | XA_ID | XA_STATE | DATANODES |
+---------+--------------------+-------------------------+------------------+
| 1 | 'Dble_Server.1.1' | TX_COMMIT_FAILED_STATE | dn1,dn3 |
+---------+--------------------+-------------------------+------------------+
1 rows in set (0.00 sec)
行描述:
SESSION：会话id
XA_ID：xa事务id
XA_STATE：xa事务状态
DATANODES：xa提交失败的datanode名称