[ZBX-19827] Slow query after restart zabbix-server Created: 2021 Aug 18  Updated: 2021 Dec 10  Resolved: 2021 Aug 20

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Server (S)
Affects Version/s: None
Fix Version/s: None

Type: Problem report Priority: Trivial
Reporter: GOID Assignee: Andrei Gushchin (Inactive)
Resolution: Won't fix Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Virtual Server (KVM) 8GB 4Core 2.1GHz
Ubuntu 20.04.2 LTS
5.4.0-54-generic #60-Ubuntu SMP Fri Nov 6 10:37:59 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux
+ pgbouncer 1.12

NVPS - 3000
Hosts - 1500
Store history - 30 days (global)
Store trends - 90 days (global)
Compression - ON

DB:
Cluster of two physical servers - 64GB 32Core 2.7GHz
Ubuntu 20.04.2 LTS
5.4.0-48-generic #52-Ubuntu SMP Thu Sep 10 10:58:49 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux
PostgreSQL 12.7 + timescaleDB 1.7.4


 Description   

After restart about 1 hour after - start log lines (see below). WEB GUI working but not informative - all triggers triggered

This is fresh installation. Initial version is 5.0.3 and few times updated to release 5.0.10. Problem happens on each release.

NO memory error logged, NO crashes of any component. NO increased RAM consumption

Almost same problem is - https://support.zabbix.com/browse/ZBX-16347 - but we not faced with crashes and query is executed correctly (no error in logs, just warnings)

Steps to reproduce:

  1. Restart zabbix-server service
  2. wait ~1 hour

Actions applied:

Restarts (with waiting when history\trends synced) help only for 1 hour - after that problem happens again.

I'am not sure but last action helps:

  1. kill processes of zabbix-server
  2. restart master\slave postgresql
  3. restart pgbouncer
  4. start zabbix-server
  5. wait ~1hour slow query logged but with less time (about 5 sec) - do not restart. just let him sync

Few lines from zabbix_server.log

 

3485356:20210817:230021.437 slow query: 15.185377 sec, "update trends_uint set num=114,value_min=0,value_avg=0,value_max=0 where itemid=1591199 and clock=1629226800;
update trends_uint set num=118,value_min=2147483645,value_avg=2147483645,value_max=2147483645 where itemid=761568 and clock=1629226800;
update trends_uint set num=118,value_min=34,value_avg=34,value_max=41 where itemid=735302 and clock=1629226800;
update trends_uint set num=59,value_min=127274725376,value_avg=192470241904,value_max=207835754496 where itemid=1069086 and clock=1629226800;
.....

Same moment in postgresql log

 

 

2021-08-17 23:00:21.428 MSK [188153] here_user@here_db LOG:  duration: 15174.317 ms  statement: update trends_uint set num=114,value_min=0,value_avg=0,value_max=0 where itemid=1591199 and clock=1629226800;
        update trends_uint set num=118,value_min=2147483645,value_avg=2147483645,value_max=2147483645 where itemid=761568 and clock=1629226800;
        update trends_uint set num=118,value_min=34,value_avg=34,value_max=41 where itemid=735302 and clock=1629226800;
        update trends_uint set num=59,value_min=127274725376,value_avg=192470241904,value_max=207835754496 where itemid=1069086 and clock=1629226800;

 

 Comments   
Comment by Andrei Gushchin (Inactive) [ 2021 Aug 20 ]

Thank you for reporting this issue.

Update trends happen if you restarting zabbix server in the middle of the hour or event after 5 min after beginning of the hour. Because of zabbix inserting already gathered trends into DB if you correctly stop it and when this hour ends it will update records.

When you kill zabbix server processes without stopping inserting trends not happen and in the beginning of next hour they just inserted which is lighter operation for DB with comparison with UPDATE.
So slow update query related to DB performance not to some zabbix issues.

So I will resolve this case as it not some zabbix bug. Please check our community resources to ask help.

Best regards,
Andrei

Comment by bunkzilla [ 2021 Dec 10 ]

What are the parameters regarding the restart? How many minutes before the end of the hour? The documentation really doesn't make it clear how best to do restarts or maintenance in order to avoid the UPDATE vs INSERT situation. It's the only thing that really taxes my database during normal operation of a zabbix cluster.

Generated at Sun Jun 15 14:10:57 EEST 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.