9 ms·
Cassandra vs MongoDB For Time Series Data
- espeed 13y agoKariosDB (https://github.com/proofpoint/kairosdb https://github.com/proofpoint/kairosdb) is a time-series database for Casssandra. It's an OpenTSDB (http://opentsdb.net http://opentsdb.net) rewrite that supports sub-second precision.
- vosper 13y agoHave you any experience using it?
- arohner 13y agoOne thing I always find interesting about these kinds of problems is that most DBs don't describe how they're implemented. It's easy to use the wrong tool, and then once you learn how e.g. Mongo is implemented, it's obvious, "oh, that's why things are slow". I'd love to see http://eagain.net/articles/git-for-computer-scientists/ http://eagain.net/articles/git-for-computer-scientists/, but for every DB technology.
- trimbo 13y agoYou nailed it. I think the issue is that people don't know what questions to ask when gathering the requirements. I'd like to know more about how this part of the article came to be: "This choice was made early on and it was supposed to be a temporary one." HOW was that choice made. What requirements were out there. I think too many people choose Mongo because they believe it's "schemaless"[1] and faster for development, but don't look at the requirements for their actual use case. [1] - There's always a schema. Either it's informally defined by your code or represented formally somewhere else.
- relistan 13y agoIt was one of those, "we need to do something now and this will work" solutions. We had a really talented consultant working for us, writing some of the early code. He was familiar with Mongo and wanted to go that route. Early on I said we should use Cassandra for this, but it took us quite some time to get to the point of being able to migrate. A testament to his code and the "this will be temporary" foreknowledge is the fact that we could swap data stores in a pretty massive sense without much in the way of outward-facing changes to our processing engine. I think the hard thing for us up front was that trying to explain Cassandra data models to someone (and this guy is really really good) and then hand the rest of the work over to them to implement now, on a contracting rate is not a trivial problem. And we needed to hurry for both deadlines and burn rate.
- trimbo 13y agoCool, thanks for the info!
- mtkd 13y agoA pattern I've seen on a lot of the negative Mongo articles has been people using it for things they probably shouldn't. I've yet to use it for any load, and am struggling to triangulate from all the articles I read on whether it does/doesn't scale efficiently. All the issues I have hit so far have been self-inflicted, it is still one of the best new technologies I've used in years - but is has taken a while to stop thinking in SQL equivalents and start thinking natively.
- otterley 13y agoFor what kind of application or workload is MongoDB superior to the alternatives?
- Xorlev 13y agoThe kind that lives on a single VPS and requires a rich query layer to bootstrap quickly.
- relistan 13y agoHopefully this article wasn't seen as bashing MongoDB. I think I mention it was pretty reliable for us even under load. We were just solving a problem with it for which it wasn't the best solution.
- arohner 13y ago> whether [Mongo] does/doesn't scale efficiently It doesn't. Three words: "global write lock". Writes block reads, reads block writes. Implications: if you run a query in production that doesn't hit an index, all traffic stops. The notablescan setting is a very, very good idea. This also means all queries must have an index, so Mongo ends up with more indexes than say, postgres would. It's impossible to configure a clustered mongo environment to not lose data: http://aphyr.com/posts/284-call-me-maybe-mongodb http://aphyr.com/posts/284-call-me-maybe-mongodb Sharding configuration is baroque, and limited.
- francesca 13y agoThe global lock was removed in 2.2 https://blog.serverdensity.com/goodbye-global-lock-mongodb-2-0-vs-2-2/ https://blog.serverdensity.com/goodbye-global-lock-mongodb-2... Now locking is on the database level.
- teraflop 13y agoAgreed. Seeing a nice, clean user-facing API tells you practically nothing about the performance characteristics and failure modes you can expect to see. The best database engine I've encountered in this respect is SQLite. It has plenty of information about what design tradeoffs it makes, and why, e.g.: http://sqlite.org/lockingv3.html http://sqlite.org/lockingv3.html http://sqlite.org/fileformat2.html http://sqlite.org/fileformat2.html http://sqlite.org/atomiccommit.html http://sqlite.org/atomiccommit.html
- mh- 13y ago> I'd love to see [git-for-computer-scientists], but for every DB technology. this is a fantastic idea. if someone gets this going I'll enthusiastically contribute.
- hcarvalhoalves 13y ago+1 It would be useful to treat databases as those big data structures, knowing the best/average/worst case, cpu vs. memory trade-offs for search, etc.
- mtkd 13y agoWould be really useful to get more background before/after and a lot more detail on the structures, the clustering and the queries required - and any perf improvements that were attempted previously successful or not - and any lessons learned from making the change.
- relistan 13y agoGood ideas, I'll see if I can generate some time to post more about what we did.
- ghc 13y agoWe had some major issues using MongoDB for time series data due to the write volume (real time sensor data). The solution for us was Riak, mainly because we never need to update a vector clock, leaving us without the need for conflict resolution (and last write wins is fine for sensor data).
- k_bx 13y agoWhy wouldn't generating some hashed _id work? It would then scale for writes easily (or, as in current mongodb, you could use hash-based indexes). I'm just asking since Riak seemed much slower for me when I tried it.
- ghc 13y agoHashed _id's aren't the bottleneck. Disks are the bottleneck. MongoDB is seriously lacking in the compression department and bogs down our disks constantly. Riak is not slow as long as you're not running your cluster on VPSs. At our scale Riak's performance has been significantly better than MongoDB's due to our heavy write load, and there are fewer issues with using big disks with somewhat larger seek times.
- snowwindwaves 13y agoi work on scada systems which usually come with a time series database built in so that operators can do some basic plotting. Often these products have a 10 or 15 year legacy. The scada vendors seem to be careful to avoid making the time series database and plotting tools which come with the HMI packages too powerful, as this might cut in to their sales of Historian type products. If it wasn't a commodity already, the rash of startups which all seem to write their own tools for storing and plotting metrics from the operations of their servers and software services has certainly made the guts of a capable historian readily available for free. for storing data open tsdb, based on hbase+hadoop, http://opentsdb.net/ http://opentsdb.net/ kairosdb, based on cassandra, https://code.google.com/p/kairosdb/ https://code.google.com/p/kairosdb/ timeseriesframework http://timeseriesframework.codeplex.com/ http://timeseriesframework.codeplex.com/ for plotting data I am hoping to find a library that allows for real time plotting and zooming, scrolling with the mouse wheel. so far I have found openhistorian http://openhistorian.codeplex.com/ http://openhistorian.codeplex.com/ kst http://sourceforge.net/projects/kst/ http://sourceforge.net/projects/kst/ veusz http://home.gna.org/veusz/ http://home.gna.org/veusz/ chaco http://docs.enthought.com/chaco/ http://docs.enthought.com/chaco/ guiqwt https://code.google.com/p/guiqwt/ https://code.google.com/p/guiqwt/ pyqtgraph http://www.pyqtgraph.org/ http://www.pyqtgraph.org/ lots of D3 based libraries: http://selection.datavisualization.ch/ http://selection.datavisualization.ch/ so there are lots of tools out there if you've got the patience to figure out which one is right for your application and glue it together
- Nate75Sanders 13y agoI had to look up "scada". http://en.wikipedia.org/wiki/SCADA http://en.wikipedia.org/wiki/SCADA for those of you in the same boat
- dpeck 13y agoMight be interested in Cube that square released a few years ago. http://corner.squareup.com/2011/09/cube.html http://corner.squareup.com/2011/09/cube.html edit: cube, not cuba :)
- ericb 13y agoWith something like Kairos, how do you segment the time series data? Like if I wanted to use it to keep track of stats for 2 different customers, how would that work?
- CurtMonash 13y agoIt would be surprising if, for data with a consistent, predictable structure, MongoDB had the best, most consistent, and most predictable performance. MongoDB's raison d'etre is that data doesn't always have consistent, predictable structures. That said, there's cool stuff out there in the Mongo ecosystem. E.g., TokuMX is a whole new Mongo storage engine.
- topbanana 13y agoWe use KDB for time series data but it is expensive. He's anyone checked this project out? http://www.monetdb.org/Home http://www.monetdb.org/Home
- espeed 13y agoThe column store research that started it all. Definitely read the paper if you haven't already: "The Researcher’s Guide to the Data Deluge: Querying a Scientific Database in Just a Few Seconds" (http://oai.cwi.nl/oai/asset/18546/18546B.pdf http://oai.cwi.nl/oai/asset/18546/18546B.pdf)
- agentultra 13y agoMy go-to for any time-series data is a column-oriented database. fyi, Cassandra is row-oriented.
- zorked 13y agoWell, but in a column-oriented database you would store your time series data in columns. Cassandra is "row-oriented" and you store the time series in rows. In both cases the data ends up sequentially on the disk, which is what you want.
- ddorian43 13y agoi think monetdb is not supposed to be used on high write (or update) scenarios
- astral303 13y agoI didn't see the size of the cluster described, but watch out for having timestamps as your row keys, for you are going to have hot spots (all timestamps in one token range). What is your replication factor and the size of the cluster? This might be improved with vNodes, though I'm not sure how granular and automatic the subnodes are. If they are just an even range (e.g. 256 vnodes across the same 00-ff range), then you will have the same problem. This is the major reason why Datastax pushes random ordered partitioning so much, it's easy to get into hot water with byte-ordered keys.
- Finster 13y agoBut is Cassandra web scale?
- AHconsidered 13y agosome vague graph from this year's Cassandra Datastax conf: https://pbs.twimg.com/media/BMfdOagCcAAxMVU.jpg https://pbs.twimg.com/media/BMfdOagCcAAxMVU.jpg
- ddorian43 13y agoAnyone uses cassandra with OrderedPartitioner and is it good ?(i know random is best)
- Aaronontheweb 13y agoOrderedPartitioner is definitely an expert-mode feature. You really have to have a good feel for the key proximity of reads and writes, or otherwise you can create some really nasty hotspots on individual nodes. We stick with Random.
- cnlwsu 13y agoFor what its worth, we have been using Cassandra for storing time series for about 2 years now at ~2k writes a sec. I would say every issue was self-induced and Cassandra has been amazingly patient with us. It works amazing with this scenario. We experimented with MongoDB a lot initially (along with riak, hbase, etc) and found about the same thing. Turns out using a database in a way its designed to work turns out in your favor. That said hbase did well too, but it scared our ops team. All the new changes in 1.2 and 2.0 with cql really make it seem like datastax is focused on being mysql and ignoring the time series use case though which makes me nervous.
- koeninger 13y agoI understand the nervousness, but I was able to convert a thrift/hector time series model to exactly equivalent CQL3 without too much trouble. The (perhaps non-obvious) options involved were "WITH COMPACT STORAGE" for wide rows and "WITH CLUSTERING ORDER BY (blah DESC)" for a reversed comparator. http://www.datastax.com/documentation/cassandra/1.2/webhelp/index.html#cassandra/cql_reference/create_table_r.html http://www.datastax.com/documentation/cassandra/1.2/webhelp/...
- relistan 13y agoThanks for the pointers. We are also looking at that as a future plan. We are currently using Thrift which has been rightly pointed out and which I should have mentioned.
- jbellis 13y agoI sympathize with that perspective. I was a huge Thrift fan five years ago. But it became clear early on that, despite your experience and mine, most people had a really hard time wrapping their heads around Cassandra's Thrift API. Teaching CQL for the last 8 months has been a night and day difference. Ease of use does matter. It doesn't hurt either that CQL is substantially more performant [1]. Perhaps that will sweeten the pill for you. :) That said, while CQL may get the most publicity, we certainly haven't been neglecting the rest of the stack, e.g. [2], [3], [4], ... [1] https://twitter.com/karbonized1/status/369472202015399936 https://twitter.com/karbonized1/status/369472202015399936 [2] http://www.datastax.com/dev/blog/performance-improvements-in-cassandra-1-2 http://www.datastax.com/dev/blog/performance-improvements-in... [3] http://www.slideshare.net/jbellis/cassandra-summit-2013-keynote/27 http://www.slideshare.net/jbellis/cassandra-summit-2013-keyn... [4] http://www.datastax.com/dev/blog/lightweight-transactions-in-cassandra-2-0 http://www.datastax.com/dev/blog/lightweight-transactions-in...
- mnutt 13y agoMy company made the same switch from MongoDB to Cassandra, a little over a year ago. We were storing analytics counters in Mongo and wanted better consistency guarantees. What we found when we switched was that Cassandra had better consistency with similar performance to MongoDB. Then a few months later, as we accumulated more data, performance started to take a nosedive. Counter increments began impacting other database operations and the nodes would become unresponsive. Eventually we moved all of the counters to an in-memory aggregator that flushed to Postgres a couple of times a second. Counters were introduced in 0.8 (when we started using them) and are pretty half-baked. There has been some good discussion about overhauling counters, though I'm not sure when they're scheduled to land.
- gigq 13y agoThe company I work for had a similar experience with Cassandra. We were running it back in the 0.6 days. Once any of our nodes got around 500GB on them the performance would tank. It seems that they later fixed the performance in 1.2 (http://www.datastax.com/dev/blog/performance-improvements-in-cassandra-1-2#comment-177166 http://www.datastax.com/dev/blog/performance-improvements-in...) but by that time we moved our data over to HBase and haven't had any regrets.
- aerolite 13y agoI'm considering using Cassandra for time series data. What exactly did you mean by the columns were for the "period"? What is the period here?
- bryanh 13y agoIt seems a bit out of the ordinary, but we've found ElasticSearch + facets to be wonderful ways to consume time series data. You can dump in a bunch of information about an event (for example, unix timestamp or even response time in ms) and then return a facet over the range and get bucket counts. ES also has quite nice clustering abilities that make it pretty painless to scale out. If you are clever about your routing keys you can even go crazy pre-shard hundreds of shards very early on and not have any performance hit for map reductions, but the capacity to scale out with another node without reindexing. We've been surprised at the swiss army knife like ability of ES.
- abalone 13y agoI'm a little confused by the schema he describes. He says represent "periods" in columns and "records" in rows. Are these two different units of time? For say stock data that is sampled every second, is he saying there'd be one row per symbol per minute (a "record"), with 60 columns holding the value for each second (a "period")? If so, does that mean the data is buffered in memory for 1 minute before getting written to the DB? "Cassandra is really good for time-series data because you can write one column for each period in your series and then query across a range of time using sub-string matching. This is best done using columns for each period rather than rows, as you get huge IO efficiency wins from loading only a single row per query. Cassandra then has to at worst do one seek and then read for all the remaining data as it’s written in sequence to disk. We designed our schema to use IDs that begin with timestamps so that they can be range queried over arbitrary periods like this, with each row representing one record and each column representing one period in the series. All data is then available to be queried on a row key and start and end times."
- marshray 13y agoMy dim understanding is that the term "column" in Cassandra doesn't mean what we think it means.
- nemothekid 13y agoNo, OP's wording can be confusing if you have never used Cassandra before. You are right. There is one row per symbol. However with Cassandra any given row can have any number of columns, so when you want to write a new value, you just create a new column for that second (/period). The writes are not buffered in memory.
- abalone 13y agoI see. From what I know about Cassandra, this is a much more expensive write than doing it as a new row. To do this he has to be using dynamic columns, and those are stored as one serialized blob per row. So the more data you have in the row, the more expensive the deserialization/reserialization is with each column you add. For very large series this could be an issue. But it sounds like this is tolerable for his app because the writes are distributed over time in a predictable fashion. I am a little surprised though at the author's claim that fetching a single big row results in "huge IO efficiency" over a range of small rows. I'd expect a small amount of overhead, but isn't it more or less the same amount of data being retrieved? What am I missing? EDIT: I see the author mentioned that it reduces disk seeks because it's all serialized together already. Sort of like you're defragging the series data on every write. I guess that makes sense. Personally I would probably look at using SSDs and keep the schema more "sane" and have more scalable writes, but that's just me.
- kailuowang 13y agoI am not sure if this is really an apple to apple comparison because from the article it seems that the data schema in Cassandra was carefully designed while it's not obvious whether it's the case for the MongoDB one("it was supposed to be a temporary one"). MongoDB has many many limitations. Data schemas have to be carefully designed taking them into consideration, otherwise they are going to have a huge impact to the performance.
- nemothekid 13y agoWhen it comes to timeseries data, there aren't a lot of ways to design your schema. In my experience when working with a write heavy load such as timeseries data, you are always fighting the writelock with MongoDB. AFAIK, there isn't much you can do about your schema to combat this.
- jsemrau 13y agoMakes one wonder what the performance of Mysql/Memcached would have been. From my work experience, time-series data is quite stable in definition. I would see this more as a business case for a relational database than a NoSQL database.
- relistan 13y agoDisk usage patterns are the problem with most relational stores (and were for Mongo). When you do a query on time series data, you want to look up a start, read some data, and stop when you hit the end point. Getting it laid out largely sequentially on disk is a big win there.
- jsemrau 13y agoWhat GB/TByte Volume are we talking about here? Since memcache would be able to store some parts in RAM.
- rishid 13y agoIsn't graphite designed specifically for time series data? Or is there another use case to use Cassandra over Graphite?
- snowwindwaves 13y agographite's back end is called whisper and it is a rrdb type datastore which throws away data as it gets older. You can see a plot of your time series from three years ago with a datapoint per day, but not one data point every .5 seconds that you initially recorded at.
- wcdolphin 13y agoI find it strange that there is no discussion of the performance of writing data. Cassandra and Mongo differ hugely in this respect, and I expect you will see huge performance gains in write performance. Mongo's write locking will mean that reads will be blocked while you are inserting data. Reads in Cassandra may trigger compaction and or require sequential IO if the table has not yet been compacted, so the tradeoff is interesting
- rbranson 13y agoReads in Cassandra will never trigger a compaction. Compaction is only be triggered by writes. If the compaction queue gets behind due to overloaded CPUs / disks, reads will begin to slow down.
- gwu78 13y agoI'm assuming this website is being served from S3 based on the HTTP headers. As I'm typing this, I get an empty Content-type: header. Is that a configuration oversight or is this par for the course if serving web pages from S3?
- relistan 13y agoAn odd oversight. Thanks for pointing it out. I'll see what's up with that.
- pjmlp 13y agoIt is interesting to see that most NoSQL databases, implemented in Java/Erlang scale better than a C++ one.
- leoplct 13y agoFor Time Series Data there is also TempoDB
- vasilipupkin 13y agohow's that, anybody try using it ?
- lnkmails 13y agoWe (Cloud Monitoring @ Rackspace) have built a time series data store for persisting metrics on top of Cassandra. Check it out: https://news.ycombinator.com/item?id=6257401 https://news.ycombinator.com/item?id=6257401