品優(yōu)購項目10配套軟件進階mycat

1、Mycat+percona_clusterMyCat 1.2支持percona_cluster的集群模式，即多節(jié)點同時讀寫，犧牲了數(shù)據(jù)插入的性能而換來高可用性和讀的性能，下面是測試報告。軟件版本:Mycat: 1.2Percona XtraDB Cluster: 5.6.15-25.3Wsrep :25.3.r4034系統(tǒng):服務器:華為Tecal RH2288 V2-8SCpu: Intel(R) Xeon(R) CPU E5-2620processor : 24Mem: 24GDisk: Scsi Raid10架構(gòu): 測試場景正常環(huán)境下三個數(shù)據(jù)庫同時寫入測試登錄mycatmysql -h8

2、-utest -ptest -P8066 TESTDB創(chuàng)建表create table travelrecord (id bigint not null ,user_id varchar(100),traveldate DATETIME, fee decimal,days int);C.1測試（400并發(fā)）./test_stand_insert_perf.sh jdbc:mysql:/8:8066/TESTDB test test 400 0-500M結(jié)果C.2 300并發(fā)./test_stand_insert_perf.sh jdbc:mysql:/8:8066/TESTDB test tes

3、t 300 0-500M200并發(fā)./test_stand_insert_perf.sh jdbc:mysql:/8:8066/TESTDB test test 200 0-500M100并發(fā)./test_stand_insert_perf.sh jdbc:mysql:/8:8066/TESTDB test test 100 0-500M場景2，三個節(jié)點在高并發(fā)中宕掉一個節(jié)點./test_stand_insert_perf.sh jdbc:mysql:/8:8066/TESTDB test test 400 0-500M第二次測試400并發(fā)實際數(shù)據(jù)存在部分數(shù)據(jù)沒有寫入的情況，此情況為檢測節(jié)點失

4、效過程中的時延導致，需要程序?qū)QL執(zhí)行失敗的數(shù)據(jù)進行處理，比如重新執(zhí)行，或則寫入文件中，事后分析和執(zhí)行。查詢測試./test_stand_select_perf.sh jdbc:mysql:/8:8066/TESTDB test test 100 1000 5000000./test_stand_select_perf.sh jdbc:mysql:/8:8066/TESTDB test test 200 1000 5000000./test_stand_select_perf.sh jdbc:mysql:/8:8066/TESTDB test test 300 1000 5000000./t

5、est_stand_select_perf.sh jdbc:mysql:/8:8066/TESTDB test test 400 1000 5000000./test_stand_select_perf.sh jdbc:mysql:/8:8066/TESTDB test test 500 1000 5000000Mycat 配置#cat schema.xml select user() #檢測mysql狀態(tài),自己可以定制sql 注意: writeType是設置多主的,0為當主宕掉后會到第二個節(jié)點寫入(默認為0),1為同時向三個節(jié)點寫入數(shù)據(jù).Mysql_cluster配置cat f client

6、socket= /usr/local/mysql/data/mysql.sockmysqldwait_timeout=28800skip-host-cacheskip-name-resolve#innodbsocket= /usr/local/mysql /data/mysql.sockdatadir= /usr/local/mysql /datauser=mysqlwsrep_provider= /usr/local/mysql/lib/libgalera_smm.sobasedir= /usr/local/mysql#wsrep_cluster_address=gcomm:/,binlog

7、_format=ROWdefault_storage_engine=InnoDBinnodb_locks_unsafe_for_binlog=1innodb_autoinc_lock_mode=2#add new parametersinnodb_buffer_pool_size=15Ginnodb_buffer_pool_instances=4innodb_additional_mem_pool_size=20Minnodb_log_buffer_size=16Minnodb_flush_log_at_trx_commit=0innodb_support_xa=0innodb_file_pe

8、r_table=1innodb_data_file_path=ibdata1:100M:autoextendinnodb_read_io_threads=64innodb_write_io_threads=64innodb_io_capacity=5000innodb_log_file_size=512Minnodb_thread_concurrency=0innodb_file_format=Barracudainnodb_flush_method=O_DIRECTinnodb_stats_on_metadata=0#wsrep#5.5.to update 5.6#wsrep_provide

9、r_options=socket.checksum=1#log_bin_use_v1_row_events=1#gtid_mode=0#binlog_checksum=NONE#read_only=ON#wsrep_certify_nonPK=1wsrep_debug=0wsrep_convert_LOCK_to_trx=0wsrep_max_ws_rows=131072wsrep_max_ws_size=1073741824wsrep_retry_autocommit=1wsrep_auto_increment_control=1wsrep_node_address=4wsrep_node_

10、name=node1#wsrep_sst_method=rsyncwsrep_sst_method=xtrabackupwsrep_drupal_282555_workaround=0wsrep_causal_reads=0wsrep_slave_threads=48max_connections=2000wsrep_cluster_name=my_centos_cluster#wsrep_debug=on#wsrep_mysql_replication_bundle=200wsrep_provider_options=socket.checksum = 1; pc.ignore_quorum = true; pc.weight=0; gcache.size=8G; evs.keepalive_period=PT3S; evs.inactive_check_period=PT10S; evs.suspect_timeou