测试参数：
|
1
2
3
4
5
6
|
sysbench --
test
=
/root/sysbench0
.5
/sysbench/tests/db/insert
.lua
--mysql-table-engine=innodb --oltp-table-size=1000000
--max-requests=0 --max-
time
=300 --num-threads=16
--oltp-tables-count=10 --report-interval=10
--mysql-host=10.8.8.100 --mysql-port=3312 --mysql-user=admin
--mysql-password=123456 --mysql-db=
test
run
|
10张100万行表，并发16个线程，纯插入操作。
MySQL参数：
|
1
2
3
4
5
6
7
8
9
|
sync_binlog = 0
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_flush_neighbors = 1
innodb_buffer_pool_size = 10G
innodb_io_capacity = 500
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_log_buffer_size = 16M
|
普通复制延迟：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 567748597
Relay_Log_File: host-100-relay-bin.000005
Relay_Log_Pos: 66853042
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 469504741
|
还剩1117MB没追完
|
1
2
3
4
5
6
7
|
MariaDB [(none)]>
select
1024-469504741
/1024/1024
+567748597
/1024/1024
;
+----------------------------------------------+
| 1024-469504741
/1024/1024
+567748597
/1024/1024
|
+----------------------------------------------+
| 1117.69264221 |
+----------------------------------------------+
1 row
in
set
(0.00 sec)
|
并行复制（8个并发线程，未开启GTID模式）延迟：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
Master_Log_File: mysql-bin.000022
Read_Master_Log_Pos: 333773843
Relay_Log_File: host-100-relay-bin.000073
Relay_Log_Pos: 134217580
Relay_Master_Log_File: mysql-bin.000021
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 722396020
|
还剩653MB没追完
|
1
2
3
4
5
6
7
|
MariaDB [(none)]>
select
1024-722396020
/1024/1024
+333773843
/1024/1024
;
+----------------------------------------------+
| 1024-722396020
/1024/1024
+333773843
/1024/1024
|
+----------------------------------------------+
| 653.38101101 |
+----------------------------------------------+
1 row
in
set
(0.00 sec)
|
并行复制（8个并发线程，开启GTID模式）延迟：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 365095829
Relay_Log_File: host-100-relay-bin.000008
Relay_Log_Pos: 134217572
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 811005100
|
还剩598MB没追完
|
1
2
3
4
5
6
7
|
MariaDB [(none)]>
select
1024-811005100
/1024/1024
+365095829
/1024/1024
;
+----------------------------------------------+
| 1024-811005100
/1024/1024
+365095829
/1024/1024
|
+----------------------------------------------+
| 598.74778080 |
+----------------------------------------------+
1 row
in
set
(0.00 sec)
|
结论：
在我这个测试中，并行复制的速度要快于普通复制的两倍多。
本文转自hcymysql51CTO博客，原文链接： http://blog.51cto.com/hcymysql/1433108，如需转载请自行联系原作者
