Every monitoring system has to deal with three kinds of performance-related challenges.

Firstly, a good monitoring system must receive, process and record incoming data very quickly. Every microsecond counts here. This may not seem obvious from the start but when your system becomes large enough all the tiny fractions of seconds add up to become seconds if not minutes.

The second challenge is to provide convenient access to the large arrays of previously collected metrics (aka historical data). Historical data may be used in many contexts, such as reports, charts, aggregate checks, triggers and calculated items. If there are any performance bottlenecks while accessing the history these quickly become obvious in virtually every part of the system.

Thirdly, the historical data is big. Even very modest monitoring configurations can accumulate large amounts of historical data very quickly. Apparently, you don’t need every value of every item from five years ago to be always at your fingertips so you need to clean up your history from time to time (this process is called housekeeping in Zabbix). Even though the deletion of data doesn’t have to be as performant as the new data collection, analysis and alerting, large delete operations take up precious database performance resources and can block other real-time activities.

The first two challenges can be addressed by employing caching techniques. Zabbix maintains several highly specialized caching areas in memory to speed up data read and write operations. And just in case if you wonder why not use caching provided by the most modern database engines – indeed, they are pretty good at utilizing their own general purpose caches, but they, naturally, are not aware of what data structures are more important for the monitoring server.

Monitoring and Time Series Data

It’s all good while data stays in Zabbix server memory but it needs to be written to the database (or read from) at some point. However good and smart the caching algorithms are these are not of any help if the database performance seriously lags behind the rates at which the metrics are collected.

The third problem also boils down to the database performance. There must be some reliable deletion strategy which would not interfere with other database operations. Zabbix deletes its historical data in small batches of a few thousand records on an hourly basis. You can configure longer housekeeping periods or different batch sizes if your data rates do not require regular cleanups this often, though. But if you collect a lot of metrics from a lot of sources housekeeping can be tricky because your data removal schedule may not keep up with the pace at which the new data is written.

So, at the highest level, there are three main aspects (all mentioned above) of a monitoring system – new data collection with associated SQL insert operations, data access with associated SQL select queries and data removal with SQL delete operations. Let’s have a look at how a typical SQL query is executed:

The database engine parses the query and checks it for syntax errors. If the query is OK the engine builds a syntax tree suitable for further processing.

A query planner analyzes the syntax tree and comes up with multiple ways (paths) to execute the query.

A query planner analyzes the syntax tree and comes up with multiple ways (paths) to execute the query. The planner figures out which path is less taxing. The planner has a lot of things to consider – how big are the tables, whether the result must be sorted or not, what are the indices and if these can be of any help, just to name a few.

When the path is defined the engine executes the query by accessing all necessary blocks of data (either by using indices or by scanning blocks sequentially), applies sorting and filtering criteria, assembles the result and returns it to the client.

For the insert, update and delete statements the engine must also update the indices for corresponding tables. For big tables, this is an expensive operation which can take up way more time than the actual work on data itself.

The engine may also update internal data usage statistics for further use by the query planner.

There is a lot of work’s going on here! Most DB engines give you a lot of knobs and switches to play with if you want to optimize query performance in your database, but these are typically geared towards some average workflows, where the insert and delete statements are as frequent as update ones.

However, as mentioned above, data in a monitoring system is frequently inserted, then accessed in an aggregated fashion most of the times (to show charts or to calculate aggregate items, for example), periodically removed and almost never updated. Moreover, the values of a typically monitored metric are ordered by time. Such data is typically referred to as Time Series data:

A time series is a series of data points indexed (or listed or graphed) in time order.

From the database point of view there are the following qualities of time series data:

Time series data can be laid out on a disk as a sequence of blocks ordered by time.

Time series data will always have at least one index by some column representing time.

Most SQL select queries will use WHERE, GROUP BY or ORDER BY clauses with time column.

Usually, time series data is a subject of retention policies and is often deleted in bulk rather than by individual records.

Obviously, a traditional SQL database is no good fit for storing such data because general-purpose optimizations do not take these qualities into account. So, unsurprisingly, quite a few new time series-oriented databses appeared in the recent years, such as InfluxDB. But there is a small problem with all popular time series databases. Neither of them supports SQL (some are officially noSQL) in full and most even is not CRUD (Create, Read, Update, Delete).

Can Zabbix benefit from these databases somehow? One possible approach is to outsource history storage altogether. In fact, Zabbix’s architecture does support external storage back-ends for historical data. But everything comes at a cost and if we supported one or more time series databases as an external storage back-end users would have to deal with the following:

One more system to learn, configure and maintain – with its own settings, storage, retention policies, performance tuning and troubleshooting.

One more point of failure. You would probably monitor it with Zabbix and get notified about the problems quickly. But you might lose history for all your infrastructure if anything went wrong with the database.

For some users, the advantages of having a dedicated time series storage may outweigh the inconvenience of worrying about one more system. But for some, this might be a show stopper. Yet another thing to keep in mind – since most of such time series solutions have their own unique APIs the complexity of Zabbix database layer would grow considerably. We prefer building new features rather than fighting foreign APIs!

So is there a way to take advantage of time series databases without losing the flexibility of SQL? Unsurprisingly, there is no one-size-fits-all answer but one specific time series solution comes pretty close – TimescaleDB.

What is TimescaleDB? TimescaleDB (TSDB) is a PostgreSQL extension, which adds time series based performance and data management optimizations to a regular PostgreSQL (PG) database. While there is no shortage of scalable time series solutions the best part of TimescaleDB is time series-awareness on top of conventional SQL database. In practice, this means that you get the best of both worlds. The database knows which tables must be treated as time series data (with all needed optimizations in place) but you can continue to use SQLs for both time series and regular database tables. Applications even don’t need to know there is a TimescaleDB extension underneath a conventional SQL interface! To mark the table as a time series table (called hypertable) you just call TSDB’s procedure create_hypertable(). Under the hood TSDB splits this table into so-called chunks in accordance with conditions you specify. You can think of chunks as of automatically managed table partitions. Every chunk has an associated time range. For every chunk TSDB also sets up dedicated indices so the application can work with data ranges without touching the rows and indices belonging to other ranges. When an application inserts a time series value the engine routes this value to a proper chunk. If it doesn’t find a chunk for this range then a new chunk is automatically created. If the application queries the hypertable the engine checks what chunks are affected by this query before actually executing it. This is not all though. There are a lot of performance and scalability related changes TSDB brings to a solid and time-tested PostgreSQL ecosystem. These include very fast inserts (vanilla PG is fast, but there is a considerable performance degradation when you hit hundreds of millions of record territory), fast time-based queries and almost free bulk deletes. As previously noted, in order to control the database size and comply with data retention policies a good monitoring solution needs to delete a lot of historical values. With TSDB we can delete historical data by just removing specific chunks from a hypertable. Even more, the application doesn’t need to track chunks by names or any other references. TSDB can delete all affected chunks just by specific time range. TimescaleDB vs PostgreSQL Partitions At first glance, TSDB may look like an over-engineered way to create PostgreSQL partitioned tables (declarative partitioning, as it’s called officially in PG10). Indeed, you may take advantage of native PG10 range partitioning for tables with historical data. But if you look closer, TSDB’s chunks and native range partitions are quite different concepts. For starters, PG partitions are not easy to set up. First, you need to figure partition hierarchy and decide whether to use sub-partitions. Second, you need to invent the partition naming scheme and code it into your schema creation scripts. Big chances are your naming scheme will include some sort of reference to dates and/or times and this means you need to script your names somehow. Then, there are data retention policies. In TSDB you can just issue drop_chunks() command which figures what chunks must be deleted for a given time range. If you need to remove a specific range of values from native PG partitions you need to calculate range-to-names relationships yourself. Another problem you would need to solve is what to do with out-of-order data that doesn’t fit into currently defined time ranges. By default in PG10 such insert operation would just fail. In PG11 you can define a catch-all partition for such data but this would just mask the problem temporarily rather than solve it. But these are not the obstacles a good system administrator can’t overcome. Bash, Python, cron jobs or database triggers can do wonders here. The real problem is, again, performance. No doubts, native partitions are better than plain monolithic tables but these still suffer from performance degradation if you have a lot of them. What is good about hyper-tables is that these are not only designed to accommodate time-series workloads by automating labor-intensive system administration procedures but also optimized to work with chunks in an isolated way. For example, chunks require less locking and are easier on memory when adding new data whereas every insert operation into a native partition opens all other partitions and indices. If you are interested in more technical details and comparison between TSDB chunks and PG partitions, please read this blog post.