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]
RE: Read Slaves, and load balancing between them...
Hi, I employ a simple method, I have a 'status' table on the master and have a cron job that updates this table with the current time (now()) every minute. I test all the slaves each minute and if the time in the status table gets too far behind the actual time then it flags a warning to me. Cheers, A -Original Message- 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. From: Mike Johnson [mailto:[EMAIL PROTECTED] 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]
RE: Read Slaves, and load balancing between them...
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]
RE: Read Slaves, and load balancing between them...
From: James [mailto:[EMAIL PROTECTED] 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. A model of a write master and several read slaves is almost guaranteed to reduce load caused by db bottlenecking. We've seen it here in our setup. As for false positives: if possible, try whipping up a lengthy UPDATE query (or LOAD DATA INFILE) that you know will take a while. Start it on the master and, while it's cranking, manually check the binlog position on the master and slave(s). I'm not sure if such a load will cause a temporary discrepancy, but it's the first thing that comes to mind. -- 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]
Re: Read Slaves, and load balancing between them...
On Thu, Jan 29, 2004 at 03:40:17PM -, Andrew Braithwaite wrote: Hi, I employ a simple method, I have a 'status' table on the master and have a cron job that updates this table with the current time (now()) every minute. I test all the slaves each minute and if the time in the status table gets too far behind the actual time then it flags a warning to me. Cheers, A That's a great idea, thanks! On a related note, I have two servers, one slave, one master, and I want to secure the datapath between them. What's the best way ? (on Linux, if that matters) I am considering stunnel, or ssh tunnel, or is there something in MySQL I can use to connect securely? -- Jim Richardson http://www.eskimo.com/~warlock We have to go forth and crush every world view that doesn't believe in tolerance and free speech, - David Brin signature.asc Description: Digital signature
RE: Read Slaves, and load balancing between them...
I believe MySQL 4.1 has support for ssl replication but it's still alpha at the moment. If you are referring to connecting to a MySQL server from an application to query it then the following applies: ---from the manual--- When you connect to a MySQL server, you normally should use a password. The password is not transmitted in clear text over the connection, however the encryption algorithm is not very strong, and with some effort a clever attacker can crack the password if he is able to sniff the traffic between the client and the server. If the connection between the client and the server goes through an untrusted network, you should use an SSH tunnel to encrypt the communication. - http://www.mysql.com/doc/en/Security.html Cheers, A -Original Message- From: Jim Richardson [mailto:[EMAIL PROTECTED] Sent: Thursday 29 January 2004 21:03 To: [EMAIL PROTECTED] Subject: Re: Read Slaves, and load balancing between them... On Thu, Jan 29, 2004 at 03:40:17PM -, Andrew Braithwaite wrote: Hi, I employ a simple method, I have a 'status' table on the master and have a cron job that updates this table with the current time (now()) every minute. I test all the slaves each minute and if the time in the status table gets too far behind the actual time then it flags a warning to me. Cheers, A That's a great idea, thanks! On a related note, I have two servers, one slave, one master, and I want to secure the datapath between them. What's the best way ? (on Linux, if that matters) I am considering stunnel, or ssh tunnel, or is there something in MySQL I can use to connect securely? -- Jim Richardson http://www.eskimo.com/~warlock We have to go forth and crush every world view that doesn't believe in tolerance and free speech, - David Brin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Read Slaves, and load balancing between them...
On Thu, Jan 29, 2004 at 11:47:52PM -, Andrew Braithwaite wrote: I believe MySQL 4.1 has support for ssl replication but it's still alpha at the moment. If you are referring to connecting to a MySQL server from an application to query it then the following applies: ---from the manual--- When you connect to a MySQL server, you normally should use a password. The password is not transmitted in clear text over the connection, however the encryption algorithm is not very strong, and with some effort a clever attacker can crack the password if he is able to sniff the traffic between the client and the server. If the connection between the client and the server goes through an untrusted network, you should use an SSH tunnel to encrypt the communication. - http://www.mysql.com/doc/en/Security.html Thanks for the link. I am mostly concerned with data security. I'll probably wind up using ssh or stunnel. -- Jim Richardson http://www.eskimo.com/~warlock I'll get a life when it is proven and substantiated to be better than what I am currently experiencing. signature.asc Description: Digital signature
