[ZBX-23688] Do not run heavy queries to history tables for new items for trigger calculation Created: 2023 Nov 09  Updated: 2024 May 17  Resolved: 2024 Feb 12

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Server (S)
Affects Version/s: 6.0.23, 6.4.8
Fix Version/s: 6.0.26rc1, 6.0.27rc1, 6.4.12rc1, 7.0.0beta2, 7.0 (plan)

Type: Problem report Priority: Trivial
Reporter: Elina Kuzyutkina (Inactive) Assignee: Andris Zeila
Resolution: Fixed Votes: 1
Labels: trigger, valuecache
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: File ZBX-23688-6.0.diff     File ZBX-23688-6.4.diff    
Issue Links:
Duplicate
duplicates ZBX-10059 Zabbix Server Caching Issue of New Un... Closed
is duplicated by ZBX-8042 On server startup, the value cache be... Closed
Sub-task
part of ZBX-23973 Avoid retrieving trends from database... Closed
Team: Team A
Sprint: Sprint candidates, S2401-2
Story Points: 1

 Description   

Hi,
If is there are at least 2 functions in the trigger expression where one is nodata() and another one is historical one (last(), find(),...) Zabbix recalculates trigger in ~ 30 sec when it appears in the cache (because of nodata()) and to check second part of the expression (last(), find(), ...) it searches value. If there is no data in the cache history syncer goes to DB to check if there is some. it will try to find in the lates (for example last 1 hour) data first, then takes older and bigger time periods to search
But if host\item is new -there is no data in DB as well. Also Zabbix has marker to 'recognize' that item is new in the cache - so these queries can not be run for such items



 Comments   
Comment by Vladislavs Sokurenko [ 2024 Jan 18 ]

To sum up the issue, in this case the problem occurs when there is nodata() timer and last function used together for example:
last(/Zabbix server/trap1)=0 or nodata(/Zabbix server/trap1,30m)=1
This results in following queries to database when timer is calculated:

102166:20240118:160125.501 query [txnlev:1] [select clock,ns,value from history_uint where itemid=46404 and clock>1705586485]
102166:20240118:160125.501 End of zbx_history_get_values():SUCCEED values:0
102166:20240118:160125.502 In zbx_history_get_values() itemid:46404 value_type:3 start:0 count:2 end:1705586485
102166:20240118:160125.503 query [txnlev:1] [select clock,ns,value from history_uint where itemid=46404 and clock<=1705586485 and clock>1705582885 order by clock desc limit 2]
102166:20240118:160125.505 query [txnlev:1] [select clock,ns,value from history_uint where itemid=46404 and clock<=1705582885 and clock>1705539685 order by clock desc limit 2]
102166:20240118:160125.506 query [txnlev:1] [select clock,ns,value from history_uint where itemid=46404 and clock<=1705539685 and clock>1705453285 order by clock desc limit 2]
102166:20240118:160125.507 query [txnlev:1] [select clock,ns,value from history_uint where itemid=46404 and clock<=1705453285 and clock>1705366885 order by clock desc limit 2]
102166:20240118:160125.507 query [txnlev:1] [select clock,ns,value from history_uint where itemid=46404 and clock<=1705366885 and clock>1704762085 order by clock desc limit 2]
102166:20240118:160125.508 query [txnlev:1] [select clock,ns,value from history_uint where itemid=46404 and clock<=1704762085 and clock>1702170085 order by clock desc limit 2]
102166:20240118:160125.509 query [txnlev:1] [select clock,ns,value from history_uint where itemid=46404 and clock<=1702170085 order by clock desc limit 2]
 

As you see it attempts to find in an hour then days, week, month and everything older then a month in the end.
We need somehow to limit this in our case to 30 minutes same as nodata.
Possible solution for all versions:
https://support.zabbix.com/browse/ZBX-23688

Another possible solution is configuring this limit in function or even globally.

Other things to consider is change function that needs 2 values

Comment by Andris Zeila [ 2024 Jan 26 ]

Patch for 6.0 ZBX-23688-6.0.diff

Comment by Vladislavs Sokurenko [ 2024 Jan 30 ]

Also see ZBX-10059

Comment by Andris Zeila [ 2024 Feb 02 ]

Released ZBX-23688 in:

  • pre-6.0.27rc1 5a909873ebd
  • pre-6.4.12rc1 eb48d8e3188
  • pre-7.0.0beta2 049195ae313
Generated at Wed Mar 05 05:00:13 EET 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.