Skip to main content

Hello,

Since upgrading to Centreon 23.10 (since 21.10), I've been experiencing frequent slowdowns in the Web UI.

From time to time, I have to restart the Centreon server to restore service.

Analysis has shown that a very specific request is the cause. This query crushes the performance of the MySQL server. It seems that the indexes are not being used (FULL SCAN).

The query is attached.

Can anyone help me identify the cause, usage or process that triggers this query?


Here is the SQL request 

SELECT SQL_CALC_FOUND_ROWS DISTINCT
            1 AS REALTIME,
            resources.resource_id,
            resources.name,
            resources.alias,
            resources.address,
            resources.id,
            resources.internal_id,
            resources.parent_id,
            resources.parent_name,
            parent_resource.status AS `parent_status`,
            parent_resource.alias AS `parent_alias`,
            parent_resource.status_ordered AS `parent_status_ordered`,
            parent_resource.address AS `parent_fqdn`,
            severities.id AS `severity_id`,
            severities.level AS `severity_level`,
            severities.name AS `severity_name`,
            severities.type AS `severity_type`,
            severities.icon_id AS `severity_icon_id`,
            resources.type,
            resources.status,
            resources.status_ordered,
            resources.status_confirmed,
            resources.in_downtime,
            resources.acknowledged,
            resources.passive_checks_enabled,
            resources.active_checks_enabled,
            resources.notifications_enabled,
            resources.last_check,
            resources.last_status_change,
            resources.check_attempts,
            resources.max_check_attempts,
            resources.notes,
            resources.notes_url,
            resources.action_url,
            resources.output,
            resources.poller_id,
            resources.has_graph,
            instances.name AS `monitoring_server_name`,
            resources.enabled,
            resources.icon_id,
            resources.severity_id
        FROM `centreon_storage`.`resources`
        LEFT JOIN `centreon_storage`.`resources` parent_resource
            ON parent_resource.id = resources.parent_id
            AND parent_resource.type = 1 LEFT JOIN `centreon_storage`.`severities`
            ON `severities`.severity_id = `resources`.severity_id
        LEFT JOIN `centreon_storage`.`resources_tags` AS rtags
            ON `rtags`.resource_id = `resources`.resource_id
        INNER JOIN `centreon_storage`.`instances`
            ON `instances`.instance_id = `resources`.poller_id WHERE ((CASE WHEN resources.type = 1 THEN resources.name ELSE resources.parent_name END REGEXP 'authorwin3-a' OR CASE WHEN resources.type = 1 THEN resources.alias ELSE parent_resource.alias END REGEXP 'authorwin3-a' OR parent_resource.address REGEXP 'authorwin3-a' OR resources.type IN (0,2) AND resources.name REGEXP 'authorwin3-a' OR resources.alias REGEXP 'authorwin3-a' OR parent_resource.alias REGEXP 'authorwin3-a' OR resources.address REGEXP 'authorwin3-a' OR resources.output REGEXP 'authorwin3-a')) AND  resources.name NOT LIKE '\_Module\_%'
            AND resources.parent_name NOT LIKE '\_Module\_BAM%'
            AND resources.enabled = 1 AND resources.type != 3 AND (EXISTS (
            SELECT 1
            FROM `centreon_storage`.centreon_acl acl
            WHERE
                resources.type = 1
                AND resources.id = acl.host_id
                AND acl.group_id IN (1)
        ) OR EXISTS (
            SELECT 1
            FROM `centreon_storage`.centreon_acl acl
            WHERE
                resources.type = 2
                AND resources.parent_id = acl.host_id
                AND resources.id = acl.service_id
                AND acl.group_id IN (1)
        ) OR EXISTS (
            SELECT 1
            FROM `centreon_storage`.centreon_acl acl
            WHERE
                resources.type = 0
                AND resources.parent_id = acl.host_id
                AND resources.id = acl.service_id
                AND acl.group_id IN (1)
        )) ORDER BY resources.last_status_change IS NULL, resources.last_status_change DESC LIMIT 0, 30

 

Here's a snapshot of the analysis performed on one of these SQL queries.

 

Query analysis

 

Hello,

I think this kind of queries comes from the “Monitoring Resources > Status” view.

It look likes a request to get the ressources infos.

Hello everyone,

This type of SQL query is generated from time to time. They accumulate until the Centreon WebUI freezes because the MySQL database is overloaded.

Does anyone have any idea why FULL_SCAN SQL queries are generated? 

Where can I find the code that generates these queries?

Hi Stéphane,
As indicated by @benoitp, this SQL query comes from the “Resource status” page.
Is it possible to capture the API query to check the search filters used?
The API url should start with “/monitoring/resources”.

How many hosts and services do you have?

Hello,
Sorry for this late reply but I do not receive any notifications.

We have 372000 services and 21000 hosts.

 

It is precisely the filtering that poses a problem. For a few months I have been regularly collapsing the database and freezing the WebUI. Forcing me to kill user sessions and SQL (+restart php-fpm).

Following multiple communications that asked to use the search criteria as much as possible (h.mame, s.description...), this seems to be improving (I am touching monkey skin).

Hi Stéphane,

I'm glad to hear that you've found a solution.

The use of search criteria improves the speed of execution of database queries. Without it, we'd have to search every possible field, which would considerably increase search times.

We're doing our best to improve the user experience, and we're working every day to improve it, particularly in terms of response times with the resource status page.

Reply


Terms & ConditionsCookie settings