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 :

  1. Multiple front facing load balancers distributes traffic (http load balancing) to several servers called the traffic handler node.

  2. 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.

  3. 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.

  • This is how i am addressing the task now ,so far its working fine- Instead of using MYSQL, i am just logging the data in plain text files , which are being pulled by Filebeat service which push the data to Kafka cluster. our analytics Databse servers picks all the data from kafka using clickhouse Kafka Engine table . This is wokring smoothly and very little CPU resources then mysql trying to insert and delete data again and again Nov 23 '19 at 13:24

I don't think that your approach is wrong, but I don't have much information. Your question is clear, but this space is limited compared to the complexity of your system.

Definitely you shouldn't consider using Clickhouse for OLTP. Not only because DELETE and UPDATE are not (yet) supported, but also because this database is designed to provide good performance for analytics. It lacks more or less everything is needed to optimize an OLTP workload.

Kafka is a good idea? Maybe. But you won't have transactions, for example.

I suggest to try to optimize your MySQL environment first. Some very generic points - sorry if they sound obvious to you, but of course I can't know your skills lever:

  • Tune your configuration for speed, as far as I understand you can give up some consistency.
  • Use READ UNCOMMITTED wherever there is not a risk of conflicts. The benefits of this isolation level are greatly underestimated.
  • Instead of running many DELETEs, consider partitioning tables by time and periodically drop the oldest partition. This is much faster.
  • Consider switching to a storage engine optimized for write-intensive workloads, namely MyRocks or TokuDB.
  • Consider using multiple slaves for scaling reads.
  • Consider using multiple masters for scaling writes.
  • These servers can even be distributed geografically, if needed.
  • Don't consider Galera or InnoDB Cluster, as writes don't really scale.

Hope these hints are useful.

  • Re: not using ClickHouse because it's for analytics workloads: He's using it for reporting, which I think mostly falls within the realm of OLAP/analytics? And that's exactly what ClickHouse is designed for.
    – dbdemon
    Mar 20 '18 at 10:36
  • Thanks for your suggesstion. i will look into MyRocks and TokuDB . but still my problem is the same i need transfer data continiouly from these traffic nodes to my reporting servers. optimizing mysql i tried alot but its not feasible bcos at the moment there are over 20 traffic nodes and each node can handle only about 300-500 Hits per second with 32GB ram server . Ofcourse i can not use clickhouse here because of several reason. Mar 20 '18 at 12:59
  • @SourabhSwarnkar if what others stated is correct - you only use MySQL for aggregating data - then it's not a good use of it. The overload of features like transaction is not justified. If so, you should definitely replace it, and Kafka could be a good idea. But again, I don't think we know enough about your use case. Mar 20 '18 at 14:11
  • Yes i do not need MYSQL i need to replace it with some key-value database .When a click is made and user reach our traffic node. example xxx.com/click.php?id=12 , first the system collected the initial click details like ipaddress, user agent etc and inserted a row in database which generated a unique clickid. at the same time a ajax request is made to another page with that clickid. this ajax request if for fraud detection stuffs and then update the row with that clickid. after the fraud detection part is done then then there will be no update in the clicklogs ever. Mar 20 '18 at 14:57
  • @SourabhSwarnkar I believe you can use ClickHouse (with Kafka in the middle, if necessary) and workaround the lack of UPDATEs and DELETEs. The 2 versions of the row (with and without ajax request) can be in 2 different tables, so you'll always use INSERT. Instead of deleting old data, you can partition tables by data and periodically drop oldest partition. Dropping a partition performs better than deleting rows anyway. Mar 20 '18 at 16:09

I don't think MySQL makes a lot of sense if you're using it for data aggregating only, so yes, consider using Apache Kafka for that. Then, if you want a more fully-featured OLAP system than ClickHouse, consider e.g. MariaDB ColumnStore (more technical details here), also known as MariaDB AX, which supports bulk loading of data in addition to all the usual conveniences and flexibility of SQL, including some advanced features (like window functions and CTEs). Also note that ColumnStore has a Kafka adapter.

  • Thanks for the suggestion but i dont have any experience with kakfa yet. but i m plannign to look into it. i need to replace mysql as i understand that its not suitable for the job. i need write intensive as well as quick deletes Mar 20 '18 at 13:27
  • @SourabhSwarnkar To clarify, I meant MariaDB ColumnStore as an alternative / replacement for ClickHouse. (I've updated the answer to clarify this.)
    – dbdemon
    Mar 20 '18 at 14:32

Instead of abandoning MySQL, PARTITION the table in such a way that you can use "transportable tablespaces" to move a partition out of the table, then copy that table to the other server.

Also, use innodb_flush_log_at_trx_commit=2 to increase the INSERT capacity.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.