I'm developing an ad server that is expected to handle ad impressions/billion clicks per day.
The most difficult challenge I am facing is moving data from one server to another.
Basically the flow is like this :
Multiple front facing load balancers distributes traffic (http load balancing) to several servers called the traffic handler node.
These traffic handler nodes job is to store the click logs in mysql table (data like geo , device, offer id, userid etc) and then redirect traffic to the offer landing page.
Every minute cron job runs in all traffic nodes that which transfers all the clicks logs to reporting server (server where all reports generation is done) in batches of 10000 rows per minute , and then delete the data after confirming that the data is successfully received by the reporting server. The reporting servers uses clickhouse database engine
I need to replace the mysql database engine from the traffic nodes as I'm facing a lot of issues with MySQL. Between the heavy inserts and then the heavy deletes it's getting slow. Plus, the data is being transferred via cron job so there is 2 minutes average delay.
I can't use clickhouse in these server as Yandex clickhouse do not support updates and deletes yet and the click logs are supposed to be updated many times (how many events happened on the visit)
I'm looking at kakfa but again I'm not sure how to achieve one way data transfer and then deletion of data.
Maybe my whole approach is wrong. I would be very grateful for any expert to guide in right direction.