Optimal DB Engine(s) for Zabbix

[Yello]

Hi,
Given that zabbix stores time series data has anyone looked into other types of (non relational) database engines? In particular I have in mind column store engines such as that provided by infobright for use with mysql(http://www.infobright.com) which seem better suited to handling this type of data and scale accordingly.

I'd be interested in hearing people's comments on this approach. It appeals to me, but not being a DBA I am not fully conversant with the issues involved. Would the nature of the underlying engine be transparent to zabbix and its schema? Or would it be a big job to integrate the two?

I've not seen any previous discussion on this topic here and my suspicion is that significant backend performance improvements might be achievable by going down this route.


Regards,
David

[Alexei]

Let me comment on this. Instead on focusing on a specific database engine, we plan to introduce an API for storage of historical data, so one can use whatever storage engine he/she wants. Adding a new engines should be just a matter of writing a 100-1000 lines of C code.

I'd love to have horizontal scaling here. It means that there must be support of alternative engines designed to scale. No promises, but it's highly likely that support of Cassandra will be implemented first.

[rmorgan466]

Any ideas/thoughts when the generic historical API will be ready? This is rather important for us since we monitor close to 10k items and historical data is important for us

[rmorgan466]

If anyone is interested in this please join in the IRC room so we can plan this module/patch.

[Yello]

You're talking about #zabbix on freenode right?

[rmorgan466]

You're talking about #zabbix on freenode right?

Yes, thats the one. I would like to setup some sort of plan or try to help the author in testing.

[Yello]

I'd love to have horizontal scaling here. It means that there must be support of alternative engines designed to scale. No promises, but it's highly likely that support of Cassandra will be implemented first.

Can anyone comment on any progress towards implementing a cassandra backend fro zabbix? We don't use zabbix at my current site but the chances of trialling it would rise significantly with a backend that scales better.


Regards,
Dave

[Alexei]

Can anyone comment on any progress towards implementing a cassandra backend fro zabbix? We don't use zabbix at my current site but the chances of trialling it would rise significantly with a backend that scales better.

We already have a prototype developed and actually used in production for some of our customers. Now we are working on making Cassandra and more general API for history storage part of the core product, I believe it will be available in Zabbix 2.6.

[aib]

I'm sorry to interrupt you - Do you mind to point me to Zabbix Roadmap?
I would like to see some milestones, like "When we will see Zabbix 2.6 version?"

[Alexei]

I'm sorry to interrupt you - Do you mind to point me to Zabbix Roadmap?
I would like to see some milestones, like "When we will see Zabbix 2.6 version?"

Have a look at http://www.zabbix.com/life_cycle_and_release_policy.php. Also https://www.zabbix.org/wiki/Docs/roadmap has details about roadmap for Zabbix 2.4, which is to be released very soon actually.

[aib]

Thank you!

[kloczek]

I'd love to have horizontal scaling here. It means that there must be support of alternative engines designed to scale. No promises, but it's highly likely that support of Cassandra will be implemented first.

What is wrong with horizontally scaled for example mysql DB?
To use this you don't need to change single line of zabbix code.

[tatapoum]

What is wrong with horizontally scaled for example mysql DB?
To use this you don't need to change single line of zabbix code.

Could you please tell us more about horizontal scaling ? Do you mean with MYSQL Sharding or Galera cluster ? Doesn't it require some changes to the way Zabbix talks to the database ?

[kloczek]

Could you please tell us more about horizontal scaling ? Do you mean with MYSQL Sharding or Galera cluster ? Doesn't it require some changes to the way Zabbix talks to the database ?

You can use for example mysql-proxy to redirect queries going to different tables to send them to physically separated mysqld instances running on separated systems. The same technique can be used on scale horizontally select queries (round-robin cfg it is one of the example mysql-proxy few lines configurations).

To be honest from point of view of scalability I'll really happy to see in zabbix integrated table partitioning.

Zabbix generates very specific DB backend workload. Biggest part of the IOs are write IOs caused by writing to the history* tables. Second smaller part of write IOs are ops going over trends* tables. In both cases zabbix adds only new data. This part of whole zabbix data is one time written are never updated. Content of these tables by partitioning is only rotated with only few IOs per table instead tons of IOs spend on delete oldest historic data. Problems caused by housekeeping oldest data by delete queries are usually first serious obstacle for each zabbix newbie.
Items setup simplification with using exactly the same history and trends periods across all items makes additionally fully predictable DB backend from point of view physical storage size needed by current zabbix configuration (estimation of such size can even be displayed in zabbix dashboard).
For example I'm now using 15 days history period (trends policy probably will be changed in next few weeks to 2-3 years).

I think that in biggest zabbix configurations used now partitioning is mandatory. Introducing it as standard will make live many hardest working zabbix admins easier because now they are using DB solution not officially supported by zabbix. In case simplest and smallest zabbix deployment impact should be in many cases more positive than negative.

Some SQL engines allows export partition and import it as binary file into SQL engine running on completely different system. Latest MySQL provides at last such operations which makes possible to organize oldest raw data archive. Export/import partition is present in Oracle (I don't know how it is exactly with other SQL engines but IIRC postgresql should have this as well). On proxy layer should be possible to redirect or split for example select query if all data will be in different engines holding data partitioned by clock (detecting this in lua should be possible to implement in few lines code). With partitions moved between physical engines will be possible to organize slower and faster DB servers to make whole solution cheaper (I'm going try to implement such infrastructure in next few months as POC).

Mysql-proxy can be programmed in lua and it is kind of generic solution.
I think that very specific zabbix workload makes very good condition to build or to organize own zabbix DB proxing infrastructure running as separated process. By sending traffic to the proxy over networks sockets and on next stages passing it over VRRP and DSR LBs is possible additionally organize full DB redundancy if duplicated VRRP/DSR and DB proxies will be working in crossover cfg (two proxies behind two redundant VRRP/DSR points).
First time such solution I've been using few years ago with mysql 4.x. In that case I had majority of the selects so it was necessary to organize few R/O nodes with slaves and one master. Select and update/delete/insert queries have been separated by mysql-proxy (no single line modification on application layer).
For example to organize such layer with additional HA on Solaris you have everything OOTB. You can use ILB (in such scenario you don't need expensive network devices).

Any changes done on such proxy layer will be completely separated from main zabbix code. I think that it can be nice and very clean solution from point of view zabbix internal architecture. Different types of DB proxies can hide different SQL types backends details.
In simplest variant zabbix should be talking over such proxy over unix sockets and proxy should be communicating with single DB engine. With growing needs by use more complicated variants of proxy configurations would be possible to scale up infrastructure fulfilling growing DB needs. With DB proxing things like data migration between different engines should be easier to organize (maybe it is not the best example because data migration is quite easy to organize on DB layer but ..).

At the end DB proxy layer can be used as well to fulfill zabbix very specific caching needs. Whole caching algorithms can be very close tied with such zabbix specific needs. Whole caching it can be very simple shifting series of data used in calculated items, triggers using series of historic data and caching data used in shifting graphs. Maintaining content of the caches can be probably executed in few loops with different timers instead caching of data using read ops frequency.
This can take off probably quite big part of the pressure from DB engines which are using generic caching solutions.

[tatapoum]

Thank you for this interesting post kloczek. Just a few comments :
- if I understand well, mysql-proxy would help to route the SQL inserts/updates/selects to different DB backends, according to LUA rules for example. So we could imagine to split route/split the SQL statements to different backends based on a clock key, to increase the performance. That would also help to store old data on cheap hardware. But if you partition by day or hour, all the SQL queries for the same day or hour would arrive on the same DB no ? So if you need huge performance, how do you handle this ?
- this means that the setup needs manual configuration/scripting and that adding a new DN node isn't fully automatic
- the scaling isn't really horizontal, no ?

With a good TSDB or NoSQL backend, most of this would be easier :
- sharding means "real" horizontal scaling
- adding a node is very easy and the workload is redistributed over the whole farm automatically