IcingaDB - poor performance when using custom variables in the queries

[BorisTrnka]

Hello team,

I’d like to ask for a support regarding performance issues for DB queries running against the IcingaDB database.

I’m running a query for “tactical view” with limitation for hosts having specific custom variable, like https://.../icingaweb2/icingadb/tactical?host.vars.customer=XYZ
It takes over 2-3 minutes to get the result; similarly for other queries having the filer based on customer variables (e.g., hostgroups: https://.../icingaweb2/icingadb/hostgroups?hostgroup.name=team&host.vars.customer=XYZ runs over 3-5 mins).

Using the URL feature/param “format=sql” I was able to observe the corresponding SQL query (attached).
If I read the query for “tactical view” correctly, it loops through all the services, check if the service exists in the filtered list for selected custom variable and then calculate/sum the occurrences.
This seems to be a bit expensive query to me – to check each service if exists in the list (each service ID is passed into sub-query to evaluate) and then process/calculate the values.

I tried to adapt it (attached), while keeping the query’s logic, by selecting services having the IDs in the filtered list (not looping through all).
Running such query, I get same result, but the runtime is 400-500 times faster (!) – around 0.3-0.5 sec.

Attached are the print-screens for “tactical view” as well as for the hostgroups query, for original and adapted version.

Could you, please, review and confirm my findings? Could you, please, guide me/share few details which engine generates the queries, how are they build in PHP code?
Would it be possible to adjust the queries for better performance?

Thanks.

[BorisTrnka]

tactical_queries.txt
Original:

with adapted query

another SQL

[tbauriedel]

ref/NC/775212

[nilmerg]

Hi,

please share a few other facts:

• How many hosts/services are concerned?
• What's the database vendor and version?
• What are the database host's figures? (Cores, RAM, etc)
• Since you already played with the query, please share an EXPLAIN output of each

Thanks

[BorisTrnka]

Hi, thanks for taking care. To answer:

• 33000+ hosts and almost 500000 services
• it's AWS RDS service - 5.7.12 MySQL Community Server
• 32CPU, 256GB RAM

[BorisTrnka]

Hi, I run the queries from the previously attached text file. Attaching the EXPLAIN outputs (in default as well as json(detailed) format).
original.txt
adapted.txt

[BorisTrnka]

NOTE: from my perspective the key issue is with having sub_customvar_flat_host_service.id = service.id as part of the sub-query. It means it has to pass service ID each iteration into the sub-query to validate if relevant or not > means has to loop through all the services.
My modification first select all service IDs having the custom variables and then it compares the service ID (from parent query) with the list of obtained service IDs from sub-query - in such case the index can be / is used ...

[BorisTrnka]

Hello, I just curious if provided information helped or if you were able to replicate the issue on your site ... thanks.
Is there any hint in which part of the PHP library code I could dive in to review how the queries are build?

[nilmerg]

We're still investigating. In theory, your adjustments make sense. We're still experimenting, because as of now we weren't able to replicate your results.

Is there any hint in which part of the PHP library code I could dive in to review how the queries are build?

If you're really interested: https://github.com/Icinga/ipl-orm/blob/master/src/Compat/FilterProcessor.php

[nilmerg]

Please show the result of SELECT @@optimizer_switch;. I can replicate your results somewhat, but not entirely yet. The result should help pinning the issue down.

[BorisTrnka]

hi, here the output:
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,hash_join=on,hash_join_cost_based=on

[BorisTrnka]

Hello, may I kindly ask if there is any update for the topic? Or any further details to provide? Thanks.

[nilmerg]

Hey, our current findings indicate that the 5.7.12 MySQL Community Server you're using does not support the latest query optimizations. That's why we didn't notice this bottleneck yet. We're still investigating whether it makes sense to change how we generate these queries.

[marcelfischer]

We have similar issues when using "icingadb/​filter/​objects" via Access Control on Roles.
We have RHEL8 and updated to MariaDB 10.5 which seem to help for Views like "Tactical" or the normal "Services" View. But we still have long load times when opening a single Host or Service Page.
This prevents us from using IcingaDB in Production because we need those Object Filters for different customers

[tectumopticum]

We also use

We have similar issues when using "icingadb/​filter/​objects" via Access Control on Roles. We have RHEL8 and updated to MariaDB 10.5 which seem to help for Views like "Tactical" or the normal "Services" View. But we still have long load times when opening a single Host or Service Page. This prevents us from using IcingaDB in Production because we need those Object Filters for different customers

we also use this filter in our roles as we did this until now in the monitoring-module - and as long as you're not an admin without any restrictions, the performance while loading the data is not much better than in ido