On 23 Sep 2012, at 14:56, nik600 <nik600@stripped> 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
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ïvely
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.
Synchromedia Limited: Creators of http://www.smartmessages.net/
UK info@hand CRM solutions
marcus@stripped | http://www.synchromedia.co.uk/