Jeffrey Hulten wrote: > You will still need to set up your replication between the two masters > and the autoincrement values to prevent collisions, but this seems > pretty light weight to me too. I do not know how it will handle under load.
I already have this set up and in production. Also have previous experience of this in the Java world > > Also if you have a farm of web servers I would probably put a proxy on > each server and have the web servers talk to localhost so you don't have > a single point of failure. I was thinking that this is the way I would do it. > > I have not tested this infrastructure and make no claims to its > suitability for any given problem, just that it is what I would try. Understood. I'm not going to rush into anything. I'll report back if it succeeds. Should be within the next couple of weeks. > > On 11/9/07, *Jonathan Share* <[EMAIL PROTECTED] > <mailto:[EMAIL PROTECTED]>> wrote: > > > Jeffrey Hulten wrote: > > Also search for mysql proxy. It is a new project from the mysql > folks that > > can load-balance multiple instances with failover. > > > > Thanks for the tip. I had forgotten mysql proxy did this, looks like it > might be the "solution of least hassle", now I just need to get my Xen > test environment up :-( > > > > > > > On 11/9/07, *Jonathan Share * <[EMAIL PROTECTED] > <mailto:[EMAIL PROTECTED]> > > <mailto:[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>>> wrote: > > > > > > Mike Orr wrote: > > > On Nov 8, 2007 8:06 AM, Jonathan Share < [EMAIL PROTECTED] > <mailto:[EMAIL PROTECTED]> > > <mailto:[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>>> wrote: > > >> I currently have 2 database servers set up in a master-master > > >> replication set up and wish for the pylons app to fail > over to the > > >> second database server when the first goes down. > > > > > > By master-master you mean each database updates the other > when a > > > change comes in? How did you structure that? I wanted to do > > that for > > > my sites with two database servers in different states > (each talking > > > to a local web application), but it looked like it would > require > > major > > > database restructuring to keep track of which record was > "more > > > accurate" in case one of the servers went down and then had to > > > automatically synched with the other. I gave up because I > didn't > > want > > > it accidentally synching the wrong way and overwriting > correct data. > > > > This is a potential problem with a master-master set up. > Fortunately my > > app has the advantage that there are very few update queries, the > > primary content on my site is versioned so to change it you > add a new > > record rather than update an existing one. Past that it's > user comments, > > user ratings and download statistics which are all inserts > so we have > > very few updates going on. The updates that do happen are > either things > > that are very unlikely to happen at the same time on > different servers > > (eg. user editing a comment) so I'm happy that "last write > wins" or are > > aggregate fields that are denormalised (download count, total > rating) > > and so are recalculated from the normalised table so is > harmless if it > > "synch"s the wrong way. > > > > > > > > When I looked at the MySQL docs, it only talked about > master-slave > > > replication, which wouldn't help if the master goes down > and the > > slave > > > has to become the master temporarily, which was the whole > point of > > > having a second database in the first place. > > > > FWIW this was the article I used for beginning with Master-Master > > replication. > > > > > > http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html > > <http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html> > > > > > > > >> I hope this is a stupid question but I'm trying to work > out how to > > >> configure a database connection in pylons to use a database > > failover. > > > > > > It's not stupid, it's just... the only thing about > SQLAlchemy we > > > maintain is the Pylons-SQLAlchemy interface. This is not an > > interface > > > issue (did the query get to SQLAlchemy? are concurrent > requests > > > stomping on each other's connections? how do you access > the model > > > from a unittest or standalone script?), it's an advanced > database > > > question that requires the experts on the sqlalchemy list, > not this > > > one. Not putting you down for asking, just point out that > you'll get > > > a better answer there. > > > > Good point, my bad. I'll sign up to the SA mailing list and > fire up the > > thread there next week instead. > > > > > > > >> I'm running Pylons 0.9.5 with SqlAlchemy 0.3 and mysqldb > but a > > solution > > >> for 0.9.6 or SA 0.4 would be sufficient for pointing me > in the right > > >> direction. > > >> > > >> In Java I would achieve this with a connection string > that looks > > like > > >> the following, > > >> > > >> jdbc:mysql://db1.example.com, > > > > db2.example.com/dbname?failOverReadOnly=false&useUnicode=true&characterEncoding=UTF-8 > > <http://db2.example.com/dbname?failOverReadOnly=false&useUnicode=true&characterEncoding=UTF-8> > > > > <http://db2.example.com/dbname?failOverReadOnly=false&useUnicode=true&characterEncoding=UTF-8 > > <http://db2.example.com/dbname?failOverReadOnly=false&useUnicode=true&characterEncoding=UTF-8>> > > > > > > You can't set up a double engine in SQLAlchemy. You could > set up two > > > separate engines and bind to one or the other at any time > -- the > > > SQLAlchemy manual and "SQLAlchemy 0.4 for people in a > hurry" have > > some > > > thoughts on distributed databases (parallel/sharding) -- > but it's not > > > 'automatic' so it's not quite what you want. > > > > > > However, your idea for a double engine that automatically > falls back > > > to the second one might be a worthwhile enhancement for > SQLAlchemy, > > > especially if you point out that Java does it and has a > reference > > > implementation. > > > > > > > > > > > > > > > > > > > > > > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "pylons-discuss" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/pylons-discuss?hl=en -~----------~----~----~----~------~----~------~--~---
