We will discuss the operation of Zabbix with the TimescaleDB database as a backend. We will show how to set it up from scratch and how to migrate from PostgreSQL. We will also provide comparative performance tests of the two configurations.
HighLoad++ Siberia 2019. Hall "Tomsk". June 24, 16:00. Theses and . The next HighLoad++ conference will take place on April 6 and 7, 2020, in St. Petersburg. Details and tickets .
Andrey Gushchin (hereafter – AG): – I am a technical support engineer for ZABBIX (hereafter – "Zabbix"), a trainer. I have been working in technical support for over 6 years and have directly encountered performance issues. Today, I will talk about the performance that TimescaleDB can deliver compared to regular PostgreSQL 10. I will also provide some introductory information on how this works in general.
The main performance challenges: from data collection to data cleanup
Let’s start with the fact that there are certain performance challenges that every monitoring system faces. The first performance challenge is the rapid collection and processing of data.
A good monitoring system should promptly and timely receive all data, process it according to trigger expressions, which means processing based on certain criteria (this varies in different systems), and store it in a database for future use.
The second performance challenge is historical data storage. Storing metrics in the database and having quick and easy access to these metrics collected over a period of time. Most importantly, the data should be conveniently accessible for use in reports, graphs, triggers, threshold values, alerts, etc.
The third performance challenge is data cleanup, that is, when you reach a point where you no longer need to store certain detailed metrics that have been collected over five years (or even months or two months). Some network nodes have been removed, or certain hosts are no longer needed because they are outdated and have stopped being collected. All this needs to be cleaned up to prevent your database from growing too large. In fact, data cleanup is often a serious test for storage and heavily impacts performance.
How to solve caching problems?
I am now going to specifically talk about 'Zabbix'. In 'Zabbix', the first and second calls are resolved through caching.
Data collection and processing – we use RAM to store all this data. More details about this data will be provided now.
There is also certain caching on the database side for main queries – for graphs and other items.
Caching on the Zabbix server side: we have ConfigurationCache, ValueCache, HistoryCache, TrendsCache. What is this?
ConfigurationCache is the main cache where we store metrics, hosts, data items, triggers; everything needed for preprocessing, data collection, what hosts to collect from, and how often. All of this is stored in ConfigurationCache to avoid unnecessary database queries. After the server starts, we update this cache and periodically refresh it (depending on configuration settings).
Caching in Zabbix. Data collection
The scheme here is quite large:
The main elements in the scheme are these collectors:
These are the assembly processes themselves, various 'pollers' that are responsible for different types of collections. They gather data via ICMP, IPMI, various protocols, and transmit it all for preprocessing.
PreProcessing HistoryCache
Also, if we have calculated data items (those familiar with 'Zabbix' will know), meaning derived, aggregated data items, we pull them directly from ValueCache. I will explain how it gets filled later. All these collectors use ConfigurationCache to obtain their tasks and then pass them on for preprocessing.
Preprocessing also uses ConfigurationCache to get preprocessing steps and processes this data in various ways. Starting from version 4.2, it has been offloaded to proxies. This is very convenient because preprocessing is quite a heavy operation. If you have a very large 'Zabbix', with a significant number of data items and a high collection frequency, this greatly eases the workload.
Accordingly, after we have processed this data in some way using preprocessing, we save it in HistoryCache for further processing. This concludes the data collection phase. We move on to the main process.
History syncer operation
The main process in Zabbix (since it is a monolithic architecture) is the History syncer. This is the primary process responsible for atomic processing of each data element, meaning each value:
- it receives a value (it takes it from HistoryCache);
- it checks in the Configuration syncer: are there any triggers for calculation – it calculates them;
if there are – it creates events, escalates to generate notifications if necessary according to the configuration; - it records triggers for subsequent processing, aggregation; if you are aggregating over the last hour and so on, this value is remembered in ValueCache, so as not to query the history table; thus, ValueCache is filled with the necessary data needed for calculating triggers, computed elements, etc.;
- then the History syncer writes all the data to the database;
- the database writes them to disk – this is where the processing ends.
Databases. Caching
On the DB side, when you want to view graphs or some reports on events, there are various caches. However, I will not discuss them in this report.
For MySQL, there is Innodb_buffer_pool, and a bunch of other caches that can also be configured.
But these are the main ones:
- shared_buffers;
- effective_cache_size;
- shared_pool.
I have listed that there are specific caches for all databases that allow frequently needed data for queries to be held in RAM. They have their own technologies for this.
About database performance
Accordingly, there is a competitive environment, meaning the Zabbix server collects data and writes it. Upon restart, it also reads from history to fill ValueCache and so on. At the same time, you may have scripts and reports that use the Zabbix API, which is built on the web interface. The Zabbix API accesses the DB and retrieves the necessary data to obtain graphs, reports, or a list of events, latest problems.
A very popular solution for visualization is Grafana, which is used by our users. It can directly access both via the Zabbix API and through the DB. It also creates certain competition for data retrieval: a more refined, good DB configuration is needed to ensure fast results and testing.
History Cleanup. In Zabbix, there is a Housekeeper
The third call used in Zabbix is the history cleanup using Housekeeper. The Housekeeper adheres to all settings, meaning we specify in the data items how long to keep data (in days), how long to retain trends, and the dynamics of changes.
I haven't mentioned TrendCache, which we calculate on the fly: data comes in, we aggregate it over one hour (mostly this is numbers from the last hour), the average/minimum count and record this once an hour in the dynamics changes table (Trends). The Housekeeper runs and deletes data from the database using regular selects, which is not always efficient.
How to understand that this is inefficient? You can see the following picture on the performance graphs of internal processes:
Your History syncer is constantly busy (red graph). And the 'orange' graph that runs above it. This is the Housekeeper, which runs and waits for the database to delete all rows that it has specified.
Let's take some Item ID: we need to delete the last 5,000; of course, based on indices. But usually, the dataset is large enough—the database still reads this from the disk and brings it into the cache, and this is a very expensive operation for the database. Depending on its size, this can lead to certain performance issues.
You can disable the Housekeeper in a simple way – we have the familiar web interface. In Administration general (settings for the Housekeeper), we turn off internal housekeeping for internal history and trends. Accordingly, the Housekeeper no longer manages this:
What can be done next? You have disabled it, your graphs have aligned… What further problems might arise in this case? What could help?
Partitioning
Usually, this is configured on each relational database that I mentioned, in various ways. MySQL has its own technology. But overall, they are very similar when it comes to PostgreSQL 10 and MySQL. Of course, there are many internal differences in how everything is implemented and how it affects performance. But overall, creating a new partition often leads to certain problems as well.
Depending on your setup (how much data you generate in a day), the minimum is typically set to 1 day/partition, while for 'trends' and change dynamics, it's set to 1 month/new partition. This can change if you have a very large setup.
Let me start by discussing the sizes of setups: up to 5,000 new values per second (nvps) is considered a small setup. An average setup ranges from 5,000 to 25,000 values per second. Anything beyond that is categorized as large or very large installations, which require meticulous database configuration.
In very large installations, 1 day may not be optimal. I have personally observed MySQL partitions reaching 40 gigabytes per day (and more). This is a substantial volume of data that can lead to various issues. It needs to be reduced.
What is the purpose of partitioning?
What Partitioning offers is well known – it's the segmentation of tables. Often, this involves separate files on disk and spans of queries. It selects one partition more optimally if it falls within the usual partitioning scheme.
For 'Zabbix', particularly, it is used by range, meaning we utilize a timestamp (a standard numeric value representing time since the epoch). You set the start of the day/end of the day, which acts as the partition. Consequently, if you query data from two days ago, it retrieves it from the database faster since it only needs to load one file into cache rather than a large table.
Many databases also speed up insert operations (inserting into one child table). While I am speaking abstractly, it is also feasible. Partitioning often helps.
Elasticsearch for NoSQL
Recently, in version 3.4, we implemented a NoSQL solution. We added the ability to write to Elasticsearch. You can write specific types of data: you choose to either write numbers or certain characters; we support string text, and logs can be written to Elasticsearch... Accordingly, the web interface will also interact with Elasticsearch. This works excellently in certain scenarios, but it's currently usable.
TimescaleDB. Hypertables
For 4.4.2, we noticed one thing, like TimescaleDB. What is it? It's an extension for PostgreSQL, meaning it has a native PostgreSQL interface. Additionally, this extension allows for much more efficient handling of time-series data and supports automatic partitioning. Here's how it looks:
This is a hypertable – a concept in Timescale. It's a hyper table that you create, which contains chunks. Chunks are partitions, they are child tables, if I'm not mistaken. This is really efficient.
TimescaleDB and PostgreSQL
As the producers of TimescaleDB assure, they use a more efficient query processing algorithm, particularly for inserts, which allows for roughly constant performance as the size of the dataset being inserted increases. So, after 200 million rows, regular PostgreSQL greatly declines and loses performance literally to zero, while Timescale allows for inserts to be processed as efficiently as possible regardless of the data volume.
How to install TimescaleDB? It's simple!
It is described in the documentation - you can install it from packages for any... It depends on the official PostgreSQL packages. You can compile it manually. It just happened that I had to compile it for the database.
In Zabbix, we simply activate the extension. I think those who have used the extension in PostgreSQL... You just activate the extension, create it for the Zabbix database you use.
And the last step...
TimescaleDB. Migrating historical tables
You need to create a hypertable. There is a special function for this – Create hypertable. In it, you specify the table that is needed in this database (for which you want to create a hypertable) as the first parameter.
The field to create on, and chunk_time_interval (this is the interval of chunks (partitions that need to be used). 86,400 – this is one day.
The migrate_data parameter: if you set it to true, it moves all the current data into the pre-created chunks.
I personally used migrate_data – it takes a considerable amount of time, depending on the size of your database. I had over a terabyte – creation took more than an hour. In some cases during testing, I deleted historical data for text (history_text) and string (history_str) to avoid migrating them – they weren't actually of interest to me.
And the last update we make in our db_extension: we are installing timescaledb so that the database, and in particular our 'Zabbix', understands that there is a db_extension. It activates it and uses the correct syntax and queries to the database, employing the 'features' necessary for TimescaleDB.
Server Configuration
I used two servers. The first server is a relatively small virtual machine, 20 processors, 16 gigabytes of RAM. I set it up with PostgreSQL 10.8:
The operating system was Debian, and the file system – xfs. I made minimal configurations to use this database specifically, aside from what 'Zabbix' itself would use. The same machine also hosted the 'Zabbix' server, PostgreSQL, and load agents.
I used 50 active agents that utilize LoadableModule to quickly generate various results. They generated strings, numbers, and so on. I filled the database with a large volume of data. Initially, the configuration contained 5,000 data items per host, and approximately each data item had a trigger – to make it a real setup. Sometimes, using even more than one trigger is required.
I regulated the update interval and the load not only by using 50 agents (adding more), but also through dynamic data elements, reducing the update interval to 4 seconds.
Performance Test. PostgreSQL: 36,000 NVPs
The first launch, my initial setup was on clean PostgreSQL 10 on this hardware (35,000 values per second). Overall, as seen on the screen, data insertion takes fractions of a second – everything is fine and fast, SSD drives (200 gigabytes). The only issue is that 20 GB fills up quite quickly.
There will be quite a few such graphs ahead. This is the standard performance dashboard of the 'Zabbix' server.
The first graph – the number of values per second (blue, top left), 35,000 values in this case. This (center top) is the load of the processing units, and this (top right) is the load of the internal processes: history syncers and the housekeeper, which here (center bottom) was running for a significant time.
This chart (below center) shows the usage of ValueCache – how many hits ValueCache receives for triggers (several thousand values per second). Another important chart is the fourth one (bottom left), which shows the usage of HistoryCache, which I mentioned, serving as a buffer before insertion into the database.
Performance test. PostgreSQL: 50,000 NVPs
Next, I increased the load to 50,000 values per second on the same hardware. When loaded with the Housekeeper, 10,000 values were recorded in just 2-3 seconds with computation. This is actually shown in the following screenshot:
The Housekeeper is starting to interfere with the operation, but overall the load on the history syncers is still at about 60% (the third chart, top right). During the Housekeeper's operation, HistoryCache starts to fill up actively (bottom left). It was around half a gigabyte, filling up by 20%.
Performance test. PostgreSQL: 80,000 NVPs
I then increased to 80,000 values per second:
This amounted to about 400,000 data elements, 280,000 triggers. As you can see, the insertion load for the history syncers (there were 30 of them) was already quite high. I then adjusted various parameters: history syncers, cache… On this hardware, the load on the history syncers began to reach its maximum, almost 'in the shelf' – consequently, HistoryCache went into very high load:
During all this time, I monitored all the system parameters (how the CPU is used, RAM) and found that disk utilization was at its peak – I reached the maximum capability of this disk on this hardware, on this virtual machine. PostgreSQL started to actively drop data under such intensity, and the disk could no longer keep up with writing, reading…
I took another server, which already had 48 processors and 128 gigabytes of RAM:
I also 'tuned' it – installed 60 History syncers and achieved acceptable performance. In fact, we are not 'in the shelf', but this is probably the performance limit where something needs to be done.
Performance test. TimescaleDB: 80,000 NVPs
My main task was to use TimescaleDB. A drop is visible on each graph:
These failures are essentially data migration. Afterward, as you can see, the loading profile of the history syncers on the 'Zabbix' server changed significantly. It allows data to be inserted nearly three times faster and uses less HistoryCache — consequently, data will be delivered to you in a timely manner. Again, 80,000 values per second is a quite high rate (certainly not for 'Yandex'). Overall, this is a fairly large setup with a single server.
PostgreSQL Performance Test: 120,000 NVPs
Next, I increased the number of data elements to half a million and obtained a calculated value of 125,000 per second:
And obtained these graphs:
In principle, this is a working setup; it can operate for a sufficiently long time. However, since I only had a disk of 1.5 terabytes, I used it up in a couple of days. The most important thing is that new partitions in TimescaleDB were being created at the same time, and this had no noticeable impact on performance, unlike MySQL.
Typically, partitions are created at night, as this blocks any data insertion and interaction with tables, potentially leading to service degradation. However, this was not the case here! The main goal was to test the capabilities of TimescaleDB. The result was: 120,000 values per second.
There are also examples in the community:
A person also activated TimescaleDB, and the I/O weight load decreased on the processor; the use of internal process elements also dropped thanks to the activation of TimescaleDB. Moreover, these are standard spinning disks, i.e., a regular virtual machine on conventional disks (not SSDs)!
For smaller setups that reach disk performance limits, TimescaleDB seems to be an excellent solution. It allows continued operation until you migrate to faster hardware for the database.
I invite all of you to our events: Conference in Moscow, Summit in Riga. Use our channels — Telegram, forum, IRC. If you have any questions, come to our booth; we can discuss everything.
Audience questions
Question from the audience (hereinafter – A): – If TimescaleDB is so easy to set up and provides such a performance boost, might it be worth using it as the best practice for configuring 'Zabbix' with 'Postgres'? Are there any pitfalls or downsides to this solution? Or if I decide to set up 'Zabbix', can I safely choose 'Postgres', install 'Timescale' right away, use it, and not worry about any issues?
AG: – Yes, I would say it's a good recommendation: to use 'Postgres' right away with the TimescaleDB extension. As I mentioned, there are many positive reviews, despite this 'feature' being experimental. But tests show that it’s an excellent solution (with TimescaleDB), and I believe it will continue to develop! We are monitoring how this extension evolves and will adjust as needed.
During development, we relied on one of its known 'features': there was a way to work with chunks a bit differently. But then they removed that in the next release, and we had to stop relying on that code. I would recommend using this solution in many setups. If you’re using MySQL... For medium setups, any solution works well.
A: – In the latest charts from the community, there was a chart with 'Housekeeper':
It kept working. What does 'Housekeeper' do in the case of TimescaleDB?
AG: – I can’t say for sure right now – I’ll check the code and provide more details. It uses TimescaleDB queries not for dropping chunks, but aggregates data in some way. I’m not ready to answer this technical question just yet. We’ll clarify on the stand today or tomorrow.
A: – I have a similar question – about the performance of delete operations in 'Timescale'.
A (response from the audience): – When you delete data from a table, if you do it through delete, you need to go through the table – delete, clean up, mark everything for future vacuuming. In 'Timescale', since you have chunks, you can drop them. Essentially, you're just telling the file that’s in big data: 'Delete!'
"Timescale" simply understands that there is no more of that chunk. And since it integrates into the query planner, it catches your conditions in the select or in other operations via hooks and immediately realizes that this chunk no longer exists – "I won't go there anymore!" (data is missing). That's it! That is, scanning the table is replaced by deleting a binary file, so it’s quick.
A: – We've already touched on the topic of non-SQL. As far as I understand, "Zabbix" doesn’t really need to modify data, and all of this is like a log. Can specialized databases be used that cannot change their data but are much faster at saving, accumulating, and retrieving – like Clickhouse, for example, or something Kafka-like?.. Kafka is also a log! Is there any way to integrate them?
AG: – You can export data. We have a certain "feature" starting from version 3.4: you can write all historical files, events, and so on to files; then use some processor to send them to any other database. In fact, many people rewrite and write directly to the database. On the fly, history-syncers write all this to files, rotate these files, and so on, and you can transfer this to "Clickhouse." I can't speak to future plans, but there may continue to be support for NoSQL solutions (like "Clickhouse").
A: – So, it turns out you can completely get rid of Postgres?
AG: – Of course, the most challenging part in "Zabbix" is the historical tables, which create the most problems, along with events. In this case, if you don’t keep events for long and store the history with trends in some other fast storage, then overall I think there won’t be any problems.
A: – Can you estimate how much faster everything will work if you switch to Clickhouse, for example?
AG: – I haven't tested it. I think at least the same numbers could be achieved quite easily, given that Clickhouse has its own interface, but I can't say for sure. It's better to test. It all depends on the configuration: how many hosts you have, etc. Insertion is one thing, but you also need to retrieve that data – with Grafana or something else.
A: – So it's a level playing field, not a significant advantage for these fast databases?
AG: – I think that when we integrate, there will be more accurate tests.
A: – Where has the good old RRD gone? What led us to switch to SQL databases? Initially, all metrics were collected on RRD.
AG: – RRD might have been in a very old version of 'Zabbix'. There have always been SQL databases – the classical approach. The classical approach involves MySQL and PostgreSQL (which have existed for a very long time). We have a common interface for SQL databases and we have hardly ever used RRD.
A little advertisement 🙂
Thank you for staying with us. Do you enjoy our articles? Want to see more interesting content? Support us by placing an order or recommending us to your friends, , a unique entry-level server alternative that we have created for you: (options available with RAID1 and RAID10, up to 24 cores and up to 40GB DDR4).
Dell R730xd at half the price in the Equinix Tier IV data center in Amsterdam? Only with us in the Netherlands! Dell R420 — 2x E5-2430 2.2GHz 6C 128GB DDR3 2x960GB SSD 1Gbps 100TB — from $99! Read about how
Source: habr.com
