[ZBX-23012] Slow query from LLD worker Created: 2023 Jun 22  Updated: 2024 Apr 10  Resolved: 2023 Oct 22

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Proxy (P), Server (S)
Affects Version/s: 6.0.19rc1, 6.4.4rc1
Fix Version/s: 6.0.23rc1, 6.4.8rc1, 7.0.0alpha7, 7.0 (plan)

Type: Problem report Priority: Critical
Reporter: Edgar Akhmetshin Assignee: Konstantins Prutkovs (Inactive)
Resolution: Fixed Votes: 2
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

RHEl8
Database hardware:

  • 64 cores
  • 512GB of RAM
  • PostgreSQL 15.3
  • TimescaleDB 2.10.3
  • all-flash array
  • Zabbix 6.2

Attachments: PNG File Screenshot 2023-06-22 at 17.15.44.png     PNG File Screenshot 2023-06-22 at 20.10.29.png     File postgresql.conf     File zabbix-agent-6.2.9-0.ZBX23012.1.el8.x86_64.rpm     File zabbix-proxy-pgsql-6.2.9-0.ZBX23012.1.el8.x86_64.rpm     File zabbix-server-mysql-6.4.6-0.ZBX23012.el8.x86_64.rpm     File zabbix-server-mysql-6.4.6-0.ZBX23012.el9.x86_64.rpm     File zabbix-server-mysql_6.4.6-0.ZBX23012+debian12_amd64.deb     File zabbix-server-mysql_6.4.6-0.ZBX23012+ubuntu22.04_amd64.deb     File zabbix-server-pgsql-6.2.9-0.ZBX23012.1.el8.x86_64.rpm     File zabbix-server-pgsql-6.4.6-0.ZBX23012.el8.x86_64.rpm     File zabbix-server-pgsql-6.4.6-0.ZBX23012.el9.x86_64.rpm     File zabbix-server-pgsql_6.4.6-0.ZBX23012+debian12_amd64.deb     File zabbix-server-pgsql_6.4.6-0.ZBX23012+ubuntu22.04_amd64.deb     File zabbix-sql-scripts-6.2.9-0.ZBX23012.1.el8.noarch.rpm     Text File zbx_23012_6_4-1.patch     Text File zbx_23012_6_4.patch    
Issue Links:
Causes
causes ZBX-24236 Nested transaction detected in LLD Closed
Duplicate
is duplicated by ZBX-23335 LLD Worker Utilization Closed
Team: Team C
Sprint: Sprint 104 (Sep 2023), Sprint 105 (Oct 2023)
Story Points: 1

 Description   

Steps to reproduce:
Create discovery rule from the master SNMP item having 5 item prototypes, start monitor about 1000 hosts with this template, get 100% busy LLD workers. 

 item_discovery             | 26 GB          |      28250169344
 item_tag                   | 11 GB          |      11745476608

Result:

3691797:20230622:152622.129 query [txnlev:0] [select ip.item_parameterid,ip.itemid,ip.name,ip.value from item_discovery id join item_parameter ip on id.itemid=ip.itemid where id.parent_itemid in (9541102,9541103,9541104)]
3691797:20230622:152622.130 query [txnlev:0] [select it.itemtagid,it.itemid,it.tag,it.value from item_discovery id join item_tag it on id.itemid=it.itemid where id.parent_itemid in (9541102,9541103,9541104)]
3691797:20230622:152628.856 slow query: 6.726439 sec, "select it.itemtagid,it.itemid,it.tag,it.value from item_discovery id join item_tag it on id.itemid=it.itemid where id.parent_itemid in (9541102,9541103,9541104)"
3691797:20230622:152628.857 End of lld_items_get()
3691797:20230622:152628.857 In lld_items_make()

Expected:
Fast query execution.



 Comments   
Comment by Edgar Akhmetshin [ 2023 Sep 01 ]

zbx_23012_6_4-1.patch

zabbix-server-pgsql-6.4.6-0.ZBX23012.el8.x86_64.rpm
zabbix-server-mysql-6.4.6-0.ZBX23012.el8.x86_64.rpm
zabbix-server-pgsql-6.4.6-0.ZBX23012.el9.x86_64.rpm
zabbix-server-mysql-6.4.6-0.ZBX23012.el9.x86_64.rpm
zabbix-server-pgsql_6.4.6-0.ZBX23012+ubuntu22.04_amd64.deb
zabbix-server-mysql_6.4.6-0.ZBX23012+ubuntu22.04_amd64.deb
zabbix-server-pgsql_6.4.6-0.ZBX23012+debian12_amd64.deb
zabbix-server-mysql_6.4.6-0.ZBX23012+debian12_amd64.deb

Or workaround for PgSQL based instances:

alter system set enable_seqscan=OFF;
select pg_reload_conf();
Comment by Steve [ 2023 Sep 04 ]

Hi Edgar,

Did this make it into 6.4.6? I don't see it in the release notes Release Notes for Zabbix 6.4.6

Comment by Edgar Akhmetshin [ 2023 Sep 20 ]

[email protected]

Did this make it into 6.4.6? I don't see it in the release notes Release Notes for Zabbix 6.4.6

Users can see the status of ZBX and Fix version is empty with status 'Need info'.

Currently the solution is tested internally, and if you have feedback regarding the shared package with a patch - provide it, if you need Docker images (ZBX-23335) with this fix without waiting for release - you can compile it yourself, example:

git clone https://github.com/zabbix/zabbix-docker.git

cd zabbix-docker

git checkout 6.0

docker-compose -f docker-compose_v3_alpine_pgsql_local.yaml build --build-arg ZBX_VERSION="feature/ZBX-23012-6.4" --parallel

docker-compose -f docker-compose_v3_alpine_pgsql_local.yaml up -d

If rebuilding is not a option for you - we can offer paid services to prepare patched containers in priority for you.

Comment by Vladislavs Sokurenko [ 2023 Sep 28 ]

Looks like the reason is that index does not favour low level discovery:

"item_discovery_1" UNIQUE, btree (itemid, parent_itemid)

But condition is to select itemids where parent_itemid=<id>
For example:

CREATE UNIQUE INDEX item_discovery_3 ON item_discovery (parent_itemid,itemid);
CREATE INDEX item_discovery_4 ON item_discovery (itemid);
drop index item_discovery_1; 
drop index item_discovery_2;
Comment by Konstantins Prutkovs (Inactive) [ 2023 Oct 18 ]

Available in versions:

Generated at Sun Jun 08 08:26:02 EEST 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.