Twice A Day Performance Degredation for 5-10 minutes

[ROI]

Hi guys,

First time poster here. I've spent about a week trying to figure this out now. Long story short every 12 hours, Zabbix locks up while a massive amount of reads are done on the database. I've been unable to determine why or what would cause this. My first suspicion was housekeeper as from what I've read that's a common culprit. It seemed weird that this isn't happening every time housekeeper ran, but just once every 12 hours. Anyways, I disabled housekeeper temporarily for history and trends to test, and the problem continues.

I'll try to post everything that may be relevant, but feel free to ask for anything else I can provide.

CentOS 8.3.2011 VM on ESXi
4 CPU cores
8 GB RAM
200 GB drive on SSD backed SAN Datastore

Zabbix/MySQL Versions:

[FONT=monospace][COLOR=#000000]$ dnf list installed | grep zabbix [/COLOR]
fping.x86_64                                3.16-1.el8                              @[COLOR=#ff5454][B]zabbix[/B][/COLOR][COLOR=#000000]-non-supported [/COLOR]
[COLOR=#ff5454][B]zabbix[/B][/COLOR][COLOR=#000000]-agent.x86_64                         5.2.4-1.el8                             @[/COLOR][COLOR=#ff5454][B]zabbix[/B][/COLOR][COLOR=#000000]               [/COLOR]
[COLOR=#ff5454][B]zabbix[/B][/COLOR][COLOR=#000000]-apache-conf.noarch                   5.2.4-1.el8                             @[/COLOR][COLOR=#ff5454][B]zabbix[/B][/COLOR][COLOR=#000000]               [/COLOR]
[COLOR=#ff5454][B]zabbix[/B][/COLOR][COLOR=#000000]-release.noarch                       5.2-1.el8                               @System               [/COLOR]
[COLOR=#ff5454][B]zabbix[/B][/COLOR][COLOR=#000000]-server-mysql.x86_64                  5.2.4-1.el8                             @[/COLOR][COLOR=#ff5454][B]zabbix[/B][/COLOR][COLOR=#000000]               [/COLOR]
[COLOR=#ff5454][B]zabbix[/B][/COLOR][COLOR=#000000]-web.noarch                           5.2.4-1.el8                             @[/COLOR][COLOR=#ff5454][B]zabbix[/B][/COLOR][COLOR=#000000]               [/COLOR]
[COLOR=#ff5454][B]zabbix[/B][/COLOR][COLOR=#000000]-web-deps.noarch                      5.2.4-1.el8                             @[/COLOR][COLOR=#ff5454][B]zabbix[/B][/COLOR][COLOR=#000000]               [/COLOR]
[COLOR=#ff5454][B]zabbix[/B][/COLOR][COLOR=#000000]-web-mysql.noarch                     5.2.4-1.el8                             @[/COLOR][COLOR=#ff5454][B]zabbix[/B][/COLOR][COLOR=#000000]               [/COLOR]
$ dnf list installed | grep mariadb
[COLOR=#ff5454][B]mariadb[/B][/COLOR][COLOR=#000000].x86_64                              3:10.3.27-3.module_el8.3.0+599+c587b2e7 @appstream            [/COLOR]
[COLOR=#ff5454][B]mariadb[/B][/COLOR][COLOR=#000000]-backup.x86_64                       3:10.3.27-3.module_el8.3.0+599+c587b2e7 @appstream            [/COLOR]
[COLOR=#ff5454][B]mariadb[/B][/COLOR][COLOR=#000000]-common.x86_64                       3:10.3.27-3.module_el8.3.0+599+c587b2e7 @appstream            [/COLOR]
[COLOR=#ff5454][B]mariadb[/B][/COLOR][COLOR=#000000]-connector-c.x86_64                  3.1.11-2.el8_3                          @appstream            [/COLOR]
[COLOR=#ff5454][B]mariadb[/B][/COLOR][COLOR=#000000]-connector-c-config.noarch           3.1.11-2.el8_3                          @appstream            [/COLOR]
[COLOR=#ff5454][B]mariadb[/B][/COLOR][COLOR=#000000]-errmsg.x86_64                       3:10.3.27-3.module_el8.3.0+599+c587b2e7 @appstream            [/COLOR]
[COLOR=#ff5454][B]mariadb[/B][/COLOR][COLOR=#000000]-gssapi-server.x86_64                3:10.3.27-3.module_el8.3.0+599+c587b2e7 @appstream            [/COLOR]
[COLOR=#ff5454][B]mariadb[/B][/COLOR][COLOR=#000000]-server.x86_64                       3:10.3.27-3.module_el8.3.0+599+c587b2e7 @appstream            [/COLOR]
[COLOR=#ff5454][B]mariadb[/B][/COLOR][COLOR=#000000]-server-utils.x86_64                 3:10.3.27-3.module_el8.3.0+599+c587b2e7 @appstream[/COLOR][/FONT]

I'm attaching various graphs from Zabbix that show what's going on both over the course of a couple days, and then in detail during the issue. Every 12ish hours, there's a massive spike in read I/O for 5-10 minutes that causes nodata() triggers to trip and the WebUI to become unresponsive. Zabbix logs also show slow insert operations to the database. The Zabbix process pegged during this is history syncer. From my understanding history syncer should be writing history to the database however and not reading.

The database is 27 G

[FONT=monospace][COLOR=#000000]$ sudo du -sh * /var/lib/mysql | grep zabbix   [/COLOR]
27G     [COLOR=#ff5454][B]zabbix[/B][/COLOR][/FONT]

Performance is absolutely fine except during these events. Does history syncer do something every 12 hours it isn't doing all the time? I was considering partitioning the database, but we'd like to keep different item values in history for different periods, which I understand is not an option with partitioning. After disabling housekeeper and the same problem happening I'm skeptical that's the issue anyways.

I don't think it's relevant, but just in case, this started happening on Jan 18th, which was after an upgrade of various packages (see attached "OS Upgrade on 18th.txt" for dnf/yum history of update).

Also attaching MySQL log, which definitely shows contention during the event. Zabbix is the only database present.

I would appreciate any help or insight on things to try.

Thanks

[Hamardaban]

It is most likely that such a periodic load is given by LLD processes. Check and change the period.

[ROI]

I checked all templates with Discovery rules. None were set for 12 hours. They were all either 1 hour or 24 hours. I bumped the 1 hour up to every 4 hours.

I have searched quite a bit for this, but have just found a few new threads revolving around database issues after updating:

https://www.zabbix.com/forum/zabbix-troubleshooting-and-problems/416036-mysql-error-fatal-innodb-semaphore-wait-has-lasted-600-seconds

https://www.zabbix.com/forum/zabbix-help/413603-mariadb-upgrade-10-4-15-10-4-17-innodb-semaphore-wait-issues-mariadb-restarts

https://www.zabbix.com/forum/zabbix-troubleshooting-and-problems/413906-zabbix-server-locks-up-when-starts-syncing-history-after-housekeeper

I'm going to try and revert mariadb back to 10.3.17 (was upgraded to 10.3.27 on the 18th). Doesn't look like the old version is present in the repos anymore though, so I won't be able to use dnf downgrade.

I will update once I get it downgraded and see if that resolves the issue.

[barnettd]

I had a very similar issue. Zabbix server is installed via the repo and running on Debian 10. After mariadb updated to 10.3.27 the database crashed almost daily. I was able to downgrade mariadb to 10.3.25 and it completely resolved the issue.

[ROI]

Thanks for the feedback barnettd. Glad it's not just me. I was able to downgrade back to 10.3.17 on 2/4, and as you can see on the attached screenshot for disk utilization on the Zabbix server it resolved the issue. No more spikes up to 100% where the disk was being thrashed. It must be a new bug in 10.3.27 or an issue with the Zabbix server and the new mariadb version specifically.

Based on your info and the other posts I found I'll probably upgrade to 10.3.25 as that seems to work for everyone and is newer than 10.3.17.

Hopefully this helps other people experiencing the same issue.