Hello,
Can someone from Icinga2 Developers team advise on this query as in when it gets fired up and a way to avoid it?
It does run occasionally although I want to find what the occasion is. It creates huge MYD ad MYI SQL files in /tmp and consumes almost all the space in root filesystem.
icinga_ido | Query | 1088732 | Copying to tmp table | SELECT n.host_name, n.host_display_name, n.service_description, n.service_display_name, n.notification_output, n.notification_contact_name, n.notification_start_time, n.notification_state FROM (SELECT h.display_name COLLATE latin1_general_ci AS host_display_name, ho.name1 AS host_name, NULL AS service_description, NULL AS service_display_name, NULL AS service_host_name, hn.state AS notification_state, UNIX_TIMESTAMP(hn.start_time) AS notification_start_time, cno.name1 AS notification_contact_name, hn.output AS notification_output, hn.object_id AS notification_object_id, cno.object_id AS contact_object_id, UNIX_TIMESTAMP(a.entry_time) AS acknowledgement_entry_time, a.author_name AS acknowledgement_author_name, a.comment_data AS acknowledgement_comment_data, ('host') AS object_type, i.instance_name FROM icinga_notifications AS hn
INNER JOIN icinga_objects AS ho ON ho.object_id = hn.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
LEFT JOIN icinga_contactnotifications AS cn ON cn.notification_id = hn.notification_id
LEFT JOIN icinga_objects AS cno ON cno.object_id = cn.contact_object_id
LEFT JOIN icinga_acknowledgements AS a ON a.object_id = hn.object_id
INNER JOIN icinga_instances AS i ON i.instance_id = hn.instance_id GROUP BY hn.notification_id,
cno.object_id,
ho.object_id,
h.host_id,
a.acknowledgement_id,
i.instance_id UNION ALL SELECT h.display_name COLLATE latin1_general_ci AS host_display_name, so.name1 AS host_name, so.name2 AS service_description, s.display_name COLLATE latin1_general_ci AS service_display_name, so.name1 AS service_host_name, sn.state AS notification_state, UNIX_TIMESTAMP(sn.start_time) AS notification_start_time, cno.name1 AS notification_contact_name, sn.output AS notification_output, sn.object_id AS notification_object_id, cno.object_id AS contact_object_id, UNIX_TIMESTAMP(a.entry_time) AS acknowledgement_entry_time, a.author_name AS acknowledgement_author_name, a.comment_data AS acknowledgement_comment_data, ('service') AS object_type, i.instance_name FROM icinga_notifications AS sn
INNER JOIN icinga_objects AS so ON so.object_id = sn.object_id AND so.is_active = 1 AND so.objecttype_id = 2
INNER JOIN icinga_services AS s ON s.service_object_id = so.object_id
INNER JOIN icinga_hosts AS h ON h.host_object_id = s.host_object_id
LEFT JOIN icinga_contactnotifications AS cn ON cn.notification_id = sn.notification_id
LEFT JOIN icinga_objects AS cno ON cno.object_id = cn.contact_object_id
LEFT JOIN icinga_acknowledgements AS a ON a.object_id = sn.object_id
INNER JOIN icinga_instances AS i ON i.instance_id = sn.instance_id GROUP BY sn.notification_id,
cno.object_id,
so.object_id,
h.host_id,
s.service_id,
a.acknowledgement_id,
i.instance_id) AS n ORDER BY n.notification_start_time DESC LIMIT 5 |
Thanks