It’s About Time for Time Series Databases • Hacker News Board

Re: It’s About Time for Time Series Databases

#131

Post by alistairbayley » Thu, Jan 25, 2018, 11:35 PM UTC

amyjess wrote: post #37

At my employer, we've recently (as of the middle of last year) been making a considerable effort to use InfluxDB to track our KPIs. It's working out wonderfully for us, and I'm expecting it'll get used more and more as the year goes on. What really floors me about Influx is how fast it is. A query that used to take hours in Oracle takes seconds in Influx. And the influx query is readable: rolling data up to various i…

How big is your database, and how long does it take to restart? We have what I would describe as a fairly small influx database, and it takes a long time to restart (20-30 mins). And the time to restart seems to be growing linearly with the db size. Not cool if you do regular server patching with reboots.

We also had a problem where user queries that return too many columns cause the DB server process to OOM. And then it restarts, so another 20 mins of downtime. Also not cool.

We liked the tagging and rollup features, and automatic retention management, but those first 2 problems really turned us off.

Re: It’s About Time for Time Series Databases

#132

Post by kgc » Fri, Jan 26, 2018, 1:58 AM UTC

lbruder wrote: post #106

gregmac wrote: post #60

Earlier quoted context omitted.

Wow, that's a pretty neat approach. I have a bunch of questions, if you don't mind.. Do you do filesystem replication or something else for HA? Is this running in one of the cloud cloud IaaS providers or something else? Why did you go with this approach? Eg: Was it a deliberate decision after evaluating other options, a "this is what we know" type situation, or did it grow organically from something smaller? How long…

Don't mind at all. ATM we're using a simple VMWare server backing up the virtual HDD every night, with a dirty hack thrown in (no full backups on weekends as that's when the bigger jobs run, so I dump the current day to a CSV on a smaller virtual HDD that's still saved daily). We're running on our own systems as our customers are pretty paranoid about where their data is sent. The history of this system is pretty org…

Wait, so to be clear, you moved from PG > Maria > SQLite?

Re: It’s About Time for Time Series Databases

#133

Post by tomc1985 » Fri, Jan 26, 2018, 1:59 AM UTC

kodablah wrote: post #4

Sorry to leave the technical detail part real quick. But is anyone else concerned about using a DB solely from a company built specifically around that DB? After Rethink DB (sustainability issue) and Foundation DB (bought and shuttered/hidden) and Riak (admittedly haven't kept up but I saw [0]), I am wary of using any DB that is not built by a large community or is not built as a non-core project from a large tech co…

Honestly I have found it is simply easier to master PostgreSQL and sqlite, and between the two (and with the right know-how) pretty much all use-cases are covered.

Played with a lot of databases, sql or no, and I always end up going back to PG

Re: It’s About Time for Time Series Databases

#134

Post by tomc1985 » Fri, Jan 26, 2018, 2:01 AM UTC

otterley wrote: post #56

lbruder wrote: post #52

Earlier quoted context omitted.

We use a combination of SQLite and a sharding frontend service. One SQLite database file per device, one table per sensor, table contents are timestamp and measured value. As simple as it gets, easy to scale, and damn fast. But try telling people you're using SQLite to store critical data...

What's your replication/HA story?

Wouldn't be hard to rsync that data to rotating s3 buckets/nfs shares/ftp drops/whatever

Re: It’s About Time for Time Series Databases

#135

Post by postwait » Fri, Jan 26, 2018, 2:54 AM UTC

marknadal wrote: post #71

We had major success by simply batching incoming writes into ~15 second chunks and writing that as a file to S3 and an index that tracks how the files are split / chunked to make read performance decent. This alone gave us an insanely scalable (load tested against 100GB/day ~100M records/day) for a grand cheap total cost of $10/day for everything, server, disk, and S3. https://youtu.be/x_WqBuEA7s8 Works great for tim…

Storing and retrieving data has never been all that hard. The challenge is having user-interactive performance on complex queries against the data. Comparing and correlating and deriving and integrating and ... (lots of other analysis). For many "scaled" systems, 100M records/minute isn't uncommon... and while that's very likely possible with your design the question of economic feasibility enters. Solving these problems at scale with good economics is the playground of TSDB vendors today.

Re: It’s About Time for Time Series Databases

#136

Post by postwait » Fri, Jan 26, 2018, 2:56 AM UTC

alistairbayley wrote: post #131

amyjess wrote: post #37

At my employer, we've recently (as of the middle of last year) been making a considerable effort to use InfluxDB to track our KPIs. It's working out wonderfully for us, and I'm expecting it'll get used more and more as the year goes on. What really floors me about Influx is how fast it is. A query that used to take hours in Oracle takes seconds in Influx. And the influx query is readable: rolling data up to various i…

How big is your database, and how long does it take to restart? We have what I would describe as a fairly small influx database, and it takes a long time to restart (20-30 mins). And the time to restart seems to be growing linearly with the db size. Not cool if you do regular server patching with reboots. We also had a problem where user queries that return too many columns cause the DB server process to OOM. And the…

Try IRONdb... Relatively fast restarts, linear cluster scalability and no downtime when a single node is malfunctioning.

Re: It’s About Time for Time Series Databases

#137

Post by aksyn » Fri, Jan 26, 2018, 3:37 AM UTC

akulkarni wrote: post #81

RobAtticus wrote: post #19

Earlier quoted context omitted.

That is currently next in our pipeline for a benchmark blog post. Early results look good on both the read and write side in terms of raw performance, with the benefit of more complex queries being more easily expressable.

To add to this: We've found that the benefit of Cassandra's approach is when scaling out to 100s of nodes (which TimescaleDB currently does not support). But we found Cassandra's per node usage not that impressive. We'll have more benchmarks vs Cassandra soon (@robatticus is literally the person working on it), but suffice to say that users have already replaced multi-node Cassandra clusters (e.g., 10 nodes) with a s…

I recommend you benchmark against ScyllaDB, not Cassandra.

Re: It’s About Time for Time Series Databases

#138

Post by creeble » Fri, Jan 26, 2018, 6:01 AM UTC

rpedela wrote: post #128

creeble wrote: post #103

Earlier quoted context omitted.

I don't see how any of those apply for a two-column 'db' of timestamp and data.

It is database per device and table per sensor, not just one table. With SQL you can do joins, aggregations, etc. If you just want to log it, sure use a log file. As soon as you want to use the data somehow, you need SQL or your own log file parsing and query code. Personally, I rather just use SQL.

If you’re storing from multiple sensors with multiple write processess, you will have concurrency problems if they’re in the same db with SQLite.

I’m not referring to manipulating or even querying the data after it’s stored. I just don’t see how saving the original data in SQLite buys you anything, if it needs to be collected into a db for queries, etc, anyway.

Re: It’s About Time for Time Series Databases

#139

Post by cbcoutinho » Fri, Jan 26, 2018, 10:12 AM UTC

We use a proprietary database system that uses a 'flat-file' format (no idea what that means) and is primarily time series based due to the fact that we're logging sensor data. Since it's primarily a backend, you can't access it outside of their proprietary gui. It's also accessible as a linked server via SQL Server, but this is slow as hell for non-trivial queries. We use it within a power plant setting where we heavily prioritize db-writes, which this software is apparently very good in, and db-reads are less of a focus.

I'm not sure if moving to another db system would be beneficial, but I would be very grateful if accessiblity could be much less of a hack

Re: It’s About Time for Time Series Databases

#140

Post by dx034 » Fri, Jan 26, 2018, 10:46 AM UTC

What I often miss with these kinds of databases is compression capabilities. I currently use my own delta-of-delta encoding for time series data (stored in postgres) as that gave me much better compression than any of the time series databases I tested.

It's not that storage isn't available, it's easy to store terrabytes of data. But really fast storage is still expensive and rare. Being able to compress time series data to 3-5% of the raw value allows keeping most in memory, speeding up reading writing and analysis.

InfluxDB is quite good at that but unfortunately, they struggle storing many small datasets.