[HN Gopher] Using ClickHouse to scale an events engine
___________________________________________________________________
Using ClickHouse to scale an events engine
Author : wyndham
Score : 116 points
Date : 2024-04-11 18:02 UTC (4 hours ago)
(HTM) web link (github.com)
(TXT) w3m dump (github.com)
| mathnode wrote:
| And if you use MariaDB, just enable columnstore. Why not treat
| yourself to s3 backed storage while you are there?
|
| It is extremely cost effective when you can scale a different
| workload without migrating.
| hipadev23 wrote:
| This is no shade to postgres or maria, but they don't hold a
| candle to the simplicity, speed, and cost efficiency of
| clickhouse for olap needs.
| flessner wrote:
| And I mean why should they? They work great for what they are
| made for and that is all that matters!
| riku_iki wrote:
| I have tons of OOMs with clickhouse on larger than RAM OLAP
| queries.
|
| While postgres works fine (even it is slower, but actually
| returns results)
| nsguy wrote:
| There are various knobs in ClickHouse that allow you to
| trade memory usage for performance. (
| https://clickhouse.com/docs/en/operations/settings/query-
| com... e.g.)
|
| But yes, I've seen similar issues, running out of memory
| during query processing, it's a price you pay for higher
| performance. You need to know what's happening under the
| hood and do more work to make sure your queries will work
| well. I think postgres can be a thousand or more times
| slower, and doesn't have the horizontal scalability, so if
| you need to do complex queries/aggregations over billions
| of records then "return result" doesn't cut it. If postgres
| addresses your needs then great- you don't need to use
| ClickHouse...
| riku_iki wrote:
| > There are various knobs in ClickHouse that allow you to
| trade memory usage for performance.
|
| but what knobs to use and what values to use in each
| specific case? Query just usually fails with some generic
| OOM message without much information.
| silisili wrote:
| As a caveat, I'd probably say 'at large volumes.'
|
| For a lot of what people may want to do, they'd probably
| notice very little difference between the three.
| mathnode wrote:
| For multi-tb or pb needs I would not stray from mariadb.
| Especially when using columnstore. I have taken the pepsi
| challenge, even after trying vertica and netezza. Not HANA
| though; one has had enough of SAP.
| dangoodmanUT wrote:
| deleting this comment because apparently jokes are not received
| well here
| mritchie712 wrote:
| > Recently, the most interesting rift in the Postgres vs OLAP
| space is [Hydra](https://www.hydra.so), an open-source, column-
| oriented distribution of Postgres that was very recently
| launched (after our migration to ClickHouse). Had Hydra been
| available during our decision-making time period, we might've
| made a different choice.
|
| There will likely be a good OLAP solution (possibly implemented
| as an extension) in Postgres in the next year or so. Many
| companies are working on it (Hydra, Parade[0], etc.)
|
| 0 - https://www.paradedb.com/
| kapilvt wrote:
| for others curious
|
| ParadeDB - AGPL License
| https://github.com/paradedb/paradedb/blob/dev/LICENSE
|
| Hydra - Apache 2.0
| https://github.com/hydradatabase/hydra/blob/main/LICENSE
|
| also hydra seems derived from citusdata's columnar
| implementation.
| mdaniel wrote:
| Don't feel bad, lots of people get bitten by not reading
| all the way down to the bottom of their readme: https://git
| hub.com/hydradatabase/hydra/blob/v1.1.2/README.md... While
| _Hydra_ may very well license their own code Apache 2, they
| ship the AGPLv3 columnar which to my very best IANAL
| understanding taints the whole stack and AGPLv3 's
| everything all the way through https://github.com/hydradata
| base/hydra/blob/v1.1.2/columnar/...
| samber wrote:
| I'm curious: how many rows Lago store in its CH cluster? Do they
| collect data for fighting fraud?
|
| PG can handle a billion rows easily.
| JosephRedfern wrote:
| Reading between the lines, given they're talking > 1 million
| rows per minute, I'd guess on the order of trillions of rows
| rather than billions (assuming they retain data for more than a
| couple of weeks)
| jacobsenscott wrote:
| PG can handle billions of rows for certain use cases, but not
| easily. Generally you can make things work but you definitely
| start entering "heroic effort" territory.
| didip wrote:
| OLAP databases need to be able to handle billions of rows per
| hour/day.
|
| I super love PG but PG is too far away from that.
| mritchie712 wrote:
| > Recently, the most interesting rift in the Postgres vs OLAP
| space is [Hydra](https://www.hydra.so), an open-source, column-
| oriented distribution of Postgres that was very recently launched
| (after our migration to ClickHouse). Had Hydra been available
| during our decision-making time period, we might've made a
| different choice.
|
| There will likely be a good OLAP solution (possibly implemented
| as an extension) in Postgres in the next year or so. There are a
| few companies are working on it (Hydra, Parade[0], tembo etc.).
|
| 0 - https://www.paradedb.com/
| riku_iki wrote:
| > 0 - https://www.paradedb.com/
|
| this looks like repackaging of datafusion as PG extension?..
| mritchie712 wrote:
| yes, that's a succinct way to put it.
| joshstrange wrote:
| I feel like with all the Clickhouse praise on HN that we /must/
| be doing something fundamentally wrong because I hate every
| interaction I have with Clickhouse.
|
| * Timeouts (only 30s???) unless I used the cli client
|
| * Cancelling rows - Just kill me, so many bugs and FINAL/PREWHERE
| are massive foot-guns
|
| * Cluster just feels annoying and fragile don't forget "ON
| CLUSTER" or you'll have a bad time
|
| Again, I feel like we must be doing something wrong but we are
| paying an arm and a leg for that "privilege".
| nsguy wrote:
| What is your use case? If you're deleting rows that already
| feels like maybe it's not the intended use case. I think about
| clickhouse as taking in a firehose of immutable data that you
| want to aggregate/analyze/report on. Let's say a million
| records per second. I'll make up an example, the orientation,
| speed and acceleration of every Tesla vehicle in the world in
| real time every second.
| joshstrange wrote:
| It's to power all our analytics. We ETL data into it and some
| data is write-once so we don't have updates/deletes but a
| number of our tables have summary data ETL'd into them which
| means cleaning up the old rows.
|
| I'm sure CH shines for insert-only workloads but that doesn't
| cover all our needs.
| mosen wrote:
| Have you looked into the ReplacingMergeTree table engine?
| (Although we still needed to use FINAL with this one)
| hipadev23 wrote:
| CH works just fine for cleaning up rows: Delete with
| mutations sync=1, or use optimize with deduplicate by, or
| use aggregate trees and optimize final, or query aggregate
| tables with final=1.
|
| Numerous ways to achieve removal of old/stale rows.
| nsguy wrote:
| You can always use different databases for different use
| cases.
|
| There are many applications that require extremely high
| insertion rates (millions of records per second), very
| large total number of rows (billions, trillions) and
| flexible/fast querying/aggregation with high read rates
| (100's of millions or higher rows/s) and that's sort of the
| sweet spot IMO for ClickHouse and where you'll be pressed
| to find alternatives. I'm sure it can be used in other
| situations but maybe there are more choices if you're in
| those.
| darthShadow wrote:
| You have already gotten excellent options from the other
| comments, but here's another one that's not been mentioned
| yet.
|
| You may want to consider adjusting your partition key (if
| feasible) as a function of datetime so you can just drop a
| complete partition when required, rather than needing
| separate delete queries.
|
| In my experience, it has proven to be a very quick and
| clean way to clear out older data.
| unixhero wrote:
| Tableau
| shin_lao wrote:
| It's meant to store immutable data, and isn't great if you need
| low-latency updates. Also it's quirky in some ways.
| joshstrange wrote:
| > It's meant to store immutable data
|
| I don't disagree, I feel like we might be using it wrong. We
| were trying to replace ES with it but it just doesn't feel
| like it fits our needed usecase.
| shin_lao wrote:
| How many rows do you have on average per day?
| joshstrange wrote:
| A couple million (<10M), I don't have a better number
| available right now. Not all (or even most) of those need
| cancelling rows thankfully.
| hipadev23 wrote:
| You don't need a cluster nor should you be having any
| issues you mentioned. I run 10x that volume daily on a
| single gcp box (8 core / 64GB). We migrated off BigQuery
| and went from $10k/mo to about $250/mo. And it's faster
| for both low-latency and big slow queries.
| joshstrange wrote:
| The plan is to 10x that volume in the not too distant
| future but given what you've said I can believe we are
| horribly over-provisioned/over-scaled. Thank you!
| hipadev23 wrote:
| It sounds like you're probably using Clickhouse Cloud? If
| so, I was not impressed. Overly pushy sales people,
| pricing isn't competitive, and they're trying to cater to
| the snowflake/databricks crowd without smoothing any
| rough edges (like the default timeout being enabled on a
| GUI).
|
| Overall I'd say CH isn't as tolerant or forgiving as
| BigQuery, Snowflake, or Databricks. You can write the
| worst SQL possible and BQ will happily charge you $5/TB
| for that cartesian self-join. CH meanwhile will error
| with memory limit or even crash.
| joshstrange wrote:
| We are using Altinity. I believe it's a 3-server cluster
| and we have 2 clusters (our prod one and another one we
| are trying to promote to production once our data
| integrity checks pass, at which point we will spin the
| other down).
| hipadev23 wrote:
| Robert Hodges of Altinity will likely be here shortly,
| they monitor HN for any mention of clickhouse, maybe they
| can get you fixed up without their standard $10k/mo
| support contract.
| zX41ZdbW wrote:
| Interesting about "Timeouts (only 30s???)" - most likely, this
| is a limitation configured explicitly for a user on your
| server. You can set it up with the `max_execution_time`, and by
| default, it is unlimited.
|
| For example, I've set it up, along with many more limitations
| for my public playground https://play.clickhouse.com/, and it
| allows me to, at least, make it public and not worry much.
|
| It could also be a configuration of a proxy if you connect
| through a proxy. ClickHouse has built-in HTTP API, so you can
| query it directly from the browser or put it behind Cloudflare,
| etc... Where do you host ClickHouse?
| joshstrange wrote:
| I can believe it's a config issue, I'll have to look into it.
| I didn't setup the cluster/dbs and when I asked about I was
| told "use the cli". I'll try to see if I can get that fixed.
| ergonaught wrote:
| Most, though certainly not all, problems I see with ClickHouse
| usage come from pretending it is another database or that it is
| intended for other use cases.
| alecfong wrote:
| What foot guns have you run into with FINAL?
| joshstrange wrote:
| Just forgetting to use it or PREWHERE. Since queries run just
| fine without those you can think you have something working
| when you actually have duplicate rules.
| citrin_ru wrote:
| > * Timeouts (only 30s???) unless I used the cli client
|
| Almost all clients (client libraries) allow a configurable
| timeout. In server settings there is a max query time settings
| which can be adjusted if necessary:
| https://clickhouse.com/docs/en/operations/settings/query-com...
| HermitX wrote:
| Is ClickHouse a suitable engine for analyzing events? Absolutely,
| as long as you're analyzing a large table, its speed is
| definitely fast enough. However, you might want to consider the
| cost of maintaining an OSS ClickHouse cluster, especially when
| you need to scale up, as the operational costs can be quite high.
|
| If your analysis in Postgres was based on multiple tables and
| required a lot of JOIN operations, I don't think ClickHouse is a
| good choice. In such cases, you often need to denormalize
| multiple data tables into one large table in advance, which means
| complex ETL and maintenance costs.
|
| For these more common scenarios, I think StarRocks
| (www.StarRocks.io) is a better choice. It's a Linux Foundation
| open-source project, with single-table query speeds comparable to
| ClickHouse (you can check Clickbench), and unmatched multi-table
| join query speeds, plus it can directly query open data lakes.
| jakearmitage wrote:
| > consider the cost of maintaining an OSS ClickHouse cluster I
| mean... it is pretty straightforward. 40~60 line Terraform,
| Ansible with templates for the proper configs that get exported
| from Terraform so you can write the IPs so they can see each
| other, and you are done.
|
| What else could you possibly need? Backing up is built into it
| with S3 support:
| https://clickhouse.com/docs/en/operations/backup#configuring...
|
| Upgrades are a breeze:
| https://clickhouse.com/docs/en/operations/update
|
| People insist that OMG MAINTENANCE I NEED TO PAY THOUSANDS FOR
| MANAGED is better, when in reality, it is not.
| drewda wrote:
| This change may make sense for Lago as a hosted multi-tenant
| service, as offered by Lago the company.
|
| Simultaneously this change may _not_ make sense for Lago as an
| open-source project self-hosted by a single tenant.
|
| But that may also mean that it effectively makes sense for Lago
| as a business... to make it harder to self host.
|
| I don't at all fault Lago for making decisions to prioritize
| their multi-tenant cloud offering. That's probably just the
| nature of running open-source SaaS these days.
| stephen123 wrote:
| How were they doing millions of events per minute with postgres.
|
| I'm struggling with pg write performance ATM and want some tips.
| unixhero wrote:
| Turn off indexing and other optimizations done on a table level
___________________________________________________________________
(page generated 2024-04-11 23:00 UTC)