Mike & Andrew, Excellent ideas... The reason I am considering checking the log location, is because normal lusers will be using this software, so it won't be too good for them to see errors about the slave being out of synch. (Some 20 people including my self are using this software.) This is something that the software will have to use some logic to figure out which slave is the best. I will have a list of read slaves that I will randomly call each program start, or upon error. Mike did bring up a good point, false positives. Has anyone done what I am talking about (checking bin log position)? Also will this method actually remove load from the servers? Thanks for your input so far.
Thanks, James -----Original Message----- From: Mike Johnson [mailto:[EMAIL PROTECTED] Sent: Thursday, January 29, 2004 10:19 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Read Slaves, and load balancing between them... From: James [mailto:[EMAIL PROTECTED] > I am interested in setting up 3 read only slaves and one > write master. I am thinking of implementing this because > one server is slower than 3 ;-). But anyway, I have read > all the docs and I am fairly sure how I want do this and > keep the sanity checking in the loop. What I am > considering doing is checking (in intervals) the binlog > positions between the slave and the master. I am worried > about this being out of synch, for just about every 3 > reads, there is a write. I am looking to see if this is > a good idea, and what others comments/suggestions are. It all depends on how much traffic your db server is getting and how quickly you want to be notified if replication is behind or a slave thread died. Your idea of checking the binlog position between the slave(s) and master at an interval is a good one, though you run the risk of getting it at "the wrong time," such as when a rather large update is being made, and being notified with a false positive. I have a very similar setup -- one master, two slaves. What I did was set up a Perl script to run in the cron every 5 minutes. It logs into each slave and performs a SHOW SLAVE STATUS. From this it looks at the Slave_IO_Running and Slave_SQL_Running columns to determine that the slave thread is still operating. If either is 'no' it pages me with the 'Last_error' column. This may not be optimal, as you sound as though you want to see if/when replication is out of sync and not just completely dead. I'd say give it a shot checking the binlog positions. If you find yourself getting a lot of false positives, try having the check script sleep for N seconds and check again to be sure of itself before notifying you, just to see if it needs to catch up. HTH! -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
