Icinga IDO MySQL 8.x - Best Practice parameters / performance tuning

Icinga 2
,
1

Hello,

I wonder if someone can share the experience in terms of my.cnf parameters which helped in achieving better IDO performance in a large scale Icinga2 Cluster deployment.

I’ve to set up 2x MYSQL 8.x nodes with a/a replication between them for Icinga2 2.15 cluster.

Thanks

2

Besides above, I would like to ask a related question…

Did you choose MariaDB over Mysql and why?

Thanks

3

Our MariaDB galera cluster (on RHEL) runs with

[mysqld]
max_allowed_packet = 16M
slow_query_log = 1
slow_query_log_file = /var/log/mariadb/slow-query.log
long_query_time = 10


[galera]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
query_cache_size=16M
query_cache_type=0
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=256M

#Galera settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_sst_method=rsync
wsrep_slave_threads=4

taken/adjusted from Galera MySQL cluster with VIPs and HAProxy for IDO-Mysql and more

This ran pretty well, until we moved to a postgres setup when we switched to IcingaDB. With wsrep_sst_method=mariabackup we had performance and reliability issues though. Switching to rsync fixed that pretty much. No cluster rebuild since then, afair.
Now only the Director and some module DBs remain, though.

Maybe it helps you in some way :slight_smile:

1 Like
4

Thank you for sharing the information. I appreciate it. One question for you - What made you to switch over to Postgres while switching to IcingaDB? Perhaps some performance gain in comparison to Mysql?

Thanks again

5

At that time it was mainly a performance and reliability gain compared to mysql.
We were running a postgres patroni cluster for jira at that time and it proved much more stable on connection losses between the datacenters.

With our (not “correctly” configured) galera cluster we had to rebuild the cluster nearly every time. This was not necessary since switching the sync method, as mentioned above, though.

6

As I understand, Postgres is good for write intensive workload and Mysql is good for read intensive workload. So in a non-IcingaDB I think Postgres would be beneficial knowing Icinga2 is basically a write intensive application as I understand. However, with IcingaDB which is based on Redis, Redis will likely taking a major part of the workload. So in that case, I’m not sure if it does make a sense to favor Postgres over Mysql. Any thoughts?