How fast is MySQL replication?
Solution 1:
MySQL replication happens as close to real-time as possible, as limited by disk and network I/O. The slaves open a socket to the master, which is kept open. When a transaction occurs on the master, it gets recorded in the binlog, and is simply replayed on the slave(s). If the socket between master and slave is interrupted, the binlog is replayed for the slave upon the next successful connection.
Multi-master replication does the same thing, but in both directions.
Some basic calculations will assist you in making a better determination of your bandwidth needs.
Average transaction size * number of slaves * updates/minute = bandwidth needed
Hope this helps.
Solution 2:
Replication in MySQL is pretty quick to get the data to the slave (quicker than you'll be able to run the UPDATE on the master, and switch to another window to run a SELECT on the slave, if (and only if) the network connections are all up and everything's running OK. Any DSL-class connection should be fine for the general case of your regular small queries, but large insert/update queries can take a little while to copy, and re-synchronisation in the event of a replication stuffup (and MySQL is viciously prone to those, unfortunately) will take a while (copying your whole database from the master again). There are tricks to limiting the impact of resynchronisation on your master, like putting your MySQL on LVM so you can do a very quick lock/snapshot and rsync the snapshot contents to the slave, but ultimately a resync is going to suck.
Solution 3:
Replication on the slaves side is handled by two independent threads.
- The log reader process, which connects to the master, receives each data modifying statement, writes it to the relay log.
- The sql writer process, which takes new items from the relay log, commits the statements on the slaves database, then moves the slave pointer past that statement to indicate receipt of the query.
Replication latency is limited by IO, firstly the IO on the slave database to apply the transactions from the relay log (which may involve complex SQL queries) and secondly by the IO on the master to read it's binlog and transmit it to each slave.
MySQL replication increases read query capacity but does not increase query write performance, which is gated at the speed IO's can be flushed to the binlog on both the master, and the slave