I am running a Master-Master replication setup, with one server being on a satellite internet connection. I have used this setup before to successfully offer more responsive applications on laggy networks, but my most recent implementation seems to have issues with the connection itself. Generally speaking, I am wondering how to either diagnose the problem, or tweak TCP and/or MySQL to be more resilient with the poor connection.
Background: Server A is a dedicated MySQL server with great connection. Server B is a small server connected via a satellite connection. Both are setup to do Master-Master synchronization, and the configuration worked great before Server B was moved to the satellite connection.
Currently, A-Master --> B-Slave is working fine. Server B is able to connect to external servers without a problem. But incoming traffic on the satellite connection is very buggy. So the B-Master ---> A-Slave rarely works. It will manages to connect once or twice a day, so it's not a total failure, but usually connections timeout or get reset. And it's not just MySQL. If I try to connect to Server B's SSH port, it only works one out of twenty attempts (usually, it just timesout).
Usually Server A times out when connecting to Server B, but when it does connect, the connection often drops, as the error log shows:
120130 8:25:41 [Note] Slave: connected to master 'userreplicate@server.B.ip.address:3306',replication resumed in log 'mysql-bin.000007' at position 13966485
120130 8:35:48 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)
120130 8:35:48 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysql-bin.000007' at position 13966485
120130 8:38:57 [ERROR] Slave I/O: error reconnecting to master 'userreplicate@server.B.ip.address:3306' - retry-time: 60 retries: 0, Error_code: 2003
Running a traceroute from Server A to Server B seems to suggest that connections are not even reaching the satellite modem before timing out:
[root@serverA ~]# traceroute -w 15 server.B.ip.address
...
4 xe-7-1-1.edge2.Chicago2.Level3.net (4.59.29.81) 5.494 ms 5.490 ms 5.473 ms
5 vl-3501-ve-115.ebr1.Chicago2.Level3.net (4.69.158.2) 12.463 ms vl-3502-ve-116.ebr1.Chicago2.Level3.net (4.69.158.6) 6.412 ms vl-3601-ve-225.ebr2.Chicago2.Level3.net (4.69.158.50) 16.806 ms
6 ae-1-51.edge4.Chicago3.Level3.net (4.69.138.134) 5.558 ms * *
7 chp-brdr-03.inet.qwest.net (63.146.27.17) 8.453 ms 8.455 ms 8.494 ms
8 * * *
9 63.149.106.110 (63.149.106.110) 74.049 ms 73.906 ms 74.196 ms
10 * * *
11 * * *
12 * * *
...
I tried extending timeouts and increasing TCP/IP connection retries:
mysql> SET GLOBAL connect_timeout = 30;
[root@serverA ~]# sysctl -w net.ipv4.tcp_retries1 = 7
But these did not seem to help. Any suggestions on either how to diagnose the connection problems, or how to configure TCP/IP to help allow incoming connections on a satellite connection would be greatly appreciated.
