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;
+------------+--------+--------------+------+------+--------+------+------+---------+-----------+------------+
| DATAHOST   | NAME   | HOST         | PORT | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+------------+--------+--------------+------+------+--------+------+------+---------+-----------+------------+
| localhost2 | hostS1 | 10.18x.2x.63 | 3307 | W    |      1 |    9 |  100 |      11 |         0 |          0 |
| localhost1 | hostM1 | 10.18x.2x.64 | 3306 | W    |      1 |    9 |  100 |      17 |         0 |          0 |
+------------+--------+--------------+------+------+--------+------+------+---------+-----------+------------+
2 rows in set (0.09 sec)

列描述

DATAHOST:dataSource所属datahost
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;
+------------+--------+--------------+------+--------------+-------------+-------------+-----------------------+------------------+-------------------+----------------+---------------+---------------+
| DATAHOST   | 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 |
+------------+--------+--------------+------+--------------+-------------+-------------+-----------------------+------------------+-------------------+----------------+---------------+---------------+
| localhost1 | hostM1 | 10.18x.2x.63 | 3320 | 10.18x.2x.61 |        3320 | qrep        |                  NULL | No               | No                |                |            60 |               |
| localhost2 | hostM2 | 10.18x.2x.64 | 3320 | 10.18x.2x.62 |        3320 | qrep        |                  NULL | No               | No                |                |            60 |               |
+------------+--------+--------------+------+--------------+-------------+-------------+-----------------------+------------------+-------------------+----------------+---------------+---------------+
2 row in set (0.00 sec)

列描述:

DATAHOST:dataSource所属datahost
NAME: dataSource名称
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;
+------------+--------+--------------+------+--------------+-------------+-------------+---------------------+-----------------------+
| DATAHOST   | NAME   | HOST         | PORT | MASTER_HOST  | MASTER_PORT | MASTER_USER | TIME                | SECONDS_BEHIND_MASTER |
+------------+--------+--------------+------+--------------+-------------+-------------+---------------------+-----------------------+
| localhost2 | hostM2 | 10.18x.2x.64 | 3320 | 10.18x.2x.62 |        3320 | qrep        | 2017-10-17 18:31:27 |                    -1 |
| localhost2 | hostM2 | 10.18x.2x.64 | 3320 | 10.18x.2x.62 |        3320 | qrep        | 2017-10-17 18:31:57 |                    -1 |
| localhost2 | hostM2 | 10.18x.2x.64 | 3320 | 10.18x.2x.62 |        3320 | qrep        | 2017-10-17 18:32:27 |                    -1 |
| localhost2 | 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)

列描述:

DATAHOST:dataSource所属datahost
NAME: dataSource名称
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  | FRONT_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名称
FRONT_ID: 前端连接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  | BACKEND_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名称
BACKEND_ID: 后端连接ID
MYSQLID: mysql线程id(对应节点上的show processlist里的MYSQLID)
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 ;
+----------+----------+-----------------------------------------------------------------+
| FRONT_ID | DN_COUNT | DN_LIST                                                         |
+----------+----------+-----------------------------------------------------------------+
| 2        | 2        | MySQLConnection [backendId=59, lastTime=1508233042917 [,... ]   |
+----------+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

列描述:

FRONT_ID: 前端连接ID
DN_COUNT: 后端连接个数:
DN_LIST: 后端连接的详情,

DN_LIST例如:

MySQLConnection [backendId=59, lastTime=1508233042917, user=xxxx , schema=dble_test, old schema=dble_test, borrowed=true, fromSlaveDB=false, mysqlId=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]

含义:

backendId: 后端连接id
lastTime:上次读写时间戳
user: 后端连接对应的用户
schema: 后端连接对应的schema
old_schema: 上次连接被使用时的schema
borrowed: 被使用
fromSlaveDB: 是否是从数据库
mysqlId: 对应后端连接在数据库内的线程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;
+----------+-----------------+------+--------+---------------------+--------------+-----------------------+
| FRONT_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)

列描述:

FRONT_ID: 前端连接ID
HOST: 客户端host
USER: 用户
SCHEMA: 所在的schema
START_TIME: 上次接收请求时间戳
EXECUTE_TIME: 响应时间或者未完成SQL持续时间(由于实现方式的原因,可能出现正负20ms的误差)
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 | MysqlId | 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  
MysqlId:后端连接对应的 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;
+----------+--------------------+-------------------------+------------------+
| FRONT_ID | XA_ID              | XA_STATE                | DATANODES        |
+----------+--------------------+-------------------------+------------------+
| 1        | 'Dble_Server.1.1'  | TX_COMMIT_FAILED_STATE  | dn1,dn3          |
+----------+--------------------+-------------------------+------------------+
1 rows in set (0.00 sec)

行描述:

FRONT_ID:前端连接ID  
XA_ID:xa事务id  
XA_STATE:xa事务状态
DATANODES:xa提交失败的datanode名称

2.1.3.47 show @@reload_status

show @@reload_status

描述:查看dble中最近的reload信息

举例

+-------+---------+-------------+---------------+---------------------+---------------------+---------------+------------+
| INDEX | CLUSTER | RELOAD_TYPE | RELOAD_STATUS | LAST_RELOAD_START   | LAST_RELOAD_END     | TRIGGER_TYPE  | END_TYPE   |
+-------+---------+-------------+---------------+---------------------+---------------------+---------------+------------+
|     0 | false   | RELOAD_ALL  | NOT_RELOADING | 2019/08/19 14:28:04 | 2019/08/19 14:28:05 | LOCAL_COMMAND | RELOAD_END |
+-------+---------+-------------+---------------+---------------------+---------------------+---------------+------------+

行描述:

INDEX:reload对应的编号,能与日志中的[RL]日志编号相对应
CLUSTER:当前dble使用的集群方式
RELOAD_TYPE:最近的reload的类型 reload_matadata/config/config_all/rollback
RELOAD_STATUS:最近一次reload的执行状状态not_reloading/self_reload/meta_reload/waiting_others
LAST_RELOAD_START:起始时间
LAST_RELOAD_END:结束时间
TRIGGER_TYPE:触发类型reload_command/cluster_notify
END_TYPE:结束原因

此命令被用于配合命令release @@reload_metadata

2.1.3.48 show @@user

show @@user

描述:查看dble 所有用户

举例

mysql> show @@user;
+----------+---------+----------+----------+
| Username | Manager | Readonly | Max_con  |
+----------+---------+----------+----------+
| man1     | Y       | N        | no limit |
| root     | N       | N        | no limit |
| user     | N       | N        | no limit |
+----------+---------+----------+----------+
3 rows in set (0.03 sec)

行描述:

Username:用户名
Manager:是否是管理用户
Readonly:是否是只读用户
Max_con:最大连接数

2.1.3.49 show @@user.privilege

show @@user.privilege

描述:查看dble 用户的权限信息,不包含管理用户

举例

mysql> show @@user.privilege;
+----------+---------+-------+--------+--------+--------+--------+
| Username | Schema  | Table | INSERT | UPDATE | SELECT | DELETE |
+----------+---------+-------+--------+--------+--------+--------+
| root     | testdb1 | *     | Y      | Y      | Y      | Y      |
| root     | testdb  | *     | Y      | Y      | Y      | Y      |
| user     | testdb  | *     | N      | Y      | Y      | N      |
+----------+---------+-------+--------+--------+--------+--------+
3 rows in set (0.01 sec)

行描述:

Username:用户名
Schema:用户授权逻辑库
Table:用户显式指定dml权限的表名, 未指定的其他表使用*表示
INSERT:插入权限位
UPDATE:更新权限位
SELECT:查询权限位
DELETE:删除权限位

results matching ""

    No results matching ""