ClickHouse + Graphite: How to Significantly Reduce Disk Space Usage

Greetings, habr.

If someone is running the system graphite-web and has encountered storage performance issues whisper (IO, disk space consumption), then the chance that ClickHouse has been considered as a replacement should approach one. This statement implies that a third-party implementation is already in use as the metrics daemon, for example carbonwriter or go-carbon.

ClickHouse effectively addresses the described problems. For instance, after transferring 2TiB of data from whisper, it fits into 300GiB. I won’t dwell too much on the comparison; there are plenty of articles on this topic. Furthermore, until recently, our ClickHouse storage had its own issues.

Issues with Disk Space Usage

At first glance, everything should work well. Following the documentation, we create a configuration for the metric storage schema (hereafter retention), then create a table according to the recommendations of the chosen backend for graphite-web: carbon-clickhouse+graphite-clickhouse or graphouse, depending on what stack is used. And… a ticking time bomb is activated.

To understand which one, you need to know how inserts work and the subsequent lifecycle of data in the tables of the *MergeTree ClickHouse family (diagrams taken from the presentation by Alexey Zatelepin):

• A block of data is inserted. In our case, these are incoming metrics.
  
• Each such block is sorted according to the key ORDER BY, specified during table creation.
• After sorting, a chunk (part) of data is written to disk.
  
• The server monitors in the background to ensure that there aren't too many chunks, and initiates background merges (merge, also known as merges).
  
  
• The server stops initiating merges automatically once data stops flowing actively into the partition (partition), but the process can be started manually with the command OPTIMIZE.
• If there is only one chunk left in the partition, a regular merge command cannot be used; it is necessary to use OPTIMIZE ... FINAL

So, the first metrics are coming in. And they occupy a certain amount of space. Subsequent events may vary somewhat depending on many factors:

• The partition key can be very small (day) or quite large (several months).
• The retention configuration can accommodate several significant thresholds for data aggregation within the active partition (where metrics are recorded), or it may not.
• If there is a large amount of data, the earliest chunks, which may already be enormous due to background merges (when an optimal partitioning key is not chosen), will not merge with fresh smaller chunks themselves.

And it always ends the same way. The space occupied by metrics in ClickHouse only grows if:

• not applying OPTIMIZE ... FINAL manually or
• not inserting data into all partitions on a regular basis, ultimately to initiate a background merge sooner or later.

The second method seems the easiest to implement and thus is wrong and was tested first.
I wrote a fairly simple Python script that sent dummy metrics for each day over the past 4 years and was run every hour by cron.
Since all operations in ClickHouse DBMS are based on the system eventually doing all the background work, but it’s unclear when, I couldn't wait for the old enormous chunks to start merging with the new smaller ones. It became clear that I needed to find a way to automate forced optimizations.

Information in ClickHouse system tables

Let's look at the structure of the table system.parts. It contains comprehensive information about each chunk of all tables on the ClickHouse server. It includes, among other things, the following columns:

• database name (database);
• table name (table);
• partition name and ID (partition & partition_id);
• when the chunk was created (modification_time);
• minimum and maximum date in the chunk (partitioning is done by days) (min_date & max_date);

There is also the table system.graphite_retentions, with the following interesting fields:

• database name (Tables.database);
• table name (Tables.table);
• the age of the metric when the next aggregation should be applied (age);

So:

1. We have a table of chunks and a table of aggregation rules.
2. We combine their intersection and get all tables *GraphiteMergeTree.
3. We look for all partitions where:
   • there is more than one chunk
   • or it is time to apply the next aggregation rule, and modification_time it is older than this time.

Implementation

This query

SELECT
    concat(p.database, '.', p.table) AS table,
    p.partition_id AS partition_id,
    p.partition AS partition,
    -- The "oldest" rule that can be applied to
    -- the partition, but not in the future, see (*)
    max(g.age) AS age,
    -- The number of parts in the partition
    countDistinct(p.name) AS parts,
    -- The oldest metric in the partition is considered to be 00:00:00 of the next day
    toDateTime(max(p.max_date + 1)) AS max_time,
    -- When the partition needs to be optimized
    max_time + age AS rollup_time,
    -- When the oldest part in the partition was updated
    min(p.modification_time) AS modified_at
FROM system.parts AS p
INNER JOIN
(
    -- All rules for all tables *GraphiteMergeTree
    SELECT
        Tables.database AS database,
        Tables.table AS table,
        age
    FROM system.graphite_retentions
    ARRAY JOIN Tables
    GROUP BY
        database,
        table,
        age
) AS g ON
    (p.table = g.table)
    AND (p.database = g.database)
WHERE
    -- Only active parts
    p.active
    -- (*) And only rows where aggregation rules should already be applied
    AND ((toDateTime(p.max_date + 1) + g.age) < now())
GROUP BY
    table,
    partition
HAVING
    -- Only partitions that are younger than the optimization moment
    (modified_at  1)
ORDER BY
    table ASC,
    partition ASC,
    age ASC

returns each of the partitions of the *GraphiteMergeTree tables, the merging of which should free up disk space. Now, all that remains is to go through all of them with a query OPTIMIZE ... FINAL. The final implementation also takes into account that there is no need to touch partitions with active writes.

This is exactly what the project graphite-ch-optimizer. Former colleagues from Yandex.Market tested it in production, and you can see the result of the work below.

If you run the program on a server with ClickHouse, it will simply start working in daemon mode. Once an hour, it will execute a query to check if any new partitions older than three days have appeared that can be optimized.

In the near future, we plan to provide at least deb packages, and possibly also rpm.

In conclusion

Over the past 9 months, I have spent a lot of time within my company InnoGames working at the intersection of ClickHouse and graphite-web. It was a good experience, resulting in a possible quick transition from whisper to ClickHouse as a metrics storage. I hope this article serves as some kind of beginning of a cycle about the improvements we have made in various parts of this stack and what will be done in the future.

The development of the request consumed several liters of beer and admin-days together with v0devil, for which I want to express my gratitude. Also for reviewing this article.

Project page on GitHub

Source: habr.com