Re: Preventing slaves from falling behind masters...
Donny Simonton wrote: With Mysql you should ONLY use RAID10. Everything else is not worth your time. I would argue that a large stripe (RAID0) would be a better solution for slaves in a large replicant network. Why waste the drive space and performance on a RAID10 when you have multiple replicants for HA. That said, all of our DBs are RAID10 as we never know which ones we may need to put in as master (if master fails). As long as you are using 15k SCSI drives, on both your master and your slave, your slave should rarely ever fall behind. Especially if you are doing less than 1,000 inserts per second on the master. Otherwise you should be just fine. The only time our slaves ever fall behind, is when you delete 40 records from table A then insert 40 new ones. But we do that for 600k records, so we do a few million deletes and inserts in a short period of time. Given that the slaves will never start a query until the master has finished it and passed it along to the slave, the slave is ALWAYS running a bit behind the master. And, if you are using MyIsam tables, and running w/ low-priority-updates (as the Mysql guide suggests for performance on slaves), then it is likely that a heavy hit replicant will lag to a noticable level when under high traffic. Innodb should lessen this a bit as it is not doing full table locks and you can have it do dirty reads. As for our replicant cluster, we do not run w/ low-piority-updates simply because we found it increased replication lags, and exposed "replication bugs" in our application (write to the rw pool, immediately look for that record in the ro pool and not find it). We also put our more powerful hardware in our replicant cluster as our rw machine does a fraction of the traffic (1/10th the qps of any of our replicants). Even w/ this, our replicants sometimes fall behind, but never more than 1 to 2 seconds. Donny -Original Message- From: Kevin Burton [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 11, 2005 7:30 PM To: mysql@lists.mysql.com Subject: Preventing slaves from falling behind masters... If you're running in a master/slave environment.. and you're application is using the slave too often... replication can fall behind which can then confuse your application. This can happen if the IO performance of both the master and slaves is equivalent and you're performaning INSERT/UPDATE/DELETE with a load of 1 on the master. Then when the transactions move to the slave all the IO is used up and any additional SELECTS will just cause the slave to fall behind. Has anyone else seen this? One way I was thinking of solving this is to use RAID5 on our master and then RAID0 on the slaves so that the master is a hard bottleneck. Then the slaves have no problem running transactions via replication and have load available to run SELECTS. Any other ideas? -- Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. See irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- 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: Preventing slaves from falling behind masters...
With Mysql you should ONLY use RAID10. Everything else is not worth your time. As long as you are using 15k SCSI drives, on both your master and your slave, your slave should rarely ever fall behind. Especially if you are doing less than 1,000 inserts per second on the master. Otherwise you should be just fine. The only time our slaves ever fall behind, is when you delete 40 records from table A then insert 40 new ones. But we do that for 600k records, so we do a few million deletes and inserts in a short period of time. Donny > -Original Message- > From: Kevin Burton [mailto:[EMAIL PROTECTED] > Sent: Wednesday, May 11, 2005 7:30 PM > To: mysql@lists.mysql.com > Subject: Preventing slaves from falling behind masters... > > If you're running in a master/slave environment.. and you're application > is using the slave too often... replication can fall behind which can > then confuse your application. > > This can happen if the IO performance of both the master and slaves is > equivalent and you're performaning INSERT/UPDATE/DELETE with a load of 1 > on the master. Then when the transactions move to the slave all the IO > is used up and any additional SELECTS will just cause the slave to fall > behind. > > Has anyone else seen this? One way I was thinking of solving this is to > use RAID5 on our master and then RAID0 on the slaves so that the master > is a hard bottleneck. Then the slaves have no problem running > transactions via replication and have load available to run SELECTS. > > Any other ideas? > > -- > > > Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. > See irc.freenode.net #rojo if you want to chat. > > Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html > >Kevin A. Burton, Location - San Francisco, CA > AIM/YIM - sfburtonator, Web - http://peerfear.org/ > GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 > > > -- > 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]
Preventing slaves from falling behind masters...
If you're running in a master/slave environment.. and you're application is using the slave too often... replication can fall behind which can then confuse your application. This can happen if the IO performance of both the master and slaves is equivalent and you're performaning INSERT/UPDATE/DELETE with a load of 1 on the master. Then when the transactions move to the slave all the IO is used up and any additional SELECTS will just cause the slave to fall behind. Has anyone else seen this? One way I was thinking of solving this is to use RAID5 on our master and then RAID0 on the slaves so that the master is a hard bottleneck. Then the slaves have no problem running transactions via replication and have load available to run SELECTS. Any other ideas? -- Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. See irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
