How to make MongoDB not suck for analytics • Hacker News Board

Re: How to make MongoDB not suck for analytics

#91

Post by stickfigure » Mon, Jul 02, 2018, 9:23 PM UTC

manigandham wrote: post #86

stickfigure wrote: post #36

I tried using MongoDB for the customer-facing analytics of a large e-commerce marketplace. It didn't work very well. The problem is that at some point you end up wanting joins. MongoDB was actually the third try. My first two attempts were BigQuery and Keen, neither of which worked out because they support only one index - time. Users want to slice and dice by various axes! And there's an obvious additional index you…

Columnstores don't use indexes, and many don't even support them (like BigQuery). You may be taking about clustering, which you can use to improve compression and scan speed by sorting data by commonly queried columns but it's unnecessary, and even table scans are fast in modern columnstores that can prune partitions and use sophisticated metadata to calculate your answers. Also it's SQL, what is preventing anyone fr…

I was being fairly liberal with the word index - by partitioning your data by time and selecting partitions in queries, time effectively becomes an "index" that allows you to avoid searching more of your data set than you have to.

table scans are fast in modern columnstores

I guess that depends on your expectations of 'fast'. Even with our smallish dataset, both BQ and Keen had multi-second responses -- frequently 10+s. It was totally unacceptable for user-facing analytics. And we had a lot of customers making a lot of queries - it started to get expensive fast.

I'm sure 10s responses would be very 'fast' for terabyte-sized data volumes. But that's not the problem we were trying to solve.

Re: How to make MongoDB not suck for analytics

#92

Post by sueders101 » Tue, Jul 03, 2018, 2:41 AM UTC

nostalgeek wrote: post #61

threeseed wrote: post #43

Earlier quoted context omitted.

You are completely contradicting yourself. On one hand you complain about using technologies before you have done a prototype and evaluated the product. Then you blindly tell startups to just use MySQL/PostgreSQL without having any idea of their use case or whether it matches their query patterns. If you are a startup the right way to go is to document your use case, understand what queries those use cases demand and…

> Right tool for the right job. I would argue that since both Mysql and PostgreSQL are JSON document stores with mostly the same capabilities when it comes to querying and aggregation I don't see the advantage of using MongoDB at all. I wouldn't even use MongoDB for caching when redis does a better job at it. Logs? I don't see why logs cannot be shoved into a RDBMS. Prototyping? create a table with a JSON field and a…

>I would argue that since both Mysql and PostgreSQL are JSON document stores with mostly the same capabilities when it comes to querying and aggregation...

I agree with your overall argument of PostgreSQL & MySQL >> MongoDB(for querying and aggregation). But in all the experiences I’ve had doing analytical work with both; Postgres easily comes out ahead. If you’re starting from a blank slate, I’d definitely recommend it over MySQL. Just update/feature addition rate along with the better community quality are enough for me to prefer Postgres over MySQL.

Re: How to make MongoDB not suck for analytics

#93

Post by kalendos » Tue, Jul 03, 2018, 11:15 AM UTC

chiaolun wrote: post #25

mindingdata wrote: post #21

Earlier quoted context omitted.

Others are sharing out of the box solutions. But I will say that many moons ago when I did actually write stuff for Mongo. The oplog was a god send. You can "tail" the oplog, and get every transaction in near real time. We used this for updating Elasticsearch indexes etc in what is basically realtime, without having to poll or modify existing code at all.

The oplog is awesome! It provides an immutable record which is really useful - we materialize the oplog directly in Athena to get a time-travelling database for debugging purposes.

Would you mind sharing the process? I'm curious which flow you use to materialize the oplog in Athena/S3.

Re: How to make MongoDB not suck for analytics

#94

Post by DrummerDaveS » Tue, Jul 03, 2018, 12:44 PM UTC

arghwhat wrote: post #58

sztanko wrote: post #55

Just try this out: https://github.com/EXASOL/docker-db and you will be impressed. This is an embryo of a real analytical database. Pros: - an 8 CPU installation with 64gb memory will probably be hundred times faster then postgres. -it supports full sql - It is super stable, even as docker container Cons: - it does not support nested data - once you reach volumes of around 2Tb, you will probably have to switch to a pa…

> an 8 CPU installation with 64gb memory will probably be hundred times faster then postgres. "Probably" not. The way this usually goes down is that there may be a few synthetic benchmarks show a large performance benefit over existing established databases (x2, not x100), with any non-synthetic benchmark showing very poor performance (1/10th, 1/100th, sometimes even worse), and also often very unstable performance.…

Full disclosure - I currently work for Exasol.. but I thought I'd just clarify that Exasol has been around for over 15 years and is far from 'beta' (currently on version 6 with hundreds of production installations worldwide). I've also been in the industry for > 40 years and worked with many database products (including Ingres and Postgres) - and all I can say is download the free community edition from the Exasol website or the Docker image as described above and try it for yourself - you will be up and running very quickly and I think you will be pleasantly surprised regarding both functionality and performance.

Re: How to make MongoDB not suck for analytics

#95

Post by mercer » Tue, Jul 03, 2018, 2:38 PM UTC

threeseed wrote: post #68

GenericsMotors wrote: post #66

Earlier quoted context omitted.

> Because if you don't know what you want you are almost guaranteed to pick the wrong technology. That's one way to look at it...but a bit shortsighted. Requirements can and do change, and a well designed model in an RDBMS will be far more extensible than a similar one in NoSQL document store. So RDBMS' aren't the "wrong" technology, they the safest bet; not to mention most modern relational DBs already out-perform m…

How does one design a proper ER model without understanding the domain, query patterns etc ? Because that sounds like magic. Also MongoDB destroys any RDBMS (minimum 10x faster) if you have embedded structures instead of joining against 10 tables in a normalised design. Hence the importance of understanding your query patterns and domain before selecting the database.

Is that speed increase still there if you use Postgres' JSON(B) storage?

Re: How to make MongoDB not suck for analytics

#96

Post by arghwhat » Wed, Jul 04, 2018, 10:46 AM UTC

DrummerDaveS wrote: post #94

arghwhat wrote: post #58

Earlier quoted context omitted.

> an 8 CPU installation with 64gb memory will probably be hundred times faster then postgres. "Probably" not. The way this usually goes down is that there may be a few synthetic benchmarks show a large performance benefit over existing established databases (x2, not x100), with any non-synthetic benchmark showing very poor performance (1/10th, 1/100th, sometimes even worse), and also often very unstable performance.…

Full disclosure - I currently work for Exasol.. but I thought I'd just clarify that Exasol has been around for over 15 years and is far from 'beta' (currently on version 6 with hundreds of production installations worldwide). I've also been in the industry for > 40 years and worked with many database products (including Ingres and Postgres) - and all I can say is download the free community edition from the Exasol we…

My comment was more general in the sense that such a grand performance statement needs some serious backing, and new products claiming to be several orders of magnitude faster than established products are usually unable to deliver anything at all.

Would you mind sharing some of the differences to, say, Postgres, and what to expect if moving from Postgres to Exasol? Porting my applications to Exasol to benchmark would be time consuming (synthetic benchmarks are very uninteresting), and without any information about what to expect, it simply wouldn't be sensible.

I tried to look at the website, but I am not interested in accepting a privacy policy just to get a white-paper, which frankly leaves me with no usable information at all. The rest of the website is basically empty, short of graphs without data and marketing "You want to do X? We can do that too! ". The only real thing I could extract was "in-memory database".

To me, "in-memory database" would appear to be the catch that makes it an entirely different product than Postgres, catering to an entirely different payload with different pros and cons, rather than an faster all-round product. None of my tables fit in RAM anyway.

Re: How to make MongoDB not suck for analytics

#97

Post by manigandham » Wed, Jul 04, 2018, 11:37 AM UTC

stickfigure wrote: post #91

manigandham wrote: post #86

Earlier quoted context omitted.

Columnstores don't use indexes, and many don't even support them (like BigQuery). You may be taking about clustering, which you can use to improve compression and scan speed by sorting data by commonly queried columns but it's unnecessary, and even table scans are fast in modern columnstores that can prune partitions and use sophisticated metadata to calculate your answers. Also it's SQL, what is preventing anyone fr…

I was being fairly liberal with the word index - by partitioning your data by time and selecting partitions in queries, time effectively becomes an "index" that allows you to avoid searching more of your data set than you have to. table scans are fast in modern columnstores I guess that depends on your expectations of 'fast'. Even with our smallish dataset, both BQ and Keen had multi-second responses -- frequently 10…

Yes, the problem is they just aren't a good fit for your data size.

Keen isn't a columnstore, it's a custom database built on top of Cassandra where they take JSON records and split them into compressed batches with each unique property stored in the CQL data model, and it's processed by Storm workers. It's an outdated architecture compared to modern columnstores that can now handle unstructured/nested data really well.

BigQuery is designed for throughput instead of latency. There is a minimum 3-5 seconds to schedule your query across the server pool before it even starts processing. It's also a single shared cluster for all customers so performance is variable, but the trade-off is that 100TB also takes seconds to scan.

Re: How to make MongoDB not suck for analytics

#98

Post by lomnakkus » Thu, Jul 05, 2018, 12:07 AM UTC

stickfigure wrote: post #85

198394549 wrote: post #77

Earlier quoted context omitted.

>This is not the case for most of the NoSQL databases where you'll pay for lack of certain features either by a) having to write a lot of code, or b) bad-to-crippling performance for use cases it wasn't meant to solve. Can you give a common example of these? This article is referring to issues related to row vs column data stores, not sql vs nosql.

Having implemented effectively the same customer-facing analytics problem in BQ, Keen, Mongo, and Postgres, I'll tell you specifically: * Column stores like BQ and Keen don't let you efficiently slice and dice data by factors other than time. If you're slicing by customer or product, your queries become incredibly slow and expensive. You start writing hacky shit like figuring out when your customer's first sale was s…

Very well put... and this was the point I was making about "decent" performance. If you have super-special requirements (you don't), you'll probably discover it along the way to SUCCESS. If you don't any old SQL database will probably be more than sufficient AND it will be flexible enough to allow you to evolve your schema along the way.