Mysql – What’s the latest on MySQL Proxy

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.

You can take a look into MySQL Fabric (Official Doc) but it requires more db server

I have tried this tool only in R&D env for testing a basic HA

It supports some sharding scenarios

Here some high level pros and cons

Pros:

• setup
• sharding
• write/read
• basic HA

Cons:

• fabric node is the SPOF
• no multi master
• rewrite software in order to use the fabric driver