The solution I use is that my application code always checks that the slave it is reading from is up-to-date. Perhaps that isn't an option if you don't want to write a DB layer under your app or modify it.
My page code uses a simple function called waitForDbSync() which just writes (to master) a unique 'marker' string then queries for it from a slave in a loop until it sees it. I call that function at critical points in the page code - like after just processing a POST request before I query the data to re-render the page - so that I know the users will see the up-to-date values. Note that in my implementation, the slave selected to read from is fixed for the duration of the request (i.e. the round robin DNS lookup on int-mysql-slave.yourdomain.tld is only performed when the request initially creates the DB connection). However, in your implementation of waitForDBSync() you could keep closing and re-opening the DB connection until you have a slave that is up-to-date, so that if the initially selected slave happened to be the one that is still starting, your request connection will be switched to another. The advantage I see to this approach is that is takes care of both the situation where your one starting slave is potentially a long way behind for a short-moderate time, as well as the usual situation where there is some normal delay between data written to the master and when the slaves have executed it. Of course, it requires either modifying your app code in many places, or writing another 'DB' layer between the existing one and your app logic. That may be easy if your app code already has a nice tidy 'DB abstraction' class or something like it, but near impossible if access to the low-level language MySQL binding is scattered all through the app code. Luckily for me, I was able to just write a compatible DB abstraction class in PHP that sits between the PHP-based CMS and the original DB class. It also allowed me to turn a non-replication-aware CMS into one that supports not only replication but also sharding (I examine the SQL queries in the DB class and re-route them to the appropriate shards depending on the value of the primary key being used - including managing inter-shard-master XA Transactions). Cheers, -David. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "scalr-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/scalr-discuss?hl=en -~----------~----~----~----~------~----~------~--~---
