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 Christian Abegg <abegg dot ch at gmail dot com>
Full name Christian Abegg <abegg dot ch at gmail dot com>
Date 2007-12-10 13:50:26 PST
Message Hi there

Thanks a lot for your feedback!

I commited changes to the following files: Propel, PropelPDO and SlavePDO.

In Propel, the 'getConnection()' function was touched. It now calls the new
initConnection function
for the master and adds the connection parameter of the slaves to the
PropelPDO.

The PropelPDO acts a the master connection and also as the connection
manager.
It seems to me, that a separat class for the connection manager would cause
too much
changes.

In case a slave connection is needed, PropelPDO initialises a connection
(using Propel::initConnection)
which is re-used on all further slave-queries.

The use of the master connection can be forced by calling
"setUseMasterConnect​ion(true);".

Unfortunately I wasn't able to get the phpunit test running(*), but I tried
it on my own project and it worked quite well.

In order to have a overviewable starting point, I left some of your
proposals out:
- forceConsistentConnection (more knowledge about other DBs needed)
- forceSingleConnection (does not have to be forced now, makes only sense in
conjunction with the function above)
- use master for reading also, in a read-only application (wouldn't be a big
deal to add)
- ConnectionMaster (tried to minimize the number of impacts on other
classes)

What needs to be done now is:
- write some specific unit tests, that verify the queries go to the right db
- check backward compatibility (run old unit tests)
- check compatibility with databases other than MySql
- document in the wiki

Is anyone able to help me with the first 3 points?

As soon as these checks have been made and any further proposals are worked
in, I'll add some documentation on the Wiki. I'll add a new chapter under
"Advanced Topics". Is that okay?


Cheers,
Christian

* although Propel works on the webserver, the phpunit tests are missing the
PDO driver, I gave up




Hans Lellelid wrote:
>
> 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
>>
>
> --------------------​--------------------​--------------------​---------
> 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​p13555142p14262596.h​tml
Sent from the propel - dev mailing list archive at Nabble.com.

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