that is very intersting, do you have some benchmarks or other performance measurements? how does it scale with zabbix?

Implemented in:

• pre-4.2.0alpha3 r89031

Good news but on Zabbix Whats New i read "TimescaleDB is supported with Zabbix server only.", what does it mean?
Does it mean is not working for Zabbix Proxy or i could not access to the history/trends data from the Web Frontend?
Could be more exhaustive the documentation about it?
Thanks

Does it mean is not working for Zabbix Proxy or i could not access to the history/trends data from the Web Frontend?

It means that Zabbix proxy is not supported. Thanks for pointing out, the docs updated!

Hi Alex,
thank you for the clarification!

Can you also update the zabbix 4.2 doc  when  Zabbix server need TimescaleDB CE  or Enterprise version ? 

Looks to me we only need CE version to get DB table auto-partitioning from https://www.timescale.com/pricing

Can you also update the zabbix 4.2 doc  when  Zabbix server need TimescaleDB CE  or Enterprise version ? 

Enterprise version of TimescaleDB is not required. 4.2 supports OSS version of TimescaleDB and is not dependent on TLS-licensed features included in CE (in fact, the 1st TLS-licensed version just coming out today) that's why we just put minimum supported version (1.0) in the requirement page.

Finally you guys are looking into this!

We have internally been using TSDB for the past year and its been working like a charm, we have created hypertables on events, alerts, history* and trends* and its been a world changer in terms of DB management / performance.

One of the main issues we have been facing are the Zabbix upgrades, currently Zabbix does not support or have the knowledge that these tables are now hypertables. It fails because of the foreign keys and other structural changes..

So for the moment we are "Manually" upgrading our DB schema.

@Alex, Are you going to fully support TSDB and Zabbix upgrades?

@Alex, Are you going to fully support TSDB and Zabbix upgrades?

Upgrading shouldn't be a problem. But please note currently TSDB support is in experimental status. We'll decide how to progress with further TSDB support as soon as we collect enough of user feedback.

Really happy to see Zabbix possibly moving to support TimescaleDB.
This might be a reason for us to move from MariaDB to Postgres in the future.

I am thinking of deploying a zabbix back-ended with postgresql as primary and replicate its data over a 10ms WAN pipe to aother DC,via repmgr.

Now with TS db supported by coming zabbix 4.2, This is even more interesting, one can design a  Zabbix Geo fail-over with auto table partitions(by TSDB).

Things I don't know is how to calculate is that what is  nvps value that will  saturate  the 10ms WAN pipe when doing  repmgr replication ?

PS: Here is my workbook a "Zabbix TimeScale DB" POC.

I got my POC working following the doc. One suggestion is to increase the max_connection from 20 upward( mine is 40).

Otherwise following error showed in my poc instance.

11421:20190203:092313.774 [Z3001] connection to database 'zabbix' failed: [0] FATAL:  sorry, too many clients already

Following  screenshot is from 4.0.3 Zabbix. Same as the one in 4.2 alpha3.  Shouldn't "TimeScale DB" be displayed somewhere ? 

Following  screenshot is from 4.0.3 Zabbix. Same as the one in 4.2 alpha3.  Shouldn't "TimeScale DB" be displayed somewhere ? 

tjyang, no, just make sure Override item history and trend periods are on. If you applied timescaledb.sql script and it ran successfully you'll see this string db_extension: timescaledb in zabbix_server.log after configuration sync.

@Alex Kalimulin

Thanks for this tip. I do find this string in my test environment.

Is following string correct ? 

[root@zabbixts01 zabbix]# grep -i db_extension zabbix_server.log*
zabbix_server.log-20190204: [select refresh_unsupported,discovery_groupid,snmptrap_logging,severity_name_0,severity_name_1,severity_name_2,severity_name_3,sererity_name_4,severity_name_5,hk_events_mode,hk_events_trigger,hk_events_internal,hk_events_discovery,hk_events_autoreg,hk_services_mode,hk_services,hk_audit_mode,hk_audit,hk_sessions_mode,hk_sessions,hk_history_mode,hk_history_global,hk_history,hk_trends_mode,hk_trends_global,hk_trends,default_inventory_mode,db_extensio from config order by configid]
[root@zabbixts01 zabbix]#

But my concern still remain. Shouldn't there be a place somewhere in GUI,  to inform login user you are operating on a Zabbix server with TimeScale DB ? 

There is no indication which backend is used (MySQL, PostgreSQL, Oracle, IBM DB2), why would TimescaleDB be special?

@Glebs Ivanovskis, you got the point. I rest/withdraw my concern on displaying DB backend.  

Is following string correct ? 

[root@zabbixts01 zabbix]# grep -i db_extension zabbix_server.log*
zabbix_server.log-20190204: [select refresh_unsupported,discovery_groupid,snmptrap_logging,severity_name_0,severity_name_1,severity_name_2,severity_name_3,sererity_name_4,severity_name_5,hk_events_mode,hk_events_trigger,hk_events_internal,hk_events_discovery,hk_events_autoreg,hk_services_mode,hk_services,hk_audit_mode,hk_audit,hk_sessions_mode,hk_sessions,hk_history_mode,hk_history_global,hk_history,hk_trends_mode,hk_trends_global,hk_trends,default_inventory_mode,db_extensio from config order by configid]

 
tjyang This is just an SQL but you need to find string db_extension: timescaledb. You need DebugLevel=5 in zabbix_server.conf to see the output from configuration sync.

Thanks Alex, I got following string as  you pointed out, after setting debug level to 5. 

14463:20190205:073908.085 In DCdump_config()
14463:20190205:073908.085 refresh_unsupported:600
14463:20190205:073908.085 discovery_groupid:5
14463:20190205:073908.085 snmptrap_logging:1
14463:20190205:073908.085 default_inventory_mode:-1
14463:20190205:073908.085 db_extension: timescaledb
14463:20190205:073908.085 severity names:
14463:20190205:073908.086 Not classified
14463:20190205:073908.086 Information
14463:20190205:073908.086 Warning
14463:20190205:073908.086 Average
14463:20190205:073908.086 High
14463:20190205:073908.086 Disaster
14463:20190205:073908.086 housekeeping:
14463:20190205:073908.086 events, mode:1 period:[trigger:31536000 internal:86400 autoreg:86400 discovery:86400]
14463:20190205:073908.086 audit, mode:1 period:31536000
14463:20190205:073908.086 it services, mode:1 period:31536000
14463:20190205:073908.086 user sessions, mode:1 period:31536000
14463:20190205:073908.086 history, mode:2 global:1 period:86400
14463:20190205:073908.086 trends, mode:2 global:1 period:86400
14463:20190205:073908.086 End of DCdump_config()

@T.J. Yang: You are right! If we would like to use TimescaleDB i think is good option in Zabbix, to force the option "Override history/trend period" in Administration/General to simplify the initial configuration.

You are right! If we would like to use TimescaleDB i think is good option in Zabbix, to force the option "Override history/trend period" in Administration/General to simplify the initial configuration.

dimitri.bellini When you run timescaledb.sql the script sets "Override history/trend period" options already so no problems here. If you decide to unset one of these options later on Zabbix will not prevent you from doing so, you may have your reasons.

Hi all

• I am setting History and Trends to keep just one day storage, just to verify TS extension is working on deleting older data automatically.

• I am also have pgadmin 4 connected to my poc server(zabbixts01.test.lan), which table I should look to observe the 1 day data retention ?

• or there is a sql command I can see trend data is only 1 day old ? 

@Alex: Ok very good! I have missed that feature

I am setting History and Trends to keep just one day storage, just to verify TS extension is working on deleting older data automatically.

tjyang There are the following history tables which are currently under TimescaleDB control:

• history
• history_uint
• history_log
• history_text
• history_str
• trends
• trends_uint

You can check the oldest item in the historic table by running the following SQL:

select to_timestamp(min(clock)) oldest from history_uint;

Hi Alex, 

From the output, looks like 1 day old retention is not working. I am still getting 02/03(two days) old data.

-bash-4.2$ hostname
zabbixts01
-bash-4.2$ /usr/pgsql-11/bin/psql -U postgres -h localhost
Password for user postgres:
psql (11.1)
Type "help" for help.

postgres=# \c zabbix;
You are now connected to database "zabbix" as user "postgres".
zabbix=# select to_timestamp(min(clock)) oldest from history_uint;
oldest
------------------------
2019-02-03 19:00:00-05
(1 row)

zabbix=#\q

From the output, looks like 1 day old retention is not working. I am still getting 02/03(two days) old data.

tjyang This may be normal and subject to various conditions such as frequency of historic data and partition size. Under TimescaleDB Zabbix deletes partitions rather than individual records.  Which means that server does not touch any partitions which contain data that is newer than your housekeeping settings.

For example, if partition #2 stores days 5, 4, 3 and partition #1 stores days 2, 1 and 0 (today), and you have 1 day retention period then only partition #2 will be deleted during next housekeeping cycle. Partition #1 will be deleted only when ALL records in the partition will be older than your retention period.

Alex, thanks for the explanation, I will add more zabbix agents,  wait and see.

In the mean time, I will study more on postgresql and timescaleDB.

glad to read this, guys

several questions on my mind:

• how efficient is ZBX frontend with TimescaleDB when zooming in history/trends? does it query only those partitions, which contain data from zoomed interval (according to its WHERE condition?) - it sems, that TimescaleDB is not using PostgreSQL native concept called "constraint exclusion", but implements its own "range exclusion" https://blog.timescale.com/optimizing-queries-timescaledb-hypertables-with-partitions-postgresql-6366873a995d/

• I see, you do not mention customizing chunk_time_interval or chunk_size in https://www.zabbix.com/documentation/4.2/manual/appendix/install/timescaledb, are values in timescaledb.sql (1 day for history and trends) meant to be as generally recommended?

• I see, you are using set_adaptive_chunking() procedure, which is obsolete and should not be used, according to https://docs.timescale.com/v1.0/api ... does it mean, TimescaleDB is cancelling their concept of adaptive chunking?

ostry,

how efficient is ZBX frontend with TimescaleDB when zooming in history/trends? does it query only those partitions, which contain data from zoomed interval (according to its WHERE condition?)

The current implementation doesn't change the way the frontend queries historical data. However selects from chunked tables undergo TSDB range exclusion optimizations automatically so you may see performance improvements described in the post you mentioned.

are values in timescaledb.sql (1 day for history and trends) meant to be as generally recommended?

1 day is just a sensible default, not a universal recommendation. If you feel 1 day doesn't match your history data rates you can always change it by calling set_chunk_time_interval().

I see, you are using set_adaptive_chunking() procedure, which is obsolete and should not be used

Indeed, the script uses adaptive chunking to set up hyper-tables. TSDB has deprecated adaptive chunking very recently so I hope they are not removing this feature right away. In fact, their main site still lists adaptive chunking as TSDB's headline feature: "TimescaleDB can dynamically adapt the interval to optimize performance" (https://www.timescale.com/how-it-works)

does it mean, TimescaleDB is cancelling their concept of adaptive chunking?

From what I see - yes, at least for now:

Deprecate adaptive chunking.
The current implementation of adaptive chunking, as often employed by users, is a failed experiment.
...
we decided to deprecate it and its current API
altogether to ensure overall stability in the system, with the potential
to revisit/reimplement in the future

(Github)
But of course it's better to ask them directly

Follow ZBXNEXT-5013