mysql实施方案

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.2Mysql的安装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/myp.cnf。其中—datadir为数据目录，--defaults-file为配置文件。2.3Mysql的配置参数[mysqld]#basedir=/disk1/weblogic/lcecs7/mysql-5.6.14-linux-glibc2.5-x86_64basedir=/home/upay/Percona-Server-5.6.14-rel62.0-483.Linux.x86_64datadir=/home/upay/mysql/dataport=33306socket=/home/upay/mysql/mysql.sock#lower_case_table_names=1#binlog_cache_size=4M#max_heap_table_size=128M#sort_buffer_size=16Minnodb_additional_mem_pool_size=128Minnodb_buffer_pool_size=48Ginnodb_file_io_threads=4innodb_flush_log_at_trx_commit=1[mysqld_safe]log-error=/home/upay/mysql/mysqld.logpid-file=/home/upay/mysql/mysqld.pid2.4MySQL服务器的性能2.4.1简单性能测试Mysqlslap指令：./mysqlslap-h10.20.38.11-P33306-ucobar-pcobar-a-x5-y5-i5--number-of-queries=5000-c50（5000简单语句，50并发）测试结果：在50并发执行情况下平均每个语句执行耗时0.2秒；2.4.2Select语句的性能(复杂SQL、简单SQL查询性能)测试用sql语句：SELECTPO.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_VALUE,DU.SELL_LOGID,TL.CUSTOM_IDFROMTF_B_PAY_ORDER4PO,TL_B_DELIVER_CARDCHARGE4DC,TL_B_DELIVER_UNICARD4DU,TF_B_TRADE_LOG4TLWHEREPO.ORDER_NO=000000000000000000000000067890ANDPO.ORDER_NO=DC.ORDER_NOANDPO.ORDER_NO=DU.ORDER_NOANDPO.ORDER_NO=TL.ORDER_NOLIMIT1;Mysqlslap指令：./mysqlslap-h10.20.38.11-P33306-ucobar-pcobar-qSELECTPO.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_VALUE,DU.SELL_LOGID,TL.CUSTOM_IDFROMTF_B_PAY_ORDER4PO,TL_B_DELIVER_CARDCHARGE4DC,TL_B_DELIVER_UNICARD4DU,TF_B_TRADE_LOG4TLWHEREPO.ORDER_NO=000000000000000000000000067890ANDPO.ORDER_NO=DC.ORDER_NOANDPO.ORDER_NO=DU.ORDER_NOANDPO.ORDER_NO=TL.ORDER_NOLIMIT1;--create-schema=cobartest--no-drop-i5--number-of-queries=1000-c50（总执行1000遍该sql指令，并发数为50）每个表5000000条记录的情况下，运行时间为：每个表5000000条记录的情况下，使用mysqlslap测试结果：每个表对order_no字段建立索引以后使用mysqlslap测试结果：测试结果：在50并发执行下，500万记录表的平均查询语句的执行时间是0.23秒。2.4.3Insert/delete/update语句的性能Insert测试的Mysqlslap语句：./mysqlslap-h10.20.38.11-P33306-ucobar-pcobar-qinsertintotf_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-i1--number-of-queries=200000-c50测试结果：在50并发执行的情况下每个插入语句需要0.003014秒（有问题，每次插入都是相同的记录，没有主键、索引的情况可以执行；有主键的情况下会报错。需要写个测试脚本，每次插入不相同的记录。）Update测试的Mysqlslap语句：./mysqlslap-h10.20.38.11-P33306-ucobar-pcobar-qupdatetf_b_trade_log4setCUSTOM_ID=123whereorder_no=000000000000000000000000000000;--create-schema=cobartest--no-drop-i5--number-of-queries=5-c5测试结果：500000条表数据，并发5次执行5条update需要27.711秒。由于update时数据加锁，所以无法并发。平均单条update执行时间是5.542。（有问题，因为每次都是对同一条记录更新，对该记录加了记录级锁，因此不能并发更新。另外，是否对order_no加了索引？现在测试结果太差。）3MySQL主-主结构部署3.1半同步双向复制设置1．在配置文件中加入log-bin=mysql-binserver-id=12replicate-do-db=cobartestauto-increment-increment=2auto-increment-offset=12．启动主服务器的半同步复制功能：mysqlINSTALLPLUGINrpl_semi_sync_masterSONAME'semisync_master.so';setglobalrpl_semi_sync_master_enabled=1;setglobalrpl_semi_sync_master_timeout=1000;(1秒,默认为10秒)3．启动从服务器或多个从服务器的半同步复制功能：mysqlINSTALLPLUGINrpl_semi_sync_slaveSONAME'semisync_slave.so';setglobalrpl_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：显示从服务器确认的不成功提交数量Rpl_semi_sync_master_tx_avg_wait_time：事务因开启Semi_sync，平均需要额外等待的时间Rpl_semi_sync_master_net_avg_wait_time：事务进入等待队