Re: MYSQL data replication
I don't run any master with that many slaves, for reasons that are irrelevant to this thread, but I know there is a point at which the slaves begin to place too much load on the master because they are all asking to read the binlog. I don't know what that point is exactly, but perhaps others can give some advice on that. I would expect, given the workload you describe, that 50 slaves might work okay -- but you should not listen to me. However, you can do multi-tier replication. Let's imagine the limit is ten slaves, just for a round number. You can chain ten slaves off the master, configure them with log-slave-updates, and then chain ten more slaves off each of them. Now you can scale the system to 110 slaves with two tiers. Baron sol beach wrote: Can 1 Master scale to replicate to 50 - 60 slaves? I have limit experience with Master/Slave replication doubt I can round up the hardware to test a 50 node configuration. On 6/8/07, Baron Schwartz [EMAIL PROTECTED] wrote: Hi sol, sol beach wrote: I have limited experience with MYSQL replication; which is why I am hoping others with more experience can answer a question or two. Let's say I have a MASTER MYSQL database. Let's say there are 50 - 60 other systems where I'd like to have MYSQL running on these slave systems. These slave systems need to be kept in synch with the Master, but it does NOT need to be anywhere near real time. The data in the slaves could lag as much a an hour or two. The amount of data in total in the MASTER is in the range of 100MB - 250MB The rate of changes to the data is in the range 2000 - 5000 DML per 24 hour day. We control the application so we can/will include date/time each record is created or modified. You can assume that no records ever get physically deleted; only INSERT UPDATE (no DELETE). What are some alternative ways to keep the slave systems current? The best, easiest, simplest way to do this is just to use MySQL's built-in replication. Once you learn its (many) strengths and (relatively few) weaknesses, it works extremely well. If you want to avoid some of the gotchas, I have written about my experiences here: http://www.xaprb.com/blog/2007/01/20/how-to-make-mysql-replication-reliable/ Hand-rolling replication is a bad idea in my opinion, so I don't have any alternative methods to suggest. However, if you just need to sync some data efficiently, try MySQL Table Sync (http://mysqltoolkit.sourceforge.net/). Cheers Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL data replication
Hello, Hmmm, I just reread your post and noticed the requirement of 50-60 slaves hanging off a single master. DRBD would NOT be the right solution here. -- Jimmy Jimmy Guerrero wrote: Hello, Although you state that there is no requirement for near real time synchronization, an alternative might be to look into DRBD. Which if you are not familiar with, is block-level replication. See: http://www.mysql.com/products/enterprise/drbd.html Combining DRBD with Linux Heartbeat also gives you failover capabilities. Mohd posted a blog on his experience yesterday. See: http://blog.irwan.name/?p=118 Jimmy Guerrero Sr Product Manager MySQL, Inc Houston, TX sol beach wrote: I have limited experience with MYSQL replication; which is why I am hoping others with more experience can answer a question or two. Let's say I have a MASTER MYSQL database. Let's say there are 50 - 60 other systems where I'd like to have MYSQL running on these slave systems. These slave systems need to be kept in synch with the Master, but it does NOT need to be anywhere near real time. The data in the slaves could lag as much a an hour or two. The amount of data in total in the MASTER is in the range of 100MB - 250MB The rate of changes to the data is in the range 2000 - 5000 DML per 24 hour day. We control the application so we can/will include date/time each record is created or modified. You can assume that no records ever get physically deleted; only INSERT UPDATE (no DELETE). What are some alternative ways to keep the slave systems current? TIA HAND! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL data replication
Hello, Although you state that there is no requirement for near real time synchronization, an alternative might be to look into DRBD. Which if you are not familiar with, is block-level replication. See: http://www.mysql.com/products/enterprise/drbd.html Combining DRBD with Linux Heartbeat also gives you failover capabilities. Mohd posted a blog on his experience yesterday. See: http://blog.irwan.name/?p=118 Jimmy Guerrero Sr Product Manager MySQL, Inc Houston, TX sol beach wrote: I have limited experience with MYSQL replication; which is why I am hoping others with more experience can answer a question or two. Let's say I have a MASTER MYSQL database. Let's say there are 50 - 60 other systems where I'd like to have MYSQL running on these slave systems. These slave systems need to be kept in synch with the Master, but it does NOT need to be anywhere near real time. The data in the slaves could lag as much a an hour or two. The amount of data in total in the MASTER is in the range of 100MB - 250MB The rate of changes to the data is in the range 2000 - 5000 DML per 24 hour day. We control the application so we can/will include date/time each record is created or modified. You can assume that no records ever get physically deleted; only INSERT UPDATE (no DELETE). What are some alternative ways to keep the slave systems current? TIA HAND! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL data replication
Hi sol, sol beach wrote: I have limited experience with MYSQL replication; which is why I am hoping others with more experience can answer a question or two. Let's say I have a MASTER MYSQL database. Let's say there are 50 - 60 other systems where I'd like to have MYSQL running on these slave systems. These slave systems need to be kept in synch with the Master, but it does NOT need to be anywhere near real time. The data in the slaves could lag as much a an hour or two. The amount of data in total in the MASTER is in the range of 100MB - 250MB The rate of changes to the data is in the range 2000 - 5000 DML per 24 hour day. We control the application so we can/will include date/time each record is created or modified. You can assume that no records ever get physically deleted; only INSERT UPDATE (no DELETE). What are some alternative ways to keep the slave systems current? The best, easiest, simplest way to do this is just to use MySQL's built-in replication. Once you learn its (many) strengths and (relatively few) weaknesses, it works extremely well. If you want to avoid some of the gotchas, I have written about my experiences here: http://www.xaprb.com/blog/2007/01/20/how-to-make-mysql-replication-reliable/ Hand-rolling replication is a bad idea in my opinion, so I don't have any alternative methods to suggest. However, if you just need to sync some data efficiently, try MySQL Table Sync (http://mysqltoolkit.sourceforge.net/). Cheers Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]