Skip to content
This repository has been archived by the owner on Dec 13, 2022. It is now read-only.

Top Counter with acl really slow #5974

Closed
garnier-quentin opened this issue Dec 22, 2017 · 18 comments
Closed

Top Counter with acl really slow #5974

garnier-quentin opened this issue Dec 22, 2017 · 18 comments
Labels
area/monitoring kind/bug priority/critical

Comments

@garnier-quentin
Copy link
Contributor

BUG REPORT INFORMATION

Centreon Web version: 2.8.16

Steps to reproduce the issue:

  1. Many services with ACL

Describe the results you received:
The request for top counter is slow with acl

EXPLAIN SELECT SUM(CASE WHEN s.state = 0 THEN 1 ELSE 0 END) AS OK_TOTAL, SUM(CASE WHEN s.state = 1 THEN 1 ELSE 0 END) AS WARNING_TOTAL, SUM(CASE WHEN s.state = 1 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS WARNING_ACK_DT, SUM(CASE WHEN s.state = 2 THEN 1 ELSE 0 END) AS CRITICAL_TOTAL, SUM(CASE WHEN s.state = 2 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS CRITICAL_ACK_DT, SUM(CASE WHEN s.state = 3 THEN 1 ELSE 0 END) AS UNKNOWN_TOTAL, SUM(CASE WHEN s.state = 3 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS UNKNOWN_ACK_DT, SUM(CASE WHEN s.state = 4 THEN 1 ELSE 0 END) AS PENDING_TOTAL FROM hosts h, services s, instances i WHERE i.deleted = 0 AND h.enabled = 1 AND s.enabled = 1 AND i.instance_id = h.instance_id AND h.host_id = s.host_id AND (h.name NOT LIKE '_Module_%' OR h.name LIKE '_Module_Meta%') AND s.service_id IN ( SELECT DISTINCT service_id FROM centreon_acl WHERE centreon_acl.group_id IN ('33','34','14'));
+------+--------------+--------------+--------+------------------------------------+------------------+---------+--------------------------------+------+-----------------------+
| id   | select_type  | table        | type   | possible_keys                      | key              | key_len | ref                            | rows | Extra                 |
+------+--------------+--------------+--------+------------------------------------+------------------+---------+--------------------------------+------+-----------------------+
|    1 | PRIMARY      | i            | ALL    | PRIMARY                            | NULL             | NULL    | NULL                           |    1 | Using where           |
|    1 | PRIMARY      | h            | ref    | host_id,instance_id                | instance_id      | 4       | centreon_storage.i.instance_id |   65 | Using where           |
|    1 | PRIMARY      | s            | ref    | host_id,service_id                 | host_id          | 4       | centreon_storage.h.host_id     |    9 | Using where           |
|    1 | PRIMARY      | <subquery2>  | eq_ref | distinct_key                       | distinct_key     | 4       | func                           |    1 |                       |
|    2 | MATERIALIZED | centreon_acl | range  | group_id_by_name,group_id_for_host | group_id_by_name | 5       | NULL                           | 1940 | Using index condition |
+------+--------------+--------------+--------+------------------------------------+------------------+---------+--------------------------------+------+-----------------------+

Describe the results you expected:
Need to replace:

if (!$obj->is_admin) {
    $query_svc_status .=  "AND s.service_id IN ( " .
        "SELECT DISTINCT service_id " .
        "FROM centreon_acl " .
        "WHERE centreon_acl.group_id IN (" . $obj->grouplistStr . ")) ";
}

By:

if (!$obj->is_admin) {
    $query_svc_status .=  "AND EXISTS (SELECT service_id FROM centreon_acl WHERE centreon_acl.host_id = h.host_id AND centreon_acl.service_id = s.service_id AND  centreon_acl.group_id IN (" . $obj->grouplistStr . ")) ";
}

Additional information you think important (e.g. issue happens only occasionally):
Better result with a good query:

EXPLAIN SELECT SUM(CASE WHEN s.state = 0 THEN 1 ELSE 0 END) AS OK_TOTAL, SUM(CASE WHEN s.state = 1 THEN 1 ELSE 0 END) AS WARNING_TOTAL, SUM(CASE WHEN s.state = 1 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS WARNING_ACK_DT, SUM(CASE WHEN s.state = 2 THEN 1 ELSE 0 END) AS CRITICAL_TOTAL, SUM(CASE WHEN s.state = 2 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS CRITICAL_ACK_DT, SUM(CASE WHEN s.state = 3 THEN 1 ELSE 0 END) AS UNKNOWN_TOTAL, SUM(CASE WHEN s.state = 3 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS UNKNOWN_ACK_DT, SUM(CASE WHEN s.state = 4 THEN 1 ELSE 0 END) AS PENDING_TOTAL FROM hosts h, services s, instances i WHERE i.deleted = 0 AND h.enabled = 1 AND s.enabled = 1 AND i.instance_id = h.instance_id AND h.host_id = s.host_id AND (h.name NOT LIKE '_Module_%' OR h.name LIKE '_Module_Meta%') AND EXISTS (SELECT service_id FROM centreon_acl WHERE centreon_acl.host_id = h.host_id AND centreon_acl.service_id = s.service_id AND centreon_acl.group_id IN ('33','34','14'));
+------+--------------------+--------------+------+---------------------------------------------------+----------------+---------+----------------------------------------------------------+------+--------------------------+
| id   | select_type        | table        | type | possible_keys                                     | key            | key_len | ref                                                      | rows | Extra                    |
+------+--------------------+--------------+------+---------------------------------------------------+----------------+---------+----------------------------------------------------------+------+--------------------------+
|    1 | PRIMARY            | i            | ALL  | PRIMARY                                           | NULL           | NULL    | NULL                                                     |    1 | Using where              |
|    1 | PRIMARY            | h            | ref  | host_id,instance_id                               | instance_id    | 4       | centreon_storage.i.instance_id                           |   65 | Using where              |
|    1 | PRIMARY            | s            | ref  | host_id                                           | host_id        | 4       | centreon_storage.h.host_id                               |    9 | Using where              |
|    2 | DEPENDENT SUBQUERY | centreon_acl | ref  | group_id_by_name,group_id_by_id,group_id_for_host | group_id_by_id | 10      | centreon_storage.h.host_id,centreon_storage.s.service_id |    5 | Using where; Using index |
+------+--------------------+--------------+------+---------------------------------------------------+----------------+---------+----------------------------------------------------------+------+--------------------------+
@jben94
Copy link

jben94 commented Dec 28, 2017

Hi,

After testing the queries, the first is faster compared to the second.
You use 'Indexes' which I do not have, by looking at the installation files of zero and update. There is a difference.

First request :

mysql> SELECT SUM(CASE WHEN s.state = 0 THEN 1 ELSE 0 END) AS OK_TOTAL, SUM(CASE WHEN s.state = 1 THEN 1 ELSE 0 END) AS WARNING_TOTAL, SUM(CASE WHEN s.state = 1 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS WARNING_ACK_DT, SUM(CASE WHEN s.state = 2 THEN 1 ELSE 0 END) AS CRITICAL_TOTAL, SUM(CASE WHEN s.state = 2 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS CRITICAL_ACK_DT, SUM(CASE WHEN s.state = 3 THEN 1 ELSE 0 END) AS UNKNOWN_TOTAL, SUM(CASE WHEN s.state = 3 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS UNKNOWN_ACK_DT, SUM(CASE WHEN s.state = 4 THEN 1 ELSE 0 END) AS PENDING_TOTAL FROM hosts h, services s, instances i WHERE i.deleted = 0 AND h.enabled = 1 AND s.enabled = 1 AND i.instance_id = h.instance_id AND h.host_id = s.host_id AND (h.name NOT LIKE '_Module_%' OR h.name LIKE '_Module_Meta%') AND s.service_id IN ( SELECT DISTINCT service_id FROM centreon_acl WHERE centreon_acl.group_id IN ('33','34','14'));
+----------+---------------+----------------+----------------+-----------------+---------------+----------------+---------------+
| OK_TOTAL | WARNING_TOTAL | WARNING_ACK_DT | CRITICAL_TOTAL | CRITICAL_ACK_DT | UNKNOWN_TOTAL | UNKNOWN_ACK_DT | PENDING_TOTAL |
+----------+---------------+----------------+----------------+-----------------+---------------+----------------+---------------+
|      969 |             3 |              0 |              2 |               0 |             6 |              6 |             0 |
+----------+---------------+----------------+----------------+-----------------+---------------+----------------+---------------+
1 row in set (0.01 sec)

mysql> explain SELECT SUM(CASE WHEN s.state = 0 THEN 1 ELSE 0 END) AS OK_TOTAL, SUM(CASE WHEN s.state = 1 THEN 1 ELSE 0 END) AS WARNING_TOTAL, SUM(CASE WHEN s.state = 1 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS WARNING_ACK_DT, SUM(CASE WHEN s.state = 2 THEN 1 ELSE 0 END) AS CRITICAL_TOTAL, SUM(CASE WHEN s.state = 2 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS CRITICAL_ACK_DT, SUM(CASE WHEN s.state = 3 THEN 1 ELSE 0 END) AS UNKNOWN_TOTAL, SUM(CASE WHEN s.state = 3 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS UNKNOWN_ACK_DT, SUM(CASE WHEN s.state = 4 THEN 1 ELSE 0 END) AS PENDING_TOTAL FROM hosts h, services s, instances i WHERE i.deleted = 0 AND h.enabled = 1 AND s.enabled = 1 AND i.instance_id = h.instance_id AND h.host_id = s.host_id AND (h.name NOT LIKE '_Module_%' OR h.name LIKE '_Module_Meta%') AND s.service_id IN ( SELECT DISTINCT service_id FROM centreon_acl WHERE centreon_acl.group_id IN ('33','34','14'));
+----+-------------+--------------+--------+-------------------------------------------+------------+---------+------------------------------------------+------+----------------------------------------------------+
| id | select_type | table        | type   | possible_keys                             | key        | key_len | ref                                      | rows | Extra                                              |
+----+-------------+--------------+--------+-------------------------------------------+------------+---------+------------------------------------------+------+----------------------------------------------------+
|  1 | SIMPLE      | centreon_acl | range  | group_id_by_name,group_id_for_host,index1 | index1     | 5       | NULL                                     | 1180 | Using where; Using index; Start temporary          |
|  1 | SIMPLE      | s            | ref    | host_id,service_id                        | service_id | 4       | centreon_storage.centreon_acl.service_id |    1 | Using where; End temporary                         |
|  1 | SIMPLE      | h            | eq_ref | host_id,instance_id                       | host_id    | 4       | centreon_storage.s.host_id               |    1 | Using where                                        |
|  1 | SIMPLE      | i            | ALL    | PRIMARY                                   | NULL       | NULL    | NULL                                     |   12 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+--------------+--------+-------------------------------------------+------------+---------+------------------------------------------+------+----------------------------------------------------+
4 rows in set (0.00 sec)

Second request :

mysql> SELECT SUM(CASE WHEN s.state = 0 THEN 1 ELSE 0 END) AS OK_TOTAL, SUM(CASE WHEN s.state = 1 THEN 1 ELSE 0 END) AS WARNING_TOTAL, SUM(CASE WHEN s.state = 1 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS WARNING_ACK_DT, SUM(CASE WHEN s.state = 2 THEN 1 ELSE 0 END) AS CRITICAL_TOTAL, SUM(CASE WHEN s.state = 2 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS CRITICAL_ACK_DT, SUM(CASE WHEN s.state = 3 THEN 1 ELSE 0 END) AS UNKNOWN_TOTAL, SUM(CASE WHEN s.state = 3 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS UNKNOWN_ACK_DT, SUM(CASE WHEN s.state = 4 THEN 1 ELSE 0 END) AS PENDING_TOTAL FROM hosts h, services s, instances i WHERE i.deleted = 0 AND h.enabled = 1 AND s.enabled = 1 AND i.instance_id = h.instance_id AND h.host_id = s.host_id AND (h.name NOT LIKE '_Module_%' OR h.name LIKE '_Module_Meta%') AND EXISTS (SELECT service_id FROM centreon_acl WHERE centreon_acl.host_id = h.host_id AND centreon_acl.service_id = s.service_id AND centreon_acl.group_id IN ('33','34','14'));
+----------+---------------+----------------+----------------+-----------------+---------------+----------------+---------------+
| OK_TOTAL | WARNING_TOTAL | WARNING_ACK_DT | CRITICAL_TOTAL | CRITICAL_ACK_DT | UNKNOWN_TOTAL | UNKNOWN_ACK_DT | PENDING_TOTAL |
+----------+---------------+----------------+----------------+-----------------+---------------+----------------+---------------+
|      969 |             3 |              0 |              2 |               0 |             6 |              6 |             0 |
+----------+---------------+----------------+----------------+-----------------+---------------+----------------+---------------+
1 row in set (25.49 sec)

mysql> explain SELECT SUM(CASE WHEN s.state = 0 THEN 1 ELSE 0 END) AS OK_TOTAL, SUM(CASE WHEN s.state = 1 THEN 1 ELSE 0 END) AS WARNING_TOTAL, SUM(CASE WHEN s.state = 1 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS WARNING_ACK_DT, SUM(CASE WHEN s.state = 2 THEN 1 ELSE 0 END) AS CRITICAL_TOTAL, SUM(CASE WHEN s.state = 2 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS CRITICAL_ACK_DT, SUM(CASE WHEN s.state = 3 THEN 1 ELSE 0 END) AS UNKNOWN_TOTAL, SUM(CASE WHEN s.state = 3 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS UNKNOWN_ACK_DT, SUM(CASE WHEN s.state = 4 THEN 1 ELSE 0 END) AS PENDING_TOTAL FROM hosts h, services s, instances i WHERE i.deleted = 0 AND h.enabled = 1 AND s.enabled = 1 AND i.instance_id = h.instance_id AND h.host_id = s.host_id AND (h.name NOT LIKE '_Module_%' OR h.name LIKE '_Module_Meta%') AND EXISTS (SELECT service_id FROM centreon_acl WHERE centreon_acl.host_id = h.host_id AND centreon_acl.service_id = s.service_id AND centreon_acl.group_id IN ('33','34','14'));
+----+--------------------+--------------+-------+-------------------------------------------+-------------+---------+--------------------------------+------+--------------------------+
| id | select_type        | table        | type  | possible_keys                             | key         | key_len | ref                            | rows | Extra                    |
+----+--------------------+--------------+-------+-------------------------------------------+-------------+---------+--------------------------------+------+--------------------------+
|  1 | PRIMARY            | i            | ALL   | PRIMARY                                   | NULL        | NULL    | NULL                           |   12 | Using where              |
|  1 | PRIMARY            | h            | ref   | host_id,instance_id                       | instance_id | 4       | centreon_storage.i.instance_id |  315 | Using where              |
|  1 | PRIMARY            | s            | ref   | host_id                                   | host_id     | 4       | centreon_storage.h.host_id     |    6 | Using where              |
|  2 | DEPENDENT SUBQUERY | centreon_acl | range | group_id_by_name,group_id_for_host,index1 | index1      | 5       | NULL                           | 1180 | Using where; Using index |
+----+--------------------+--------------+-------+-------------------------------------------+-------------+---------+--------------------------------+------+--------------------------+
4 rows in set (0.00 sec)

I think we should review the indexes of this table, so that everyone is at the same level.
The index 'group_id_by_id' that you use is not present on my table, but 'index1' yes.

Regards

@garnier-quentin
Copy link
Contributor Author

garnier-quentin commented Dec 28, 2017 •
edited
Loading

Index have changed in 2.8.x... I'm not sure that all acl requests have changed. The index is better (you can remove the index on host_id/group_id). But in the upgrade, it seems we didn't remove the host_id/group_id...

So could you test following request ?

SELECT SUM(CASE WHEN s.state = 0 THEN 1 ELSE 0 END) AS OK_TOTAL, SUM(CASE WHEN s.state = 1 THEN 1 ELSE 0 END) AS WARNING_TOTAL, SUM(CASE WHEN s.state = 1 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS WARNING_ACK_DT, SUM(CASE WHEN s.state = 2 THEN 1 ELSE 0 END) AS CRITICAL_TOTAL, SUM(CASE WHEN s.state = 2 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS CRITICAL_ACK_DT, SUM(CASE WHEN s.state = 3 THEN 1 ELSE 0 END) AS UNKNOWN_TOTAL, SUM(CASE WHEN s.state = 3 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS UNKNOWN_ACK_DT, SUM(CASE WHEN s.state = 4 THEN 1 ELSE 0 END) AS PENDING_TOTAL FROM hosts h, services s, instances i WHERE 
i.deleted = 0 AND i.instance_id = h.instance_id AND h.enabled = 1 AND (h.name NOT LIKE '_Module_%' OR h.name LIKE '_Module_Meta%') AND 
h.host_id = s.host_id AND s.enabled = 1 AND
AND EXISTS (SELECT service_id FROM centreon_acl WHERE centreon_acl.group_id IN ('33','34','14') AND centreon_acl.host_id = h.host_id AND centreon_acl.service_id = s.service_id);

@jben94
Copy link

jben94 commented Dec 29, 2017 •
edited
Loading

Hi,

You put 'AND' too much in your request, at the end.

s.enabled = 1 AND
AND EXISTS

So I modified it, here is the result:

mysql> SELECT SUM(CASE WHEN s.state = 0 THEN 1 ELSE 0 END) AS OK_TOTAL, SUM(CASE WHEN s.state = 1 THEN 1 ELSE 0 END) AS WARNING_TOTAL, SUM(CASE WHEN s.state = 1 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS WARNING_ACK_DT, SUM(CASE WHEN s.state = 2 THEN 1 ELSE 0 END) AS CRITICAL_TOTAL, SUM(CASE WHEN s.state = 2 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS CRITICAL_ACK_DT, SUM(CASE WHEN s.state = 3 THEN 1 ELSE 0 END) AS UNKNOWN_TOTAL, SUM(CASE WHEN s.state = 3 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS UNKNOWN_ACK_DT, SUM(CASE WHEN s.state = 4 THEN 1 ELSE 0 END) AS PENDING_TOTAL FROM hosts h, services s, instances i WHERE
    -> i.deleted = 0 AND i.instance_id = h.instance_id AND h.enabled = 1 AND (h.name NOT LIKE '_Module_%' OR h.name LIKE '_Module_Meta%') AND
    -> h.host_id = s.host_id AND s.enabled = 1 AND
    -> EXISTS (SELECT service_id FROM centreon_acl WHERE centreon_acl.group_id IN ('33','34','14') AND centreon_acl.host_id = h.host_id AND centreon_acl.service_id = s.service_id);
+----------+---------------+----------------+----------------+-----------------+---------------+----------------+---------------+
| OK_TOTAL | WARNING_TOTAL | WARNING_ACK_DT | CRITICAL_TOTAL | CRITICAL_ACK_DT | UNKNOWN_TOTAL | UNKNOWN_ACK_DT | PENDING_TOTAL |
+----------+---------------+----------------+----------------+-----------------+---------------+----------------+---------------+
|      968 |             4 |              0 |              2 |               0 |             6 |              6 |             0 |
+----------+---------------+----------------+----------------+-----------------+---------------+----------------+---------------+
1 row in set (27.94 sec)

Here is the list of files using 'centreon_acl':

image

Here is the list of Indexes, which I have after updates since the last version 2.6:

image

Here is the construction of the table, createTablesCentstorage.sql file:

CREATE TABLE centreon_acl (
host_id int(11) DEFAULT NULL,
service_id int(11) DEFAULT NULL,
group_id int(11) DEFAULT NULL,
KEY index1 (group_id,host_id,service_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Here are the changes in the update, file 'Update-CSTG-2.8.0-beta2_to_2.8.0.sql':

ALTER TABLE centreon_acl DROP INDEX group_id_by_id;
ALTER TABLE centreon_acl ADD INDEX index1 (group_id,host_id,service_id);

@garnier-quentin
Copy link
Contributor Author

Could you give me the explain ?

@jben94
Copy link

jben94 commented Dec 29, 2017 

+----+--------------------+--------------+-------+-------------------------------------------+-------------+---------+--------------------------------+------+--------------------------+
| id | select_type        | table        | type  | possible_keys                             | key         | key_len | ref                            | rows | Extra                    |
+----+--------------------+--------------+-------+-------------------------------------------+-------------+---------+--------------------------------+------+--------------------------+
|  1 | PRIMARY            | i            | ALL   | PRIMARY                                   | NULL        | NULL    | NULL                           |   12 | Using where              |
|  1 | PRIMARY            | h            | ref   | host_id,instance_id                       | instance_id | 4       | centreon_storage.i.instance_id |  315 | Using where              |
|  1 | PRIMARY            | s            | ref   | host_id                                   | host_id     | 4       | centreon_storage.h.host_id     |    6 | Using where              |
|  2 | DEPENDENT SUBQUERY | centreon_acl | range | group_id_by_name,group_id_for_host,index1 | index1      | 5       | NULL                           | 1180 | Using where; Using index |
+----+--------------------+--------------+-------+-------------------------------------------+-------------+---------+--------------------------------+------+--------------------------+
4 rows in set (0.00 sec)

@garnier-quentin
Copy link
Contributor Author

I have looked. And EXISTS is far better with large result sets. But the problem is the index. EXISTS is better if we filter directly with the top counter. So we need to add the other index:

ALTER TABLE centreon_acl ADD INDEX `index2` (`host_id`,`service_id`, `group_id`);

And try following requests:

SELECT SUM(CASE WHEN s.state = 0 THEN 1 ELSE 0 END) AS OK_TOTAL, SUM(CASE WHEN s.state = 1 THEN 1 ELSE 0 END) AS WARNING_TOTAL, SUM(CASE WHEN s.state = 1 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS WARNING_ACK_DT, SUM(CASE WHEN s.state = 2 THEN 1 ELSE 0 END) AS CRITICAL_TOTAL, SUM(CASE WHEN s.state = 2 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS CRITICAL_ACK_DT, SUM(CASE WHEN s.state = 3 THEN 1 ELSE 0 END) AS UNKNOWN_TOTAL, SUM(CASE WHEN s.state = 3 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS UNKNOWN_ACK_DT, SUM(CASE WHEN s.state = 4 THEN 1 ELSE 0 END) AS PENDING_TOTAL FROM hosts h, services s, instances i WHERE i.deleted = 0 AND i.instance_id = h.instance_id AND h.enabled = 1 AND (h.name NOT LIKE '_Module_%' OR h.name LIKE '_Module_Meta%') AND h.host_id = s.host_id AND s.enabled = 1 AND EXISTS (SELECT service_id FROM centreon_acl WHERE h.host_id = centreon_acl.host_id AND s.service_id = centreon_acl.service_id AND centreon_acl.group_id IN ('33','34','14'));

@jben94
Copy link

jben94 commented Dec 29, 2017

Yes, it works better. But it takes even longer than the current request:

mysql> SELECT SUM(CASE WHEN s.state = 0 THEN 1 ELSE 0 END) AS OK_TOTAL, SUM(CASE WHEN s.state = 1 THEN 1 ELSE 0 END) AS WARNING_TOTAL, SUM(CASE WHEN s.state = 1 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS WARNING_ACK_DT, SUM(CASE WHEN s.state = 2 THEN 1 ELSE 0 END) AS CRITICAL_TOTAL, SUM(CASE WHEN s.state = 2 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS CRITICAL_ACK_DT, SUM(CASE WHEN s.state = 3 THEN 1 ELSE 0 END) AS UNKNOWN_TOTAL, SUM(CASE WHEN s.state = 3 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS UNKNOWN_ACK_DT, SUM(CASE WHEN s.state = 4 THEN 1 ELSE 0 END) AS PENDING_TOTAL FROM hosts h, services s, instances i WHERE i.deleted = 0 AND i.instance_id = h.instance_id AND h.enabled = 1 AND (h.name NOT LIKE '_Module_%' OR h.name LIKE '_Module_Meta%') AND h.host_id = s.host_id AND s.enabled = 1 AND EXISTS (SELECT service_id FROM centreon_acl WHERE h.host_id = centreon_acl.host_id AND s.service_id = centreon_acl.service_id AND centreon_acl.group_id IN ('33','34','14'));
+----------+---------------+----------------+----------------+-----------------+---------------+----------------+---------------+
| OK_TOTAL | WARNING_TOTAL | WARNING_ACK_DT | CRITICAL_TOTAL | CRITICAL_ACK_DT | UNKNOWN_TOTAL | UNKNOWN_ACK_DT | PENDING_TOTAL |
+----------+---------------+----------------+----------------+-----------------+---------------+----------------+---------------+
|      969 |             3 |              0 |              2 |               0 |             6 |              6 |             0 |
+----------+---------------+----------------+----------------+-----------------+---------------+----------------+---------------+
1 row in set (0.32 sec)

mysql> EXPLAIN SELECT SUM(CASE WHEN s.state = 0 THEN 1 ELSE 0 END) AS OK_TOTAL, SUM(CASE WHEN s.state = 1 THEN 1 ELSE 0 END) AS WARNING_TOTAL, SUM(CASE WHEN s.state = 1 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS WARNING_ACK_DT, SUM(CASE WHEN s.state = 2 THEN 1 ELSE 0 END) AS CRITICAL_TOTAL, SUM(CASE WHEN s.state = 2 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS CRITICAL_ACK_DT, SUM(CASE WHEN s.state = 3 THEN 1 ELSE 0 END) AS UNKNOWN_TOTAL, SUM(CASE WHEN s.state = 3 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS UNKNOWN_ACK_DT, SUM(CASE WHEN s.state = 4 THEN 1 ELSE 0 END) AS PENDING_TOTAL FROM hosts h, services s, instances i WHERE i.deleted = 0 AND i.instance_id = h.instance_id AND h.enabled = 1 AND (h.name NOT LIKE '_Module_%' OR h.name LIKE '_Module_Meta%') AND h.host_id = s.host_id AND s.enabled = 1 AND EXISTS (SELECT service_id FROM centreon_acl WHERE h.host_id = centreon_acl.host_id AND s.service_id = centreon_acl.service_id AND centreon_acl.group_id IN ('33','34','14'));
+----+--------------------+--------------+------+--------------------------------------------------+-------------+---------+----------------------------------------------------------+------+--------------------------+
| id | select_type        | table        | type | possible_keys                                    | key         | key_len | ref                                                      | rows | Extra                    |
+----+--------------------+--------------+------+--------------------------------------------------+-------------+---------+----------------------------------------------------------+------+--------------------------+
|  1 | PRIMARY            | i            | ALL  | PRIMARY                                          | NULL        | NULL    | NULL                                                     |   12 | Using where              |
|  1 | PRIMARY            | h            | ref  | host_id,instance_id                              | instance_id | 4       | centreon_storage.i.instance_id                           |  315 | Using where              |
|  1 | PRIMARY            | s            | ref  | host_id                                          | host_id     | 4       | centreon_storage.h.host_id                               |    6 | Using where              |
|  2 | DEPENDENT SUBQUERY | centreon_acl | ref  | group_id_by_name,group_id_for_host,index1,index2 | index2      | 10      | centreon_storage.h.host_id,centreon_storage.s.service_id |    3 | Using where; Using index |
+----+--------------------+--------------+------+--------------------------------------------------+-------------+---------+----------------------------------------------------------+------+--------------------------+
4 rows in set (0.00 sec)

Comparison, (Even after several launch):
For infomation: We are under MySQL and not MariaDB.

mysql> SELECT SUM(CASE WHEN s.state = 0 THEN 1 ELSE 0 END) AS OK_TOTAL, SUM(CASE WHEN s.state = 1 THEN 1 ELSE 0 END) AS WARNING_TOTAL, SUM(CASE WHEN s.state = 1 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS WARNING_ACK_DT, SUM(CASE WHEN s.state = 2 THEN 1 ELSE 0 END) AS CRITICAL_TOTAL, SUM(CASE WHEN s.state = 2 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS CRITICAL_ACK_DT, SUM(CASE WHEN s.state = 3 THEN 1 ELSE 0 END) AS UNKNOWN_TOTAL, SUM(CASE WHEN s.state = 3 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS UNKNOWN_ACK_DT, SUM(CASE WHEN s.state = 4 THEN 1 ELSE 0 END) AS PENDING_TOTAL FROM hosts h, services s, instances i WHERE i.deleted = 0 AND h.enabled = 1 AND s.enabled = 1 AND i.instance_id = h.instance_id AND h.host_id = s.host_id AND (h.name NOT LIKE '_Module_%' OR h.name LIKE '_Module_Meta%') AND s.service_id IN ( SELECT DISTINCT service_id FROM centreon_acl WHERE centreon_acl.group_id IN ('33','34','14'));
+----------+---------------+----------------+----------------+-----------------+---------------+----------------+---------------+
| OK_TOTAL | WARNING_TOTAL | WARNING_ACK_DT | CRITICAL_TOTAL | CRITICAL_ACK_DT | UNKNOWN_TOTAL | UNKNOWN_ACK_DT | PENDING_TOTAL |
+----------+---------------+----------------+----------------+-----------------+---------------+----------------+---------------+
|      969 |             3 |              0 |              2 |               0 |             6 |              6 |             0 |
+----------+---------------+----------------+----------------+-----------------+---------------+----------------+---------------+
1 row in set (0.01 sec)

mysql> SELECT SUM(CASE WHEN s.state = 0 THEN 1 ELSE 0 END) AS OK_TOTAL, SUM(CASE WHEN s.state = 1 THEN 1 ELSE 0 END) AS WARNING_TOTAL, SUM(CASE WHEN s.state = 1 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS WARNING_ACK_DT, SUM(CASE WHEN s.state = 2 THEN 1 ELSE 0 END) AS CRITICAL_TOTAL, SUM(CASE WHEN s.state = 2 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS CRITICAL_ACK_DT, SUM(CASE WHEN s.state = 3 THEN 1 ELSE 0 END) AS UNKNOWN_TOTAL, SUM(CASE WHEN s.state = 3 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS UNKNOWN_ACK_DT, SUM(CASE WHEN s.state = 4 THEN 1 ELSE 0 END) AS PENDING_TOTAL FROM hosts h, services s, instances i WHERE i.deleted = 0 AND i.instance_id = h.instance_id AND h.enabled = 1 AND (h.name NOT LIKE '_Module_%' OR h.name LIKE '_Module_Meta%') AND h.host_id = s.host_id AND s.enabled = 1 AND EXISTS (SELECT service_id FROM centreon_acl WHERE h.host_id = centreon_acl.host_id AND s.service_id = centreon_acl.service_id AND centreon_acl.group_id IN ('33','34','14'));
+----------+---------------+----------------+----------------+-----------------+---------------+----------------+---------------+
| OK_TOTAL | WARNING_TOTAL | WARNING_ACK_DT | CRITICAL_TOTAL | CRITICAL_ACK_DT | UNKNOWN_TOTAL | UNKNOWN_ACK_DT | PENDING_TOTAL |
+----------+---------------+----------------+----------------+-----------------+---------------+----------------+---------------+
|      969 |             3 |              0 |              2 |               0 |             6 |              6 |             0 |
+----------+---------------+----------------+----------------+-----------------+---------------+----------------+---------------+
1 row in set (0.30 sec)

@garnier-quentin
Copy link
Contributor Author

EXISTS is slower on small sets (than IN). But it's ok (it's quite the same). but it's very faster on large sets.

@jben94
Copy link

jben94 commented Dec 29, 2017

Small sets ? On which scale (above 50000) ?

mysql> SELECT SUM(CASE WHEN s.state = 0 THEN 1 ELSE 0 END) AS OK_TOTAL, SUM(CASE WHEN s.state = 1 THEN 1 ELSE 0 END) AS WARNING_TOTAL, SUM(CASE WHEN s.state = 1 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS WARNING_ACK_DT, SUM(CASE WHEN s.state = 2 THEN 1 ELSE 0 END) AS CRITICAL_TOTAL, SUM(CASE WHEN s.state = 2 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS CRITICAL_ACK_DT, SUM(CASE WHEN s.state = 3 THEN 1 ELSE 0 END) AS UNKNOWN_TOTAL, SUM(CASE WHEN s.state = 3 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS UNKNOWN_ACK_DT, SUM(CASE WHEN s.state = 4 THEN 1 ELSE 0 END) AS PENDING_TOTAL FROM hosts h, services s, instances i WHERE i.deleted = 0 AND i.instance_id = h.instance_id AND h.enabled = 1 AND (h.name NOT LIKE '_Module_%' OR h.name LIKE '_Module_Meta%') AND h.host_id = s.host_id AND s.enabled = 1 AND EXISTS (SELECT service_id FROM centreon_acl WHERE h.host_id = centreon_acl.host_id AND s.service_id = centreon_acl.service_id AND centreon_acl.group_id IN ('3'));
+----------+---------------+----------------+----------------+-----------------+---------------+----------------+---------------+
| OK_TOTAL | WARNING_TOTAL | WARNING_ACK_DT | CRITICAL_TOTAL | CRITICAL_ACK_DT | UNKNOWN_TOTAL | UNKNOWN_ACK_DT | PENDING_TOTAL |
+----------+---------------+----------------+----------------+-----------------+---------------+----------------+---------------+
|    44689 |            91 |             27 |             45 |              16 |           500 |             20 |             0 |
+----------+---------------+----------------+----------------+-----------------+---------------+----------------+---------------+
1 row in set (0.21 sec)

mysql> SELECT SUM(CASE WHEN s.state = 0 THEN 1 ELSE 0 END) AS OK_TOTAL, SUM(CASE WHEN s.state = 1 THEN 1 ELSE 0 END) AS WARNING_TOTAL, SUM(CASE WHEN s.state = 1 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS WARNING_ACK_DT, SUM(CASE WHEN s.state = 2 THEN 1 ELSE 0 END) AS CRITICAL_TOTAL, SUM(CASE WHEN s.state = 2 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS CRITICAL_ACK_DT, SUM(CASE WHEN s.state = 3 THEN 1 ELSE 0 END) AS UNKNOWN_TOTAL, SUM(CASE WHEN s.state = 3 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS UNKNOWN_ACK_DT, SUM(CASE WHEN s.state = 4 THEN 1 ELSE 0 END) AS PENDING_TOTAL FROM hosts h, services s, instances i WHERE i.deleted = 0 AND h.enabled = 1 AND s.enabled = 1 AND i.instance_id = h.instance_id AND h.host_id = s.host_id AND (h.name NOT LIKE '_Module_%' OR h.name LIKE '_Module_Meta%') AND s.service_id IN ( SELECT DISTINCT service_id FROM centreon_acl WHERE centreon_acl.group_id IN ('3'));
+----------+---------------+----------------+----------------+-----------------+---------------+----------------+---------------+
| OK_TOTAL | WARNING_TOTAL | WARNING_ACK_DT | CRITICAL_TOTAL | CRITICAL_ACK_DT | UNKNOWN_TOTAL | UNKNOWN_ACK_DT | PENDING_TOTAL |
+----------+---------------+----------------+----------------+-----------------+---------------+----------------+---------------+
|    44692 |            90 |             27 |             45 |              16 |           498 |             20 |             0 |
+----------+---------------+----------------+----------------+-----------------+---------------+----------------+---------------+
1 row in set (0.16 sec)

@garnier-quentin
Copy link
Contributor Author

It's not large :)

@jben94
Copy link

jben94 commented Dec 29, 2017

Ok, but it should not be penalized for the smallest infrastructure.
To go from 0.01 second to 0.30 seconds, there is even a difference.

@garnier-quentin
Copy link
Contributor Author

When i look, you have: 0.21s and 0.16s.

@jben94
Copy link

jben94 commented Dec 29, 2017

Everything depends on the group in which you belong:

mysql> SELECT SUM(CASE WHEN s.state = 0 THEN 1 ELSE 0 END) AS OK_TOTAL, SUM(CASE WHEN s.state = 1 THEN 1 ELSE 0 END) AS WARNING_TOTAL, SUM(CASE WHEN s.state = 1 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS WARNING_ACK_DT, SUM(CASE WHEN s.state = 2 THEN 1 ELSE 0 END) AS CRITICAL_TOTAL, SUM(CASE WHEN s.state = 2 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS CRITICAL_ACK_DT, SUM(CASE WHEN s.state = 3 THEN 1 ELSE 0 END) AS UNKNOWN_TOTAL, SUM(CASE WHEN s.state = 3 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS UNKNOWN_ACK_DT, SUM(CASE WHEN s.state = 4 THEN 1 ELSE 0 END) AS PENDING_TOTAL FROM hosts h, services s, instances i WHERE i.deleted = 0 AND h.enabled = 1 AND s.enabled = 1 AND i.instance_id = h.instance_id AND h.host_id = s.host_id AND (h.name NOT LIKE '_Module_%' OR h.name LIKE '_Module_Meta%') AND s.service_id IN ( SELECT DISTINCT service_id FROM centreon_acl WHERE centreon_acl.group_id IN ('14','20'));
+----------+---------------+----------------+----------------+-----------------+---------------+----------------+---------------+
| OK_TOTAL | WARNING_TOTAL | WARNING_ACK_DT | CRITICAL_TOTAL | CRITICAL_ACK_DT | UNKNOWN_TOTAL | UNKNOWN_ACK_DT | PENDING_TOTAL |
+----------+---------------+----------------+----------------+-----------------+---------------+----------------+---------------+
|     5457 |            10 |              1 |              2 |               0 |            14 |              6 |             0 |
+----------+---------------+----------------+----------------+-----------------+---------------+----------------+---------------+
1 row in set (0.06 sec)

mysql> SELECT SUM(CASE WHEN s.state = 0 THEN 1 ELSE 0 END) AS OK_TOTAL, SUM(CASE WHEN s.state = 1 THEN 1 ELSE 0 END) AS WARNING_TOTAL, SUM(CASE WHEN s.state = 1 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS WARNING_ACK_DT, SUM(CASE WHEN s.state = 2 THEN 1 ELSE 0 END) AS CRITICAL_TOTAL, SUM(CASE WHEN s.state = 2 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS CRITICAL_ACK_DT, SUM(CASE WHEN s.state = 3 THEN 1 ELSE 0 END) AS UNKNOWN_TOTAL, SUM(CASE WHEN s.state = 3 AND (s.acknowledged = '1' OR s.scheduled_downtime_depth = '1') THEN 1 ELSE 0 END) AS UNKNOWN_ACK_DT, SUM(CASE WHEN s.state = 4 THEN 1 ELSE 0 END) AS PENDING_TOTAL FROM hosts h, services s, instances i WHERE i.deleted = 0 AND i.instance_id = h.instance_id AND h.enabled = 1 AND (h.name NOT LIKE '_Module_%' OR h.name LIKE '_Module_Meta%') AND h.host_id = s.host_id AND s.enabled = 1 AND EXISTS (SELECT service_id FROM centreon_acl WHERE h.host_id = centreon_acl.host_id AND s.service_id = centreon_acl.service_id AND centreon_acl.group_id IN ('14', '20'));
+----------+---------------+----------------+----------------+-----------------+---------------+----------------+---------------+
| OK_TOTAL | WARNING_TOTAL | WARNING_ACK_DT | CRITICAL_TOTAL | CRITICAL_ACK_DT | UNKNOWN_TOTAL | UNKNOWN_ACK_DT | PENDING_TOTAL |
+----------+---------------+----------------+----------------+-----------------+---------------+----------------+---------------+
|     5457 |            10 |              1 |              2 |               0 |            14 |              6 |             0 |
+----------+---------------+----------------+----------------+-----------------+---------------+----------------+---------------+
1 row in set (0.30 sec)

@garnier-quentin
Copy link
Contributor Author

I think it's acceptable. In some other case, i can be 25s more.

@jben94
Copy link

jben94 commented Dec 29, 2017

it's not huge as a difference but it all depends on how many people connect to it on the WEB interface.

The 25 seconds are not linked to the indexes?

@garnier-quentin
Copy link
Contributor Author

That's 25 seconds because of the IN.

@jben94
Copy link

jben94 commented Dec 29, 2017 •
edited
Loading

Ok, good end of the year ;)

kduret added a commit that referenced this issue Jan 4, 2018
@kduret
fix(sql): add index in centreon_acl for topcounter
d6067de 
Refs: #5974
@kduret kduret mentioned this issue Jan 4, 2018
Merged
kduret added a commit that referenced this issue Jan 8, 2018
@kduret
fix(sql): add index in centreon_acl for topcounter
a541ee5 
Refs: #5974
kduret added a commit that referenced this issue Jan 8, 2018
@kduret
fix(sql): add index in centreon_acl for topcounter
8f631df 
Refs: #5974
kduret added a commit that referenced this issue Jan 8, 2018
@kduret
fix(sql): add index in centreon_acl for topcounter
7a4decb 
Refs: #5974
kduret added a commit that referenced this issue Jan 8, 2018
@kduret
fix(sql): add index in centreon_acl for topcounter
62943a2 
Refs: #5974
kduret added a commit that referenced this issue Jan 16, 2018
@kduret
fix(sql): add index in centreon_acl for topcounter (#5992)
4592a80 
* fix(sql): add index in centreon_acl for topcounter

Refs: #5974

* enh(update): add acl index on updgrade
kduret added a commit that referenced this issue Jan 16, 2018
@kduret
fix(sql): add index in centreon_acl for topcounter (#5992)
116cc48 
* fix(sql): add index in centreon_acl for topcounter

Refs: #5974

* enh(update): add acl index on updgrade
@thiuyendang
Copy link
Contributor

The top counter values are correct since upgrade from 2.8.9 to 2.8.18 but cannot notice better response times for statuscounter queries.

kduret added a commit that referenced this issue Feb 27, 2018
@kduret
fix(perf): fix centreon_acl index creation on update
2e7b29b 
Refs: #6085 #5974
@kduret kduret mentioned this issue Feb 27, 2018
Merged
kduret added a commit that referenced this issue Feb 27, 2018
@kduret
fix(perf): fix centreon_acl index creation on update (#6093)
4338ffa 
Refs: #6085 #5974
kduret added a commit that referenced this issue Feb 27, 2018
@kduret
fix(perf): fix centreon_acl index creation on update (#6093)
2e61ff0 
Refs: #6085 #5974
Ridene pushed a commit that referenced this issue Mar 20, 2018
@kduret
fix(perf): fix centreon_acl index creation on update (#6093)
3e0baca 
Refs: #6085 #5974
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees
No one assigned
Labels
area/monitoring kind/bug priority/critical
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@lpinsivy @garnier-quentin @jben94 @thiuyendang