Optimizing writes to OLAP using buffers (ClickHouse, Redpanda, MooseStack)

https://www.fiveonefour.com/blog/optimizing-writes-to-olap-using-buffers

by oatsandsugar • 9 months ago

40 16 9 months ago

flexiflex• 9 months ago

Weird, I always think real time when I think OLAP, but I guess that’s in the “consumption reactivity side” not the “batch inserts are good” side

boomskats• 9 months ago

See it's the exact opposite for me, although my experience is mostly a) building giant cubes in giant enterprise orgs with hourly data volumes you couldn't fit in memory, and b) 10-15 years old (so the hardware sucked and we didn't have duckDB). But yeah, I don't think the O in OLAP standing for 'online' ever really made sense.

I'm curious to know how much of this article is OLAP specific vs just generic good practice for tuning batch insert chunk size. The whole "batch your writes, use 100k rows or 1s worth of data" thing applies equally to pretty much any database, they're just ignoring the availability of builtin bulkload methods so they can arguing that INSERTs are slow so they can fix it by adding Kafka, for reasons? Maybe I'm missing something.

oatsandsugar3• 9 months ago

Author here—this article was meant to highlight how you can optimize writes to CH with streams.

If you want to directly insert data into ClickHouse with MooseStack, we have a direct insert method that allows you to use ClickHouse's bulkload methods.

Here's the implementation: https://github.com/514-labs/moosestack/blob/43a2576de2e22743...

Documentation is here: https://docs.fiveonefour.com/moose/olap/insert-data#performa...

Would love to hear your thoughts on our direct insert implementation!

schmidtleonard• 9 months ago

Well yeah that's the sales pitch :)

It's a tradeoff. Analytics databases are often filled with periodic dumps of transactional databases and this feels so dirty that it's easy to accidentally forget that it isn't just a hack, it's actually a structural workaround for the poor random-write performance of analytics DBs:

OLTP = more read amplification on analytics workflows, less write amplification of random insert

OLAP = less read amplification on analytics workflows, more write amplification of random insert

If that's too theoretical, the other day I saw 1-row updates of about 10kb data lead to 1GB of writes in Redshift: 1MB block size times 300 columns times a log+shuffle factor of about 3. That's a write amplification factor of 100000. Crazy stuff.

quadrature• 9 months ago

There are a few realtime analytic DBs that let you query the in memory streaming buffer. That lets you have the best of both worlds but comes at the risk of inconsistency because you’re querying data that isn’t durable.

coxley• 9 months ago

Off-topic rant: I hate when websites hide the scrollbar. By all means, apply minimal styling to make it cohesive with the website background and foreground. But don't completely hide it.

This is included on that page's stylesheet:

    ::-webkit-scrollbar {
        width: 0;
        height: 0;
        display: none;
    }

doix• 9 months ago

Another reason to use Firefox, it doesn't respect that CSS :)

oatsandsugar3• 9 months ago

Timely! We're redesigning our blog, will keep you posted

bonobocop3• 9 months ago

Why add RedPanda/Kafka over using async insert? https://clickhouse.com/docs/optimize/asynchronous-inserts

It’s recommended in the docs over the Buffer table, and is pretty much invisible to the end user.

At ClickHouse Inc itself, this scaled far beyond millions of rows per second: https://clickhouse.com/blog/building-a-logging-platform-with...

olavgg• 9 months ago

The biggest reason is that you may also have other consumers than just Clickhouse.

bonobocop3• 9 months ago

Sure, but the article doesn’t talk about that, it seemed to be focused on CH alone, in which case async insert is much fewer technical tokens.

If you need to ensure that you have super durable writes, you can consider, but I really think it’s not something you need to reach for at first glance

oatsandsugar3• 9 months ago

Author here: commented here about how you can use async inserts if that's your preferred ingest method (we recommend that for batch).

https://news.ycombinator.com/item?id=45651098

One of the reasons we streaming ingests is because we often modify the schema of the data in stream. Usually to conform w ClickHouse best practices that aren't adhered to in the source data (restrictive types, denormalization, default not nullable, etc).

Callicles• 9 months ago

Hey,

We went from the get go to that infrastructure for multiple reasons in the first place:

* Having a durable buffer before ensures if you have big spikes that gets eaten by the buffer, not OLAP which when it is powering your online dashboard you want to keep responsive. Clickhouse cloud now has compute/compute that addresses that but open source users' don't.

* When we shipped this for the first time, clickhouse did not have the async buffering in place, so not doing some kind of buffered inserts was forwned upon. * As oatsandsugar mentioned, since them we also shipped direct insert where you don't need a kafka buffer if you don't want it

* From an architecture standpoint, with that architecture you can have multiple consumers

* Finally, having kafka enables having streaming function written in your favorite language vs using SQL. Definitely will be less performance to task ratio, but depending on the task might be faster to setup or even you can do things you couldn't directly in the database.

Disclaimer I am the CTO at Fiveonefour

hodgesrm• 9 months ago

Clickhouse cloud now has compute/compute that addresses that but open source users' don't.

Altinity is addressing this with Project Antalya builds. We have extended open source ClickHouse with stateless swarm clusters to scale queries on shared Iceberg tables.

Disclaimer: CEO of Altinity

maxjustus• 9 months ago

Nothing stopping an OSS user from pointing inserts at one or more write focused replicas and user facing queries at read focused replicas!

bonobocop3• 9 months ago

The durability and transformation reasons are definitely more compelling, but the article doesn’t mention those reasons.

It’s mainly focused on the insert batching which is why I was drawing attention to async_insert.

I think it’s worth highlighting the incremental transformation that CH can do via the materialised views too. That can often replace the need for a full blown streaming transformation pipelines too.

IMO, I think you can get a surprising distance with “just” a ClickHouse instance these days. I’d definitely be interested in articles that talk about where that threshold is no longer met!

Optimizing writes to OLAP using buffers (ClickHouse, Redpanda, MooseStack)

Related Stories