From: nik600 Date: September 23 2012 6:12pm Subject: Re: how to manage one master and many slaves List-Archive: http://lists.mysql.com/replication/2383 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable 2012/9/23 Marcus Bointon : > 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, un= less the proxy itself is transaction-aware. > > I've built my apps with the ability to use split read/write database host= s, 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 tra= nsaction, so na=EFvely splitting select queries to read-only slaves will br= eak very badly. > > I do something similar when I need to maintain additional connections out= side of a transaction - imagine you have a long-running transaction with ma= ny queries, but you want to make its progress available via the database. Y= ou can't do that from inside the transaction because the writes are not vis= ible until the whole transaction is committed, and updates inside the trans= action are not visible from outside, so you must maintain a sparate write c= onnection 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 nev= er encountered any database abstraction / ORM that takes either of these in= to account. > > Marcus > -- > Marcus Bointon > Synchromedia Limited: Creators of http://www.smartmessages.net/ > UK info@hand CRM solutions > marcus@stripped | http://www.synchromedia.co.uk/ > I've only said that it has an impact on the server-side, on many aspects application-level,networking-level,security-aspects (for example, using a proxy you can allow access to slaves only from the proxy,just for one example), or if you have many web servers adding a new slave node requires some configuration on all of them. Having a proxy in the middle just simplify it: you don't have to change the application and you can manage the proxy with less constraints. You are true about transactions. Thanks for your answer. --=20 /*************/ nik600 http://www.kumbe.it