Processing huge amount of data externally

[limo]

I would want to open discussion about processing huge amount of data externaly from Zabbix. I found that it is possible to write zabbix module but if I understand it good, module can be used only for getting items but not to work with data from history, cache or trends?

I mean I will need to take data from internal cache, database history and database trends and do some neural network analysis. I know it is possible to use API to get history or trends data but I think this will be very slow for huge amount of data.

Any suggestions how to achieve this? Maybe some plugin function to work with cache, history or trends? Yes, there is still possibility to not use zabbix server at all and use external software which will look inside database tables. But I am not sure this is good way.

It is possible that there will be more plugins working with zabbix history data to achieve some more interesting analysis of data. But it is important to not use some high level API, because it has to be quick enough.

I know it is not good idea to maintain next API, but I think if it is low-level API which could look into memory pages containing data, it would be good point of start.

Thank you

[Alexei]

You may consider creating one way database replication of historical data to external system for further analysis. It would be easy to manage and no need to change anything on Zabbix side.

[limo]

Yes, that is probably way. Maybe it could solve even math formulas in trends. I will think about this.

Again, question is, whhat would be best way. Is there are ambition to make Zabbix server with plugins for further data analysis or everything should be done externaly? Both ways are possible. I think that plugin solution is better.

In future, maybe there are some history data, which will not be in history table, only in cache? Math operations or analysis could be much more faster. Each plugin can specialise over some kind of analysis (neural, statistical, numerical, ..).

I know that Zabbix is primarily monitoring system. My work today is, to do continuous analysis of data inside Zabbix and find some interresting processess, correlations and incidents which are very hard to find by todays approach. And I want to know, if this is interresting for community and if this should be implemented absolutely externaly or maybe, in future, this could be part of Zabbix. I know that we cannot answer this question now. This is still only discussion

[Alexei]

We already have in-memory value cache for faster access to historical data in pre-2.2. Support of plugins/modules for every functional part of Zabbix is one of our primary long term goals for sure.

[limo]

Ok, if somebody is interested in, I made small sql script, which will backup data from

history
history_uint

trends
trends_uint

into _backup tables. Backup tables have no indexes and 20 partitions (by clock). Result is, that it is possible to leave history and trends in tables even on slow systems because _backup tables have no indexes and it is myisam format. So inserts are quick enough. No need to change anything inside Zabbix, Zabbix will not see data in this tables. But it is possible to access them externaly later.

It is relatively easy to create same backup for more history tables. Feel free to use/comment!

CREATE TABLE IF NOT EXISTS `history_backup` (
  `itemid` bigint(20) unsigned NOT NULL,
  `clock` int(11) NOT NULL DEFAULT '0',
  `value` double(16,4) NOT NULL DEFAULT '0.0000',
  `ns` int(11) NOT NULL DEFAULT '0'
) ENGINE=MyISAM;

ALTER TABLE history_backup PARTITION BY KEY(clock) PARTITIONS 20;

DROP TRIGGER IF EXISTS `history_backup`;
DELIMITER //
CREATE TRIGGER `history_backup` AFTER INSERT ON `history`
 FOR EACH ROW begin
  insert into history_backup (itemid,clock,value,ns) values (new.itemid, new.clock,new.value,new.ns);
end
//
DELIMITER ;

CREATE TABLE IF NOT EXISTS `history_uint_backup` (
  `itemid` bigint(20) unsigned NOT NULL,
  `clock` int(11) NOT NULL DEFAULT '0',
  `value` bigint(20) unsigned NOT NULL DEFAULT '0',
  `ns` int(11) NOT NULL DEFAULT '0'
) ENGINE=MyISAM ;

ALTER TABLE history_uint_backup PARTITION BY KEY(clock) PARTITIONS 20;

DROP TRIGGER IF EXISTS `history_uint_backup`;
DELIMITER //
CREATE TRIGGER `history_uint_backup` AFTER INSERT ON `history_uint`
 FOR EACH ROW begin
  insert into history_uint_backup (itemid,clock,value,ns) values (new.itemid, new.clock,new.value,new.ns);
end
//
DELIMITER ;

CREATE TABLE IF NOT EXISTS `trends_backup` (
  `itemid` bigint(20) unsigned NOT NULL,
  `clock` int(11) NOT NULL DEFAULT '0',
  `num` int(11) NOT NULL DEFAULT '0',
  `value_min` double(16,4) NOT NULL DEFAULT '0.0000',
  `value_avg` double(16,4) NOT NULL DEFAULT '0.0000',
  `value_max` double(16,4) NOT NULL DEFAULT '0.0000'
) ENGINE=myisam ;

ALTER TABLE trends_backup PARTITION BY KEY(clock) PARTITIONS 20;

DROP TRIGGER IF EXISTS `trends_backup`;
DELIMITER //
CREATE TRIGGER `trends_backup` AFTER INSERT ON `trends`
 FOR EACH ROW begin
  insert into trends_backup (itemid,clock,num,value_min,value_avg,value_max) values (new.itemid, new.clock, new.num, new.value_min, new.value_avg, new.value_max);
end
//
DELIMITER ;

CREATE TABLE IF NOT EXISTS `trends_uint_backup` (
  `itemid` bigint(20) unsigned NOT NULL,
  `clock` int(11) NOT NULL DEFAULT '0',
  `num` int(11) NOT NULL DEFAULT '0',
  `value_min` bigint(20) unsigned NOT NULL DEFAULT '0',
  `value_avg` bigint(20) unsigned NOT NULL DEFAULT '0',
  `value_max` bigint(20) unsigned NOT NULL DEFAULT '0'
) ENGINE=myisam ;

ALTER TABLE trends_uint_backup PARTITION BY KEY(clock) PARTITIONS 20;

DROP TRIGGER IF EXISTS `trends_uint_backup`;
DELIMITER //
CREATE TRIGGER `trends_uint_backup` AFTER INSERT ON `trends_uint`
 FOR EACH ROW begin
  insert into trends_uint_backup (itemid,clock,num,value_min,value_avg,value_max) values (new.itemid, new.clock, new.num, new.value_min, new.value_avg, new.value_max);
end
//
DELIMITER ;