Original message

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>