search

Icinga / icinga-web

Icinga Web 1.x, the old new web interface (EOL 31.12.2018)
22 stars 11 forks source link

[dev.icinga.com #12175] Long Running SQL from Web IDO #1379

Closed icinga-migration closed 6 years ago

icinga-migration commented 8 years ago

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

Created by Tux12Fun on 2016-07-19 09:07:51 +00:00

Assignee: (none) Status: New Target Version: (none) Last Update: 2016-07-19 09:19:37 +00:00 (in Redmine)

Hi,

I found the following SQL:

| 1671 | icinga_web_IDO  | localhost       | icinga2    | Query   |   51 | Copying to tmp table | SELECT DISTINCT i17.notification_id, i17.start_time AS i17__6 FROM ... |    0.000 |

MariaDB [icinga2]>  
SELECT DISTINCT i17.notification_id, 
                i17.start_time AS i17__6 
FROM   icinga_notifications i17 
       INNER JOIN icinga_objects i18 
               ON i17.object_id = i18.object_id 
       LEFT JOIN icinga_services i19 
              ON i18.object_id = i19.service_object_id 
       LEFT JOIN icinga_hosts i20 
              ON i18.object_id = i20.host_object_id 
       LEFT JOIN icinga_hosts i21 
              ON i19.host_object_id = i21.host_object_id 
       LEFT JOIN icinga_contactnotifications i22 
              ON i17.notification_id = i22.notification_id 
       LEFT JOIN icinga_contactnotificationmethods i23 
              ON i22.contactnotification_id = i23.contactnotification_id 
       LEFT JOIN icinga_commands i24 
              ON i23.command_object_id = i24.object_id 
       LEFT JOIN icinga_objects i25 
              ON i24.object_id = i25.object_id 
       LEFT JOIN icinga_objects i26 
              ON i22.contact_object_id = i26.object_id 
       LEFT JOIN icinga_hostgroup_members i28 
              ON ( i20.host_object_id = i28.host_object_id ) 
       LEFT JOIN icinga_hostgroups i27 
              ON i27.hostgroup_id = i28.hostgroup_id 
       LEFT JOIN icinga_objects i29 
              ON i27.hostgroup_object_id = i29.object_id 
       LEFT JOIN icinga_hostgroup_members i31 
              ON ( i21.host_object_id = i31.host_object_id ) 
       LEFT JOIN icinga_hostgroups i30 
              ON i30.hostgroup_id = i31.hostgroup_id 
       LEFT JOIN icinga_objects i32 
              ON i30.hostgroup_object_id = i32.object_id 
WHERE  (( ( i18.objecttype_id = 1 
            AND i29.name1 IN ( 'res.ce.it-hir.pcprog', 
                               'res.ce.it-hir.pcprog.ps', 
                                                'res.ce.it-hir.pcprog.pl', 
                                   'res.ce.it-hir.pcprog.at', 
                               'res.ce.it-hir.pcprog.pr', 
                               'res.ce.it-hir.pcprog.fi', 
                                                'res.ce.it-wbg' ) ) 
           OR ( i18.objecttype_id = 2 
                AND i32.name1 IN ( 'res.ce.it-hir.pcprog', 
                                   'res.ce.it-hir.pcprog.ps' , 
                                   'res.ce.it-hir.pcprog.pl', 
                                       'res.ce.it-hir.pcprog.at' , 
                                   'res.ce.it-hir.pcprog.pr', 
                                   'res.ce.it-hir.pcprog.fi', 
                                                    'res.ce.it-wbg' ) ) )) 
ORDER  BY i17__6 DESC 
LIMIT  25; 

+-----------------+---------------------+
| notification_id | i17__6              |
+-----------------+---------------------+
|           25031 | 2016-07-18 07:25:17 |
|           25032 | 2016-07-18 07:25:17 |
|           25033 | 2016-07-18 07:25:17 |
|           25028 | 2016-07-18 07:23:20 |
|           25029 | 2016-07-18 07:23:20 |
|           25030 | 2016-07-18 07:23:20 |
|           25025 | 2016-07-18 07:10:01 |
|           25026 | 2016-07-18 07:10:01 |
|           25027 | 2016-07-18 07:10:01 |
|           25024 | 2016-07-18 07:08:54 |
|           25022 | 2016-07-18 07:08:54 |
|           25023 | 2016-07-18 07:08:54 |
|           25019 | 2016-07-18 07:06:53 |
|           25020 | 2016-07-18 07:06:53 |
|           25021 | 2016-07-18 07:06:53 |
|           25016 | 2016-07-18 07:05:19 |
|           25017 | 2016-07-18 07:05:19 |
|           25018 | 2016-07-18 07:05:19 |
|           25014 | 2016-07-18 06:21:57 |
|           25015 | 2016-07-18 06:21:57 |
|           25012 | 2016-07-18 06:11:57 |
|           25013 | 2016-07-18 06:11:57 |
|           25011 | 2016-07-18 06:09:27 |
|           25010 | 2016-07-18 06:09:27 |
|           24942 | 2016-07-16 01:12:36 |
+-----------------+---------------------+
25 rows in set (1 min 1.96 sec)

Is there a housekeeping Job to cleanup the Database, or is it possible to improve the Statement?

SELECT count(*) from icinga_acknowledgements;
407
SELECT count(*) from icinga_commands;
2740
SELECT count(*) from icinga_commenthistory ;
416
SELECT count(*) from icinga_comments ;
17
SELECT count(*) from icinga_configfiles ;
0
SELECT count(*) from icinga_configfilevariables ;
0
SELECT count(*) from icinga_conninfo ;
566
SELECT count(*) from icinga_contact_addresses ;
0
SELECT count(*) from icinga_contact_notificationcommands ;
0
SELECT count(*) from icinga_contactgroup_members ;
0
SELECT count(*) from icinga_contactgroups ;
1
SELECT count(*) from icinga_contactnotificationmethods ;
0
SELECT count(*) from icinga_contactnotifications ;
35352
SELECT count(*) from icinga_contacts ;
42
SELECT count(*) from icinga_contactstatus ;
42
SELECT count(*) from icinga_customvariables;
17976
SELECT count(*) from icinga_customvariablestatus ;
17976
SELECT count(*) from icinga_dbversion;
1
SELECT count(*) from icinga_downtimehistory ;
102562
SELECT count(*) from icinga_endpoints ;
1
SELECT count(*) from icinga_endpointstatus ;
1
SELECT count(*) from icinga_eventhandlers ;
1913
SELECT count(*) from icinga_externalcommands ;
9746192
SELECT count(*) from icinga_flappinghistory ;
0
SELECT count(*) from icinga_host_contactgroups ;
0
SELECT count(*) from icinga_host_contacts ;
1157
SELECT count(*) from icinga_host_parenthosts ;
437
SELECT count(*) from icinga_hostchecks;
0
SELECT count(*) from icinga_hostdependencies ;
437
SELECT count(*) from icinga_hostescalation_contactgroups ;
0
SELECT count(*) from icinga_hostescalation_contacts ;
0
SELECT count(*) from icinga_hostescalations ;
0
SELECT count(*) from icinga_hostgroup_members ;
3499
SELECT count(*) from icinga_hostgroups;
99
SELECT count(*) from icinga_hosts ;
585
SELECT count(*) from icinga_hoststatus ;
585
SELECT count(*) from icinga_instances ;
1
SELECT count(*) from icinga_logentries ;
1044549
SELECT count(*) from icinga_notifications ;
25192
SELECT count(*) from icinga_objects ;
7595
SELECT count(*) from icinga_processevents ;
0
SELECT count(*) from icinga_programstatus;
1
SELECT count(*) from icinga_runtimevariables;
4
SELECT count(*) from icinga_scheduleddowntime ;
775
SELECT count(*) from icinga_service_contactgroups ;
0
SELECT count(*) from icinga_service_contacts ;
4834
SELECT count(*) from icinga_servicechecks ;
0
SELECT count(*) from icinga_servicedependencies ;
2954
SELECT count(*) from icinga_serviceescalation_contactgroups ;
0
SELECT count(*) from icinga_serviceescalation_contacts ;
0
SELECT count(*) from icinga_serviceescalations ;
0
SELECT count(*) from icinga_servicegroup_members ;
412
SELECT count(*) from icinga_servicegroups ;
8
SELECT count(*) from icinga_services ;
4107
SELECT count(*) from icinga_servicestatus ;
4106
SELECT count(*) from icinga_statehistory ;
749008
SELECT count(*) from icinga_systemcommands ;
0
SELECT count(*) from icinga_timeperiod_timeranges ;
71
SELECT count(*) from icinga_timeperiods ;
10
SELECT count(*) from icinga_zones ;
1
SELECT count(*) from icinga_zonestatus ;
1
icinga-migration commented 8 years ago

Updated by elippmann on 2016-07-19 09:19:37 +00:00