3 ms·

I did some time series data benchmarking recently. Most large data is "time series" data, but I will not digress on terminology right now. For the usecase I was

by ants_a 6y ago

I did some time series data benchmarking recently. Most large data is "time series" data, but I will not digress on terminology right now. For the usecase I was looking at my results for InfluxDB did not qualitatively disagree the above blogpost. Timescale got better compression efficiency, faster query results, more constrained memory usage, but lower ingest speed at high concurrencies blocked by WAL insert locking and single threaded compression. If InfluxDBs measurement oriented data model and query interface is a good fit for your use case, and you don't have high cardinality data, then it might be convenient to use it, but it's a terrible choice for anything outside its niche. However none of the databases tested above is anywhere close to the efficiency of a column store database that can do vectorized execution over batches of rows. ClickHouse is a good example of one such database. For queries that have to shift through large amounts of data, either filtering or aggregating it, the performance difference is easily >10x. I was seeing aggregation performance above 2B rows/s and that was I/O throughput bound.

hardwaresofton 6y ago
I know the first thing that shocked me was how they could get so close to something that was purpose built for time series. Even being within spitting distance is really great in my opinion for an off the shelf, general tool. > However none of the databases tested above is anywhere close to the efficiency of a column store database that can do vectorized execution over batches of rows. ClickHouse is a good example of one such database. For queries that have to shift through large amounts of data, either filtering or aggregating it, the performance difference is easily >10x. I was seeing aggregation performance above 2B rows/s and that was I/O throughput bound. Agreed -- OLTP (in the case of Timescale) and purpose built timeseries-focused (but not necessarily analytics focused) DBs hold nothing to a proper OLAP database. Did you write about this anywhere? would love to read it. I've never had a real need for the kind of stuff that Clickhouse does, but it looks to be the best in class for F/OSS OLAP DBs. Have you ever tried Druid?
- arminiusreturns 6y ago
  I'm out of my depth here (not a db guy but curious), so forgive the interruption, but have either of you messed with either SciDB or VoltDB? I have found them to be quite interesting especially given Stonebraker himself being involved in the design. Druid and timescale have also been on my radar too, and I think these comments have finally given me the push to play around with them in my sandbox.
  - hardwaresofton 6y ago
    I've never even heard of SciDB, thanks for the pointer. I have heard of VoltDB and the only thing I remembered was that it was in-memory... I'm still not 100% sure what SciDB is for but it looks like none of the databases we're mentioning are directly comparable: - SciDB for scientific computing (??) - VoltDB for in memory + SQL - Druid for OLAP queries - TimescaleDB for row-based timeseries storage Seems like all apples to oranges to me. I personally like TimescaleDB because it runs on Postgres and I can usually find a way to do all those other things in postgres relatively efficiently (memory requires some squinting while using UNLOGGED tables).
  - manigandham 6y ago
    VoltDB is about performance by computing entirely in-memory and using a strict sharding architecture. It's unique that you interact through stored procedures that are shared by the data so each node processes completely in parallel without any side-effects. It's more akin to other streaming/SQL processing engines like Spark rather than a fully functional SQL database. I highly recommend MemSQL (now called Singlestore) for a really polished distributed relational database that combines OLTP with OLAP columnstores.
- ants_a 6y ago
  I was really surprised at how well Timescales compression worked. It was pretty much comparable to best in class columnstores. Only the row-by-row query execution engine was holding it back. Perhaps something that future versions of postgres can help with. I haven't published the benchmarking results anywhere yet, but I will probably do some conference talks on it once that is a thing again. Didn't look into Druid in detail, but did try out Hive. Both of them look more suitable for cases where there is significant engineering effort in developing the data ingest and structuring pipeline. I wouldn't recommend either to a small team. With a measly triple digit TB database size both seemed overkill.
  - hardwaresofton 6y ago
    > I was really surprised at how well Timescales compression worked. It was pretty much comparable to best in class columnstores. Only the row-by-row query execution engine was holding it back. Perhaps something that future versions of postgres can help with. I think zedstore[0] might be something that could help here. I've mentioned it in the past but one of the best things about postgres is it's extensibility, and if timescale rides that wave (and maybe contacts zedstore to get this integration started early) it could be awesome. [0]: https://blogs.vmware.com/opensource/2020/07/14/zedstore-compressed-columnar-storage-for-postgres/ https://blogs.vmware.com/opensource/2020/07/14/zedstore-comp... > Didn't look into Druid in detail, but did try out Hive. Both of them look more suitable for cases where there is significant engineering effort in developing the data ingest and structuring pipeline. I wouldn't recommend either to a small team. With a measly triple digit TB database size both seemed overkill. Thanks for this -- I haven't tried it yet at all but will try to remember this. ClickHouse was already first on my list for hobbyist->enterprise scalability but this cements it.
valyala 6y ago
ClickHouse is great OLAP database with outstanding performance! It can be used for collecting and querying observability data [1]. But it may be hard to properly design database schema for ClickHouse for storing general-purpose observability data. That's why we created VictoriaMetrics - purpose-built time series database, which is based on ClickHouse architecture ideas [2]. It just works out of the box without the need to design database schema, while providing outstanding performance [3]. [1] https://github.com/lomik/graphite-clickhouse https://github.com/lomik/graphite-clickhouse [2] https://valyala.medium.com/how-victoriametrics-makes-instant-snapshots-for-multi-terabyte-time-series-data-e1f3fb0e0282 https://valyala.medium.com/how-victoriametrics-makes-instant... [3] https://valyala.medium.com/measuring-vertical-scalability-for-time-series-databases-in-google-cloud-92550d78d8ae https://valyala.medium.com/measuring-vertical-scalability-fo...

by ants_a 6y ago

hardwaresofton 6y ago

I know the first thing that shocked me was how they could get so close to something that was purpose built for time series. Even being within spitting distance is really great in my opinion for an off the shelf, general tool. > However none of the databases tested above is anywhere close to the efficiency of a column store database that can do vectorized execution over batches of rows. ClickHouse is a good example of one such database. For queries that have to shift through large amounts of data, either filtering or aggregating it, the performance difference is easily >10x. I was seeing aggregation performance above 2B rows/s and that was I/O throughput bound. Agreed -- OLTP (in the case of Timescale) and purpose built timeseries-focused (but not necessarily analytics focused) DBs hold nothing to a proper OLAP database. Did you write about this anywhere? would love to read it. I've never had a real need for the kind of stuff that Clickhouse does, but it looks to be the best in class for F/OSS OLAP DBs. Have you ever tried Druid?

arminiusreturns 6y ago
I'm out of my depth here (not a db guy but curious), so forgive the interruption, but have either of you messed with either SciDB or VoltDB? I have found them to be quite interesting especially given Stonebraker himself being involved in the design. Druid and timescale have also been on my radar too, and I think these comments have finally given me the push to play around with them in my sandbox.
- hardwaresofton 6y ago
  I've never even heard of SciDB, thanks for the pointer. I have heard of VoltDB and the only thing I remembered was that it was in-memory... I'm still not 100% sure what SciDB is for but it looks like none of the databases we're mentioning are directly comparable: - SciDB for scientific computing (??) - VoltDB for in memory + SQL - Druid for OLAP queries - TimescaleDB for row-based timeseries storage Seems like all apples to oranges to me. I personally like TimescaleDB because it runs on Postgres and I can usually find a way to do all those other things in postgres relatively efficiently (memory requires some squinting while using UNLOGGED tables).
- manigandham 6y ago
  VoltDB is about performance by computing entirely in-memory and using a strict sharding architecture. It's unique that you interact through stored procedures that are shared by the data so each node processes completely in parallel without any side-effects. It's more akin to other streaming/SQL processing engines like Spark rather than a fully functional SQL database. I highly recommend MemSQL (now called Singlestore) for a really polished distributed relational database that combines OLTP with OLAP columnstores.
ants_a 6y ago
I was really surprised at how well Timescales compression worked. It was pretty much comparable to best in class columnstores. Only the row-by-row query execution engine was holding it back. Perhaps something that future versions of postgres can help with. I haven't published the benchmarking results anywhere yet, but I will probably do some conference talks on it once that is a thing again. Didn't look into Druid in detail, but did try out Hive. Both of them look more suitable for cases where there is significant engineering effort in developing the data ingest and structuring pipeline. I wouldn't recommend either to a small team. With a measly triple digit TB database size both seemed overkill.
- hardwaresofton 6y ago
  > I was really surprised at how well Timescales compression worked. It was pretty much comparable to best in class columnstores. Only the row-by-row query execution engine was holding it back. Perhaps something that future versions of postgres can help with. I think zedstore[0] might be something that could help here. I've mentioned it in the past but one of the best things about postgres is it's extensibility, and if timescale rides that wave (and maybe contacts zedstore to get this integration started early) it could be awesome. [0]: https://blogs.vmware.com/opensource/2020/07/14/zedstore-compressed-columnar-storage-for-postgres/ https://blogs.vmware.com/opensource/2020/07/14/zedstore-comp... > Didn't look into Druid in detail, but did try out Hive. Both of them look more suitable for cases where there is significant engineering effort in developing the data ingest and structuring pipeline. I wouldn't recommend either to a small team. With a measly triple digit TB database size both seemed overkill. Thanks for this -- I haven't tried it yet at all but will try to remember this. ClickHouse was already first on my list for hobbyist->enterprise scalability but this cements it.

valyala 6y ago

ClickHouse is great OLAP database with outstanding performance! It can be used for collecting and querying observability data [1]. But it may be hard to properly design database schema for ClickHouse for storing general-purpose observability data. That's why we created VictoriaMetrics - purpose-built time series database, which is based on ClickHouse architecture ideas [2]. It just works out of the box without the need to design database schema, while providing outstanding performance [3]. [1] https://github.com/lomik/graphite-clickhouse https://github.com/lomik/graphite-clickhouse [2] https://valyala.medium.com/how-victoriametrics-makes-instant-snapshots-for-multi-terabyte-time-series-data-e1f3fb0e0282 https://valyala.medium.com/how-victoriametrics-makes-instant... [3] https://valyala.medium.com/measuring-vertical-scalability-for-time-series-databases-in-google-cloud-92550d78d8ae https://valyala.medium.com/measuring-vertical-scalability-fo...