Solving both these problems is the best hidden feature of Vitess - Messaging. You can ack a message, do data work, and add to a destination queue all in a single transaction. You can do selective requeuing, and infinite parallelization, since it isn't sequential processing. It's easy to introspect, debug, and have metrics for since it's just MySQL. Vitess lets you shard horizontally, so it can handle any QPS, and has at YouTube. It also supports native message priority. All of that, plus your infrastructure is simplified because you don't have to maintain a separate data store from your main RDBMS. Highly recommended.
https://vitess.io/docs/reference/features/messaging/
Issues we've encountered while building a Kafka based data processing pipeline
81–90 of 96 posts
Re: Issues we've encountered while building a Kafka based data processing pipeline
#82Earlier quoted context omitted.
But can you stream data from lets say MS SQL directly into Postgres? Easiest way I found is Kafka, I would love some simple python script instead
Stream? Unless you have really hight traffic, put that in a python script while loop that regularly check for new rows, and it will be fine. If you want to get fancy, db now have pub/sub. There are use case for stream replication, but you need way more data than 99% of biz have.
You don’t want stream replication for scale, you want it for availability and durability. The number of times replication has saved my ass is too damn high.
And you want Kafka (or something like it) the moment you need two processes handling updates which again you probably want for availability reasons so a crash doesn’t stop the stream.
You also don’t catch deletes or updates with this setup. But there’s a million db to Kafka connectors that read the binlog or pretend to be a replica to handle this.
Re: Issues we've encountered while building a Kafka based data processing pipeline
#83I ran a few dozen kafka clusters at MegaCorp in a previous life. My answer to anyone who asks for kafka: Show me that you can't do what you need with a beefy Postgres.
Read 500m messages in less than an hour?
Re: Issues we've encountered while building a Kafka based data processing pipeline
#84Earlier quoted context omitted.
IIRC ~2 decades ago we were dequeueing from JMS, updating RDBMS, and then enqueuing all under the cover of JTA (Java Transaction API) for atomic ops. https://docs.oracle.com/en/middleware/fusion-middleware/12.2... Using a very broad definition of ‘noSQL’ approach that would include solutions like Kafka, the issue becomes clear: A 2PC or ‘distributed transaction manager’ approach ala JTA comes with a performance/scala…
And MySQL didn’t yet support transactions!
Actually, Innodb, which supports transactions, has been bundled with MySQL since 2001, but existed before then. It became the default storage engine in 2010.
Re: Issues we've encountered while building a Kafka based data processing pipeline
#85Earlier quoted context omitted.
Stream? Unless you have really hight traffic, put that in a python script while loop that regularly check for new rows, and it will be fine. If you want to get fancy, db now have pub/sub. There are use case for stream replication, but you need way more data than 99% of biz have.
You don’t want stream replication for scale, you want it for availability and durability. The number of times replication has saved my ass is too damn high. And you want Kafka (or something like it) the moment you need two processes handling updates which again you probably want for availability reasons so a crash doesn’t stop the stream. You also don’t catch deletes or updates with this setup. But there’s a million…
Sure, if you need high availability + replication cross db, go for Kafka, but that's the thread point: it's not a common use case.
Most people don't need high availability, and most replication use the same db which comes with tooling for that.
Re: Issues we've encountered while building a Kafka based data processing pipeline
#86I ran a few dozen kafka clusters at MegaCorp in a previous life. My answer to anyone who asks for kafka: Show me that you can't do what you need with a beefy Postgres.
I agree so much I wrote an article about that https://link.medium.com/eQqVhuvRtkb
"You really can replace Kafka with a database."
Re: Issues we've encountered while building a Kafka based data processing pipeline
#87Earlier quoted context omitted.
It's just hard to know what people mean when they say "most people don't need to do this." I was sitting wondering about a similar scale (200-1000 rps), where I've had issues with scaling rabbitmq, and have been thinking about whether kafka might help. Without context provided, you might think: "oh, here's somebody with kafka and postgres experience, saying that postgres has some other super powers I hadn't learned a…
Note superyesh was talking about 100 to 500 thousand requests per second. Your overall question stands, but the scale superyesh was talking about is very different and I am quite confident superyesh's scale is definitely in the minority.
Oops, yes, was omitting the intended "k", totally skewing the scale of infrastructure my comment was intending to describe. Very funny, ironic. Unfortunately I can no longer edit that comment.
Re: Issues we've encountered while building a Kafka based data processing pipeline
#88Earlier quoted context omitted.
It's just hard to know what people mean when they say "most people don't need to do this." I was sitting wondering about a similar scale (200-1000 rps), where I've had issues with scaling rabbitmq, and have been thinking about whether kafka might help. Without context provided, you might think: "oh, here's somebody with kafka and postgres experience, saying that postgres has some other super powers I hadn't learned a…
I’m not sure if you’re omitting the k in your numbers, or missed it in the other comment? Do you mean 100-500 and 200-1000, or 100 000-500 000 and 200 000-1 000 000?
Yes, quite ironically, I accidentally forgot the "k" in my numbers. Oops.
Re: Issues we've encountered while building a Kafka based data processing pipeline
#89Earlier quoted context omitted.
For kafka the default is round robin in each partition. A hash key can let you direct the work to particular partitions. Each partition is guaranteed ordering. Also only one consumer in a consumer group can remove an item from a partition at a time. No two consumers in a consumer group will get the same message.
It's round robin if no key specified otherwise it uses murmur2 hash of the key so the partition for a key is always deterministic. Just checking the docs it appears the round robin is no longer true after Confluent Platform 5.4. After 5.4 it looks like if no key specified the partition is assigned based on the batch being processed. > If the key is provided, the partitioner will hash the key with murmur2 algorithm an…
That is new! Guess I missed that. Thank you for the heads up. Wonder why the do not have a flag to set it to the old way, maybe they do I will have to dig through the docs. I could see some processes that could have that built in as a dependency and now that would change.
Re: Issues we've encountered while building a Kafka based data processing pipeline
#90Earlier quoted context omitted.
I have done both patterns. Kafka has its use case. Databases have theirs. You can make a DB do what kafka does. But you also add in the programming overhead of getting the DB semantics correct to make an event system. When I see people saying 'lets put the DB into kafka' I make the exact same argument. You will spend more time making kafka act like a database and getting the semantics right. Kafka is more of a data/e…
Well, if you want events, you have lighter alternative. Redis pub/sub, crossbar... Even rabbitMQ is lighter. If you need queuing, you have libs like celery. You don't need to go full kafka
I do not disagree. Kafka is kind of a monster to fully configure correctly. Once they get rid of zookeeper it may be nicer to spin up and 'set it and forget it' sort of thing like the others.