ZABBIX: clickhouse details

On high rates (more then one 1k NVPS) of new items being written to history, installing clickhouse alongside with the mysql server  (and probably with any other supported OLTP database) will make a BIG deal.

Clickhouse requires virtually no CPU and disk IO for writing data to drive.

Let's start from disadvantages of clickhosue:

The biggest one - merge delay. On systems where sharding is used, data may appear on graphs only after 5-20 minutes. This is not happening when there is only one node or several nodes with the same information.

That merge delay is acceptable for analytics data, and rest of functionality like triggers are not affected.

Clickhouse uses a litle bit more CPU on data fetch. Since it marginally more then Elastics and considering CPU expenses that Elastics need on data save, this could hardly be called disadvantage.

With clickhouse, there is no need to clean history tables tono need in trends. Housekeeper won't clean data in Clickhouse (use some internal DB methods to delete old partitions)

Impact of Clickhouse is invaluable: it almost doesn't use neither CPU not disk IOPs for storing large amounts of data. Loads of 50-100k new rows per second is something that could be handled on a usual spindle SATA drive.

Installing it alongside with MySQL on the same machine leads to freeing lots of resources of MySQL (or perhaps any other OLTP database) needed for storing history data. At the same time clickhouse almost doesn't use any resources at all. I would say it this way - you will barely notice clickhouse or mysql in top or htop utilities on the system processing 20-30k NVPS of data.

So, installing clickhouse is rather simple - there is a nice Yandex documentation about it: https://clickhouse.yandex/docs/en/getting_started/ . You can use sources or there are packages for most popular Linux flavors.

Having clickhouse installed you need to create the table for storing history data. It's easy to do via clickhouse client (https://clickhouse.yandex/docs/en/interfaces/http_interface/). Launch the clickhouse-client and execute the query:

CREATE TABLE zabbix.history ( day Date,  itemid UInt32,  clock DateTime,  ns UInt32,  value Int64,  value_dbl Float64,  value_str String) ENGINE = MergeTree(day, (itemid, clock), 8192);

CREATE TABLE zabbix.history_buffer ( day Date,  itemid UInt32,  clock DateTime,  ns UInt32,  value Int64,  value_dbl Float64,  value_str String) ENGINE = Buffer(zabbix, history, 16, 30, 100, 50000, 1000000, 1000000, 10000000) ;

Which will create table and table buffer for effective data merging.

That's it. Clickhouse is ready to accept data.

Then prepare zabbix:
Apply clickhouse patch (it fixes both server and frontend) and setup config files: in zabbix_server.conf:

HistoryStorageURL=http://localhost:8123
HistoryStorageTypes=uint,dbl,str,text
HistoryStorageType=clickhouse 

HistoryStorageTableName=zabbix.history_buffer

in frontend configuration:  zabbix.conf.php

global $HISTORY;
$HISTORY['url']   = 'http://localhost:8123';
$HISTORY['types'] = ['uint','dbl','str','text'];
$HISTORY['storagetype']   = 'clickhouse'; 
$HISTORY['tablename']   = 'zabbix.history_buffer';


Then you might use sql check if data is actually coming to the clickhouse server by doing count() or selecting latest data:

select count(*) from zabbix.history_buffer;

Make sure you use history_buffer as table to see the latest data.
To make ZABBIX support clickhouse storage engine download and apply clickhouse patch or download complete sources with patches already applied.

No log file support, sorry folks, never need it.

So, thats it. Until you get really high rates and volume of data, clickhouse works just fine in default configuration.