MySQL高可用方案之--PXC vs MGR

原创 Roger 2018-04-15

2819

首先我们来安装部署好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@'{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}' identified by 'enmotech';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to rep@'{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}';
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@'{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}' identified by 'enmotech';
grant replication slave on *.* to rep@'{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}';
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 '{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}host{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}';
+-------------------------------+----------+
| 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}): 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

mysql

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

Roger's Database Notes（共77篇）

非知名DBA、数据库爱好者、专注于Oracle/MySQL/PostgreSQL/NewSQL/国产数据库等

MySQL高可用方案之--PXC vs MGR

文章被以下合辑收录

评论