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 Moritz Mertinkat <moritz at mertinkat dot net>
Full name Moritz Mertinkat <moritz at mertinkat dot net>
Date 2007-12-04 12:11:38 PST
Message 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
>

« Previous message in topic | 11 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: