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.
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 have not tested this infrastructure and make no claims to its suitability for any given problem, just that it is what I would try. On 11/9/07, Jonathan Share <[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]>> wrote: > > > > > > Mike Orr wrote: > > > On Nov 8, 2007 8:06 AM, Jonathan Share <[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 > > > > > > > >> 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 > > > > > > > > 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 -~----------~----~----~----~------~----~------~--~---
