I have configured two MySQL servers (MySQL-1, MySQL-2) in master-master replication in the same datacenter using a local backend connection with the following options:
innodb_flush_log_at_trx_commit=1
sync_binlog=1
We use a load balancer to round robin MySQL requests back and forth equally between the two MySQL servers. This works great, but I am concerned about replication lag. For example, if user A inserts a row into MySQL-1, then user A selects from MySQL-2, the data may have not been successfully replicated.
Basically, my question is, how much lag should be expected (milliseconds, seconds)? Are their anymore MySQL options to set to prevent/reduce lag?
Best Answer
This depends on your servers performance which is related to how many queries each server has to process, how big are your tables, and so on. Using such replication solution should be synchronous which will definitely imposes some delay during transactions. This is simply because each transaction should not be considered committed completely unless it is done so on both nodes.
I think a safer option is to balance the requests based on the source IP of the client (if this is supported/possible). In this case, all requests coming from the same client will be forwarded to the same DB server.