Testing Replication Over the Pond – Part 2 Secure

[Chris]

Testing Secure Replication

For the first test I used encrypted (SSL) replication and inserted 200,000 records using three 10 minute disconnection intervals per hour.

After several hundred thousand of inserts, deletes and updates on the SOA and RR tables simultaneously over a normally connected SSL replication channel I have the following results:

RR Update Test
Test 1:
RR Records Updated (Changes Set) = 129145
Elapsed Time = 14147.411601
RR Updates/Second = 9.13
RR Updates/Minute = 547.8
Europe MySQL Slave RR Update Counts (Changes Set) = 129145
Asia MySQL Slave RR Update Counts (Changes Set) = 129145
Test 2:
RR Records Updated (Changes Cleared) = 129145
Elapsed Time = 10168.638867
RR Updates/Second = 12.70
RR Updates/Minute = 762
Europe MySQL Slave RR Update Counts (Changes Cleared) = 129145
Asia MySQL Slave RR Update Counts (Changes Cleared) = 129145

SOA Insert and Delete Test:
Test 1:
SOA Records Inserted = 125000
Elapsed Time = 13726.669661
SOA Inserts/Second = 9.11
SOA Inserts/Minute = 546.6
Europe MySQL Slave SOA Insert Counts = 125000
Asia MySQL Slave SOA Insert Counts = 125000
Test 2:
SOA Records Deleted = 125000
Elapsed Time = 10399.711966
SOA Deletes/Second = 12.02
SOA Deletes/Minute = 721.2
Europe MySQL Slave SOA Delete Counts = 125000
Asia MySQL Slave SOA Delete Counts = 125000

SOA Update Test
Test 1:
SOA Records Updated (Changes Set) = 125040
Elapsed Time = 13634.032205
SOA Updates/Second = 9.17
SOA Updates/Minute = 550.2
Europe MySQL Slave SOA Update Counts (Changes Set) = 125040
Asia MySQL Slave SOA Update Counts (Changes Set) = 125040
Test 2:
SOA Records Updated (Changes Cleared) = 125040
Elapsed Time = 10490.043709
SOA Updates/Second = 11.92
SOA Updates/Minute = 715.2
Europe MySQL Slave SOA Update Counts (Changes Cleared) = 125040
Asia MySQL Slave SOA Update Counts (Changes Cleared) = 125040

In order to gain information in regard to transfer rate degradation over the SSL replication channels, the replication channel was switched to Non-SSL mode, the Slaves were disconnected, and the Master was loaded with 50,000+ records. Replication was switched back to SSL mode and the Slaves reconnected to evoke the follow of replication data over the SSL channel. The replication transfer rate was in the range of 222-243 records per second from Europe and in the range of 202-222 records per second from Asia.

Below is a snapshot of the transfer results after the Slaves are reconnected to the Master over the given SSL channels:

Europe
12:54:20 --- SOA Replication Table Row Count = 22000
12:54:32 --- SOA Replication Table Row Count = 24701
12:54:44 --- SOA Replication Table Row Count = 27374
12:54:56 --- SOA Replication Table Row Count = 30061
12:55:08 --- SOA Replication Table Row Count = 32772
12:55:19 --- SOA Replication Table Row Count = 35450

Asia
12:56:14 --- SOA Replication Table Row Count = 33364
12:56:26 --- SOA Replication Table Row Count = 35801
12:56:37 --- SOA Replication Table Row Count = 38236
12:56:49 --- SOA Replication Table Row Count = 40664
12:57:00 --- SOA Replication Table Row Count = 43095
12:57:11 --- SOA Replication Table Row Count = 45546

Summary
After several days of working with and analyzing the behavior of MySQL replication under a multi-continent WAN configuration, it is safe to conclude that this solution is technically sound for moving information to multiple data centers around the globe.