MariaDB 10之并行复制--延迟测试结果

测试参数：

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，如需转载请自行联系原作者