Login | Register
My pages Projects Community openCollabNet

Discussions > dev > Re: [propel-dev] r/w splitting in master-slave db replication environment

propel
Discussion topic

Back to topic list

Re: [propel-dev] r/w splitting in master-slave db replication environment

Reply

Author hlellelid
Full name Hans Lellelid
Date 2007-12-04 13:22:07 PST
Message This all looks awesome. I'll let Christian digest / comment on these
changes and commit something that everyone can agree on (at least as a
good starting point). As I personally haven't [yet] had a need for this
or even had any experience setting this up, I'll defer to you experts.

My only reminder is that we make sure it's a system that's general
enough to apply to solutions beyond a single RDBMS (e.g. MySQL). I
think everyone is thinking of that, though.

Hans

Shane Langley wrote:
> 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
>

« Previous message in topic | 13 of 25 | Next message in topic »

Messages

Show all messages in topic

r/w splitting in master-slave db replication environment Christian Abegg <abegg dot ch at gmail dot com> Christian Abegg <abegg dot ch at gmail dot com> 2007-11-02 13:09:28 PDT
     Re: [propel-dev] r/w splitting in master-slave db replication environment Cameron Brunner <cameron dot brunner at gmail dot com> Cameron Brunner <cameron dot brunner at gmail dot com> 2007-11-02 17:23:10 PDT
         Re: [propel-dev] r/w splitting in master-slave db replication environment Christian Abegg <abegg dot ch at gmail dot com> Christian Abegg <abegg dot ch at gmail dot com> 2007-11-18 08:59:06 PST
             Re: [propel-dev] r/w splitting in master-slave db replication environment Christian Abegg <abegg dot ch at gmail dot com> Christian Abegg <abegg dot ch at gmail dot com> 2007-12-02 06:26:12 PST
                 Re: [propel-dev] r/w splitting in master-slave db replication environment hlellelid Hans Lellelid 2007-12-02 17:22:35 PST
                     Re: [propel-dev] r/w splitting in master-slave db replication environment Christian Abegg <abegg dot ch at gmail dot com> Christian Abegg <abegg dot ch at gmail dot com> 2007-12-02 23:29:59 PST
                         Re: [propel-dev] r/w splitting in master-slave db replication environment hlellelid Hans Lellelid 2007-12-03 03:54:52 PST
                         Re: Re: [propel-dev] r/w splitting in master-slave db replication environment Moritz Mertinkat <moritz at mertinkat dot net> Moritz Mertinkat <moritz at mertinkat dot net> 2007-12-04 06:48:31 PST
                             Re: Re: [propel-dev] r/w splitting in master-slave db replication environment Pedram Nimreezi <zenstyle at gmail dot com> Pedram Nimreezi <zenstyle at gmail dot com> 2007-12-04 07:40:31 PST
                             Re: [propel-dev] r/w splitting in master-slave db replication environment Shane Langley <shane dot langley at gmail dot com> Shane Langley <shane dot langley at gmail dot com> 2007-12-04 11:40:14 PST
                                 Re: [propel-dev] r/w splitting in master-slave db replication environment Moritz Mertinkat <moritz at mertinkat dot net> Moritz Mertinkat <moritz at mertinkat dot net> 2007-12-04 12:11:38 PST
                                     Re: [propel-dev] r/w splitting in master-slave db replication environment Shane Langley <shane dot langley at gmail dot com> Shane Langley <shane dot langley at gmail dot com> 2007-12-04 12:48:15 PST
                                         Re: [propel-dev] r/w splitting in master-slave db replication environment hlellelid Hans Lellelid 2007-12-04 13:22:07 PST
                                             Re: [propel-dev] r/w splitting in master-slave db replication environment Christian Abegg <abegg dot ch at gmail dot com> Christian Abegg <abegg dot ch at gmail dot com> 2007-12-10 13:50:26 PST
                                         Re: [propel-dev] r/w splitting in master-slave db replication environment Moritz Mertinkat <moritz at mertinkat dot net> Moritz Mertinkat <moritz at mertinkat dot net> 2007-12-04 13:24:16 PST
                                             Re: [propel-dev] r/w splitting in master-slave db replication environment hlellelid Hans Lellelid 2008-01-04 05:57:21 PST
                                                 Re: [propel-dev] r/w splitting in master-slave db replication environment Christian Abegg <abegg dot ch at gmail dot com> Christian Abegg <abegg dot ch at gmail dot com> 2008-01-06 00:45:46 PST
                                                     Re: [propel-dev] r/w splitting in master-slave db replication environment hlellelid Hans Lellelid 2008-01-06 06:31:58 PST
                                                 Re: [propel-dev] r/w splitting in master-slave db replication environment =?ISO-8859-1?Q?David_Z=FClke?= <dz at bitxtender dot com> =?ISO-8859-1?Q?David_Z=FClke?= <dz at bitxtender dot com> 2008-01-07 06:46:19 PST
                                                     Re: [propel-dev] r/w splitting in master-slave db replication environment hlellelid Hans Lellelid 2008-01-07 06:47:37 PST
                                                         Re: [propel-dev] r/w splitting in master-slave db replication environment =?ISO-8859-1?Q?David_Z=FClke?= <dz at bitxtender dot com> =?ISO-8859-1?Q?David_Z=FClke?= <dz at bitxtender dot com> 2008-01-07 07:56:22 PST
                                                             Re: [propel-dev] r/w splitting in master-slave db replication environment hlellelid Hans Lellelid 2008-01-07 07:56:27 PST
                                                                 Re: [propel-dev] r/w splitting in master-slave db replication environment =?ISO-8859-1?Q?David_Z=FClke?= <dz at bitxtender dot com> =?ISO-8859-1?Q?David_Z=FClke?= <dz at bitxtender dot com> 2008-01-07 08:02:14 PST
                                                                     Re: [propel-dev] r/w splitting in master-slave db replication environment hlellelid Hans Lellelid 2008-01-07 08:06:02 PST
                                                                         Re: [propel-dev] r/w splitting in master-slave db replication environment =?ISO-8859-1?Q?David_Z=FClke?= <dz at bitxtender dot com> =?ISO-8859-1?Q?David_Z=FClke?= <dz at bitxtender dot com> 2008-01-07 08:32:56 PST
Messages per page: