Advanced MySQL Replication - Improving Performance

Feb 12, 2009, 23:01 (0 Talkback[s])
(Other stories by Sean Hull)

"One method would be to break up data into multiple slaves. This could be done database by database (schema by schema) creating one slave database. Each slave would be pointing to the same master, and using replicate_do_db and replicate_ignore_db statements to specify which data to capture for it's slave. However it may be that your applications are not broken up cleanly that way, or that load favors certain tables, rather than certain schemas. So for your site there may be reason to do it on a table-by-table basis. You would then use replicate_do_table and replicate_ignore_table. You'll also need to keep in mind that more slaves mean more threads on the master database, which could add load there. If you run into that problem, consider creating a distribution master, and then slaving off of that. As a further consideration, realize that if you are running backups off of slave databases, your backup scripts will obviously be more complicated in this arrangement, so document well."

Complete Story

Related Stories:

• Monty Widenius talks about why he left Sun Microsystems(Feb 11, 2009)
• Quick DB Setups With MySQL Sandbox(Feb 08, 2009)
• Installing PowerDNS (With MySQL Backend) And Poweradmin On Ubuntu 8.10(Jan 31, 2009)
• Setting, Changing And Resetting MySQL Root Passwords(Jan 22, 2009)
• Setting Up A High-Availability Load Balancer With Perlbal/Heartbeat On Debian Etch(Jan 18, 2009)
• Intro to MySQL Proxy(Jan 16, 2009)