[dev.icinga.com #2703] Notification view still painfully slow

[icinga-migration]

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

Created by tgelf on 2012-06-22 08:33:49 +00:00

Assignee: (none)
Status: New
Target Version: Backlog
Last Update: 2015-05-18 12:17:33 +00:00 (in Redmine)

Icinga Version: 1.7.0
Icinga Web Version: 1.7.0
IDO Version: 1.7.0
OS Version: Any
DB Type: MySQL
DB Version: 5.5
Browser Version: any

---

While trying to find out what the heck is going on, we stumbled over queries like the following:

SELECT DISTINCT i7.notification_id FROM icinga_notifications i7 INNER JOIN icinga_objects i8 ON i7.object_id = i8.object_id LEFT JOIN icinga_contactnotifications i9 ON i7.notification_id = i9.notification_id LEFT JOIN icinga_contactnotificationmethods i10 ON i9.contactnotification_id = i10.contactnotification_id LEFT JOIN icinga_commands i11 ON i10.command_object_id = i11.object_id LEFT JOIN icinga_objects i12 ON i9.contact_object_id = i12.object_id ORDER BY i7.notification_id DESC LIMIT 25;

They are doing full table scan / temp table voodoo, hammering your disks once the notification tables start growing. In this example the "DISTINCT" shall be useless and the query is a lot faster without DISTINCT (0.01s VS 16-40s in our testbed). And I have no idea what this "fetch-ids-only" strategy shall be useful for.

The DISTINCT is added by Doctrine, see line 1403 in lib/doctrine/lib/Doctrine/Query.php. But please take care, removing it there COULD cause other issues.

Regards,
Thomas Gelf

---

Relations:

• relates #2670
• relates #6518

[icinga-migration]

Updated by tgelf on 2012-06-22 08:34:55 +00:00

NB: tested with Icinga-Web 1.7.1 and Icinga 1.7.1, user profile has been resetted, no hostgroup or other limits have been in effect.

[icinga-migration]

Updated by tgelf on 2012-06-22 08:56:33 +00:00

Some farther tests showed that globally disabling this auto-super-DISTINCT-feature has severe impacts on other grids. Host grids for example will start to issue multiple JSON requests for each row (even for modules such us LConf) on each reload.

[icinga-migration]

Updated by mfriedrich on 2012-09-24 13:46:43 +00:00

• Icinga Version set to 1
• Icinga Web Version set to 1
• IDO Version set to 1
• OS Version set to Any
• DB Type set to MySQL
• DB Version set to 5
• Browser Version set to any

could be related to #2670

[icinga-migration]

Updated by mfriedrich on 2012-10-11 22:12:02 +00:00

or #2618

[icinga-migration]

Updated by mfriedrich on 2012-10-11 22:12:08 +00:00

• Status changed from New to Feedback

[icinga-migration]

Updated by mfriedrich on 2015-03-12 19:43:28 +00:00

• Status changed from Feedback to New

[icinga-migration]

Updated by mfriedrich on 2015-04-26 07:44:22 +00:00

• Category set to Database & Queries

I'm fairly certain this cannot be fixed easily. I'd rather close the issue, what do you think?

[icinga-migration]

Updated by berk on 2015-05-18 12:17:33 +00:00

• Target Version set to Backlog