mariadb upgrade 10.4.15 -> 10.4.17 - innodb semaphore wait issues : mariadb restarts

[seb perkins]

Hello All,

we are currently running zabbix 4.4.10 : mariadb 10.4.15

Following standard patching cycles we have upgrade mariadb 10.4.15 -> 10.4.17

We have identified one serious issue... alas with zabbix : we are getting regular warnings about "innodb semaphore wait" issues leading sometimes to mariadb restarts : this freezes the monitoring tool during the restart (10/15min)

We have had to rollback the VM to a previous version running 10.4.15, since yesterday, these innodb issues are not observed.

As we cannot reproduce the issue in any other DB environment we have, we would like to know if someone has the same issue. Maybe its a config issue.

the server is split into 2 servers : one runs zabbix and the other runs the DB, both have approx 50GB RAM and 4 CPUs and are checking over 55.000 devices.

Please find below the mariadb conf file

[server]
max_connections=1000
port=3306
default-storage-engine=innodb
server-id=11301526

[mariadb]
query_cache_size=0
query_cache_type=0
query_cache_limit=1M

datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
user=mysql
binlog_format=ROW
bind-address=0.0.0.0
performance_schema = ON
event_scheduler = on

default_storage_engine=innodb
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=32G
innodb_log_file_size=2G
innodb_buffer_pool_instances=20
innodb_lock_wait_timeout=1000
innodb_print_all_deadlocks=1
innodb_strict_mode=0

expire_logs_days=1
max_binlog_size=1000M
log_error=/var/log/mysql/error.log

#
innodb_log_buffer_size = 8M
key_buffer_size = 16M
tmp_table_size = 64M
max_heap_table_size = 64M

# Thread Buffers
read_buffer_size = 128K
read_rnd_buffer_size = 128K
sort_buffer_size = 4M
join_buffer_size = 2M
binlog_cache_size = 8M
thread_cache_size = 8

# Memory Limit
max_allowed_packet = 256M

[mysqld]

[mysql_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mariadb-10.1]

[embedded]


If another infomation/configuration is needed please let me know.

Thanks in advance,

Sebastian Perkins

[nickcn]

Hello, Sebastian.

I have been having this exact same issue with our servers.
Zabbix server is 4.0.26 and MariaDB is 10.3.27 though.
I also noticed that when this happens, the zabbix agent stops on both the main server and the proxies.
Did you find a solution after all?

Thanks,
Nick

[xolox]

Hi Sebastian and Nick,

Cheers for so clearly reporting your issue here, it's very helpful to me.

Since this week our Zabbix server setup started acting up, sometimes slowing to a crawl, eventually culminating in a crashing MySQL daemon (MariaDB, same versions you've both reported) some hours ago.

Because we run a highly available multi-master (active/passive) setup with two servers, our servers are configured to start up with readonly=True (a safety measure) so after the automatic MariaDB restart by systemd some hours ago, our monitoring database was running in read only mode, prolonging our monitoring outage until the issue was detected.

We are running Zabbix 5.0.6 and MariaDB 10.4.17 however of course we will be downgrading MariaDB on our two servers in the coming hours

[sjbais]

Hi xolox,

which version did you eventually downgrade MariaDB to? Did it solve your issues?

I am having the exact same problem with MariaDB 10.4.17 after upgrade from 10.4.14 (this had another bug causing high I/O load). We also run multi-master set-up and have some busy tables. So far I was blaming my queries, but after dumping full processlist every 5 seconds to find what is locking during the issue, I came to the conclusion that nothing actually is. It just freezes and then restarts.

Thanks for your update!