Our performance problems went away, with almost no work other than the initial migration. We fixed many data integrity problems during the migration, and have not noticed any problems since. Our uptime has become near perfect. We now get to use all of the other great features that PostgreSQL offers.

I'd have to check with our DB folks to get the exact size of our dataset and the number of servers we're using now with PostgreSQL. At the time of the migration, it was around 85 TB, with 6 physical servers and replication being used.

I generally prefer relational databases, and from a theory point of view they feel much nicer and have the potential to be much faster, however, one thing MongoDB has got right is the simple primitives for scaling, and having them built into the core product, something I don't think Postgres has.

I think there's a lot that Postgres (and other relational databases) can learn from MongoDB (ease of sharding perhaps?), Cassandra (consistent hashing maybe?), HyperDex, etc. And I'm sure there's much that those products can learn from the relational world as well.

MySQL/PostgreSQL are fantastic database systems and exceptional at solving the problems they were designed for. But its wrong to think of it as this blanket solution for every problem, or to suggest it on HN every single time someone asks a question about MongoDB.

Just look at the different approaches that PostgreSQL supports: http://www.postgresql.org/docs/9.4/static/different-replicat...

Other relational DB systems, from Oracle, to SQL Server, and even MySQL, offer similar functionality.

Practice shows that they're very capable of successfully handling many different scenarios, even if some of these scenarios hadn't been considered decades ago when these relational database systems were first implemented. But that's just what happens when the core building blocks they do provide are so solid and fundamentally sound.

It's only anecdotal evidence, but of all of the DB professionals I've worked with over the years, the ones who complained the least were always the ones working with some sort of relational DB system. The ones who complained the most have been working with some NoSQL database system.

I've heard nothing but praise for the PostgreSQL documentation, while we repeatedly see submissions like this one asking for help with NoSQL scalability problems that just don't happen when using PostgreSQL, or Oracle, or SQL Server, or DB2, or MySQL, or even SQLite!

Uh that's simply not true? VoltDB, for instance, easily and natively supports sharding. SQL Server's AlwaysOn system (despite being failover clustering) is incredibly simple to setup and is transparent to clients.

The OP is asking about a problem with only 1M records. Unless they're huge records or something, no one should be discussing such a small number of records as a performance limit.

1. Run your query with explain - to confirm that your queries are indeed indexed[1]. There have been a couple of times where we discovered that a specific branch of a complex query was not hitting index - so discovering that was useful.

2. One thought on slow reads with aggregation framework and 1M+ documents is that you are bottlenecked on memory. Even though all your queries may be indexed - you may be hitting disk too often (and doing a lot of random I/O) which is expensive. Check out MMS - and specifically the page faults graph - and you will get an idea about how many times a second you hit disk. For my specific application - response time was very important - so we tried to keep our page faults close to zero. Is increasing the RAM in your instance a viable option? If so this would definitely give you some breathing room.

3. You should try and verify if your response is slow because the query processing time itself is high, or if it's because you have several concurrent queries competing for system resources (CPU / memory etc). If it's the latter - the fix is easy - just add more machines to your replicaset (balance this with adding more RAM).

4. Check your lock %. My guess - from your brief description - this will not be an issue for you. If this is an issue - you __may__ need to shard your system - or do 5.

5. I think the most scalable solution would be to rethink your data pipeline. Why do you need to apply complex aggregation queries on 1M+ documents? Can you build a processing pipeline that takes in your data - and builds "prepared views" for the queries you expect out of your aggregation pipeline? In my application - we used stream processing pipeline that aggregated all the messages we got - in all the different ways we needed - and inserted them into different databases (to work around the DB level locking :( ) in mongo. Each collection had exactly ONE type of query that would be executed - this would be an extremely simple query and would be indexed. This pattern is common - even outside of mongo.

Good luck :)

[1] http://docs.mongodb.org/manual/reference/method/cursor.expla...

I believe it was because we were not using xfs as our file system, which we have recently moved over to. So far the issue hasn't re-occured.

Other than this issue, MongoDB has been pretty good. Indices are important and formulating queries so that they are index based is also important (the same result can be obtained in multiple ways, some of which likely do not use the indices, while other formulations will use them.)

We use Redis as a cache in front to reduce the unnecessary repetitive load on MongoDB for common operations.

We have MongoDB cluster setup and it has saved us a number of times. Also it is really just a live backup system -- we've had a few hardware failures and we still have never had to restore for an archive backup, we just resync from one of the failover servers. But using that is likely a no-brainer, and you are likely already doing it.

So far I've worked at two separate companies deploying MongoDB into production and we've never had any real issues, mostly because in both cases the lead engineers really knew what they were doing, had read the manual front-to-back, and they knew exactly what kind of OS and filesystem setup to use for maximum effectiveness.

You won't get real time data any more, but switching to batch processing will speed up your users' experience.

The cynic in me, however, wants to just tell you that you've outgrown Mongo and should move on. It happens; not every database is designed to support every need, and Mongo happens to be a spectacularly shitty fit for the kind of OLAP scenario you're describing.

If you absolutely must have real-time analytics crunched on the fly, I recommend that you look at data stores that are a better fit for your application. InfluxDB is great for time series data. Aerospike is getting a lot of press in the AdTech industry. And old standbys like MySQL or Postgres can handle queries over a million records without breaking a sweat.

I won't claim that we did everything under the sun to optimize it, we could have probably optimized it to work eventually. It was just easier to switch and not deal with the headaches of maintaining/optimizing the mongoDB setup after days/weeks of headaches.

In what world is 100 updates/sec "heavy"? I don't mean to come off rude, but even with a full disk write per update that fits into normal magnetic disk IOPS without even batching transactions.

For comparison, with zero work on our end, and running off a 2-disk RAID 1 array with a 10 or 15K HDD, it was trivial to pass 10K/sec updates.

It's a weird phenomenon you see these days, people talk about 10 updates a second on a million records as some sort of intense load that needs "sharding" and God knows what else, when a DBA from 20 years ago wouldn't even blink at it.

This suggests there are things that can be done to improve performance enough, and in that case, maybe switching would be a bad idea. There is certainly a very high cost in terms of development time, learning and dev-ops to switching between two databases.

Seems like a drop in the bucket to be experiencing performance problems... What are your use cases? Why did you pick MongoDB i the first place?

Have the right indexes; especially with complex aggregation you might be surprised to find they aren't being used due to a quirk of your query.

A single mongo instance can handle a beefy load, but not if every read is an aggregation over a million by million by who knows how many docs.

TokuMX does have one significant problem in that the technology they use for indexes experiences performance degradation over time if you do lots of deletes from the head of the collection (ie, if you're processing data that gets inserted and deleted roughly chronologically), but it is fixable with reIndex() (which is an async operation analogous to OPTIMIZE TABLE). That's really the only large problem we've had with it, and it's surmountable with regular maintenance. In the case of monotonically-ordered data though, you can address the problem with partitioned collections, which lets you split a collection up into chunks, and then drop chunks as they become unnecessary, without having to do expensive things to the whole collection.

It looks like WiredTiger should provide many of the same benefits, so you may evaluate that first before taking the plunge, but I haven't personally compared it with our tokumx setup yet.

Outside of that, you probably need to start sharding your dataset. Figure out a shard key that will let you distribute your data well, and start splitting your load among multiple replica sets. Figure out if your issues are IO, CPU, or whatever - there's a lot of documentation on how to do this, so I won't rehash it here, but in general, you first need to understand what is slow before you can fix it. Chances are very good that your culprit is disk IO, though (because it always is in databases), and you can probably get some of that performance back with more RAM (to keep more of your working set in RAM at a given point), faster disks, and/or a striped disk array. Depending on your platform, some kernel tuning may help, as well, though it's going to be specific to your platform and setup.

You might also look at other tools and see if they're better-suited for your purposes. We use Elasticsearch as our ad hoc query layer on top of TokuMX, and are doing some stuff with Cassandra and BigQuery for bulk analysis of historical data. I'm not suggesting that you replace your whole stack, but if you're trying to shove a square peg through a round hole, you're just going to be frustrated. Scaling is all about specializing your toolset to fit your problem domain - there is almost never a single solution that is the best thing for all use cases. If you need to add another tool to the stack to offload one part of your requirements, do it!

Business is about costs and prices not about thinking that business is about the religious adoptions of mongo, agile, C#, python, linux ....

This question is saying that your business is ruled by the techs not anyone with the common sense needed to pay the bills (thus your paychecks).

The feedbacks I have to give you is that TCO per mb stored increase more than linearly with mongodb, with weiredly enough a decrease in SLA (due to the weired unexpected incident hard to troubleshoot) so if you look at the probability you have success, it is bad news. Customer expect cost to diminish with volume, thus prices (investors too). If the fat of your business model is around document storage, you have a problem.

You should not trust me, and do some real costs analysis of the cost per documents for your business case with more than one scenarii and make some costs analysis and some projections. Because even if someone else optimization worked for them, it may not be relevant to your business case: at one point you make money, so you issue a transaction that cannot be handled in a distributed system (CAP theorem + NoSQL==NoACID) and this point differs than fellow devs.

Mongo could be -not is- killing your business. If you do mongo because that is the only techno you know, consider that you have been taken hostage, and call SWAT to have your mongo guru arrested. They may look nice and harmless, but you are the hostages of dangerous fanatic religious techs. Maybe call an exorcist, it could be as efficient.

And then go back to thinking what is the most efficient way to have your bills paid, and how to diminish cost as your business grow. Forget technical stuff as a compass for making successful software solution. Success is about benefits only

Je dis ça, je dis rien, hein?! My .02 eurocents.

You completely ignore the question, make a comment about "religious adoptions of mongo" and then do an about-face by showing your OWN religious zeal AGAINST mongo.

Mongo is not killing his business. Your comment should be deleted.

To the OPs question: can you post any more specifics? 1M documents with an index shouldn't be an issue for the aggregation pipeline. What are the specs of the machine? What are your indexes? I think it would be tough to help without having that info, and sadly a question requiring that type of info may be best suited for something like Stack Overflow.

The comment raised an interesting way of thinking about the problem that many developers would not consider, and while it might have had a fair bit of religious zeal, that is very common here and not worthy of comments like "unbelievable waste of a comment" and "your comment should be deleted".

    The comment raised an interesting way of thinking about the
    problem that many developers would not consider

I think your comment only needed to be a polite "can we keep the conversation on the topic of MongoDB scaling" rather than a quite personal attack on the author.

Also, personally, I thought the main point raised about viewing the database choice from a business rather than development point of view was interesting and well worth discussion.

Because it might be more helpful to think about scaling your data needs than scaling with only a certain product.

Just to give you an idea where SFJulie1 is coming from:

http://www.reddit.com/r/programming/comments/2pwkcl/help_nee...

I think mikegioia was perfectly reasonable in calling out this comment, it is trash and he was right to recognize it as such.

It is hardly arguable that there is a segment of the developer population that has a religious zeal for their "stack" or against others (just pick any random topic on functional programming or PHP for examples of each). It is entirely appropriate to discuss and address that in topics like this on HN, in my opinion.

I suppose one might infer from it notion of a default assumption that developers are all zealots about their particular technology choices. I'm not sure such an assumption itself is terribly bad to have, honestly. Sometimes I think that if my only insight into the world of technology came from startup job postings and HN, I ought to be forgiven for thinking that the entire industry is a kind of elaborate and humorous play on role-playing game mechanics ("Ruby Dev" <==> "Developer/Ruby" class/skill combo), which are notorious for producing fanatical devotees of one combination or another.

Perhaps it's a deserved attitude.

Taking a look at the economics of a technology that's being used is a very sensible thing to do, especially for a business. If this analysis shows that one solution is inferior to another, it does not mean that the analyst has a "religious zeal" against the economically-inferior technology.

It would be doing this person a disservice to not mention alternative technologies that have solved very similar problems that have affected many other people/organizations.

It is total absurdity to avoid bringing up these solutions to the problem merely in order to maintain some vague purity of the discussion, or to otherwise keep the discussion unnecessarily and unhelpfully focused.

If I asked on a forum for help with the engine on my Dodge Dart, and you posted "Just buy a Prius", that would not be helpful. Do you see how that isn't helpful? That's what I'm trying to say, that I would like to see, for once a post stay on topic when it's about MongoDB (heck, even PHP for that matter!).

This submission is about a problem involving MongoDB. It is not about MongoDB itself. The submitter clearly wants a variety of ideas here. That's why the submission says, and I quote, "Any tips, stories, suggestions are welcome!"

"Any" means "any", including the suggestion that some other technology be used.

I think the argument against it is that developer time and productivity is worth a considerable amount for many companies, and working with tools (DBMSs in this case, but it applies to lots more) that speed up application development is often worth more to the business than the basic cost of storing and serving data.

This isn't always the case, at Facebook scale for example, it might be that the most efficient use of money is to use (speaking entirely hypothetically here) is an Oracle database instead of MySQL, and instead hire twice as many developers because they are half as productive with it. Maybe that would be a good trade-off? However, at many (most?) companies, I suspect that the better trade-off would be to hire fewer developers and use tools that are more expensive to run.

If product X is free, but requires hard to find skilled operators and doesn't have well established operational best practices, paying über dollars for Oracle might be the smart move.

Where I work, location and policies for hiring contract staff make a product like Mongo high risk for data we care about.