Icingaweb2: Consuming more CPU resources since upgraded to 2.7.1
Describe the bug
Since I upgraded IcingaWeb2 from version 2.7.0 to 2.7.1 the CPU usage is very high and WebUI is slower than before. Checking with some monitoring tools I see that mysqld process is consuming a lot.
I'm wondering if I'm the only one with this problem..
Here is a system graph. Upgraded on Monday morning, restarted on Tuesday to see if it helps..
To Reproduce
Provide a link to a live example, or an unambiguous set of steps to reproduce this issue. Include configuration, logs, etc. to reproduce, if relevant.
Expected behavior
A clear and concise description of what you expected to happen.
Screenshots
If applicable, add screenshots to help explain your problem.
Your Environment
Include as many relevant details about the environment you experienced the problem in
Icinga Web 2 version and modules (System - About):
Web browser used:
Google Chrome Version 76.0.3809.100 (Official Build) (64-bit)Icinga 2 version used (
icinga2 --version):
icinga2 - The Icinga 2 network monitoring daemon (version: r2.10.5-1)
Copyright (c) 2012-2019 Icinga GmbH (https://icinga.com/)
License GPLv2+: GNU GPL version 2 or later <http://gnu.org/licenses/gpl2.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.
System information:
Platform: Debian GNU/Linux
Platform version: 9 (stretch)
Kernel: Linux
Kernel version: 4.9.0-9-amd64
Architecture: x86_64
Build information:
Compiler: GNU 6.3.0
Build host: cb654124b660
Application information:
General paths:
Config directory: /etc/icinga2
Data directory: /var/lib/icinga2
Log directory: /var/log/icinga2
Cache directory: /var/cache/icinga2
Spool directory: /var/spool/icinga2
Run directory: /run/icinga2
Old paths (deprecated):
Installation root: /usr
Sysconf directory: /etc
Run directory (base): /run
Local state directory: /var
Internal paths:
Package data directory: /usr/share/icinga2
State path: /var/lib/icinga2/icinga2.state
Modified attributes path: /var/lib/icinga2/modified-attributes.conf
Objects path: /var/cache/icinga2/icinga2.debug
Vars path: /var/cache/icinga2/icinga2.vars
PID path: /run/icinga2/icinga2.pid
- PHP version used (
php --version):
PHP 7.0.33-0+deb9u3 (cli) (built: Mar 8 2019 10:01:24) ( NTS )
Copyright (c) 1997-2017 The PHP Group
Zend Engine v3.0.0, Copyright (c) 1998-2017 Zend Technologies
with Zend OPcache v7.0.33-0+deb9u3, Copyright (c) 1999-2017, by Zend Technologies
- Server operating system and version:
PRETTY_NAME="Debian GNU/Linux 9 (stretch)"
NAME="Debian GNU/Linux"
VERSION_ID="9"
VERSION="9 (stretch)"
ID=debian
Additional context
Add any other context about the problem here.
bunghi
All 44 comments
I'd assume that 2.6 -> 2.7 lowered the resources needed and with "Revert "Fix double query execution" #3905" they increased again.
dnsmichi
on 21 Aug 2019
Is anybody working on this issue?
bunghi
on 30 Aug 2019
No, not at the moment - vacation time. How was the performance with 2.6.x for instance? Are there any slow queries being logged? Anything else which would indicate why CPU load is higher than before, e.g. specific process load monitoring & graphs?
https://icinga.com/docs/icinga2/latest/doc/15-troubleshooting/#analyse-your-linuxunix-environment
dnsmichi
on 30 Aug 2019
I have the same problem with my instance since upgraded to 2.7.1. CPU Usage increased a lot. If i click on "Overview => Services" it takes round about 14 seconds to load all the objects which was really fast before.
Running
- Ubuntu 18.04.3
- PHP 7.2.19
- Icinga2 r2.10.5-1
- Icingaweb2 2.7.1
cbuehrer
on 30 Aug 2019
This is the content of my slow query log. (long_query_time = 4)
icinga_slow.log
cbuehrer
on 30 Aug 2019
No, not at the moment - vacation time. How was the performance with 2.6.x for instance? Are there any slow queries being logged? Anything else which would indicate why CPU load is higher than before, e.g. specific process load monitoring & graphs?
https://icinga.com/docs/icinga2/latest/doc/15-troubleshooting/#analyse-your-linuxunix-environment
Last month CPU graph:
Day 5 -> upgraded to 2.7.0
Day 19 -> upgraded to 2.7.1
htop screenshot:
vmstat outputs:
$ vmstat -s
16432596 K total memory
4704416 K used memory
13142184 K active memory
805928 K inactive memory
317008 K free memory
487292 K buffer memory
10923880 K swap cache
3997692 K total swap
16 K used swap
3997676 K free swap
244798673 non-nice user cpu ticks
0 nice user cpu ticks
2917778 system cpu ticks
98108215 idle cpu ticks
1038130 IO-wait cpu ticks
0 IRQ cpu ticks
1853319 softirq cpu ticks
0 stolen cpu ticks
9748679 pages paged in
1927449263 pages paged out
0 pages swapped in
4 pages swapped out
1614868427 interrupts
755241609 CPU context switches
1566282255 boot time
4666354 forks
$ vmstat -t -S M 1 5
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu----- -----timestamp-----
r b swpd free buff cache si so bi bo in cs us sy id wa st CEST
11 0 0 311 475 10667 0 0 3 553 7 7 70 1 28 0 0 2019-08-30 10:20:42
9 0 0 303 475 10668 0 0 0 13348 4587 3487 89 8 2 0 0 2019-08-30 10:20:43
7 0 0 303 475 10668 0 0 0 11312 2624 2092 96 3 1 0 0 2019-08-30 10:20:44
8 0 0 305 475 10668 0 0 0 2376 2573 2357 95 3 2 0 0 2019-08-30 10:20:45
10 0 0 304 475 10668 0 0 0 1404 1806 1652 97 1 2 0 0 2019-08-30 10:20:46
iostat output:
$ iostat 5
Linux 4.9.0-9-amd64 08/30/2019 _x86_64_ (4 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
97.29 0.00 0.85 0.05 0.00 1.80
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 1.00 0.00 12.80 0 64
sdb 29.20 0.00 1619.20 0 8096
dm-0 34.60 0.00 1619.20 0 8096
dm-1 0.00 0.00 0.00 0 0
dm-2 0.00 0.00 0.00 0 0
dm-3 0.00 0.00 0.00 0 0
dm-4 2.60 0.00 10.40 0 52
dm-5 0.00 0.00 0.00 0 0
dm-6 0.00 0.00 0.00 0 0
dm-7 0.20 0.00 2.40 0 12
dm-8 0.00 0.00 0.00 0 0
dm-9 0.00 0.00 0.00 0 0
avg-cpu: %user %nice %system %iowait %steal %idle
98.05 0.00 0.75 0.00 0.00 1.20
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 3.00 0.00 27.20 0 136
sdb 32.80 0.00 1744.00 0 8720
dm-0 37.60 0.00 1744.00 0 8720
dm-1 0.00 0.00 0.00 0 0
dm-2 0.00 0.00 0.00 0 0
dm-3 0.00 0.00 0.00 0 0
dm-4 4.00 0.00 16.00 0 80
dm-5 0.00 0.00 0.00 0 0
dm-6 0.00 0.00 0.00 0 0
dm-7 1.20 0.00 4.80 0 24
dm-8 0.00 0.00 0.00 0 0
dm-9 1.60 0.00 6.40 0 32
avg-cpu: %user %nice %system %iowait %steal %idle
97.45 0.00 0.85 0.05 0.00 1.65
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 0.80 0.00 18.40 0 92
sdb 29.60 0.00 1735.20 0 8676
dm-0 36.20 0.00 1735.20 0 8676
dm-1 0.00 0.00 0.00 0 0
dm-2 0.00 0.00 0.00 0 0
dm-3 0.00 0.00 0.00 0 0
dm-4 3.00 0.00 12.00 0 60
dm-5 0.00 0.00 0.00 0 0
dm-6 0.00 0.00 0.00 0 0
dm-7 0.00 0.00 0.00 0 0
dm-8 0.00 0.00 0.00 0 0
dm-9 1.60 0.00 6.40 0 32
avg-cpu: %user %nice %system %iowait %steal %idle
97.75 0.00 0.60 0.00 0.00 1.65
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 2.20 0.00 18.40 0 92
sdb 32.60 0.00 1683.20 0 8416
dm-0 38.40 0.00 1683.20 0 8416
dm-1 0.00 0.00 0.00 0 0
dm-2 0.00 0.00 0.00 0 0
dm-3 0.00 0.00 0.00 0 0
dm-4 1.20 0.00 4.80 0 24
dm-5 0.00 0.00 0.00 0 0
dm-6 0.00 0.00 0.00 0 0
dm-7 1.60 0.00 7.20 0 36
dm-8 0.00 0.00 0.00 0 0
dm-9 1.60 0.00 6.40 0 32
sar outputs:
root@mqde01icinga02:~$ sar -r
Linux 4.9.0-9-amd64 08/30/2019 _x86_64_ (4 CPU)
10:30:24 LINUX RESTART (4 CPU)
10:32:01 AM kbmemfree kbmemused %memused kbbuffers kbcached kbcommit %commit kbactive kbinact kbdirty
10:34:01 AM 311564 16121032 98.10 487532 10397320 2094132 10.25 13113768 835912 328
10:36:01 AM 278720 16153876 98.30 487540 10397432 2049772 10.03 13145844 836012 180
10:38:01 AM 254660 16177936 98.45 487540 10397384 2078856 10.18 13170288 835960 400
10:40:01 AM 267820 16164776 98.37 487548 10397584 2034336 9.96 13156304 836172 376
10:42:01 AM 233840 16198756 98.58 487556 10397636 2060636 10.09 13188588 836212 336
10:44:01 AM 251236 16181360 98.47 487560 10397700 2039092 9.98 13174964 836288 416
10:46:01 AM 228100 16204496 98.61 487560 10397872 2065484 10.11 13195208 836460 352
10:48:01 AM 374876 16057720 97.72 487560 10398392 2045500 10.01 13047948 836940 708
10:50:01 AM 311556 16121040 98.10 487560 10397976 2081988 10.19 13109116 836532 208
10:52:01 AM 287592 16145004 98.25 487568 10400088 2044080 10.01 13132772 838656 2240
10:54:01 AM 205532 16227064 98.75 487568 10398256 2060044 10.08 13215096 836844 184
10:56:01 AM 781336 15651260 95.25 487560 10387728 2072212 10.14 13190712 830344 176
10:58:01 AM 684944 15747652 95.83 487560 10389184 2072664 10.15 13285564 831740 156
11:00:01 AM 680164 15752432 95.86 487560 10392200 2072536 10.14 13286784 834664 1540
Average: 367996 16064600 97.76 487555 10396197 2062238 10.09 13172354 835624 543
root@mqde01icinga02:~$ sar
Linux 4.9.0-9-amd64 08/30/2019 _x86_64_ (4 CPU)
10:30:24 LINUX RESTART (4 CPU)
10:32:01 AM CPU %user %nice %system %iowait %steal %idle
10:34:01 AM all 96.64 0.00 1.12 0.19 0.00 2.04
10:36:01 AM all 97.37 0.00 1.01 0.10 0.00 1.51
10:38:01 AM all 97.33 0.00 0.96 0.07 0.00 1.64
10:40:01 AM all 96.85 0.00 0.94 0.05 0.00 2.15
10:42:01 AM all 97.42 0.00 0.96 0.05 0.00 1.58
10:44:01 AM all 94.23 0.00 1.24 0.15 0.00 4.38
10:46:01 AM all 92.06 0.00 1.31 0.14 0.00 6.49
10:48:01 AM all 91.36 0.00 1.28 0.16 0.00 7.20
10:50:01 AM all 91.48 0.00 1.17 0.13 0.00 7.22
10:52:01 AM all 91.05 0.00 1.13 0.16 0.00 7.66
10:54:01 AM all 90.59 0.00 1.07 0.15 0.00 8.20
10:56:01 AM all 89.27 0.00 1.59 0.16 0.00 8.98
10:58:01 AM all 90.84 0.00 0.69 0.18 0.00 8.29
11:00:01 AM all 93.83 0.00 0.84 0.14 0.00 5.20
Average: all 93.59 0.00 1.09 0.13 0.00 5.18
root@mqde01icinga02:~$ sar -q
Linux 4.9.0-9-amd64 08/30/2019 _x86_64_ (4 CPU)
10:30:24 LINUX RESTART (4 CPU)
10:32:01 AM runq-sz plist-sz ldavg-1 ldavg-5 ldavg-15 blocked
10:34:01 AM 7 243 8.00 7.70 6.79 0
10:36:01 AM 5 241 6.45 7.33 6.78 0
10:38:01 AM 5 242 7.50 7.42 6.88 0
10:40:01 AM 7 238 6.21 6.97 6.78 0
10:42:01 AM 5 242 7.00 6.97 6.80 0
10:44:01 AM 4 233 5.83 6.54 6.66 0
10:46:01 AM 6 239 5.96 6.30 6.56 0
10:48:01 AM 6 231 5.87 6.12 6.46 1
10:50:01 AM 5 238 5.97 6.04 6.38 0
10:52:01 AM 7 235 5.68 5.95 6.31 0
10:54:01 AM 4 234 5.73 5.87 6.23 0
10:56:01 AM 5 237 6.00 6.02 6.25 0
10:58:01 AM 4 238 5.57 6.05 6.24 0
11:00:01 AM 4 240 5.99 6.10 6.24 0
Average: 5 238 6.27 6.53 6.53 0
root@mqde01icinga02:~$ sar -b
Linux 4.9.0-9-amd64 08/30/2019 _x86_64_ (4 CPU)
10:30:24 LINUX RESTART (4 CPU)
10:32:01 AM tps rtps wtps bread/s bwrtn/s
10:34:01 AM 47.02 0.00 47.02 0.00 5123.24
10:36:01 AM 41.65 0.00 41.65 0.00 4565.55
10:38:01 AM 42.79 0.00 42.79 0.00 4857.94
10:40:01 AM 37.35 0.00 37.35 0.00 4376.74
10:42:01 AM 38.34 0.00 38.34 0.00 4563.70
10:44:01 AM 37.13 0.00 37.13 0.00 4380.84
10:46:01 AM 37.32 0.00 37.32 0.00 4254.11
10:48:01 AM 37.16 0.00 37.16 0.00 4309.93
10:50:01 AM 36.74 0.00 36.74 0.00 4334.34
10:52:01 AM 37.52 0.00 37.52 0.00 4272.23
10:54:01 AM 37.17 0.00 37.17 0.00 4261.01
10:56:01 AM 36.81 0.32 36.49 40.28 4259.51
10:58:01 AM 38.75 0.23 38.52 21.89 4454.05
11:00:01 AM 37.79 0.28 37.51 31.76 4388.65
Average: 38.82 0.06 38.76 6.69 4457.08
Ok, definitely MySQL. Apart from that, I can see Dashing consuming quite a few resources as well. So, your next bet will be MySQL performance analysis, e.g. with mysql_health queries and logs. I can only hint here as I would do on https://community.icinga.com as I'm busy with Icinga 2 Core.
dnsmichi
on 30 Aug 2019
Maybe this info from mysql could help:
$ mysqladmin -u root -p version
error: Found option without preceding group in config file: /etc/mysql/conf.d/innodb.cnf at line: 2
Enter password:
mysqladmin Ver 9.1 Distrib 10.1.38-MariaDB, for debian-linux-gnu on x86_64
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Server version 10.1.38-MariaDB-0+deb9u1
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 10 days 3 hours 37 min 1 sec
Threads: 10 Questions: 125896664 Slow queries: 0 Opens: 757 Flush tables: 1 Open tables: 751 Queries per second avg: 143.550
mytop output:
bunghi
on 30 Aug 2019
I have doubled the CPU resources, using them all 100%. This is happening since 2.7.1 upgrade..
bunghi
on 4 Sep 2019
It seems that nobody is working on this issue, at least not marked as bug. I have downgraded to old version 2.6.3 till this problem is fixed.
bunghi
on 5 Sep 2019
CPU usage after downgrade:
bunghi
on 5 Sep 2019
@bunghi @cbuehrer Please clarify which versions are affected (2.6.3, 2.7.0 or/and 2.7.1) and how many host/services are in your environment.
nilmerg
on 9 Sep 2019
Affected version: 2.7.1
Hosts: 2266
Services: 42108
I rolled back to 2.6.3 because 2.7.0 has a bug with sorts ..
bunghi
on 9 Sep 2019
I also skipped 2.7.0 because of a bug with permissions. 2.7.1 is affected. 2.6.3 not.
I have 1003 hosts and 3071 services
cbuehrer
on 9 Sep 2019
Same here in docker swarm environment:
mariadb:10.4 ; icingaweb2:2.7.1 ; icinga2:2.10.5 (3200 Services)
Much higher load on mariadb container. In frontend Overview->Servicegroups runs into timeout.
foxos42
on 12 Sep 2019
In my case SELECT hostgroupsummary.hostgroup_alias, hostgroupsummary.hostgroup_name, SUM(CASE .....
and SELECT servicesgroupsummary.servicegroup_alias, servicesgroupsummary.servicegroup_name ...
are the looong(>2500s) running querys
explain SELECT hostgroupsummary.hostgroup_alias, hostgroupsummary.hostgroup_name, SUM(CASE WHEN host_state = 1 AND host_handled = 1 THEN 1 ELSE 0 END) AS hosts_down_handled, SUM(CASE WHEN host_state = 1 AND host_handled = 0 THEN 1 ELSE 0 END) AS hosts_down_unhandled, SUM(CASE WHEN host_state = 99 THEN 1 ELSE 0 END) AS hosts_pending, SUM(CASE WHEN host_state IS NOT NULL THEN 1 ELSE 0 END) AS hosts_total, SUM(CASE WHEN host_state = 2 AND host_handled = 1 THEN 1 ELSE 0 END) AS hosts_unreachable_handled, SUM(CASE WHEN host_state = 2 AND host_handled = 0 THEN 1 ELSE 0 END) AS hosts_unreachable_unhandled, SUM(CASE WHEN host_state = 0 THEN 1 ELSE 0 END) AS hosts_up, SUM(CASE WHEN service_state = 2 AND service_handled = 1 THEN 1 ELSE 0 END) AS services_critical_handled, SUM(CASE WHEN service_state = 2 AND service_handled = 0 THEN 1 ELSE 0 END) AS services_critical_unhandled, SUM(CASE WHEN service_state = 0 THEN 1 ELSE 0 END) AS services_ok, SUM(CASE WHEN service_state = 99 THEN 1 ELSE 0 END) AS services_pending, SUM(CASE WHEN service_state IS NOT NULL THEN 1 ELSE 0 END) AS services_total, SUM(CASE WHEN service_state = 3 AND service_handled = 1 THEN 1 ELSE 0 END) AS services_unknown_handled, SUM(CASE WHEN service_state = 3 AND service_handled = 0 THEN 1 ELSE 0 END) AS services_unknown_unhandled, SUM(CASE WHEN service_state = 1 AND service_handled = 1 THEN 1 ELSE 0 END) AS services_warning_handled, SUM(CASE WHEN service_state = 1 AND service_handled = 0 THEN 1 ELSE 0 END) AS services_warning_unhandled FROM (SELECT hg.alias COLLATE latin1_general_ci AS hostgroup_alias, hgo.name1 AS hostgroup_name, CASE WHEN (hs.problem_has_been_acknowledged + hs.scheduled_downtime_depth) > 0 THEN 1 ELSE 0 END AS host_handled, CASE WHEN hs.has_been_checked = 0 OR hs.has_been_checked IS NULL THEN 16 ELSE CASE WHEN hs.current_state = 0 THEN 1 ELSE CASE WHEN hs.current_state = 1 THEN 64 WHEN hs.current_state = 2 THEN 32 ELSE 256 END + CASE WHEN hs.problem_has_been_acknowledged = 1 THEN 2 WHEN hs.scheduled_downtime_depth > 0 THEN 1 ELSE 256 END END END AS host_severity, CASE WHEN hs.has_been_checked = 0 OR (hs.has_been_checked IS NULL AND hs.hoststatus_id IS NOT NULL) THEN 99 ELSE hs.current_state END AS host_state, NULL AS service_handled, 0 AS service_severity, NULL AS service_state FROM icinga_objects AS hgo INNER JOIN icinga_hostgroups AS hg ON hg.hostgroup_object_id = hgo.object_id AND hgo.is_active = 1 AND hgo.objecttype_id = 3 LEFT JOIN icinga_hostgroup_members AS hgm ON hgm.hostgroup_id = hg.hostgroup_id LEFT JOIN icinga_objects AS ho ON hgm.host_object_id = ho.object_id AND ho.is_active = 1 AND ho.objecttype_id = 1 LEFT JOIN icinga_hoststatus AS hs ON hs.host_object_id = ho.object_id WHERE ( ( (EXISTS (SELECT 1 FROM icinga_objects AS sub_hgo INNER JOIN icinga_hostgroups AS sub_hg ON sub_hg.hostgroup_object_id = sub_hgo.object_id AND sub_hgo.is_active = 1 AND sub_hgo.objecttype_id = 3 LEFT JOIN icinga_hostgroup_members AS sub_hgm ON sub_hgm.hostgroup_id = sub_hg.hostgroup_id LEFT JOIN icinga_objects AS sub_ho ON sub_hgm.host_object_id = sub_ho.object_id AND sub_ho.is_active = 1 AND sub_ho.objecttype_id = 1 WHERE ( ((TRUE) AND sub_hgm.host_object_id = ho.object_id) OR ho.object_id IS NULL)) AND (TRUE)) ) ) GROUP BY hgo.object_id, hg.hostgroup_id, hs.hoststatus_id UNION ALL SELECT hg.alias COLLATE latin1_general_ci AS hostgroup_alias, hgo.name1 AS hostgroup_name, NULL AS host_handled, 0 AS host_severity, NULL AS host_state, CASE WHEN (ss.problem_has_been_acknowledged + ss.scheduled_downtime_depth + COALESCE(hs.current_state, 0)) > 0 THEN 1 ELSE 0 END AS service_handled, CASE WHEN ss.current_state = 0 THEN CASE WHEN ss.has_been_checked = 0 OR ss.has_been_checked IS NULL THEN 16 ELSE 0 END + CASE WHEN ss.problem_has_been_acknowledged = 1 THEN 2 ELSE CASE WHEN ss.scheduled_downtime_depth > 0 THEN 1 ELSE 4 END END ELSE CASE WHEN ss.has_been_checked = 0 OR ss.has_been_checked IS NULL THEN 16 WHEN ss.current_state = 1 THEN 32 WHEN ss.current_state = 2 THEN 128 WHEN ss.current_state = 3 THEN 64 ELSE 256 END + CASE WHEN hs.current_state > 0 THEN 1024 ELSE CASE WHEN ss.problem_has_been_acknowledged = 1 THEN 512 ELSE CASE WHEN ss.scheduled_downtime_depth > 0 THEN 256 ELSE 2048 END END END END AS service_severity, CASE WHEN ss.has_been_checked = 0 OR (ss.has_been_checked IS NULL AND ss.servicestatus_id IS NOT NULL) THEN 99 ELSE ss.current_state END AS service_state FROM icinga_objects AS hgo INNER JOIN icinga_hostgroups AS hg ON hg.hostgroup_object_id = hgo.object_id AND hgo.is_active = 1 AND hgo.objecttype_id = 3 LEFT JOIN icinga_hostgroup_members AS hgm ON hgm.hostgroup_id = hg.hostgroup_id LEFT JOIN icinga_objects AS ho ON hgm.host_object_id = ho.object_id AND ho.is_active = 1 AND ho.objecttype_id = 1 LEFT JOIN icinga_hosts AS h ON h.host_object_id = ho.object_id LEFT JOIN icinga_services AS s ON s.host_object_id = h.host_object_id LEFT JOIN icinga_objects AS so ON so.object_id = s.service_object_id AND so.is_active = 1 AND so.objecttype_id = 2 LEFT JOIN icinga_hoststatus AS hs ON hs.host_object_id = ho.object_id LEFT JOIN icinga_servicestatus AS ss ON ss.service_object_id = so.object_id WHERE ( ( (EXISTS (SELECT 1 FROM icinga_objects AS sub_hgo INNER JOIN icinga_hostgroups AS sub_hg ON sub_hg.hostgroup_object_id = sub_hgo.object_id AND sub_hgo.is_active = 1 AND sub_hgo.objecttype_id = 3 LEFT JOIN icinga_hostgroup_members AS sub_hgm ON sub_hgm.hostgroup_id = sub_hg.hostgroup_id LEFT JOIN icinga_objects AS sub_ho ON sub_hgm.host_object_id = sub_ho.object_id AND sub_ho.is_active = 1 AND sub_ho.objecttype_id = 1 WHERE ( ((TRUE) AND sub_hgm.host_object_id = ho.object_id) OR ho.object_id IS NULL)) AND (TRUE)) ) ) GROUP BY hgo.object_id, hg.hostgroup_id, hs.hoststatus_id, ss.servicestatus_id) AS hostgroupsummary GROUP BY hostgroup_name, hostgroup_alias ORDER BY hostgroup_alias ASC LIMIT 25;
JOIN/UNION/GROUP/SORT hell , in my case 8032040 rows to sort (Using temporary; Using filesort)
foxos42
on 12 Sep 2019
So I think I'm able to reproduce this. But to be really sure, please post what filters you've set up for the monitoring/filter/objects restriction, if you have any.
nilmerg
on 16 Sep 2019
I have two users. One has this as filter:
monitoring/filter/objects = "hostgroup_name=hostgrp_abc || hostgroup_name=hostgrp_abc-alpha*"
The second one has no filter and indeed, I have no problem with the second one. I have not noticed this until now because I never use it.
cbuehrer
on 16 Sep 2019
Thank you. This confirms what I've noticed.
nilmerg
on 16 Sep 2019
Thank you. This confirms what I've noticed.
Why was it removed from 2.7.2 milestone?
bunghi
on 24 Sep 2019
I upgraded to 2.7.1 and some users complained that their dashsboards stopped working. They got an error message:
Backend unavailable
It seems that the PHP FPM service is not running. Make sure to start PHP FPM service in order to access Icinga Web 2. If you upgraded Icinga Web 2 recently, make sure to read the docs regarding PHP FPM, also locally available under /usr/share/icingaweb2/doc/02-Installation.md.
I found one entry in the error log on the server:
[Mon Sep 23 09:52:04.852549 2019] [proxy_fcgi:error] [pid 8588] (70007)The timeout specified has expired: [client 159.216.207.30:30879] AH01075: Error dispatching request to :, referer:
Downgrading/reverting to 2.6.3 solved the issue for the users.
bardahlm
on 25 Sep 2019
@bunghi By accident. I seem to have caught it during an bulk update :thinking:
I'm now on it.
nilmerg
on 25 Sep 2019
Hey all, I've opened a PR which should solve the performance issue. Please apply it in your environment if you can and report back if it solved the problem for you. Thanks.
nilmerg
on 25 Sep 2019
Hey @nilmerg
i've applied it and it works like a charm :-) It's really fast again
Thx so far, Christof
cbuehrer
on 26 Sep 2019
Is there any estimated date for 2.7.2 version to be released and available on the repositories?
bunghi
on 1 Oct 2019
No, we still wait for more feedback.
nilmerg
on 1 Oct 2019
How can I apply the PR to my environment if i don't use git but official repo instead?
bunghi
on 1 Oct 2019
Switch to directory /usr/share/icingaweb2.
Run the following: curl https://github.com/Icinga/icingaweb2/compare/fix/slow-queries-when-filtering-groups-3928.patch | patch -Np1
When it asks to patch file library/Icinga/Data/SimpleQuery.php enter the following path: /usr/share/php/Icinga/Data/SimpleQuery.php
nilmerg
on 1 Oct 2019
Patch output:
$ curl --insecure https://github.com/Icinga/icingaweb2/compare/fix/slow-queries-when-filtering-groups-3928.patch | patch -Np1
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 23661 0 23661 0 0 60169 0 --:--:-- --:--:-- --:--:-- 60206
patching file modules/monitoring/library/Monitoring/Backend/Ido/Query/HostgroupQuery.php
patching file modules/monitoring/library/Monitoring/Backend/Ido/Query/ServicegroupQuery.php
patching file modules/monitoring/library/Monitoring/Backend/Ido/Query/ServicegroupsummaryQuery.php
patching file modules/monitoring/library/Monitoring/Backend/Ido/Query/IdoQuery.php
patching file modules/monitoring/library/Monitoring/Backend/Ido/Query/EmptyhostgroupQuery.php
patching file modules/monitoring/library/Monitoring/Backend/Ido/Query/HostgroupsummaryQuery.php
patching file modules/monitoring/library/Monitoring/Backend/Ido/Query/EmptyservicegroupQuery.php
patching file modules/monitoring/library/Monitoring/Backend/Ido/Query/ServicegroupsummaryQuery.php
can't find file to patch at input line 487
Perhaps you used the wrong -p or --strip option?
The text leading up to this was:
--------------------------
|
|From 0081937f6c0ea13517070290bb27a79573bfd759 Mon Sep 17 00:00:00 2001
|From: Johannes Meyer <[email protected]>
|Date: Thu, 26 Sep 2019 12:50:51 +0200
|Subject: [PATCH 6/7] SimpleQuery: Cache count query result and use it in
| `hasResult()`
|
|Does not affect views which do not run a count query. (e.g. dashlets)
|Though, this is a quick win for all other views with which the user
|interacts directly and gets the desired result quicker than before.
|
|refs #3905
|refs #3836
|---
| library/Icinga/Data/SimpleQuery.php | 10 +++++++++-
| 1 file changed, 9 insertions(+), 1 deletion(-)
|
|diff --git a/library/Icinga/Data/SimpleQuery.php b/library/Icinga/Data/SimpleQuery.php
|index 2d0eeef6f2..e4b9f1f9bf 100644
|--- a/library/Icinga/Data/SimpleQuery.php
|+++ b/library/Icinga/Data/SimpleQuery.php
--------------------------
File to patch: /usr/share/php/Icinga/Data/SimpleQuery.php
patching file /usr/share/php/Icinga/Data/SimpleQuery.php
patching file modules/monitoring/application/views/scripts/partials/event-history.phtml
Should I worry about this message? can't find file to patch at input line 487
Do I have to restart anything?
bunghi
on 1 Oct 2019
This went perfectly fine. The message was what you correctly answered with the path I've suggested. A restart is not necessary, the patch should be live now.
nilmerg
on 1 Oct 2019
It looks good, CPU usage decreased to normal values:
bunghi
on 1 Oct 2019
I was filtering by hostgroup_name and had very long loading times for specific searches.
With your provided patch everything is a lot faster and seems good for me.
Foxeronie
on 2 Oct 2019
This patch lowers refreshtime of my hostgroup filter view from 3-4 to 1 sec (1500 Hosts 8500 Services)
NeverUsedID
on 10 Oct 2019
Hello,
after patching slow-queries-when-filtering-groups-3928.patch we have an SQL error for the overview HostGroups and ServiceGroups.
Have you ever seen this error after patching?
HostGroups:
SQLSTATE[42803]: Grouping error: 7 ERROR: column "hg.alias" must appear in the GROUP BY clause or be used in an aggregate function
LINE 18: ss.servicestatus_id UNION ALL SELECT hg.alias AS hostgroup_...
^, query was: SELECT hostgroupsummary.hostgroup_alias, hostgroupsummary.hostgroup_name, SUM(CASE WHEN host_state = 1 AND host_handled = 1 THEN 1 ELSE 0 END) AS hosts_down_handled, SUM(CASE WHEN host_state = 1 AND host_handled = 0 THEN 1 ELSE 0 END) AS hosts_down_unhandled, SUM(CASE WHEN host_state = 99 THEN 1 ELSE 0 END) AS hosts_pending, SUM(CASE WHEN host_state IS NOT NULL THEN 1 ELSE 0 END) AS hosts_total, SUM(CASE WHEN host_state = 2 AND host_handled = 1 THEN 1 ELSE 0 END) AS hosts_unreachable_handled, SUM(CASE WHEN host_state = 2 AND host_handled = 0 THEN 1 ELSE 0 END) AS hosts_unreachable_unhandled, SUM(CASE WHEN host_state = 0 THEN 1 ELSE 0 END) AS hosts_up, SUM(CASE WHEN service_state = 2 AND service_handled = 1 THEN 1 ELSE 0 END) AS services_critical_handled, SUM(CASE WHEN service_state = 2 AND service_handled = 0 THEN 1 ELSE 0 END) AS services_critical_unhandled, SUM(CASE WHEN service_state = 0 THEN 1 ELSE 0 END) AS services_ok, SUM(CASE WHEN service_state = 99 THEN 1 ELSE 0 END) AS services_pending, SUM(CASE WHEN service_state IS NOT NULL THEN 1 ELSE 0 END) AS services_total, SUM(CASE WHEN service_state = 3 AND service_handled = 1 THEN 1 ELSE 0 END) AS services_unknown_handled, SUM(CASE WHEN service_state = 3 AND service_handled = 0 THEN 1 ELSE 0 END) AS services_unknown_unhandled, SUM(CASE WHEN service_state = 1 AND service_handled = 1 THEN 1 ELSE 0 END) AS services_warning_handled, SUM(CASE WHEN service_state = 1 AND service_handled = 0 THEN 1 ELSE 0 END) AS services_warning_unhandled FROM (SELECT hg.alias AS hostgroup_alias, hgo.name1 AS hostgroup_name, CASE WHEN (hs.problem_has_been_acknowledged + hs.scheduled_downtime_depth) > 0 THEN 1 ELSE 0 END AS host_handled, CASE WHEN hs.has_been_checked = 0 OR hs.has_been_checked IS NULL THEN 16 ELSE CASE WHEN hs.current_state = 0 THEN 1 ELSE CASE WHEN hs.current_state = 1 THEN 64 WHEN hs.current_state = 2 THEN 32 ELSE 256 END + CASE WHEN hs.problem_has_been_acknowledged = 1 THEN 2 WHEN hs.scheduled_downtime_depth > 0 THEN 1 ELSE 256 END END END AS host_severity, CASE WHEN hs.has_been_checked = 0 OR hs.has_been_checked IS NULL THEN 99 ELSE hs.current_state END AS host_state, NULL AS service_handled, 0 AS service_severity, NULL AS service_state FROM icinga_objects AS hgo
INNER JOIN icinga_hostgroups AS hg ON hg.hostgroup_object_id = hgo.object_id AND hgo.is_active = 1 AND hgo.objecttype_id = 3
INNER JOIN icinga_hostgroup_members AS hgm ON hgm.hostgroup_id = hg.hostgroup_id
INNER JOIN icinga_objects AS ho ON hgm.host_object_id = ho.object_id AND ho.is_active = 1 AND ho.objecttype_id = 1
INNER JOIN icinga_hoststatus AS hs ON hs.host_object_id = ho.object_id GROUP BY hgo.object_id,
hg.hostgroup_id,
hs.hoststatus_id UNION ALL SELECT hg.alias AS hostgroup_alias, hgo.name1 AS hostgroup_name, NULL AS host_handled, 0 AS host_severity, NULL AS host_state, CASE WHEN (ss.problem_has_been_acknowledged + ss.scheduled_downtime_depth + COALESCE(hs.current_state, 0)) > 0 THEN 1 ELSE 0 END AS service_handled, CASE WHEN ss.current_state = 0 THEN CASE WHEN ss.has_been_checked = 0 OR ss.has_been_checked IS NULL THEN 16 ELSE 0 END + CASE WHEN ss.problem_has_been_acknowledged = 1 THEN 2 ELSE CASE WHEN ss.scheduled_downtime_depth > 0 THEN 1 ELSE 4 END END ELSE CASE WHEN ss.has_been_checked = 0 OR ss.has_been_checked IS NULL THEN 16 WHEN ss.current_state = 1 THEN 32 WHEN ss.current_state = 2 THEN 128 WHEN ss.current_state = 3 THEN 64 ELSE 256 END + CASE WHEN hs.current_state > 0 THEN 1024 ELSE CASE WHEN ss.problem_has_been_acknowledged = 1 THEN 512 ELSE CASE WHEN ss.scheduled_downtime_depth > 0 THEN 256 ELSE 2048 END END END END AS service_severity, CASE WHEN ss.has_been_checked = 0 OR ss.has_been_checked IS NULL THEN 99 ELSE ss.current_state END AS service_state FROM icinga_objects AS hgo
INNER JOIN icinga_hostgroups AS hg ON hg.hostgroup_object_id = hgo.object_id AND hgo.is_active = 1 AND hgo.objecttype_id = 3
INNER JOIN icinga_hostgroup_members AS hgm ON hgm.hostgroup_id = hg.hostgroup_id
INNER JOIN icinga_objects AS ho ON hgm.host_object_id = ho.object_id AND ho.is_active = 1 AND ho.objecttype_id = 1
INNER JOIN icinga_hosts AS h ON h.host_object_id = ho.object_id
INNER JOIN icinga_services AS s ON s.host_object_id = h.host_object_id
INNER JOIN icinga_objects AS so ON so.object_id = s.service_object_id AND so.is_active = 1 AND so.objecttype_id = 2
INNER JOIN icinga_hoststatus AS hs ON hs.host_object_id = ho.object_id
INNER JOIN icinga_servicestatus AS ss ON ss.service_object_id = so.object_id GROUP BY hgo.object_id,
hg.hostgroup_id,
hs.hoststatus_id,
ss.servicestatus_id UNION ALL SELECT hg.alias AS hostgroup_alias, hgo.name1 AS hostgroup_name, NULL AS host_handled, 0 AS host_severity, NULL AS host_state, NULL AS service_handled, 0 AS service_severity, NULL AS service_state FROM icinga_objects AS hgo
INNER JOIN icinga_hostgroups AS hg ON hg.hostgroup_object_id = hgo.object_id AND hgo.is_active = 1 AND hgo.objecttype_id = 3
LEFT JOIN icinga_hostgroup_members AS ehgm ON ehgm.hostgroup_id = hg.hostgroup_id GROUP BY hgo.object_id HAVING (COUNT(ehgm.hostgroup_member_id) = 0)) AS hostgroupsummary GROUP BY hostgroup_name,
hostgroup_alias ORDER BY hostgroup_alias ASC LIMIT 25
________________________________________
#0 /usr/share/icingaweb2/library/vendor/Zend/Db/Statement.php(297): Zend_Db_Statement_Pdo->_execute(Array)
#1 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Abstract.php(470): Zend_Db_Statement->execute(Array)
#2 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query(String, Array)
#3 /usr/share/icingaweb2/library/vendor/Zend/Db/Select.php(705): Zend_Db_Adapter_Pdo_Abstract->query(Object(Zend_Db_Select))
#4 /usr/share/php/Icinga/Data/Db/DbConnection.php(100): Zend_Db_Select->query()
#5 /usr/share/php/Icinga/Data/SimpleQuery.php(171): Icinga\Data\Db\DbConnection->query(Object(Icinga\Module\Monitoring\Backend\Ido\Query\HostgroupsummaryQuery))
#6 zend.view:///usr/share/icingaweb2/modules/monitoring/application/views/scripts/list/hostgroups.phtml(38): Icinga\Data\SimpleQuery->rewind()
#7 /usr/share/php/Icinga/Web/View.php(262): include(String)
#8 /usr/share/icingaweb2/library/vendor/Zend/View/Abstract.php(877): Icinga\Web\View->_run(String)
#9 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(904): Zend_View_Abstract->render(NULL)
#10 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(925): Zend_Controller_Action_Helper_ViewRenderer->renderScript(String, NULL)
#11 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(964): Zend_Controller_Action_Helper_ViewRenderer->render()
#12 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/HelperBroker.php(272): Zend_Controller_Action_Helper_ViewRenderer->postDispatch()
#13 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action.php(518): Zend_Controller_Action_HelperBroker->notifyPostDispatch()
#14 /usr/share/php/Icinga/Web/Controller/Dispatcher.php(76): Zend_Controller_Action->dispatch(String)
#15 /usr/share/icingaweb2/library/vendor/Zend/Controller/Front.php(937): Icinga\Web\Controller\Dispatcher->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#16 /usr/share/php/Icinga/Application/Web.php(300): Zend_Controller_Front->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#17 /usr/share/php/Icinga/Application/webrouter.php(99): Icinga\Application\Web->dispatch()
#18 /usr/share/icingaweb2/public/index.php(4): require_once(String)
#19 {main}
SQLSTATE[42803]: Grouping error: 7 ERROR: column "hg.alias" must appear in the GROUP BY clause or be used in an aggregate function
LINE 18: ss.servicestatus_id UNION ALL SELECT hg.alias AS hostgroup_...
^
________________________________________
#0 /usr/share/icingaweb2/library/vendor/Zend/Db/Statement/Pdo.php(219): PDOStatement->execute(Array)
#1 /usr/share/icingaweb2/library/vendor/Zend/Db/Statement.php(297): Zend_Db_Statement_Pdo->_execute(Array)
#2 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Abstract.php(470): Zend_Db_Statement->execute(Array)
#3 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query(String, Array)
#4 /usr/share/icingaweb2/library/vendor/Zend/Db/Select.php(705): Zend_Db_Adapter_Pdo_Abstract->query(Object(Zend_Db_Select))
#5 /usr/share/php/Icinga/Data/Db/DbConnection.php(100): Zend_Db_Select->query()
#6 /usr/share/php/Icinga/Data/SimpleQuery.php(171): Icinga\Data\Db\DbConnection->query(Object(Icinga\Module\Monitoring\Backend\Ido\Query\HostgroupsummaryQuery))
#7 zend.view:///usr/share/icingaweb2/modules/monitoring/application/views/scripts/list/hostgroups.phtml(38): Icinga\Data\SimpleQuery->rewind()
#8 /usr/share/php/Icinga/Web/View.php(262): include(String)
#9 /usr/share/icingaweb2/library/vendor/Zend/View/Abstract.php(877): Icinga\Web\View->_run(String)
#10 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(904): Zend_View_Abstract->render(NULL)
#11 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(925): Zend_Controller_Action_Helper_ViewRenderer->renderScript(String, NULL)
#12 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(964): Zend_Controller_Action_Helper_ViewRenderer->render()
#13 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/HelperBroker.php(272): Zend_Controller_Action_Helper_ViewRenderer->postDispatch()
#14 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action.php(518): Zend_Controller_Action_HelperBroker->notifyPostDispatch()
#15 /usr/share/php/Icinga/Web/Controller/Dispatcher.php(76): Zend_Controller_Action->dispatch(String)
#16 /usr/share/icingaweb2/library/vendor/Zend/Controller/Front.php(937): Icinga\Web\Controller\Dispatcher->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#17 /usr/share/php/Icinga/Application/Web.php(300): Zend_Controller_Front->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#18 /usr/share/php/Icinga/Application/webrouter.php(99): Icinga\Application\Web->dispatch()
#19 /usr/share/icingaweb2/public/index.php(4): require_once(String)
#20 {main}
Servicegroups:
SQLSTATE[42803]: Grouping error: 7 ERROR: column "sg.alias" must appear in the GROUP BY clause or be used in an aggregate function
LINE 10: hs.hoststatus_id UNION ALL SELECT sg.alias AS servicegroup_...
^, query was: SELECT servicesgroupsummary.servicegroup_alias, servicesgroupsummary.servicegroup_name, SUM(CASE WHEN service_state = 2 AND service_handled = 1 THEN 1 ELSE 0 END) AS services_critical_handled, SUM(CASE WHEN service_state = 2 AND service_handled = 0 THEN 1 ELSE 0 END) AS services_critical_unhandled, SUM(CASE WHEN service_state = 0 THEN 1 ELSE 0 END) AS services_ok, SUM(CASE WHEN service_state = 99 THEN 1 ELSE 0 END) AS services_pending, SUM(1) AS services_total, SUM(CASE WHEN service_state = 3 AND service_handled = 1 THEN 1 ELSE 0 END) AS services_unknown_handled, SUM(CASE WHEN service_state = 3 AND service_handled = 0 THEN 1 ELSE 0 END) AS services_unknown_unhandled, SUM(CASE WHEN service_state = 1 AND service_handled = 1 THEN 1 ELSE 0 END) AS services_warning_handled, SUM(CASE WHEN service_state = 1 AND service_handled = 0 THEN 1 ELSE 0 END) AS services_warning_unhandled FROM (SELECT sg.alias AS servicegroup_alias, sgo.name1 AS servicegroup_name, CASE WHEN (ss.problem_has_been_acknowledged + ss.scheduled_downtime_depth + COALESCE(hs.current_state, 0)) > 0 THEN 1 ELSE 0 END AS service_handled, CASE WHEN ss.current_state = 0 THEN CASE WHEN ss.has_been_checked = 0 OR ss.has_been_checked IS NULL THEN 16 ELSE 0 END + CASE WHEN ss.problem_has_been_acknowledged = 1 THEN 2 ELSE CASE WHEN ss.scheduled_downtime_depth > 0 THEN 1 ELSE 4 END END ELSE CASE WHEN ss.has_been_checked = 0 OR ss.has_been_checked IS NULL THEN 16 WHEN ss.current_state = 1 THEN 32 WHEN ss.current_state = 2 THEN 128 WHEN ss.current_state = 3 THEN 64 ELSE 256 END + CASE WHEN hs.current_state > 0 THEN 1024 ELSE CASE WHEN ss.problem_has_been_acknowledged = 1 THEN 512 ELSE CASE WHEN ss.scheduled_downtime_depth > 0 THEN 256 ELSE 2048 END END END END AS service_severity, CASE WHEN ss.has_been_checked = 0 OR ss.has_been_checked IS NULL THEN 99 ELSE ss.current_state END AS service_state FROM icinga_objects AS sgo
INNER JOIN icinga_servicegroups AS sg ON sg.servicegroup_object_id = sgo.object_id AND sgo.objecttype_id = 4 AND sgo.is_active = 1
INNER JOIN icinga_servicegroup_members AS sgm ON sgm.servicegroup_id = sg.servicegroup_id
INNER JOIN icinga_objects AS so ON so.object_id = sgm.service_object_id AND so.objecttype_id = 2 AND so.is_active = 1
INNER JOIN icinga_services AS s ON s.service_object_id = so.object_id
INNER JOIN icinga_hoststatus AS hs ON hs.host_object_id = s.host_object_id
INNER JOIN icinga_servicestatus AS ss ON ss.service_object_id = so.object_id GROUP BY sgo.object_id,
sg.servicegroup_id,
ss.servicestatus_id,
hs.hoststatus_id UNION ALL SELECT sg.alias AS servicegroup_alias, sgo.name1 AS servicegroup_name, NULL AS service_handled, 0 AS service_severity, NULL AS service_state FROM icinga_objects AS sgo
INNER JOIN icinga_servicegroups AS sg ON sg.servicegroup_object_id = sgo.object_id AND sgo.objecttype_id = 4 AND sgo.is_active = 1
LEFT JOIN icinga_servicegroup_members AS esgm ON esgm.servicegroup_id = sg.servicegroup_id GROUP BY sgo.object_id HAVING (COUNT(esgm.servicegroup_member_id) = 0)) AS servicesgroupsummary GROUP BY servicegroup_name,
servicegroup_alias ORDER BY servicegroup_alias ASC LIMIT 25
________________________________________
#0 /usr/share/icingaweb2/library/vendor/Zend/Db/Statement.php(297): Zend_Db_Statement_Pdo->_execute(Array)
#1 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Abstract.php(470): Zend_Db_Statement->execute(Array)
#2 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query(String, Array)
#3 /usr/share/icingaweb2/library/vendor/Zend/Db/Select.php(705): Zend_Db_Adapter_Pdo_Abstract->query(Object(Zend_Db_Select))
#4 /usr/share/php/Icinga/Data/Db/DbConnection.php(100): Zend_Db_Select->query()
#5 /usr/share/php/Icinga/Data/SimpleQuery.php(171): Icinga\Data\Db\DbConnection->query(Object(Icinga\Module\Monitoring\Backend\Ido\Query\ServicegroupsummaryQuery))
#6 zend.view:///usr/share/icingaweb2/modules/monitoring/application/views/scripts/list/servicegroups.phtml(33): Icinga\Data\SimpleQuery->rewind()
#7 /usr/share/php/Icinga/Web/View.php(262): include(String)
#8 /usr/share/icingaweb2/library/vendor/Zend/View/Abstract.php(877): Icinga\Web\View->_run(String)
#9 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(904): Zend_View_Abstract->render(NULL)
#10 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(925): Zend_Controller_Action_Helper_ViewRenderer->renderScript(String, NULL)
#11 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(964): Zend_Controller_Action_Helper_ViewRenderer->render()
#12 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/HelperBroker.php(272): Zend_Controller_Action_Helper_ViewRenderer->postDispatch()
#13 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action.php(518): Zend_Controller_Action_HelperBroker->notifyPostDispatch()
#14 /usr/share/php/Icinga/Web/Controller/Dispatcher.php(76): Zend_Controller_Action->dispatch(String)
#15 /usr/share/icingaweb2/library/vendor/Zend/Controller/Front.php(937): Icinga\Web\Controller\Dispatcher->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#16 /usr/share/php/Icinga/Application/Web.php(300): Zend_Controller_Front->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#17 /usr/share/php/Icinga/Application/webrouter.php(99): Icinga\Application\Web->dispatch()
#18 /usr/share/icingaweb2/public/index.php(4): require_once(String)
#19 {main}
SQLSTATE[42803]: Grouping error: 7 ERROR: column "sg.alias" must appear in the GROUP BY clause or be used in an aggregate function
LINE 10: hs.hoststatus_id UNION ALL SELECT sg.alias AS servicegroup_...
^
________________________________________
#0 /usr/share/icingaweb2/library/vendor/Zend/Db/Statement/Pdo.php(219): PDOStatement->execute(Array)
#1 /usr/share/icingaweb2/library/vendor/Zend/Db/Statement.php(297): Zend_Db_Statement_Pdo->_execute(Array)
#2 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Abstract.php(470): Zend_Db_Statement->execute(Array)
#3 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query(String, Array)
#4 /usr/share/icingaweb2/library/vendor/Zend/Db/Select.php(705): Zend_Db_Adapter_Pdo_Abstract->query(Object(Zend_Db_Select))
#5 /usr/share/php/Icinga/Data/Db/DbConnection.php(100): Zend_Db_Select->query()
#6 /usr/share/php/Icinga/Data/SimpleQuery.php(171): Icinga\Data\Db\DbConnection->query(Object(Icinga\Module\Monitoring\Backend\Ido\Query\ServicegroupsummaryQuery))
#7 zend.view:///usr/share/icingaweb2/modules/monitoring/application/views/scripts/list/servicegroups.phtml(33): Icinga\Data\SimpleQuery->rewind()
#8 /usr/share/php/Icinga/Web/View.php(262): include(String)
#9 /usr/share/icingaweb2/library/vendor/Zend/View/Abstract.php(877): Icinga\Web\View->_run(String)
#10 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(904): Zend_View_Abstract->render(NULL)
#11 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(925): Zend_Controller_Action_Helper_ViewRenderer->renderScript(String, NULL)
#12 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(964): Zend_Controller_Action_Helper_ViewRenderer->render()
#13 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/HelperBroker.php(272): Zend_Controller_Action_Helper_ViewRenderer->postDispatch()
#14 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action.php(518): Zend_Controller_Action_HelperBroker->notifyPostDispatch()
#15 /usr/share/php/Icinga/Web/Controller/Dispatcher.php(76): Zend_Controller_Action->dispatch(String)
#16 /usr/share/icingaweb2/library/vendor/Zend/Controller/Front.php(937): Icinga\Web\Controller\Dispatcher->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#17 /usr/share/php/Icinga/Application/Web.php(300): Zend_Controller_Front->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#18 /usr/share/php/Icinga/Application/webrouter.php(99): Icinga\Application\Web->dispatch()
#19 /usr/share/icingaweb2/public/index.php(4): require_once(String)
#20 {main}
gbin2265
on 10 Oct 2019
@gbin2265 Is this a PostgreSQL database by chance?
nilmerg
on 11 Oct 2019
Yes, Postgres 9.5 (Redhat Software Collections)
We also had the error "It seems that the PHP FPM service is not running..." and that is why we did the upgrade.
After the upgrade, everything goes much faster , except we have now sql errors on hostsgroups / severvicegroups
gbin2265
on 11 Oct 2019
Thanks. Fixed it and updated the PR.
nilmerg
on 11 Oct 2019
We have done the fix and everything works perfect and faster!
Thanks !!!
gbin2265
on 11 Oct 2019
Don't know if this still belongs to this bug, but I found the following example which is still much slower with an active hostgroup_name filter set for this user (tested with patch).
address string:
/icingaweb2/monitoring/list/services?(service=*APT*|service_display_name=*APT*)&sort=service_severity
user with hostgroup_name filter:
Description Off (ms) Dur (ms) Mem (diff) Mem (total)
Bootstrap, autoloader registered 0.001 0.001 376.38 KiB 376.38 KiB
Action::postDispatch() 7.153 7.152 1522.09 KiB 1898.47 KiB
Counting all results started 7.782 0.629 55.24 KiB 1953.71 KiB
Counting all results finished 6165.909 6158.127 7.00 KiB 1960.71 KiB
Fetching one row started 6170.205 4.296 402.89 KiB 2.31 MiB
Fetching one row finished 11964.670 5794.465 3.86 KiB 2.31 MiB
Query result iteration started 18481.402 6516.732 86.05 KiB 2.40 MiB
Query result iteration finished 18486.467 5.065 119.36 KiB 2.51 MiB
Fetching one row started 18486.718 0.251 36.56 KiB 2.55 MiB
Fetching one row finished 110422.333 91935.615 2.43 KiB 2.55 MiB
user without filter:
Description | Off (ms) | Dur (ms) | Mem (diff) | Mem (total)
-- | -- | -- | -- | --
Bootstrap, autoloader registered | 0.001 | 0.001 | 376.88 KiB | 376.88 KiB
Action::postDispatch() | 5.881 | 5.880 | 1486.91 KiB | 1863.78 KiB
Counting all results started | 6.672 | 0.791 | 54.78 KiB | 1918.56 KiB
Counting all results finished | 470.956 | 464.284 | 18.44 KiB | 1937.00 KiB
Query result iteration started | 1051.007 | 580.051 | 501.25 KiB | 2.38 MiB
Query result iteration finished | 1059.300 | 8.293 | 160.52 KiB | 2.54 MiB
Fetching one row started | 1059.547 | 0.247 | 36.56 KiB | 2.57 MiB
Fetching one row finished | 1970.676 | 911.129 | 2.12 KiB | 2.58 MiB
Query via mysql:
with hostgroup_name filter:
24 rows in set, 1 warning (10.72 sec)
without filter:
41 rows in set, 1 warning (0.59 sec)
Query with hostgroup_name filter:
SELECT so.name1 AS host_name, h.display_name COLLATE latin1_general_ci AS
host_display_name, CASE WHEN hs.has_been_checked = 0 OR hs.has_been_checked
IS NULL THEN 99 ELSE hs.current_state END AS host_state, so.name2 AS
service_description, s.display_name COLLATE latin1_general_ci AS
service_display_name, CASE WHEN ss.has_been_checked = 0 OR
ss.has_been_checked IS NULL THEN 99 ELSE ss.current_state END AS
service_state, CASE WHEN (ss.scheduled_downtime_depth = 0 OR
ss.scheduled_downtime_depth IS NULL) THEN 0 ELSE 1 END AS
service_in_downtime, ss.problem_has_been_acknowledged AS
service_acknowledged, CASE WHEN (ss.problem_has_been_acknowledged +
ss.scheduled_downtime_depth + COALESCE(hs.current_state, 0)) > 0 THEN 1
ELSE 0 END AS service_handled, ss.output AS service_output, ss.perfdata AS
service_perfdata, ss.current_check_attempt || '/' || ss.max_check_attempts
AS service_attempt, UNIX_TIMESTAMP(ss.last_state_change) AS
service_last_state_change, s.icon_image AS service_icon_image,
s.icon_image_alt AS service_icon_image_alt, ss.is_flapping AS
service_is_flapping, ss.state_type AS service_state_type, CASE WHEN
ss.current_state = 0 THEN CASE WHEN ss.has_been_checked = 0 OR
ss.has_been_checked IS NULL THEN 16 ELSE 0 END + CASE WHEN
ss.problem_has_been_acknowledged = 1 THEN 2 ELSE CASE WHEN
ss.scheduled_downtime_depth > 0 THEN 1 ELSE 4 END END ELSE CASE WHEN
ss.has_been_checked = 0 OR ss.has_been_checked IS NULL THEN 16 WHEN
ss.current_state = 1 THEN 32 WHEN ss.current_state = 2 THEN 128 WHEN
ss.current_state = 3 THEN 64 ELSE 256 END + CASE WHEN hs.current_state > 0
THEN 1024 ELSE CASE WHEN ss.problem_has_been_acknowledged = 1 THEN 512 ELSE
CASE WHEN ss.scheduled_downtime_depth > 0 THEN 256 ELSE 2048 END END END
END AS service_severity, ss.notifications_enabled AS
service_notifications_enabled, ss.active_checks_enabled AS
service_active_checks_enabled, ss.passive_checks_enabled AS
service_passive_checks_enabled, ss.check_command AS service_check_command,
CASE WHEN ss.has_been_checked = 0 OR ss.has_been_checked IS NULL THEN CASE
ss.should_be_scheduled WHEN 1 THEN UNIX_TIMESTAMP(ss.next_check) +
(ss.normal_check_interval * 60) ELSE NULL END ELSE
UNIX_TIMESTAMP(ss.next_check) + (CASE WHEN COALESCE(ss.current_state, 0) >
0 AND ss.state_type = 0 THEN ss.retry_check_interval ELSE
ss.normal_check_interval END * 60) + (CEIL(ss.execution_time + ss.latency)
* 2) END AS service_next_update FROM icinga_objects AS so
INNER JOIN icinga_services AS s ON s.service_object_id = so.object_id AND
so.is_active = 1 AND so.objecttype_id = 2
INNER JOIN icinga_hosts AS h ON h.host_object_id = s.host_object_id
INNER JOIN icinga_hoststatus AS hs ON hs.host_object_id = s.host_object_id
INNER JOIN icinga_servicestatus AS ss ON ss.service_object_id =
so.object_id WHERE ( (EXISTS (SELECT 1 FROM icinga_objects AS sub_hgo
INNER JOIN icinga_hostgroups AS sub_hg ON sub_hg.hostgroup_object_id =
sub_hgo.object_id AND sub_hgo.is_active = 1 AND sub_hgo.objecttype_id = 3
INNER JOIN icinga_hostgroup_members AS sub_hgm ON sub_hgm.hostgroup_id =
sub_hg.hostgroup_id
INNER JOIN icinga_objects AS sub_ho ON sub_hgm.host_object_id =
sub_ho.object_id AND sub_ho.is_active = 1 AND sub_ho.objecttype_id = 1
WHERE ((sub_hgo.name1 IN ('my/group/server/doos')) AND
sub_hgm.host_object_id = s.host_object_id))) AND (so.name2 COLLATE
latin1_general_ci LIKE '%APT%' OR s.display_name COLLATE latin1_general_ci
LIKE '%APT%') ) ORDER BY CASE WHEN ss.current_state = 0 THEN CASE WHEN
ss.has_been_checked = 0 OR ss.has_been_checked IS NULL THEN 16 ELSE 0 END +
CASE WHEN ss.problem_has_been_acknowledged = 1 THEN 2 ELSE CASE WHEN
ss.scheduled_downtime_depth > 0 THEN 1 ELSE 4 END END ELSE CASE WHEN
ss.has_been_checked = 0 OR ss.has_been_checked IS NULL THEN 16 WHEN
ss.current_state = 1 THEN 32 WHEN ss.current_state = 2 THEN 128 WHEN
ss.current_state = 3 THEN 64 ELSE 256 END + CASE WHEN hs.current_state > 0
THEN 1024 ELSE CASE WHEN ss.problem_has_been_acknowledged = 1 THEN 512 ELSE
CASE WHEN ss.scheduled_downtime_depth > 0 THEN 256 ELSE 2048 END END END
END DESC, ss.last_state_change DESC
Query without filter:
SELECT so.name1 AS host_name, h.display_name COLLATE latin1_general_ci AS
host_display_name, CASE WHEN hs.has_been_checked = 0 OR hs.has_been_checked
IS NULL THEN 99 ELSE hs.current_state END AS host_state, so.name2 AS
service_description, s.display_name COLLATE latin1_general_ci AS
service_display_name, CASE WHEN ss.has_been_checked = 0 OR
ss.has_been_checked IS NULL THEN 99 ELSE ss.current_state END AS
service_state, CASE WHEN (ss.scheduled_downtime_depth = 0 OR
ss.scheduled_downtime_depth IS NULL) THEN 0 ELSE 1 END AS
service_in_downtime, ss.problem_has_been_acknowledged AS
service_acknowledged, CASE WHEN (ss.problem_has_been_acknowledged +
ss.scheduled_downtime_depth + COALESCE(hs.current_state, 0)) > 0 THEN 1
ELSE 0 END AS service_handled, ss.output AS service_output, ss.perfdata AS
service_perfdata, ss.current_check_attempt || '/' || ss.max_check_attempts
AS service_attempt, UNIX_TIMESTAMP(ss.last_state_change) AS
service_last_state_change, s.icon_image AS service_icon_image,
s.icon_image_alt AS service_icon_image_alt, ss.is_flapping AS
service_is_flapping, ss.state_type AS service_state_type, CASE WHEN
ss.current_state = 0 THEN CASE WHEN ss.has_been_checked = 0 OR
ss.has_been_checked IS NULL THEN 16 ELSE 0 END + CASE WHEN
ss.problem_has_been_acknowledged = 1 THEN 2 ELSE CASE WHEN
ss.scheduled_downtime_depth > 0 THEN 1 ELSE 4 END END ELSE CASE WHEN
ss.has_been_checked = 0 OR ss.has_been_checked IS NULL THEN 16 WHEN
ss.current_state = 1 THEN 32 WHEN ss.current_state = 2 THEN 128 WHEN
ss.current_state = 3 THEN 64 ELSE 256 END + CASE WHEN hs.current_state > 0
THEN 1024 ELSE CASE WHEN ss.problem_has_been_acknowledged = 1 THEN 512 ELSE
CASE WHEN ss.scheduled_downtime_depth > 0 THEN 256 ELSE 2048 END END END
END AS service_severity, ss.notifications_enabled AS
service_notifications_enabled, ss.active_checks_enabled AS
service_active_checks_enabled, ss.passive_checks_enabled AS
service_passive_checks_enabled, ss.check_command AS service_check_command,
CASE WHEN ss.has_been_checked = 0 OR ss.has_been_checked IS NULL THEN CASE
ss.should_be_scheduled WHEN 1 THEN UNIX_TIMESTAMP(ss.next_check) +
(ss.normal_check_interval * 60) ELSE NULL END ELSE
UNIX_TIMESTAMP(ss.next_check) + (CASE WHEN COALESCE(ss.current_state, 0) >
0 AND ss.state_type = 0 THEN ss.retry_check_interval ELSE
ss.normal_check_interval END * 60) + (CEIL(ss.execution_time + ss.latency)
* 2) END AS service_next_update FROM icinga_objects AS so
INNER JOIN icinga_services AS s ON s.service_object_id = so.object_id AND
so.is_active = 1 AND so.objecttype_id = 2
INNER JOIN icinga_hosts AS h ON h.host_object_id = s.host_object_id
INNER JOIN icinga_hoststatus AS hs ON hs.host_object_id = s.host_object_id
INNER JOIN icinga_servicestatus AS ss ON ss.service_object_id =
so.object_id WHERE ( (so.name2 COLLATE latin1_general_ci LIKE '%APT%' OR
s.display_name COLLATE latin1_general_ci LIKE '%APT%') ) ORDER BY CASE WHEN
ss.current_state = 0 THEN CASE WHEN ss.has_been_checked = 0 OR
ss.has_been_checked IS NULL THEN 16 ELSE 0 END + CASE WHEN
ss.problem_has_been_acknowledged = 1 THEN 2 ELSE CASE WHEN
ss.scheduled_downtime_depth > 0 THEN 1 ELSE 4 END END ELSE CASE WHEN
ss.has_been_checked = 0 OR ss.has_been_checked IS NULL THEN 16 WHEN
ss.current_state = 1 THEN 32 WHEN ss.current_state = 2 THEN 128 WHEN
ss.current_state = 3 THEN 64 ELSE 256 END + CASE WHEN hs.current_state > 0
THEN 1024 ELSE CASE WHEN ss.problem_has_been_acknowledged = 1 THEN 512 ELSE
CASE WHEN ss.scheduled_downtime_depth > 0 THEN 256 ELSE 2048 END END END
END DESC, ss.last_state_change DESC
Best regards,
Patrick
Foxeronie
on 11 Oct 2019
The queries are fine. The benchmark with the hostgroup filter however.. Are you sure you've experienced this with the patch applied? Because, both benchmarks should be identical in terms of what's measured. Though, the first one measures something that should not occur with the patch being applied.
nilmerg
on 11 Oct 2019
Sorry, you are right. I messed up a little bit with all the tests. ^^
So now here is a step by step:
Filter:
hostgroup_name=my/host/group/server
Address:
/icingaweb2/monitoring/list/services?(service=*APT*|service_display_name=*APT*)&sort=service_severity
without patch - with hostgroup_name filter:
Description | Off (ms) | Dur (ms) | Mem (diff) | Mem (total)
-- | -- | -- | -- | --
Bootstrap, autoloader registered | 0.002 | 0.002 | 375.33 KiB | 375.33 KiB
Action::postDispatch() | 7.338 | 7.336 | 1522.50 KiB | 1897.83 KiB
Counting all results started | 7.991 | 0.653 | 55.24 KiB | 1953.07 KiB
Counting all results finished | 7536.555 | 7528.564 | 7.00 KiB | 1960.07 KiB
Fetching one row started | 7540.591 | 4.036 | 403.79 KiB | 2.31 MiB
Fetching one row finished | 14595.529 | 7054.938 | 3.86 KiB | 2.31 MiB
Query result iteration started | 20835.595 | 6240.066 | 86.05 KiB | 2.40 MiB
Query result iteration finished | 20842.757 | 7.162 | 119.36 KiB | 2.51 MiB
Fetching one row started | 20843.081 | 0.324 | 36.56 KiB | 2.55 MiB
Fetching one row finished | 119397.608 | 98554.527 | 2.43 KiB | 2.55 MiB
After that:
root@icinga-master01:/usr/share/icingaweb2# git branch
* master
root@icinga-master01:/usr/share/icingaweb2# git pull
Already up-to-date.
root@icinga-master01:/usr/share/icingaweb2# curl https://github.com/Icinga/icingaweb2/compare/fix/slow-queries-when-filtering-groups-3928.patch | patch -Np1
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 23706 0 23706 0 0 50178 0 --:--:-- --:--:-- --:--:-- 50224
patching file modules/monitoring/library/Monitoring/Backend/Ido/Query/HostgroupQuery.php
patching file modules/monitoring/library/Monitoring/Backend/Ido/Query/ServicegroupQuery.php
patching file modules/monitoring/library/Monitoring/Backend/Ido/Query/ServicegroupsummaryQuery.php
patching file modules/monitoring/library/Monitoring/Backend/Ido/Query/IdoQuery.php
patching file modules/monitoring/library/Monitoring/Backend/Ido/Query/EmptyhostgroupQuery.php
patching file modules/monitoring/library/Monitoring/Backend/Ido/Query/HostgroupsummaryQuery.php
patching file modules/monitoring/library/Monitoring/Backend/Ido/Query/EmptyservicegroupQuery.php
patching file modules/monitoring/library/Monitoring/Backend/Ido/Query/ServicegroupsummaryQuery.php
patching file library/Icinga/Data/SimpleQuery.php
patching file modules/monitoring/application/views/scripts/partials/event-history.phtml
root@icinga-master01:/usr/share/icingaweb2#
with patch - with hostgroup_name filter:
Description | Off (ms) | Dur (ms) | Mem (diff) | Mem (total)
-- | -- | -- | -- | --
Bootstrap, autoloader registered | 0.002 | 0.002 | 375.33 KiB | 375.33 KiB
Action::postDispatch() | 6.321 | 6.319 | 1522.50 KiB | 1897.83 KiB
Counting all results started | 6.943 | 0.622 | 55.24 KiB | 1953.07 KiB
Counting all results finished | 9858.079 | 9851.136 | 7.00 KiB | 1960.07 KiB
Query result iteration started | 49710.218 | 39852.139 | 489.04 KiB | 2.39 MiB
Query result iteration finished | 49715.341 | 5.123 | 123.31 KiB | 2.51 MiB
Fetching one row started | 49715.657 | 0.316 | 36.56 KiB | 2.55 MiB
Fetching one row finished | 51564.255 | 1848.598 | 2.12 KiB | 2.55 MiB
with patch - without hostgroup_name filter:
Description | Off (ms) | Dur (ms) | Mem (diff) | Mem (total)
-- | -- | -- | -- | --
Bootstrap, autoloader registered | 0.000 | 0.000 | 376.16 KiB | 376.16 KiB
Action::postDispatch() | 5.299 | 5.299 | 1486.78 KiB | 1862.94 KiB
Counting all results started | 6.087 | 0.788 | 54.78 KiB | 1917.72 KiB
Counting all results finished | 439.461 | 433.374 | 18.44 KiB | 1936.16 KiB
Query result iteration started | 1129.778 | 690.317 | 501.34 KiB | 2.38 MiB
Query result iteration finished | 1138.271 | 8.493 | 160.52 KiB | 2.54 MiB
Fetching one row started | 1138.504 | 0.233 | 36.56 KiB | 2.57 MiB
Fetching one row finished | 2043.356 | 904.852 | 2.12 KiB | 2.57 MiB
patched - with hostgroup_name filter - query via mysql client:
24 rows in set, 1 warning (10.61 sec)
Well, about 70 seconds less with the patch. I'd call this an improvement. :thinking: And in case it's not faster than 50 seconds with v2.6.3 I'd say your database host is the issue. :wink:
nilmerg
on 11 Oct 2019
Please don't get me wrong. It's a very good patch and I'm thankful for your work. :)
The only thing I don't understand is that the query via the mysql client needs ~10 seconds, but via the Web UI it needs ~50 seconds.
Icinga Web 2 is running on the same machine where the database is running. So I thought that there is a problem while processing the data or something.
Foxeronie
on 11 Oct 2019
Take a look at the benchmarks. The query alone is the query result iteration. That's what you'll do in the client, minus all the php code execution. On top of that the counting of all results and the query for the summary bar at the bottom.
nilmerg
on 11 Oct 2019
Related issues
K0nne
路
4Comments
Al2Klimov
路
6Comments
josy1024
路
8Comments
ghost
路
5Comments
icinga-migration
路
6Comments
Most helpful comment
Hey all, I've opened a PR which should solve the performance issue. Please apply it in your environment if you can and report back if it solved the problem for you. Thanks.