Configuration for multiple databases
0 views
Skip to first unread message
Brian Pickens
May 26, 2009, 12:54:08 PM5/26/09
to Reactor
Hello,
Im new to Reactor. I've been going through the documentation,
particularly the 'getting started' section. My question has to do with
configuring reactor to support multiple databases within one dsn. Now,
our database system is set up like so:
We have two data source names, 'read', and 'write'. On our development
dbms these point to the same system, but on our live dbms we have two
separate systems, one meant only for any type of read operation, and
the other meant for any type of write operations. So these two
datasource names, not only being separate for the different type of
operation being performed, have multiple databases within which our
queries need to specifiy. For example we could have a DB_users
database, with a user table inside along with some other tables, also
maybe a DB_services db with its own plethora of tables all being
accessed by the one datasource 'read', or if we are writing, 'write'.
Is it possible to configure reactor for this scenario, and if so are
there any directions out there?
PS, this is a mysql 5.+ dbms
Im new to Reactor. I've been going through the documentation,
particularly the 'getting started' section. My question has to do with
configuring reactor to support multiple databases within one dsn. Now,
our database system is set up like so:
We have two data source names, 'read', and 'write'. On our development
dbms these point to the same system, but on our live dbms we have two
separate systems, one meant only for any type of read operation, and
the other meant for any type of write operations. So these two
datasource names, not only being separate for the different type of
operation being performed, have multiple databases within which our
queries need to specifiy. For example we could have a DB_users
database, with a user table inside along with some other tables, also
maybe a DB_services db with its own plethora of tables all being
accessed by the one datasource 'read', or if we are writing, 'write'.
Is it possible to configure reactor for this scenario, and if so are
there any directions out there?
PS, this is a mysql 5.+ dbms
Chris Blackwell
May 26, 2009, 4:36:04 PM5/26/09
to reacto...@googlegroups.com
You would need an instance of Reactor for each database and dsn, so given your example you'd need 4 instances
users_read
users_write
services_read
services_write
Because of the way Reactor works (kind of active record-ish) you couldn't read a record from your read dsn and save it using the write dsn. Record.save() will save back to wherever it was read from.
You may want to look at Transfer for this as i believe (don't quote me) that this should be possible
I doubt that helps, but hopefully it answers your question
Out of interest is the read/write setup to help balance load, with the read mysql instance being a slave to the write? Just wondered if you'd considered circular replication (or master-master) that way you can read/write from either and just have your app balance the load between the two and you don't need to explicitly target your read operations at a particular dsn... just a thought. I've used circular replication recently and i got all excited about it :)
Chris
2009/5/26 Brian Pickens <brian....@fccinteractive.com>
Brian Pickens
May 27, 2009, 9:42:34 AM5/27/09
to Reactor
Yeah, thats what I was afraid of. Our db system simply replicates the
write db over to the read db. Our system guys are the ones who manages
that. But thanks for the reply, I appreciate that.
On May 26, 3:36 pm, Chris Blackwell <ch...@team193.com> wrote:
> You would need an instance of Reactor for each database and dsn, so given
> your example you'd need 4 instances
> users_read
> users_write
> services_read
> services_write
>
> Because of the way Reactor works (kind of active record-ish) you couldn't
> read a record from your read dsn and save it using the write dsn.
> Record.save() will save back to wherever it was read from.
>
> You may want to look at Transfer for this as i believe (don't quote me) that
> this should be possible
>
> I doubt that helps, but hopefully it answers your question
>
> Out of interest is the read/write setup to help balance load, with the read
> mysql instance being a slave to the write? Just wondered if you'd
> considered circular replication (or master-master) that way you can
> read/write from either and just have your app balance the load between the
> two and you don't need to explicitly target your read operations at a
> particular dsn... just a thought. I've used circular replication recently
> and i got all excited about it :)
>
> Chris
>
> 2009/5/26 Brian Pickens <brian.pick...@fccinteractive.com>
write db over to the read db. Our system guys are the ones who manages
that. But thanks for the reply, I appreciate that.
On May 26, 3:36 pm, Chris Blackwell <ch...@team193.com> wrote:
> You would need an instance of Reactor for each database and dsn, so given
> your example you'd need 4 instances
> users_read
> users_write
> services_read
> services_write
>
> Because of the way Reactor works (kind of active record-ish) you couldn't
> read a record from your read dsn and save it using the write dsn.
> Record.save() will save back to wherever it was read from.
>
> You may want to look at Transfer for this as i believe (don't quote me) that
> this should be possible
>
> I doubt that helps, but hopefully it answers your question
>
> Out of interest is the read/write setup to help balance load, with the read
> mysql instance being a slave to the write? Just wondered if you'd
> considered circular replication (or master-master) that way you can
> read/write from either and just have your app balance the load between the
> two and you don't need to explicitly target your read operations at a
> particular dsn... just a thought. I've used circular replication recently
> and i got all excited about it :)
>
> Chris
>
Brian Pickens
May 27, 2009, 10:37:09 AM5/27/09
to Reactor
Thanks chris, thats pretty much what I suspected. That circular
replication sounds useful, but we've got a competent team of systems
guys and Im sure they set up our dbms this way for a good reason. And
by the way, I already looked into transfer and it has the same
limitations which are support for only a single datasource, and the
assumption that a datasource points to a database rather than the
system.
@Doug Hughes
I created tickets in track for two features I feel would add some
flexibility to reactor to be used with more dbms configurations.
Obviously you choose the path reactor will take, so do with those
suggestions what you will. They are ticket numbers 196 and 197
respectively. Due to our current dbms configuration we won't be able
to use reactor but I hope this will change in the future. :)
replication sounds useful, but we've got a competent team of systems
guys and Im sure they set up our dbms this way for a good reason. And
by the way, I already looked into transfer and it has the same
limitations which are support for only a single datasource, and the
assumption that a datasource points to a database rather than the
system.
@Doug Hughes
I created tickets in track for two features I feel would add some
flexibility to reactor to be used with more dbms configurations.
Obviously you choose the path reactor will take, so do with those
suggestions what you will. They are ticket numbers 196 and 197
respectively. Due to our current dbms configuration we won't be able
to use reactor but I hope this will change in the future. :)
> You would need an instance of Reactor for each database and dsn, so given
> your example you'd need 4 instances
> users_read
> users_write
> services_read
> services_write
>
> Because of the way Reactor works (kind of active record-ish) you couldn't
> read a record from your read dsn and save it using the write dsn.
> Record.save() will save back to wherever it was read from.
>
> You may want to look at Transfer for this as i believe (don't quote me) that
> this should be possible
>
> I doubt that helps, but hopefully it answers your question
>
> Out of interest is the read/write setup to help balance load, with the read
> mysql instance being a slave to the write? Just wondered if you'd
> considered circular replication (or master-master) that way you can
> read/write from either and just have your app balance the load between the
> two and you don't need to explicitly target your read operations at a
> particular dsn... just a thought. I've used circular replication recently
> and i got all excited about it :)
>
> Chris
>
> 2009/5/26 Brian Pickens <brian.pick...@fccinteractive.com>
> your example you'd need 4 instances
> users_read
> users_write
> services_read
> services_write
>
> Because of the way Reactor works (kind of active record-ish) you couldn't
> read a record from your read dsn and save it using the write dsn.
> Record.save() will save back to wherever it was read from.
>
> You may want to look at Transfer for this as i believe (don't quote me) that
> this should be possible
>
> I doubt that helps, but hopefully it answers your question
>
> Out of interest is the read/write setup to help balance load, with the read
> mysql instance being a slave to the write? Just wondered if you'd
> considered circular replication (or master-master) that way you can
> read/write from either and just have your app balance the load between the
> two and you don't need to explicitly target your read operations at a
> particular dsn... just a thought. I've used circular replication recently
> and i got all excited about it :)
>
> Chris
>
Chris Blackwell
May 27, 2009, 12:31:10 PM5/27/09
to reacto...@googlegroups.com
Hi Brian,
I know I'm straying slightly OT, and I'm sure your DB guys are very competent. I'm just interested what happens if either the read or write server go down? Surely any queries targeted at those dsn's fail and you've got yourself a dead site?
In the app I worked on recently we had 2 servers in master-master configuration. Under normal operation the app balances load between the dsn's. If a dsn fails then the app removes it from the pool of available dsn's and everything carries on as normal, albeit with reduced capacity. When the failed node is brought back up it syncs its changes and once its updated its added back to the pool.
Cheers, Chris
2009/5/27 Brian Pickens <brian....@fccinteractive.com>
Brian Pickens
May 27, 2009, 3:14:59 PM5/27/09
to Reactor
hmm, thats an interesting thought. Does this mean that you are using
multiple instances of reactor, one for each dsn in your applications
pool? Also Is this your coldfusion application that is managing the
balance between the datasources? I find that quite intriguing if this
is the case.
On May 27, 11:31 am, Chris Blackwell <ch...@team193.com> wrote:
> Hi Brian,
> I know I'm straying slightly OT, and I'm sure your DB guys are
> very competent. I'm just interested what happens if either the read or write
> server go down? Surely any queries targeted at those dsn's fail and you've
> got yourself a dead site?
>
> In the app I worked on recently we had 2 servers in master-master
> configuration. Under normal operation the app balances load between the
> dsn's. If a dsn fails then the app removes it from the pool of available
> dsn's and everything carries on as normal, albeit with reduced capacity.
> When the failed node is brought back up it syncs its changes and once its
> updated its added back to the pool.
>
> Cheers, Chris
>
> 2009/5/27 Brian Pickens <brian.pick...@fccinteractive.com>
multiple instances of reactor, one for each dsn in your applications
pool? Also Is this your coldfusion application that is managing the
balance between the datasources? I find that quite intriguing if this
is the case.
On May 27, 11:31 am, Chris Blackwell <ch...@team193.com> wrote:
> Hi Brian,
> I know I'm straying slightly OT, and I'm sure your DB guys are
> very competent. I'm just interested what happens if either the read or write
> server go down? Surely any queries targeted at those dsn's fail and you've
> got yourself a dead site?
>
> In the app I worked on recently we had 2 servers in master-master
> configuration. Under normal operation the app balances load between the
> dsn's. If a dsn fails then the app removes it from the pool of available
> dsn's and everything carries on as normal, albeit with reduced capacity.
> When the failed node is brought back up it syncs its changes and once its
> updated its added back to the pool.
>
> Cheers, Chris
>
Brian Pickens
May 27, 2009, 3:17:41 PM5/27/09
to Reactor
oh, and the other thing that we do is assign our datasource to the
dbms instead of a database in the dbms, so then we access the specific
database we need from the query. If we had a datasource for each
database we have we would be managing many many datasource names
dbms instead of a database in the dbms, so then we access the specific
database we need from the query. If we had a datasource for each
database we have we would be managing many many datasource names
> Hi Brian,
> I know I'm straying slightly OT, and I'm sure your DB guys are
> very competent. I'm just interested what happens if either the read or write
> server go down? Surely any queries targeted at those dsn's fail and you've
> got yourself a dead site?
>
> In the app I worked on recently we had 2 servers in master-master
> configuration. Under normal operation the app balances load between the
> dsn's. If a dsn fails then the app removes it from the pool of available
> dsn's and everything carries on as normal, albeit with reduced capacity.
> When the failed node is brought back up it syncs its changes and once its
> updated its added back to the pool.
>
> Cheers, Chris
>
> 2009/5/27 Brian Pickens <brian.pick...@fccinteractive.com>
> I know I'm straying slightly OT, and I'm sure your DB guys are
> very competent. I'm just interested what happens if either the read or write
> server go down? Surely any queries targeted at those dsn's fail and you've
> got yourself a dead site?
>
> In the app I worked on recently we had 2 servers in master-master
> configuration. Under normal operation the app balances load between the
> dsn's. If a dsn fails then the app removes it from the pool of available
> dsn's and everything carries on as normal, albeit with reduced capacity.
> When the failed node is brought back up it syncs its changes and once its
> updated its added back to the pool.
>
> Cheers, Chris
>
denstar
May 27, 2009, 4:13:37 PM5/27/09
to reacto...@googlegroups.com
Not to get in the way of the master-master deal (most DBMS have
clustering options as well, just to throw that into the mix (tho I've
had better luck with replication)), what about switching the DSN at
runtime, using the same project for all the DSNs?
clustering options as well, just to throw that into the mix (tho I've
had better luck with replication)), what about switching the DSN at
runtime, using the same project for all the DSNs?
Eh. Not likely, but whatever. :-)
FWIW, a reactor factory per DSN works swell, especially if you're
using ColdSpring to manage stuff. Might even be better than one
factory that served different DSNs, from a management perspective.
*shrug*. The overhead doesn't seem to be much, even with a few
reactor factories in play.
Sounds like something that would be cool, would to be able to specify
the database when you're building your reactor mappings, and I'm
pretty sure that's a feature request already.
At least then it would be only 4 factories, instead of 100s or whatever. :-/
I for one would love to see the ability to specify the DB (if not the
DSN) get into the next version of Reactor.
--
Solitude is the place of purification.
Martin Buber
Brian Pickens
May 28, 2009, 10:10:56 AM5/28/09
to Reactor
@denstar
Your solution would work great in the case where a dsn didn't
neccessarily point only to a read or only a write dbms. If we were to
ever delete/insert/update on our read dbms, that change would quickly
be overwritten when the write dbms gets replicated over to the read
dbms. We could read off of the write dbms, but then that would nullify
the whole reason for separating the two. The purpose is so that
selects are not slowed down by writes to the db. In order for reactor
to work correctly with our dbms configuration a single object would
have to know to use one dsn for write operations, and another dsn for
read operations.
Your solution would work great in the case where a dsn didn't
neccessarily point only to a read or only a write dbms. If we were to
ever delete/insert/update on our read dbms, that change would quickly
be overwritten when the write dbms gets replicated over to the read
dbms. We could read off of the write dbms, but then that would nullify
the whole reason for separating the two. The purpose is so that
selects are not slowed down by writes to the db. In order for reactor
to work correctly with our dbms configuration a single object would
have to know to use one dsn for write operations, and another dsn for
read operations.
Tom Chiverton
May 28, 2009, 11:08:02 AM5/28/09
to reacto...@googlegroups.com
On Thursday 28 May 2009, Brian Pickens wrote:
> dbms. We could read off of the write dbms, but then that would nullify
> the whole reason for separating the two. The purpose is so that
> selects are not slowed down by writes to the db.
selects shouldn't slow down writes, unless you are thrashing the DB server, or
have funny transaction isoloation or something...
have funny transaction isoloation or something...
|
||||
| ||
Brian Pickens
May 28, 2009, 11:21:07 AM5/28/09
to Reactor
@Tom
No, we don't care about writes being slow. We care about write
operations slowing down selects. Im sure it has to do with harddisk I/
O and all that stuff, but really im not a system guy so I can't really
explain exactly why they chose this configuration. Also I know that a
mySql consultation recommended this configuration as well, well, im
fairly certain that was mentioned in conversation.
On May 28, 10:08 am, Tom Chiverton <tom.chiver...@halliwells.com>
wrote:
No, we don't care about writes being slow. We care about write
operations slowing down selects. Im sure it has to do with harddisk I/
O and all that stuff, but really im not a system guy so I can't really
explain exactly why they chose this configuration. Also I know that a
mySql consultation recommended this configuration as well, well, im
fairly certain that was mentioned in conversation.
On May 28, 10:08 am, Tom Chiverton <tom.chiver...@halliwells.com>
wrote:
> On Thursday 28 May 2009, Brian Pickens wrote:
>
> > dbms. We could read off of the write dbms, but then that would nullify
> > the whole reason for separating the two. The purpose is so that
> > selects are not slowed down by writes to the db.
>
> selects shouldn't slow down writes, unless you are thrashing the DB server, or
> have funny transaction isoloation or something...
>
> Tom.Chiver...@halliwells.com
>
> > dbms. We could read off of the write dbms, but then that would nullify
> > the whole reason for separating the two. The purpose is so that
> > selects are not slowed down by writes to the db.
>
> selects shouldn't slow down writes, unless you are thrashing the DB server, or
> have funny transaction isoloation or something...
>
Reply all
Reply to author
Forward
0 new messages