IDO SQL Query - Copying to tmp table - Filled up /tmp

Icinga 2
,
1

Hello,

I came across a Select SQL query being made by Master ( seen in ‘processlist’ ) to the DB creating 2 “#sql…MYx” files in /tmp. One of them was 77GB. After checking a day after, the files no longer exists and the query was not running. Based on the recent history, it does seem to use all /tmp space and mysqld.log did show the errors to repair the files which I think because there was no space left in /tmp.

I’m wondering what that query is for and why it created those files ? I gathered that a temp table may have been created under /tmp by the query. As per the file name, it seems the tables used “MyISAM” engine. Does that mean Icinga2/IDO use “MyISAM” engine to create a temp table?
All the tables of Icinga2 IDO and Icingaweb2 are using “InnoDB”

I can provide that query when I get to it soon.

Any idea what Icinga2 might have been running that query for?

icinga_ido | Query       |  2262125 | Copying to tmp table                                                  | SELECT COUNT(*) AS cnt FROM (SELECT h.display_name COLLATE latin1_general_ci AS host_display_name, h |

Thanks

2

Learning some on this issue. The files are likely created by the query since there is no enough memory available for the data. Once it finishes, the space should be regained.
The issue re-appeared again. I think this happens when the 'cleanup" starts. This is what I’ve

 cleanup = {
      
      logentries_age = 90d
      statehistory_age = 90d
    }

Howerver, I don’t understand why the query was run by config master instead of the secondary master ? Yes, I’ve 'enable_ha" set.

Thanks

3

Here is the query

| 119862 | icadmin | master.com:38924    | icinga_ido | Query       |  1469799 | 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 |
4

Hello moniga!

Have you tried

  1. less “aggressive” thresholds, i.e. keeping more (days of) history?
  2. not to clean up history at all? Some NETWAYS customers do this for performance reasons IIRC.

Best,
A/K