ZBX-20506-5.0.diff
ZBX-20506-sync-all-trigers-trigger_tags (1).diff
|
Causes |
Team A
[ZBX-20506] Slow queries when running zbx_dbsync_compare_trigger_tags Created: 2022 Jan 28 Updated: 2024 Apr 10 Resolved: 2022 Apr 21 |
|
| Status: | Closed |
| Project: | ZABBIX BUGS AND ISSUES |
| Component/s: | Server (S) |
| Affects Version/s: | 5.4.9 |
| Fix Version/s: | 6.0.1rc1, 6.2.0alpha1, 6.2 (plan) |
| Type: | Problem report | Priority: | Trivial |
| Reporter: | Yurii Polenok | Assignee: | Vladislavs Sokurenko |
| Resolution: | Fixed | Votes: | 0 |
| Labels: | database, sync | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Environment: |
postgres 13.5 and timescale 2.1.0 Number of hosts (enabled/disabled) 13957 13847 / 110 |
||
| Attachments: |
ZBX-20506-5.0.diff
ZBX-20506-sync-all-trigers-trigger_tags (1).diff
|
||||
| Issue Links: |
|
||||
| Team: | Team A |
||||
| Sprint: | Sprint 85 (Feb 2022), Sprint 86 (Mar 2022), Sprint 87 (Apr 2022) | ||||
| Story Points: | 1 | ||||
| Description |
|
Steps to reproduce:
Result: slow query: 3.816762 sec, "select distinct tt.triggertagid,tt.triggerid,tt.tag,tt.value from trigger_tag tt,triggers t,hosts h,items i,functions f where t.triggerid=tt.triggerid and t.flags<>2 and h.hostid=i.hostid and i.itemid=f.itemid and f.triggerid=tt.triggerid and h.status in (0,1)" HashAggregate (cost=268197.80..324806.64 rows=1268142 width=50) (actual time=2860.942..3699.744 rows=1147489 loops=1) Its consuming 50MB of mem and writes a temp file to disk for ~120MB each time. It is unlikely that this can be called a bug, but perhaps this query can be optimized. Expected: |
| Comments |
| Comment by Vladislavs Sokurenko [ 2022 Feb 01 ] |
|
Thank you for your report, is it possible for you to test the patch ZBX-20506-5.0.diff |
| Comment by Yurii Polenok [ 2022 Feb 01 ] |
|
Unfortunately not possible. Our production environment is quite stable and does not allow for tests. |
| Comment by Alex Kalimulin [ 2022 Feb 04 ] |
|
Tests show that on systems with big number of triggers and trigger counts there may be 2x config sync speed increase at cost of extra 15% memory consumption in config cache. Let's do this optimization. The change needs to be tested on all supported databases before merging. |
| Comment by Vladislavs Sokurenko [ 2022 Feb 17 ] |
|
Implemented in:
Updated documentation: |
| Comment by Ryan Eberly [ 2022 Feb 24 ] |
|
I don't suppose this could be merged/cherry picked into 5.4.11? I'll be taking that release for the fixes in |
| Comment by Vladislavs Sokurenko [ 2022 Feb 25 ] |
|
Unfortunately it cannot be simply cherry-picked to 5.4.11, also 5.4 is non LTS release and support ends soon. |
| Comment by Ryan Eberly [ 2022 Feb 25 ] |
|
Ok. I should at least be able to test your updated query from the code in a MySQL shell though. Will move to 6.0 come April during our next 3 month release schedule. |
| Comment by Ryan Eberly [ 2022 Feb 25 ] |
|
On Mysql 5.7.34, using the raw sql query changes from the "
select triggerid,description,expression,error,priority,type,value,state,lastchange,status,recovery_mode,recovery_expression,correlation_mode,correlation_tag,opdata,event_name,null,null,null,flags from triggers 1956505 rows in set (3.22 sec) Resident Set Size = 971026
select triggertagid,triggerid,tag,value from trigger_tag 2480973 rows in set (1.08 sec) Resident Set Size = 243204
Previous query SELECTs would take ~66 and 43 seconds, respectively. I didn't benchmark memory.
|
| Comment by Vladislavs Sokurenko [ 2022 May 02 ] |
|
Thanks for the information, please let us know the results when you upgrade |
