|
Duplicate |
|||
| is duplicated by |
|
Zabbix should stop to attempt DB upgr... | Closed |
Team C
[ZBX-12895] 3.2 to 3.4 update very slow on large events table (inoptimal DB usage) Created: 2017 Oct 18 Updated: 2024 Apr 10 Resolved: 2017 Nov 18 |
|
| Status: | Closed |
| Project: | ZABBIX BUGS AND ISSUES |
| Component/s: | Server (S) |
| Affects Version/s: | 3.4.3 |
| Fix Version/s: | 3.4.5rc1, 4.0.0alpha1, 4.0 (plan) |
| Type: | Problem report | Priority: | Minor |
| Reporter: | Alexey Asemov | Assignee: | Sergejs Paskevics |
| Resolution: | Fixed | Votes: | 0 |
| Labels: | database, performance, update | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Environment: |
CentOS 7, MariaDB 10.1.23 |
||
| Issue Links: |
|
||||||||
| Team: | Team C |
||||||||
| Sprint: | Sprint 20, Sprint 21 | ||||||||
| Story Points: | 2 | ||||||||
| Description |
|
At some point of the database upgrade from 3.2 to 3.4, Zabbix Server starts running queries like that for each event ID: update alerts set p_eventid=53850802 where eventid=53850827 While there is only ~60K records in alerts table, update takes hours because it runs queries for all the 87 million event IDs one by one (99+% of which do not exist in alerts table at all). Batching this update using some JOIN query would do much good there. If impossible, then preloading alerts table as well and updating only really affected alerts would be sane. |
| Comments |
| Comment by Alexey Asemov [ 2017 Oct 18 ] |
|
Even just running it one by one in batched transactions will do some good I think, because it autocommits for each query now and that should be primary reason for slowness. |
| Comment by Andris Zeila [ 2017 Oct 18 ] |
|
It's actually for each recovery event, so 1/2 of events or less. But yes, alerts table in most cases will have much less records than event_recovery, so it would make sense to preload (or in worst case iterate through) it. |
| Comment by Sergejs Paskevics [ 2017 Nov 09 ] |
|
Fixed in svn://svn.zabbix.com/branches/dev/ZBX-12895 |
| Comment by Vladislavs Sokurenko [ 2017 Nov 16 ] |
|
Successfully tested. |
| Comment by Sergejs Paskevics [ 2017 Nov 16 ] |
|
Fixed in:
|