[dev.icinga.com #2670] MySQLd using very high CPU, related to notifications query

[icinga-migration]

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

Created by shogg on 2012-06-07 08:01:59 +00:00

Assignee: (none)
Status: Closed (closed on 2013-04-08 20:39:34 +00:00)
Target Version: (none)
Last Update: 2013-04-08 20:39:34 +00:00 (in Redmine)

Icinga Version: 1.6.1
Icinga Web Version: 1.6.1
IDO Version: 1.6.1
OS Version: Any
DB Type: MySQL
DB Version: 5.1
Browser Version: any

---

I was seeing very high CPU (160%+) coming from the mysqld process. Looking at the SQL processes, I see the following:

+-------+--------+-----------+--------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id    | User   | Host      | db     | Command | Time | State                | Info                                                                                                 |
+-------+--------+-----------+--------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| 76745 | icinga | localhost | icinga | Query   | 1542 | Copying to tmp table | SELECT DISTINCT i13.notification_id FROM icinga_notifications i13 INNER JOIN icinga_objects i14 ON i |
| 76747 | icinga | localhost | icinga | Query   | 1515 | Copying to tmp table | SELECT DISTINCT i13.notification_id FROM icinga_notifications i13 INNER JOIN icinga_objects i14 ON i |
| 76837 | icinga | localhost | icinga | Query   |  959 | Copying to tmp table | SELECT DISTINCT i13.notification_id FROM icinga_notifications i13 INNER JOIN icinga_objects i14 ON i |

I'm running 1.6.1 currently, core and web.

I'm no expert at all on mysql, googling seem to suggest some form of indexing might help?

Thanks a lot!

---

Relations:

• relates #2670

[icinga-migration]

Updated by mfriedrich on 2012-09-24 13:44:31 +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

hmmm, possibly related to #2937 and how the joins are done. replacing the inner join would possibly prevent data copying, but i am not sure about that, or where to change that join.

a fully query output would be helpful as well.

furthermore, please try with the current 1.7.2 releases of both, core/idoutils and web, as well as provide information on the mysql server version.

[icinga-migration]

Updated by mfriedrich on 2012-11-20 17:32:25 +00:00

any feedback on this?

[icinga-migration]

Updated by mfriedrich on 2013-04-08 20:39:34 +00:00

• Status changed from New to Closed

closing for no feedback at all, possible related to #2703