image-2021-08-10-12-02-02-841.png
[ZBX-19792] Slow query Zabbix Update 4.4 --> 5.4 Created: 2021 Aug 10 Updated: 2023 Jan 19 |
|
| Status: | Confirmed |
| Project: | ZABBIX BUGS AND ISSUES |
| Component/s: | Server (S) |
| Affects Version/s: | 5.4.2 |
| Fix Version/s: | None |
| Type: | Problem report | Priority: | Critical |
| Reporter: | Alexis EGGER | Assignee: | Zabbix Development Team |
| Resolution: | Unresolved | Votes: | 4 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Attachments: |
image-2021-08-10-12-02-02-841.png
|
| Description |
|
Steps to reproduce:
Result:
970:20210730:124312.627 completed 99% of database upgrade 970:20210730:130245.232 slow query: 1172.484390 sec, "select distinct i2.key_ from items i join item_discovery id on id.itemid=i.itemid join items i2 on id.parent_itemid=i2.itemid join functions f on i.itemid=f.itemid and f.triggerid=XXXX2 where i.flags=2" 970:20210730:132219.251 slow query: 1174.017700 sec, "select distinct i2.key_ from items i join item_discovery id on id.itemid=i.itemid join items i2 on id.parent_itemid=i2.itemid join functions f on i.itemid=f.itemid and f.triggerid=XXXX3 where i.flags=2" 970:20210730:134144.385 slow query: 1165.132978 sec, "select distinct i2.key_ from items i join item_discovery id on id.itemid=i.itemid join items i2 on id.parent_itemid=i2.itemid join functions f on i.itemid=f.itemid and f.triggerid=XXXX4 where i.flags=2" Because of this the DB does not upgrade completely and therefore Zabbix has a huge downtime (in our case weeks for 28000 triggers).
Expected: My SQL is a bit rusty but I'm pretty sure that it is possible to do better than a double join query to get the wanted info (I might be wrong though). But still almost half an hour for a select seems wrong to me.
MariaDB [DBZBX]> SELECT DISTINCT i2.key_ FROM items i2 WHERE i2.itemid IN (SELECT id.parent_itemid FROM item_discovery id WHERE id.itemid IN (SELECT i.itemid FROM items i WHERE i.itemid IN (SELECT f.itemid FROM functions f WHERE f.triggerid=XXXX6) AND i.flags=2)); +------------------+ | key_ | +------------------+ | memory.discovery | +------------------+ 1 row in set (0.010 sec) MariaDB [DBZBX]> select distinct i2.key_ from items i join item_discovery id on id.itemid=i.itemid join items i2 on id.parent_itemid=i2.itemid join functions f on i.itemid=f.itemid and f.triggerid=XXXX6 where i.flags=2; +------------------+ | key_ | +------------------+ | memory.discovery | +------------------+ 1 row in set (23 min 25.798 sec) It might be interesting to fix this before the next LTS Release. I hope that my Issue is well written enough if not I can rewrite it with more / less info. Contact me if you need any extra info. |
| Comments |
| Comment by Alexis EGGER [ 2021 Aug 10 ] |
|
To clarify in the query example the first one is a similar query written by myself without joins and the second one is the original one.
The exact line in code for this query is https://fossies.org/linux/zabbix/src/libs/zbxdbupgrade/dbupgrade_5030.c line 7188 |
| Comment by Daniel Pogač [ 2021 Nov 08 ] |
|
Any updates on this? Upgrate of existing installation is unusable because of slow query. |
| Comment by Marcel Turansky [ 2022 Mar 29 ] |
|
is planned to fix in v5.4.12? |
| Comment by Sebastian [ 2022 Aug 24 ] |
|
what is you DB application version? |
| Comment by Marcel Turansky [ 2022 Aug 24 ] |
|
found this problem on mariadb 10.5.8-9(rocky linux 8) but resolve problem with fresh-install zabbix 5.4. But on few zabbix instances will be test zabbix upgrade, for safe... |
| Comment by Alexis EGGER [ 2022 Aug 24 ] |
|
@Sebastian I used MariaDB 10.4.20 (I believe it was running on Debian 10). As for the version of the client library (if there is one) used by Zabbix I sincerely don't know. You might want to check directly within the code of Zabbix.
@Marcel Turansky Yes one way of avoiding the problem is, indeed, to go for a fresh install but some people can't afford the dataloss nor the time required to set up again the items / triggers / graphs. Which is why I opened the issue to make sure it would not appear during the migration to the next LTS (which is when most users will face the problem).
This issue was opened a year ago so I do not have access to all the details anymore but I am pretty sure that rewriting the SQL query in the C file responsible for the migration should be enough. I might try to send a pull request one of these days to finally close the problem. |
| Comment by Daniel Pogač [ 2023 Jan 19 ] |
|
@Alexis EGGER |
