For the sake of simplicity, I recommend MySQL Circular Replication only. Here is why:
There are many technologies and topologies that are far superior to MySQL Circular Replication. My favorite, hands down, is DRBD (Distributed Replicated Block Device). However, DRBD works great when the Server Pair is in the same bulding, data center, and rack. It's even better when using a crossover cable in the 192.168.x.x. subnet between the DRBD Primary and DRBD Secondary. Unfortunately, DRBD has horrible performance over a distance between two locations, although DRBD can still work. There are no network topologies around to give you the satisfactory DRBD performance needed between two datacenters.
Once you setup MySQL Circular Replication between the two DB servers in two different data centers, the only tuning needed is for the network. In essence, the replication performance is a function of network settings (speed/latency of binary log transmission in MySQL Replication Setup) and disk I/O (DRBD).
An alternative you may want for better redundancy is the following for the sake of example:
Setup a DRBD Pair in both locations
DRBD Pair in Site #1 with VIP 111.111.111.111
DRBD Pair in Site #2 with VIP 222.222.222.222
Setup MySQL Circular Replication between the DRBD Primary Servers under these conditions:
For site #1, use 222.222.222.222 as the Master_Host in MySQL
For site #2, use 111.111.111.111 as the Master_Host in MySQL
Although introducing a level of complexity, now you have two levels of redundancy: DRBD within each site and MySQL Circular Replication between sites. You have the additional benefits of running backups via mysqldump on the DRBD Primary of the hot standby server.
As for failover, DRBD provides automatic failover at any one site.
Only in the event that a datacenter is totally unavailble would you using the DB VIP at the hot standby site.
UPDATE
I just did a double take and noticed that you are using Drupal6. I am glad you will be converting all the drupal tables to InnoDB. This will remove any chance of MyISAM table updates causing table locks to freeze DB Connections that are simply reading MyISAM tables. Any DML update (INSERTs, UPDATEs, DELETEs) against a MyISAM table WILL ALWAYS DO A FULL TABLE LOCK !!! Using InnoDB will introduce row-level locking, which eliminates full table locks.
In addition, DRBD becomes your friend when everything is InnoDB because crash recovery will be consistent between the DRBD Pair. Contrawise, DRBD with MyISAM buys you nothing because a crashed MyISAM table on the DRBD Primary is simply duplicated to the DRBD Secondary as, you guessed it, a crashed MyISAM table.
UPDATE #2
You should use two levels of redundancy
Level 1 : At each database center, use DRBD.
http://dev.mysql.com/doc/refman/5.1/en/ha-drbd.html
Set up a pair of DB Servers
Startup DRBD
Startup MySQL on the DRBD Primary
This creates redundant data at the disk level.
Level 2 : You should setup MySQL Circular Replication between
the DRBD Primary of DataCenter#1 and the DRBD Primary of DataCenter#2
Each DRBD Primary will be running MySQL and will act
as both Master and Slave to Each Other
I have setup for clients topologies like this and I consider it quite stable.
I am against writing to both Masters in a dual-master setup. There are too many things that can go wrong, and they can be messy to fix -- AUTO_INCREMENTs, other duplicate keys, etc.
Hundreds of "Sleep" connections is virtually no impact on a server, so limiting to 40 is not useful. 10 or more active connections (non-Sleep) can be an issue. In that case I would look at the queries. Usually optimizing the queries is the best answer.
Note also, that every write (INSERT, UPDATE, etc) that is done on one Master must be done on all the other Masters and Slaves. So, you can't really "spread" writes around.
If you have processes that do only reads (SELECTs), then they should go to Slaves and/or the backup Master, not the live, writable, Master. This will help.
Be aware of the "critical write" problem. Example: A user posts a blog comment, then looks at his comments, but it is missing. This can happen if the write went to one machine, but the read hit another, and replication is "behind".
(My comments apply to all versions, and all APIs, not just 5.1 and PHP's mysqli.)
I stay away from mysql_pconnect (and other connection pooling mechanisms). Connection startup/teardown is very fast in MySQL. Pooled connections may have issues with @variables, transaction modes, sql_modes, etc.
Best Answer
Turn on the slowlog on both machines. What version of MySQL are you using? Here are good settings to have for a relatively new version:
More: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog
A possible cause of what you are seeing: An
ALTERthat takes some time and hits tableA is replicated. Meanwhile, other things are happening (read or write) to tableA. The above settings will probably catch the ALTER.Another thing to look for (but hard to see) in the slowlog: A bunch of queries ending at about the same second. This usually means that one of them took a long time and was blocking the others (either directly or indirectly).
In any case, use
pt-query-digest; its default sort is to put the "worst" queries first. Then...UPDATE ... WHERE primary_key = 123taking second(s) or even minute(s), not milliseconds.