ClickHouse is a column-oriented DBMS for analytical queries: counting events, parsing logs, aggregating metrics over long periods. The ZevsHost knowledge base has no article about it yet, even though "where to put logs and events so they do not load the main MySQL database" is a common question. This article covers when ClickHouse is worth running next to MySQL on one VDS, and what not to expect from it.
When ClickHouse is needed, and when MySQL is enough
ClickHouse wins where you need to scan millions of rows and compute an aggregate — a sum, a count of unique visitors over a month, a breakdown by hour. Column storage reads only the needed columns instead of the whole row, so such queries run orders of magnitude faster than on a regular row-based InnoDB table holding the same amount of data.
If a site only ever needs a lookup like "the last 50 orders of a customer" or "a product card by id", ClickHouse is not needed — for point lookups by primary key and for rows with frequent single-row updates, MySQL stays the right choice; the difference between relational engines is covered in more detail in the article MySQL or PostgreSQL: how to choose a DBMS.
How ClickHouse stores data
Tables in ClickHouse are created with an engine from the MergeTree family — it is the one responsible for sorting data by key and for background merging of small parts into larger ones.
CREATE TABLE events
(
event_date Date,
event_time DateTime,
user_id UInt64,
event_type String
)
ENGINE = MergeTree()
ORDER BY (event_date, user_id);Data physically sits sorted by the ORDER BY key rather than by insertion order, so a query filtering by a date range reads only the needed parts from disk instead of the whole table.
How to move data from MySQL
Direct row-by-row INSERT statements from a PHP script work, but they create many small parts of data in ClickHouse that are then merged in the background and load the disk harder than the insert itself did.
- Insert data in batches: send from 1,000 to 100,000 rows per insert — the larger the batch, the fewer parts the background merge has to combine, and the exact number depends on row size and insert frequency.
- Use the MySQL table engine or a dictionary with a MySQL source to read lookup tables without duplicating data on both sides.
- For event streams, accumulate events in a file or a queue and import them in batches on a schedule, instead of writing to ClickHouse directly from every user request.
- For updating and deleting rows, use mutations (ALTER TABLE ... UPDATE/DELETE) and keep in mind that they run asynchronously in the background, not instantly like an UPDATE in InnoDB.
- Turn off single-row inserts from the web application in production — even rare direct INSERT statements from a request handler create load disproportionate to the amount of data.
ClickHouse does not replace a transactional database — it has no multi-table transactions or foreign keys like MySQL and PostgreSQL do; the principles of choosing a relational engine for transactional load are covered in the article about setting up PostgreSQL on a VPS.
Resources needed on a VDS
ClickHouse actively uses RAM for the uncompressed block cache and for intermediate structures during aggregation, and it constantly writes to disk during background part merges — on a slow network disk this creates noticeable I/O load even with no user queries at all.
| Parameter | InnoDB (MySQL) | MergeTree (ClickHouse) |
|---|---|---|
| Row storage | By row, the whole row at once | By column, each column separately |
| Strong point | Point lookups and frequent row updates | Aggregates over millions of rows |
| Row update | Instant, inside a transaction | Asynchronous mutation, no transactions |
| Disk load | Random reads and writes | Sequential reads, background merges |
How much memory and disk space ClickHouse needs on a given VDS depends on the volume of historical data and on how many columns a typical query touches — plain VDS resource monitoring set up for a separate partition under the database works fine for tracking free space and disk load.
How to watch ClickHouse on the server
The DBMS itself writes its own log into system tables — system.query_log stores the text and execution time of every query, and system.merges shows which parts are being merged in the background right now.
If background merges fall behind the stream of inserts, the number of parts in a table grows, and new read queries get slower — that is the first sign that the insert batch size needs to be increased, or that ClickHouse needs to move to a disk with a higher write speed.
Frequently asked questions
Can ClickHouse and MySQL run on one VDS?
Yes, given enough RAM for both processes this is a common setup: MySQL serves the site's transactional load, while ClickHouse handles analytics and logs through a separate read process. It is worth splitting them across different disks, or at least partitions, if disk load from ClickHouse merges interferes with MySQL.
Is ClickHouse suitable for storing an online store's orders?
No, orders need transactions, foreign keys, and instant point updates to status — that is a job for MySQL or PostgreSQL. ClickHouse is suitable for an analytics view over those same orders: orders per hour, average order value by category, but not for the record itself, which should stay in the relational database.
Is ClickHouse needed if there are not many logs or events?
If log volume is measured in thousands of records a day, a regular InnoDB table with the right indexes handles it without trouble, and deploying a separate analytical DBMS makes no sense — ClickHouse earns its place once the count reaches millions of rows and aggregate queries against InnoDB start taking noticeably longer than point lookups.