05.clickhouse

Install ClickHouse

[!IMPORTANT]

If you use VM, set CPU type to "HOST" for this VM. (Recommended CPU is at least Intel(R) Xeon(R) CPU E2690v4)

apt-get install -y apt-transport-https ca-certificates dirmngr
apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 8919F6BD2B48D754
echo "deb https://packages.clickhouse.com/deb lts main" | tee /etc/apt/sources.list.d/clickhouse.list
apt-get update
apt-get install -y clickhouse-server clickhouse-client

[default password is: qqq]

Modify some "default" parameters

ln -sf /opt/rbt/install/clickhouse/log.xml /etc/clickhouse-server/config.d/log.xml

Known issues

If you have problems writing data to the plog table (events in the mobile app), then try running next query:

SELECT isValidJSON('{}')

If you get the error message:

DB::Exception: Couldn't allocate 2 bytes when parsing JSON: In scope SELECT isValidJSON('{}'). (CANNOT_ALLOCATE_MEMORY)

make sure you set the CPU type to "HOST" if you are using a VM.

If that doesn't help, or you are not using a VM, then the server probably has an old CPU. In this case, try disabling the simdjson library, go to /etc/clickhouse-server/users.xml and add <allow_simdjson>0</allow_simdjson> to the default profile settings, then restart the service. You can read more about it here and here.

Configure ClickHouse

We strongly recommend making some changes to the server configuration file /etc/clickhouse-server/config.xml, namely: change the logging level from "trace" to "warning" and specify TTL for the system tables "*_log". Otherwise, you will waste disk space, because by default the growth of system tables is unlimited.

By running this query in ClickHouse, you can see how much disk space the system tables are currently taking up:

SELECT table,
       formatReadableSize(sum(bytes_on_disk)) AS total_size,
       sum(rows)                              AS total_rows
FROM system.parts
WHERE database = 'system'
GROUP BY table
ORDER BY sum(bytes) DESC;

Find <level>trace</level> and set it to "warning". Also find <*_log>...</*_log> sections and specify there <ttl>event_date + INTERVAL 3 DAY</ttl>. Instead of 3 days, you can use the desired TTL. A more detailed example can be found here.

Restart ClickHouse after editing the configuration:

systemctl restart clickhouse-server

After starting, ClickHouse will recreate the system tables, and you can drop old tables with a numeric suffix. For example:

DROP TABLE system.query_log_0;
DROP TABLE system.part_log_0;

After 8 minutes the data will be deleted from the disk.

Next