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]
