Linux, Postgresql

Partitioning of the larger tables will also help in reducing the time it takes to restore a zabbix DB from backup in case of disasters (parallel import).

This feature will really provide GREAT performance gains in PostgreSQL query plan. Vote for it!

Maybe this thread could help... http://www.zabbix.com/forum/showthread.php?p=57018

what's preventing you from using partitioning ?
since 1.8.4 there's even a frontend define to help to work with them - see ZBX_HISTORY_DATA_UPKEEP at http://www.zabbix.com/documentation/1.8/manual/config/defines

so could this feature request said to be about alternative data storage options ?

Hmm. When I look at my schema, I see a single history table and a single items table. They are so big that I can't even get a real size – SELECT count from history; will take hours. Here's an approximation:

zabbix=# SELECT (reltuples)::integer FROM pg_class r WHERE relkind = 'r' AND relname = 'history';
reltuples
-----------
598507392
(1 row)

... I was looking for a way to break this up into several smaller tables. E.g. history_2011_01, history_2011_02. You're saying that's already supported? I couldn't find it in the documentation.

not in individual tables, but partitions - see http://dev.mysql.com/doc/refman/5.5/en/partitioning.html for mysql or relevant documentation for other databases

We're using postgresql, which doesn't have great native support for this type of partitioning. I was hoping you were telling me that Zabbix could do it itself.

ZBXNEXT-844 deals with initial cassandra support

Perhaps it would be worthwhile to consider http://graphite.wikidot.com/ as a replacement graphing backend. It's getting lots of traction, scales well, and does a pretty nice job.

PostgeSQL also supports partitioning - in fact, that's why i'm using it instead of mysql, see here: https://www.zabbix.org/wiki/Docs/howto/zabbix2_postgresql_autopartitioning

ZBXNEXT-1971 talks about opentsdb

maybe this is an option?

ZBXNEXT-1847

My idea is to store latest data in zabbix (for the trigger functionality), but all historical data store in graphite.

ZBXNEXT-2176

Wont ZBXNEXT-714 be covered by ZBXNEXT-1836?

+1 for OpenTSDB and Graphite

ZBXNEXT-2640 was closed as a duplicate of this bug. It proposes to solve the backend storage problem by adding an optional call to send data to an AMQ type queue at the point in zabbix server logic where data is being prepared for insertion into sql.

This single addition is effectively a modular storage solution - you can pretty much route the data wherever you want once it's in e.g. rabbitmq. It makes OpenTSDB, Graphite, and just about anything else you can think of almost trivial additions from the user perspective, and has the advantage of reducing the zabbix code complexity "load" to only dealing with one single api (the amq api). I believe rabbitmq is a good choice here, but others would work as well.

I believe the "hook" would live somewhere in src/libs/zbxdbcache/dbcache.c in the dc_add_history_<type> functions. If the target is rabbitmq, the C client library is this: http://alanxz.github.io/rabbitmq-c/docs/0.5.0/.

Implementation would be to add optional configuration to the server config file sufficient to describe a connection to an amq exchange. If that configuration section is populate, code establishes the connection and then pushes data through it. Data would be text format (could be just comma delimited to keep overhead down on the zabbix side).

I believe this would not be hard to implement.

I believe that a lot of the ideas that have been presented here are fantastic. Definitely more database storage options are needed for both storing data AND Zabbix reading it back out. Something lacking here though is the concept of storage "modules". I've heard it be alluded to in #zabbix and such, but figured I'd point it out here.

There really needs to be something of a storage framework that people can write pluggable modules for. For instance, let's say that this framework allows me provide my own plugin (or another one someone has written) that tells Zabbix how to store data in OpenTSDB. This same plugin could tell Zabbix how to read data back out of that datastore.

To improve on this further it should be possible for a custom plugin to support sharding of the datastore. Why couldn't someone write into the MySQL module the ability for Zabbix read/write data from/to multiple servers? If all Zabbix cares about is function calls to the plugin that provide or store the data, it should be trivial to support that from a Zabbix perspective. The difficulty lies in the pluggable module.

Zabbix SIA could provide a base set of modules as they see fit (ie: MySQL, Postgres, etc.) and also rely on the community to provide them.

Obviously this is not a minor change and require a significant amount of work. It is possible to get an interim (and let me stress the word "interim") solution probably a bit more quickly. ZBXNEXT-2640 has the concept of adding a "hook" into the current Zabbix code to publish data it collects to an AMQP bus (like RabbitMQ) - this was mentioned in the previous comment by David Parker. This would provide the ability to shove the data off to some other datastore of choice without completely redesigning the Zabbix storage code as it stands now. While it wouldn't provide the ability for Zabbix to read data from the arbitrary datastore, it would allow people to offload some work from the main Zabbix database, thereby freeing up resources (which the Zabbix database desperately is in want of).

I think InfluxDB (http://influxdb.com), should be a very reasonable alternative for the items storage, which have a SQL like query engine.
What you think?

One of the current major issues with InfluxDB is that its clustering is experimental at best, so it would suffer from scalability concerns. If it was simply another plugin for a storage framework (like I mentioned in my last comment), that'd be great!

As you mentioned, having output go through an AMQ (eg rabbitmq) as in ZBXNEXT-2640 is a fast interrim path toward that. If everything gets shoved into queue, it can go wherever the user wants it to go. The effect is much like having a plugin-able storage backend, albeit write-only.

An implementation in the sense of ZBXNEXT-1836 would be the best approach in my opinion.

Then there would be an abstraction layer that allows to use almost any thinkable storage backend.
But t's probably better to have ZBXNEXT-2519 in place first. This would ensure that one history storage plug-in is sufficient for both server and frontend.

ZBXNEXT-2659 talks about FoundationDB support.

When Alexey was in Moscow, he was agreed to make something like middleware with support of different storage engines like plugins.
So it can be configured to store history, histrory_unit in Cassandra, HBASE, OpenTSB, Influx, but logs to store in Elasticsearch or Solr.
So every type of data cen be stored in different databases, so middleware is abstraction layer to access databases.
So plugins must be easy writeable, and I would like it would be a standalone process and written on Go.

Hi !
I've just tried InfluxDB as a database storage replacement for history.
I've used the jdbc input plugin of logstash, to continuously fetch the last minute of history and then send data to influxdb (with the influxdb output plugin).
And it works great, all chart was viewable through Grafana, faster than the Zabbix Datasource plugin.
I think you should consider using InfluxDB as a scalable alternative for the history and trends tables.
You can use the "Continuous Query" to aggregate data over time (ie trends), and each item can have a specific retention policy (like in zabbix).

Regards,

Thierry.

In reference to ZBXNEXT-1836, it would be nice to support (optionally) a module per type of information with option to provide different ones for multiple/both types (but at least for type numeric and text).
Otherwise it could be difficult or even impossible to use database/storage engines that are highly specialized to specific types only.

ZBXNEXT-3353 is similar, but only asks for one-way data export module support. see that issue for more detail.

I generally don't like the solutions which suggest manual sharding, partitioning or other manual hacks.
There are plenty of timeseries storage systems now which were specifically designed for the task, many of them scales both horizontally and vertically, and does all of this automagically. They have similar APIs, I've seen many of them providing APIs for the others.
Zabbix uses RDBMS wor a task it isn't suited well. I understand that this is the "original" approach but scaling and redundancy is a big problem. I try to use zabbix API in grafana and the results are horribly slow, compared to basically anything else.

Just to name a few older and newer, all of them possibly better than *sql: dalmatinerdb, apache kubu, opentsdb (these all automagically clustered); influx, prometheus, riakTS, kairosDB, graphite/whisper, or even elasticsearch; and some I don't even know: druid, blueflood, atlas, chronix, hawkular, warp10, heroic, btrdb, merticTank...

I personally see the first two as a good candidate for myself, but they are very similar from the usage standpoint. All of them great in storing and retrieving time series.

A fresh related issue: ZBXNEXT-3661.

Hello,

just some suggestions:
I think Zabbix don't need an alternative database
Zabbix must use a better schema some example to imrpove performance:

• at the moment zabbix stores a lot of unneeded data: eg a ping which returns 0 will be saved in the DB, everytime, but that is useless, you need only changes, so can can just save the data when it changed + the last check which returns 0
• zabbix stores in table trends 6columns; min, avg, max are three of them, it would be better (performance and size reason) to create an array like {min,avg,max}
• and one of the important things, use partitioning as default

@stefan krüger: You believe that, and many of us disagrees.

RDBMS is not good for storing (sometimes many hundred millions) of simple, unstructured time series data, and even less for retrieving them fast. It doesn't really matter how you try to tweak the schema, you always need lots of dirty (manual) work to make it at least somewhat bearable, but it never will be good.

In contrast the clustered time series databases usually provide you with a working, distributed, load balanced and high peformance backend without any fiddling necessary. Retrieval times are (often) magnitudes lower than RDBMS.

I understand that some people believes that the solution is not to keep historical data, they delete everything older than a week and be happy. But then we (those who need the history for years) would need a parallel system, gathering the same data but storing it, then another to retrieve and visualise it. Kind of defeats the purpose.

I haven't browsed the code but probably all tasks can be somehow traced back to
insert_some_data(class, value, timestamp, metadata)
and
get_some_data( class_selector, timerange )
and it shouldn't really matter what the backend is, how does it partition, cluster, load-balance or whatnot.

Data export to JSON files available in 4.0 alpha5 - ZBXNEXT-4417

Elasticsearch added in ZBXNEXT-4320

Timescale support added in ZBXNEXT-4868

Does it still make sense to keep the request open? Please comment with case description. 

Data export to JSON files available in 4.0 alpha5 - ZBXNEXT-4417

Export files became "second class" and aren't really a match for what is requested in this ticket.

Will be support victoriametrics as storage?

serrrios , VictoriaMetrics will definitely be an option once we have history API implemented.

What about better schema to improve data density?

The order of the columns matter:
https://stackoverflow.com/questions/2966524/calculating-and-saving-space-in-postgresql/7431468#7431468

https://stackoverflow.com/questions/12604744/does-the-order-of-columns-in-a-postgres-table-impact-performance

what do you think of clickhouse for storing large historical data ?

Can we test VictoriaMetrics as alternative to SQLite?

I have lost my patience in the recent years waiting for solution, since RDBMS clearly not fitting the case.

Since we use VictoriaMetrics for about everything metric, and it handles about 20 billion entry on a single non-clustered virtual machine rather well (both disk usage and response time is excellent) I started to implement querying metrics through zabbix api and inject it into V.M. and use Grafana to watch metrics (not to mention some algorithmic alerts through alertmanager, which shall be fed back to zabbix, enevtually), and tell Zabbix to remove metric stuff older than some ridiculously short time, say, 7 days.

It would be nice if zabbix could automagically push metric data to anything, using a plugin-based system (prometheus compatible, influx compatible, tsdb compatible, just to name a few), and maybe able to include Grafana dashboards (even in an iframe), if it's hard to implement actual metric queries against the said protocols and displaying it (but, honestly, Grafana displays graphs a million times better than a hacked-together local graph, why reimplement it again?).

Clickhouse is nice but I wouldn't store metrics in a non-metric oriented backend; however, even if it's not perfect, it could handle storage way better than the current solutions. Especially any historical non-metric data. (And Elasticsearch is not resource friendly in any way, mind you.)

I was suggesting generic metric API a loong time ago, following the discussions but I have seen marginal demand and will, and probably it's easier for me to syphon out the data than waiting for some miracle to happen. (In my mind Zabbix is an excellent alert engine and a horrible metrics engine.)

Still I carry hope.

Dear grin,

It would be nice if zabbix could automagically push metric data to anything, using a plugin-based system

Zabbix already has such "plugin-based system" (see ZBXNEXT-3353 and docs).

Grafana displays graphs a million times better than a hacked-together local graph, why reimplement it again?

Totally agree. That's why my loadable module for InfluxDB has integration with Grafana Zabbix plugin.

I don't use this solution myself, but since there are virtually no open issues it either works to everyone's satisfaction or nobody uses it.

since Zabbix supports TimescaleDB, it has a good scalable alternative for history and items.. also Zabbix has the possibility  to export metrics everything is fine

so for me, that can be closed

@Glebs

While in theory influx can be used in VM in practice as you mention with no label information (group, item name, interface, ...) it has little use, possibly not unrelated to the lack of feedback. I am not sure i would be simple (or possible) to write a module which could generate "whole" data (ts+data+labels), posssibly doing large amount of lookups in the process. For the general case (or for really anything which works on the data, from alerting to various external plugs) the grafana "hack" do not seem to be useful.

We are aiming to introduce a generic HTTP based API for connecting any time series DB engines in 7.0 LTS. It is currently in design phase.

Use of timeseries databases for history usage would be easier if zabbix item keys supported named parameters.

Example:
Instead of vfs.fs.size[/usr,used] -> vfs.fs.size[filesystem=/usr,mode=used]

This could easily exported in the history file of zabbix and imported in a prometheus compatible timeseries (we use victoriametrics for this) for example a on a timeseries with name vfs.fs.size and labels

Retrocompatibility, for those who need it, could be achieved by supporting in zabbix item.keys both positional and new named parameters.

I'm looking forward for a timeseries backend that can work OOTB with zabbix!

Keep up with the good work
Andrea

Any update on this?

As I see, it has label "Zabbix7.2", but in roadmap this request planned for 7.0.

NexonSU It was initially planned for 7.0, now postponed to later releases.

+1 for victoriametrics

What about Clickhouse?

Really fast OLAP DB that supports SQL, it has MySQL wire protocol for connecting to it and can scale to tremendous size.

Only drawback to OLTP databases like MySQL and Postgres that it does not support triggers so you have to reside on an external mechanism.

Nevertheless, it supports aggregate functions like timescaledb's continuous aggregates and materialized views.

christos.diamantis 
With Clickhouse, we can have issues with low end setups:
 

Using less than 16GB of RAM
The recommended amount of RAM is 32 GB or more.

If your system has less than 16 GB of RAM, you may experience various memory exceptions because default settings do not match this amount of memory. You can use ClickHouse in a system with a small amount of RAM (as low as 2 GB), but these setups require additional tuning and can only ingest at a low rate.

—  https://clickhouse.com/docs/en/operations/tips#using-less-than-16gb-of-ram

livreacesso.pro 

Clickhouse is a good choice for big setups indeed.

Low end setups would require a lot of tuning but the benefits would not be visible in contrast to Timescale for example.

Mentioning again, that if possible a generic interface and external plugins/plumbing would be more efficient than coding a half dozen different backend interfaces into zabbix core  with wildly different semantics. Victoriametrics is great, clickhouse is great, others like other TSDBs (like influx, timescale), and some thought elastic/opensearch is a good idea, all of these should be using a unified API/interface. Otherwise it requires familiarity with both deep zabbix internals and the internals of the backend system; instead of just writing the maching API endpoints for the specific backend which would require minimal zabbixism from the plugin writer.

And I apologise for stating the obvious, repeated every 5 years.

On roadmap 7.4 :

With the evolution of our information systems, and in particular containerization, the search is often for horizontal scalability. When using a database such as Postgres, a plugin called Citus makes this horizontal extensibility possible. 
Is compatibility with this plugin and horizontal extensibility planned?

Perhaps we need to create a specific upgrade request?

francois.hugues.mailliet , Citus might be considered as one of solutions, however now we are more focused on engines designed specifically for time-series and JSON data storage.

alexei : Thanks for this update.

TSDB is very interestinfg way and since three years, I only install Zabbix instances with TimescaleDB.

But to work on a full containerized architecture, including the storage part, I understand that working on the new JSON data storage possibilities is on the rails. I understand and I agree. 

I agree because, if I have understood everything correctly, it's a native Postgres functionality. But for the moment, this doesn't necessarily address the horizontal scalability of Postgres database storage. Json data storage It is useful for manipulating semi-structured documents without defining a strict schema, isnn't it?
Only Citus seems to be an interesting solution for this storage distribution. But Zabbix is currently not officially compatible with this plugin, is it?

Is this Jira is the must to follow this evolution?

francois.hugues.mailliet, you may follow Zabbix roadmap at https://zabbix.com/roadmap in order to get up-to-date information that also includes actual references to ZBXNEXT tickets.

Just chiming in here to say that TimescaleDB works great for us.

A single Postgres VM (10 cores, 32GB RAM on Xeon 6230 VMware hypervisor) carries about 10k NVPS with no issues. No manual partitioning. We do have a similar streaming replica server as well (for failover), and our Grafana connects to that read-only for dashboards.

I'm curious what numbers others have to cause performance issues on the Postgres side?

Well, our PostgreSQL can handle 100k NVPS... on new Dell server with 128 cores and 256GB of RAM.
In the same time, VictoriaMetrics can handle a few millions NVPS without even noticing something on old Intel server with 12 cores and 64GB RAM.
And this is normal, because VictoriaMetrics designed like this.

But, right now we have problem even with events table, because it's too big...

It is extremely necessary to move the timeseries to a separate specialized TSDB. Only then will zabbix become a real monitoring system. Until then, it is a complicated toy from the 90s

I agree a TSDB is needed but also naming convention of zabbix items needs to change before, as it cannot contain information of monitored instances in the zabbix item name

It can, some of our SRE engineers stores a ****ing access.log in VictoriaMetrics labels and... it works.

This is wrong on so many levels, but it works.

Support for alternative scalable storage engines is on the way in Zabbix 8.0. Stay tuned for updates!

everyone who hit the limit of the current implemantation, that looks like a bigger instance (which is used in a bigger company), so when u really need that feature? why u don't pay for them and asking zabbix how much does it cost to implement this?

To keep the discussion focused, the last few comments have been removed. klimenko.andrei, we kindly ask you to avoid further personal remarks and maintain a respectful tone moving forward.

We use PostgreSQL with TImescaleDB with compression and love it. Schema changes if and when necessary from new zabbix releases can be tedious to minimize downtime. Current DB is 700GB with compression enabled.

TimescaleDB over Citus Data is a good compromise for HA scenarios, alexei 
VictoriaMetrics can be used as alternative for Proxy servers, as PoC for a start.

After being part of the roadmap for 3 major releases this is no longer on the roadmap.

splitice :  It is included in the roadmap, but not directly explicit and more associated with telemetry data:

splitice, it is in the roadmap. The functionality for all history tables is coming in Zabbix 8.0 LTS.

alexei that sounds great..

It would be nice if u can told us in which status u are.. do you elaborating/testing a technology/product (if so which one) or did you already an decision and if so which product/technology will zabbix support?! (that should be NOT a discussion why do u use X and not Y or if u are in elaborating please also take a look at Z)

shad0w , we are about to choose timeseries DB engine, all R&D is already done. I will keep everyone updated.

alexei thats what I thought But which one (TDEngine, QuestDB, Cassandra, InfluxDB, etc) ?

shad0w , we are close, but no decision is made yet.

So, ClickHouse seems to be one of your choices: https://git.zabbix.com/projects/ZBX/repos/zabbix/compare/diff?sourceBranch=refs%2Fheads%2Ffeature%2FDEV-4427-7.5&targetRepoId=152