Hans Lellelid wrote:> > Hi All -> > I wanted to re-open this discussion. I'm currently working with the> various 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 stuff> a bit. I wanted to provide some information about what I'm currently> thinking of and get someone (Moritz?) to provide a unified set of> requirements for good replication support -- with the requirement that> it not cause excessive complexity or performance penalties to those who> don't need the replication support. Ideally, I'd also not like to have> the replication support affect the OM build.> > So, currently in the works is a system like this (this is mostly code by> Christian):> > - Propel configuration parsing will look for the presence of <slave>> connection configurations in the runtime configuration file. If they> are present, the ReplicationPDO class will be used (instead of the> default, PropelPDO). ReplicationPDO delegates read queries (SELECT> statements) to a *random* slave. The slave connection is only actually> initialized when requested. The SlavePDO class is used for the slave> connections (when they're initialized); this class overrides methods to> ensure 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 the> slave connections. It is worth noting that the generated code requires> a PropelPDO object (for nested transaction support), so the custom class> will 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)? -- Maybe> compromise would be to start with a random slave and then loop over> them. I don't know that calling rand() for every SELECT statement is a> big 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 this> be allowed? I guess my concern is that by checking the SQL for 'SELECT'> (but not 'SELECT INTO'), we are not comprehensively eliminating update> statements --- e.g. "select sp_insert_into_my_table('foo', 'bar')". I> don't think there's any good way for the ReplicationPDO to be able to> guess, based on the SQL, which connection should be used. For that> reason, I like Mortiz's suggestion of having this be explicitly> requested 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 you> could also fetch a slave connection if you know that you're performing a> SQL query. Putting this into the hands of the programmer, is probably> wise, no?> - From Mortiz's description below, is it safe to assume that for systems> that don't care about replication Propel::getConnection() would always> be returning a master connection -- regardless of whether a slave was> requested?> > I've created an empty wiki page to hold the finalized version of this: > http://propel.phpdb.​org/trac/wiki/Develo​pment/ReplicationSup​port> I'm looking for volunteer(s) to help distill this discussion into a set> of basic requirements and help me plan (and test) this implementation. > I definitely like what I see below (and think I understand it); I just> want to make sure everyone is in agreement. Also, I have not used db> replication at all, so I look for wiser input on what application> support for this should look like. The important thing from my> perspective, as mentioned earlier, is that it not introduce complexity> or penalty for those not using replication -- and also that it not break> the current API. I don't see anything below that would do either, but> just 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>>> > --------------------​--------------------​--------------------​---------> To unsubscribe, e-mail: dev-unsubscribe@prop​el.tigris.org> For additional commands, e-mail: dev-help at propel dot tigris dot org> > >