Joe points out the following from this interview with Twitter’s Alex Payne:
The problem is that more instances of Rails (running as part of a Mongrel cluster, in our case) means more requests to your database. At this point in time there’s no facility in Rails to talk to more than one database at a time.
(David and Rafe weigh in also. Good stuff)
We've run against multiple (five now) separate PostgreSQL servers for a long time now. To be clear, that’s five separate “databases” in the sense that PostgreSQL uses the term. Not a replicating / mirror setup – separate databases with different data but with similar structure.
Each of our clients gets a schema (again, in the postgres sense of the word) on one of the database boxes. There are multiple client schemas per database. When a box becomes over utilized we get another box and move schemas around.
Sidebar: this whole schema-per-client thing might seem like overkill but when we say client, we mean, a company that processes health claims for 50 to 1000 self-funded employers. Each client is fairly massive in the volume of data they load into the system and our entire market consists of maybe 400 total prospects. Those 400 companies process something like 60% of the covered US population’s health claims every year.
In addition to the client databases, we have a master-slave balanced and replicated shared schema that stores user account information and other data applicable to all clients.
We run multiple fastcgi dispatchers on two severely underutilized web boxes. Our database load dwarfs our web load as we’re doing live data-warehouse style queries, which are insanely intensive when compared to generating the HTML or PDFs to display the results.
When an HTTP request comes in, it can go to any of the fastcgi dispatchers. We practice shared nothing. We establish, from the shared schema, which client the user belongs to and can then determine the server and schema that houses their data. Each fastcgi dispatcher has a pool of connections: one dispatcher has one connection to each postgres server. We scope the connection in using a set of Rails extensions similar to ActiveRecord::Base::with_scope at the beginning of the request.
This situation is surely very different from Twitter’s but I hope it shows the difference between the statement made by Alex, “there’s no facility in Rails to talk to more than one database at a time,” and the significantly more problematic, “you cannot talk to more than one database at a time.” While the former may be true (and I’ll argue in a moment that it’s not), the latter is clearly false.
Talking about this on the level of having “multi-database connectivity” come out of the box in Rails is simplifying the problem to an unreasonable level, I think. Would the multi-database connectivity features meet my needs or Twitter’s? They are different problems with minor overlap and we’re talking about the minor overlapping piece like it’s the biggest part of the problem.
Most of the time spent getting our setup running was in the conceptual and data wrangling phases. The amount of time it took to implement the multi-database connectivity was negligible compared to the amount of time it took to devise a method of splitting things out at the data level. When all was said and done, the Ruby/Rails related bits were implemented in no more than 40-50 lines of code.
In my case, ActiveRecord provided exactly the right level of functionality. I can have multiple database connections established and write code to manage when each should be used. Control of which connection is used is managed at the model level. Connections cascade up inheritance chains and I can specify that one model use the connection specified on another model using a simple delegate statement:
class A < ActiveRecord::Base
end
class B < ActiveRecord::Base
class << self
delegate :connection, :to => A
end
end
Changing A’s connection changes B’s without effecting the connection used by any other model. We have a simple macro (uses_connection_of) that brings this down to a one liner for each top level model class:
class C < ActiveRecord::Base
uses_connection_of B
end
This is only the tip of the framework level customizations we've made to Rails over almost two years of development. In most cases, I find the base functionality well balanced for the general (80%) case. We expect to write additional framework code when we get into special case territory, which our multi-database/schema setup clearly is, and which Twitter’s seems to also be.
When I consider what contributed to the unraveling of J2EE, one thing that stands out is that it tried to do too much. The promise was that of infinite scalability based on tooling, which assumes that designing scalable systems is a general case problem. I now firmly believe that this is flawed reasoning. Frameworks don’t solve scalability problems, design solves scalability problems.
I picked up a word from Joe a few years back and find myself using it a lot: “friction.” When referring to framework and tooling, “friction” is a (subjective) measure of how much the tooling gets in your way when trying to solve a specific-case problem. I've come to evaluate frameworks based on two rough metrics: how far the framework goes in solving the general case problem out of the box and how little friction the framework creates when you have to solve the specific-case problem yourself. When a framework finds a balance between these two areas, we call it “well designed.”
Measured along these lines, there are portions of Rails that have a less than perfect balance but I don’t think multi-database connectivity is one of them. It seems to me that moving too far in one direction on this would cause lots of friction for moving in other directions. There just doesn’t seem like there’s a lot of general case to solve here when you dig into the details.
Bottom line for me is that Twitter’s scaling and multi-database connection issues seem to be just that: Twitter’s issues. David’s response seems to indicate that he believes Rails could probably do more here but how far could framework level support really go and how much friction would be created?
Discuss
Ryan,
I'm a big fan of your blog, and it’s great to hear your thoughts on this matter.
You’re absolutely right: these are Twitter’s issues. David managed to interpret my statement as a request to include multiple database support in Rails. It wasn’t. Whatever solution we come up will likely be too domain-specific, initially, to exist at the framework level.
That said, thanks for sharing your solution. It’s a logical one.
— Alex Payne on Friday, April 13, 2007 at 07:58 AM #
Hi Alex, thanks for stopping by.
To be honest, I thought your position was stated fairly clearly in the interview — I was trying to elaborate on it a bit. It bothered me that many seemed fixated on this one issue, which to me didn’t seem like much of an issue at all.
You’re doing a good thing for the Ruby/Rails communities by talking about the things you guys are running into. I hope the negative aspects of this experience don’t stop you from talking about future issues.
— Ryan Tomayko on Friday, April 13, 2007 at 08:35 AM #
Surely the pattern of a read/write master database and read-only slaves is common enough that it would be reasonable to put out-of-box support for it in Rails.
— Paul on Friday, April 13, 2007 at 08:40 AM #
Paul, that seems to be the way the discussion is moving on the blogs and mailing lists.
The feeling I got from Alex’s interview was that Twitter’s case was a bit more complex.
Still, I can see where you guys are coming from. At the very least, I think we can expect to see a few interesting gems and plugins come out of this discussion.
I'm personally enjoying reading all of the accounts of clustered productions setups immensely.
— Ryan Tomayko on Friday, April 13, 2007 at 09:08 AM #
So that’s what a shared-nothing application looks like. For that matter, you don’t have to centralize it at all; your clients are essentially running on completely separate systems and are just sharing a common web server (which is cheap enough). What technical benefit, if any, is there to all these being co-located (other than easy rollout of the shared code)?
— John Cowan on Saturday, April 14, 2007 at 08:00 AM #
Hi John,
Well, we do run multiple clients on a single database machine, so you still have all the benefits of shared resource use. We can shift client schemas from machine to machine based on utilization. Our clients range in size pretty significantly so the number of clients you can run on a single machine varies but we typically match up a large client with multiple smaller clients. We’re still experimenting with different approaches here.
I should also note that the databases are dumb. All of the application logic is in the web/app tier and is shared by all clients so there’s a maintenance win there. When we push up new releases, we don’t do it on a client-by-client basis because the client specific aspects of the system have no logic.
We’re also planning on experimenting with features that would take advantage of all of this data being stored in one place. For example, we have a full historical record of PPO network performance, physician and facility use, employer expense for different types of benefits, etc. While this data is currently kept in separate schemas, it would not be hard to create tables with normative / aggregate values across all clients. There’s all kinds of interesting benchmark type features that could come out of this but it’s tricky because this is health information and the privacy considerations are obviously a big deal.
There’s other benefits, of course. Having a small number of extremely specialized techs maintaining a system for everyone as opposed to having a whole bunch of non-specialized techs maintaining the system’s individually costs less and should result in better support. All of the standard ASP/SaaS arguments are in play.
— Ryan Tomayko on Saturday, April 14, 2007 at 11:53 AM #
Thanks for the explanations.
I wonder how, if at all, these insights can be extended to systems that don’t have such data firewalls between users. Perhaps a design in which those who write content own it, whereas those who merely read it have access to a read-only representation, perhaps one of many?
— John Cowan on Saturday, April 14, 2007 at 12:33 PM #
Excellent article, very interesting. However it is I think the second time I've heard this “we process 60% of the U.S. population’s health claims” statistic (or rather, non-statistic). For someone not in the U.S. this might sound like a much bigger number than I suspect it is, considering the disgusting state of healthcare in this country and the actual percentage of people coverered. Perhaps a real number would be more interesting? I hesitate to call the statistic a disingenuous statement, but it does have a certain smell to it.
— Marcus on Tuesday, April 17, 2007 at 04:25 AM #
On reflection, I apologize and take back the sharpness of my comment, you did state some very interesting numbers.
— Marcus on Tuesday, April 17, 2007 at 04:26 AM #
Whoa, whoa, whoa:
That’s not what I said at all.
We don’t “process” health claims. Our client’s do. We perform data-warehousing and analysis on processed claims.
We don’t do analysis on 60% of the U.S. population’s health claims. That’s the total size of the market we target. We currently have a small percentage of that market.
My point there was simply that each of our clients have a large amount of data. The reason for stating that was to illustrate why we've partitioned data along client boundaries instead of some other boundary like date or region. I'm not trying to claim that we’re a massive system here. I'm trying to show that scalability isn’t a general case problem.
Well, I wouldn’t take it that far but you make a good point: I'm talking about 60% of the covered population. I have no idea what percentage of the country isn’t covered. It’s significant, though.
— Ryan Tomayko on Tuesday, April 17, 2007 at 05:02 AM #
Is it just me or is this whole discussion kind of seem wrong?
I've bought in to the Rails thing. I've spent money on the books, started using it at work. In the Agile Web book, scale is treated with a simple, just cluster your database and throw more CPUs at it type approach. Which is fine, we’ll worry about it when we get there.
Now real users with real sites with still a somewhat modest load (we’re still talking magnitudes away from a Yahoo)are talking about it and simply clustering the database doesn’t seem like such as easy thing to do to make things work. Store data in different databases? Are these different table spaces or actually different databases? Shouldn’t that only be an option of the data really belongs in different databases? What happened to DRY? Or is it okay to have different replications to speed up different things in a database because that is supposed to be an easier problem to solve?
I'm not going to say that j2ee is the way to go but in 7 years of shipping code built on j2ee, I don’t ever remember seeing this kind of discussion. More to the point, there are “caching” objects and classes in j2ee frameworks that are called as much and have all sorts of different implementations. At one point we used a caching layer that actually was a different database but the pattern was “caching” it was called as much and the framework supported it.
Did I read correctly? Twitter dumped active record? Maybe there is a misunderstanding and they didn’t know how to make it do some odd database stuff? Maybe not. That just strikes me as a very very fundamental issue. Is it really rails anymore without active record, active support, rjs etc.? What if you just start calling primary keys pk instead of id and using some other old school “conventions” is it still rails? Meanwhile developer performance is still being mentioned but what does it cost timewise to do these exotic database things? That’s still developer time, isn’t it? Sounds to me like twitter isn’t pushing the edge of rails, they've got a 100% customed up web application built with Ruby and some other technologies and probably a fairly high benchmark of entry for any new folks they wish to add to their team. It’s not like any old rails hacker is going to drop in and be productive. Again, in the past, we built on top of BEA and JBoss because there was a pool of developers doing the same things and we never really had that much trouble finding folks to help out and they generally understood what and why we were doing stuff.
I don’t know, I'm still having fun but the marketing is really really shining through and as the buzz and the honeymoon fade it’s not feeling as great and certain as it did 18 months ago.
— Bill Clemson on Friday, April 20, 2007 at 03:18 AM #
there is no way to use a postgres cluster or the database level clustering is not enough?
— litch on Saturday, April 21, 2007 at 04:47 AM #
I'm doing something similar in a Rails app I'm developing. At the moment I choose a different database depending on the request’s host (e.g. the foobar database for foobar.com). I'm currently doing this as a before_filter in applicaiton.rb, depending on request.host. I was just wondering what your database choosing logic is? Do you have a better way?
— Matthew on Monday, September 10, 2007 at 12:58 PM #
HI, I m also a big fan of your blogs.Sir I have an issue can we use too databases in one rails app.Suppose we have frontend and backend named respectively Core and Admin.Can we split the models but connecting with each other?
Thanks Saket
— Saket on Wednesday, February 27, 2008 at 09:43 PM #
Bill Clemson said “I’m not going to say that j2ee is the way to go but in 7 years of shipping code built on j2ee, I don’t ever remember seeing this kind of discussion.”
Actually I was in the server-side Java world in 1997 (11 years ago), then in 1999 I was working with the very unstable J2EE reference implementation from Sun (since vendors weren’t on the scene yet) and the discussions we are having are EXACTLY the same as back then. You were just late to the Java scene, which is why you don’t remember.
— S. Potter on Friday, March 28, 2008 at 01:54 AM #
what plugins or gems are you using to create PDFs in Ruby? Can you provide any example in a future article?
— dfguy on Tuesday, November 18, 2008 at 03:06 AM #
Rails is fast enough for me now, will keep this article as reference.
— Rails Newbie on Wednesday, July 29, 2009 at 12:55 AM #
Sorry, comments are temporarily closed due to a broken spam filter. bbl.