Hi
Once in a while one of our filesystems is completely filled with mysql tmp
files, to a total of 30G tmp files.
A restart of the mysqld frees the filesystem, and the system runs till the next
time the filesystem fills up.
The system is running on up-to-date openSuSE 13.2 (to be replaced with either
CentOS or Leap in the near future), with icinga2 2.6.2 and mariadb 10.0.27, but
we have had the problem before with other versions of icinga2 and mariadb.
Architecture of the system is i686.
Today we looked in the slow-query log and the last statement before we
restarted the system was the following query:
Time: 170220 16:35;26
# User@Host: .........
#Thread_id: 66089 Schema: icinga2 QC_hit: No
# Query)time: 48132.917737 Lock_time: 0.001244 Rows_sent: 0 Rows_examined: 0
SET timestamp=1487604926;
SELECT COUNT(*) AS cnt 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;
/usr/sbin/mysqld, Version: 10.0.27-MariaDB (openSUSE package). started with:
I don't have any idea why the query is running so long and making these tmp
files, anyone any clue about this?
Hans Scheffers
AIX / Linux Systeembeheer
_______________________________________________
icinga-users mailing list
icinga-users@lists.icinga.org
https://lists.icinga.org/mailman/listinfo/icinga-users
