MySQL高可用方案之–PXC vs MGR

本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客

首先我们来安装部署好MGR，如下是MGR的简单配置步骤：

1. 初始化node1

/usr/local/mysql/bin/mysqld  --defaults-file=/etc/my.cnf --initialize --user=mysql

1	/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)

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;

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

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

[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> 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

[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:

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

[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:

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

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:

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

Tags: mgr, mysql cluster, pxc, sysbench
Posted in MySQL, MySQL 架构设计 on 4月 15, 2018

You must be logged in to post a comment.

love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客

Categories

Archives

最新评论

国内圈子

oracle security

MySQL高可用方案之–PXC vs MGR

Leave a Reply