Upgrade from 6.0 to 6.2 | High/100% MariaDB CPU Usage
Server Specs:
OS: Debian 11
Single VM on VMWare
4 CPU Cores
15.6 GiB of RAM
Zabbix server/web/DB all on same VM
VM hosts cacti that uses MariaDB as well.
/var/lib/mysql is a mount on a separate disk than root
Zabbix Info:
Started at version 5.0
Version: 6.2.1
# of Hosts: 4447
# of Items: 37893
# of Triggers: 15662
NVPS: 711.75
Recently upgraded from 6.0.x to 6.2.0 last Monday.
Immediately after upgrading I noticed that there was high CPU usage coming from MariaDB(two cores at 100%)
Looking in Zabbix at the CPU Util for the server I noticed that usage doubled from what it was before.
Running "show processlist;" in MariaDB while it was spiking did not show any "stuck" queries or even very many at al.
I then did some research and added "innodb_buffer_pool_size = 11G" to my 50-sever.cnf file. That did take down the usage tremendously when completely idle.
However when navigating the site it will spike a core up to 100% and sometimes completely spike all 4 cores to 100
Although now I do see stuck queries in the DB.
When navigating to -Monitoring-Hosts the query that gets stuck is: (spikes one core)
When navigating to -Monitoring-"Zabbix server health" Dashboard 6 identical querys get stuck:
Things I have tried:
Waiting a day to see if it would calm down.
Adding more memory to innodb buffer pool.
Restarting Zabbix services/entire VM
Upgraded to 6.2.1
Performed a mariadbcheck
Database upgrade to primary keys schema(did not complete this when upgrading from 5.4 to 6.0)
OS: Debian 11
Single VM on VMWare
4 CPU Cores
15.6 GiB of RAM
Zabbix server/web/DB all on same VM
VM hosts cacti that uses MariaDB as well.
/var/lib/mysql is a mount on a separate disk than root
Zabbix Info:
Started at version 5.0
Version: 6.2.1
# of Hosts: 4447
# of Items: 37893
# of Triggers: 15662
NVPS: 711.75
Recently upgraded from 6.0.x to 6.2.0 last Monday.
Immediately after upgrading I noticed that there was high CPU usage coming from MariaDB(two cores at 100%)
Looking in Zabbix at the CPU Util for the server I noticed that usage doubled from what it was before.
Running "show processlist;" in MariaDB while it was spiking did not show any "stuck" queries or even very many at al.
I then did some research and added "innodb_buffer_pool_size = 11G" to my 50-sever.cnf file. That did take down the usage tremendously when completely idle.
However when navigating the site it will spike a core up to 100% and sometimes completely spike all 4 cores to 100
Although now I do see stuck queries in the DB.
When navigating to -Monitoring-Hosts the query that gets stuck is: (spikes one core)
Code:
SELECT DISTINCT t.triggerid FROM triggers t,functions f,items i WHERE i.hostid IN (12455,[I][B][-SNIP 498 more numbers-][/B][/I],15051) AND f.triggerid=t.triggerid AND f.itemid=i.itemid AND NOT EXISTS (SELECT NULL FROM functions f,items i,hosts h WHERE t.triggerid=f.triggerid AND f.itemid=i.itemid AND i.hostid=h.hostid AND (i.status<>0 OR h.status<>0)) AND t.status=0 AND t.flags IN (0,4)
Code:
SELECT DISTINCT t.triggerid FROM triggers t,functions f,items i WHERE i.hostid=10084 AND f.triggerid=t.triggerid AND f.itemid=i.itemid AND NOT EXISTS (SELECT NULL FROM functions f,items i,hosts h WHERE t.triggerid=f.triggerid AND f.itemid=i.itemid AND i.hostid=h.hostid AND (i.status<>0 OR h.status<>0)) AND t.status=0 AND t.flags IN (0,4)
Waiting a day to see if it would calm down.
Adding more memory to innodb buffer pool.
Restarting Zabbix services/entire VM
Upgraded to 6.2.1
Performed a mariadbcheck
Database upgrade to primary keys schema(did not complete this when upgrading from 5.4 to 6.0)