Skip to content

Install RBT: ClickHouse

ClickHouse stores high-volume operational data used by RBT services. This step installs the database, records known recovery cases and applies the project configuration expected by the application.

This page is one step in the complete RBT installation guide.

Before you start

  • Place ClickHouse data on storage sized for the expected event volume.
  • Restrict its network listeners to trusted hosts and service networks.
  • Back up existing data and configuration before applying recovery commands.

Expected result

The ClickHouse server should be active and answer a local query before RBT services are configured to write operational data to it.

Verify this step

systemctl is-active clickhouse-server
clickhouse-client --query 'SELECT version()'

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 install -y apt-transport-https ca-certificates curl gnupg
curl -fsSL 'https://packages.clickhouse.com/rpm/stable/repodata/repomd.xml.key' | gpg --dearmor -o /usr/share/keyrings/clickhouse-keyring.gpg
ARCH=$(dpkg --print-architecture)
echo "deb [signed-by=/usr/share/keyrings/clickhouse-keyring.gpg arch=${ARCH}] https://packages.clickhouse.com/deb stable main" | tee /etc/apt/sources.list.d/clickhouse.list
apt update
apt install clickhouse-client clickhouse-server

source

[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