4 ms·

Scaling request logging with ClickHouse, Kafka, and Vector

136 points by mjwhansen 1y ago

rozenmd 1y ago
Great write-up! I had a similar project back in August when I realised my DB's performance (Postgres) was blocking me from implementing features users commonly ask for (querying out to 30 days of historical uptime data). I was already blown away at the performance (200ms to query what Postgres was doing in 500-600ms), but then I realized I hadn't put an index on the Clickhouse table. Now the query returns in 50-70ms, and that includes network time.
- fermuch 1y ago
  Materialized views are a great tool for aggregating data in CH since they are automatically updated on insert from the original table. I recommend you to take a look and try it out, maybe it'll go down to single digit milliseconds!
  - ansgri 1y ago
    And there are 2 kinds of those: the other is refreshable materialized views, which run on schedule, can have dependencies between them, thus can implement quite complex data transformation pipelines.
nasretdinov 1y ago
BTW you could've used e.g. kittenhouse (https://github.com/YuriyNasretdinov/kittenhouse https://github.com/YuriyNasretdinov/kittenhouse, my fork) or just a simpler buffer table, with 2 layers and a larger aggregation period than in the example. Alternatively, you could've used async insert functionality built into ClickHouse: https://clickhouse.com/docs/optimize/asynchronous-inserts https://clickhouse.com/docs/optimize/asynchronous-inserts . All of these solutions are operationally simpler than Kafka + Vector, although obviously it's all tradeoffs.
- devmor 1y ago
  There were a lot of simpler options that came to mind while reading through this, frankly. But I imagine the writeup eschews myriad future concerns and does not entirely illustrate the pressure and stress of trying to solve such a high-scale problem. Ultimately, going with a somewhat more complex solution that involves additional architecture but has been tried and tested by a 3rd party that you trust can sometimes be the more fitting end result. Assurance often weighs more than simplicity, I think.
  - nasretdinov 1y ago
    While kittenhouse is, unfortunately, abandonware (even though you can still use it and it works), you can't say the same about e.g. async inserts in ClickHouse: it's a very simple and robust solution to tackle exactly the problem the PHP (and some other languages') backends often face when trying to use ClickHouse
  - ajayvk 1y ago
    Yes, had similar questions. Wouldn't tuning the settings for the buffer table have helped avoid the TOO_MANY_LINKS error?
frenchmajesty 1y ago
Thanks for sharing I enjoyed reading this.
tlaverdure 1y ago
Thanks for sharing. I really enjoyed the breakdown, and great to see small tech companies helping each other out!
mperham 1y ago
Seems weird not to use Redis as the buffering layer + minutely cron job. Seems a lot simpler than installing Kafka + Vector.
- SteveNuts 1y ago
  Vector is very simple to operate and (mostly) stateless, and can handle buffering if you choose. Kafka and Redis is a "pick your poison" IMO, scaling and operating those have their own headaches.
- otterley 1y ago
  Redis isn’t a good durable message queue.
albertgoeswoof 1y ago
Currently at the millions stage with https://mailpace.com https://mailpace.com relying mostly on Postgres Tbh this terrifies me! We don’t just have to log the requests but also store the full emails for a few days, and they can be up to 50 mib in total size. But it will be exciting when we get there!
fnord77 1y ago
How does Clickhouse compare to Druid, Pinot or Star Tree?
- jamesblonde 1y ago
  Here's a good performance study by OneHouse comparing Clickhouse, StarRocks, Trino: https://www.onehouse.ai/blog/apache-spark-vs-clickhouse-vs-presto-vs-starrocks-vs-trino-comparing-analytics-engines https://www.onehouse.ai/blog/apache-spark-vs-clickhouse-vs-p... Druid is real-time analytics, similar to Clickhouse. StarRocks is best at Joins - Clickhouse is not good for joins.
  - manish_gill 1y ago
    > Clickhouse is not good for joins This is less and less true as time goes on tbh. 25.9 introduced Join Reordering as well - https://clickhouse.com/blog/clickhouse-release-25-09 https://clickhouse.com/blog/clickhouse-release-25-09
saisrirampur 1y ago
Sai from ClickHouse here. Very compelling story! Really love your emphasis on using the right tool for the right job - power of row vs column stores. We recently added a MySQL/MariaDB CDC connector in ClickPipes on ClickHouse Cloud. This would have simplified your migration from MariaDB. https://clickhouse.com/docs/integrations/clickpipes/mysql https://clickhouse.com/docs/integrations/clickpipes/mysql https://clickhouse.com/docs/integrations/clickpipes/mysql/source/rds_maria https://clickhouse.com/docs/integrations/clickpipes/mysql/so...
ch2026 1y ago
1) clickhouse async_insert would have solved all your issues: https://clickhouse.com/docs/optimize/asynchronous-inserts https://clickhouse.com/docs/optimize/asynchronous-inserts 1a) If you’re still having too many files/parts, then fix your partition by, and mergetree primary key. 2) why are you writing to kafka when vector dev does buffering / batching? 3) if you insist on kafka, https://clickhouse.com/docs/engines/table-engines/integrations/kafka https://clickhouse.com/docs/engines/table-engines/integratio... consumes directly from kafka (or since you’re on CHC, use clickhouse pipes) — what’s the point of vector here? Your current solution is unnecessarily complex. I’m guessing the core problem is your merge tree primary key is wrong.
- momothereal 1y ago
  Writing to Kafka allowed them to continue their current ingestion process into MariaDB at the same time as ClickHouse. Kafka consumer groups allow the data to be consumed twice by different consumer pools that have different throughput without introducing bottlenecks. From experience the Kafka tables in ClickHouse are not stable at a high volumes, and harder to debug when things go sideways. It is also easier to mutate your data before ingestion using Vector's VRL scripting language vs. ClickHouse table views (SQL) when dealing with complex data that needs to be denormalized into a flat table.
  - ch2026 1y ago
    > Writing to Kafka allowed them to continue their current ingestion process into MariaDB at the same time as ClickHouse. The one they're going to shut down as soon as this works? Yeah, great reason to make a permanent tech choice for a temporary need. Versus just keeping the MariaDB stuff exactly the same on the PHP side and writing to 2 destinations until cutover is achieved. Kafka is wholly unnecessary here. Vector is great tech but likely not needed. Kafka + Vector is absolutely the incorrect solution. Their core problem is the destination table schema (which they did not provide) and a very poorly chosen primary key + partition.
est 1y ago
can you just buffer some writes in Vector and eliminate Kafka? I setup some Vector to buffer ElasticSearch writes years ago, also for logs, it ran so well without any problems that I almost fogot about it.
- anticodon 1y ago
  Or vice versa: make ClickHouse ingest batches directly from Kafka. Messages are already buffered in Kafka, I don't get why Vector is necessary here.
  - ch2026 1y ago
    tbh the only thing they needed was a correct schema that didn’t constantly spawn new parts and async_insert enabled.
  - est 1y ago
    https://clickhouse.com/docs/knowledgebase/kafka-to-clickhouse-setup https://clickhouse.com/docs/knowledgebase/kafka-to-clickhous... For anyone if curious.
pachico 1y ago
I shared this article internally and my peers were impressed about how similar it is to our final implementation. (It differs in the fact that we use Redis as queue.) Happy to exchange notes about our journey too. Cheers
solatic 1y ago
Geocodio offers a pay-as-you-go metered plan where users get 2,500 free geocoding lookups per day. This means we need to: Track the 2,500 free tier requests Continue tracking above that threshold for billing Let users view their usage in real-time on their dashboard Give admins the ability to query this data for support and debugging Store request details so we can replay customer requests when debugging issues Just on the basis of what you wrote here, I'm not convinced ClickHouse is the right tool. ClickHouse very much would help with helping you crunch statistics for latencies etc., but just for billing and getting individual query data? 1) push the request to Kafka/Pub Sub/etc. 2) one consumer pushing to TigerBeetle for tracking request usage within the free tier and other billing 3) one consumer to push individual requests to object storage, which scales out infinitely-ish, allows you to get full request details for an individual request, lifecycle rules will automatically async delete old requests for you. If request statistics is important for business analysis, then instead of (boring) object storage you could look at one of the newer Iceberg-based options on top of object storage, e.g. S3 tables; as long as querying an individual request remains fast and getting statistics can be generated, say, for a nightly report. Another cheap approach could hook up another consumer to the PubSub, any request with too-high latency above a reasonable threshold, dump it into a Slack channel with a reference to the request ID so someone can look into debugging it.
matthewaveryusa 1y ago
I shimmed vector in my log pipeline recently and it really is a wonderfully simple and powerful tool. It's where I transform logs of software I don't own in to prometheus metrics and drop useless logs from making it to loki.
enether 11mo ago
weird you have to adopt Kafka AND Vector just to batch a bit of writes into Clickhouse...

136 points by mjwhansen 1y ago

Great write-up! I had a similar project back in August when I realised my DB's performance (Postgres) was blocking me from implementing features users commonly ask for (querying out to 30 days of historical uptime data). I was already blown away at the performance (200ms to query what Postgres was doing in 500-600ms), but then I realized I hadn't put an index on the Clickhouse table. Now the query returns in 50-70ms, and that includes network time.

fermuch 1y ago
Materialized views are a great tool for aggregating data in CH since they are automatically updated on insert from the original table. I recommend you to take a look and try it out, maybe it'll go down to single digit milliseconds!
- ansgri 1y ago
  And there are 2 kinds of those: the other is refreshable materialized views, which run on schedule, can have dependencies between them, thus can implement quite complex data transformation pipelines.

nasretdinov 1y ago

BTW you could've used e.g. kittenhouse (https://github.com/YuriyNasretdinov/kittenhouse https://github.com/YuriyNasretdinov/kittenhouse, my fork) or just a simpler buffer table, with 2 layers and a larger aggregation period than in the example. Alternatively, you could've used async insert functionality built into ClickHouse: https://clickhouse.com/docs/optimize/asynchronous-inserts https://clickhouse.com/docs/optimize/asynchronous-inserts . All of these solutions are operationally simpler than Kafka + Vector, although obviously it's all tradeoffs.

devmor 1y ago
There were a lot of simpler options that came to mind while reading through this, frankly. But I imagine the writeup eschews myriad future concerns and does not entirely illustrate the pressure and stress of trying to solve such a high-scale problem. Ultimately, going with a somewhat more complex solution that involves additional architecture but has been tried and tested by a 3rd party that you trust can sometimes be the more fitting end result. Assurance often weighs more than simplicity, I think.
- nasretdinov 1y ago
  While kittenhouse is, unfortunately, abandonware (even though you can still use it and it works), you can't say the same about e.g. async inserts in ClickHouse: it's a very simple and robust solution to tackle exactly the problem the PHP (and some other languages') backends often face when trying to use ClickHouse
- ajayvk 1y ago
  Yes, had similar questions. Wouldn't tuning the settings for the buffer table have helped avoid the TOO_MANY_LINKS error?

frenchmajesty 1y ago

Thanks for sharing I enjoyed reading this.

tlaverdure 1y ago

Thanks for sharing. I really enjoyed the breakdown, and great to see small tech companies helping each other out!

mperham 1y ago

Seems weird not to use Redis as the buffering layer + minutely cron job. Seems a lot simpler than installing Kafka + Vector.

SteveNuts 1y ago
Vector is very simple to operate and (mostly) stateless, and can handle buffering if you choose. Kafka and Redis is a "pick your poison" IMO, scaling and operating those have their own headaches.
otterley 1y ago
Redis isn’t a good durable message queue.

albertgoeswoof 1y ago

Currently at the millions stage with https://mailpace.com https://mailpace.com relying mostly on Postgres Tbh this terrifies me! We don’t just have to log the requests but also store the full emails for a few days, and they can be up to 50 mib in total size. But it will be exciting when we get there!

fnord77 1y ago

How does Clickhouse compare to Druid, Pinot or Star Tree?

jamesblonde 1y ago
Here's a good performance study by OneHouse comparing Clickhouse, StarRocks, Trino: https://www.onehouse.ai/blog/apache-spark-vs-clickhouse-vs-presto-vs-starrocks-vs-trino-comparing-analytics-engines https://www.onehouse.ai/blog/apache-spark-vs-clickhouse-vs-p... Druid is real-time analytics, similar to Clickhouse. StarRocks is best at Joins - Clickhouse is not good for joins.
- manish_gill 1y ago
  > Clickhouse is not good for joins This is less and less true as time goes on tbh. 25.9 introduced Join Reordering as well - https://clickhouse.com/blog/clickhouse-release-25-09 https://clickhouse.com/blog/clickhouse-release-25-09

saisrirampur 1y ago

Sai from ClickHouse here. Very compelling story! Really love your emphasis on using the right tool for the right job - power of row vs column stores. We recently added a MySQL/MariaDB CDC connector in ClickPipes on ClickHouse Cloud. This would have simplified your migration from MariaDB. https://clickhouse.com/docs/integrations/clickpipes/mysql https://clickhouse.com/docs/integrations/clickpipes/mysql https://clickhouse.com/docs/integrations/clickpipes/mysql/source/rds_maria https://clickhouse.com/docs/integrations/clickpipes/mysql/so...

ch2026 1y ago

1) clickhouse async_insert would have solved all your issues: https://clickhouse.com/docs/optimize/asynchronous-inserts https://clickhouse.com/docs/optimize/asynchronous-inserts 1a) If you’re still having too many files/parts, then fix your partition by, and mergetree primary key. 2) why are you writing to kafka when vector dev does buffering / batching? 3) if you insist on kafka, https://clickhouse.com/docs/engines/table-engines/integrations/kafka https://clickhouse.com/docs/engines/table-engines/integratio... consumes directly from kafka (or since you’re on CHC, use clickhouse pipes) — what’s the point of vector here? Your current solution is unnecessarily complex. I’m guessing the core problem is your merge tree primary key is wrong.

momothereal 1y ago
Writing to Kafka allowed them to continue their current ingestion process into MariaDB at the same time as ClickHouse. Kafka consumer groups allow the data to be consumed twice by different consumer pools that have different throughput without introducing bottlenecks. From experience the Kafka tables in ClickHouse are not stable at a high volumes, and harder to debug when things go sideways. It is also easier to mutate your data before ingestion using Vector's VRL scripting language vs. ClickHouse table views (SQL) when dealing with complex data that needs to be denormalized into a flat table.
- ch2026 1y ago
  > Writing to Kafka allowed them to continue their current ingestion process into MariaDB at the same time as ClickHouse. The one they're going to shut down as soon as this works? Yeah, great reason to make a permanent tech choice for a temporary need. Versus just keeping the MariaDB stuff exactly the same on the PHP side and writing to 2 destinations until cutover is achieved. Kafka is wholly unnecessary here. Vector is great tech but likely not needed. Kafka + Vector is absolutely the incorrect solution. Their core problem is the destination table schema (which they did not provide) and a very poorly chosen primary key + partition.

est 1y ago

can you just buffer some writes in Vector and eliminate Kafka? I setup some Vector to buffer ElasticSearch writes years ago, also for logs, it ran so well without any problems that I almost fogot about it.

anticodon 1y ago
Or vice versa: make ClickHouse ingest batches directly from Kafka. Messages are already buffered in Kafka, I don't get why Vector is necessary here.
- ch2026 1y ago
  tbh the only thing they needed was a correct schema that didn’t constantly spawn new parts and async_insert enabled.
- est 1y ago
  https://clickhouse.com/docs/knowledgebase/kafka-to-clickhouse-setup https://clickhouse.com/docs/knowledgebase/kafka-to-clickhous... For anyone if curious.

pachico 1y ago

I shared this article internally and my peers were impressed about how similar it is to our final implementation. (It differs in the fact that we use Redis as queue.) Happy to exchange notes about our journey too. Cheers

solatic 1y ago

Geocodio offers a pay-as-you-go metered plan where users get 2,500 free geocoding lookups per day. This means we need to: Track the 2,500 free tier requests Continue tracking above that threshold for billing Let users view their usage in real-time on their dashboard Give admins the ability to query this data for support and debugging Store request details so we can replay customer requests when debugging issues Just on the basis of what you wrote here, I'm not convinced ClickHouse is the right tool. ClickHouse very much would help with helping you crunch statistics for latencies etc., but just for billing and getting individual query data? 1) push the request to Kafka/Pub Sub/etc. 2) one consumer pushing to TigerBeetle for tracking request usage within the free tier and other billing 3) one consumer to push individual requests to object storage, which scales out infinitely-ish, allows you to get full request details for an individual request, lifecycle rules will automatically async delete old requests for you. If request statistics is important for business analysis, then instead of (boring) object storage you could look at one of the newer Iceberg-based options on top of object storage, e.g. S3 tables; as long as querying an individual request remains fast and getting statistics can be generated, say, for a nightly report. Another cheap approach could hook up another consumer to the PubSub, any request with too-high latency above a reasonable threshold, dump it into a Slack channel with a reference to the request ID so someone can look into debugging it.

matthewaveryusa 1y ago

I shimmed vector in my log pipeline recently and it really is a wonderfully simple and powerful tool. It's where I transform logs of software I don't own in to prometheus metrics and drop useless logs from making it to loki.

enether 11mo ago

weird you have to adopt Kafka AND Vector just to batch a bit of writes into Clickhouse...