by
aaron on Thu, 05/24/2007 - 10:13
Recently we've been working with a couple of vendors to identify some potential solutions for scaling backend databases horizontally. This problem has been plagueing drupal.org, as well as many other large scale Drupal sites. Drupal.org has been plagued with database issues (as the infrastructure team is painfully aware), and we're doing what we can to help.
We've taken a look at third party hardware, software, and MySQL's own technologies, and some actually do have promise.
There are two scenarios we are dealing with, and they both have their own set of challenges to overcome.
- Multi-tenant Hosting: several databases coexisting on the same mysql server instance.
- Single-tenant Hosting: a single database on one instance of mysql, ideally spread across multiple servers. (Drupal.org falls into this category)
The market breakdown:
- Continuent uni/cluster provides a middleware solution for MySQL/PostgreSQL that proxies requests between your application and the database servers, allowing one to scale the backend horizontally. It's a great idea, but the price for most startups is a tad prohibitive, and it suffers from write performance problems.
- Sequoia is Continuent's open source offering. It offers a subset of the features provided by their commercial offering, and should work surprisingly well for single-tenant installs that do a lot of reads (it still has issues with write speed). Other downsides include the requirement of a specialized odbc driver, as well as a less-than-stellar memory footprint of 512MB per sequoia instance (it's a Java process, and it's generally run on each MySQL physical backend server). This is, of course, assuming that you have the required 3 days to set it up properly.
- Dolphinics provides a hardware based solutions for clustering that allows multiple servers to share the same memory pool in a very low latency. Ideally this works in tandem with MySQL Cluster to allow the cluster to be grown by simply throwing more hardware at it. We're currently testing out this solution, and will update as we have more.
- MySQL Cluster provides a solution that should theoretically solve most of the pain point for the market, but it only addresses the availability element of the equation and not scalability. In order to grow your cluster, you are required to have a hot standby cluster replicated and waiting, and then have to export and re-import all of your data once the cluster has been expanded.
The more I think about it, the more I'm frustrated that MySQL hasn't solved this problem reliably themselves. Not even MySQL's own site is using Cluster at this point (they're still using master-slave replication), so I'm worried that they've shipped a solution that doesn't provide what the market is demanding.
We've been talking recently with Continuent to help address this issue, but the problem is far more than "just throw more hardware at it." Our discovery with them led us to understand that there are different models for scaling; some around replication, others around data partitioning, and it's a matter of finding the right balance of the two for a given solution.
We'll be working more with both Continuent and Dolphin, as I can guarantee that hope of a working solution is on everyone's mind.
Tags: clusteringcontinuentdolphinDrupalhigh availabilitymysqlreplicationsequoia
aaron's blog
Comments
Amen
I've started researching this stuff also and am equally non-excited about MySQL scale out options when using the criteria you are implying.
While the sites I am working on now will be large by traffic volume (reads and writes) the traffic is cyclical, so while we need to be highly reliabe we have the option of taking the sites down for maintenance windows.
Alas I have not found the MySQL site to be super helpful on their clustering solution, though some of their white papers were helpful. I am waiting for a call from them (I guess next week at this point). From what I read (at least) I didn't get the impression that cluster could not grow as you descibed above. How did you arrive at that conclusion?
Thoughts on MySQL
In general, my experience with MySQL cluster has been that it requires a high degree of technical aptitude (more than most admins), that it's technical to convert databases onto it, and that it requires a lot of hardware to get started (6 machines to get "real HA").
Recently I learned that MySQL Cluster was created on physical cards, and I've got a few here for testing, but more on that later. It's really quite interesting.
When I'm planning really big sites, I often use MySQL cluster as part of the HA planning. In general, it's better for databases that are more 1-1 (reads/writes).
If you're working on a really large database with high reads, a M/S setup works best, and if you need it (it's brittle) you can do a M/M S/S (think of an upside down U with slaves attached to each master). The problem is that indexes tend to get out of wack really easily, and it can be hard to recover - we refer to that as brittleness.
I'm really interesting learning about individuals experiences. I'd love it if you posted back, or even let me interview you, about the MySQL solutions as you explore them. It think it would be super-awesome to explore some of this for a podcast, and whatever experience I can bring to the table would be on me.
For the record, Aaron is quoting me on the dual clusters, because if you want to grow the cluster, it can be sometimes manual and require export and re-import. YMMV, and I know a lot of effort has been put into this recently. I'm actually supposed to get an intro the MySQL "Cluster Guy" who I've been told "gets it" when it comes to cluster - I'll post an article if said event occurs.
Let me know if you are interested.
SQL Relay
Have not used it, but it seems to do some of what you said, and it is open source.
SQL Relay.
I've downloaded this
I downloaded that tool just this week, but I haven't had a chance to look at it. I'm a little concerned that it's not ACID compliant. I'll get a chance to look at it later this week - I'm running behind on stuff for Drupal (as always!).
What excites me is this, which is a lot like Wackamole (a tool I love).
I actually think this architecture makes sense. I think Wackamole should be used on Drupal.org btw. ;-)
HA-JDBC
Hi,
Did you consider running Drupal on Java and using free Open Source HA-JDBC drivers which is more easy to install than C-Jdbc/Sequioa and can wrap other JDBC drivers?
I would also reccomend JRocket (free from BEA) JVM for 2x better runtime performance. And replacing Resin with Glassfish to get the Grizzly NIO socket peformance pack developed by Sun thats added to Tomcat. All products mentioned are free open source..
Best Regards
Carlo
Post new comment