Self-hosted ClickHouse on a dedicated server: RAM, disks, install and access

What the server needs

ClickHouse reads compressed column parts from disk and serves hot data from the page cache, so memory and storage throughput set query speed. The figures below come from the official usage recommendations, sizing guide and install pages.

To estimate disk, the install pages say to measure average row size on a loaded sample and apply the compression you observe; clickstream data typically compresses 6-10x.

Matching memory to retained data

The sizing guide gives three memory-to-storage ratios: 1:1 for low data volumes (never below 8 GB of RAM), 1:30 to 1:50 for frequently queried, customer-facing data, and 1:100 to 1:130 for long retention or high volumes. Storage here means data on disk after compression.

The table is our arithmetic on the guide's ratios, not a figure the docs print. Profile real queries before buying the next size; the guide says the RAM you need depends on query complexity and how much data each query touches.

Cores follow the same logic. 128 GB on a 16-core processor is 8 GB per core, the guide's data-warehousing ratio; 32 GB on 8 cores is 4 GB per core, its general-purpose ratio. When memory runs short, max_bytes_before_external_group_by and max_bytes_before_external_sort let aggregations and sorts spill to disk, at a cost the guide calls significant.

Disks: NVMe, RAID and the filesystem

Fast local disk is the documented first choice: use SSD when you can, and prefer many servers with local drives over fewer servers with attached shelves. NVMe adds what SATA lacks, a PCIe link and deep command queues, which helps when many queries scan different parts at once. Our NVMe vs SATA SSD vs HDD comparison sets out the bus figures.

• RAID. The recommendations allow RAID-0 on SSD and say to always use replication for data security, whatever RAID you run. For HDD they suggest RAID-10 with the far layout, or RAID-6 past four disks, built with mdadm.
• Filesystem. ext4 with noatime is called the most reliable option; XFS works well. No compressed or encrypted filesystems: ClickHouse compresses itself and has built-in encryption.
• TRIM. Make sure fstrim runs on NVMe and SSD, usually as a systemd timer or cron job.
• Host settings. CPU governor performance, transparent huge pages madvise, vm.overcommit_memory 0 or 1, swap off.

Which of our machines fit

Our NVMe dedicated servers are whole HPE Moonshot cartridges with root, an NVMe SSD on PCIe and a 1 Gbps unmetered port. Three processor lines carry NVMe: Xeon D-1500 (8-core D-1548 or 16-core D-1587, 32 GB to 128 GB DDR4), Xeon E3-1585L v5 (4 cores, 32 GB to 64 GB) and Xeon E-2286M (8 cores, 32 GB to 64 GB, Bucharest). Drives run from 240 GB to 3.84 TB, one drive or two in RAID 1, where usable space is one drive.

• Up to about 1 TB of frequently queried data, compressed: 32 GB, the documented recommendation, already reaches the guide's 1:30 ratio on any NVMe line.
• A few terabytes of hot data: the D-1587 with 128 GB and a 3.84 TB drive sits at about 1:30, the guide's ratio for frequent access.
• More than one drive's worth: a rack server with up to 24 bays, hardware RAID and SAS SSD up to 30.72 TB. See SQL dedicated servers or price the parts in the build-to-order configurator.

Merges, page cache and query threads use every core and all the memory, and no other tenant's I/O competes for the drive. The NVMe lines go from 1 Gbps to 10 Gbps on the same server. A racked build is handed over about 30 minutes after payment approval; one built to order takes 4 to 24 hours. The machines sit in five cities on our own network, AS19624; ping each one from the locations page before choosing.

Installing from the official packages

The documentation recommends the official pre-compiled packages. On Ubuntu or Debian these are its commands (the last line adds --password, which the docs use once a default-user password is set, as the installer prompts you to do); replace stable with lts for the twice-yearly long-term releases, which are supported for a year.

sudo apt-get install -y apt-transport-https ca-certificates curl gnupg
curl -fsSL 'https://packages.clickhouse.com/rpm/lts/repodata/repomd.xml.key' | sudo 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" | sudo tee /etc/apt/sources.list.d/clickhouse.list
sudo apt-get update
sudo apt-get install -y clickhouse-server clickhouse-client
sudo service clickhouse-server start
clickhouse-client --password

AlmaLinux takes the RPM route through yum-config-manager. If you pin a version, pin every package to it.

The default user and listen_host

A fresh install creates one account, default, with broad administrative rights: it can manage users and named collections, but cannot read named-collection secrets until you enable that. In the shipped users.xml its password is empty and its allowed networks are ::/0. What keeps that safe is config.xml: with no listen_host set, the server listens on localhost only.

Run interactively, the installer asks for a default-user password and writes its SHA-256 hash to /etc/clickhouse-server/users.d/default-password.xml. If you skipped the prompt, create the same file yourself. It holds an unsalted SHA-256 hash, which a short password does not survive, so use a long random password and restrict the file so that only root and the clickhouse account can read it.

<clickhouse>
    <users>
        <default>
            <password remove='1' />
            <password_sha256_hex>REPLACE_WITH_SHA256_HEX</password_sha256_hex>
        </default>
    </users>
</clickhouse>

Get the hash with echo -n 'your-password' | sha256sum and copy the 64 hex characters, then clear that line from shell history: a password typed on a command line lands there. The installer only offers to open the network when the default user has credentials; saying yes writes a config.d/listen.xml that sets listen_host to ::, every address. Before doing that, the comment in config.xml asks for at least one safeguard: a firewall that keeps untrusted networks out, users restricted to known addresses, strong passwords with TLS-only interfaces, or read-only access for users without a password. Ports 8123 (HTTP) and 9000 (native) are plain text. Their TLS versions, 8443 and 9440, are commented out in the shipped config.xml: uncomment tcp_port_secure and https_port and install a certificate before relying on them.

For production the access-control docs have you create a SQL admin from default, then restrict default. The full GRANT ALL ON *.* ... WITH GRANT OPTION is refused until show_named_collections_secrets, shipped commented out, is enabled for default. Until then, reach the server through an SSH tunnel rather than opening a port.

Backups with BACKUP and RESTORE

A mirrored pair protects against a dead drive, not against a dropped table. ClickHouse has native BACKUP and RESTORE statements. The Disk engine needs a destination declared first, in /etc/clickhouse-server/config.d/backup_disk.xml, with that disk listed under allowed_disk and the path under allowed_path; restart the server afterwards.

1. Full backup: BACKUP TABLE db.events TO Disk('backups', 'full-1.zip').
2. Incremental: the same statement to a new file with SETTINGS base_backup = Disk('backups', 'full-1.zip'). Keep the base: every incremental needs it to restore.
3. Restore into an empty table with RESTORE TABLE db.events FROM Disk('backups', 'full-1.zip'); allow_non_empty_tables=true lifts that rule but can duplicate data.

A backup on the same machine does not survive losing the machine. Send it off the server with BACKUP ... TO S3(...), or declare an S3 disk in config.d so keys stay out of the query text, and lock that file down so only root and the service account can read it. ZIP backups also accept a password setting.

When one server is the wrong answer

Per the sizing guide, each shard should have three replicas or more, and ClickHouse does not shard automatically, so a high-availability cluster is several machines plus ClickHouse Keeper, which the install pages want on separate nodes in production. That design wants the 10 Gbit network the docs name. One NVMe cartridge suits a single analytics server whose compressed data fits on one drive and that you can rebuild from a backup.