Help - Utilization of history syncer processes over 75%

[Sven Kodali]

Hi,

I'm running Zabbix 6.4.4 with MariaDB. A few months ago I was constantly hitting the housekeeper process over 75% which was solved by partitioning tables.

I now have another problem which I cannot find an easy answer for online, as per subject. Some have suggested that the issue could be with the database itself. I would appreciate any suggestions.. I am not a DBA however I can do some basic tasks

I did notice that the process dies completely by midnight, and starts picking up again 3 hours later. Is there any way I can know what is causing this?

[PavelZ]

You can try to increase StartDBSyncers, but judging by the documentation, it is not recommended to abuse this.
Most likely, the disk's speed capabilities are exhausted here.

how about innodb_flush_log_at_trx_commit = 0 ?
sync_binlog=0 ?

Of course, you should first read about these settings in the mysql documentation.

[PavelZ]

You can try to increase StartDBSyncers, but judging by the documentation, it is not recommended to abuse this.
Most likely, the disk's speed capabilities are exhausted here.

how about innodb_flush_log_at_trx_commit = 0 ?
sync_binlog=0 ?

Of course, you should first read about these settings in the mysql documentation.

[tim.mooney]

Hi,

I'm running Zabbix 6.4.4 with MariaDB. A few months ago I was constantly hitting the housekeeper process over 75% which was solved by partitioning tables.

I now have another problem which I cannot find an easy answer for online, as per subject. Some have suggested that the issue could be with the database itself. I would appreciate any suggestions.. I am not a DBA however I can do some basic tasks

Understood that you are not a DBA, but if you want useful suggestions you'll need to provide more information about your database environment.

1. Any reason you're not running 6.4.21? Either way you are running an unsupported series (6.4.x), but a lot of things have been fixed between 6.4.4 and 6.4.21. I'm skeptical that this particular problem will magically disappear if you upgrade, but it often helps to be running the latest version.
2. what version of MariaDB?
3. is this an all-in-one install where the Zabbix server, the database, and the web front end all run on a single servers, or is the database on a separate, dedicated server?
   1. How much memory does the system have?
   2. how much memory is devoted to the database?
4. what type of storage do you have backing the database?
5. Is the storage shared with other hosts (like in a VM or some SAN environments), or is it accessed only by the host where the database resides?
6. has any database tuning been done previously?
7. what are the settings currently in /etc/my.cnf and any files matching /etc/my.cnf.d/*.cnf

Typically the largest and easiest performance tuning win you'll see is giving the database as much RAM as you can, and for a system like Zabbix, where all the tables should be in Innodb (or compatible) formats, setting the innodb_buffer_pool_size to use most of the RAM available to the database.

Also, if your backend storage is some type of flash storage, like older SSDs, tuning the innodb_io_capacity slightly might help. My guess is you're not using fast storage, though, so I don't think the innodb_io_capacity defaulting too low is your issue.

[Sven Kodali]

You can try to increase StartDBSyncers, but judging by the documentation, it is not recommended to abuse this.
Most likely, the disk's speed capabilities are exhausted here.

how about innodb_flush_log_at_trx_commit = 0 ?
sync_binlog=0 ?

Of course, you should first read about these settings in the mysql documentation.

Hello I increased the StartDBSyncers to 12 from the default setting of 4, and it solved the issue for me. Given that the maximum is 100, I guess this is not a drastic change. Thank you!

[Sven Kodali]

Understood that you are not a DBA, but if you want useful suggestions you'll need to provide more information about your database environment.

1. Any reason you're not running 6.4.21? Either way you are running an unsupported series (6.4.x), but a lot of things have been fixed between 6.4.4 and 6.4.21. I'm skeptical that this particular problem will magically disappear if you upgrade, but it often helps to be running the latest version.
2. what version of MariaDB?
3. is this an all-in-one install where the Zabbix server, the database, and the web front end all run on a single servers, or is the database on a separate, dedicated server?
   1. How much memory does the system have?
   2. how much memory is devoted to the database?
4. what type of storage do you have backing the database?
5. Is the storage shared with other hosts (like in a VM or some SAN environments), or is it accessed only by the host where the database resides?
6. has any database tuning been done previously?
7. what are the settings currently in /etc/my.cnf and any files matching /etc/my.cnf.d/*.cnf

Typically the largest and easiest performance tuning win you'll see is giving the database as much RAM as you can, and for a system like Zabbix, where all the tables should be in Innodb (or compatible) formats, setting the innodb_buffer_pool_size to use most of the RAM available to the database.

Also, if your backend storage is some type of flash storage, like older SSDs, tuning the innodb_io_capacity slightly might help. My guess is you're not using fast storage, though, so I don't think the innodb_io_capacity defaulting too low is your issue.

Hello

I solved the issue with the previous post however I believe your input could be valuable for the longer term. Here are my replies below:

Any reason you're not running 6.4.21? Either way you are running an unsupported series (6.4.x), but a lot of things have been fixed between 6.4.4 and 6.4.21. I'm skeptical that this particular problem will magically disappear if you upgrade, but it often helps to be running the latest version.
what version of MariaDB?
- Planning an upgrading to v7.2 soon
- MariaDB 8.0.30

is this an all-in-one install where the Zabbix server, the database, and the web front end all run on a single servers, or is the database on a separate, dedicated server?
All-in-one.

How much memory does the system have?
16GB

how much memory is devoted to the database?
How can I check this?

what type of storage do you have backing the database?
Shared storage over fiber channel, 10k HDD drives

Is the storage shared with other hosts (like in a VM or some SAN environments), or is it accessed only by the host where the database resides?
has any database tuning been done previously?
Same as above, no database tuning. The only changes I made was a scheduled task to partition tables.

what are the settings currently in /etc/my.cnf and any files matching /etc/my.cnf.d/*.cnf

As below:

my.cnf:

[client-server]
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

--------------------------------------------------------

log_bin_trust_function_creators.cnf:

log_bin_trust_function_creators = 1

--------------------------------------------------------

mysql-default-authentication-plugin.cnf:

default_authentication_plugin=mysql_native_passwor d

--------------------------------------------------------

mysql-server.cnf:

event_scheduler = ON
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid

--------------------------------------------------------

Zabbix.cnf:

user = mysql
local_infile = 0

datadir = /var/lib/mysql/

default-storage-engine = InnoDB
skip-name-resolve
key_buffer_size = 32M
max_allowed_packet = 128M
table_open_cache = 1024
table_definition_cache = 1024
max_connections = 2000
join_buffer_size = 1M
sort_buffer_size = 2M
read_buffer_size = 256K
read_rnd_buffer_size = 256K
myisam_sort_buffer_size = 1M
thread_cache_size = 512
open_files_limit = 65535
wait_timeout = 86400

optimizer_switch=index_condition_pushdown=off
"zabbix.cnf" [readonly] 62L, 1262C

[PavelZ]

Shared storage over fiber channel, 10k HDD drives

This is not very fast considering the current fashion for SSDs. Isn't it ?

I increased the StartDBSyncers to 12 from the default setting of 4, and it solved the issue for me. Given that the maximum is 100, I guess this is not a drastic change.

I'm afraid this setting is some kind of insanity limiter, but not a designed maximum.
It's good that it helps in your situation, but most likely due to the displacement of other storage users.

I see that you haven't made any other settings, but there are some that are almost always worth doing.
Try increasing innodb_buffer_pool_size anyway .We don't know how much, but start with at least 2 GB

This is also an important setting that I would recommend without any concerns:
innodb_flush_log_at_trx_commit =2

[Sven Kodali]

This is not very fast considering the current fashion for SSDs. Isn't it ?


I'm afraid this setting is some kind of insanity limiter, but not a designed maximum.
It's good that it helps in your situation, but most likely due to the displacement of other storage users.

I see that you haven't made any other settings, but there are some that are almost always worth doing.
Try increasing innodb_buffer_pool_size anyway .We don't know how much, but start with at least 2 GB

This is also an important setting that I would recommend without any concerns:
innodb_flush_log_at_trx_commit =2

Agree HDDs are now way behind now on speed... will push to get it on SSDs

Will also carry out the changes you recommended, and monitor how it goes. Much appreciated.

[Sven Kodali]

Just to update on the DB settings

innodb_flush_log_at_trx_commit =2 was there already

I increased the buffer to 2GB, and the history syncer went from hovering between 40 - 60% to a complete flatline below 1%. Massive difference!

[tim.mooney]

- MariaDB 8.0.30

MariaDB never had an 8.0.30 version, so that must be MySQL. Because of where MariaDB forked from MySQL, the developers skipped MariaDB 8.x and 9.x and went directly to 10.x.

Also, it's totally understandable that you're planning on eventually upgrading to 7.2, but if your install is having problems, trying the latest version in your series is one of the easiest things you can try to see if the problem goes away.

is this an all-in-one install where the Zabbix server, the database, and the web front end all run on a single servers, or is the database on a separate, dedicated server?
All-in-one.

How much memory does the system have?
16GB

how much memory is devoted to the database?
How can I check this?

If you haven't done any database tuning, the database is probably using 1 GiB or less. There are better, more accurate ways to get this, but a simple way to get a ballpark number is to run "top" and look at the numbers for VIRT, RES, SHR, and %MEM for the mysql user.

With no tuning, the database defaults for things like innodb_buffer_pool_size are set quite low (check the documentation for your version of MySQL to see what the default is. Alternately, before you make a change, you can do something like

SHOW VARIABLES LIKE '%innodb%';

after connecting via the "mysql" command line tool, to see what the current settings are.

what type of storage do you have backing the database?
Shared storage over fiber channel, 10k HDD drives

Is the storage shared with other hosts (like in a VM or some SAN environments), or is it accessed only by the host where the database resides?
has any database tuning been done previously?
Same as above, no database tuning. The only changes I made was a scheduled task to partition tables.

That's not optimal, but your install is small enough that even traditional storage would be OK as long as you tune your database. If you do switch the storage to SSDs, that will certainly help. SSDs can hide or mask a lot of database sins. In a lot of environments, the time you spend diagnosing and debugging a problem is more expensive than just throwing some better hardware at the problem.

log_bin_trust_function_creators = 1

max_connections = 2000
optimizer_switch=index_condition_pushdown=off

I deleted most of the MySQL settings from the list. The deleted ones are all "reasonable": there might actually be slightly better settings for some of them, but you would have to carefully monitor and adjust, and I don't think any of them are the source of your problem. When tuning, you always start with the most important changes first.

Regarding the settings I quoted:

1. you only need "log_bin_trust_function_creators" enabled during Zabbix upgrades, when the upgrade procedure may be changing the database objects, but as far as I'm aware it does no harm to have it enabled all the time, like you do.
2. unless this is a shared MySQL server with other applications connecting to the database, you do not need 2000 connections. Setting it that high is just wasting database memory, because you're forcing MySQL to allocate connection memory that you're not using. What you should do instead is use the "mysql" command line tool to connect to the database and execute the command: SHOW GLOBAL STATUS LIKE '%conn%'; and look for "Max_used_connections" in that list. That will reflect the highest # of connections your database has needed so far. It's probably in double digits. Whatever it is, pick a number that's double or triple the current value for Max_used_connections and lower your "max_connections" setting to that. You'll probably need a database restart to get MySQL/MariaDB to free up the memory it had set aside for all those connections.
3. I've never seen the optimizer_switch config setting before. Do you know where it came from? I bring it up because your config is missing some of the most obvious tuning settings, but then it has a setting that is (in my experience) pretty rare. I'm not saying it's wrong, I'm just pointing out that when the basics are missing, you don't expect to see some exotic setting present.

The big thing you're missing is the setting Pavel already mentioned: innodb_buffer_pool_size . In a MySQL/MariaDB install that primarily uses InnoDB (which should be the case for Zabbix), innodb_buffer_pool_size is the single most important tunable. MySQL and MariaDB have pretty good online documentation explaining each setting, so take a look at what the docs have to say about that setting.

In an all-in-one Zabbix install, where the zabbix server, the database server, and the web front end are all on the same system, you typically want to allocate approximately 40-50% of the entire system memory to the database. That number can be bigger (a lot bigger) if your system has a lot of RAM, but at 16 GiB of system memory I would probably try give MySQL/MariaDB between 6 GiB and 8 GiB of RAM. Typically you can set innodb_buffer_pool_size to 60%-70% of the amount of memory you want to dedicate to MySQL. Pavel suggested 2G as a good starting point (and it is), but I would go even higher, especially once you've fixed your max_connections setting. Try 4G . You can probably go even a little higher, but run your system for a while and see how things perform.

[PavelZ]

I've never seen the optimizer_switch config setting before. Do you know where it came from? I bring it up because your config is missing some of the most obvious tuning settings, but then it has a setting that is (in my experience) pretty rare. I'm not saying it's wrong, I'm just pointing out that when the basics are missing, you don't expect to see some exotic setting present.

This setting described in the Known Issues section. But I also find this confusing and outdated.

https://www.zabbix.com/documentation/current/en/manual/installation/known_issues

https://support.zabbix.com/browse/ZBX-10652

[tim.mooney]

Interesting! Thanks for the link to where this appears in the Zabbix docs. I haven't partitioned my Zabbix install on MariaDB (my database is tuned and it's on SSDs, so even housekeeping is pretty fast) so I'm a lot less familiar with the settings for partitioned installs.