Mycat的读写分离 2019-09-15 程序之旅 暂无评论 860 次阅读 ## Mycat的读写分离 ### 产生数据库性能瓶颈的原因 #### 数据库连接数 数据在连接数过多时，就会出现“Too many connecttions”的错误，出现这种情况无非以下几种情况：1. 网站访问量太大；2. 数据库最大连接数设置太小；3. 没有正确的关闭数据库连接。 mysql 默认链接数是100个 最大是16384，可以通过以下的语句来查询最大的连接数(mysql)，最大的连接数可以在my.ini的配置文件中修改(max_connections) ``` show variables like 'max_connections'; # 最大连接数 ``` #### 表数据量大 单表数据量过大会导致数据库查询效率的降低。网上也有对mysql的单表数据规模的测试，分为1万、10万、百万和千万的数据。 MyISAM引擎本地查询操作平均执行时间（单位：微秒） | 项目记录数 | 单表select | 双表select | insert | update | 单表delete | 双表delete | | :--------: | :--------: | ---------- | ------ | ------ | ---------- | ---------- | | 1万条 | 178 | 247 | 694 | 141 | 170 | 345 | | 10万条 | 195 | 271 | 705 | 157 | 186 | 585 | | 100万条 | 3055 | 5164 | 746 | 4308 | 12373 | 22533 | | 1000万条 | 8665 | 15259 | 803 | 11033 | 17224 | 35485 | > 发现对innodb的配置参数进行优化后，更新和插入速度可以提高10倍多。 InnoDB引擎本地单次查询操作平均执行时间（单位：微秒） | 项目记录数 | 单表select | 双表select | insert | update | 单表delete | 双表delete | | :--------: | :--------: | ---------- | ------ | ------ | ---------- | ---------- | | 1万条 | 180 | 264 | 709 | 165 | 156 | 3125 | | 10万条 | 226 | 340 | 754 | 204 | 205 | 590 | | 100万条 | 1853 | 4530 | 746 | 1782 | 2771 | 9816 | | 1000万条 | 9275 | 17609 | 889 | 6941 | 14727 | 36282 | 该测试也会受内存和磁盘文件大小等外界因素的影响，仅从测试数据看出当数据量达到100万条的时候就该考虑对数据进行优化。 > PS：在我过去的工作中，如果一分钟一条数据，10台设备，一分钟10条，一天1万4千条，3个月就能达到百万数据，现在单表应该有达到百万条数据 。。。 > 影响索引的因素： > 1. 不命中 > 2. 硬盘级索引，磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分 #### 硬件资源(qts/tps) 硬件资源所影响的每秒事务数(tps)和每秒查询数(QPS)的因素有CPU（配置、使用率等）、内存（占用率等）、磁盘（I/O、页交换等） ### 数据性能优化方案 可以从以下的几个方案来对数据库进行优化： 1. sql优化 2. 缓存 3. 建立索引 4. 读写分离 5. 分库分表 > 后两种的解决大数量的解决方案。 ### 设计需求 实现两个逻辑库db_user和db_store的操作，db_user中存在数据字典(data_dictionary)与有E-R关系的users和user_address的表；db_store中包含员工表employee和商品表store。 #### 实现功能 - db_store主要实现数据库的读写分离操作 - db_user主要实现数据的分片操作 以下是逻辑图：  #### window 中使用mycat 在官网中下载稳定版本1.6，[下载地址](http://dl.mycat.io/1.6-RELEASE/) 解压后找到bin目录，打开shell命令行窗口，之后先install一下，之后才start，就能启动程序。 ``` .\mycat install .\mycat start ``` mycat最主要的是conf目录， server.xml、schema.xml和rule.xml三个文件分别用于配置数据库、节点和分片表等。 #### 需求实现 ##### 开发环境 - 一个本地mysql数据库 localhost - 一个ubuntu 18虚拟机数据库 192.168.124.18 - windows10 上的 mycat > 其中localhost对192.168.124.18的数据库db_store进行读写分离，localhsot是master 首先，分别在两个不同的mysql中配置db_store的主从复制，主从复制的实现之前已经说过，之后就是db_user的分片节点。如下是schema.xml配置文件的所有内容： ##### schema.xml ```xml <?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="db_store" checkSQLschema="false" sqlMaxLimit="100"> <table name="store" dataNode="db_store_dataNode" primaryKey="storeID"/> <table name="employee" dataNode="db_store_dataNode" primaryKey="employeeID"/> </schema> <schema name="db_user" checkSQLschema="false" sqlMaxLimit="100"> <table name="data_dictionary" type="global" dataNode="db_user_dataNode1, db_user_dataNode2" primaryKey="dataDictionaryID"/> <table name="users" dataNode="db_user_dataNode$1-2" rule="mod-userID-long" primaryKey="userID"> <childTable name="user_address" joinKey="userID" parentKey="userID" primaryKey="addressID"/> </table> </schema> <dataNode name="db_store_dataNode" dataHost="db_storeHOST" database="db_store" /> <dataNode name="db_user_dataNode1" dataHost="db_userHOST1" database="db_user" /> <dataNode name="db_user_dataNode2" dataHost="db_userHOST2" database="db_user" /> <dataHost name="db_storeHOST" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="hostM1" url="localhost:3306" user="root" password="root"> <readHost host="hostS1" url="192.168.124.18:3306" user="root" password="root"/> </writeHost> </dataHost> <dataHost name="db_userHOST1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="userHost1" url="localhost:3306" user="root" password="root" /> </dataHost> <dataHost name="db_userHOST2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="userHost1" url="192.168.124.18:3306" user="root" password="root" /> </dataHost> </mycat:schema> ``` 简单的说一下配置 #### dataHost--节点主机 定义分片的主机实例，说白了就是给dataNode分配的物理数据库，例如上边的分的三个分片节点`db_store_dataNode`、`db_user_dataNode1`和`db_user_dataNode2`分别属于`db_storeHOST`、`db_userHOST1`和`db_userHOST2`这三个实例数据库，这三个实例数据库指向物理机的数据库(ip和port)。 > 如果心细的人会看到dataHost节点中的balance属性的值不同 > > - balance="0" : 不开启读写分离机制，所有的读操作都发送到当前可以用的writeHost上 > > - balance="1" : 全部的readHost与stand by writeHost（双主从模式下的master） 都参与select语句的负债均衡 > > - balance="2" : 所有的读操作都随机的往writeHost和readHost上分发 > - balance="3" : 所有的读分发到readHost上，writeHost负责写 #### dataNode-分片节点 定义了mycat中的数据节点，这也就是我们通常所说的数据分片，一个单独的dataNode就是一个独立的数据分片。dataNode能够定义该分片所属的逻辑库，也就是schema的database，定义该分片所属的逻辑数据库上的具体数据库。 #### schema-逻辑库 从应用层的角度来看，逻辑库就是一个独立的数据库，而逻辑库可能是一个或者多个数据库构造而成。 之后是配置他们的分片规则，分片规则有很多的算法，这里先使用最简单的取余来进行数据分片，rule.xml配置添加配置项如下： ##### rule.xml ```xml <tableRule name="mod-userID-long"> <rule> <columns>userID</columns> <algorithm>mod-long</algorithm> </rule> </tableRule> <function name="mod-long" class="io.mycat.route.function.PartitionByMod"> <!-- how many data nodes --> <property name="count">2</property> </function> ``` > function这里修改为2，表示分片节点为2 最后是设置逻辑库连接，在server.xml中添加： ```xml <user name="root"> <property name="password">123456</property> <property name="schemas">db_store,db_user</property> </user> ``` 配置完成后重启mycat ```shell .\mycat restart ```  --- ### 2019年11月24日 ## Mycat的linux安装方法 可以到Mycat的官网中下载linux版的压缩包。我这里下载的是 1.6.5的版本。 上传文件到云端服务器，使用以下的方法解压文件。 ```shell tar -zxvf Mycat-server-1.6.5-release-20180122220033-linux.tar.gz ``` #### 配置文件介绍： | 文件 | 说明 | | ---------- | ------------------------------------- | | server.xml | Mycat的配置文件，设置账号、参数等 | | schema.xml | Mycat对应的物理数据库和数据库表的配置 | | rule.xml | Mycatt分片(分库分表)规则 | 之后来到bin目录启动mycat 报如下错误 ### 1、没有装jdk ``` STATUS | wrapper | 2019/11/23 22:59:36 | Launching a JVM... ERROR | wrapper | 2019/11/23 22:59:36 | Unable to start JVM: No such file or directory (2) ERROR | wrapper | 2019/11/23 22:59:36 | JVM exited while loading the application. ``` #### 解决方法 这里就安装下载Linux版本的jdk安装就好了。 ```shell # /etc/profile export JAVA_HOME=/usr/java/jdk1.8.0_231 export JRE_HOME=${JAVA_HOME}/jre export CLASSPATH=.:${JAVA_HOME}/lib:${JRE_HOME}/lib:$CLASSPATH export JAVA_PATH=${JAVA_HOME}/bin:${JRE_HOME}/bin export PATH=$PATH:${JAVA_PATH} ``` ### 2、内存不够 ``` INFO | jvm 5 | 2019/11/23 23:34:53 | Java HotSpot(TM) 64-Bit Server VM warning: INFO: os::commit_memory(0x0000000715550000, 715849728, 0) failed; error='Cannot allocate memory' (errno=12) ``` #### 解决方法 在conf/wrapper.conf中设置所需内存，原本需要的内存太大，我这里服务器内存只有1G，所以启动不了。 ``` wrapper.java.additional.10=-Xmx1G wrapper.java.additional.11=-Xms256M ``` ### 3、mycat异常（1146 table xxx doesn,t exist） 这里还需要配置主从数据库。 [Mysql-主从复制](https://blog.mufeng.info/index.php/archives/135/) 打赏: 微信, 支付宝 标签: mycat, 读写分离, 主从复制 本作品采用 知识共享署名-相同方式共享 4.0 国际许可协议 进行许可。
微信
支付宝