Scaling out Postgres, Part 1: Partitioning(petrohi.me)
petrohi.me
Scaling out Postgres, Part 1: Partitioning
http://petrohi.me/post/30848036722/scaling-out-postgres-partitioning
4 comments
As someone who's currently looking for a new hosting provider, I'm curious to know what made you go with Liquid Web. My experiences with them have been so-so and have been thinking of moving to SoftLayer for possibly more reliable, albeit more expensive, hardware.
What were the reasons for moving away from MongoDB?
Our app does a lot of writing. We were on Mongo 1.8 which has a global write lock. So concurrency was a big problem under load. We also have a lot of users with networks of 200k+ people – not even including followers. This meant that we had to have arrays in the User collection with 200k+ IDs to perform a lot of our queries. The RAM needed for indexes wasn't really worth the cost and performance started to become a big concern. In the end, our data makes more sense in SQL with a cache layer (Redis in our case) rather than NoSQL.
Our experience thus far in Mongo vs Postgres: go with what you know. We probably could have made Mongo work for us (especially Mongo 2.0+), but it was easier for us to work out the performance issues with Postgres. Plus, things made more sense in SQL.
If you're using Mongo and need a cloud provider, I can recommend the people at MongoLab. They really went above and beyond helping us get up and running and are really cool people.
Our experience thus far in Mongo vs Postgres: go with what you know. We probably could have made Mongo work for us (especially Mongo 2.0+), but it was easier for us to work out the performance issues with Postgres. Plus, things made more sense in SQL.
If you're using Mongo and need a cloud provider, I can recommend the people at MongoLab. They really went above and beyond helping us get up and running and are really cool people.
Thanks for the summary.
Maybe I'm not understanding something, but there seems to be an implicit assumption that the message broker (RabbitMQ) is more reliable than the database. This seems to be a common fallacy.
Yes, I can see where you're coming from. Message queue in the article is more of an abstract service that provides certain guarantees and less of a specific RabbitMQ implementation. There are examples of message queues implementations on top of database--the resulting tradeoffs can be an interesting discussion.
Glad to see you are having "good problems".
Didn't like parallel queries using pgpool2?
Didn't like parallel queries using pgpool2?
We are planning to use pgpool2, but only for connection pooling. In our case the subset of postgres databases to execute a distributed query is dependent on the input parameters, current sharding configuration and cached routes. Cached routes are used to handle adding new shards without repartitioning--I will cover this in the second part.
I guess we could use pgpool2 parallel queries, but this would likely require some adjustments in how they are implemented.
I guess we could use pgpool2 parallel queries, but this would likely require some adjustments in how they are implemented.
Having to implement your own sharding in your application layer for a database with 3 tables is ridiculous.
No wonder people are moving to products like Cassandra, HBase, MongoDB, CouchDB etc which automatically and transparently shard across as many nodes as you like.
No wonder people are moving to products like Cassandra, HBase, MongoDB, CouchDB etc which automatically and transparently shard across as many nodes as you like.
I used MongoDB in a medium-sized production environment (Aol's Editions backend, which I built with my brother). The sharding, for our needs, was far from automatic. In fact, the only way to get MongoDB to shard records evenly across servers (this is with 1.8) was to for all intents and purposes implement our own sharding on top of it. In other words, the global write lock coupled with worthless auto-sharding (at least given our purposes: high write, temporary but mission-critical data) made MongoDB a PITA. That, along with the fact that we were more or less told that MongoDB would be able to handle what we were planning by the MongoDB consultants Aol hired completely soured my view of 10gen. The only reason we needed sharding in the first place was because we were getting large amounts of queued writes on a 32GB server with 16 cores (with < 300 w/s). Pathetic.
My point is that if you want to spread out your writes on a database that supports rich queries and honors the "C" in CAP, you're going to have to do some work. The distributed DBs are nice and shiny if you can forego some of the nicer querying features of relational DBs (or MongoDB), but not everyone's requirements fit into document-based records, and to assume they do is just NoSQL fanboism.
Note: I am using MongoDB for my latest venture because we are realistic about it's abilities and also because our development time with MongoDB is no joke about 1.5x-2x faster than with MySQL (or equivalent). So MongoDB has its place and it definitely a good system (especially after v2) but NoSQL isn't for everybody, especially if you can't get past the hype and look at decisions very technically and objectively.
My point is that if you want to spread out your writes on a database that supports rich queries and honors the "C" in CAP, you're going to have to do some work. The distributed DBs are nice and shiny if you can forego some of the nicer querying features of relational DBs (or MongoDB), but not everyone's requirements fit into document-based records, and to assume they do is just NoSQL fanboism.
Note: I am using MongoDB for my latest venture because we are realistic about it's abilities and also because our development time with MongoDB is no joke about 1.5x-2x faster than with MySQL (or equivalent). So MongoDB has its place and it definitely a good system (especially after v2) but NoSQL isn't for everybody, especially if you can't get past the hype and look at decisions very technically and objectively.
It is not the number of tables that requires sharding, but the amount of data and associated workload.
While automatic sharding definitely has its place, we wanted to be very explicit in our consistency tradeoffs.
While automatic sharding definitely has its place, we wanted to be very explicit in our consistency tradeoffs.
Sure. But let's be realistic here the amount of data you would be dealing with is pretty insignificant compared to the bigger users of Cassandra and HBase or even MongoDB.
But hey if you wanted to be explicit about consistency then I am sure you had your reasons. Just strikes me as all a bit odd. Then again I didn't realise PostgreSQL didn't have an automatic sharding feature.
But hey if you wanted to be explicit about consistency then I am sure you had your reasons. Just strikes me as all a bit odd. Then again I didn't realise PostgreSQL didn't have an automatic sharding feature.
yes, lets be realistic.
Automatically and transparently sharding across an arbitrary number of nodes is a case that is actually required by a tiny fraction of the industry. And that fraction is already paying people to monitor and control that sharding because they're too large to trust the "automatic and transparent" algorithms.
The big users of Cassandra and Hbase use them for deeper architectural reasons than simply automatic sharding.
Automatically and transparently sharding across an arbitrary number of nodes is a case that is actually required by a tiny fraction of the industry. And that fraction is already paying people to monitor and control that sharding because they're too large to trust the "automatic and transparent" algorithms.
The big users of Cassandra and Hbase use them for deeper architectural reasons than simply automatic sharding.
PostgreSQL with sharding is called Postgres-XC.
I think XC is a shared-nothing but globally consistent system. This lets you get some scaling up to a handful of nodes (maybe, charitably, a dozen...or two or three dozen), but as something that gives you ACID it is bound to have contention on the transaction coordinator at some point.
I'd say it is closer to Oracle RAC in the space of use cases, without some of the nutty (but very impressive, and expensive) engineering to get this "shared-something" tradeoff RAC has.
I'd say it is closer to Oracle RAC in the space of use cases, without some of the nutty (but very impressive, and expensive) engineering to get this "shared-something" tradeoff RAC has.
Only tested with CentOS 5.4 and is at version 1.0 ?
Seems odd for PostgreSQL not to have made cluster features more of a priority given the more towards cloud based deployments.
Seems odd for PostgreSQL not to have made cluster features more of a priority given the more towards cloud based deployments.
Well, PostgreSQL is a project -- not a company. As a project, it is very roughly run by consensus, and that means acting strategically is not something the project as a whole does very much.
Sure, there are many project goals, but they're rather informed by what various correspondents of pgsql-hackers (this could be loosely coined "the community". Admission is free.) seem personally willing, interested and/or funded to do. You, too, can be a database internals engineer! And Postgres's code is still considered crisp enough to fork for Your Very Own Database Startup (which I think is pretty remarkable -- most such stand-alone programs are typically not worth reworking), so you can waltz right on in.
One piece of project gridlock is that the bar is very, very high for any new code to be committed (and almost certainly released), and that goes double if you are not someone who is proven to suffer through all the bugs one is going to find over the foreseeable future and no one else is excited about doing that for you (if you submitted some excellently written, badly needed functionality, I think it would be accepted under that rationale, though, in spite of most complexity concerns). Postgres has a long history, so "the foreseeable future" lasts an awfully long time.
Some old features like the way hash-group and hash indexes work I am reasonably confident could not be committed if submitted as-is today. (The former can't spill to disk and the latter has no crash recovery, which is why the documentation shoos you away...but some people use it?)
Besides bugs, there also needs to be a lot of convincing that the feature is worth whatever complexity it brings, in implementation and in user-interface.
In spite of that, a lot of code has gotten committed, and not without some trepidation as to the sheer amount of growth. I used to hack a fork of Postgres a few years ago, and the project is probably about 10-20% bigger now:
http://www.ohloh.net/p/postgres/analyses/latest/languages_su...
The problem is hard, there is not even a single solution everyone agrees is the best, reliability and maintenance requirements are stringent, and even in spite of all that the project's code is growing at a rather scary clip.
All in all though, I think a lot of the pre-requisites for what you want are locked up in the attack on logical replication, which is ongoing in the 9.3 release cycle.
Sure, there are many project goals, but they're rather informed by what various correspondents of pgsql-hackers (this could be loosely coined "the community". Admission is free.) seem personally willing, interested and/or funded to do. You, too, can be a database internals engineer! And Postgres's code is still considered crisp enough to fork for Your Very Own Database Startup (which I think is pretty remarkable -- most such stand-alone programs are typically not worth reworking), so you can waltz right on in.
One piece of project gridlock is that the bar is very, very high for any new code to be committed (and almost certainly released), and that goes double if you are not someone who is proven to suffer through all the bugs one is going to find over the foreseeable future and no one else is excited about doing that for you (if you submitted some excellently written, badly needed functionality, I think it would be accepted under that rationale, though, in spite of most complexity concerns). Postgres has a long history, so "the foreseeable future" lasts an awfully long time.
Some old features like the way hash-group and hash indexes work I am reasonably confident could not be committed if submitted as-is today. (The former can't spill to disk and the latter has no crash recovery, which is why the documentation shoos you away...but some people use it?)
Besides bugs, there also needs to be a lot of convincing that the feature is worth whatever complexity it brings, in implementation and in user-interface.
In spite of that, a lot of code has gotten committed, and not without some trepidation as to the sheer amount of growth. I used to hack a fork of Postgres a few years ago, and the project is probably about 10-20% bigger now:
http://www.ohloh.net/p/postgres/analyses/latest/languages_su...
The problem is hard, there is not even a single solution everyone agrees is the best, reliability and maintenance requirements are stringent, and even in spite of all that the project's code is growing at a rather scary clip.
All in all though, I think a lot of the pre-requisites for what you want are locked up in the attack on logical replication, which is ongoing in the 9.3 release cycle.
Postgres's replication is very good these days, and it works for many of the read-scaling use cases. Scaling writes is a harder problem and I don't think there's a automatic drop-in solution for any sql store that I know of. Mongo gets away with it because it's non-relational so you never have to worry about joining across servers.
Probably because the PostgreSQL people value humility more than hype.
I don't have any experience with HBase and CouchDB, but Cassandra and MongoDB will only shard automatically and transparently if your application does not do much writing. :-P
Also, the number of tables has nothing to with the problem of sharding, it's the amount of data you're trying to store.
Also, the number of tables has nothing to with the problem of sharding, it's the amount of data you're trying to store.
HBase guy chiming in. Sharding in HBase is a bit different than the other NoSQL guys. HBase is super fast when doing random lookups and scans, so the developer is in complete control of his failure or success, depending on the row key design.
Good thing about HBase is that it's designed to hold a massive amount of data, I've seen people with hundreds of thousands of columns on a single row, it's pretty remarkable.
Good thing about HBase is that it's designed to hold a massive amount of data, I've seen people with hundreds of thousands of columns on a single row, it's pretty remarkable.
[deleted]
I suppose you would have an issue with MongoDB specifically if you were trying to rebalance from day one. But generally you wouldn't. I know Foursquare is using MongoDB's Auto Sharding in production and they would definitely have a lot of writes.
And the number of tables DOES matter for sharding if you are implementing it yourself.
And the number of tables DOES matter for sharding if you are implementing it yourself.
Foursquare's experience with sharding MongoDB has, as far as I understand, been far from automatic and transparent (based on their blog posts and the word on the street).
I've heard Foursquare having a lot of problems with the terrible behaviour of MongoDB once your indexes go above 95% of the machine's memory.
Never with the sharding part though.
Never with the sharding part though.
We also initially wrote the app to use a mix of Postgres and MongoDB but ended up moving everything into Postgres.