Icingaweb2 performance degradation due to slow queries - ido-mysql

Hello guys!

I’ve been been running an Icinga cluster with HA configuration hosting 9k servers and around 250k services.
As time went by, the icingaweb2 interface started to slow down, taking a considerable amount of time to render information, specially while querying specifics about a service.
Looking at DB performance data, the amount of slow queries increases every time a configuration item gets updated (this happens all day long as people requests changes).
Checking with our DBA team, they recommend to either optimize slow queries or split read queries between the MySQL master and slave node.
The slave node has no CPU, load or DB utilization at all.
Looking at the logs I can see once in a while the “Your database is not able to keep up” legend.

DB specs, both Master and Slave:
VMWare VM.
8 core CPU.
32 GB RAM.

Software:
icingaweb2-2.1.2-1.
icinga2 and ido-mysql version 2.4.
MySQL 5.1.

A few questions before moving forward into building a solution MySQL Galera, where multiple “masters” are available for read and write operations:
Has anyone encounter this situation before?
Is it possible to configure the ido-mysql plugin to split reads and writes onto different MySQL nodes?
Has any query optimization fixes been added into latest versions of Icinga? I may look into updating the app but will cause me trouble if legacy support for the Graphite plugin has been dropped (we use that schema in our prod environment).
Any recommendations on how to keep up with this increasing load? We’re planning to add another 250k checks into the mix :astonished:

Thank you all for reading!

1 Like

you should update your versions of icinga2 and icingaweb2 first, before doing anything else, as the schema has been changed.

could you also give some more info about the DB, e.g. how big it is, index utilization, config etc?
a db server with 8 cores and 32GB RAM should have no problems in running such a DB maybe it is just not correctly configured

Yes. I’ve seen lot’s of Database servers with a lot of ressources doing virtually nothing because the database management system was configured to use default values which leaves most of the ressources untouched.

2 Likes

MySQL 5.1 is quite old. Better Upgrade to 5.6+ or even better switch to MariaDB 10.2. That will be a huge performance gain for you.

1 Like

Hello guys,

Thank you so much for your responses!

@KevinHonka here’s a piece of the DB config:

[mysqld]
character_set_server=utf8
default_storage_engine=InnoDB
max_connections=4000
max_user_connections=3990
max_connect_errors=4000
min_examined_row_limit=100
query_cache_type=0
query_cache_size=0
sql_mode=STRICT_ALL_TABLES
transaction_isolation=READ-COMMITTED  
max_allowed_packet=20971520
net_read_timeout=60
net_write_timeout=90
thread_cache_size=1000
table_open_cache=12000
sort_buffer_size=512K 
long_query_time=0.3
log_warnings=1
max_binlog_size=512M
slow_launch_time=2
slow_query_log=1
innodb_buffer_pool_instances=8
innodb_buffer_pool_size=13938540544
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=2
innodb_flush_method=O_DIRECT    
innodb_io_capacity=500
innodb_lock_wait_timeout=10
innodb_log_buffer_size=16M
innodb_log_file_size=4G
innodb_log_files_in_group=2
innodb_stats_on_metadata=1
binlog-checksum=none
innodb_open_files=2000
innodb_purge_threads=2
innodb_max_dirty_pages_pct=75
innodb_spin_wait_delay=1
innodb_strict_mode=1
innodb_thread_concurrency=48
bulk_insert_buffer_size=32M
key_buffer_size=32M
myisam_sort_buffer_size=48M

[mysql]
no-auto-rehash

[isamchk]
key_buffer=128M
sort_buffer_size=2M
read_buffer=2M
write_buffer=2M

DB size is around 123 GB.

We’re planning on updating Icinga, icingaweb2 (even though extra work is needed to get back the now deprecated Graphite legacy format support on 2.8.x) and moving from MySQL to later versions of MariaDB as well. I’ll let you know how it goes :grinning:

Thanks again!

Maybe you should also read about optimize tables (from 2.8.2 docs).

Thanks @Carsten , I’ll give a shot once the instance is up to date. :grinning: