[dev.icinga.com #6518] notification cronk: long running mysql query with hostgroup restriction

[icinga-migration]

This issue has been migrated from Redmine: https://dev.icinga.com/issues/6518

Created by mfischer on 2014-06-19 08:37:13 +00:00

Assignee: (none)
Status: Rejected (closed on 2015-04-26 07:43:51 +00:00)
Target Version: (none)
Last Update: 2015-04-26 07:43:51 +00:00 (in Redmine)

Icinga Version: 1.10.1
Icinga Web Version: 1.10.0
IDO Version: 1.10.1
OS Version: RHEL 6.5
DB Type: MySQL
DB Version: Ver 14.14 Distrib 5.1.73
Browser Version: Firefox 24.0

---

A user in icinga-web that has a hostgroup restriction opens the notifications cronk in the history category.
Then this very long running query is started in our environment:

SELECT DISTINCT i17.notification_id, i17.start_time AS i17__6
FROM
icinga_notifications i17 
INNER JOIN icinga_objects i18 ON i17.object_id = i18.object_id 
LEFT JOIN icinga_services i19 ON i18.object_id = i19.service_object_id 
LEFT JOIN icinga_hosts i20 ON i18.object_id = i20.host_object_id 
LEFT JOIN icinga_hosts i21 ON i19.host_object_id = i21.host_object_id 
LEFT JOIN icinga_contactnotifications i22 ON i17.notification_id = i22.notification_id 
LEFT JOIN icinga_contactnotificationmethods i23 ON i22.contactnotification_id = i23.contactnotification_id 
LEFT JOIN icinga_commands i24 ON i23.command_object_id = i24.object_id 
LEFT JOIN icinga_objects i25 ON i24.object_id = i25.object_id 
LEFT JOIN icinga_objects i26 ON i22.contact_object_id = i26.object_id 
LEFT JOIN icinga_hostgroup_members i28 ON (i20.host_object_id = i28.host_object_id) 
LEFT JOIN icinga_hostgroups i27 ON i27.hostgroup_id = i28.hostgroup_id 
LEFT JOIN icinga_objects i29 ON i27.hostgroup_object_id = i29.object_id 
LEFT JOIN icinga_hostgroup_members i31 ON (i21.host_object_id = i31.host_object_id) 
LEFT JOIN icinga_hostgroups i30 ON i30.hostgroup_id = i31.hostgroup_id 
LEFT JOIN icinga_objects i32 ON i30.hostgroup_object_id = i32.object_id
WHERE ( ((i18.objecttype_id = 1 AND i29.name1 IN ('windows-app-servers')) OR (i18.objecttype_id = 2 AND i32.name1 IN ('windows-app-servers'))) )
ORDER BY i17__6 DESC LIMIT 25

After 10 minutes this query is still running and mysql loads up all of the servers ressources
Maybe there is some index missing, but my mysql knowledge is really limited

---

Relations:

• relates #6518

[icinga-migration]

Updated by mfriedrich on 2014-07-12 11:14:05 +00:00

• Subject changed from Notifications Cronk - long running querie to notification cronk: long running mysql query with hostgroup restriction
• Description updated

I'd say that's a known problem caused by doctrine and huge datasets. How many rows are within the notifications table?

[icinga-migration]

Updated by mfischer on 2014-07-12 11:38:37 +00:00

mysql> select count(notification_id) from icinga_notifications;
+------------------------+
| count(notification_id) |
+------------------------+
|                 154918 |
+------------------------+
1 row in set (0.29 sec)

[icinga-migration]

Updated by mfriedrich on 2014-07-19 14:13:56 +00:00

Yeah well. Similar to #2703 making this a duplicate.

[icinga-migration]

Updated by mfriedrich on 2015-04-26 07:43:51 +00:00

• Category set to Database & Queries
• Status changed from New to Rejected

Closing as duplicate.