# ClickHouse

# What is ClickHouse?

ClickHouse is a columnar database management system (DBMS) for online analytical query processing (OLAP). In a conventional, “row” DBMS, data related to a single row is physically stored side by side. In a columnar one, values from different columns are stored separately, while data from a single column is stored in a single location.

The different order of data storage is suitable for different work scenarios. With increasing load on the system, it is important to move to a structure optimized for the tasks at hand. In the context of Keitaro, this new type of database organization will increase the speed of the processes involved in sampling and the speed of report generation. The testing has shown that reports are generated 14 times faster after the migration.

# Benefits of switching to ClickHouse

  • Increased capacity for free sub_id_1-30.
  • ClickHouse allows for storing more than 255 characters in free sub_id_1-30.
  • IPv6 support in all reports.
  • Faster report generation speed.
  • Reduced disk space consumption.

# Changes in API

  • Column ip contains both IPv4 and IPv6 values.
  • Columns click_id and conversion_id contain UUID value instead of number (integer).
  • Column visitor_id is removed, but possible to use visitor_code.

# What do you need to do before switching to ClickHouse?

    1. Tracker version — make sure you have the latest stable version of the tracker checked in the admin panel profile. If you have an earlier version of the tracker, please either upgrade it yourself or contact support team for assistance.
    1. It is recommended to stop traffic in the tracker before starting the conversion process.
    1. You need to provide support team with access to the server via SSH to perform the conversion.
Why is it recommended to stop traffic?

While the conversion process is well established, it stays complex and time—consuming. The traffic that is being processed in the tracker can affect the speed of the conversion script, so it is highly recommended to temporarily pause any ongoing work.

During the conversion process, Cron jobs are stopped, and any incoming traffic will be queued in the tracker. No new clicks will be processed until the conversion is complete. While the admin panel will still function and tracker pages will open correctly, new statistics will not be updated. This will continue until Cron jobs are turned on again, at which point the queued commands will be executed in your tracker.

Although developers try to ensure a smooth conversion process, there is still a possibility of unforeseen issues arising. That is why the recommendation for stopping traffic is still in place, as it can help prevent potential problems during the conversion process. In most cases, technical support team can implement the transition without stopping traffic if requested by the user.

# What parameters does the conversion time depend on?

  • The size of your tracker's database.

On average, a database of 1 million clicks is updated in 10—30 minutes.

  • Versions and state of the environment on the server and in the tracker.

If the tracker requires additional preparation for conversion, the waiting time will be increased. There is a possibility of errors on the server during conversion, making traffic, statistics unavailable. The support team will swiftly identify and fix problems, but it will take time for diagnostics.