php mycat 读写分离,MyCAT读写分离以及参数调配

MyCat的说明文档请参见

主要使用到得几个配置文件有schema.xml、rule.xml、server.xml

MYCAT_HOME/conf/schema.xml 中定义逻辑库，表、分片节点等内容.

MYCAT_HOME/conf/rule.xml 中定义分片规则.

MYCAT_HOME/conf/server.xml 中定义用户以及系统相关变量，如端口等.

假设有如下几个数据库，arp库是a库的复制库，brp库是b库的复制库，需要搭建成mycat模式，配置成单个实例模式，同时配置成读写分离模式

mysqldatabasetable

a.mysql.com.cnt_database1-4t_table

arp.mysql.com.cnt_database1-4t_table

b.mysql.com.cnt_database5-8t_table

brp.mysql.com.cnt_database5-8t_table

mycatdatabasetable

mycat.mysql.com.cnt_databaset_table

schema.xml配置读写分离数据库，并定义读写分离的模式

[envuser@node1 conf]$ more schema.xmlselect user()select user()

rule.xml定义读写规则

site_idpartStr8site_idpartStr81664:2568128:256

server.xml配置，定义mycat的数据库名称、用户名、密码等参数

passwordt_database

默认分配的账户是具备读写权限的账户

开启MyCat

[envuser@localhost conf]$ cd ~/mycat/bin/

[envuser@localhost bin]$ ./mycat start

关闭MyCat

[envuser@localhost conf]$ cd ~/mycat/bin/

[envuser@localhost bin]$ ./mycat stop

查看MyCat状态

[envuser@localhost conf]$ cd ~/mycat/bin/

[envuser@localhost bin]$ ./mycat status

Mycat-server is running (8320).

如果只想给MyCat分配一个只读账户，可以通过配置server.xml来实现,重启MyCat，使配置生效

passwordt_databasetrue

MyCat的log都存放在logs目录下，可以通过查找logs目录找到对应的log,默认的log级别是info，可以通过调整~/mycat/conf/log4j2.xml调整日志的级别和日志的格式，如果MyCat无法正常开启，可以通过查找该日志来定位原因，修改日志的配置文件，重启MyCat使配置生效

[envuser@ip-10-21-8-46 conf]$ more log4j2.xml%d{yyyy-MM-dd HH:mm:ss.SSS} %5p [%t] (%l) - %m%n-->-->-->-->

大体数据库架构如上面所示，由于以a.mysql.com.cn和arp.mysql.com.cn，这两个数据库通过mycat配置成读写分离，但是发现复制库的cpu长时间处于100%状态，且该数据库的Read的QPS明显要高于Write的QPS，而主数据库的CPU长期处于空闲状态，针对该现象做原因分析

该数据库由4个分库，每个分库中只有1张表，但是表最高的数据行数达到了1800W行((⊙﹏⊙)b)，通过修改代码将每一条SQL的耗时打印到日志中观察发现，用户的使用行为中针对该数据库的Wirie操作较少，且写的数据库操作耗时较短，属于正常现象，但是Read操作较为频繁，通过分析日志发现，Read的操作最长耗时竟然达到了20s，这也是为什么用户使用起来觉得查询慢的原因，由于查询不断堆积，导致cpu长时间100%

通过日志分析定位出查询时间较长的SQL，发现有几个SQL的查询非常慢，需要20s以上。将该SQL通过MyCat执行，确实需要花费20s以上的时间，验证日志无异常；

由于担心MyCat的查询规则导致查询慢，通过在MyCat explain该语句，定位到需要执行该语句的数据库，直接在该数据库上执行该语句，发现依然需要20s以上，也就是说该查询的慢跟MyCat没有关系

mysql>SELECTglobal_idFROMt_tableWHEREwarn_type='102'ANDchild_warn_type='102003'ANDsite_id='xxxxxxx'ANDdevice_id='xxxxxx'ANDpoint_id='INV.State'ANDcategory='22'ANDOCCUR_TIME_UTC'2017-07-01 00:00:00'ORDERBYOCCUR_TIMEDESCLIMIT1;+----------------------------------+|global_id|+----------------------------------+|xxxxxxxx|+----------------------------------+1rowinset(25.80sec)

通过在数据库上单独explain该查询用户，获取执行过程中hit到的索引，发现该查询查找了数百万行的数据，同时explain的结果显示，并没有hit到以前的索引，所以导致查询慢，并通过分析所有慢的查询，通过综合分析索引和查询的条件，发现以前的索引已经不符合现有的使用情况；

mysql>explainSELECTglobal_idFROMt_tableWHEREwarn_type='102'ANDchild_warn_type='102003'ANDsite_id='xxxxxxxx'ANDdevice_id='xxxxxxx'ANDpoint_id='INV.State'ANDcategory='22'ANDOCCUR_TIME_UTC'2017-07-01 00:00:00'ORDERBYOCCUR_TIMEDESCLIMIT1;mysql>showindexfromt_table;+------------------+------------+----------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality|Sub_part|Packed|Null|Index_type|Comment|Index_comment|+------------------+------------+----------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+|t_table|0|PRIMARY|1|id|A|8669108|NULL|NULL||BTREE||||t_table|0|PRIMARY|2|occur_time|A|8669108|NULL|NULL||BTREE||||t_table|1|t_table_global_id_index|1|global_id|A|8669108|NULL|NULL||BTREE||||t_table|1|t_table_index|1|site_id|A|7833|NULL|NULL|YES|BTREE||||t_table|1|t_table_index|2|occur_time_utc|A|6831715|NULL|NULL|YES|BTREE||||t_table|1|t_table_index|3|device_id|A|8669108|NULL|NULL||BTREE||||t_table|1|t_table_index|4|point_id|A|8669108|NULL|NULL|YES|BTREE||||t_table|1|t_table_index|5|warn_type|A|8669108|NULL|NULL|YES|BTREE||||t_table|1|t_table_index|6|child_warn_type|A|8669108|NULL|NULL|YES|BTREE|||+------------------+------------+----------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+9rowsinset(0.00sec)mysql>

决定重构索引，将查询的字段根据查询的内容重构索引，并将老的索引删除，重启MyCat后，该查询正常，经过冲过股索引后，已经可以解决大部分查询慢的问题，但是还有若干查询很奇怪，每次查询的耗时很长20s以上，但是查询的频率非常高，基本上市1s一次，这样的查询在某个时间段内一直出现，且该查询可以hit到正常的索引，也就是说他是可以正常的查询，但是这样的查询行为是很怪异的

经过定位查询语句和查询逻辑找到了查询的调用方，经过检查发现，由于调用方的配置错误，导致了这样的异常查询调用，要求调用方整改后，基本上已经没有了长时间的CPU非常高的情况，说明用户的调用都已经开始恢复正常

但是每天都会有一段时间复制库的cpu达到100%，持续时间在1h左右，经过定位分析这是用户的正常使用逻辑，之所以CPU 100%,是由于业务方每天定时调用，每次调用的数据量较大，完成这样的大量查询需要1h才能把所有的查询执行完成，在这段时间内复制库的cpu是100%的，但是Master数据库的cpu却一直长期处于低领用率状态

既然不能要求业务方该，那就只能从数据库这方面修改了，由于索引的利用价值已经不高，在不增加成本的情况下，相当一个方案是，将读写分离的架构调整成为，主库写/读，复制库读的模式，将复制库的读压力部分的转移到主库上来，可以分担一部分的压力

通过查询MyCat的schema.xml配置，发现dataHost的blance配置可以满足我们这样的需求，balance的具体配置如下：

balance 属性

负载均衡类型，目前的取值有 3 种：

1. balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上。

2. balance="1"，全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双

主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，正常情况下，M2,S1,S2 都参与 select 语句的负载

均衡。

3. balance="2"，所有读操作都随机的在 writeHost、readhost 上分发。

4. balance="3"，所有读请求随机的分发到 wiriterHost 对应的 readhost 执行，writerHost 不负担读压

力，注意 balance=3 只在 1.4 及其以后版本有，1.3 没有。

通过修改balance的值=2，重启MyCat使配置生效，后面观察发现主数据库的Read的IOPS明显上升，同时副本数据库的IOPS明显下降，说明该配置已经生效，复制数据库的CPU明显下降，再也没有打到100%的情况

虽然我也不推荐使用balance=2的模式，但是在成本控制的时候，可以使用，当然了有更好的方案，比如再增加一个副本数据库，如arp2.mysql.com.cn，同时按照如下配置，将从数据库的读写分担，也是可以实现这样的压力分担的，但是MySQL数据库的瓶颈上限是1000W行，当数据量超过1000W行时，查询等操作会明显有瓶颈，应当考虑其他的存储方式，如HBase等

[envuser@node1 conf]$ more schema.xmlselect user()select user()