Login | Register
My pages Projects Community openCollabNet

Discussions > dev > r/w splitting in master-slave db replication environment

propel
Discussion topic

Hide all messages in topic

All messages in topic

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

Reply

Author =?ISO-8859-1?Q?David_Z=FClke?= <dz at bitxtender dot com>
Full name =?ISO-8859-1?Q?David_Z=FClke?= <dz at bitxtender dot com>
Date 2008-01-07 08:32:56 PST
Message okay, I agree, that should be sufficient for every situation

David




Am 07.01.2008 um 17:06 schrieb Hans Lellelid:

> Yeah, I think it's simpler and safer to let the type be specified
> explicitly. It's just impossible to tell when stored procedures are
> involved. We alreay have doSelect(), doUpdate(), etc. statements,
> so those methods may as well specify explicitly the type of
> connection they need. For user-defined methods / custom SQL, we'll
> let the users specify the connection type. Default would be master,
> though, which should keep backwards compatibility.
>
> Hans
>
> David Zülke wrote:
>> Hmmm... sure? AFAIK, that's just what SQLRelay, MySQLProxy et al
>> do... But yeah, you might have a point.
>> David
>> Am 07.01.2008 um 16:56 schrieb Hans Lellelid:
>>> Ok, I think I understand how this would work. One change, though,
>>> is that I'd like to not have any SQL-parsing done by Propel to
>>> determine which connection to use. I think the connection type
>>> (master/write or slave/read) needs to be explicitly requested by
>>> the generated peer classes. There's no good way to determine
>>> whether a SQL statement represents a read or a write operation.
>>>
>>> Hans
>>>
>>> David Zülke wrote:
>>>> No, Propel uses an instance of the router to determine which
>>>> connection to write to. That is done by calling a method on the
>>>> router, along with the SQL query. The router could then decide
>>>> which actual connection to return. In Propels default
>>>> implementation this would, unconditionally, be a random slave for
>>>> any SELECT; for everything else, it would be the master connection.
>>>>
>>>>
>>>> David
>>>>
>>>>
>>>>
>>>> Am 07.01.2008 um 15:47 schrieb Hans Lellelid:
>>>>
>>>>> I'm not sure I understand how they fit in to the big picture,
>>>>> though. Does PropelPDO implement PropelReplicationRouter? Or
>>>>> are these separate objects -- and if so, it seems like this
>>>>> would be a breaking API change?
>>>>>
>>>>> Hans
>>>>>
>>>>> David Zülke wrote:
>>>>>> An idea...
>>>>>> interface PropelReplicationRouter { /* with some stuff */ }
>>>>>>
>>>>>> Such an implementation is used to determine where to write to.
>>>>>> We have a default one that shoots SELECTs to a random slave,
>>>>>> rest to a master. It's not a static class, so instead of
>>>>>> setting a class name in the configuration, a user could also
>>>>>> provide an existing instance (because, for instance, he needed
>>>>>> to initialize it with whatever information first).
>>>>>>
>>>>>> Thoughts?
>>>>>>
>>>>>>
>>>>>> David
>>>>>>
>>>>>>
>>>>>>
>>>>>> Am 04.01.2008 um 14:57 schrieb Hans Lellelid:
>>>>>>
>>>>>>> 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
>>>>>>>
>>>>>>>
>>>>>>
>>>>>> --------------------​--------------------​--------------------​---------
>>>>>> 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
>
>

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

Reply

Author hlellelid
Full name Hans Lellelid
Date 2008-01-07 08:06:02 PST
Message Yeah, I think it's simpler and safer to let the type be specified
explicitly. It's just impossible to tell when stored procedures are
involved. We alreay have doSelect(), doUpdate(), etc. statements, so
those methods may as well specify explicitly the type of connection they
need. For user-defined methods / custom SQL, we'll let the users
specify the connection type. Default would be master, though, which
should keep backwards compatibility.

Hans

David Zülke wrote:
> Hmmm... sure? AFAIK, that's just what SQLRelay, MySQLProxy et al do...
> But yeah, you might have a point.
>
>
> David
>
>
>
> Am 07.01.2008 um 16:56 schrieb Hans Lellelid:
>
>> Ok, I think I understand how this would work. One change, though, is
>> that I'd like to not have any SQL-parsing done by Propel to determine
>> which connection to use. I think the connection type (master/write or
>> slave/read) needs to be explicitly requested by the generated peer
>> classes. There's no good way to determine whether a SQL statement
>> represents a read or a write operation.
>>
>> Hans
>>
>> David Zülke wrote:
>>> No, Propel uses an instance of the router to determine which
>>> connection to write to. That is done by calling a method on the
>>> router, along with the SQL query. The router could then decide which
>>> actual connection to return. In Propels default implementation this
>>> would, unconditionally, be a random slave for any SELECT; for
>>> everything else, it would be the master connection.
>>>
>>>
>>> David
>>>
>>>
>>>
>>> Am 07.01.2008 um 15:47 schrieb Hans Lellelid:
>>>
>>>> I'm not sure I understand how they fit in to the big picture,
>>>> though. Does PropelPDO implement PropelReplicationRouter? Or are
>>>> these separate objects -- and if so, it seems like this would be a
>>>> breaking API change?
>>>>
>>>> Hans
>>>>
>>>> David Zülke wrote:
>>>>> An idea...
>>>>> interface PropelReplicationRouter { /* with some stuff */ }
>>>>>
>>>>> Such an implementation is used to determine where to write to. We
>>>>> have a default one that shoots SELECTs to a random slave, rest to a
>>>>> master. It's not a static class, so instead of setting a class name
>>>>> in the configuration, a user could also provide an existing
>>>>> instance (because, for instance, he needed to initialize it with
>>>>> whatever information first).
>>>>>
>>>>> Thoughts?
>>>>>
>>>>>
>>>>> David
>>>>>
>>>>>
>>>>>
>>>>> Am 04.01.2008 um 14:57 schrieb Hans Lellelid:
>>>>>
>>>>>> 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
>>>>>>
>>>>>>
>>>>>
>>>>> --------------------​--------------------​--------------------​---------
>>>>> 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
>

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

Reply

Author =?ISO-8859-1?Q?David_Z=FClke?= <dz at bitxtender dot com>
Full name =?ISO-8859-1?Q?David_Z=FClke?= <dz at bitxtender dot com>
Date 2008-01-07 08:02:14 PST
Message Hmmm... sure? AFAIK, that's just what SQLRelay, MySQLProxy et al do...
But yeah, you might have a point.


David



Am 07.01.2008 um 16:56 schrieb Hans Lellelid:

> Ok, I think I understand how this would work. One change, though,
> is that I'd like to not have any SQL-parsing done by Propel to
> determine which connection to use. I think the connection type
> (master/write or slave/read) needs to be explicitly requested by the
> generated peer classes. There's no good way to determine whether a
> SQL statement represents a read or a write operation.
>
> Hans
>
> David Zülke wrote:
>> No, Propel uses an instance of the router to determine which
>> connection to write to. That is done by calling a method on the
>> router, along with the SQL query. The router could then decide
>> which actual connection to return. In Propels default
>> implementation this would, unconditionally, be a random slave for
>> any SELECT; for everything else, it would be the master connection.
>>
>>
>> David
>>
>>
>>
>> Am 07.01.2008 um 15:47 schrieb Hans Lellelid:
>>
>>> I'm not sure I understand how they fit in to the big picture,
>>> though. Does PropelPDO implement PropelReplicationRouter? Or are
>>> these separate objects -- and if so, it seems like this would be a
>>> breaking API change?
>>>
>>> Hans
>>>
>>> David Zülke wrote:
>>>> An idea...
>>>> interface PropelReplicationRouter { /* with some stuff */ }
>>>>
>>>> Such an implementation is used to determine where to write to. We
>>>> have a default one that shoots SELECTs to a random slave, rest to
>>>> a master. It's not a static class, so instead of setting a class
>>>> name in the configuration, a user could also provide an existing
>>>> instance (because, for instance, he needed to initialize it with
>>>> whatever information first).
>>>>
>>>> Thoughts?
>>>>
>>>>
>>>> David
>>>>
>>>>
>>>>
>>>> Am 04.01.2008 um 14:57 schrieb Hans Lellelid:
>>>>
>>>>> 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 at propel dot tigris dot 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
>>>>>
>>>>>
>>>>
>>>> --------------------​--------------------​--------------------​---------
>>>> 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
>
>

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

Reply

Author hlellelid
Full name Hans Lellelid
Date 2008-01-07 07:56:27 PST
Message Ok, I think I understand how this would work. One change, though, is
that I'd like to not have any SQL-parsing done by Propel to determine
which connection to use. I think the connection type (master/write or
slave/read) needs to be explicitly requested by the generated peer
classes. There's no good way to determine whether a SQL statement
represents a read or a write operation.

Hans

David Zülke wrote:
> No, Propel uses an instance of the router to determine which
> connection to write to. That is done by calling a method on the
> router, along with the SQL query. The router could then decide which
> actual connection to return. In Propels default implementation this
> would, unconditionally, be a random slave for any SELECT; for
> everything else, it would be the master connection.
>
>
> David
>
>
>
> Am 07.01.2008 um 15:47 schrieb Hans Lellelid:
>
>> I'm not sure I understand how they fit in to the big picture,
>> though. Does PropelPDO implement PropelReplicationRouter? Or are
>> these separate objects -- and if so, it seems like this would be a
>> breaking API change?
>>
>> Hans
>>
>> David Zülke wrote:
>>> An idea...
>>> interface PropelReplicationRouter { /* with some stuff */ }
>>>
>>> Such an implementation is used to determine where to write to. We
>>> have a default one that shoots SELECTs to a random slave, rest to a
>>> master. It's not a static class, so instead of setting a class name
>>> in the configuration, a user could also provide an existing instance
>>> (because, for instance, he needed to initialize it with whatever
>>> information first).
>>>
>>> Thoughts?
>>>
>>>
>>> David
>>>
>>>
>>>
>>> Am 04.01.2008 um 14:57 schrieb Hans Lellelid:
>>>
>>>> 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
>>>>
>>>>
>>>
>>> --------------------​--------------------​--------------------​---------
>>> 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
>

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

Reply

Author =?ISO-8859-1?Q?David_Z=FClke?= <dz at bitxtender dot com>
Full name =?ISO-8859-1?Q?David_Z=FClke?= <dz at bitxtender dot com>
Date 2008-01-07 07:56:22 PST
Message No, Propel uses an instance of the router to determine which
connection to write to. That is done by calling a method on the
router, along with the SQL query. The router could then decide which
actual connection to return. In Propels default implementation this
would, unconditionally, be a random slave for any SELECT; for
everything else, it would be the master connection.


David



Am 07.01.2008 um 15:47 schrieb Hans Lellelid:

> I'm not sure I understand how they fit in to the big picture,
> though. Does PropelPDO implement PropelReplicationRouter? Or are
> these separate objects -- and if so, it seems like this would be a
> breaking API change?
>
> Hans
>
> David Zülke wrote:
>> An idea...
>> interface PropelReplicationRouter { /* with some stuff */ }
>>
>> Such an implementation is used to determine where to write to. We
>> have a default one that shoots SELECTs to a random slave, rest to a
>> master. It's not a static class, so instead of setting a class name
>> in the configuration, a user could also provide an existing
>> instance (because, for instance, he needed to initialize it with
>> whatever information first).
>>
>> Thoughts?
>>
>>
>> David
>>
>>
>>
>> Am 04.01.2008 um 14:57 schrieb Hans Lellelid:
>>
>>> 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
>>>
>>>
>>
>> --------------------​--------------------​--------------------​---------
>> 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
>
>

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

Reply

Author hlellelid
Full name Hans Lellelid
Date 2008-01-07 06:47:37 PST
Message I'm not sure I understand how they fit in to the big picture, though.
Does PropelPDO implement PropelReplicationRouter? Or are these separate
objects -- and if so, it seems like this would be a breaking API change?

Hans

David Zülke wrote:
> An idea...
> interface PropelReplicationRouter { /* with some stuff */ }
>
> Such an implementation is used to determine where to write to. We have
> a default one that shoots SELECTs to a random slave, rest to a master.
> It's not a static class, so instead of setting a class name in the
> configuration, a user could also provide an existing instance
> (because, for instance, he needed to initialize it with whatever
> information first).
>
> Thoughts?
>
>
> David
>
>
>
> Am 04.01.2008 um 14:57 schrieb Hans Lellelid:
>
>> 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
>>
>>
>
> --------------------​--------------------​--------------------​---------
> To unsubscribe, e-mail: dev-unsubscribe@prop​el.tigris.org
> For additional commands, e-mail: dev-help at propel dot tigris dot org
>

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

Reply

Author =?ISO-8859-1?Q?David_Z=FClke?= <dz at bitxtender dot com>
Full name =?ISO-8859-1?Q?David_Z=FClke?= <dz at bitxtender dot com>
Date 2008-01-07 06:46:19 PST
Message An idea...
interface PropelReplicationRouter { /* with some stuff */ }

Such an implementation is used to determine where to write to. We have
a default one that shoots SELECTs to a random slave, rest to a master.
It's not a static class, so instead of setting a class name in the
configuration, a user could also provide an existing instance
(because, for instance, he needed to initialize it with whatever
information first).

Thoughts?


David



Am 04.01.2008 um 14:57 schrieb Hans Lellelid:

> 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
>
>

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

Reply

Author hlellelid
Full name Hans Lellelid
Date 2008-01-06 06:31:58 PST
Message Hi Christian,

Thanks for putting that up -- and the comments. I think I understand a
bit better. Over the next few days, I'm going to finalize this API
work. You are correct that having the connection_read selected in the
doSelect method would alter the API, but it shouldn't change the public
API at all. And I assume that if someone has overridden the doSelect
method, and as a result does not specify a slave connection, that they
will just get a master connection -- which won't cause anything to break.

It seems that having the master connection always be the default is a
safe situation.

I will look at modifying the tutorial also when I make the changes. I
think on the whole that will be the same, although we'll want to iron
out what the XML looks like for the runtime-conf.xml -- since we don't
want people to have to modify the generated php conf file.

Thanks-
Hans

Christian Abegg wrote:
> hi
>
> i copied your post the the wiki and added my comments there.
> (http://propel.phpdb.​org/trac/wiki/Develo​pment/ReplicationSup​port)
>
> @hans: is the replication tutorial in the wiki
> (http://propel.phpdb.​org/trac/wiki/Users/​Documentation/1.3/Re​plication)
> still correct? my attention is especially on the slave/slaves element in the
> configuration, but maybe there are other things as well. could you have a
> look at it?
>
> thanks,
> christian
>
>
> 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
>>
>>
>>
>>
>
>

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 2008-01-06 00:45:46 PST
Message hi

i copied your post the the wiki and added my comments there.
(http://propel.phpdb.​org/trac/wiki/Develo​pment/ReplicationSup​port)

@hans: is the replication tutorial in the wiki
(http://propel.phpdb.​org/trac/wiki/Users/​Documentation/1.3/Re​plication)
still correct? my attention is especially on the slave/slaves element in the
configuration, but maybe there are other things as well. could you have a
look at it?

thanks,
christian


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
>
>
>

--
View this message in context: http://www.nabble.co​m/r-w-splitting-in-m​aster-slave-db-repli​cation-environment-t​p13555142p14645394.h​tml
Sent from the propel - dev mailing list archive at Nabble.com.

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

Reply

Author hlellelid
Full name Hans Lellelid
Date 2008-01-04 05:57:21 PST
Message 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
>

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.

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 13:24:16 PST
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
>

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
>

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

Reply

Author Shane Langley <shane dot langley at gmail dot com>
Full name Shane Langley <shane dot langley at gmail dot com>
Date 2007-12-04 12:48:15 PST
Message 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
>
>

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
>

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

Reply

Author Shane Langley <shane dot langley at gmail dot com>
Full name Shane Langley <shane dot langley at gmail dot com>
Date 2007-12-04 11:40:14 PST
Message "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
>
>
>

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

Reply

Author Pedram Nimreezi <zenstyle at gmail dot com>
Full name Pedram Nimreezi <zenstyle at gmail dot com>
Date 2007-12-04 07:40:31 PST
Message I'm sure any ideas you've gathered in your career in web farming are
appreciated...

At the very least by one person ;)

On Dec 4, 2007 9:48 AM, Moritz Mertinkat <moritz at mertinkat dot net> 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
>
>


--
~
Pedram Nimreezi
Senior Programmer / Frameworkologist
mc at majorcomputing dot com | pedram at 5g dot com
--


"A common mistake that people make when trying to design something
completely foolproof is to underestimate the ingenuity of complete fools." -
Douglas Adams
Attachments

Re: 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 06:48:31 PST
Message 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.
>
>

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

Reply

Author hlellelid
Full name Hans Lellelid
Date 2007-12-03 03:54:52 PST
Message Hi Christian,

Yes -- we do have a basic test suite. Essentially, you configure the
bookstore application for your datasource, build, then run the unit
tests. Here are more thorough instructions:

http://propel.phpdb.​org/trac/wiki/Develo​pment/Guidelines/Wri​tingUnitTests

Why don't you contact me off-list with a desired username and I can get
you an SVN + Trac account (if you don't have one already).

Thanks,
Hans

Christian Abegg wrote:
> 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
>>
>>
>>
>

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-02 23:29:59 PST
Message 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.

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

Reply

Author hlellelid
Full name Hans Lellelid
Date 2007-12-02 17:22:35 PST
Message 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
>>>
>>>
>>>
>>
>

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-02 06:26:12 PST
Message 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
>>
>>
>>
>
>

--
View this message in context: http://www.nabble.co​m/r-w-splitting-in-m​aster-slave-db-repli​cation-environment-t​f4740128.html#a14116​000
Sent from the propel - dev mailing list archive at Nabble.com.

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-11-18 08:59:06 PST
Message 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
>
>
>

--
View this message in context: http://www.nabble.co​m/r-w-splitting-in-m​aster-slave-db-repli​cation-environment-t​f4740128.html#a13820​966
Sent from the propel - dev mailing list archive at Nabble.com.

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

Reply

Author Cameron Brunner <cameron dot brunner at gmail dot com>
Full name Cameron Brunner <cameron dot brunner at gmail dot com>
Date 2007-11-02 17:23:10 PDT
Message 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

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-11-02 13:09:28 PDT
Message 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.
Messages per page: