本站文章除注明转载外，均为本站原创： 转载自love wife & love life —Roger的Oracle&MySQL技术博客
本文链接地址: MySQL高可用方案之–PXC vs MGR
首先我们来安装部署好MGR，如下是MGR的简单配置步骤：
1. 初始化node1
/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql
2. 修改密码并创建复制用户
mysql> set password=password('enmotech');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
mysql> set SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> create user rep@'%' identified by 'enmotech';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to rep@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> set SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='rep', MASTER_PASSWORD='enmotech' FOR CHANNEL 'group_replication_recovery'
-> ;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql>
mysql> install plugin group_replication soname 'group_replication.so';
Query OK, 0 rows affected (0.03 sec)
mysql> set global group_replication_single_primary_mode=off;
Query OK, 0 rows affected (0.00 sec)
mysql> start group_replication;
Query OK, 0 rows affected (3.42 sec)
3. 其他节点分别执行:
set SQL_LOG_BIN=0; create user rep@'%' identified by 'enmotech'; grant replication slave on *.* to rep@'%'; flush privileges; set SQL_LOG_BIN=1; CHANGE MASTER TO MASTER_USER='rep', MASTER_PASSWORD='enmotech' FOR CHANNEL 'group_replication_recovery'; install plugin group_replication soname 'group_replication.so'; set global group_replication_single_primary_mode=off; set global group_replication_allow_local_disjoint_gtids_join=ON; start group_replication;
4. 每个节点的参数配置如下：
[client] port = 3306 socket = /tmp/mysqld.sock [mysqld] port = 3306 #basedir = /usr/local/mysql datadir = /opt/mysql/data/ socket = /tmp/mysqld.sock character-set-server = UTF8 default-storage-engine = InnoDB lower_case_table_names = 1 user = mysql open_files_limit = 102400 #sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" #explicit_defaults_for_timestamp = true symbolic-links = 0 skip-external-locking skip-slave-start server_id = 111 master_info_repository = TABLE relay_log_info_repository = TABLE log_bin = binlog #============================= Network =============================== back_log = 50 max_connections = 800 max_user_connections = 0 max_connect_errors = 999999999 net_buffer_length = 8K max_allowed_packet = 64M wait_timeout = 388000 interactive_timeout = 388000 #max_long_data_size = 1024M #========================== Session Thread =========================== thread_cache_size = 128 thread_stack = 512K #thread_concurrency = 4 #============================ Table Cache ============================ #table-cache = 512 table_open_cache = 512 join_buffer_size = 16M sort_buffer_size = 16M query_cache_type = OFF table_definition_cache = 768 #============================= Temptable ============================= tmp_table_size = 128M max_heap_table_size = 128M tmpdir = /opt/mysql/tmp/ #======================= Query Specific options ====================== #query_cache_limit = 32M query_cache_min_res_unit = 4096 query_cache_size = 0 #query_cache_strip_comments = 0 query_cache_type = 1 query_cache_wlock_invalidate = 0 #====================== MyISAM Specific options ====================== read_buffer_size = 2M read_rnd_buffer_size = 16M key_buffer_size = 512M myisam_sort_buffer_size = 256M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 #myisam_recover_options #====================== INNODB Specific options ====================== innodb_data_home_dir = /opt/mysql/data/ innodb_fast_shutdown = 1 innodb_force_recovery = 0 innodb_buffer_pool_size = 512M innodb_log_buffer_size = 64M innodb_log_file_size = 512M innodb_log_files_in_group = 2 innodb_data_file_path = ibdata1:100M:autoextend innodb_file_per_table = 1 innodb_write_io_threads = 8 innodb_read_io_threads = 8 innodb_io_capacity = 2000 innodb_open_files = 1024 innodb_purge_threads = 1 innodb_thread_concurrency = 34 innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 1000 #innodb_additional_mem_pool_size = 67108864 innodb_strict_mode = 1 innodb_use_native_aio = 1 #innodb_status_file = 1 #================================ Log ================================ log-error = /opt/mysql/log/error.log slow_query_log_file = /opt/mysql/log/slow.log long_query_time = 5 # Group Replication gtid_mode = ON enforce_gtid_consistency = ON binlog_checksum = NONE log_slave_updates = ON binlog_format= ROW transaction_write_set_extraction = XXHASH64 loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" loose-group_replication_start_on_boot = off loose-group_replication_local_address = '172.16.29.154:33061' loose-group_replication_group_seeds ='172.16.29.154:33061,172.16.29.132:33061,172.16.29.133:33061' loose-group_replication_bootstrap_group = off [mysqld_safe] log-error = /opt/mysql/log/mysqld_safe.log pid-file=/opt/mysql/mysqld.pid [mysqldump] quick max_allowed_packet = 1024M
这里需要注意的是每个节点的my.cnf配置文件修改server_id,local_address需要修改。
5. 创建测试数据验证MGR配置是否ok
mysql> create database enmotech;
Query OK, 1 row affected (0.01 sec)
mysql> use enmotech;
Database changed
mysql> create table test(a date);
Query OK, 0 rows affected (0.04 sec)
mysql>
mysql> insert into test values('2018-04-11'); ---MGR要求表必须有主键
ERROR 3098 (HY000): The table does not comply with the requirements by an external plugin.
mysql> alter table test add primary key (a);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into test values('2018-04-11');
Query OK, 1 row affected (0.01 sec)
mysql>
其他节点进行验证:
mysql> show variables like '%host%';
+-------------------------------+----------+
| Variable_name | Value |
+-------------------------------+----------+
| host_cache_size | 643 |
| hostname | mysqldb3 |
| performance_schema_hosts_size | -1 |
| report_host | |
+-------------------------------+----------+
4 rows in set (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| enmotech |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)可见整个MySQL Group Replication配置是完整ok的。
MySQL PXC集群的配置更为简单，这里不再贴出来了，有兴趣的可以自行测试一下，其中/etc/my.cnf在进行修改时保证每个节点的
server_id 、wsrep_node_address、wsrep_node_name 不同即可。
下面我们来看一下sysbench的对测试结果究竟如何：
a) MGR
[root@mysqldb1 lua]# /tools/sysbench-1.0/src/sysbench oltp_read_write.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-db=enmotech --mysql-user=root --mysql-password=enmotech --table_size=1000000--tables=10 --threads=50 --report-interval=10 --time=300 run
sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 50
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 50 tps: 231.19 qps: 4711.90 (r/w/o: 3302.59/385.31/1024.00) lat (ms,95%): 376.49 err/s: 0.20 reconn/s: 0.00
[ 20s ] thds: 50 tps: 254.75 qps: 5119.27 (r/w/o: 3584.55/512.71/1022.01) lat (ms,95%): 363.18 err/s: 0.80 reconn/s: 0.00
[ 30s ] thds: 50 tps: 290.81 qps: 5823.82 (r/w/o: 4078.38/688.41/1057.02) lat (ms,95%): 272.27 err/s: 0.60 reconn/s: 0.00
[ 40s ] thds: 50 tps: 297.70 qps: 5901.19 (r/w/o: 4129.56/769.71/1001.91) lat (ms,95%): 262.64 err/s: 0.30 reconn/s: 0.00
[ 50s ] thds: 50 tps: 279.50 qps: 5637.10 (r/w/o: 3957.50/794.60/885.00) lat (ms,95%): 287.38 err/s: 0.20 reconn/s: 0.00
[ 60s ] thds: 50 tps: 295.30 qps: 5863.39 (r/w/o: 4094.99/863.30/905.10) lat (ms,95%): 267.41 err/s: 0.40 reconn/s: 0.00
[ 70s ] thds: 50 tps: 280.00 qps: 5648.56 (r/w/o: 3956.24/861.71/830.61) lat (ms,95%): 314.45 err/s: 0.30 reconn/s: 0.00
[ 80s ] thds: 50 tps: 269.10 qps: 5386.30 (r/w/o: 3782.50/831.80/772.00) lat (ms,95%): 320.17 err/s: 0.10 reconn/s: 0.00
[ 90s ] thds: 50 tps: 268.20 qps: 5369.21 (r/w/o: 3759.20/854.30/755.70) lat (ms,95%): 314.45 err/s: 0.40 reconn/s: 0.00
[ 100s ] thds: 50 tps: 276.90 qps: 5549.93 (r/w/o: 3875.05/915.09/759.79) lat (ms,95%): 292.60 err/s: 0.10 reconn/s: 0.00
[ 110s ] thds: 50 tps: 276.40 qps: 5531.38 (r/w/o: 3877.05/896.51/757.81) lat (ms,95%): 287.38 err/s: 0.20 reconn/s: 0.00
[ 120s ] thds: 50 tps: 287.10 qps: 5702.73 (r/w/o: 3987.35/941.59/773.79) lat (ms,95%): 272.27 err/s: 0.40 reconn/s: 0.00
[ 130s ] thds: 50 tps: 286.40 qps: 5769.53 (r/w/o: 4040.82/962.31/766.40) lat (ms,95%): 272.27 err/s: 0.50 reconn/s: 0.00
[ 140s ] thds: 50 tps: 283.50 qps: 5642.51 (r/w/o: 3950.91/938.80/752.80) lat (ms,95%): 282.25 err/s: 0.40 reconn/s: 0.00
[ 150s ] thds: 50 tps: 283.10 qps: 5700.86 (r/w/o: 4001.54/956.01/743.31) lat (ms,95%): 287.38 err/s: 0.10 reconn/s: 0.00
[ 160s ] thds: 50 tps: 289.29 qps: 5784.19 (r/w/o: 4044.62/973.38/766.19) lat (ms,95%): 267.41 err/s: 0.20 reconn/s: 0.00
[ 170s ] thds: 50 tps: 280.00 qps: 5584.27 (r/w/o: 3910.45/939.61/734.21) lat (ms,95%): 292.60 err/s: 0.10 reconn/s: 0.00
[ 180s ] thds: 50 tps: 291.00 qps: 5822.57 (r/w/o: 4072.28/994.09/756.20) lat (ms,95%): 277.21 err/s: 0.20 reconn/s: 0.00
[ 190s ] thds: 50 tps: 277.20 qps: 5561.18 (r/w/o: 3901.15/943.11/716.91) lat (ms,95%): 292.60 err/s: 0.40 reconn/s: 0.00
[ 200s ] thds: 50 tps: 262.10 qps: 5257.41 (r/w/o: 3677.80/899.80/679.80) lat (ms,95%): 320.17 err/s: 0.30 reconn/s: 0.00
[ 210s ] thds: 50 tps: 255.01 qps: 5098.24 (r/w/o: 3568.80/866.08/663.36) lat (ms,95%): 344.08 err/s: 0.10 reconn/s: 0.00
[ 220s ] thds: 50 tps: 174.46 qps: 3502.23 (r/w/o: 2446.96/604.81/450.46) lat (ms,95%): 569.67 err/s: 0.00 reconn/s: 0.00
[ 230s ] thds: 50 tps: 212.50 qps: 4184.02 (r/w/o: 2922.42/708.10/553.50) lat (ms,95%): 530.08 err/s: 0.10 reconn/s: 0.00
[ 240s ] thds: 50 tps: 270.60 qps: 5468.46 (r/w/o: 3844.27/921.79/702.40) lat (ms,95%): 297.92 err/s: 0.00 reconn/s: 0.00
[ 250s ] thds: 50 tps: 244.40 qps: 4823.30 (r/w/o: 3359.33/830.78/633.19) lat (ms,95%): 369.77 err/s: 0.10 reconn/s: 0.00
[ 260s ] thds: 50 tps: 244.61 qps: 4970.67 (r/w/o: 3492.02/840.23/638.42) lat (ms,95%): 344.08 err/s: 0.30 reconn/s: 0.00
[ 270s ] thds: 50 tps: 256.70 qps: 5115.65 (r/w/o: 3583.47/867.49/664.69) lat (ms,95%): 325.98 err/s: 0.20 reconn/s: 0.00
[ 280s ] thds: 50 tps: 245.90 qps: 4902.72 (r/w/o: 3423.64/843.49/635.59) lat (ms,95%): 344.08 err/s: 0.30 reconn/s: 0.00
[ 290s ] thds: 50 tps: 242.69 qps: 4891.17 (r/w/o: 3427.71/834.28/629.18) lat (ms,95%): 344.08 err/s: 0.10 reconn/s: 0.00
[ 300s ] thds: 50 tps: 264.69 qps: 5290.91 (r/w/o: 3711.36/895.97/683.58) lat (ms,95%): 314.45 err/s: 0.10 reconn/s: 0.00
SQL statistics:
queries performed:
read: 1117718
write: 251518
other: 227227
total: 1596463
transactions: 79762 (265.72 per sec.)
queries: 1596463 (5318.39 per sec.)
ignored errors: 75 (0.25 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.1760s
total number of events: 79762
Latency (ms):
min: 14.81
avg: 188.12
max: 2903.44
95th percentile: 320.17
sum: 15004724.27
Threads fairness:
events (avg/stddev): 1595.2400/94.14
execution time (avg/stddev): 300.0945/0.04
b) PXC
[root@perconadb1 lua]# /tmp/sysbench-1.0/src/sysbench oltp_read_write.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-db=pxcdb --mysql-user=root --mysql-password=enmotech --table_size=1000000 --tables=10 --threads=50 --report-interval=10 --time=300 run
sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 50
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 50 tps: 126.99 qps: 2652.90 (r/w/o: 1869.45/292.36/491.09) lat (ms,95%): 646.19 err/s: 2.50 reconn/s: 0.00
[ 20s ] thds: 50 tps: 146.36 qps: 2927.18 (r/w/o: 2047.40/334.85/544.94) lat (ms,95%): 719.92 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 50 tps: 155.16 qps: 3130.15 (r/w/o: 2187.38/365.01/577.76) lat (ms,95%): 759.88 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 50 tps: 66.62 qps: 1320.97 (r/w/o: 929.16/150.44/241.37) lat (ms,95%): 3208.88 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 50 tps: 89.61 qps: 1778.42 (r/w/o: 1246.88/204.41/327.12) lat (ms,95%): 4517.90 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 50 tps: 133.01 qps: 2656.27 (r/w/o: 1859.89/307.43/488.95) lat (ms,95%): 646.19 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 50 tps: 65.48 qps: 1338.71 (r/w/o: 930.06/161.84/246.81) lat (ms,95%): 2985.89 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 50 tps: 109.94 qps: 2165.80 (r/w/o: 1520.89/252.98/391.93) lat (ms,95%): 2680.11 err/s: 0.10 reconn/s: 0.00
[ 90s ] thds: 50 tps: 198.38 qps: 3965.49 (r/w/o: 2772.42/478.15/714.93) lat (ms,95%): 363.18 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 50 tps: 210.13 qps: 4215.45 (r/w/o: 2954.69/499.77/761.00) lat (ms,95%): 325.98 err/s: 0.10 reconn/s: 0.00
[ 110s ] thds: 50 tps: 195.88 qps: 3924.88 (r/w/o: 2750.98/477.76/696.14) lat (ms,95%): 369.77 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 50 tps: 193.21 qps: 3863.44 (r/w/o: 2702.37/470.43/690.64) lat (ms,95%): 383.33 err/s: 0.00 reconn/s: 0.00
[ 130s ] thds: 50 tps: 181.90 qps: 3618.09 (r/w/o: 2529.19/454.80/634.10) lat (ms,95%): 434.83 err/s: 0.00 reconn/s: 0.00
[ 140s ] thds: 50 tps: 200.21 qps: 4009.46 (r/w/o: 2808.51/499.62/701.33) lat (ms,95%): 356.70 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 50 tps: 205.50 qps: 4121.74 (r/w/o: 2884.13/531.10/706.51) lat (ms,95%): 325.98 err/s: 0.00 reconn/s: 0.00
[ 160s ] thds: 50 tps: 201.90 qps: 4019.86 (r/w/o: 2812.07/513.19/694.59) lat (ms,95%): 350.33 err/s: 0.00 reconn/s: 0.00
[ 170s ] thds: 50 tps: 206.47 qps: 4141.01 (r/w/o: 2899.48/527.32/714.20) lat (ms,95%): 331.91 err/s: 0.00 reconn/s: 0.00
[ 180s ] thds: 50 tps: 203.93 qps: 4085.63 (r/w/o: 2861.24/526.58/697.81) lat (ms,95%): 350.33 err/s: 0.00 reconn/s: 0.00
[ 190s ] thds: 50 tps: 189.78 qps: 3789.32 (r/w/o: 2656.16/491.14/642.02) lat (ms,95%): 397.39 err/s: 0.00 reconn/s: 0.00
[ 200s ] thds: 50 tps: 215.33 qps: 4327.72 (r/w/o: 3024.46/580.07/723.19) lat (ms,95%): 308.84 err/s: 0.00 reconn/s: 0.00
[ 210s ] thds: 50 tps: 219.80 qps: 4369.65 (r/w/o: 3059.37/579.69/730.59) lat (ms,95%): 308.84 err/s: 0.00 reconn/s: 0.00
[ 220s ] thds: 50 tps: 192.20 qps: 3870.22 (r/w/o: 2706.42/524.40/639.40) lat (ms,95%): 419.45 err/s: 0.00 reconn/s: 0.00
[ 230s ] thds: 50 tps: 198.58 qps: 3954.90 (r/w/o: 2773.02/527.85/654.03) lat (ms,95%): 383.33 err/s: 0.00 reconn/s: 0.00
[ 240s ] thds: 50 tps: 193.71 qps: 3873.09 (r/w/o: 2712.80/520.07/640.22) lat (ms,95%): 363.18 err/s: 0.00 reconn/s: 0.00
[ 250s ] thds: 50 tps: 202.01 qps: 4040.48 (r/w/o: 2828.40/553.04/659.05) lat (ms,95%): 369.77 err/s: 0.00 reconn/s: 0.00
[ 260s ] thds: 50 tps: 167.47 qps: 3355.93 (r/w/o: 2347.50/467.70/540.73) lat (ms,95%): 520.62 err/s: 0.00 reconn/s: 0.00
[ 270s ] thds: 50 tps: 198.01 qps: 3946.20 (r/w/o: 2759.24/546.43/640.53) lat (ms,95%): 369.77 err/s: 0.10 reconn/s: 0.00
[ 280s ] thds: 50 tps: 205.00 qps: 4099.92 (r/w/o: 2870.21/574.00/655.70) lat (ms,95%): 344.08 err/s: 0.00 reconn/s: 0.00
[ 290s ] thds: 50 tps: 191.50 qps: 3842.12 (r/w/o: 2694.52/533.60/614.00) lat (ms,95%): 390.30 err/s: 0.00 reconn/s: 0.00
[ 300s ] thds: 50 tps: 209.22 qps: 4177.31 (r/w/o: 2920.09/603.18/654.04) lat (ms,95%): 344.08 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 739382
write: 135589
other: 181233
total: 1056204
transactions: 52785 (175.77 per sec.)
queries: 1056204 (3517.02 per sec.)
ignored errors: 28 (0.09 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.3102s
total number of events: 52785
Latency (ms):
min: 42.84
avg: 284.35
max: 5014.55
95th percentile: 442.73
sum: 15009224.91
Threads fairness:
events (avg/stddev): 1055.7000/13.52
execution time (avg/stddev): 300.1845/0.07我们不难看出当threads=50时，mgr依然可以维持在5300的QPS，而pxc基本上平均降到3500左右。
同时测试了threads=20的情况，pxc的性能稍微有所提升，大概在4000左右。但是仍然不敌MGR，因为MGR QPS 超过5000. 由于我这里是虚拟机，本地盘使用的是三星SSD，因此所测QPS相对还算理想。实际上测试20并发的时候，cpu 基本上已经耗尽了，因为我每个虚拟机只分配了一颗cpu。
如下是pxc sysbench threads=20的测试：
[root@perconadb1 lua]# /tmp/sysbench-1.0/src/sysbench oltp_read_write.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-db=pxcdb --mysql-user=root --mysql-password=enmotech --table_size=1000000 --tables=10 --threads=20 --report-interval=10 --time=300 run
sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 20
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 20 tps: 185.02 qps: 3734.59 (r/w/o: 2616.87/377.43/740.28) lat (ms,95%): 173.58 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 20 tps: 176.94 qps: 3517.18 (r/w/o: 2460.85/357.28/699.05) lat (ms,95%): 193.38 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 20 tps: 218.70 qps: 4390.41 (r/w/o: 3076.51/454.70/859.20) lat (ms,95%): 147.61 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 20 tps: 214.38 qps: 4296.33 (r/w/o: 3004.34/447.16/844.83) lat (ms,95%): 153.02 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 20 tps: 193.52 qps: 3858.51 (r/w/o: 2701.92/406.93/749.66) lat (ms,95%): 189.93 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 20 tps: 217.50 qps: 4351.91 (r/w/o: 3047.60/465.90/838.40) lat (ms,95%): 142.39 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 20 tps: 220.80 qps: 4412.19 (r/w/o: 3087.60/483.30/841.30) lat (ms,95%): 147.61 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 20 tps: 205.30 qps: 4096.21 (r/w/o: 2865.50/451.70/779.00) lat (ms,95%): 167.44 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 20 tps: 192.10 qps: 3864.54 (r/w/o: 2705.56/435.59/723.39) lat (ms,95%): 161.51 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 20 tps: 150.70 qps: 2990.31 (r/w/o: 2092.64/338.19/559.48) lat (ms,95%): 390.30 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 20 tps: 216.60 qps: 4330.10 (r/w/o: 3029.80/493.80/806.50) lat (ms,95%): 147.61 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 20 tps: 224.31 qps: 4510.22 (r/w/o: 3159.76/521.23/829.24) lat (ms,95%): 142.39 err/s: 0.00 reconn/s: 0.00
[ 130s ] thds: 20 tps: 212.60 qps: 4241.80 (r/w/o: 2971.50/492.10/778.20) lat (ms,95%): 161.51 err/s: 0.00 reconn/s: 0.00
[ 140s ] thds: 20 tps: 195.40 qps: 3915.58 (r/w/o: 2739.18/464.40/712.00) lat (ms,95%): 164.45 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 20 tps: 208.10 qps: 4157.42 (r/w/o: 2913.51/494.30/749.60) lat (ms,95%): 167.44 err/s: 0.00 reconn/s: 0.00
[ 160s ] thds: 20 tps: 198.30 qps: 3975.83 (r/w/o: 2778.45/488.99/708.39) lat (ms,95%): 158.63 err/s: 0.00 reconn/s: 0.00
[ 170s ] thds: 20 tps: 207.50 qps: 4142.33 (r/w/o: 2900.52/503.00/738.81) lat (ms,95%): 155.80 err/s: 0.00 reconn/s: 0.00
[ 180s ] thds: 20 tps: 215.40 qps: 4313.75 (r/w/o: 3018.66/522.09/772.99) lat (ms,95%): 150.29 err/s: 0.00 reconn/s: 0.00
[ 190s ] thds: 20 tps: 204.40 qps: 4060.35 (r/w/o: 2841.43/503.01/715.91) lat (ms,95%): 161.51 err/s: 0.00 reconn/s: 0.00
[ 200s ] thds: 20 tps: 203.10 qps: 4079.75 (r/w/o: 2859.54/508.61/711.61) lat (ms,95%): 167.44 err/s: 0.00 reconn/s: 0.00
[ 210s ] thds: 20 tps: 201.80 qps: 4022.44 (r/w/o: 2812.06/503.79/706.59) lat (ms,95%): 155.80 err/s: 0.00 reconn/s: 0.00
[ 220s ] thds: 20 tps: 216.90 qps: 4344.29 (r/w/o: 3041.70/550.40/752.20) lat (ms,95%): 142.39 err/s: 0.10 reconn/s: 0.00
[ 230s ] thds: 20 tps: 204.10 qps: 4094.18 (r/w/o: 2869.45/525.31/699.41) lat (ms,95%): 153.02 err/s: 0.00 reconn/s: 0.00
[ 240s ] thds: 20 tps: 218.00 qps: 4360.99 (r/w/o: 3052.09/567.50/741.40) lat (ms,95%): 144.97 err/s: 0.00 reconn/s: 0.00
[ 250s ] thds: 20 tps: 194.20 qps: 3887.01 (r/w/o: 2721.14/503.89/661.98) lat (ms,95%): 204.11 err/s: 0.00 reconn/s: 0.00
[ 260s ] thds: 20 tps: 188.20 qps: 3745.21 (r/w/o: 2619.01/490.50/635.70) lat (ms,95%): 196.89 err/s: 0.00 reconn/s: 0.00
[ 270s ] thds: 20 tps: 198.40 qps: 3980.26 (r/w/o: 2789.74/527.51/663.01) lat (ms,95%): 161.51 err/s: 0.00 reconn/s: 0.00
[ 280s ] thds: 20 tps: 209.20 qps: 4194.88 (r/w/o: 2934.19/557.20/703.50) lat (ms,95%): 139.85 err/s: 0.10 reconn/s: 0.00
[ 290s ] thds: 20 tps: 206.20 qps: 4101.96 (r/w/o: 2869.37/543.79/688.79) lat (ms,95%): 150.29 err/s: 0.00 reconn/s: 0.00
[ 300s ] thds: 20 tps: 216.80 qps: 4340.95 (r/w/o: 3039.07/583.99/717.89) lat (ms,95%): 142.39 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 856338
write: 145666
other: 221330
total: 1223334
transactions: 61165 (203.78 per sec.)
queries: 1223334 (4075.81 per sec.)
ignored errors: 2 (0.01 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.1434s
total number of events: 61165
Latency (ms):
min: 14.16
avg: 98.12
max: 1365.66
95th percentile: 158.63
sum: 6001736.47
Threads fairness:
events (avg/stddev): 3058.2500/13.03
execution time (avg/stddev): 300.0868/0.04
总的来说，MGR的性能完胜PXC！未来MGR是大势所趋！
备注：
1、我这里pxc和mgr均为5.7最新版本。
2、后续又分别调整了部分参数进行优化，分析性能均有一定上升，如下是参数：
PXC：
wsrep_slave_threads=16
MGR：
slave_parallel_type =LOGICAL_CLOCK
slave_parallel_workers =16
group_replication_compression_threshold =3000000
group_replication_flow_control_certifier_threshold=250000
group_replication_flow_control_applier_threshold=250000
