Five months with MySQL Cluster

So, the whole world changed at dealnews when Yahoo! linked us. We realized that our current infrastructure was not scaling very well. We had to make a change.

The Problem

Even though we were using all sorts of cool techniques, the server architecture was really still just a bunch of web servers all serving the same content. In addition to that, our existing systems as the time used a pull method. When a request came in, memcache was checked, if the data was not there, it was fetched from our main MySQL server. So, when there is no data in the cache or when it expires, this was very bad. Like when Yahoo! hit us. Some cache item would expire and 60,000 users would hit a page and each page would try and create the cache item.

The Solution

I was tasked with two things. Find a way to handle something like the Yahoo burst and finding a way to store the data we need to generate our web pages that was highly available and would scale. For bursting, I wrote a proxy using apache, mod_rewrite, php and memcached. I have reasons I did it this way that are not relevent to this post. Maybe more on that later.

For the data solution, I considered several things: MySQL replication, writing my own replicating memcached client, and other exotic ideas. One of the semi-exotic ideas for us was MySQL Cluster. We had not used it at all. Some things about it made us gun shy. But, we tested it and were very happy with the results.

Initial Test

With the help of Gentoo, getting a cluster up and running was really, really easy. In fact, it seemed too easy. We ran a cluster on some dev boxes at first. We did some generic testing using the PHPTestSuite from the guys at MySQL Performance Blog. What we found was that while the cluster appeared slower at low concurrent connections, it scaled much better than InnoDB (our prefered storage engine) when the concurrent connections grew.

Application Testing

So, we moved to the next step, testing our application. We discovered early on with cluster that we would have to redesign our application. Our DB was highly relational. Almost no data could be put on the site without data from other tables. We used a lot of joins. We learned (later) that joins in the cluster are not a good idea. Neither are sub-selects. So, we wrote some proof of concept scripts for our application. We were very happy. Very few issues were found. Nothing anywhere near show stopping.

Installation

We ordered our servers. Six new Dell dual-core, dual processor Opterons with a lot of memory. Two would become SQL nodes and the other four would be storage nodes. Our data set is not that large compared to a lot of companies. So, we configured the cluster with 4 replicas. Our main goal is high availability and scalability. I could find nothing in my tests or in the manual that indicated this would be bad for scalability and it should be great for HA.

We rewrote our application (basically, our public web site) to use the new cluster and its new table design. We hit our first snag when we tried to seed the data in the cluster. We got errors from the cluster about its transaction logs not being big enough to handle the inserts. Through the manual, forum posts, the mailing list archives and some blogs I was able to find the correct settings for our needs. I remembered back when I first installed the cluster thinking it was too easy. I now realize that getting a cluster running is easy. Making it run well, is a whole other story.

The second snag was with joins. Our test bed for the cluster was not a cluster. We used a group of servers using InnoDB to test against. That was a mistake. Joins did not work at all with the cluster. We had to back up, rewrite some code and redo some tables. In the end, the design is probably faster on InnoDB or cluster.

Everyday Use

We started using the cluster for every day use about a month ago. I guess 5 months is not bad for starting from nothing to live in production. We have been slowly moving applications to it. We take care each time to monitor the cluster and see that its not throwing new errors. So far, so good. We have about 80% of our page views (40% of our page views are our front page) and about 50% of our end user applications using the cluster now. We are doing caching at the proxy level for a lot of this. But, when tested, the new architecture is much more reliable even without the caching proxies. Some things like our forums will never translate to the cluster. But, they have their own dedicated systems already and are non-critical for our business. They could be shut down if there was a problem with them.

Administration

MySQL Cluster is a whole new animal. Its not like monitoring mysqld, apache or other stuff we already use. It took me a while to get the hang of rolling restarts, brining nodes up and down after crashes, etc. We have had just one crashed node since we switched over to production use. The cluster stayed up and kept serving content. We have written a Nagios monitor to keep track of the nodes’ status. It uses ndb_mgm and reports any problems to us.

Feedback

Now, as the title says, I have only been using MySQL Cluster for 5 months. If you are reading this and have more experience and are thinking “What a moron!”, please tell me. We are still learning.

Update:

Ronald Bradford had some questions on his blog for me.  I figured I would just answer them here.

You didn’t mention any specific sizes for data, I’d be interested to know, particularly growth and how you will manage that?

We currently have a DataMemory of 4GB and IndexMemory of 2GB.  Based on the crude methods we have to monitor it, I think we are at about 40% capacity.  We are using MySQL Cluster purely as a data store for content on our web site.  So, we can trim the data store down significantly.  If it does not appear on the site, its not in cluster.

You also didn’t mention anything about Disk? MySQL Cluster may be an in-memory database but it does a lot of disk work, and having appropriate disk is important. People overlook that.

Yes, we have U320 15k SCSI drives.  We do use RAID 1 on our servers contrary to some opinions.  We see a lot of drive failures.  About one every 4 months.  Sucks to lose a whole machine just because a $200 drive failed.

You didn’t mention anything about timings? Like how does backups for example compare now to previously.

Well, we don’t currently back up the cluster data as it is being copied from our main database already.  Maybe that is a mistake, I don’t know.  But, I can’t come up with a reason to backup data that is just a copy of another database server.  Also, I have written a PHP class that does parallel writing to multiple servers using transactions.  Everything we write to the cluster also gets written to an “oh shit” mysql server that users InnoDB.  So, in the event we have a total cluster failure, F5 BIG-IP load balancers will send mysql traffic to the InnoDB server.

You didn’t mention version? 5.1 (not GA) is significant improvement in memory utilization due to true varchar support, saving a lot of memory, but as I said not yet production software.

Yeah, I am drooling over 5.1.  But, we are using current Gentoo stable, 5.0.38 I believe.  5.1 looks superior in many many ways.  I can’t wait to upgrade.

12 Responses to Five months with MySQL Cluster

  1. Vetle says:

    Interesting! What kind of applications was this?

    We’ve been thinking about MySQL Cluster, but we were aware that it doesn’t like joins and stuff like that, so we’ve been hesitating… We’re going to have to rewrite stuff in our app to be able to scale anyway, perhaps MySQL Cluster could be an ok solution after all.

  2. doughboy says:

    It is dealnews.com. Just go there and see it. 80% of the pageviews at dealnews.com get their data straight out of mysql cluster. Unless of course they are cached by the proxy. But, even without the proxy, it does very very well. Most of our bottleneck is still in PHP generating HTML. Very little time is spent waiting on MySQL Cluster.

    Of course, you will need to evaluate your application. I will try and post some of my cluster tips and tricks later.

  3. Brian. My comments grew, I’ve posted them as a more detailed response at http://blog.arabx.com.au/?p=718 Some bullet points to entice the reader more!

    . Firstly, it’s great you wrote about your experiences in moving to MySQL Cluster. I think more people should.
    . It’s great you acknowledged that you had to rewrite your application.
    . Interesting. You don’t see many references to more then the default, documented and accepted 2 replicas.
    . “What a moron!” — Far from it, I hope your article helps in the education of MySQL Cluster more to the community.

  4. Monty Taylor says:

    Hey Brian,

    I’m happy to see you have a Nagios plugin… I’ve got a cacti plugin that uses the MGM API to track various statistics in cluster.

    https://code.launchpad.net/get-ndb-status

    It could also be extended to be a decent Nagios plugin as well, or maybe the get-ndb-status project should just add a second Nagios plugin that does different things. If it winds up being useful to you, I’d love any feedback you have.

  5. doughboy says:

    Cool Monty! I am not the Cacti/Nagios guy, but I wil get your link to the right people. If you think it would be helpful, I will see about getting the Nagios stuff put on http://dealnews.com/developers/.

  6. Monty Taylor says:

    Totally… I think the more we can start sharing cluster tools out there, the easier it will be to deal with. Too many people are rewriting the same things over and over again.

  7. Also, posting and discussing the code somewhere (Cluster list at lists.mysql.com perhaps) will let more review happen – some of the APIs do have some oddities – although some are amazingly easy to use (esp the management ones for some things).

    We’d also love to improve them to help with real world situations and make people’s code nicer…. so seeing how they’re used is great.

    glad to hear you’ve been having good experiences with NDB!

  8. [...] Cluster SQL Tips So, I mentioned in my MySQL Cluster post that I found out that cluster and joins don’t get a lot too well. There are a couple of [...]

  9. Tech Tools says:

    Thanks for the post, it’s very informative.
    I recently set up a MySQL cluster for a client and for some reason when using ‘ORDER’ with certain large queries take a few seconds to complete when other queries are instantanious.
    I haven’t yet found any good sites with information on optimizing ndb yet
    and am wondering if you know of any good resources for this.

  10. doughboy says:

    I would have a look at an explain and make sure you are not having to do a file sort. If you are, that is likely your problem. You will need to create keys that deal with your where AND order in one key.

  11. [...] cluster and all dump 1000 So, I had written a while back: “We currently have a DataMemory of 4GB and IndexMemory of 2GB. Based on the crude methods we [...]

  12. [...] I hosted the Caching for fun and profit BoF.  It was not packed, but it was a good time.  The MySQL BoF was at the same time, so we lost some folks to that I am sure.  They had beer and pizza.  Brad Fitzpatrick did come by and contribute.  Thanks Brad.  It was mostly the same stuff you get on the memcached mailing list.  “How do we expire lots of cache at once?”  Questions about different clients.  Stuff like that.  It kind of turned into a memcached BoF, but I tried to share the dealnews experience with the attendees including our MySQL Cluster pushed caching. [...]

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: