The direct answer to your question is Yes, but it depends on the version of MySQL you are running. Before MySQL 5.5, replication would operate as follows:
- Master Executes SQL
- Master Records SQL Event in its Binary Logs
- Slave Reads SQL Event from Master Binary Logs
- Slave Stores SQL Event in its Relay Logs via I/O Thread
- Slave Reads Next SQL Event From Relay Log via SQL Thread
- Slave Executes SQL
- Slave Acknowledges Master of the Complete Execution of the SQL Event
As of MySQL 5.5, using Semisynchronous Replication, now replication would operate as follows:
- Master Executes SQL
- Master Records SQL Event in its Binary Logs
- Slave Reads SQL Event from Master Binary Logs
- Slave Acknowledges Master of the Receipt of the SQL Event
- Slave Stores SQL Event in its Relay Logs via I/O Thread
- Slave Reads Next SQL Event From Relay Log via SQL Thread
- Slave Executes SQL
- Slave Acknowledges Master of the Complete Execution of the SQL Event
This new paradigm will permit a Slave to be closer sync'd to its Master.
Notwithstanding, latency within the network could hamper MySQL Semisync Replication to the point where it reverts back to the old-style asynchronous replication. Why ? If a timeout occurs without any slave having acknowledged the transaction, the master reverts to asynchronous replication. When at least one semisynchronous slave catches up, the master returns to semisynchronous replication.
UPDATE 2011-08-08 14:22 EDT
The configuration of MySQL 5.5 Semisynchronous Replication is straightforward
Step 1) Add these four(4) lines to /etc/my.cnf
[mysqld]
plugin-dir=/usr/lib64/mysql/plugin
#rpl_semi_sync_master_enabled
#rpl_semi_sync_master_timeout=5000
#rpl_semi_sync_slave_enabled
Step 2) Restart MySQL
service mysql restart
Step 3) Run these commands in the MySQL client
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Step 4) Uncomment the three rpm_semi_sync options after the plugin-dir option
[mysqld]
plugin-dir=/usr/lib64/mysql/plugin
rpl_semi_sync_master_enabled
rpl_semi_sync_master_timeout=5000
rpl_semi_sync_slave_enabled
Step 5) Restart MySQL
service mysql restart
All Done !!! Now just setup MySQL Replication as usual.
I think a chunk is a set number of rows to be scanned and checksummed.
I never did like chunks because if the table order matters, each chunk could be completely different if just one row was written ahead or behind another.
Try not setting a chunk size and see if the entire table is checksummed as a whole as a opposed to each chunk checksum.
You can also run mk-table-sync using the --print option and redirect to a text file to see if there is any SQL created that would have been executed to sync the slave had you used the --execute option. If nothing comes out into the text file from mk-table-sync, you will have to resort to doing physical copying (for MyISAM) or mysqldump the table and loading it on the slave (for InnoDB).
Give it a Try !!!
Best Answer
20 commits/second is not very fast.
A Master can have any number of Slaves. Those Slaves can be local to the same datacenter or remote, in the other datacenters.
You can have "relay" servers that act like a Slave to the Master, but then act like a Master to 'downstream' Slaves. With this kind of topology, you can have an unlimited number of Slaves. (I have not seen it more than 3 level deep including the Master. Nor have I seen more than a few dozen Slaves total. But no "limit" was being hit.)
Multiple Masters is a much different matter. Usually the best topology is to have two Masters, one being written to and the other being a hot backup. This is "Dual Master, single writer". Each of them can have Slave(s).
Even better is Galera Clustering -- See MariaDB or Percona (PXC). It allows for 3 or more writable masters and handles most of the nasties that led me to say "single writer" in the previous paragraph. Again, these "nodes" can have "Slave(s)" hanging off them.
Keep in mind that a Slave can only be read from. To "write" you need to hit a Master. If the master is on the "other side of the country (or world)", there are speed-of-light delays, upwards of 100ms to go across the US.
The delays do not necessarily impact the "20 commits/sec". At 100ms, you cannot perform more than 10 commits/sec from a single connection. But, you probably have multiple connections, correct?
At 100 commits/sec, some disk tuning issues come into play. At 1000/sec, other issues raise their ugly heads. Some carefully crafted benchmarks have run at 1M/sec.
You have not explained what you mean by "ID consistency". One thing comes to mind: When writing to both Masters in a Dual-Master setup, you are asking for this kind of trouble.