You're just moving application logic to the SQL server - point is you need to set up the server to use a given offset for your app. And how do you define what your app is? By host/IP? Shared setups won't work. It's too cumbersome and doesn't solve many of the issues.
The 'correct' (widely used) HA/distributed answer is single master, many slaves, and if you need to fail over, you make a slave the master. Typically you use middleware like MySQL Proxy to achieve this. If you want some background reading, High Performance MySQL (O'Reilly) is a good start, along with MySQL Stored Procedure Programming (also). MySQL Cluster isn't something that has been discussed much here and it's perhaps worth looking at for MySQL, since in theory it's a drop-in solution. It's of course (as with all things MySQL now) somewhat Oracle-encumbered now but should do multi-master and everything else discussed here out of the box. However, the issue of HA for Rivendell doesn't stop at the database. There's plenty of other stuff to consider, like audio stores and so on. Having a fully HA MySQL system with multi-master and DNS round-robin between keeps your DB up all the time assuming no systemic faults, but now you need bidirectional replication on your audio store, and DNS round-robin (or IP failover) on that. For proper HA Rivendell clusters you really need to start looking at cluster managers, though, and that gets real complex real fast (well outside the capabilities of most radio techs or even most full-time sysadmins). If you need five-nines, you can hire a DBA/cluster expert - if you can't afford it you can probably live with a bit of downtime by thinking up some other redundancies (say, keep an iPod stocked up with some relevant music in a box in the studio... or if you've got the budget, two iPods, one for jingles...) I do have to wonder what it'd take to get Rivendell on, say, MongoDB - nonrelational DB, has its own file storage mechanism which allows for redundancy and high availability and sharding along with the rest of the DB out of the box with an elected master. I know of a few video sites using this, and I've had good success using it for an image hosting website at nontrivial scale. It would remove the file storage as an independent thing - just hook into Mongo, replica sets on all the machines with enough space, and you've got as much reliability as you can afford fairly simply... I'm tempted to try this out to store /var/snd on my test rig just to see how it'd work. Cheers, James Harrison On 30/04/2012 13:23, Wayne Merricks wrote: > I agree with the Update/Delete side your app might have to be aware but > the whole point of the auto number system is that you don't have to code > for it. Its a server side configuration option. > > Any clients you wanted to bolt on could just use the master server and > would never know that their auto number field was going up in 10s rather > than 1s. I was thinking about this kind of set up for just the on air > nodes. What I find odd is there must be lots of companies who have > clustered their MySQL servers to do stuff like this, I haven't found any > practical solutions for the disaster recovery side of it. > > More generically, there has got to be a company somewhere with offices > spread around the country that decided to localise their (My)SQL servers > and sync via the WAN connections to save some bandwidth. It seems like > such an obvious use case but finding docs and set up examples is beyond > my Google skills at the moment. > > The only thing I've stumbled across (open source wise) is something > called Symmetric DS (http://symmetricds.codehaus.org/). It looks > promising but I need to get reading I guess. > > Wayne Merricks > The Voice Asia > 0121 522 6080 > > > On 30/04/12 13:08, James Harrison wrote: >> What kills this though is the application awareness requirement. Ideally >> any high availability DB implementation should be completely transparent >> to Rivendell clients to a large extent and you should be able to put >> pretty much any DB on the end of Rivendell, with HA being done by the DB >> (and Rivendell just telling clients which server to connect to via >> ripcd). This way you're not locking everything to one implementation of >> SQL with a very specific set of interaction requirements. Say I want to >> bolt on my own, totally unconnected to Rivendell, web interface tied >> into the DB - that app now has to know how to do all this. Not great. >> >> Cheers, >> James Harrison >> >> >> On 30/04/2012 13:03, Wayne Merricks wrote: >>> Hi again, >>> >>> I'm pretty sure it was a SELECT statement that killed it but my memory >>> might be off, its been 3 years since the program was used and about 4 >>> since I made it. What annoyed me was that MySQL would just sit there >>> reporting that the slave was waiting for a bin log update (this was >>> before I knew about things like Nagios so the first I knew was a phone >>> call from the India office moaning that things aren't updating). >>> >>> Multi-master inserts can be quite simple but get very complicated very >>> quickly the more nodes you have. In its simplest form its very easy, >>> you have two nodes. Node A has its auto number entries starting at 1 >>> and increments by 2 (all the odd numbers), hence node B has even auto >>> numbers. >>> >>> With a bit of foresight you can do things like the following, auto >>> number increments by 10. Server A starts at 1, then 11, 21 etc. Server >>> B, 2, 22 etc >>> >>> Obviously this falls over with more than 10 nodes. You still have >>> problems with updates and deletes though especially after a failure and >>> this is where I'm not sure what MySQL does (in my experience MySQL >>> generally goes into "hmm its all gone Pete Tong so I will sit here doing >>> nothing until someone notices"). >>> >>> I haven't looked into the DB side of Riv in detail to figure out if the >>> auto number field is critical to any part of it though. As you can have >>> a situation where Server B not being used very often is sitting at auto >>> number position 4 while Server A has already gone up to 213. >>> >>> Wayne Merricks >>> The Voice Asia >>> 0121 522 6080 >>> >>> >>> On 30/04/12 11:38, Fred Gleason wrote: >>>> On Apr 30, 2012, at 04:36 57, Benjamin D. Fillmore wrote: >>>> >>>>> When writes to master fail, it triggers the unavailable flag. Which >>>>> sets Rivendell to write only to spool DB, and launches daemon to >>>>> periodically check for the master. Once master is back online, that >>>>> daemon flushes the spool to master, resets the flag, checks for any >>>>> entries in spool as a result of a race condition, processes them, and >>>>> exits gracefully. >>>> Ok, now imagine that we have two workstations (or three, or six, or 'N'), >>>> each of which fails over to its respective 'spool' DB instance. While in >>>> that mode, they each make different, mutually contradictory changes to the >>>> DB. Now we'd need a way to resolve all of that as part of the recovery >>>> process. >>>> >>>> Failover is easy. It's the *recovery* that's the nasty part. >>>> Architecturally, my current thinking is a pair of MySQL instances in >>>> 'master-master' replication, with some application logic to ensure that >>>> only one get's written to at any time. If *both* of those fail, we could >>>> still have a 'doomsday' mode that would permit operation from a local >>>> MySQL instance on each workstation, but I don't see a way to support >>>> automatic recovery from that state -- any changes made to the various >>>> local DBs while in that mode would basically be lost (or require manual >>>> re-integration by a DBA, which takes it outside the scope of automatic >>>> failover). >>>> >>>> Cheers! >>>> >>>> >>>> |-------------------------------------------------------------------------| >>>> | Frederick F. Gleason, Jr. | Chief Developer | >>>> | | Paravel Systems | >>>> |-------------------------------------------------------------------------| >>>> | "No, `Eureka!' is Greek for `This bath is too hot!'" | >>>> | -- Dr. Who | >>>> |-------------------------------------------------------------------------| >>>> >>>> _______________________________________________ >>>> Rivendell-dev mailing list >>>> [email protected] >>>> http://lists.rivendellaudio.org/mailman/listinfo/rivendell-dev >>> ####################### >>> Scanned by MailMarshal >>> ####################### >>> >>> ############ >>> >>> Attention: >>> >>> The information contained in this message is confidential and intended >>> for the addressee(s) only. If you have received this message in error >>> or there are any problems, please notify the originator immediately. >>> The unauthorised use, disclosure, copying or alteration of this message >>> is strictly forbidden. Christian Vision or any of its subsidiaries will >>> not be liable for direct, special, indirect or consequential damages >>> arising from alteration of the contents of this message by a third party >>> or as a result of any virus being passed on. Please note that we reserve >>> the right to monitor and read any e-mails sent or received by the >>> company under the Telecommunications (Lawful Business Practice) >>> (Interception of Communications) Regulation 2000. Christian Vision is >>> registered in England as a limited company 2842414 and as a charity >>> 1031031 >>> >>> ############ >>> _______________________________________________ >>> Rivendell-dev mailing list >>> [email protected] >>> http://lists.rivendellaudio.org/mailman/listinfo/rivendell-dev >> _______________________________________________ >> Rivendell-dev mailing list >> [email protected] >> http://lists.rivendellaudio.org/mailman/listinfo/rivendell-dev > ####################### > Scanned by MailMarshal > ####################### > > ############ > > Attention: > > The information contained in this message is confidential and intended > for the addressee(s) only. If you have received this message in error > or there are any problems, please notify the originator immediately. > The unauthorised use, disclosure, copying or alteration of this message > is strictly forbidden. Christian Vision or any of its subsidiaries will > not be liable for direct, special, indirect or consequential damages > arising from alteration of the contents of this message by a third party > or as a result of any virus being passed on. Please note that we reserve > the right to monitor and read any e-mails sent or received by the > company under the Telecommunications (Lawful Business Practice) > (Interception of Communications) Regulation 2000. Christian Vision is > registered in England as a limited company 2842414 and as a charity > 1031031 > > ############ > _______________________________________________ > Rivendell-dev mailing list > [email protected] > http://lists.rivendellaudio.org/mailman/listinfo/rivendell-dev _______________________________________________ Rivendell-dev mailing list [email protected] http://lists.rivendellaudio.org/mailman/listinfo/rivendell-dev
