2022年mysql实施方案 .pdf

1 总体结构2*Cobar+8*(1 主+1 主)结构2 单 mysql 服务器部署2.1 服务器的系统配置硬件：16 台 PC服务器， （CPU 数）/64G 内存，2*300G 本地盘， RAID0+1，可用存储空间 300G。软件：操作系统： RHEL6。MySQL 数据库： Percona最新的版本，当前是5.6.14。基准测试工具： SysBench ，模拟应用场景提供基准性能测试数据；压力测试工具：mysqldap， 指定并发用户、并发数、操作语句等测试 mysql服务器的内存、 CPU、IO 的性能；2.2 Mysql的安装1 Mysql 使用 Percona的 5.6.14。2 下载 Percona-Server-5.6.14-rel62.0-483.Linux.x86_64.tar.gz 包，解压。3 初始化 mysql，使用 ./scripts/mysql_install_db 脚本初始化 mysql，使用命令./scripts/mysql_install_db -datadir=/home/upay/mysql/data/ -defaults-file=/home/upay/mysql/f。其中 datadir 为数据目录，-defaults-file 为配置文件。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 18 页 - - - - - - - - - 2.3 Mysql的配置参数mysqld #basedir=/disk1/weblogic/lcecs7/mysql-5.6.14-linux-glibc2.5-x86_64 basedir=/home/upay/Percona-Server-5.6.14-rel62.0-483.Linux.x86_64 datadir=/home/upay/mysql/data port=33306 socket = /home/upay/mysql/mysql.sock #lower_case_table_names = 1 #binlog_cache_size = 4M #max_heap_table_size = 128M #sort_buffer_size = 16M innodb_additional_mem_pool_size = 128M innodb_buffer_pool_size = 48G innodb_file_io_threads = 4 innodb_flush_log_at_trx_commit = 1 mysqld_safe log-error=/home/upay/mysql/mysqld.log pid-file=/home/upay/mysql/mysqld.pid 2.4 MySQL 服务器的性能2.4.1 简单性能测试Mysqlslap 指令：./mysqlslap -h10.20.38.11 -P33306 -ucobar -pcobar -a -x5 -y5 -i 5 -number-of-queries=5000 -c50 （5000 简单语句， 50并发）名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 18 页 - - - - - - - - - 测试结果：在 50 并发执行情况下平均每个语句执行耗时0.2 秒；2.4.2 Select语句的性能 (复杂 SQL 、简单 SQL 查询性能 ) 测试用 sql 语句：SELECT PO.ORDER_NO, PO.ORDER_ID, PO.SERVICE_TYPE, PO.TOPAY_TOTAL_MONET, PO.COUPON_MONEY, PO.INCOME_TOTAL_MONEY, PO.PAY_TYPE, PO.CREATE_TIME,PO.NEED_INVOICE, PO.NEED_POST, PO.PAY_STATE, PO.HALT_TAG , PO.REFUND_TAG ,DC.BSS_PROC_NO, DC.BSS_TRADE_NO, DC.CREATE_TIME, DC.TELE_CODE,DC.PHONE_NO, DC.ACC_PROVINCE_CODE, DC.ACC_CITY_CODE, DC.ACC_BASE_TYPE, DC.ACC_SYSTEM, DC.BRAND_CODE,DC.NETYPE_CODE, DC.CARDCHARGE_MONEY, DC.DEDUCT_STATE, DC.BSS_TRADE_STATE, DC.CONN_CHANNEL, DC.CHANNEL_TYPE, DC.CONN_IP, DC.CONN_IP_V ALUE,DU.SELL_LOGID, TL.CUSTOM_ID FROM TF_B_PAY_ORDER4 PO, TL_B_DELIVER_CARDCHARGE4 DC, TL_B_DELIVER_UNICARD4 DU, TF_B_TRADE_LOG4 TL WHERE PO.ORDER_NO = 000000000000000000000000067890 AND PO.ORDER_NO = DC.ORDER_NO AND PO.ORDER_NO = DU.ORDER_NO AND PO.ORDER_NO = TL.ORDER_NO LIMIT 1; Mysqlslap 指令：./mysqlslap -h10.20.38.11 -P33306 -ucobar -pcobar -qSELECT PO.ORDER_NO, PO.ORDER_ID, PO.SERVICE_TYPE, PO.TOPAY_TOTAL_MONET, PO.COUPON_MONEY, PO.INCOME_TOTAL_MONEY, PO.PAY_TYPE, PO.CREATE_TIME,PO.NEED_INVOICE, PO.NEED_POST, PO.PAY_STATE, PO.HALT_TAG , PO.REFUND_TAG ,DC.BSS_PROC_NO, DC.BSS_TRADE_NO, 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 18 页 - - - - - - - - - DC.CREATE_TIME, DC.TELE_CODE,DC.PHONE_NO, DC.ACC_PROVINCE_CODE, DC.ACC_CITY_CODE, DC.ACC_BASE_TYPE, DC.ACC_SYSTEM, DC.BRAND_CODE,DC.NETYPE_CODE, DC.CARDCHARGE_MONEY, DC.DEDUCT_STATE, DC.BSS_TRADE_STATE, DC.CONN_CHANNEL, DC.CHANNEL_TYPE, DC.CONN_IP, DC.CONN_IP_VALUE,DU.SELL_LOGID, TL.CUSTOM_ID FROM TF_B_PAY_ORDER4 PO, TL_B_DELIVER_CARDCHARGE4 DC, TL_B_DELIVER_UNICARD4 DU, TF_B_TRADE_LOG4 TL WHERE PO.ORDER_NO = 000000000000000000000000067890 AND PO.ORDER_NO = DC.ORDER_NO AND PO.ORDER_NO = DU.ORDER_NO AND PO.ORDER_NO = TL.ORDER_NO LIMIT 1; -create-schema=cobartest -no-drop -i 5 -number-of-queries=1000 -c 50 （总执行1000 遍该 sql指令，并发数为50）每个表 5000000条记录的情况下，运行时间为：每个表 5000000条记录的情况下，使用mysqlslap测试结果：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 18 页 - - - - - - - - - 每个表对 order_no字段建立索引以后使用mysqlslap测试结果：测试结果：在50 并发执行下， 500 万记录表的平均查询语句的执行时间是0.23 秒。2.4.3 Insert/delete/update 语句的性能Insert测试的 Mysqlslap 语句：./mysqlslap -h10.20.38.11 -P33306 -ucobar -pcobar -qinsert into tf_b_pay_order4(ORDER_NO,SERVICE_TYPE,UDP_ORDER,PAY_ORG_CODE,PAY_ORG_ORDER,PAY_CHANNEL_CODE,PAY_CHANNEL_ORDER,PAY_ACUNT_NO,PAY_ACUNT_NAME,TOPAY_TOTAL_MONET,COUPON_MONEY,INCOME_TOTAL_MONEY,PAY_TYPE,CREATE_TIME,CONTRAST_TAG,CONTRAST_DATE,PAY_NOTIFY_TYPE,PAY_CREATE_TIME,PAY_COMPLETE_TIME,NEED_INVOICE,NEED_POST,CURRENCY_TYPE,PAY_STATE,PAY_REMARK,HALT_TAG,HALT_TIME,REFUND_TAG,REFUND_TIME,RESERVED1,RESERVED2,ORDER_ID) values(020000000000000000000000000002,06,NULL,NULL,NULL,NULL,NULL,NULL,NULL,10000,150,9850,1,123,NULL,NULL,NULL,123,NULL,1,1,01,0,123,0,NULL,0,NULL,NULL,NULL,0310121136010003); -create-schema=cobartest -no-drop -i 1 -number-of-queries=200000 -c 50 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 18 页 - - - - - - - - - 测试结果：在 50并发执行的情况下每个插入语句需要0.003014秒 （有问题，每次插入都是相同的记录，没有主键、索引的情况可以执行；有主键的情况下会报错。需要写个测试脚本，每次插入不相同的记录。）Update测试的 Mysqlslap 语句：./mysqlslap -h10.20.38.11 -P33306 -ucobar -pcobar -qupdate tf_b_trade_log4 set CUSTOM_ID=123 where order_no=000000000000000000000000000000; -create-schema=cobartest -no-drop -i 5 -number-of-queries=5 -c 5 测试结果： 500000条表数据，并发5 次执行 5 条 update需要 27.711秒。由于 update时数据加锁， 所以无法并发。平均单条update执行时间是 5.542。 （有问题，因为每次都是对同一条记录更新，对该记录加了记录级锁，因此不能并发更新。另外，是否对order_no加了索引？现在测试结果太差。 ）名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 18 页 - - - - - - - - - 3 MySQL 主-主结构部署3.1 半同步双向复制设置1 在配置文件中加入log-bin = mysql-bin server-id = 12 replicate-do-db = cobartest auto-increment-increment = 2 auto-increment-offset = 1 2 启动主服务器的半同步复制功能：mysql INSTALL PLUGIN rpl_semi_sync_master SONAME semisync_master.so; set global rpl_semi_sync_master_enabled=1; set global rpl_semi_sync_master_timeout=1000; (1 秒, 默认为 10 秒) 3 启动从服务器或多个从服务器的半同步复制功能：mysql INSTALL PLUGIN rpl_semi_sync_slave SONAME semisync_slave.so; set global rpl_semi_sync_slave_enabled=1; 3.2 复制实时监控命令在主服务器中：Rpl_semi_sync_master_status ：用于指示主服务器使用的是异步复制模式，还是半同步复制模式；Rpl_semi_sync_master_clients ： 用于显示有多少个从服务器配置成了半同步复制模式；Rpl_semi_sync_master_yes_tx ：显示从服务器确认的成功提交数量Rpl_semi_sync_master_no_tx ：显示从服务器确认的不成功提交数量名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 18 页 - - - - - - - - - Rpl_semi_sync_master_tx_avg_wait_time ：事务因开启Semi_sync，平均需要额外等待的时间Rpl_semi_sync_master_net_avg_wait_time ：事务进入等待队列后， 到网络平均等待时间依据上面两个状态值可以知道，Semi-sync 的网络消耗有多大，给某个事务带来的额外的消耗有多大。3.3 复制的性能3.3.1 批量复制的性能两台服务器配置主从。实验步骤：1. 从机停止 slave服务。2. 主机执行 10w 条 insert 语句并记录日志。3. 从机启动 slave服务，记录加载时间。结果： 10 万条记录需要 85 秒钟生效，平均每秒钟生效1176条。3.3.2 实时复制的性能用过 mysqlslap工具往主节点插入200000条数据，一共耗时 12.7秒，从节点需要在 46.4 秒时才能完全生效所有的主节点日志，达到数据的同步，平均每秒钟能生效 4310 条插入语句。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 18 页 - - - - - - - - - 4 Cobar 部署4.1 Cobar 的安装代理软件： Cobar 1.2.7 ；安装部署：1 软件准备MySQL: http:/ (推荐使用 5.6 以上版本 ) JDK: http:/ (推荐使用1.6 以上版本 ) Cobar: http:/ 2 下载 cobar 包并解压，在cobar根目录下的 conf 文件夹中包含了相应的配置文件配置参数：1 修改 rule.xml 2 修改 schema.xml 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 9 页，共 18 页 - - - - - - - - - 3 修改 server.xml 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 10 页，共 18 页 - - - - - - - - - 4.2 Cobar 的路由规则4.2.1 Cobar 路由函数的设计与实现（加上源代码）PartitionTest.java4.2.2 批量加载测试测试情况：用 mysql 客户端的 source命令加载 insert 语句，cobar自动插入到后台两个 mysql 服务器。脚本： insert.sql 测试数据：插入前在 cobar上统计计数：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 11 页，共 18 页 - - - - - - - - - 插入后在 cobar上统计计数：在服务器 0 上查询 order_no：在服务器 1 上查询 order_no：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 12 页，共 18 页 - - - - - - - - - 4.2.3 Insert/delete/update 测试Insert测试：通过 mysqlslap连接 cobar服务器测试插入性能，./mysqlslap -h10.40.32.12 -P38066 -utest -ptest -qinsert into tf_b_pay_order4(ORDER_NO,SERVICE_TYPE,UDP_ORDER,PAY_ORG_CODE,PAY_ORG_ORDER,PAY_CHANNEL_CODE,PAY_CHANNEL_ORDER,PAY_ACUNT_NO,PAY_ACUNT_NAME,TOPAY_TOTAL_MONET,COUPON_MONEY,INCOME_TOTAL_MONEY,PAY_TYPE,CREATE_TIME,CONTRAST_TAG,CONTRAST_DATE,PAY_NOTIFY_TYPE,PAY_CREATE_TIME,PAY_COMPLETE_TIME,NEED_INVOICE,NEED_POST,CURRENCY_TYPE,PAY_STATE,PAY_REMARK,HALT_TAG,HALT_TIME,REFUND_TAG,REFUND_TIME,RESERVED1,RESERVED2,ORDER_ID) values(020000000000000000000000000002,06,NULL,NULL,NULL,NULL,NULL,NULL,NULL,10000,150,9850,1,123,NULL,NULL,NULL,123,NULL,1,1,01,0,123,0,NULL,0,NULL,NULL,NULL,0310121136010003); -create-schema=cobartest -no-drop -i 5 -number-of-queries=10000 -c 50 测试结果：在 50 并发执行情况下，每条插入语句需要执行0.003114秒，和直连 mysql服务器的 0.003014秒相比，性能损失了 3.3%。每次都插入相同的记录值，导致都插入到一个mysql数据库，没有分片的效果。需要修改测试脚本。Update测试的 Mysqlslap 语句：./mysqlslap -h10.40.32.12 -P38066 -utest -ptest -qupdate tf_b_trade_log4 set CUSTOM_ID=123 where order_no=000000000000000000000000000000; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 13 页，共 18 页 - - - - - - - - - -create-schema=cobartest -no-drop -i 5 -number-of-queries=5 -c 5 测试结果： （我高度怀疑没加索引） 500000条数据并发 5 次执行 5 条 update需要 27.638 秒，由于 update 时数据加锁，所以无法并发。平均单条update执行时间是 5.528。4.3 Cobar 的查询性能在另外一台服务器上搭建cobar，直连 mysql 服务器，进行2.4 中 mysqlslap测试。在每个表 5000000条记录的情况下，直连cobar运行时间为：在每个表 5000000条记录的情况下，使用mysqlslap直连 cobar测试结果：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 14 页，共 18 页 - - - - - - - - - 和直连 mysql 相比，通过cobar 代理所需时间多了12 秒，性能损耗了3.7% 每个表对 order_no字段建立索引以后使用mysqlslap测试结果：测试结果：在 50 个并发执行情况下，对500 万记录表执行查询语句平均需要 0.24 秒，比起直连 mysql数据库 0.23秒，损失了 4%。4.4 故障自动切换在 cobar的 schema.xml文件中配置故障切换：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 15 页，共 18 页 - - - - - - - - - 定义 dataNode时可以定义多个dataSourceRef， cobar 会从上往下设置服务器访问的顺序，从第一台服务器开始测试心跳，超时以后尝试第二台，直到有一台连接成功为止。当当前连接的服务器心跳超时以后，cobar 会从新选择一个服务器连接，期间执行该dataNode 相关 sql会返回错误。Cobra缺省故障检查时间和切换时延：10 秒钟我 们 查 看 了 cobar 的 源 代 码 ， 该 设 置 在 com.alibaba.cobar.config.model. SystemConfig中，参数为 DEFAULT_CLUSTER_HEARTBEAT_TIMEOUT 。并未提供配置文件的配置方法。4.5 主从手工切换如何手工在主 -从节点之间切换：切换脚本：测试情况：4.6 Cobar 的负载均衡通过 F5设备在两台 Cobar 服务器之间进行负载均衡。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 16 页，共 18 页 - - - - - - - - - 5 Cobar 运行监控5.1 监控工具 Cacti 的安装包括 cacti和 percona提供的监控插件；5.2 Mysql性能参数的监控5.3 告警工具 Nagios的安装包括 nagios和 percona提供的告警插件5.4 Mysql告警事件的监控6 维护功能6.1 数据在线备份在从节点上执行数据备份任务；Percona提供的在线备份工具XtraBackup 的使用：全量备份、增量备份和恢复；名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 17 页，共 18 页 - - - - - - - - - 6.2 数据在线归档定期将生产库中的过期数据导出，保证生产数据库的高性能。在线归档工具： Percona pt-archiver ，用于在线将历史记录归档。6.3 在线修改表结构在主-主复制结构下，直接使用mysql 的 DDL 语句修改表结构会同步到另一个 mysql 服务器上，但直接使用mysql 的 DDL 存在锁表的问题。（据杨华说，mysql 新版本已经解决此问题，需要确认） 。也可以使用 percona提供的在线 shema修改工具 pt-online-schema-change ，可以在线不加锁地修改表结构， 但是否会破坏复制结构， 是否能复制到另一个mysql服务器，还是需要两个mysql 都执行该命令？名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 18 页，共 18 页 - - - - - - - - -