I wanted to re-open this discussion. I'm currently working with thevarious PDO subclasses in Propel to address the need for logging, etc. As part of this, I'm also going to be refactoring the master/slave stuffa bit. I wanted to provide some information about what I'm currentlythinking of and get someone (Moritz?) to provide a unified set ofrequirements for good replication support -- with the requirement thatit not cause excessive complexity or performance penalties to those whodon't need the replication support. Ideally, I'd also not like to havethe replication support affect the OM build.

So, currently in the works is a system like this (this is mostly code byChristian):

- Propel configuration parsing will look for the presence of <slave>connection configurations in the runtime configuration file. If theyare present, the ReplicationPDO class will be used (instead of thedefault, PropelPDO). ReplicationPDO delegates read queries (SELECTstatements) to a *random* slave. The slave connection is only actuallyinitialized when requested. The SlavePDO class is used for the slaveconnections (when they're initialized); this class overrides methods toensure that it is only performing read queries.- It is also possible to override the PDO subclass by specifying a<classname> within the <connection> for the master connection or theslave connections. It is worth noting that the generated code requiresa PropelPDO object (for nested transaction support), so the custom classwill have to subclass PropelPDO.

Some questions / comments I have:- Is there a reason to use random connections for every SELECT query? Or should we iterate over slaves (if more than one)? -- Maybecompromise would be to start with a random slave and then loop overthem. I don't know that calling rand() for every SELECT statement is abig performance penalty, but it doesn't seem entirely necessary.- Is it strictly necessary to enforce READ-only in the SlavePDO? i.e.if a slave object is explicitly asked to perform an update, should thisbe allowed? I guess my concern is that by checking the SQL for 'SELECT'(but not 'SELECT INTO'), we are not comprehensively eliminating updatestatements --- e.g. "select sp_insert_into_my_table('foo', 'bar')". Idon't think there's any good way for the ReplicationPDO to be able toguess, based on the SQL, which connection should be used. For thatreason, I like Mortiz's suggestion of having this be explicitlyrequested as part of the getConnection() call -- i.e. the doSelect()methods know they can use a slave, other methods will use a master. When fetching your own connection, the default could be master, but youcould also fetch a slave connection if you know that you're performing aSQL query. Putting this into the hands of the programmer, is probablywise, no?- From Mortiz's description below, is it safe to assume that for systemsthat don't care about replication Propel::getConnection() would alwaysbe returning a master connection -- regardless of whether a slave wasrequested?

I've created an empty wiki page to hold the finalized version of this: http://propel.phpdb.​org/trac/wiki/Develo​pment/ReplicationSup​portI'm looking for volunteer(s) to help distill this discussion into a setof basic requirements and help me plan (and test) this implementation. I definitely like what I see below (and think I understand it); I justwant to make sure everyone is in agreement. Also, I have not used dbreplication at all, so I look for wiser input on what applicationsupport for this should look like. The important thing from myperspective, as mentioned earlier, is that it not introduce complexityor penalty for those not using replication -- and also that it not breakthe current API. I don't see anything below that would do either, butjust wanted to make that clear.

Thanks!Hans

Moritz Mertinkat wrote:> Yeah, you're absolutely right. CONNECTION_SELECT or CONNECTION_READ or> something like that (think i'd prefer _READ and _WRITE for being a bit> more unspecific ;-).>> Things to think about:>> 1) If a MASTER/WRITE connection is established first (and no master> connection is forced), should all subsequent SLAVE/SELECT/READ> "connections" use that established connection? Or would it be better> to establish an additional SLAVE/SELECT/READ connection?> Maybe Propel::forceSingleC​onnection(true/false​)?>> 2) Propel::forceMasterC​onnection(true/false​) should be implemented imho.>> 3) Propel::forceConsist​entConnection(true/f​alse) would be quite nice.>> Regards,> Moritz>> Shane Langley schrieb:>> I think CONNECTION_SLAVE is a bit confusing - since CONNECTION_SLAVE>> could be a connection to the master.>>>> Maybe CONNECTION_SELECT?>>>> Shane.>>>> Moritz Mertinkat wrote:>>> Hi,>>>>>> what about Propel::getConnection(..., <TYPE>) where TYPE is>>> something like this: CONNECTION_MASTER, CONNECTION_SLAVE?>>>>>> Within a doDelete method this would be called with CONNECTION_MASTER>>> whereas in a doSelect method CONNECTION_SLAVE is used.>>>>>> That way there's a maximum of two open connection (first SLAVE and>>> then MASTER). If a MASTER is opened first one may use that>>> connection also for reading (even if CONNECTION_SLAVE is used).>>> PRO: the connection is _only_ established when required.>>>>>> What it does NOT fix is the problem Shane described (forced reading>>> from>>> a MASTER). Therefore a Propel::forceMasterC​onnection(true) method may>>> be implemented.>>>>>> [A nice add-on feature might be>>> Propel::forceConsist​entConnection(true).​ When a slave connection is>>> required the slave's status is checked first and the connection will>>> only be used if the slave is up-to-date. However I do only know how>>> to do this with MySQL.]>>>>>> Regards,>>> Moritz>>>>>>>>> Shane Langley schrieb:>>>> "Another problem with random-slave-per-query might occur when>>>> you're slaves are>>>> not 100 % synchronized (i.e. some are a few seconds behind master).>>>> I think a single page view should (in general) be handled by just>>>> one slave.">>>>>>>> I agree with this. The first time a connection to a slave is>>>> required a connection should be established to one slave, and>>>> stored as the "select" connection.>>>>>>>> Subsequent selects should re-use that slave .>>>>>>>> "[Onother idea would be to tell Propel if you need read/write- or just>>>> read-access to your database. In case you only need read-access it's>>>> enough to connect _one_ a random slave (no master db is required).>>>> Maybe both ideas together would make the perfect>>>> master-slave-propel :-]">>>>>>>> The reverse works as well.>>>>>>>> For an application I worked on I created a ConnectionMananger class>>>> that>>>> handled the split between master/slave.>>>>>>>> I added a method "forceMaster()" that would ensure every query>>>> would hit the master.>>>>>>>> Some pages you want to have the most update-to-date data (when>>>> editing for example) loaded into>>>> your form. Without this method, the data would come from a slave>>>> (since the select query is not in a transaction) which could be>>>> out-of-date by a few seconds.>>>>>>>> In this case, you don't need a slave connection at all.>>>>>>>> Regards,>>>>>>>> Shane.>>>>>>>> Moritz Mertinkat wrote:>>>>> Hi everybody,>>>>>>>>>> I've just looked at the code and I think it's a great thing to add>>>>> to propel.>>>>>>>>>> What I think should be improved is that all slave connections get>>>>> connected at>>>>> the moment Propel ist loaded. That is, if you have eight MySQL>>>>> slave servers>>>>> you establish a MySQL connection to all of them, even though you>>>>> just need one>>>>> (for example). Kinda slow :)>>>>>>>>>> Wouldn't it be better to load just _one_ random slave upon starting?>>>>>>>>>> Another problem with random-slave-per-query might occur when>>>>> you're slaves are>>>>> not 100 % synchronized (i.e. some are a few seconds behind master).>>>>> I think a single page view should (in general) be handled by just>>>>> one slave.>>>>>>>>>> For those who (really) want to have a different slave each query>>>>> one might add>>>>> a flag to Propel::init or to the configuration.>>>>>>>>>> [Onother idea would be to tell Propel if you need read/write- or just>>>>> read-access to your database. In case you only need read-access it's>>>>> enough to connect _one_ a random slave (no master db is required).>>>>> Maybe both ideas together would make the perfect>>>>> master-slave-propel :-]>>>>>>>>>> Regards,>>>>> Moritz>>>>>>>>>>>>>>> Christian Abegg schrieb:>>>>> >>>>>> Hi Hans>>>>>>>>>>>> - This will work fine without master/slave replication and it>>>>>> should not>>>>>> have any impact on a existing single db setup. Although I'd be>>>>>> glad for>>>>>> further testings or code reviews.>>>>>>>>>>>> - The changes in the Propel class primarly touch the>>>>>> "getConnection" method.>>>>>> I copied some code from it to that new "initConnection" method>>>>>> which can now>>>>>> be called multiple times from the "getConnection" method (i.e.>>>>>> for the>>>>>> master and its slaves).>>>>>>>>>>>> - Of course I'll document it.>>>>>>>>>>>> Please note that I've just tried it on a mysql replication setup.>>>>>> I'd be>>>>>> happy to run some tests. Do you have a test suite?>>>>>>>>>>>> Regards,>>>>>> Christian>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Hans Lellelid wrote:>>>>>> >>>>>>> Hi Christian,>>>>>>>>>>>>>> Yes :)>>>>>>>>>>>>>> Am I right in assuming that this will work fine without a>>>>>>> master/slave>>>>>>> setup? (i.e. in traditional, single-db model?)>>>>>>>>>>>>>> I didn't drop these in to do a diff, but the PropelPDO looked>>>>>>> fairly>>>>>>> similar; Propel class too?>>>>>>>>>>>>>> I'd like to get this added in, though. I think it would be a great>>>>>>> benefit.>>>>>>>>>>>>>> Would you be willing to contribute some documentation on getting>>>>>>> this>>>>>>> setup? -- e.g. in 1.3 user guide?>>>>>>>>>>>>>> Thanks,>>>>>>> Hans>>>>>>>>>>>>>>>>>>>>> Christian Abegg wrote:>>>>>>> >>>>>​>>> hi there>>>>>​>>>>>>>>​>>> here's a refinend implementation of r/w splitting:>>>>>​>>>http://www.nabble.co​m/file/p14116000/rwS​plitting.zip>>>>>​>>> rwSplitting.zip>>>>>​>>>>>>>>​>>> anyone interested?>>>>>​>>>>>>>>​>>> regards>>>>>​>>> christian>>>>>​>>>>>>>>​>>>>>>>>​>>>>>>>>​>>> Christian Abegg wrote:>>>>>​>>>>>>>>​>>>> hi cameron, dear devs>>>>>​>>>>>>>>>​>>>> thank you for your reply. i made a first try to implement it>>>>>​>>>> the way you>>>>>​>>>> proposed:>>>>>​>>>> - the propel class initializes a PropelPDO object which acts>>>>>​>>>> as db>>>>>​>>>> connection to the master server>>>>>​>>>> - the PropelPDO object also holds an array of other PDO>>>>>​>>>> connections used>>>>>​>>>> for read only queries>>>>>​>>>>>>>>>​>>>> pro: realtively simple to implement, building up on the>>>>>​>>>> existing code>>>>>​>>>> contra: PropelPDO extending PDO is no more used as a singe db>>>>>​>>>> connection>>>>>​>>>>>>>>>​>>>> have a look at the two patches attached. they are a very first>>>>>​>>>> draft>>>>>​>>>> showing the way I would choose.>>>>>​>>>>http://www.nabble.co​m/file/p13820966/Pro​pel.diff Propel.diff>>>>>​>>>>http://www.nabble.co​m/file/p13820966/Pro​pelPDO.diff>>>>>​>>>> PropelPDO.diff>>>>>​>>>>>>>>>​>>>> please let me know if you'd appreciate any further work on>>>>>​>>>> that matter>>>>>​>>>> and>>>>>​>>>> if there are architectural changes/constraints to be considered.>>>>>​>>>>>>>>>​>>>> cheers>>>>>​>>>> christian>>>>>​>>>>>>>>>​>>>>>>>>>​>>>> Cameron Brunner wrote:>>>>>​>>>>>>>>>​>>>>>​ There is no reason this cant be done purely by slotting in a new>>>>>​>>>>>​ PropelPDO layer with intelligence on what to send the query>>>>>​>>>>>​ to IMO. I>>>>>​>>>>>​ have thought this out before and it shouldn't be too painful>>>>>​>>>>>​ depending>>>>>​>>>>>​ upon just how smart you want it.>>>>>​>>>>>​>>>>>​>>>>>​ On 11/3/07, Christian Abegg <abegg dot ch at gmail dot com> wrote:>>>>>​>>>>>​>>>>>​>>>>>​> hi>>>>>​>>>>>​>>>>>>​>>>>>​> i'd like to use my propel app in an environment where the>>>>>​>>>>>​> db-master is>>>>>​>>>>>​> replicated to one or more slave databases. the master gets>>>>>​>>>>>​> the writing>>>>>​>>>>>​> statements, the slave gets the reading statements.>>>>>​>>>>>​>>>>>>​>>>>>​> in my opinion, the propel layer would be appropriate>>>>>​>>>>>​> implement that>>>>>​>>>>>​> function.>>>>>​>>>>>​>>>>>>​>>>>>​> as far as i see, there is no such feature in propel.>>>>>​>>>>>​>>>>>>​>>>>>​> after a short look into the generated base-classes, it seems>>>>>​>>>>>​> like a>>>>>​>>>>>​> quite an easy task to implement a read-write splitting.>>>>>​>>>>>​> assuming that>>>>>​>>>>>​> all reading queries call the "doSelectStmt" function, i'd>>>>>​>>>>>​> introduce>>>>>​>>>>>​> the>>>>>​>>>>>​> DATABASE_NAME_READ-constant which points to the>>>>>​>>>>>​> configuration of the>>>>>​>>>>>​> slave-db.>>>>>​>>>>>​>>>>>>​>>>>>​> for load balancing between multiple slave-db's i'd use the>>>>>​>>>>>​> mysql-proxy.>>>>>​>>>>>​> but a simple round robin mechanism could be implemented in>>>>>​>>>>>​> propel as>>>>>​>>>>>​> well.>>>>>​>>>>>​>>>>>>​>>>>>​> i'm not sure wheter this approach could solve my problem. a big>>>>>​>>>>>​> question>>>>>​>>>>>​> is: are the connection objects cached by propel? or is the>>>>>​>>>>>​> $con-variable>>>>>​>>>>>​> always null if the user doesn't give a connection on the>>>>>​>>>>>​> application>>>>>​>>>>>​> layer?>>>>>​>>>>>​>>>>>>​>>>>>​> if that enhancement is considered usefull and could be>>>>>​>>>>>​> implemented>>>>>​>>>>>​> within reasonable time, i'd be glad to help.>>>>>​>>>>>​>>>>>>​>>>>>​> christian abegg>>>>>​>>>>>​>>>>>>​>>>>>​>>>>>>​>>>>>​> ps: my attempts to add an enhancement ticket failed beacaus>>>>>​>>>>>​> they were>>>>>​>>>>>​> rejected as spam by trac.>>>>>​>>>>>​>>>>>>​>>>>>​>--------------------​--------------------​--------------------​--------->>>>>​>>>>>​>>>>>>​>>>>>​> To unsubscribe, e-mail: dev-unsubscribe@prop​el.tigris.org>>>>>​>>>>>​> For additional commands, e-mail: dev-help at propel dot tigris dot org>>>>>​>>>>>​>>>>>>​>>>>>​>>>>>>​>>>>>​>>>>>>​>>>>>​ -- >>>>>​>>>>>​ Cameron Brunner>>>>>​>>>>>​>>>>>​>>>>>​ Want a better web browser?>>>>>​>>>>>​http://www.spreadfir​efox.com/?q=affiliat​es&id=182780​&t=1>>>>>​>>>>>​>>>>>​>>>>>​--------------------​--------------------​--------------------​--------->>>>>​>>>>>​>>>>>​>>>>>​ To unsubscribe, e-mail: dev-unsubscribe@prop​el.tigris.org>>>>>​>>>>>​ For additional commands, e-mail: dev-help at propel dot tigris dot org>>>>>​>>>>>​>>>>>​>>>>>​>>>>>​>>>>>​>>>>>​>>>>>​>>>>>>> --------------------​--------------------​--------------------​--------->>>>>>>>>>>>>> To unsubscribe, e-mail: dev-unsubscribe@prop​el.tigris.org>>>>>>> For additional commands, e-mail: dev-help at propel dot tigris dot org>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>> -- >>>>>> View this message in context:>>>>>> http://www.nabble.co​m/r-w-splitting-in-m​aster-slave-db-repli​cation-environment-t​f4740128.html#a14124​959>>>>>>>>>>>> Sent from the propel - dev mailing list archive at Nabble.com.>>>>>>>>>>>>>>>>>> >>>>>>>>>> --------------------​--------------------​--------------------​--------->>>>> To unsubscribe, e-mail: dev-unsubscribe@prop​el.tigris.org>>>>> For additional commands, e-mail: dev-help at propel dot tigris dot org>>>>>>>>>>>>>>> >>>>>>>> --------------------​--------------------​--------------------​--------->>>> To unsubscribe, e-mail: dev-unsubscribe@prop​el.tigris.org>>>> For additional commands, e-mail: dev-help at propel dot tigris dot org>>>>>>>>>> --------------------​--------------------​--------------------​--------->>> To unsubscribe, e-mail: dev-unsubscribe@prop​el.tigris.org>>> For additional commands, e-mail: dev-help at propel dot tigris dot org>>>>>>>>>> --------------------​--------------------​--------------------​--------->> To unsubscribe, e-mail: dev-unsubscribe@prop​el.tigris.org>> For additional commands, e-mail: dev-help at propel dot tigris dot org>>>> --------------------​--------------------​--------------------​---------> To unsubscribe, e-mail: dev-unsubscribe@prop​el.tigris.org> For additional commands, e-mail: dev-help at propel dot tigris dot org>