Re: Advise on High Availability configuration
Hi, one question remains: what you got is a master - master slave - multiple slaves setup, right? Or did you really set up a multi-master replication? If so, how? By the way, thank you for sharing your setup, it is very helpful for us (as we are looking for a high-availability solution for mysql (preferably a multi-master cluster)) but the last bits of mist also need to be lifted! So, basically, if I got you right, in your case no app can choose randomly whether to update master1 or master2: it's either master1 __or__ master2 for __all__ slaves? In case of error on master1 you fail over to master2 which from now on is the only master available for updates? Thank you again for your insights. Kind regards, Frank. Andrew Braithwaite schrieb: Hi, So how does this bit work? If one master falls over and slaves move to master two, how do you rebuild master one without downtime? Don't the slaves try and use Master 1 as soon as it's back online even though its data is out of sync? If master 1 fails and the slaves move to master 2, then they effectively swap roles as primary and secondary masters and the directive is issued to the webservers to connect in the reverse order. Yes, master 1 will be offline until it is rebuilt and will join again in the secondary role. The master connect wrapper is robust. If a connection fails it will try to reconnect a few times and run that query again. All the slaves and masters are on the same switch so if one fails, generally speaking, they all fail. What if one of the slaves loses its heartbeat with master one and falls over to master two when other slaves can still see master one. Don't you then have inconsistencies in your data? The heartbeat code is pretty robust too. It's similar to the above wrapper and has the same benefits. Having said all that, I did mention that this is an inexpensive medium sized solution to availability and that it is a read-heavy, write-light DB scenario. Additionally there is no data of a financial nature (if there was, we would be using another set-up - commit / rollback for example..). As such we are tolerant to very small amounts of data loss in the event of a failover situation. On this, I refer you to the how many 9's do you need dscussion, nicely explained by Jeremy here: http://jeremy.zawodny.com/blog/archives/000805.html Cheers, Andrew -Original Message- From: Russell Horn [mailto:[EMAIL PROTECTED] Sent: Monday 02 February 2004 16:21 To: [EMAIL PROTECTED] Subject: RE: Advise on High Availability configuration Andrew Braithwaite wrote: Each slave keeps a heartbeat to the master and in the event of a failure, changes it's master to master2. So how does this bit work? If one master falls over and slaves move to master two, how do you rebuild master one without downtime? Don't the slaves try and use Master 1 as soon as it's back online even though its data is out of sync? What if one of the slaves loses its heartbeat with master one and falls over to master two when other slaves can still see master one. Don't you then have inconsistencies in your data? Russell. -- Dr. Frank Ullrich, DBA Netzwerkadministration Heise Zeitschriften Verlag GmbH Co KG, Helstorfer Str. 7, D-30625 Hannover E-Mail: [EMAIL PROTECTED] Phone: +49 511 5352 587; FAX: +49 511 5352 538 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advise on High Availability configuration
No, when we implemented high-availability MySQL servers we used MySQL's inbuilt replication - this has been running here for years now and we have had constant DB availability during that time, even though individual machines have failed now and again. We're using 2 masters 4 slaves with the logic for sql reads going to the slaves and sql writes going to the master handled in the application layer. We also have automatic master failover (although I believe MySQL plan to build this into their product at some point in the near future). At the moment we are studying the implementation of a MySQL-Apache high High Availability system. Would you be so kind to explain a bit more detailedly the soft/hard aspects of yours application?. I believe that at the moment this is a hot question. Probably there are many more people interested in yours experience. Greetings. A.J.Millan ZATOR Systems. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advise on High Availability configuration
On Mon, Feb 02, 2004 at 09:17:08AM +0100, A.J.Millan wrote: No, when we implemented high-availability MySQL servers we used MySQL's inbuilt replication - this has been running here for years now and we have had constant DB availability during that time, even though individual machines have failed now and again. We're using 2 masters 4 slaves with the logic for sql reads going to the slaves and sql writes going to the master handled in the application layer. We also have automatic master failover (although I believe MySQL plan to build this into their product at some point in the near future). At the moment we are studying the implementation of a MySQL-Apache high High Availability system. Would you be so kind to explain a bit more detailedly the soft/hard aspects of yours application?. I believe that at the moment this is a hot question. Probably there are many more people interested in yours experience. Greetings. A.J.Millan ZATOR Systems. Very much so! -- Jim Richardson http://www.eskimo.com/~warlock If space is warped, time is all that's weft. signature.asc Description: Digital signature
RE: Advise on High Availability configuration
Hi, OK - I'll try to explain in as much detail as I can.. We have redhat linux apache webservers running our apps with fcgi (which uses persistant DB connections). We have about 8 of these. It's important to understand that our MySQL system is optimised for a read-heavy / write-light site (about 80% reads in our case) We also have 2 Master and 4 Slave MySQL DB's. The slaves all replicate from master1 - master2 also replicates from master1. The masters are compaq DL380 with 15,000rpm raid 5 disk. Our 4 slaves (and 8 webservers) are what we call RAIN (Redundant Array of Inexpensive Nodes) - cheap boxes with fast disk running RedHat Linux. If one breaks, we don't care - we just buy another and slot it in (automated install scripts really help us here). In the applications we have a wrapper for connecting to the DB's - basically as each instance of the app is initialised, it scans the list of slaves, gets the number of MySQL processes running on each slave, then connects to the one with the least amount of connections. Each instance will also connect to the master (and if that fails - the 2nd master.) therefore each instance of our apps will have a persistant connection to a master and a slave. We also have a query wrapper we use to run all queries. This will send select statements to the slaves and everything else to the master. Each slave keeps a heartbeat to the master and in the event of a failure, changes it's master to master2. We have 128MB dedicated to MySQL query-caching and about half our queries hit the query-cache. I think this is a nice meduim sized solution for an extremely avaiable but non-expensive database system. We're currently doing more than 5 Mill page views a day (each page view generates about 5 - 10 queries) using this method. Hope this helps with your study. Cheers, Andrew -Original Message- From: A.J.Millan [mailto:[EMAIL PROTECTED] Sent: Monday 02 February 2004 08:17 To: [EMAIL PROTECTED] Subject: Re: Advise on High Availability configuration No, when we implemented high-availability MySQL servers we used MySQL's inbuilt replication - this has been running here for years now and we have had constant DB availability during that time, even though individual machines have failed now and again. We're using 2 masters 4 slaves with the logic for sql reads going to the slaves and sql writes going to the master handled in the application layer. We also have automatic master failover (although I believe MySQL plan to build this into their product at some point in the near future). At the moment we are studying the implementation of a MySQL-Apache high High Availability system. Would you be so kind to explain a bit more detailedly the soft/hard aspects of yours application?. I believe that at the moment this is a hot question. Probably there are many more people interested in yours experience. Greetings. A.J.Millan ZATOR Systems. -- 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: Advise on High Availability configuration
Andrew Braithwaite wrote: Each slave keeps a heartbeat to the master and in the event of a failure, changes it's master to master2. So how does this bit work? If one master falls over and slaves move to master two, how do you rebuild master one without downtime? Don't the slaves try and use Master 1 as soon as it's back online even though its data is out of sync? What if one of the slaves loses its heartbeat with master one and falls over to master two when other slaves can still see master one. Don't you then have inconsistencies in your data? Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Advise on High Availability configuration
Hello All: Thank you for all the advise provided on this issue. Much appreciate Andrew's detailed answers and alternate solution. I will investigate the Replication method and try to devise some strategy to make sure that the Slave mirrors the Master as closely as possible. Also taking advantage of using the Slave to read data off from can prove to be very advantageous to me. Again, thank you all very much. Gowtham. --- Russell Horn [EMAIL PROTECTED] wrote: Andrew Braithwaite wrote: Each slave keeps a heartbeat to the master and in the event of a failure, changes it's master to master2. So how does this bit work? If one master falls over and slaves move to master two, how do you rebuild master one without downtime? Don't the slaves try and use Master 1 as soon as it's back online even though its data is out of sync? What if one of the slaves loses its heartbeat with master one and falls over to master two when other slaves can still see master one. Don't you then have inconsistencies in your data? Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Advise on High Availability configuration
Hi, So how does this bit work? If one master falls over and slaves move to master two, how do you rebuild master one without downtime? Don't the slaves try and use Master 1 as soon as it's back online even though its data is out of sync? If master 1 fails and the slaves move to master 2, then they effectively swap roles as primary and secondary masters and the directive is issued to the webservers to connect in the reverse order. Yes, master 1 will be offline until it is rebuilt and will join again in the secondary role. The master connect wrapper is robust. If a connection fails it will try to reconnect a few times and run that query again. All the slaves and masters are on the same switch so if one fails, generally speaking, they all fail. What if one of the slaves loses its heartbeat with master one and falls over to master two when other slaves can still see master one. Don't you then have inconsistencies in your data? The heartbeat code is pretty robust too. It's similar to the above wrapper and has the same benefits. Having said all that, I did mention that this is an inexpensive medium sized solution to availability and that it is a read-heavy, write-light DB scenario. Additionally there is no data of a financial nature (if there was, we would be using another set-up - commit / rollback for example..). As such we are tolerant to very small amounts of data loss in the event of a failover situation. On this, I refer you to the how many 9's do you need dscussion, nicely explained by Jeremy here: http://jeremy.zawodny.com/blog/archives/000805.html Cheers, Andrew -Original Message- From: Russell Horn [mailto:[EMAIL PROTECTED] Sent: Monday 02 February 2004 16:21 To: [EMAIL PROTECTED] Subject: RE: Advise on High Availability configuration Andrew Braithwaite wrote: Each slave keeps a heartbeat to the master and in the event of a failure, changes it's master to master2. So how does this bit work? If one master falls over and slaves move to master two, how do you rebuild master one without downtime? Don't the slaves try and use Master 1 as soon as it's back online even though its data is out of sync? What if one of the slaves loses its heartbeat with master one and falls over to master two when other slaves can still see master one. Don't you then have inconsistencies in your data? Russell. -- 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: Advise on High Availability configuration
On Fri, Jan 30, 2004 at 10:36:34AM -0800, Gowtham Jayaram wrote:
CONFIGURATION:
[...]
- Additionally, I will setup a SCSII controller in
the Primary and Secondary Application machines so that
the actual data store (disk drive) runs on another
physical machine in a disk-array (RAID).
So your servers independently write to the same disk partition through a
SCSI interface? I don't have any experience with this, but I guess you
couldn't even mount the filesystem on the secondary before the primary
goes down. If the primary goes down unexpectedly, your filesystem may be
corrupt unless you use a logging filesystem.
Also, the disks are redundant, but is all hardware in this RAID system
('physical machine') redundant?
OPERATION:
[...]
- If the Primary goes down, the Secondary becomes
Active and starts to perform the database operations.
If the primary goes down, how do you know that the database is in a good
state, even if the filesystem itself is consistent? I don't think MyISAM
gives you any guarantees in that regard. The secondary would need to run
myisamcheck on the tables before taking over.
Regards,
Fred.
--
Fred van Engen XB Networks B.V.
email: [EMAIL PROTECTED]Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Advise on High Availability configuration
Hi, In answer to your questions: - Have any of you seen such a configuration being deployed? No, when we implemented high-availability MySQL servers we used MySQL's inbuilt replication - this has been running here for years now and we have had constant DB availability during that time, even though individual machines have failed now and again. We're using 2 masters 4 slaves with the logic for sql reads going to the slaves and sql writes going to the master handled in the application layer. We also have automatic master failover (although I believe MySQL plan to build this into their product at some point in the near future). - Do you see any big gotcha's in this configuration? Yes. You are tring to acheive high availability but with the architecture you propose, you still have a single point of failure (the data store) if this were to break, the system is down. Another scenario is that if the data were to become corrupt (which is quite likely if one of the two MySQL servers were to crash), you would have to take the MySQL servers down whilst you do a fsck/myisamchk to regain data integrity. Besides all that, I have a gut feeling that this is not a good way to acheive high availability. - Is it possible for the Primary MySQL process to lock the data store such that the Secondary MySQL process cannot access the data store? Yes, this could be done using either file permissions or MySQL privileges - Is it possible for the 2 MySQL processes update the same table simultaneously? InnoDB has row level locking but MyISAM doesn't Hope this helps, Andrew -Original Message- From: Gowtham Jayaram [mailto:[EMAIL PROTECTED] Sent: Friday 30 January 2004 18:37 To: [EMAIL PROTECTED] Subject: Advise on High Availability configuration Hello All; I am in the process of examining a High Availability (HA) configuration. The motivation is to not use database replication (at least at this stage) because of the need to work on the complete data set at any given point in time. Here is the configuration choice being considered CONFIGURATION: - I would like to configure two machines, Primary and Secondary. Each machine has our Application and an instance of MySQL database server running on it. - Additionally, I will setup a SCSII controller in the Primary and Secondary Application machines so that the actual data store (disk drive) runs on another physical machine in a disk-array (RAID). - With this setup the MySQL Servers on both the machines will write/read data to/from this disk array. OPERATION: - The idea is to have only one machine actively use the data store at any given time. The other machine will be in the stand-by mode. - To start off, the Primary is Active, performing database operations on the data store. The Secondary is in stand-by mode and does NOT perform any database operations. - If the Primary goes down, the Secondary becomes Active and starts to perform the database operations. CAVEATS I AM AWARE OFF: - Does not work with InnoDB. - Works with MyISAM but need to disable key buffer. This leads to big hit on performance QUESTIONS: - Have any of you seen such a configuration being deployed? - Do you see any big gotcha's in this configuration? - Is it possible for the Primary MySQL process to lock the data store such that the Secondary MySQL process cannot access the data store? - Is it possible for the 2 MySQL processes update the same table simultaneously? Looking forward to all your feedback. Thank you. Gowtham. __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- 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: Advise on High Availability configuration
I am wary of something so 'do it yourself'. Have you looked at ReHat's clustering solution? http://www.redhat.com/software/rha/cluster/ http://www.redhat.com/software/rha/cluster/manager/ I don't think it has any issue with InnoDB, key buffers, etc. I believe this solution works best for failover situations. Also if you have machine A doing A-type work and B doing B-type work, then if one goes down then the other will do both A-type and B-type work until the other machine comes back up. I think if both A-type work and B-type work are both MySQL, then you may have to use different ports for connections, use skip-name-resolve (and setting the name of error files, binlog files, etc) in my.cnf to eliminate issues with moving between machines. Also there is Veritas Cluster Server which has a MySQL module. Emic has load balancing as well as failover, offering these items: i) online backup capability ii) dynamic load balancing iii) fault management with fast failovers iv) high availability, and v) performance scalability with each added server node vi) does not require shared SCSI raid array http://www.emicnetworks.com/ http://www.emicnetworks.com/products/mysql.html Lastly, there is MySQL which bought a company to add clustering themselves: http://www.mysql.com/press/release_2003_30.html I have not used any, though I am evaluating all at the moment. -steve- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
