測試引數:
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引數:
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
普通複製延遲:
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沒追完
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模式)延遲:
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沒追完
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模式)延遲:
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沒追完
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)
結論:
在我這個測試中,並行複製的速度要快於普通複製的兩倍多。