Main Tech Discovery Antistress
🇬🇧 English 🇪🇸 Español 🇩🇪 Deutsch 🇫🇷 Français 🇵🇱 Polski 🇨🇿 Česky 🇨🇳 中文 🇰🇷 한국어
Back to Home

ClickHouse configuration: production setup of config.xml and users.xml

Practical guide to configuring ClickHouse for a production environment. The main sections of config.xml are covered: logger with size-based rotation (10 files of 1 GB each), data and swap paths, MergeTree parameters (min_bytes_for_wide_part, parts_to_throw_insert=300). The structure of users.xml for three types of users is shown: analysts (readonly=1, memory and time limits), application (limited resources), administrator (local access). Profile settings: max_memory_usage, max_execution_time, max_rows_to_read. Quotas on the number of queries and rows read. LZ4 (speed) vs ZSTD (space saving) compression. TLS/SSL for secure access. Configuration principle via config.d/ to preserve settings during updates. Minimal security checklist: change default password, close ports via UFW/iptables, restrict networks in users.xml, enable query_log.

By Sudonull Editorial Team • Published • Updated

ClickHouse: how to set up production and not lose data
Advertisement 728x90

ClickHouse Configuration: How I Set Up Production and Didn't Shoot Myself in the Foot

The Story of One Typo: 300 GB of Logs and a Crashed Server

By default, ClickHouse writes logs to /var/log/clickhouse-server/ and never rotates them. Two weeks after launch, we found that the log file had ballooned to 300 GB and filled the entire root partition. The server crashed. Analysts couldn't run SELECT, production went down.

ClickHouse documentation is huge, but in production you really only need to change about 20 parameters. Below is my checklist from 5 years of operating clusters ranging from 1 node to 12.

1. config.xml: The Skeleton of a Production Config

The main config lives in /etc/clickhouse-server/config.xml. Here are the sections I touch in every project:

Google AdInline article slot
<clickhouse>
    <!-- Logging — add rotation to avoid repeating my mistake -->
    <logger>
        <level>information</level>
        <log>/var/log/clickhouse-server/clickhouse-server.log</log>
        <errorlog>/var/log/clickhouse-server/clickhouse-server.err.log</errorlog>
        <size>1000M</size>
        <count>10</count>
        <!-- This saves disks. 10 files of 1 GB each — size-based rotation -->
    </logger>

    <!-- Ports — standard, but I change them in production -->
    <tcp_port>9000</tcp_port>           <!-- Native client -->
    <http_port>8123</http_port>         <!-- HTTP API -->
    <interserver_http_port>9009</interserver_http_port>  <!-- Replication -->
    
    <!-- Listen interfaces — for production, DO NOT set 0.0.0.0 without a firewall -->
    <listen_host>0.0.0.0</listen_host>  <!-- All interfaces — be careful -->
    <!-- <listen_host>::</listen_host>   IPv6 -->

Why I advise against blindly using 0.0.0.0: In production with a public IP, your ClickHouse will become a target for port scanning. Better to use VPN or firewalls.

2. Data Paths: Where ClickHouse Stores Everything

<path>/var/lib/clickhouse/</path>           <!-- Main data -->
<tmp_path>/var/lib/clickhouse/tmp/</tmp_path>
<user_files_path>/var/lib/clickhouse/user_files/</user_files_path>
<format_schema_path>/var/lib/clickhouse/format_schemas/</format_schema_path>

What burned me: The system's /tmp was mounted in memory (tmpfs) with only 2 GB. ClickHouse tried to write temporary files for large GROUP BY queries there and crashed with an error. I moved tmp_path to a 50 GB disk:

<tmp_path>/data/clickhouse_tmp/</tmp_path>

3. MergeTree Settings: Preventing Disasters

I set these parameters on all production servers:

Google AdInline article slot
<merge_tree>
    <!-- A part won't merge until it accumulates 10 GB — reduces background load -->
    <min_bytes_for_wide_part>10000000000</min_bytes_for_wide_part>
    
    <!-- Maximum part size for merging (100 GB) -->
    <max_bytes_to_merge_at_max_space_in_pool>107374182400</max_bytes_to_merge_at_max_space_in_pool>
    
    <!-- If parts exceed 300, new INSERTs will be rejected -->
    <parts_to_throw_insert>300</parts_to_throw_insert>
    
    <!-- Warning at 200 parts -->
    <parts_to_delay_insert>200</parts_to_delay_insert>
    
    <!-- Maximum number of parts in a partition -->
    <max_parts_in_total>10000</max_parts_in_total>
</merge_tree>

Why parts_to_throw_insert = 300: If you have 300+ parts, SELECT slows down, and INSERTs only make things worse. Better to reject the insert and investigate.

4. users.xml: Users, Profiles, Quotas

The file /etc/clickhouse-server/users.xml is access control. I create three users:

<clickhouse>
    <!-- 1. User for analysts (read-only) -->
    <analyst>
        <password>analyst_secure_password</password>
        <networks>
            <ip>10.0.0.0/8</ip>   <!-- Internal network only -->
        </networks>
        <profile>readonly_profile</profile>
        <quota>analyst_quota</quota>
    </analyst>

    <!-- 2. Application (limited resources) -->
    <app_user>
        <password>${CLICKHOUSE_APP_PASSWORD}</password>  <!-- From environment variable -->
        <networks>
            <ip>::/0</ip>  <!-- Be careful here, better to use specific subnets -->
        </networks>
        <profile>app_profile</profile>
    </app_user>

    <!-- 3. Administrator (full access) -->
    <admin>
        <password>admin_strong_password</password>
        <networks>
            <host>localhost</host>
            <host>10.0.0.50</host>  <!-- Bastion host only -->
        </networks>
        <profile>admin_profile</profile>
    </admin>
</clickhouse>

Important nuance: The default user's password is empty by default. That's the first thing I change after installation.

Google AdInline article slot

5. Settings Profiles: Protection from Crazy Queries

Profiles are sets of constraints that save the cluster from SELECT * FROM huge_table CROSS JOIN.

<profiles>
    <!-- Analysts: can run heavy queries but with limits -->
    <readonly_profile>
        <readonly>1</readonly>   <!-- SELECT only -->
        <max_memory_usage>10000000000</max_memory_usage>  <!-- 10 GB -->
        <max_execution_time>300</max_execution_time>      <!-- 5 minutes -->
        <max_rows_to_read>50000000000</max_rows_to_read>  <!-- 50 billion rows -->
        <max_bytes_to_read>107374182400</max_bytes_to_read> <!-- 100 GB -->
        <prefer_global_in_and_join>1</prefer_global_in_and_join>
    </readonly_profile>

    <!-- Application: fast queries, small limits -->
    <app_profile>
        <readonly>0</readonly>
        <max_memory_usage>1000000000</max_memory_usage>   <!-- 1 GB -->
        <max_execution_time>10</max_execution_time>        <!-- 10 seconds -->
        <max_rows_to_read>100000000</max_rows_to_read>     <!-- 100 million -->
        <timeout_before_checking_execution_speed>0</timeout_before_checking_execution_speed>
    </app_profile>

    <!-- Admin: almost no limits (but be careful) -->
    <admin_profile>
        <readonly>0</readonly>
        <max_memory_usage>0</max_memory_usage>  <!-- no limit -->
        <max_execution_time>0</max_execution_time>
    </admin_profile>
</profiles>

Quotas — so one analyst doesn't kill everyone:

<quotas>
    <analyst_quota>
        <interval>
            <duration>3600</duration>      <!-- per hour -->
            <queries>100</queries>         <!-- no more than 100 queries -->
            <errors>0</errors>             <!-- no errors -->
            <result_rows>100000000</result_rows>  <!-- 100 million result rows -->
            <read_rows>10000000000</read_rows>    <!-- 10 billion read -->
        </interval>
    </analyst_quota>
</quotas>

6. Default Compression Settings

ClickHouse compresses data with LZ4 (fast) or ZSTD (stronger). I choose based on data type:

<compression>
    <!-- LZ4 for fresh data (speed) -->
    <case>
        <last_level>1</last_level>  <!-- Compression level -->
        <method>LZ4</method>
    </case>
    
    <!-- ZSTD for partitions older than 30 days (space savings) -->
    <case>
        <min_part_size>10737418240</min_part_size>  <!-- 10 GB -->
        <method>ZSTD</method>
        <level>3</level>
    </case>
</compression>

My choice in production: LZ4 for all data. ZSTD saves 20-30% space but loses up to 10% speed on queries.

7. TLS/SSL — If You're Not in an Isolated Network

<https_port>8443</https_port>
<tcp_port_secure>9440</tcp_port_secure>

<openSSL>
    <server>
        <certificateFile>/etc/clickhouse-server/ssl/server.crt</certificateFile>
        <privateKeyFile>/etc/clickhouse-server/ssl/server.key</privateKeyFile>
        <verificationMode>none</verificationMode>
        <caConfig>/etc/clickhouse-server/ssl/CA.pem</caConfig>
        <cacheSessions>true</cacheSessions>
        <disableProtocols>sslv2,sslv3,tlsv1,tlsv1_1</disableProtocols>
        <preferServerCiphers>true</preferServerCiphers>
    </server>
</openSSL>

What burned me: A self-signed certificate without verification is only safe inside a trusted network. For external access, use Let's Encrypt or a paid certificate.

8. config.d/ — Overrides Without Pain

Never edit config.xml directly. Put all changes in /etc/clickhouse-server/config.d/.

File config.d/memory.xml:

<clickhouse>
    <max_server_memory_usage>0.75</max_server_memory_usage>  <!-- 75% of total RAM -->
    <max_concurrent_queries>100</max_concurrent_queries>
</clickhouse>

File config.d/networks.xml:

<clickhouse>
    <listen_host>0.0.0.0</listen_host>
    <max_connections>4096</max_connections>
</clickhouse>

Advantages: When updating ClickHouse, your config.xml won't be overwritten, and the overrides remain. I learned this the hard way after losing my config during an upgrade from 22.x to 23.x.

9. Security Checklist for a Betting Platform

Before opening ClickHouse access to the outside world, I check:

✅ Change default user password

clickhouse-client --password
ALTER USER default IDENTIFIED BY 'new_strong_password';

✅ Remove or restrict default user

<!-- In users.xml, comment out the default section -->
<!-- <default>
    <password></password>
</default> -->

✅ Close ports from external access (UFW)

sudo ufw default deny incoming
sudo ufw allow from 10.0.0.0/8 to any port 9000 proto tcp  # internal network only
sudo ufw allow from 10.0.0.0/8 to any port 8123 proto tcp
sudo ufw allow ssh

✅ Enable host-level firewall

sudo iptables -A INPUT -p tcp --dport 9000 -s 10.0.0.0/8 -j ACCEPT
sudo iptables -A INPUT -p tcp --dport 9000 -j DROP

✅ Set max_memory_usage so one query doesn't kill the server

<max_memory_usage>10000000000</max_memory_usage>  <!-- 10 GB -->

✅ Disable experimental features (if not needed)

<allow_experimental_geo_types>0</allow_experimental_geo_types>
<allow_experimental_window_functions>1</allow_experimental_window_functions> <!-- 23.8+ -->

✅ Enable query_log and slow_log for monitoring

<query_log>
    <database>system</database>
    <table>query_log</table>
    <partition_by>toYYYYMM(event_date)</partition_by>
    <flush_interval_milliseconds>7500</flush_interval_milliseconds>
</query_log>

<query_thread_log>
    <database>system</database>
    <table>query_thread_log</table>
</query_thread_log>

✅ Restrict access by IP (whitelist)

<analyst>
    <networks>
        <ip>10.0.0.100</ip>   <!-- Specific analyst IP only -->
        <ip>10.0.0.101</ip>
        <ip>192.168.1.0/24</ip>
    </networks>
</analyst>

What's Next

Now your ClickHouse is configured for battle. The next article is about monitoring and backups in production.

← Previous: → Next: ReplacingMergeTree: How to Beat Duplicates in ClickHouse Without Pain

— Editorial Team

Advertisement 728x90

Read Next

Tags

#clickhouse-configuration #database-administration #clickhouse-security #production-setup #devops

Topics

Databases DevOps

FAQ

How to set up log rotation in ClickHouse to avoid disk overflow?

In the <logger> section of config.xml, add <size>1000M</size> and <count>10</count>. ClickHouse will store 10 files of 1 GB each, old ones will be automatically deleted. Without this setting, the log grows indefinitely — in production we got 300 GB.

What is parts_to_throw_insert and why configure it?

A parameter in the <merge_tree> section — if the number of parts in a table exceeds 300, ClickHouse rejects new INSERTs. This protects against degradation: 300+ parts mean SELECT is already slow, and new inserts will only worsen the situation.

How to create a read-only user in ClickHouse?

In users.xml, create a section with <profile>readonly_profile</profile>, and in the profile specify <readonly>1</readonly>. Also add limits max_memory_usage, max_execution_time, and quotas so that analysts do not bring down the server with heavy queries.

Which compression method to choose for ClickHouse — LZ4 or ZSTD?

LZ4 is faster on queries (almost no speed loss), ZSTD saves 20-30% space but is 5-10% slower. I use LZ4 for fresh data (speed is more important), and ZSTD for old partitions via conditional compression section.

How to update ClickHouse without losing user settings?

Place all changes in /etc/clickhouse-server/config.d/ and users.d/, not in the main config.xml/users.xml. During update, the package overwrites only the main files, overrides remain untouched.