Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

ListenBrainz moves to TimescaleDB

blog.metabrainz.org

Post reply on HN
Print view View original

61–70 of 100 posts

Re: ListenBrainz moves to TimescaleDB

#61

Post by willvarfar »

samhld wrote: post #55
RedShift1 wrote: post #18

I'm in the process of moving from InfluxDB to TimescaleDB myself and can't wait to get rid of the hoops I have to jump through to get InfluxDB to answer some basic questions, mostly stemming from the fact that InfluxQL doesn't support boolean expressions. Something like 'SELECT MAX(temperature) > 10 FROM...' doesn't work.

(InfluxData solution architect here) Boolean is supported. You query it in the WHERE clause. Try `SELECT MAX(temperature) FROM ... WHERE temperature > 10`. That said, I'm not sure why you'd run a query like that in InfluxQL as it's the same as `SELECT max(temperature)`. :).

Question for influx solution architect:

How do you delete points from a specific measurement in a specific retention policy?

Re: ListenBrainz moves to TimescaleDB

#62

Post by jbmsf »

dominotw wrote: post #58
jbmsf wrote: post #56

I'd love to hear more about how your data ingestion works. I'm thinking of implementing TimescaleDB myself, but in my initial read of the docs, the focus seemed to be managing the database, not getting data into the database...

same way you'd insert data into postgres.

That's not really helpful. Let's assume you have a distributed system; you probably don't want all of your system components connecting directly to TimescaleDB. You also probably want to have some layer that implements queuing and handles back pressure if it can't insert into the database at the rate that events are coming in. You may want to batch insert data.

I'd assume that most anyone building a system like this at scale has to solve these problems; does everyone roll their own?

Re: ListenBrainz moves to TimescaleDB

#63

Post by samhld »

kev009 wrote: post #59
samhld wrote: post #55

Earlier quoted context omitted.

(InfluxData solution architect here) Boolean is supported. You query it in the WHERE clause. Try `SELECT MAX(temperature) FROM ... WHERE temperature > 10`. That said, I'm not sure why you'd run a query like that in InfluxQL as it's the same as `SELECT max(temperature)`. :).

Blame the customer. Nice solution, architect!

Huh? I don't mean to come off as blaming anyone for anything. Trying to offer a work around for their problem with InfluxQL. They usually exist. They may not be good enough for some but offering them doesn't hurt. In this case, I certainly misunderstood the need though.

Re: ListenBrainz moves to TimescaleDB

#64

Post by 1996 »

cevian wrote: post #48
1996 wrote: post #41

Earlier quoted context omitted.

It would be too long. To quickly summarize, from the pain of backups (unless you setup a WAL replica, the load may take your database down), the large size of the data on disk (timescale does offer some compression now, but it's still too much), the low performance of large queries, the memory requirements - it's death by a thousand papercuts! Don't get me wrong, timescale is a great way to get started with time seri…

(TimescaleDB engineer here) Some of the comments here sound technically off. We've never seen a backup take down a machine. The backups we use are the same as Postgres which are used by millions of companies without a problem (and can be streaming incremental backups like pgBackrest, WAL-E, etc. or whole-database backups like pg_dump). As with any DB you do have to size and configure your database correctly (which th…

On servers with a very high CPU load, backup done without using continuous streaming to a second server and done from this second server, something (I have stopped using timescale so I can't tell you what did) during backup caused a peak in load and IO, impacting read and write performance of the primary server, causing a cascading failure of the processes due to timeouts, eventually taking the server down due to swap issues and OOM triggering a reboot.

So we stopped doing backups. Actually, that's how we started using clickhouse: for cold storage, as the files in /var/lib/clickhouse used far less storage space and issues. Eventually the same data was sent both to timescaledb and clickhouse, in a poor's man backup. Finally, timescaledb was removed.

> As with any DB you do have to size and configure your database correctly (which these days isn't hard).

Thanks for supposing we didn't try. We did not end up with 256Gb of RAM per server for no reason.

All I'm saying is that Timescale totally has a place, but not beyond a certain scale and complexity.

> We've never seen anybody claim that ClickHouse offers significantly better compression than we do overall

Altiny does, so do a few others. mandigandham above says that you are now at 70% of what clickhouse does. I'm not saying you're not improving. It was just one of the too many issues we had to fight.

Also, you have only recently introduced compression - good, but I'm not aware if you already offer something like DateTime Codec(DoubleDelta, LZ4), or the choice of compression algorithms. LZ4 can be slow, so there is a choice between various alternatives.

For example, T64 calculates the max and min values for the encoded range, and then strips the higher bits by transposing a 64-bit matrix. Sometimes it makes sense. zStd is slower than T64 but needs to scan less data, which makes up for it. Sometimes it makes more sense.

Large databases need more flexibility.

> If you are processing all of your data for all your queries then yes, click house sequential scans may be better

I confirm, it is better.

And for some workloads, continuous aggregates make no sense.

> We've seen customers successfully use our single-node version with 100s of billions of rows so claiming that we are just for small use-cases is simply untrue, and especially with the launch of multi-node TimescaleDB

I have about 50Tb of data per server. What is below 1Tb I call "small use cases".

> I understand people may have different preferences and experiences, but some of these felt a bit off to me.

When I was trying to use timescaledb and reported weird issues, I had the same return: my use case and bug report felt "off" to the person I reported them to.

Maybe it is why they weren't addressed - or maybe much later, when reported by more clients?

Personally, I have no horse in the game. If you become better than clickhouse for my workload, and if the license change to allow me to deploy to a cluster of AWS servers (just in case we ditch our own hardawre), I will consider timescale again in the future.

For now, I'm watching it evolve, and slowly address the outstanding issues, like disk usage, and performance. By your own admission and benchmarks, you are now at 70% of what clickhouse does - in my experience, the actual difference is much higher.

But I sincerely hope you succeed and catch up, as more software diversity is always better.

Re: ListenBrainz moves to TimescaleDB

#65

Post by samhld »

RedShift1 wrote: post #57
samhld wrote: post #55

Earlier quoted context omitted.

(InfluxData solution architect here) Boolean is supported. You query it in the WHERE clause. Try `SELECT MAX(temperature) FROM ... WHERE temperature > 10`. That said, I'm not sure why you'd run a query like that in InfluxQL as it's the same as `SELECT max(temperature)`. :).

It's not the same thing. 'SELECT MAX(temperature) > 10 FROM ...' gets you all the datapoints, with a value of true or false. Moving it to the WHERE clause only gets you the datapoints where temperature is > 10. Yes, you can fill with 0 after the GROUP BY, but if all datapoints are less than 10, you get nothing back from the database. That's confusing: were all datapoints less than 10, or was there nothing registered…

Yep you're right -- oversight on my part. To be clear, the "not sure why you'd run a query like that" was referring to doing it the InfluxQL way (which is not the same thing)...where your results would end up being the same controlling for time range.

Re: ListenBrainz moves to TimescaleDB

#66

Post by 1996 »

mfreed wrote: post #49
1996 wrote: post #41

Earlier quoted context omitted.

It would be too long. To quickly summarize, from the pain of backups (unless you setup a WAL replica, the load may take your database down), the large size of the data on disk (timescale does offer some compression now, but it's still too much), the low performance of large queries, the memory requirements - it's death by a thousand papercuts! Don't get me wrong, timescale is a great way to get started with time seri…

Hi - I just want to clarify some mistake / misrepresentation about our Timescale License (TSL): 1. The TSL is not a new license, have had it in place since late 2018. What we recently announced is that multi-node TimescaleDB will be available for free under the TSL (free, source available), while (for example) clustered InfluxDB is purely proprietary (paid, closed source). 2. Our TSL license prevents offering Timesca…

> InfluxDB is purely proprietary (paid, closed source).

And clickhouse is not. I just suggest skipping the timescaledb step to someone migrating from influx, and going straight to clickhouse.

> For the TSL version, what it primarily restricts is the cloud providers like AWS and Azure from offering TimescaleDB-as-a-service (e.g., TimescaleDB Community on AWS RDS)

If there is some kind of emergency and I need to have the database on the cloud, this is a serious restriction. It limits my choices and constrains my actions.

> Many thousands of companies use our community version for free to build SaaS services running on their own AWS instances.

We have our servers, so it wasn't an issue. It was more of a long term concern, a chilling effect: what else may be restricted in the future?

Again, I think timescaledb has a wonderful place. It will certainly become the entry level database for timeseries.

It is just not suite for our workload.

Re: ListenBrainz moves to TimescaleDB

#67

Post by akulkarni »

1996 wrote: post #64
cevian wrote: post #48

Earlier quoted context omitted.

(TimescaleDB engineer here) Some of the comments here sound technically off. We've never seen a backup take down a machine. The backups we use are the same as Postgres which are used by millions of companies without a problem (and can be streaming incremental backups like pgBackrest, WAL-E, etc. or whole-database backups like pg_dump). As with any DB you do have to size and configure your database correctly (which th…

On servers with a very high CPU load, backup done without using continuous streaming to a second server and done from this second server, something (I have stopped using timescale so I can't tell you what did) during backup caused a peak in load and IO, impacting read and write performance of the primary server, causing a cascading failure of the processes due to timeouts, eventually taking the server down due to swa…

Thanks for the thoughtful response.

On the compression point:

- I believe the Altinity Benchmarks [0] are from 2018, on TimescaleDB 0.12.1. TimescaleDB has gotten much better since then (now on version 1.7.2), and most notably, offers native compression now (it did not then).

- I believe manigandham's 70% comment is more of an offhand estimate and not a concrete benchmark. But perhaps he can weigh in. :-)

- Re: compression algorithms, TimescaleDB now employs several best-in-class algorithms, including delta-delta, gorilla, Simple-8b RLE. Much more than just LZ4. [1]

Overall, I don't think anyone has done a real storage comparison between TimescaleDB and Clickhouse since we launched native compression. It's on our todo list, but we also welcome external benchmarks. But based on what we've found versus similar systems, I suspect our storage usage would be really similar.

[0] https://www.altinity.com/blog/clickhouse-for-time-series

[1] https://blog.timescale.com/blog/time-series-compression-algo...

Re: ListenBrainz moves to TimescaleDB

#68

Post by Lockerman »

jarym wrote: post #60
mfreed wrote: post #19

Fun fact: TimescaleDB exists because we were using InfluxDB + Postgres for a previous IoT project and also found it unworkable (developer experience, query language, reliability, scalability and performance, operations, etc). We first built TimescaleDB as "Postgres for time-series" for our own needs and then decided to open-source it for others. :-)

Have you been following ZHeap and do you think Timescale will benefit from a storage engine like that (less write amplification)?

Timescale engineer here. I'm betting we'll see a nice win; we tend to see write-mostly workloads the UNDO shouldn't be too expensive, and the smaller tuple sizes should be nice. We've built Timescale to be compatible with custom storage engines, so it should work as a drop-in, though of course until we've tested it we won't be sure.

Re: ListenBrainz moves to TimescaleDB

#69

Post by majkinetor »

samhld wrote: post #54
sgt wrote: post #43

Earlier quoted context omitted.

Ok, we had an issue deleting data from a series. The delete just hung and never completed. I assumed this was not really possible on Influx. Perhaps this is an edge case.

(Solution architect at InfluxData here) Out of curiosity...what was the need for this DELETE? Deleting (not dropping) being somewhat of a "second class citizen" was a design choice to make room for more pressing time series needs. In my experience, `DELETE`ing is rarely necessary.

I often use metrics for giving reports via Grafana. They are usually 99% correct which is good enough for many cases. The benefit of doing so is that you already need and have nice dashboard for devs to follow what is going on with the system real time, and its just grafana account distant from customer. So we do this on several big gov systems. Sometimes customers complain that there is a slight difference between real state and what metrics show but its not a big deal and rarelly happens (when you have millions of things, is it really important to know 100% precise value in majority of contexts?)

Recently my colegue was testing some script and added some huge numbers on metric that is used for one of those reports. We had to delete those tests as customer complained that now his total invoice number jumped to trillions.

Re: ListenBrainz moves to TimescaleDB

#70

Post by mfreed »

1996 wrote: post #66
mfreed wrote: post #49

Earlier quoted context omitted.

Hi - I just want to clarify some mistake / misrepresentation about our Timescale License (TSL): 1. The TSL is not a new license, have had it in place since late 2018. What we recently announced is that multi-node TimescaleDB will be available for free under the TSL (free, source available), while (for example) clustered InfluxDB is purely proprietary (paid, closed source). 2. Our TSL license prevents offering Timesca…

> InfluxDB is purely proprietary (paid, closed source). And clickhouse is not. I just suggest skipping the timescaledb step to someone migrating from influx, and going straight to clickhouse. > For the TSL version, what it primarily restricts is the cloud providers like AWS and Azure from offering TimescaleDB-as-a-service (e.g., TimescaleDB Community on AWS RDS) If there is some kind of emergency and I need to have t…

Hey, thanks for the continued discussion:

  > If there is some kind of emergency and I need to have
  > the database on the cloud, this is a serious restriction.
  > It limits my choices and constrains my actions.

  > if the license change to allow me to deploy to a cluster
  > of AWS servers (just in case we ditch our own hardawre),
You can deploy TimescaleDB on AWS servers (the TSL certainly allows it). Most of our users do. They don't run their own hardware.

You can even use our Apache-2 k8s helm charts [1] to immediately spin up a cluster of replicated TimescaleDB nodes with automated leader-election/failover and continuous incremental backup. The helm charts have first-class support for AWS EKS.

What the TSL prevents is _Amazon_ offering TimescaleDB as a paid DBaaS service. To my knowledge, none of the major cloud vendors offer Clickhouse as a first-class paid service, so that's somewhat a moot point. I guess theoretically Amazon could launch Clickhouse-as-a-service, but that theoretical possibility doesn't help you in your emergency.

[1] https://github.com/timescale/timescaledb-kubernetes

Post reply on HN