MySQL

"By 2010, Global 2000 IT organizations will use open-source products in 80 percent of their infrastructure-focused software investments".

Gartner, Gartner – 2005

This HOWTO was designed for a classic setup of two servers behind a loadbalancer. The aim is to have true redundancy - either server can be unplugged and yet the site will remain up.

Notes:

You MUST have a third server as a managment node but this can be shut down after the cluster starts. Also note that I do not recommend shutting down the managment server (see the extra notes at the bottom of this document for more information). You can not run a MySQL Cluster with just two servers And have true redundancy.

Although it is possible to set the cluster up on two physical servers you WILL NOT GET the ability to "kill" one server and for the cluster to continue as normal. For this you need a third server running the managment node.

I am going to talk about three servers:

mysql1.domain.com 		192.168.0.1
mysql2.domain.com 		192.168.0.2
mysql3.domain.com 		192.168.0.3

Servers 1 and 2 will be the two that end up "clustered". This would be perfect for two servers behind a loadbalancer or using round robin DNS and is a good replacement for replication. Server 3 needs to have only minor changes made to it and does NOT require a MySQL install. It can be a low-end machine and can be carrying out other tasks.

Alex Davies, HOWTO set up a MySQL Cluster for two servers – February 8, 2005

• CNET Networks uses MySQL to store FULLTEXT searchable data for many of their Websites' search applications.
• Friendster uses MySQL to serve more than 85 million dynamic page views per day and is capable of supporting over 1.5 billion MySQL queries per day.
• Wikipedia is the most popular wiki in the world, operating in more than 50 languages. Wikipedia uses MySQL to process more than 200 million queries and 1.2 million updates per day.

MySQL, MySQL Application of the Year – 2005

AdWords was built using the MySQL database, which is open-source and therefore available for free. It is by now also nearly as full-featured as the best commercial databases, but back in 2000 this was not the case. MySQL was quite a capable system, but missing a few (what some would consider basic) features. These missing features were obviously not a show-stopper, as we managed to get AdWords to work without them, but in a few cases it did take some extra programming to work around one of these missing features. On the plus side, MySQL was fast and reliable and, as I have already noted, free.

We finally decided to go with a commercial database (I won't say which one) over the objections of a number of engineers, including myself. To ease the transition it was decided to convert AdWords over to the new system first, and to do the main ads system later. It was a project on a par with the internationalzation effort in terms of the tedious work required to comb over nearly all of the AdWords code and change all of the database queries. (Databases are supposed to all be compatible with one another, but in reality they pretty much aren't.)

To make a long story short, it was an unmitigated disaster. The new system was slower than molasses in February. Some heroic optimization efforts eventually produced acceptable performance, but it was never as good as the old MySQL-based system had been. For a long time we were stuck with the worst of all possible worlds, with the two ads systems running on two different databases. It was still that way when I left Google in October of 2001, but I have heard through the grapevine that they eventually went back to MySQL. (Since then, MySQL has added many of the features that had been missing at the time.)

The moral of the story is that sometimes, and in particular with free software, you get more than what you pay for. There are a lot of companies out there paying dearly for commercial databases (and operating systems for that matter). As far as I'm concerned they might as well be flushing that money down the toilet. Actually, they might be better off. We certainly would have been.

Xooglers, Let's get a real database – December 9, 2005

tags are an interesting one. lots of the 'web 2.0' feature set doesn't fit well with traditional normalised db schema design. denormalization (or heavy caching) is the only way to generate a tag cloud in milliseconds for hundereds of millions of tags. you can cache stuff that's slow to generate, but if it's so expensive to generate that you can't ever regenerate that view without pegging a whole database server then it's not going to work (or you need dedicated servers to generate those views - some of our data views are calculated offline by dedicated processing clusters which save the results into mysql).

federating data also means denormalization is necessary - if we cut up data by user, where do we store data which relates to two users (such as a comment by one user on another user's photo). if we want to fetch it in the context of both user's, then we need to store it in both shards, or scan every shard for one of the views (which doesn't scale). we store alot of data twice, but then theres the issue of it going out of sync. we can avoid this to some extent with two-step transactions (open transaction 1, write commands, open transaction 2, write commands, commit 1st transaction if all is well, commit 2nd transaction if 1st commited) but there still a chance for failure when a box goes down during the 1st commit.

Tim O'Reilly, Database War Stories #3: Flickr – April 27, 2006

all database machines are on 64 bit linux boxen/ 14 local drives with 16gig of ram.

craigslist runs clusters of dbs for each of our various services: forums: 1 master and 1 slave (mostly for backup) myIsam tables everywhere. DataDir size including indexes 17G. Largest table is approaching 42 million rows.
classifeds: 1 master and 12 slaves. We have various flavor of slave databases. we have teamreader, longreader, thrashbox, for backups and very long adhoc queries and a few extra boxen. At times we have an offsite slave incase the colo goes dark. Currently this is on hold until we get a bigger pipe to our office location. Current footprint including indexes 114G, 56 million rows in the largest table (it's time to archive some of those oh yes it is) yesterday we wrote 330000 new rows to this table; Myisam everywhere, mostly because it works.
ArchiveDB: 1 master 1 slave. holds all craiglsist postings older than about 3 months. Looks very similar to classifieds except bigger. 238Gigs, 96 million rows. Oh yea we use merge tables all over the archive spliting data into more managable chunks. We may do this in production soon.
searchdbs: 16 of these in 4 clusters. We take live postings and split them by area/category type (sfbay/housing) and then use myisam full text indexing. each cluster only contains a subset of all positngs. We find the right host/table in software. This runs good, but do not think this solution will scale for much more than year. Indexing is expensive and we have a lot of churn.
Authdb: 1 master and 1 slave. smallish.
a few smaller "junk" db's that have transient data.

Tim O'Reilly, Database War Stories #5: craigslist – April 28, 2006