集群比对+haproxy

总：集群的选择By writer:需要注意的地方一.介绍项目地址： /doc/percona-xtradb-cluster/intro.htmlPercona XtraDB Cluster是MySQL高可用性和可扩展性的解决方案.Percona XtraDB Cluster提供的特性有：1.同步复制，事务要么在所有节点提交或不提交。2.多主复制，可以在任意节点进行写操作。3.在从服务器上并行应用事件，真正意义上的并行复制。4.节点自动配置。5.数据一致性，不再是异步复制。Percona XtraDB Cluster完全兼容MySQL和Percona Server，表现在：1.数据的兼容性2.应用程序的兼容性：无需更改应用程序1.集群是有节点组成的，推荐配置至少3个节点，但是也可以运行在2个节点上。2.每个节点都是普通的mysql/percona服务器，可以将现有的数据库服务器组成集群，反之，也可以将集群拆分成单独的服务器。3.每个节点都包含完整的数据副本。优点如下：1.当执行一个查询时，在本地节点上执行。因为所有数据都在本地，无需远程访问。2.无需集中管理。可以在任何时间点失去任何节点，但是集群将照常工作。3.良好的读负载扩展，任意节点都可以查询。缺点如下：1.加入新节点，开销大。需要复制完整的数据。2.不能有效的解决写缩放问题，所有的写操作都将发生在所有节点上。3.有多少个节点就有多少重复的数据。架构图如下：Percona XtraDB Cluster与MySQL Replication区别在于：分布式系统的CAP理论。C-一致性，所有节点的数据一致。A-可用性，一个或多个节点失效，不影响服务请求。P-分区容忍性，节点间的连接失效，仍然可以处理请求。任何一个分布式系统，需要满足这三个中的两个。MySQL Replication: 可用性和分区容忍性Percona XtraDB Cluster: 一致性和可用性因此MySQL Replication并不保证数据的一致性，而Percona XtraDB Cluster提供数据一致性。Percona XtraDB Cluster组件：Percona XtraDB Cluster基于XtraDB的Percona Server以及包含写复制集补丁。使用Galera 2.x library，事务型应用下的通用的多主同步复制插件。Galera 2.x新特性有：1.IST(Incremental State Transfer)增量状态传输。对于WAN特别有用。2.RSU(Rolling Schema Update)旋转更新架构。不会阻止对表进行操作。二.初始配置为了使用XtraDB集群，需要在f文件中配置以下选项：wsrep_provider - a path to Galera library.wsrep_cluster_address - cluster connection URL.binlog_format=ROWdefault_storage_engine=InnoDBinnodb_autoinc_lock_mode=2innodb_locks_unsafe_for_binlog=1# 额外的参数有：wsrep_slave_threads #指定线程数量wsrep_sst_method4.安装XtraBackup SST方法为了使用Percona XtraBackup的State Transfer method(节点间数据的快照副本拷贝)。可以使用支持Galera信息的脚本的正式的xtrabackup包，可以从innobackupex源码包中得到innobackupex脚本。同时在f文件中制定：wsrep_sst_method=xtrabackup三.局限性 仅Innodb1.目前的复制仅仅支持InnoDB存储引擎。任何写入其他引擎的表，包括mysql.*表将不会复制。但是DDL语句会被复制的，因此创建用户将会被复制，但是insert into mysql.user.将不会被复制的。2.DELETE操作不支持没有主键的表。没有主键的表在不同的节点顺序将不同，如果执行SELECT.LIMIT. 将出现不同的结果集。3.在多主环境下LOCK/UNLOCK TABLES不支持。以及锁函数GET_LOCK(), RELEASE_LOCK().4.查询日志不能保存在表中。如果开启查询日志，只能保存到文件中。5.允许最大的事务大小由wsrep_max_ws_rows和wsrep_max_ws_size定义。任何大型操作将被拒绝。如大型的LOAD DATA操作。6.由于集群是乐观的并发控制，事务commit可能在该阶段中止。如果有两个事务向在集群中不同的节点向同一行写入并提交，失败的节点将中止。对于集群级别的中止，集群返回死锁错误代码(Error: 1213 SQLSTATE: 40001 (ER_LOCK_DEADLOCK).7.XA事务不支持，由于在提交上可能回滚。8.整个集群的写入吞吐量是由最弱的节点限制，如果有一个节点变得缓慢，那么整个集群将是缓慢的。为了稳定的高性能要求，所有的节点应使用统一的硬件。9.集群节点建议最少3个。10.如果DDL语句有问题将破坏集群。四集群内部机制写入数据id自增递增比如集群中三台可能是3 6 9这样类似递增五刚做好这个集群每一个节点的日志都要保留时间长点最好是每个节点都做日志备份，数据备份防止哪一个节点退出集群但是数据库没有挂掉然后haproxy还是在分发这样的数据不一致解决起来没有比对十分麻烦使用toad for mysql进行比对那时，总而言之言而总之这个集群坑还是不少的一.Mariadb集群+haproxy+keepalived作者：注意sysbench5.0 版本与 sysbench4.2版本测试的结果将会大不相同这里是同环境作比较1.安装mariadb集群1.服务器环境如下：1颗双核 xeon e5-2680的CPU rootzyz_dba_test01 # head -4 /proc/meminfo MemTotal: 1922464 kBMemFree: 79904 kBBuffers: 159296 kBCached: 1366644 kB2G不到的内存2.系统环境如下Red Hat Enterprise Linux Server release 6.4 (Santiago)3. 集群版本mariadb-galera-10.0.17-linux-x86_64.tar.gzHaproxyHaproxy是一个反向代理负载均衡解决方案，支持4层和7层模式，提供后端服务器健康检查，非常稳定。淘宝前期也使用Haproxy作为CDN系统负载均衡器haproxy版本：1.4.254. ip地址06 node07 node08 node3 09 haproxy配置到每台的/etc/hosts文件中5. 安装1.node1 node2 node3 执行以下操作： ln -sf /usr/lib64/libssl.so.10 /usr/lib64/libssl.so.6 ln -sf /usr/lib64/libcrypto.so.10 /usr/lib64/libcrypto.so.6 2.文件传达一个目录如/usr/local/sc 在3台上解压tar -xf /usr/local/src/mariadb-galera-10.0.17-linux-x86_64.tar.gzmv /usr/local/src/mariadb-galera-10.0.17-linux-x86_64 /usr/local/mysqlcd mysql/scriptsmkdir /data/dbuseradd mariadbchown mariadb:mariadb /data/db./mysql_install_db -datadir=/data/db/ -basedir=/usr/local/mysql/ -user=mariadb以下分别的node1 node2 node3的配置文件 cat /usr/local/mysql/f /etc/f /etc/f HHclient#password = your_passwordport = 3306socket = /tmp/mysql.sockmysqldport = 3306user=mariadbsocket = /tmp/mysql.sockskip-external-lockingkey_buffer_size = 16Kmax_allowed_packet = 1Mtable_open_cache = 4sort_buffer_size = 64Kread_buffer_size = 256Kread_rnd_buffer_size = 256Knet_buffer_length = 2Kthread_stack = 240Kdatadir=/data/db max_connections=1000 log-bin=mysql-binbinlog_format = ROWserver-id = 3innodb_autoinc_lock_mode = 2wsrep_provider = /usr/local/mysql/lib/libgalera_smm.sowsrep_cluster_name = my_mariadb_clusterwsrep_cluster_address=gcomm:/06,07wsrep_cluster_name=example_clusterwsrep_node_name = cluster_node3wsrep_node_address = 08:4406wsrep_sst_auth=tt:123wsrep_node_name=node3wsrep_sst_method=rsyncmysqldumpquickmax_allowed_packet = 16Mmysqlno-auto-rehashmyisamchkkey_buffer_size = 8Msort_buffer_size = 8Mmysqlhotcopyinteractive-timeoutHH顺序启动数据库Node2和node3上（node1不需要）cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld Node1: /usr/local/mysql/bin/mysqld -defaults-file=/usr/local/mysql/f -wsrep-new-clusterNode2:service mysqld restartNode3:serivce mysqld restart测试在任意一台创建删除数据均会同步6. 压力测试CREATE TABLE test.sbtest ( id int(10) unsigned NOT NULL AUTO_INCREMENT, k int(10) unsigned NOT NULL DEFAULT 0, c char(120) NOT NULL DEFAULT , pad char(60) NOT NULL DEFAULT , PRIMARY KEY (id), KEY k (k) ENGINE=InnoDB;create table test.sbtest1 select * from test.sbtest; create table test.sbtest2 select * from test.sbtest;create table test.sbtest3 select * from test.sbtest;create table test.sbtest4 select * from test.sbtest;create table test.sbtest5 select * from test.sbtest;create table test.sbtest6 select * from test.sbtest;create table test.sbtest7 select * from test.sbtest;create table test.sbtest8 select * from test.sbtest;create table test.sbtest9 select * from test.sbtest;create table test.sbtest10 select * from test.sbtest;create database sbtest;create table sbtest.sbtest select * from test.sbtest;create table sbtest.sbtest1 select * from test.sbtest;create table sbtest.sbtest2 select * from test.sbtest;create table sbtest.sbtest3 select * from test.sbtest;create table sbtest.sbtest4 select * from test.sbtest;create table sbtest.sbtest5 select * from test.sbtest;create table sbtest.sbtest6 select * from test.sbtest;create table sbtest.sbtest7 select * from test.sbtest;create table sbtest.sbtest8 select * from test.sbtest;create table sbtest.sbtest9 select * from test.sbtest;create table sbtest.sbtest10 select * from test.sbtest;安装sysbenchrootzyz_dba_test02#wget /redir/downloads/Percona-XtraDB-Cluster/5.5.37-25.10/RPM/rhel6/x86_64/Percona-XtraDB-Cluster-shared-55-5.5.37-25.10.756.el6.x86_64.rpmrootzyz_dba_test02#yum -y install Percona-XtraDB-Cluster-shared-55-5.5.37-25.10.756.el6.x86_64.rpm rootzyz_dba_test02 # rpm -ivh sysbench-0.5-2.el6_.x86_64.rpm Preparing. # 100% 1:sysbench # 100%至此安装完毕sysbench -test=/usr/share/doc/sysbench/tests/db/select.lua -mysql-table-engine=innodb -oltp-table-size=1000000 -max-requests=0 -max-time=60 -num-threads=100 -oltp-tables-count=10 -report-interval=1 -mysql-host=08 -mysql-port=3306 -mysql-user=root -mysql-password=123 -mysql-db=test run测试下并发800的情况下的QPS 读CPU 已经使用load 43.19由上图可以看出基本上在以上服务器环境只读能达到14015.38CPU占用负载0.3的时候也就是正常值的时候平均值不到5000 rootzyz_dba_test03 # sysbench -test=/usr/share/doc/sysbench/tests/db/select.lua -mysql-table-engine=innodb -oltp-table-size=1000000 -max-requests=0 -max-time=60 -num-threads=1 -oltp-tables-count=10 -report-interval=1 -mysql-host=08 -mysql-port=3306 -mysql-user=root -mysql-password=123 -mysql-db=test runsysbench 0.5: multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 1Report intermediate results every 1 second(s)Random number generator seed is 0 and will be ignoredThreads started! 1s threads: 1, tps: 0.00, reads/s: 4603.85, writes/s: 0.00, response time: 0.34ms (95%) 2s threads: 1, tps: 0.00, reads/s: 5050.07, writes/s: 0.00, response time: 0.28ms (95%) 3s threads: 1, tps: 0.00, reads/s: 5080.91, writes/s: 0.00, response time: 0.28ms (95%) 4s threads: 1, tps: 0.00, reads/s: 4807.06, writes/s: 0.00, response time: 0.28ms (95%) 5s threads: 1, tps: 0.00, reads/s: 4794.00, writes/s: 0.00, response time: 0.29ms (95%) 6s threads: 1, tps: 0.00, reads/s: 5018.65, writes/s: 0.00, response time: 0.28ms (95%) 7s threads: 1, tps: 0.00, reads/s: 5119.29, writes/s: 0.00, response time: 0.28ms (95%) 8s threads: 1, tps: 0.00, reads/s: 4835.05, writes/s: 0.00, response time: 0.30ms (95%) 9s threads: 1, tps: 0.00, reads/s: 4900.95, writes/s: 0.00, response time: 0.30ms (95%) 10s threads: 1, tps: 0.00, reads/s: 4981.05, writes/s: 0.00, response time: 0.28ms (95%) 11s threads: 1, tps: 0.00, reads/s: 5090.04, writes/s: 0.00, response time: 0.27ms (95%) 12s threads: 1, tps: 0.00, reads/s: 5091.90, writes/s: 0.00, response time: 0.27ms (95%) 13s threads: 1, tps: 0.00, reads/s: 5085.02, writes/s: 0.00, response time: 0.28ms (95%) 14s threads: 1, tps: 0.00, reads/s: 5111.03, writes/s: 0.00, response time: 0.28ms (95%) 15s threads: 1, tps: 0.00, reads/s: 5062.60, writes/s: 0.00, response time: 0.29ms (95%) 16s threads: 1, tps: 0.00, reads/s: 5044.30, writes/s: 0.00, response time: 0.28ms (95%) 17s threads: 1, tps: 0.00, reads/s: 3402.04, writes/s: 0.00, response time: 0.28ms (95%) 18s threads: 1, tps: 0.00, reads/s: 5106.10, writes/s: 0.00, response time: 0.28ms (95%) 19s threads: 1, tps: 0.00, reads/s: 5096.99, writes/s: 0.00, response time: 0.27ms (95%) 20s threads: 1, tps: 0.00, reads/s: 4992.88, writes/s: 0.00, response time: 0.28ms (95%) 21s threads: 1, tps: 0.00, reads/s: 5110.09, writes/s: 0.00, response time: 0.28ms (95%) 22s threads: 1, tps: 0.00, reads/s: 5148.97, writes/s: 0.00, response time: 0.28ms (95%) 23s threads: 1, tps: 0.00, reads/s: 5077.03, writes/s: 0.00, response time: 0.28ms (95%) 24s threads: 1, tps: 0.00, reads/s: 5065.69, writes/s: 0.00, response time: 0.28ms (95%) 25s threads: 1, tps: 0.00, reads/s: 5071.30, writes/s: 0.00, response time: 0.28ms (95%) 26s threads: 1, tps: 0.00, reads/s: 5095.95, writes/s: 0.00, response time: 0.28ms (95%) 27s threads: 1, tps: 0.00, reads/s: 5141.07, writes/s: 0.00, response time: 0.27ms (95%) 28s threads: 1, tps: 0.00, reads/s: 5095.01, writes/s: 0.00, response time: 0.28ms (95%) 29s threads: 1, tps: 0.00, reads/s: 5130.99, writes/s: 0.00, response time: 0.28ms (95%) 30s threads: 1, tps: 0.00, reads/s: 5040.05, writes/s: 0.00, response time: 0.29ms (95%) 31s threads: 1, tps: 0.00, reads/s: 4992.92, writes/s: 0.00, response time: 0.29ms (95%) 32s threads: 1, tps: 0.00, reads/s: 4390.00, writes/s: 0.00, response time: 0.28ms (95%) 33s threads: 1, tps: 0.00, reads/s: 4949.77, writes/s: 0.00, response time: 0.28ms (95%) 34s threads: 1, tps: 0.00, reads/s: 4619.22, writes/s: 0.00, response time: 0.28ms (95%) 35s threads: 1, tps: 0.00, reads/s: 4519.03, writes/s: 0.00, response time: 0.28ms (95%) 36s threads: 1, tps: 0.00, reads/s: 5154.03, writes/s: 0.00, response time: 0.28ms (95%) 37s threads: 1, tps: 0.00, reads/s: 4816.98, writes/s: 0.00, response time: 0.28ms (95%) 38s threads: 1, tps: 0.00, reads/s: 5111.99, writes/s: 0.00, response time: 0.28ms (95%) 39s threads: 1, tps: 0.00, reads/s: 5146.03, writes/s: 0.00, response time: 0.27ms (95%) 40s threads: 1, tps: 0.00, reads/s: 5025.99, writes/s: 0.00, response time: 0.27ms (95%) 41s threads: 1, tps: 0.00, reads/s: 5105.00, writes/s: 0.00, response time: 0.27ms (95%) 42s threads: 1, tps: 0.00, reads/s: 5053.86, writes/s: 0.00, response time: 0.28ms (95%) 43s threads: 1, tps: 0.00, reads/s: 4647.15, writes/s: 0.00, response time: 0.29ms (95%) 44s threads: 1, tps: 0.00, reads/s: 5068.91, writes/s: 0.00, response time: 0.29ms (95%) 45s threads: 1, tps: 0.00, reads/s: 5105.05, writes/s: 0.00, response time: 0.28ms (95%) 46s threads: 1, tps: 0.00, reads/s: 5094.96, writes/s: 0.00, response time: 0.27ms (95%) 47s threads: 1, tps: 0.00, reads/s: 5066.08, writes/s: 0.00, response time: 0.27ms (95%) 48s threads: 1, tps: 0.00, reads/s: 4955.88, writes/s: 0.00, response time: 0.27ms (95%) 49s threads: 1, tps: 0.00, reads/s: 4237.05, writes/s: 0.00, response time: 0.28ms (95%) 50s threads: 1, tps: 0.00, reads/s: 4958.96, writes/s: 0.00, response time: 0.29ms (95%) 51s threads: 1, tps: 0.00, reads/s: 4997.98, writes/s: 0.00, response time: 0.29ms (95%) 52s threads: 1, tps: 0.00, reads/s: 4984.12, writes/s: 0.00, response time: 0.29ms (95%) 53s threads: 1, tps: 0.00, reads/s: 4818.93, writes/s: 0.00, response time: 0.28ms (95%) 54s threads: 1, tps: 0.00, reads/s: 5099.00, writes/s: 0.00, response time: 0.28ms (95%) 55s threads: 1, tps: 0.00, reads/s: 5079.00, writes/s: 0.00, response time: 0.28ms (95%) 56s threads: 1, tps: 0.00, reads/s: 5119.94, writes/s: 0.00, response time: 0.27ms (95%) 57s threads: 1, tps: 0.00, reads/s: 4636.10, writes/s: 0.00, response time: 0.29ms (95%) 58s threads: 1, tps: 0.00, reads/s: 5023.99, writes/s: 0.00, response time: 0.28ms (95%) 59s threads: 1, tps: 0.00, reads/s: 5041.95, writes/s: 0.00, response time: 0.29ms (95%) 60s threads: 1, tps: 0.00, reads/s: 4971.98, writes/s: 0.00, response time: 0.29ms (95%)OLTP test statistics: queries performed: read: 297036 write: 0 other: 0 total: 297036 transactions: 0 (0.00 per sec.) deadlocks: 0 (0.00 per sec.) read/write requests: 297036 (4950.56 per sec.) other operations: 0 (0.00 per sec.)General statistics: total time: 60.0005s total number of events: 297036 total time taken by event execution: 59.5268s response time: min: 0.10ms avg: 0.20ms max: 333.52ms approx. 95 percentile: 0.28msThreads fairness: events (avg/stddev): 297036.0000/0.00execution time (avg/stddev): 59.5268/0.002个线程的时候：sysbench -test=/usr/share/doc/sysbench/tests/db/select.lua -mysql-table-engine=innodb -oltp-table-size=1000000 -max-requests=0 -max-time=90 -num-threads=2 -oltp-tables-count=10 -report-interval=1 -mysql-host=08 -mysql-port=3306 -mysql-user=root -mysql-password=123 -mysql-db=test run后面我又做了mysql5.6.24的压测同一个环境 安装的mysql在3307端口结果证明还是mysql5.6.24的tps 和qps更加的高一些Qps相差还不是太大（使用的是sysbench0.5版本的 0.4版本的话mariadb 测试的tps还是和0.5版本有比较大的区别的因此这里的话是同一环境下mariadb10.0.17集群和mysql5.6.24的单实例的比较回头会安装上mysql5.6.24集群的情况下再做压测比较） TPS：rootzyz_dba_test03 # sysbench -test=/usr/share/doc/sysbench/tests/db/oltp.lua -oltp_tables_count=9 -oltp-table-size=10 -rand-init=on -num-threads=1000 -oltp-read-only=off -report-interval=1 -rand-type=gaussian -max-time=3000 -max-requests=0 -mysql-host=08 -mysql-port=3307 -mysql-user=root -mysql-password=123 runsysbench 0.5: multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 1000Report intermediate results every 1 second(s)Initializing random number generator from timer.Random number generator seed is 0 and will be ignoredThreads started! 1s threads: 1000, tps: 0.00, reads/s: 11258.02, writes/s: 7.00, response time: 0.00ms (95%) 2s threads: 1000,