Here's a fun little performance tweak you can use to increase the throughput of your MySQL slave boxes.
MySQL slave replication is single threaded when it comes to running SQL. If you have a number of clients performing INSERTs at the same time on a master server these are serialized and replayed on your slaves. The master has an IO advantage in that the controller can batch operations (especially on SCSI and RAID controllers) and generally perform more intelligent IO which the slaves aren't capable of handling. Apparently this is slated to be fixed in MySQL 5.1 but I'm not currently aware of the details.
Rewriting your UPDATE/INSERTS to use ON DUPLICATE KEY UPDATE can allow you take individual statements and rewrite them. For example you can do:
INSERT INTO FOO (ID, BAR) VALUES(1,2),(3,4) ON DUPLICATE KEY UPDATE BAR=VALUES(BAR)
Instead of issuing two individual INSERT or UPDATE statements.
Here's the …
[Read more]