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         | CHARSET | STATUS |
+------------+-------------+--------------+------------+---------------------+---------+--------+
| 1h 4m 47s  |    17414592 |     87031808 | 1840250880 | 2017/10/17 16:42:09 | utf8    | ON     |
+------------+-------------+--------------+------------+---------------------+---------+--------+
1 row in set (0.05 sec)

列描述:

UPTIME: 服务已经启动时间  
USED_MEMORY: 已使用堆内存  
TOTAL_MEMORY: 总共的堆内存  
MAX_MEMORY: 最大可用堆内存   
RELOAD_TIME: 上次config加载时间  
CHARSET: 字符集  
STATUS: 在线状态

2.1.3.5 show @@threadpool / show @@threadpool.task

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: 总共线程数量

注意:

  • 这里的 ACTIVE_COUNT/TASK_QUEUE_SIZE/COMPLETED_TASK/TOTAL_TASK 都是只统计 线程数 的变化。但是,dble 针对某些线程池(BusinessExecutor和writeToBackendExecutor)采用了常驻线程的实现方式,故线程数不变,使得这些字段不能反映 "是否消费了任务" 以及 "消费了多少了个任务" 。为此,如需获得线程池执行任务的统计情况,可以考虑使用show @@threadpool.task。
show @@threadpool.task;

描述:展示当前线程池的执行任务的情况

例:

mysql> show @@threadpool.task;
+-------------------------+-----------+-------------------+-----------------+----------------+------------+
| NAME                    | POOL_SIZE | ACTIVE_TASK_COUNT | TASK_QUEUE_SIZE | COMPLETED_TASK | TOTAL_TASK |
+-------------------------+-----------+-------------------+-----------------+----------------+------------+
| Timer                   |         1 |                 0 |               0 |            100 |        100 |
| BusinessExecutor        |         4 |                 1 |               0 |             45 |         46 |
| backendBusinessExecutor |        12 |                 0 |               0 |           1631 |       1631 |
| complexQueryExecutor    |         8 |                 0 |               0 |             98 |         98 |
| writeToBackendExecutor  |         2 |                 0 |               0 |              0 |          0 |
+-------------------------+-----------+-------------------+-----------------+----------------+------------+
5 rows in set (0.00 sec)

列描述:

NAME: 线程池名称  
POOL_SIZE: 线程池大小  
ACTIVE_TASK_COUNT: 处理中的任务数量  
TASK_QUEUE_SIZE: 队列中的任务数量  
COMPLETED_TASK: 已完成的任务数量  
TOTAL_TASK: 总共任务数量

2.1.3.6 show @@database

show @@database;

描述:展示配置的schema名字

结果:略

2.1.3.7 show @@shardingnode

show @@shardingnode;

描述:展示配置中所有已使用的shardingnode信息

例:

mysql> show @@shardingnode;
+------+----------------+---------------+--------+------+------+---------+---------------+
| NAME | DB_GROUP        | SCHEMA_EXISTS | ACTIVE | IDLE | SIZE | EXECUTE | RECOVERY_TIME |
+------+----------------+---------------+--------+------+------+---------+---------------+
| dn1  | dh1/dble_test  | true          |      0 |    0 | 1000 |      34 |            -1 |
| dn2  | dh2/dble_test  | true          |      0 |    0 | 1000 |      34 |            -1 |
| dn3  | dh1/dble2_test | false         |      0 |    0 | 1000 |      26 |            -1 |
| dn4  | dh2/dble2_test | true          |      0 |    0 | 1000 |      26 |            -1 |
| dn5  | dh1/nosharding | true          |      0 |    0 | 1000 |       9 |            -1 |
+------+----------------+---------------+--------+------+------+---------+---------------+
5 rows in set (0.09 sec)

列描述:

NAME: 名称
DB_GROUP: dbGroupName/实际schema
SCHEMA_EXISTS: 对应后端物理库是否存在,true为存在,false为不存在。
ACTIVE: 当前活动的后端连接数量
IDLE: 当前空闲的后端连接数量(空闲容量维护疑似bug)
SIZE: maxCon容量
EXECUTE: 有过活动的后端连接数量统计
RECOVERY_TIME: 恢复心跳还需要秒数(stop @@heartbeat 中设置)

如果要查看某个schema相关的shardingnode信息,执行:

show @@shardingnode where schema=xxx;

其中,xxx为要查看的schema的名字。

2.1.3.8 show @@dbinstance

show @@dbinstance

描述:展示配置的所有dbinstance信息

例:

mysql> show @@dbinstance;
+------------+--------+--------------+------+------+--------+------+------+---------+-----------+------------+----------+
| DB_GROUP   | NAME   | HOST         | PORT | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD | DISABLED |
+------------+--------+--------------+------+------+--------+------+------+---------+-----------+------------+----------+
| localhost2 | hostS1 | 10.18x.2x.63 | 3307 | W    |      1 |    9 |  100 |      11 |         0 |          0 |     true |
| localhost1 | hostM1 | 10.18x.2x.64 | 3306 | W    |      1 |    9 |  100 |      17 |         0 |          0 |    false |
+------------+--------+--------------+------+------+--------+------+------+---------+-----------+------------+----------+
2 rows in set (0.09 sec)

列描述

DB_GROUP:dbinstance所属DB_GROUP
NAME: dbinstance名称
HOST: host名
PORT: 端口
W/R: 读写结点标识
ACTIVE: 当前活动的后端连接数量,按照dbinstance统计
IDLE: 当前空闲的后端连接数量,按照dbinstance统计(空闲容量维护疑似bug)
SIZE: maxCon容量
EXECUTE: 有过活动的后端连接数量统计,按照dbinstance统计
READ_LOAD: 对select或者show语句的统计 (每个事务统计一次)
WRITE_LOAD: 非select或者show语句的统计,当开启显示事务后sql都被统计为WRITE_LOAD(每个事务统计一次)
DISABLED: db.xml中dbinstance中的配置 (2.19.09.0以前的版本没有此列,disabled为true的结点不显示)

如果要查看某个shardingnode对应的dbinstance信息,执行:

show @@dbinstance where shardingnode=xxx;

其中,xxx为要查看的shardingnode的名字。

2.1.3.9 show @@dbinstance.synstatus

show @@dbinstance.synstatus;

描述:展示当前各dbinstance的同步信息
前提条件: heartbeat 配置了 show slave status(参见db.xml)

例:


mysql> show @@dbinstance.synstatus \G
*************************** 1. row ***************************
             DB_GROUP: dbGroup2
                 NAME: instanceM3
                 HOST: 111.231.25.141
                 PORT: 30309
          MASTER_HOST: mysql3
          MASTER_PORT: 3306
          MASTER_USER: replicator
SECONDS_BEHIND_MASTER: 0
     SLAVE_IO_RUNNING: Yes
    SLAVE_SQL_RUNNING: Yes
       SLAVE_IO_STATE: Waiting for master to send event
        CONNECT_RETRY: 10
        LAST_IO_ERROR:
1 row in set (0.00 sec)

列描述:

DB_GROUP:dbinstance所属DB_GROUP
NAME: dbinstance名称
HOST: 主机名/ip
PORT: 端口

其余列含义参见mysql中show slave status的命令。

2.1.3.10 show @@dbinstance.syndetail where name=?

show @@dbinstance.syndetail where name=xxx;

其中,xxx为要查看的dbinstance的名字。

描述:展示24小时内各dbinstance的历次同步信息

例:

mysql> show @@dbinstance.syndetail WHERE name =hostM2;
+------------+--------+--------------+------+--------------+-------------+-------------+---------------------+-----------------------+
| DB_GROUP   | 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)

列描述:

DB_GROUP:dbinstance所属DB_GROUP
NAME: dbinstance名称
HOST: 主机名/ip
PORT: 端口

其余列含义参见mysql中show slave status的命令。

2.1.3.11 show @@datasource.cluster

show @@datasource.cluster;

描述:此功能在2.20.04.0 版本已经废除。

2.1.3.12 show @@processor

show @@processor;

描述:展示dble实例的processor信息

例:

mysql> show @@processor\G
*************************** 1. row ***************************
        NAME: frontProcessor0
      NET_IN: 0
     NET_OUT: 0
 REACT_COUNT: 0
     R_QUEUE: 0
     W_QUEUE: 0
 FREE_BUFFER: 1072169008
TOTAL_BUFFER: 1073741824
  BU_PERCENT: 0
    BU_WARNS: 0
    FC_COUNT: 0
    BC_COUNT: 0
*************************** 2. row ***************************
        NAME: frontProcessor1
      NET_IN: 0
     NET_OUT: 267
 REACT_COUNT: 0
     R_QUEUE: 0
     W_QUEUE: 0
 FREE_BUFFER: 1072169008
TOTAL_BUFFER: 1073741824
  BU_PERCENT: 0
    BU_WARNS: 0
    FC_COUNT: 0
    BC_COUNT: 0
*************************** 3. row ***************************
        NAME: frontProcessor2
      NET_IN: 0
     NET_OUT: 150
 REACT_COUNT: 0
     R_QUEUE: 0
     W_QUEUE: 0
 FREE_BUFFER: 1072169008
TOTAL_BUFFER: 1073741824
  BU_PERCENT: 0
    BU_WARNS: 0
    FC_COUNT: 0
    BC_COUNT: 0
*************************** 4. row ***************************
        NAME: frontProcessor3
      NET_IN: 0
     NET_OUT: 1548
 REACT_COUNT: 0
     R_QUEUE: 0
     W_QUEUE: 0
 FREE_BUFFER: 1072169008
TOTAL_BUFFER: 1073741824
  BU_PERCENT: 0
    BU_WARNS: 0
    FC_COUNT: 0
    BC_COUNT: 0
...

列描述:

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 where processor=? and front_id=? and host=? and user=?

show @@connection where processor=? and front_id=? and host=? and user=?;

描述:查询前端连接信息,可通过processor,front_id,host和user进行过滤筛选,条件可以任意组合搭配。

例:


mysql> show @@connection where processor='frontProcessor4' \G
*************************** 1. row ***************************
            PROCESSOR: frontProcessor4
             FRONT_ID: 4
                 HOST: 192.168.2.190
                 PORT: 9066
           LOCAL_PORT: 52082
                 USER: man1
               SCHEMA:
 CHARACTER_SET_CLIENT: utf8mb4
 COLLATION_CONNECTION: utf8mb4_general_ci
CHARACTER_SET_RESULTS: utf8mb4
               NET_IN: 1438
              NET_OUT: 10925
        ALIVE_TIME(S): 526
          RECV_BUFFER: 32767
           SEND_QUEUE: 0
           RECV_QUEUE: 0
   TX_ISOLATION_LEVEL:
           AUTOCOMMIT:
        SYS_VARIABLES:
       USER_VARIABLES:
                XA_ID: -
1 row in set (0.01 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: 发送缓冲区队列里的任务数量
RECV_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 where processor=? and backend_id=? and mysqlid=? and host=? and port=?

show @@backend where processor=? and backend_id=? and mysqlid=? and host=? and port=?;

描述:查询活动的后端连接信息,可与show @@session结合使用。该命令可通过processor,backend_id,mysqlid,host和port进行过滤筛选,条件可以任意组合搭配。

例:

mysql> show @@backend where processor='backendProcessor9' and host='172.18.0.3' \G
*************************** 1. row ***************************
            processor: backendProcessor9
           BACKEND_ID: 29
              MYSQLID: 26
                 HOST: 172.18.0.3
                 PORT: 3306
       LOCAL_TCP_PORT: 34848
               NET_IN: 93
              NET_OUT: 85
       ACTIVE_TIME(S): 699
               CLOSED: false
                STATE: IDLE
           SEND_QUEUE: 0
               SCHEMA: NULL
 CHARACTER_SET_CLIENT: utf8mb4
 COLLATION_CONNECTION: utf8mb4_general_ci
CHARACTER_SET_RESULTS: utf8mb4
   TX_ISOLATION_LEVEL: 2
           AUTOCOMMIT: true
        SYS_VARIABLES:
       USER_VARIABLES:
            XA_STATUS: 0
            DEAD_TIME:
   USED_FOR_HEARTBEAT: false
1 row in set (0.01 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: 连接池被回收的时间,连接在完成任务后也会关闭回收
USED_FOR_HEARTBEAT: 是否被用于心跳

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                   | STAGE     |
+----------+-----------------+------+--------+---------------------+--------------+-----------------------+-----------+
|    1     | 0:0:0:0:0:0:0:1 | man  | NULL   | 2017/10/17 17:00:58 |          139 | show @@connection.sql | Read 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
STAGE: 运行的当前阶段,结束时会变成finished

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;

描述:展示dbinstance的heartbeat信息

例:

mysql> show @@heartbeat;
+--------+--------------+------+---------+-------+--------+---------+--------------+------------------+-------+-------------+
| NAME   | HOST         | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME | STOP  | RS_MESSAGE  |
+--------+--------------+------+---------+-------+--------+---------+--------------+------------------+-------+-------------+
| hostM1 | 10.18x.2x.63 | 3320 |      OK |     0 | idle   |       0 | 8,8,8        | NULL             | false | NULL        |
| hostM2 | 10.18x.2x.64 | 3320 |      OK |     0 | idle   |       0 | 9,9,9        | NULL             | false | NULL        |
+--------+--------------+------+---------+-------+--------+---------+--------------+------------------+-------+-------------+
2 rows in set (0.07 sec)

列描述:

NAME:dbGroup名称
HOST:主机名/IP
PORT:端口
RS_CODE:状态码,有以下四种状态:INIT, OK, ERROR, TIMEOUT
RETRY:重试错误次数
STATUS:checking/idle
TIMEOUT:心跳超时阈值(来源于db.xml中heartbeat子元素timeout的值,默认为0)
EXECUTE_TIME:最近3个时段的平均响应时间,默认1,10,30分钟
LAST_ACTIVE_TIME:上次收到心跳回复时间戳
STOP:是否stop,和stop命令相关
RS_MESSAGE:心跳失败信息,当RS_CODE为INIT, OK, TIMEOUT时,message为null,只有当RS_CODE为ERROR时,message才会显示最近一次心跳失败的信息

2.1.3.27 show @@heartbeat.detail where name=?

show @@heartbeat.detail where name=xxx;

其中,xxx为要查询的dbinstance的名字。

描述:展示指定dbinstance的heartbeat的详细信息
前提条件:至少发生过一次心跳语句(与shardingNodeHeartbeatPeriod相关)

例:

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:dbGroup名称
HOST:主机名/IP
PORT:端口
TIME:收到心跳时间戳
EXECUTE_TIME:心跳执行耗时(毫秒)

2.1.3.28 show @@sysparam

show @@sysparam;

描述:展示sysconfig参数配置
结果:略

2.1.3.29 show @@white

show @@white;

描述:展示配置的白名单信息

例:

mysql> show @@white;
+-----------------+------+
| IP              | USER |
+-----------------+------+
| 0:0:0:0:0:0:0:1 | root |
| 127.0.0.1       | root |
| 0:0:0:0:0:0:0:1 | test |
| 127.0.0.1       | test |
+-----------------+------+
4 rows in set (0.00 sec)

列描述:

2.1.3.30 show @@directmemory

show @@directmemory;

描述:堆外内存使用总览
结果集举例:

+---------------------+-------------------------+-------------------------+
| DIRECT_MEMORY_MAXED | DIRECT_MEMORY_POOL_SIZE | DIRECT_MEMORY_POOL_USED |
+---------------------+-------------------------+-------------------------+
| 3GB                 | 1024MB                  | 44KB                    |
+---------------------+-------------------------+-------------------------+
1 row in set (0.16 sec)

结果列描述 :

DIRECT_MEMORY_MAXED:通过-XX:MaxDirectMemorySize设置的值
DIRECT_MEMORY_POOL_SIZE: 内存池的大小,等于bufferPoolPageSize和bufferPoolPageNumber的乘积
DIRECT_MEMORY_POOL_USED:已经使用的内存池中的DirectMemory内存

2.1.3.31 show @@command.count

show @@command.count;

描述:查询当前系统的查询数;

结果:略

2.1.3.32 show @@connection.count

show @@connection.count;

描述:查询当前的前端链接数;

结果:略

2.1.3.33 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.34 show @@backend.old

show @@backend.old;

描述:reload @@config_all之后待回收的活动的后端链接信息
结果格式:同show @@backend

2.1.3.35 show @@binlog.status

show @@binlog.status;

描述:对被分库分表(sharding.xml)使用的mysql节点拉一条一致性的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.36 show @@help

show @@help;

描述:展示帮助信息
结果:略

2.1.3.37 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.38 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.39 show @@cost_time;

show @@cost_time;

描述:查询query耗时统计的结果,需要在bootstrap.cnf中开启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.40 show @@shardingNodes where schema=? and table=?;

show @@shardingNodes

描述:查询某具体表格的节点信息

例:

mysql> show @@shardingNodes 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.41 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.42 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.43 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.44 show @@processlist;

show @@processlist;

描述:查看前端连接和后端连接对应关系,若前端连接没有对应的后端连接,显示NULL。

例:

mysql> show @@processlist;
+----------+--------------+---------+------+-----------------+------+---------+------+-------+------+
| Front_Id | db_instance  | MysqlId | User | Front_Host      | db   | Command | Time | State | Info |
+----------+--------------+---------+------+-----------------+------+---------+------+-------+------+
|        1 | instanceM2   |    2303 | root | 127.0.0.1:33222 | db2  | Sleep   |   17 |       | NULL |
|        2 | instanceM2   |    NULL | man1 | 127.0.0.1:34882 | NULL | NULL    |    0 |       | NULL |
|        3 | instances2   |    2259 | root | 127.0.0.1:33226 | db1  | Sleep   |    4 |       | NULL |
|        3 | instanceS2   |    2308 | root | 127.0.0.1:33226 | db2  | Sleep   |    4 |       | NULL |
|        3 | instanceS2   |    2304 | root | 127.0.0.1:33226 | db1  | Sleep   |    4 |       | NULL |
+----------+--------------+---------+------+-----------------+------+---------+------+-------+------+
5 rows in set (0.05 sec)

行描述:

Front_Id:前端连接ID  
db_instance:前端连接下发操作的实例名  
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.45 show @@session.xa;

show @@session.xa;

描述:查看后台重试的xa事务信息。

例:

mysql> show @@session.xa;
+----------+--------------------+-------------------------+------------------+
| FRONT_ID | XA_ID              | XA_STATE                | SHARDING_NODES   |
+----------+--------------------+-------------------------+------------------+
| 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事务状态
SHARDING_NODES:xa提交失败的shardingNode名称

2.1.3.46 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 |No Cluster| RELOAD_ALL  | NOT_RELOADING | 2020/06/19 14:28:04 | 2020/06/19 14:28:05 | LOCAL_COMMAND | RELOAD_END |
+-------+----------+-------------+---------------+---------------------+---------------------+---------------+------------+

行描述:

INDEX:reload对应的编号,能与日志中的[RL]日志编号相对应
CLUSTER:当前dble使用的集群方式
RELOAD_TYPE:最近的reload的类型 RELOAD_ALL/RELOAD_META/MANAGER_INSERT/MANAGER_UPDATE/MANAGER_DELETE
RELOAD_STATUS:最近一次reload的执行状状态not_reloading/self_reload/meta_reload/waiting_others
LAST_RELOAD_START:起始时间
LAST_RELOAD_END:结束时间
TRIGGER_TYPE:触发类型  LOCAL_COMMAND/CLUSTER_NOTIFY
END_TYPE:结束原因 RELOAD_END/INTERRUPUTED

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

2.1.3.47 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.48 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:删除权限位

2.1.3.49 show @@data_distribution where table ='schema.table'

show @@data_distribution where table ='schema.table'

描述:查看某个表在各个节点上的数据分布情况

举例

+---------------+-------+
| SHARDING_NODE | COUNT |
+---------------+--------
| dn1           | 100   |
| dn2           | 101   |
| dn3           | 98    |
| dn4           | 104   |
+---------------+-------+
4 rows in set (0.09 sec)

行描述:

SHARDING_NODE:数据结点名字
COUNT:数据量

2.1.3.50 show @@Questions

show @@Questions

描述:查看自启动之后SQL服务端口执行的QUERY和Transaction数量

举例:

mysql> show @@Questions;
+-----------+--------------+
| Questions | Transactions |
+-----------+--------------+
|         0 |            0 |
+-----------+--------------+

行描述:

Questions:收到的查询的数量
Transactions:执行事务的数量(非事务查询算单语句事务)

Transactions统计规则:

  1. 非事务中执行sql,只要有响应结果都参与统计; 事务中,执行含有隐式提交语句报错1064、1046错误码,不参与统计
  2. 事务中,执行exit(隐式rollback)表示事务结束,参与统计
  3. set多个变量时,如'set autocommit=0,autocommit=n,xxxxx,xxxxx;' 一律取最右边的autocommit=n,根据实际场景实际处理(若无autocommit=n则视为普通sql处理)
  4. sharding中执行'set xa = on/off/1/0'语句和rwsplit中执行的'XA start/end/prepare/commit/rollback XXX'都被视为普通sql
  5. rwsplit中执行multi-query(指一次执行多个sql,mysql client可使用delimiter关键字实现),multi-query将会直接透传至后端节点,这里会被视作为事务级sql(如commit),参与统计(实际上Transactions只+1,Questions只+1)

2.1.3.51 show @@connection_pool

show @@connection_pool

描述:查看后端连接池的各种属性

举例:

mysql> show @@connection_pool;
+----------+-------------+-------------------------------+--------+
| DB_GROUP | DB_INSTANCE | PROPERTY                      | VALUE  |
+----------+-------------+-------------------------------+--------+
| dbGroup1 |  instanceM1 | minCon                        |      2 |
| dbGroup1 |  instanceM1 | maxCon                        |      4 |
| dbGroup1 |  instanceM1 | testOnCreate                  |  false |
| dbGroup1 |  instanceM1 | testOnBorrow                  |  false |
| dbGroup1 |  instanceM1 | testOnReturn                  |  false |
| dbGroup1 |  instanceM1 | testWhileIdle                 |  false |
| dbGroup1 |  instanceM1 | connectionHeartbeatTimeout    |     20 |
| dbGroup1 |  instanceM1 | connectionTimeout             |  10000 |
| dbGroup1 |  instanceM1 | heartbeatPeriodMillis         |  10000 |
| dbGroup1 |  instanceM1 | idleTimeout                   | 600000 |
| dbGroup1 |  instanceM1 | evictorShutdownTimeoutMillis  |  10000 |
| dbGroup1 |  instanceM1 | timeBetweenEvictionRunsMillis |  30000 |
+----------+-------------+-------------------------------+--------+
12 rows in set (0.01 sec)

行描述:

DB_GROUP:dbinstance所属DB_GROUP
DB_INSTANCE:dbinstance名
PROPERTY:属性名
VALUE:属性值

2.1.3.52 show @@cap_client_found_rows

show @@cap_client_found_rows

描述:查看cap_client_found_rows权能标志

举例:

mysql> show @@cap_client_found_rows;
+-------------------------+
| @@cap_client_found_rows |
+-------------------------+
| 0                       |
+-------------------------+
1 row in set (0.02 sec)

值描述:

0-关闭,1-开启

2.1.3.53 show @@general_log

show @@general_log

描述:查看general相关信息

举例:

mysql> show @@general_log;
+------------------+---------------------------------------+
| NAME             | VALUE                                 |
+------------------+---------------------------------------+
| general_log      | ON                                    |
| general_log_file | /tmp/dble-general/general/general.log |
+------------------+---------------------------------------+
2 rows in set (0.02 sec)

值描述:

general_log:关闭/启用
general_log_file:显示general日志的绝对路径

2.1.3.54 show @@statistic;

show @@statistic;

描述:查看sql statistic相关信息

举例:

mysql> show @@statistic;
+-----------------------------------------+-------+
| NAME                                    | VALUE |
+-----------------------------------------+-------+
| statistic                               | OFF   |
| associateTablesByEntryByUserTableSize   | 1024  |
| frontendByBackendByEntryByUserTableSize | 1024  |
| tableByUserByEntryTableSize             | 1024  |
| samplingRate                            | 0     |
| sqlLogTableSize                         | 1024  |
| queueMonitor                      | monitoring  |
+-----------------------------------------+-------+
6 rows in set (0.01 sec)

值描述:

statistic:关闭/启用
associateTablesByEntryByUserTableSize:sql_statistic_by_associate_tables_by_entry_by_user表格大小
frontendByBackendByEntryByUserTableSize:sql_statistic_by_frontend_by_backend_by_entry_by_user表格大小
tableByUserByEntryTableSize:sql_statistic_by_table_by_user_by_entry表格大小  
samplingRate:采样统计的采样率,采样率为0的话表示关闭采样统计。采样率是[0,100]之间的整数,单位是 %。
sqlLogTableSize:sql_log 表格大小
queueMonitor:观测队列状态

2.1.3.55 show @@load_data.fail;

show @@load_data.fail;

描述:load data批处理模式下查询本次失败的文件

举例:

show @@load_data.fail;
Empty set (0.01 sec)

if have error file may like 
show @@load_data.fail;
+----------------------------------+
| error_load_data_file             |
+----------------------------------+
| ./temp/error/1-data-table-dn1.txt |
| ./temp/error/1-data-table-dn2.txt |
+----------------------------------+
2 rows in set (0.01 sec)

值描述:

error_load_data_file:错误文件地址

2.1.3.56 show @@statistic_queue.usage;

show @@statistic_queue.usage;

描述:查看队列的使用率情况列表(观测期间,每次查询结果递增)

举例:

show @@statistic_queue.usage;
+---------------------+-------+
| TIME                | USAGE |
+---------------------+-------+
| 2021-05-31 16:33:30 | 0.00% |
| 2021-05-31 16:33:35 | 0.00% |
| 2021-05-31 16:33:40 | 0.00% |
+---------------------+-------+
3 rows in set (0.01 sec)

值描述:

TIME:采样时间点
USAGE:使用率

results matching ""

    No results matching ""