Urban Airship: Postgres to NoSQL and back
wiki.postgresql.org
wiki.postgresql.org
When you're starting out, you go with something on the cloud -- EC2, Rackspace, Softlayer, Linode, or whatever cheeses your hamburger.
That's just common sense. There's no reason for a fresh web or mobile startup to have tens of thousands of dollars of server inventory, bought for the anticipated rush of people that want to get in on the 'social synchronized tuba-thumping' craze.
But when you hit the point where you're spending $10k a month on cloud hosting and your team is having to put out performance fires thanks to the shared hardware, why not just buy servers and throw them in a datacenter?
Amortized over a year, you'll spend a lot less on capital (network and hardware), and a lot more on labor, as you'll need at least one more team member to handle the on-call rotation load.
Apologies if this is rambling -- it's about 7:30am here, and I'm a bit low on sleep. :)
In the end I'm a big fan of owning the hardware and dealing with the issues yourself.
That said, a small ops team (three people) can manage thousands of machines without much difficulty, and you can probably share two of those with the dev team as Dev-Ops guys, with one grizzled old sysadmin to handle the nastier problems.
I think that a lot of companies shy away from buying hardware for two reasons. One, because they haven't sat down and looked at the long-term ROI, and two, because for the past five years or so, every piece of IT media has talked about how stupid buying hardware is, and how we're all just going to go live in the cloud.
Sounds quite a bit like the NoSQL rhetoric, actually.
The reality is that at a certain scale, it's cheaper from a strategic and/or monetary standpoint to have in-house hardware.
Don't get me wrong -- there's definitely a big home in the ecosystem for cloud hosting. Probably less than ten percent of businesses need to buy their own hardware. But the ones that do, need dedicated hardware in a bad way.
I worked for a company that contracted with a guy whose entire business was based on this model. He was the hardcore sysadmin and would juggle tons of servers and administrative config for you. He trained the devs to handle all of the mundane ops stuff, with him as backup.
Really great system. It was really empowering for us devs, too.
It is expensive, but not as expensive as something like heroku or aws for equivalent cores/RAM, and you get real hard drives.
In certain datacenters (think might be dallas05?) you can even get a 10Gbps uplink brought to a box so they have pretty big pipes available.
In Softlayer's Dallas facility (selected when ordering a server) 10 Gbps is an option.
The maximum amount of bandwidth available is 20TB.
Sometimes you can get more options by contacting their sales people directly instead of using the shopping cart.
The real killer with the cloud is bandwidth and storage. With my colo deal I am paying about 5-10% of what bandwidth would cost at Amazon. Not to mention I can put any kind of storage I like with the actual cost per Gb to buy outright being equal to monthly cost on EC2. Go figure!
Cloud has its place to handle spikes of demand, but it is really not cost effective to scale.
There are advantages of a Hosting company buying hardware for you and amortizing it. It is impossible for a small startup buying < 5-10 servers to achieve say a cost of Rs. 100-200 USD per month per server including amortization ( say 24 months ), bandwidth, power and remote hands per server. And I super agree with the fact that it is easy to run high bill on cloud hosting where you pay through your nose for everything except perhaps the base level machines for the privilege of being able to burst your load to 100x. Only about 2% of the customers on cloud actually require burst capacity beyond 2x. It is easy enough to achieve cost savings of 30% with always-on 2x capacity using standard dedicated servers from a dedicated server focused hosting company than buying capacity on the cloud and using maybe burst capacity 5% of the time.
I love NoSQL as much as the next person, but turning straight to NoSQL when you are faced with scalability problems in a more conventional relational database is always going to be a mistake. Before you translate everything over to a NoSQL db, try dropping the ORM (or find another one), looking at your table structures, or tuning your indexes. If you do this, then there is a strong possibility that you will save yourself some time, energy, and effort.
It pays to think deeply about your issue. NoSQL should be another tool in the toolkit, and not the hammer to be used to drive all of your database issues into the wall.
When you turn to a NoSQL solution, you should not be doing so with a mind to find a "magic bullet". You should be doing so because you have thought deeply about the problem and have found that a NoSQL solution answers a specific need.
If everyone could use NoSQL when it is appropriate to do so, then there will be less "horror stories" and more illustrations of valid use cases than what we have today.
Dropping or replacing the ORM is a big undertaking in anything but small toy projects.
Better advice is to run a profiler and crank up the ORM's verbosity in order to determine the extent of overhead imposed by the ORM, and something like pg_stat_statement and EXPLAIN ANALYZE (in the case of Postgres) to find slow statements and see why they are slow. This will give you a much better idea of where time is spent, to what extent things can be optimized, and whether the ORM is to blame for any performance issues.
A 90 GB database fits in a single $150 SSD. You can get 1 TB of SSD storage for $3,000.
"One EC2 Compute Unit provides the equivalent CPU capacity of a 1.0-1.2 GHz 2007 Opteron or 2007 Xeon processor. This is also the equivalent to an early-2006 1.7 GHz Xeon processor referenced in our original documentation. " [1]
You can get a 6 core AMD Phenom II which runs at 3.2Ghz. for $180.
16GB of RAM will set you back $400.
From the sounds of what they went through, spending $10k on decent hardware might have saved them a man year or two of developer time.
Granted that's not nearly as fun or sexy as trying to use MongoDB, Cassandra or HBase in production. And, saying that you're going to use actual hardware is soooo old school.
ref: [1] http://aws.amazon.com/ec2/faqs/#What_is_an_EC2_Compute_Unit_...
The larger EC2 instances (especially for always-on systems like primary databases) do get quite a bit cheaper with the 1-year reserved instance reservations, so if you are on EC2 be sure to get those as soon as you're at a somewhat stable point.
Also, I don't think you even need $10k in hardware. Sounds like you could do just fine with a $3k 1U server. $3k can still get you 16 cores w/ 32GB memory and SSD drives.
1. The "NoSQL" field is generally immature and filled with land mines, so a random selection favors a bad outcome
2. The mass-appeal rank ordered reputation of these projects is not in line with their actual quality/robustness, especially when it comes to very high-demand situations (node counts are high enough to render node failures common, write loads are heavy, machines are within 50% of their I/O limit).
So, I wouldn't let these reports discourage one too much about the possibilities of NoSQL, especially the scaling possibilities of fully distributed databases. It really can be a game changer with a solid implementation. Riak is one.
(Note: "wrong decision" is not meant to be judgmental, we (bu.mp) are ourselves just recovering from a similar "wrong decision"... VERY similar. :-)
Agree completely re: bare metal. If you have scaling problems you should have a very good reason not to be doing dedicated hosting on bare metal IME.
Can you write up a case study? What did Riak do right where the others failed? What are the downsides of it, and why are those downsides more tolerable than the downsides of other systems?
Or perhaps I'm missing something?
It's ideal for the counter use-case and with bit of smart thinking you can fit a surprising amount of data into a 32G or 64G machine (and then you can always shard).
I tried doing the same thing on a Macbook Pro i7/SSD and it took ~1 hour.
EBS disk performance is reliable, but miserable.
EBS performance is unreliable and miserable. There, fixed that for you.
However, EBS also has a couple things going for it. Namely: easy snapshots, fast/seamless provisioning (need another 1T?), mobility (need that volume on another instance?) and quite a fair price point when you take all that into account.
All of which are doable with LVM.
EBS is just a mouse-click.
It's far from "rack and forget" once you've grow beyond 2 racks.
I have dealt with storage 'at scale' enough to require a full team of storage people.
Some guy clicking buttons on the Amazon website is usually not a very effective way of dealing with storage 'at scale'.
However I maintain that EBS has it's place in a wide range of applications where raw I/O performance isn't the main concern (e.g. archival).
There I said it!
(Now that I am in Ruby land though, I am a little sad that arel/ActiveRecord/DataMapper do not seem as on their game.)
I've used and written a number of O/RMs and Sequel is definitely the best.
Also, I'm really amused that apparently the creator of DataMapper is (a) selling me on using Sequel and (b) the testimonial on their front page. (Not that there's anything wrong with that.)
Example: Redis can hold 1 million counters, every counter stored in a different key, for every 200 MB of memory. This means 5 million counters per gigabyte. Clearly you can have a lot of counters. But you can make this figure a lot higher if you aggregate counters using hashes: with this trick when applicable the above figure will be five times bigger.
If anyone reads this, note that in Opera at least, clicking the builtin pdf reader's down button skips some very informative notes that Adam left in the presentation. Press page down instead.
I love the idea of NoSQL, but Cassandra was horrible (just look their source code or Thrift) and Mongo lost data. I guess 40 years of relational databases isn't so easy to replace.
Also to be clear, scaling is difficult, no matter what the tool. We've had problems with Cassandra, HBase and PostgreSQL (most recently Friday), no storage option is as good as we would like under stress.
I'd consider switching to pgsql for a future project but throwing my mysql expertise out the window for nothing seems like a bit waste of time.
Few of the items on that list bit me personally.
It has a mostly compliant SQL dialect.
It is more compatible with other DBMSs, specially DB2 but also Oracle.
It implements a host of procedural programming languages and extensions, including types and operators.
It is way faster, and scales way more.
It is totally free (no paying Oracle to do InnoDB hot backups).
It is way more consistent.
It does geographic data second to none.
Its development is totally open and way faster. Reading the PostgreSQL TODO wiki page is a joy.
And so on and so on.
1. MySQL's "feature set" is usually described as a union of storage engine features; whereas in fact you only ever get some of them at a time. I find that extremely annoying.
2. PostgreSQL has a decent, smart query planner. In cases where I have multiply-layered views, I've seen MySQL throw up its hands and manually churn through each view in turn, while PostgreSQL did the smart thing and combined all the views into a single execution plan.
3. It's much more featuresome for in-database programming. The web world tends to look on RDBMSes as flat files with a funny accent, so this doesn't matter for a lot of programmers. But sometimes you absolutely must either a) protect the data or b) place computation as close to the data as possible. Featuresome databases like PostgreSQL, Oracle, DB2 etc let you do this. MySQL not as well.
* HStore, the key-value store column type * PGXN, still new, but going to change the world * Replication that Actually Works For Real No Kidding. * Window functions, which are a total brainf*ck but once you internalize them, indispensible.
The most striking difference[1] is probably the cost-based optimizer and the availability of a lot more execution plans. Most often cited is that MySQL has no hash join, but there are lots of plans that make a huge difference in execution time, and (for the most part), mysql just doesn't have those execution plans available.
And these are algorithmic differences. Typically, you think of optimization as something that does your same algorithm, just a little faster. In a SQL DBMS, optimization means choosing a better algorithm.
And it's hard for MySQL to add new algorithms because it doesn't have a cost-based optimizer, so it would have no idea which ones to choose.
[1] There are lots of other differences, but sometimes differences aren't easily represented as convincing "check-mark" features. So I really think you are asking the wrong question. The right question is: if I were making non-trivial application XYZ in postgres, how would it be done (from both dev and operational standpoint)? And is it easier to develop and smoother to operate if I do it that way?
* microsecond granularity on timestamps, rather than second
* transactional DDL (if a migration fails halfway through, you don't end up in a broken or inconsistent state)
* really superb documentation - clear and detailed explanations of hard stuff like locking semantics
* No silent coercion/truncation between data types
* Functional indices (as far as I can tell from http://bugs.mysql.com/bug.php?id=4990, MySQL doesn't have these)
* http://postgres.heroku.com (yeah MySQL has RDS but I've not heard great things)
The systems are quite different; I don't think it was ever really about preference -- it's more about the approach you take to development and operations.
It's hard to point to any one thing that you might find useful in postgres without knowing more about the kind of projects you work on. But if you start to do things the postgresql way, then I think you'll find some things that make development much easier and operations much smoother.
"Better" is a loaded word; but if developing a new application, I think it's a good idea to default to postgresql, and only use mysql if you have a specific reason (and there are a few good ones). I've been a postgresql community member for a long time and use postgresql much more than mysql, so take that opinion with a grain of salt.
* Postgre 8.3 on AWS not good enough
-> lets try Mongo
* Mongo performance not good enough
-> lets try and fix this
* Oh shit we don't know what we're doing lets go back to Postgre
* There's a new version 9.0 this might sort us out
* No sadly Postgres 9.0 is no better
* Oh wait maybe Amazon performance is to blame
* Lets try it on real hardware
* Oh yes that's betterI used to complain about the lack of "out-of-the-box" sharding middleware myself, but came to realize that this is really something that belongs in the application layer for all but the most trivial use-cases.
However, there is no reason why full-stack frameworks such as Django and Rails couldn't grow meaningful sharding support.
But at least it gave me starting point, which is already more than most other ORMs can offer.
In my past life, I've built high available MySQL cluster using the combo and it worked great. The same technique can be applied to Postgres.
It has some interesting properties and it's rare to find real-world rapports about how it fares outside of Hadoop.
http://wiki.postgresql.org/images/7/7f/Adam-lowry-postgresop...
Really guys? :)
My recommendation: switch to SoftLayer. Save thousands a month, use real hardware.
I agree with the guy. PostgreSQL (and SQL server ironically) have kept me up less than any other piece of software out there.
I'm watching a turd occuring with MongoDB at the minute. Every time something breaks, I hear the team saying "it'll be fixed in the next version" or "feature X works around it for now". Grr. Too much marketing, not enough reliability study.
There was one interesting tidbit in one of the Heroku presentations : they apparently run over 150,000 databases ( https://wal-e-pgopen2011.herokuapp.com/#6 ) which is incredible.
Fundamentally, I wouldn't have designed the messaging model that would require a write for each client device for each message.
Then for each client pull the groups the client belongs to and look for new messages (possibly more reads than storing the messages on a per-client basis).
It's an extra database table and layer of indirection, but that's one way.