I am running 2 cpanel servers with MySQL Replication set on them, one in france the other in US. Running a database hungary site. Replication was setup to load balance as well as for failover reason with mysql master being in US.
The issue is slave sometimes lag behind and leaves some rows which creates difference in dbs.
I want to know what could be the possible reason behind this.
Latency between servers is 250ms which some times rises to 400ms or more but sometimes.
Nodes are 32 GB Octa core, tables with around 400k rows and total database size below 5GB and table count around 700. And connectivity at around 100 Mbps.
I know there is always room for optimizations though databases have been optimised.
Our Application receives 10 -15 hits per second in peak time and every hit creates 2/3 database queries.
What could be a possible solution to this problem? What are standard practices followed in the industry for similar situations.
Best Answer
First You can try to enable compression for slave
it can reduce replication lags
than if the problem not in log-size, but as well because MySQL slave processes single threaded You can test use feature for multi thread slave, it increase speed of apply transactions on the slave server - https://dev.mysql.com/tech-resources/articles/mysql-5.6-replication.html
Before this 2 changes on one of my clients, slave server replication lag was always couple of hours in period of high loading hours, after - 0