From: Marcus Bointon
Date: September 23 2012 4:00pm
Subject: Re: how to manage one master and many slaves
List-Archive: http://lists.mysql.com/replication/2382
Message-Id: <5A6F669B-1F1A-44C2-8DB8-9D9C06FBC279@synchromedia.co.uk>
MIME-Version: 1.0 (Mac OS X Mail 6.1 \(1498\))
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
On 23 Sep 2012, at 14:56, nik600 wrote:
> - is mysql-proxy the correct product for this scenario?
> - what will you use to organize database access from an application
> when you have a single master and many slaves?
You can only do this at the proxy level if you don't use transactions, =
unless the proxy itself is transaction-aware.
I've built my apps with the ability to use split read/write database =
hosts, and I really don't see why you think it's so hard. It's quite =
similar to using memcache on multiple hosts.
Transactions make it more difficult because if you issue a select in the =
middle of a transaction, it must go to the same host as the rest of the =
transaction, so na=EFvely splitting select queries to read-only slaves =
will break very badly.
I do something similar when I need to maintain additional connections =
outside of a transaction - imagine you have a long-running transaction =
with many queries, but you want to make its progress available via the =
database. You can't do that from inside the transaction because the =
writes are not visible until the whole transaction is committed, and =
updates inside the transaction are not visible from outside, so you must =
maintain a sparate write connection for jobs like that. So, my app =
usually has two write connections and several read-only connections =
available, and it knows to send the read queries to the primary write =
connection when inside a transaction. I've never encountered any =
database abstraction / ORM that takes either of these into account.
Marcus
--=20
Marcus Bointon
Synchromedia Limited: Creators of http://www.smartmessages.net/
UK info@hand CRM solutions
marcus@stripped | http://www.synchromedia.co.uk/