Master - Slave MySQL setup on a VMWare Cloud -- Is it needed?

Question

We currently are in the research phase of building a "Master" database for our e-commerce business that will centralize all data, including product information, vendor information, Magento information, Amazon, etc .... We have looked into both "physical hardware" (Two RAID 5 machines, master/slave, with a HDD backup off the slave -- and a separate application server).... Or we could do a "cloud based" system.

The heart of the question is, is there any benefit with replicating on a cloud? The whole point of a cloud is scalability and "no hardware downtime" therefore no lost data due to bad hardware. The data loss that would occur, if any, on a cloud based system would be software based. With that said, being a software based issue that would cause loss of data, this issue would most likely get replicated right? Therefore we'd have 2 machines with the same corrupted data?

We are trying to analyze the cost/benefit of either solution. Of course, if there is no benefit to replicating on a cloud, then the benefits the cloud has to offer outweigh the hardware solution. However if a replicated solution on the cloud is a better option, then the hardware solution will be far less expensive, including physical management time.

Does anyone have any experience or insights here?

Answer 1

The biggest thing to remember about virtual machines (which is essentially what you'll get from a 'cloud' provider) is that nothing magic happened just because someone said 'Virtual'. Or 'Cloud'.

You still need to plan and test high availability instead of just assuming it'll work. You still need to worry about data corruption being written to replicas, etc.

Essentially all you'd be getting from pushing to the cloud is less visability of the platform - it's tempting to view this as less responsibility but if your business needs the cloud resources and they're not available (e.g. imagine a New York based business with an onsite server and cloud fail-over to a New Jersey datacentre a few months ago) then being able to point to a cloud vendor and say "It's your fault" doesn't help your website resume taking orders any faster.

Computers still break down, even those that run "clouds".

That's not to say you shouldn't do it. There are benefits to having an off-site replica ready to step in if you have problems, and there are benefits to moving the entire infrastructure to a cloud provider, so both approaches are valid. You just need to be clear about what precisely you're buying (you're not buying some "cloud", you're buying a service and you need to nail down precisely what services you'll have and what SLA they'll be under.)

Answer 2

It's important to clarify a couple points here:

• Some cloud architectures can provide 'no downtime for scheduled maintenance' -- from the use of VMotion and similar.

• Systems running with VMWare Fault Tolerance or similar can provide resistance to unexpected hardware failure, but there are significant limits to the setup (with VMWare FT, protected VMs can only have one CPU core).

• Neither is automatic just because you bought something labeled 'Cloud'.

Thus, for scalability, you will probably want to go with master/slave replication; this works just as well in a cloud setup as in a dedicated-hardware setup.

As databases are particularly sensitive to disk performance, you'll want to make sure you understand your cloud provider's IO QoS options and over-subscription ratio.

Answer 3

RAID5 Viewpoint

While some regard RAID5 as a poor man's disk redundancy solution, for your own safety and sanity, please get rid of RAID5 ASAP. Why ???

• In a read-heavy, low-write environment on a RAID5, I would just leave that to
  • Your budget
  • Your tolerance
  • Your blood pressure
• In a write-heavy, low-read or write-heavy, read-heavy environment, RAID5 is simply out of the question. This is especially true for InnoDB.

Now let's discuss InnoDB and MyISAM

InnoDB

If you do not use innodb_file_per_table, OMG all the activity would be centered around just one file, ibdata1. What is contained in InnoDB's ibdata1?

• Table Data Pages
• Table Index Pages
• Table Metadata for Managing TableSpace IDs
• MVCC Data (for ACID Compliance and Transaction Isolation)

Even reads in InnoDB tend to shroud rows with MVCC protection to allow repeatable reads and permit transactions to hit the same rows being read. Thus, reads as well as writes produce disk I/O in ibdata1.

Using innodb_file_per_table may relieve some of the disk I/O by separating Table Data and Index pages from ibdata1 into .ibd files. Yet, I would expect a somwehat noticeable performance improvement only for a limited time in a RAID5 environment. The table interaction is still somewhat the same. Every access to a .ibd file is always preceded by reference checks against ibdata1.

While the separation can bring significant performance changes, RAID5 would be what they call in the chemistry world, a limiting reagent. Any benefits expected from InnoDB layout changes would be neutralized by outside factors, such as RAID5. The presence of extra tablespace files due to innodb_file_per_table buys you nothing over time but just the presence of extra tablespace files.

MyISAM

When it comes to MyISAM, RAID5 is OK in a read-heavy, low-write environment provided you map all temp tables (using tmpdir) to another disk, separate from the RAID5. (Sounds like defeating the purpose of RAID5, eh?)

Please remember that table data pages live in .MYD files and its corresponding index pages live in .MYI files. A write-heavy environment (INSERTs, UPDATEs, DELETEs) will obligate RAID5 to slow things down. Given MyISAM's locking behavior (full table lock with each INSERT, UPDATE, and DELETE) in a write-heavy environment, a steady stream of DML will keep RAID5 rather busy and have DB users enter a brief-but-annoying time warp waiting for DML to complete.

Conclusion on RAID5

Under the hood, RAID5 has the following characteristics for writing with parity

• Read the old data block
• Read the old parity block
• Compare the old data block with the write request. For each bit that has flipped (changed from 0 to 1, or from 1 to 0) in the data block, flip the corresponding bit in the parity block
• Write the new data block
• Write the new parity block

Should any of these steps see the slightest intermittency, the RAID5 set enters a brief-but-annoying time warp. Multiply that by a huge number of writes and you will feel it in the database performance. Each of these steps could be a point of failure. Why?

According to Wikipedia about RAID5

In the event of a system failure while there are active writes, the parity of a stripe may become inconsistent with the data. If this is not detected and repaired before a disk or block fails, data loss may ensue as incorrect parity will be used to reconstruct the missing block in that stripe. This potential vulnerability is sometimes known as the write hole. Battery-backed cache and similar techniques are commonly used to reduce the window of opportunity for this to occur.

RECOMMENDATION (RAID5)

RAID10 not only provides stability but allows some leeway in disk maintenance without taking mysql down in most cases. When data is mirrored, you know where the data is going and you know from where the data is being read.

I would say go with RAID10. Unless you do not mind long periods of downtime, you cannot afford to do RAID5 disk maintenance in lieu of the necessary disk synchronization. In fact, the smaller the disks you stripe in RAID10, the faster the sync time would be after a RAID 10 disk maintenance.

Other things to consider

• Tune your queries
• Remove redundant indexes
• Cache as much of the data as you can
• Use covering indexes wisely

VMWare Viewpoint

Concerning the Master and Slave in VMWare, please make sure the Master and Slave are sitting in separate physical disks. If the disks in VMWare are RAID5, get another VMWare Cluster ready right now using RAID10, please.

Answer 4

If you want reliability then go with RAID 10 not RAID 5 and master/slave setup (RAID 10 gives you performance as well as reliability). I doubt you can get the IO performance of physical server (RAID 10) with any cloud provider. Use of cloud is very useful when your load/traffic is not consistent or you have traffic spikes 2-3 times a day. In such cases, you create new web server and database instances and discard them when traffic goes normal.

Backup your data regularly whether you are in cloud, on physical server with RAID 10/RAID 5 or master/slave replication. And most importantly, test your backups health often.

Answer 5

The whole point of a cloud is scalability and "no hardware downtime" therefore no lost data due to bad hardware.

You understand that "The Cloud" is merely just normal servers running virtualised operating systems. That can, and do, suffer more (usually much more) downtime and data loss than a normal dedicated server.

We currently are in the research phase of building a "Master" database for our e-commerce business

Is this venture solely for your Magento store database - or for some broader ERP implementation?

If the former, then start researching again. Magento isn't bound by its DB - you'll hit a LOT of other bottlenecks before MySQL becomes a concern. That is if you don't locate your MySQL server on distant "Cloud" VPS connected by a high latency, poorly routed, highly congested, highly contended, low bandwidth WAN connection.

I've seen more data loss and unreliable stores from DIY attempts at high availability - than with a simple, single-server solution.

Looking at your other question. You are spending $14k per year on a Magento EE licence - but trying to manage your own server?

There is a good reason that specialist Magento hosting providers exist - and its to prevent you spending and potentially losing a small fortune making the wrong decisions trying to DIY. You should be focusing on running your store and doing what you're good at - not trying to be a system administrator.