OneProxy :: 监控所有流量,提供比数据库后端更丰富更多维度的实时性能数据!

由于所有访问数据库的流量都经过OneProxy中间件,使得非常适合进行多个维度的性能数据实时统计,比如说根据查询SQL语句、表名、客户端IP地址等不同维度来做实时汇总。

oneproxy_performance_schema

OneProxy中的实时性能数据,可以用MySQL客户端工具或程序(比如PHP等)登录到管理端口来进行查询。比如使用“list sqltext”命令来获得所有执行过的SQL语句(在SQL防火墙功能介绍时已经用过),所有的字符串和数字都会被替换成问号进行归类。

mysql> list sqltext;
+------------+---------------------------------------------------------+
| HASHCODE   | SQLTEXT                                                 |
+------------+---------------------------------------------------------+
| 4091520238 | select * from my_list where id in (? , ? , ?)           |
| 1751540541 | select * from my_hash where id in (? , ? , ?)           |
| 1561995348 | show tables                                             |
......
+------------+---------------------------------------------------------+
14 rows in set (0.01 sec)

执行“list dmltext”命令可以列出所有的数据库更新语句,所有的字符串和数字都会被替换成问号进行归类。

mysql> list dmltext;
+------------+---------------------------------------------------------+
| HASHCODE   | SQLTEXT                                                 |
+------------+---------------------------------------------------------+
| 1463889950 | insert into my_hash(id , col2 , col3) values(? , ? , ?) |
| 2191039147 | update my_list set col2 = col2 + ? where id = ?         |
|  832761468 | insert into my_list(id , col2 , col3) values(? , ? , ?) |
+------------+---------------------------------------------------------+
3 rows in set (0.00 sec)

或者执行“list sqlstats”命令来获得每个SQL的执行次数、平均用时、返回的记录数等十分有用的信息。

mysql> list sqlstats;
+------------+-------+-----------------------+------+--------+--------+--------+------+--------+------+
| HASHCODE   | EXECS | DIST                  | FAIL | ELAPSE | AVGELA | MAXELA | ROWS | AVGROW | LAST |
+------------+-------+-----------------------+------+--------+--------+--------+------+--------+------+
| 1259447424 |     1 | 0,0,0,0,0,0,0,0,0,99  |    0 |     21 |     21 |     21 |    0 |      0 | 2020 |
| 2364317242 |     1 | 0,0,99,0,0,0,0,0,0,0  |    0 |      2 |      2 |      2 |    0 |      0 | 2019 |
| 1463889950 |    36 | 83,8,0,0,0,0,0,0,0,8  |    0 |    155 |      4 |     57 |   36 |      1 | 2275 |
......
14 rows in set (0.00 sec)

OneProxy中也提供了按表名统计增删改查四类操作次数的功能,执行“list tabstats”命令就可以了,省去了DBA从SQL中分析出表名再进行累计的工作。

mysql> list tabstats;
+-----------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| TABLE     | INSERT | INSROW | INSTIM | UPDATE | UPDROW | UPDTIM | DELETE | DELROW | DELTIM | SELECT | SELROW | SELTIM |
+-----------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| my_range  |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |     16 |      0 |    106 |
| my_range2 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |     16 |      0 |     87 |
| my_hash   |     36 |     36 |    155 |      0 |      0 |      0 |      0 |      0 |      0 |     52 |     97 |     26 |
| my_list   |     36 |     36 |     19 |     20 |     16 |      6 |      0 |      0 |      0 |     76 |    124 |     12 |
+-----------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
4 rows in set (0.00 sec)

或者运行“list ipstats”来查看每个客户端(用IP地址进行归类)的数据库请求情况,包括了登录、查询、非法SQL命令及网络流量等多方信息,可以用来辅助识别非法的客户端程序。

mysql> list ipstats;
+-----------+------+------+------+-------+-------+--------+--------+------+--------+--------+--------+--------+-------+--------+
| ADDRESS   | CONN | AUTH | FAIL | QUERY | ERROR | DBTIME | DBROWS | DENY | FWFAIL | SQLBAD | SQLDIS | SQLSEC | NETIN | NETOUT |
+-----------+------+------+------+-------+-------+--------+--------+------+--------+--------+--------+--------+-------+--------+
| 127.0.0.1 |    6 |    6 |    0 |  5670 |     0 |    240 |    331 |    0 |      0 |      1 |      0 |      0 | 88620 |  29772 |
+-----------+------+------+------+-------+-------+--------+--------+------+--------+--------+--------+--------+-------+--------+
1 row in set (0.00 sec)

在管理端口中运行“list help”命令可以得到所有可以在管理端口中执行的命令,你可以编写脚本将性能数据定期同步到你的监控中心数据库,与现有的监控系统进行集成展示。