Zabbix 6.0-1 / MariaDB high CPU load

[normaL]

Hello everyone,

I'm having an Issue with high CPU load after a fresh install of Zabbix 6.0-1. The issue is persistent and I can reprocude it every time. Actually it's pretty simple all I have to do is start the Zabbix Server service.

VM Specs: 2 vCPU / 16GB RAM / 500GB disk space
OS: Debian 11.2.0
Zabbix Release: 6.0-1
MariaDB: 10.6.7

After starting Zabbix Server the CPU load goes up to 100% (on one core) and stays there. Checking htop I can see that MariaDB is causing the load spike:



Checking MariaDB processes I can see the following:


This process keeps running seemingly forever or until I manually kill it. I've had it running over night in case it might just be a case of me being impatient but finally ended up killing the process in the morning (killing the process within MariaDB not MariaDB itself). After that the CPU load was back to near 0%. Since this is a brand new installation there's not much data (other than what comes with the default sql statements during installation) that would need to be processed either. I've also checked the output of MariaDB error log which is clean apart from normal startup messages. I've also checked journlactl for service startup problems but again nothing except normal startup messages.

Anyone having the same issue ? Forther troubleshooting advice would be much appreciated.

Cheers!

[little_martian]

In this scenario, the configuration of your DB is critical. From your screen post I see the DB is using ~1.5GB ram. Try incresing the RAM allocated to DB to about 6-8GB RAM, you should see and improvement over time as the DB caches things. Also, check the IO performance (IO wait), it has a huge impact on the DB performance. The 'select distinct' query forces the DB to read a lot of data from the files and sort-merge them in memory, in pages, so having a lot of RAM allocated to the DB makes a difference.

[tim.mooney]

+1 to everything little_martian said.

[normaL]

Hey little_martian,

Thanks for taking the time to reply. I've changed the MariaDB configuration in a way that it allows the use of max. 12GB RAM (Zabbix is the only db and I'll fine tune actual requirements later). Below is the output of "iowait -mxy 10' (average over 10 seconds interval).



I've had Zabbix running for about an hour now and as you can see in the below screenshots I'm still having 100% CPU on one vcore and MariaDB is not using much of the available RAM either. The "SELECT DISTINCT ..." is still running of course.



I am going to leave Zabbix running but I don't expect much change. Any other ideas what might be wrong ?

[little_martian]

Something is not write. MySQL is using just 500MB (res) memory although has allocated 11.7GB (virt).

Here is how my installation looks:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2870391 monitor+ 20 0 20.2g 4.9g 37820 S 4.0 15.6 614:55.14 mysqld

As you can see, it has 20G allocated and uses ~5g. You are still using far less memory than needed.
Here is my config (just the relevant part, new installation)

default-storage-engine=innodb
innodb_buffer_pool_size=16G
innodb_file_per_table=ON
innodb_data_file_path=ibdata1:1g:autoextend:max:60 0g
innodb_log_file_size=2G
innodb_log_buffer_size=500M
innodb_flush_log_at_trx_commit=1
innodb_fast_shutdown=0
innodb_lru_scan_depth=256
innodb_flush_method=O_DSYNC
innodb_page_cleaners=10
sync_binlog=1
gtid-mode = ON
enforce-gtid-consistency = TRUE
sql_mode=TRADITIONAL,ONLY_FULL_GROUP_BY
explicit_defaults_for_timestamp=1
transaction-isolation = READ-COMMITTED
table_open_cache = 1024
max_connections = 500

Check also with MySQL Workbench, Administration tab, Performance->Dashboard. Below is from my old installation, similar configuration as above.

[normaL]

You're absolutely right something is wrong. I have adapted your configuration to my use case (no replication , less ram etc) but I'm still getting the same results. I've now installed Zabbix 6.0-1 with the same vm specs (OS, Kernel, etc) on Xen instead of VMware and here I don't have this issue.

I have to assume that somehow the VM settings (vm tools are installed) of the VMware guest are at fault. It might also have to do with the storage backend but I doubt that since other VMs don't have any i/o issues. I'll further investigate on this end. For the time being I'd like to thank you for your time and effort. Once I've found what is causing this problem I'll update this post so that others may learn something from this.

[normaL]

As promised, I am back with the solution. It has got nothing to do with the configuration of the VMware guest. It is a simple case of "Know the difference between MySQL collations utf8mb4_general_ci and utf8mb4_bin", the latter being the correct one for the database. As soon as I created the database with the correct collation, the issue was gone.

[zhazell]

Hi normaL , were you able to fix this because I checked my charset and collation and they are set correctly. I cannot figure this out. I is a fresh install on Ubuntu and started experiencing this issue once I started adding hosts. About 95 in total.