[ZBX-20932] Slow SQL query for getting problems Created: 2022 Apr 21  Updated: 2024 Oct 11

Status: Confirmed
Project: ZABBIX BUGS AND ISSUES
Component/s: Frontend (F), Server (S)
Affects Version/s: 6.0.2, 6.0.3
Fix Version/s: None

Type: Problem report Priority: Trivial
Reporter: Adawid Assignee: Zabbix Development Team
Resolution: Unresolved Votes: 2
Labels: SQL, frontend
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Stack: 1 server with zabbix-web and zabbix-server, for DB I use postgresql (13.2) and timescaleDB (2.4.0) in a patroni cluster with consul.

OS: Ubuntu 20.04.2 LTS
RAM:
Server: 8G
DB: 10G per node
CPU:
Server: 4 vCPU
DB: 8vCPU

Zabbix:
Version 6.0.3
Number of hosts (enabled/disabled) 1311 810 / 501
Number of templates 208
Number of items (enabled/disabled/not supported) 79341 54490 / 22307 / 2544
Number of triggers (enabled/disabled [problem/ok]) 40136 29112 / 11024 [851 / 28261]
Required server performance, new values per second 759.09
Current problems : 262

Attachments: Text File SQL_ANALYZE.txt     Text File psql-conf.txt    

 Description   

We use Zabbix to notify an external dashboard. We send notifications every 5 minutes until the event is closed.I have old event with 1000+ actions.When I tried to see all current problems, to total sql time is greater than 200 sec

Steps to reproduce:

  1. Go to the monitoring > Problems 
    *Result:*
******************** Script profiler ********************
Total time: 229.407166
Total SQL time: 229.087297
SQL count: 1453 (selects: 774 | executes: 691)
Peak memory usage: 34M
Memory limit: 512M
 
SQL (200.412189): 
SELECT COUNT(*) AS rowscount,a.eventid FROM alerts a WHERE EXISTS (SELECT NULL FROM actions aa WHERE a.actionid=aa.actionid AND aa.eventsource='0') AND a.eventid IN (3529764,3529873,3529875,3560887,3589542,3601809,3601815,3601819,3601820,3601821,3601824,3601841,3601850,3610947,3610948,3610950,3610951,3610954,3610955,3610957,3614349,3615811,3646525,3646533,3646554,3646733,3646737,3653434,3653438,3653594,3653599,3653605,3653826,3653834,3653835,3653849,3653854,3653857,3653858,3653862,3653870,3653871,3653872,3653879,3653883,3653900,3653926,3653936,3653949,3653965,3653979,3654166,3654169,3654177,3654179,3654181,3654185,3654188,3654194,3654199,3654202,3654205,3654209,3654211,3654219,3695041,3695052,3695054,3695062,3695073,3695087,3695099,3695253,3695320,3695567,3695568,3695569,3695570,3695572,3698324,3698331,3712695,3718317,3718323,3753468,3753529,3753748,3755376,3763184,3763200,3763201,3763213,3763223,3763233,3763557,3763560,3763561,3763565,3763569,3763575,3763576,3763581,3763582,3763592,3763593,3763603,3763628,3763864,3763900,3763906,3777672,3819506,3820613,3862875,3862958,3863527,3863528,3864267,3870351,4018743,4018997,4108455,4115714,4303340,4504058,4555618,4589979,4591324,4591554,4591651,4592106,4607541,4616596,4619565,4708825,4708945,4711388,4720657,4751732,4751745,4752648,4753353,4753354,4778674,4821761,4880979,4880988,4880990,4882066,4882067,4882068,4887041,4887043,4887067,4887068,4936352,4954130,5007985,5020710,5021153,5021499,5021668,5021714,5021799,5021812,5022132,5026468,5042064,5081539,5120766,5126734,5155512,5187443,5191046,5191770,5228254,5232218,5233902,5262583,5291480,5293166,5293991,5294069,5294995,5295054,5297881,5298002,5298746,5302504,5305133,5312568,5320610,5322427,5323825,5331510,5331892,5332317,5332850,5333140,5333688) GROUP BY a.eventid
zabbix.php:22 → require_once() → ZBase->run() → ZBase->processRequest() → ZBase->processResponseFinal() → CView->getOutput() → include() → CScreenProblem->get() → CScreenProblem::makeData() → getEventsActionsIconsData() → getEventsAlertsOverview() → CApiWrapper->__call(


 Comments   
Comment by Edgar Akhmetshin [ 2022 Apr 25 ]

Hello,

Please provide information about PgSQL configuration and hardware used.

And execute the following SQL, (output should be attached to the issue):

EXPLAIN ANALYZE SELECT COUNT(*) AS rowscount,a.eventid FROM alerts a WHERE EXISTS (SELECT NULL FROM actions aa WHERE a.actionid=aa.actionid AND aa.eventsource='0') AND a.eventid IN (3529764,3529873,3529875,3560887,3589542,3601809,3601815,3601819,3601820,3601821,3601824,3601841,3601850,3610947,3610948,3610950,3610951,3610954,3610955,3610957,3614349,3615811,3646525,3646533,3646554,3646733,3646737,3653434,3653438,3653594,3653599,3653605,3653826,3653834,3653835,3653849,3653854,3653857,3653858,3653862,3653870,3653871,3653872,3653879,3653883,3653900,3653926,3653936,3653949,3653965,3653979,3654166,3654169,3654177,3654179,3654181,3654185,3654188,3654194,3654199,3654202,3654205,3654209,3654211,3654219,3695041,3695052,3695054,3695062,3695073,3695087,3695099,3695253,3695320,3695567,3695568,3695569,3695570,3695572,3698324,3698331,3712695,3718317,3718323,3753468,3753529,3753748,3755376,3763184,3763200,3763201,3763213,3763223,3763233,3763557,3763560,3763561,3763565,3763569,3763575,3763576,3763581,3763582,3763592,3763593,3763603,3763628,3763864,3763900,3763906,3777672,3819506,3820613,3862875,3862958,3863527,3863528,3864267,3870351,4018743,4018997,4108455,4115714,4303340,4504058,4555618,4589979,4591324,4591554,4591651,4592106,4607541,4616596,4619565,4708825,4708945,4711388,4720657,4751732,4751745,4752648,4753353,4753354,4778674,4821761,4880979,4880988,4880990,4882066,4882067,4882068,4887041,4887043,4887067,4887068,4936352,4954130,5007985,5020710,5021153,5021499,5021668,5021714,5021799,5021812,5022132,5026468,5042064,5081539,5120766,5126734,5155512,5187443,5191046,5191770,5228254,5232218,5233902,5262583,5291480,5293166,5293991,5294069,5294995,5295054,5297881,5298002,5298746,5302504,5305133,5312568,5320610,5322427,5323825,5331510,5331892,5332317,5332850,5333140,5333688) GROUP BY a.eventid

Regards,
Edgar

Comment by Adawid [ 2022 Apr 25 ]

Hi Edgar,

In attachment the result of the SQL query and the configuration of postgresql.

The db is hosted on a VM (datastore are using SSD).

Regards,
Adawid

Comment by Adawid [ 2024 Oct 11 ]

Hi,

After a reflection of me, I imagined 2 options:
The first idea is to add a different retention of 'alert' who can be different from events retention.
Currently, alerts are purged when events are clean by Housekeeper right?
This options means a modification of Housekeeper process.

The second idea is to rework the storage of alerts, we can store only:

  • The first 
  • The last
  • The number of successes between 2 events (ex: number between the fist action and error and between the error and the last)
  • Errors

I don't know the impact on the product but if it can help you, that's great

Regards,

Adawid

Generated at Sun Jun 15 13:48:12 EEST 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.