Hi,
Recently the time to display the Timeline of a host/service is it very long (more than 30secondes).
Version Centreon : Centreon version 24.04.7
Version DB : mysql Ver 15.1 Distrib 10.11.8-MariaDB, for debian-linux-gnu (x86_64) using EditLine wrapper
Example :
Others tabs works well (Details/Graph/Notifications).
Any ideas ?
thx
Hi
Can you check in “Administration > Parameters > Options” menu the value for “Retention duration for logs” field?
How many entries do you have in db:
MariaDB scentreon_storage]> SELECT max(log_id) FROM log_archive_host;
+-------------+
| max(log_id) |
+-------------+
| 883 |
+-------------+
1 row in set (0.001 sec)
MariaDB bcentreon_storage]> SELECT max(log_id) FROM log_archive_service;
+-------------+
| max(log_id) |
+-------------+
| 9893 |
+-------------+
Thx
Retentions durations :
Entries :
Can you check slow queries on your database and get SQL request to analyze it and maybe add indexes (take care this will consume a lot of disk space).
I activated slow queries, logs :
MariaDB (none)]> EXPLAIN SELECT SQL_CALC_FOUND_ROWS
-> 1 AS REALTIME,
-> log.id,
-> log.type,
-> log.date,
-> log.start_date,
log.contact_id,
log.contact_name,
log.status_code,
log.statu -> log.end_date,
-> log.content,
-> log.contact_id,
-> log.contact_name,
-> log.status_code,
-> log.status_name,
-> log.status_severity_code,
-> log.tries
-> FROM (
-> SELECT
-> l.log_id AS `id`,
-> 'event' AS `type`,
-> l.ctime AS `date`,
-> NULL AS `start_date`,
-> NULL AS `end_date`,
-> l.output AS `content`,
-> NULL AS `contact_id`,
-> NULL AS `contact_name`,
-> l.status AS `status_code`,
-> CASE
-> WHEN l.status = 0 THEN 'OK'
-> WHEN l.status = 1 THEN 'WARNING'
-> WHEN l.status = 2 THEN 'CRITICAL'
-> WHEN l.status = 3 THEN 'UNKNOWN'
-> WHEN l.status = 4 THEN 'PENDING'
-> END AS `status_name`,
-> CASE
-> WHEN l.status = 0 THEN 5
-> WHEN l.status = 1 THEN 2
-> WHEN l.status = 2 THEN 1
-> WHEN l.status = 3 THEN 3
-> WHEN l.status = 4 THEN 4
-> END AS `status_severity_code`,
-> l.retry AS `tries`
-> FROM `centreon_storage`.`logs` l
-> WHERE l.host_id = 1002
-> AND (l.service_id = 6267)
-> AND l.msg_type IN (0,1,8,9)
-> AND l.output NOT LIKE 'INITIAL % STATE:%'
-> AND l.instance_name != ''
-> UNION ALL SELECT
-> l.log_id AS `id`,
ype`,
-> 'notification' AS `type`,
-> l.ctime AS `date`,
-> NULL AS `start_date`,
-> NULL AS `end_date`,
-> l.output AS `content`,
-> c.contact_id AS `contact_id`,
-> c.contact_alias AS `contact_name`,
-> l.status AS `status_code`,
THEN -> CASE
-> WHEN l.status = 0 THEN 'OK'
-> WHEN l.status = 1 THEN 'WARNING'
-> WHEN l.status = 2 THEN 'CRITICAL'
-> WHEN l.status = 3 THEN 'UNKNOWN'
'PEN -> WHEN l.status = 4 THEN 'PENDING'
-> END AS `status_name`,
-> CASE
-> WHEN l.status = 0 THEN 5
-> WHEN l.status = 1 THEN 2
-> WHEN l.status = 2 THEN 1
-> WHEN l.status = 3 THEN 3
-> WHEN l.status = 4 THEN 4
`
-> END AS `status_severity_code`,
-> NULL AS `tries`
-> FROM `centreon_storage`.`logs` l
-> LEFT JOIN `centreon`.contact AS `c` ON c.contact_name = l.notification_contact
-> WHERE l.host_id = 1002
-> AND (l.service_id = 6267)
-> AND l.msg_type IN (2,3)
-> UNION ALL SELECT
-> d.downtime_id AS `id`,
-> 'downtime' AS `type`,
-> d.actual_start_time AS `date`,
actual_e -> d.actual_start_time AS `start_date`,
-> d.actual_end_time AS `end_date`,
-> d.comment_data AS `content`,
-> d.a c.contact_id AS `contact_id`,
-> d.author AS `contact_name`,
-> NULL AS `status_code`,
-> NULL AS `status_name`,
-> NULL AS `status_severity_code`,
-> NULL AS `tries`
-> FROM `centreon_storage`.`downtimes` d
-> LEFT JOIN `centreon`.contact AS `c` ON c.contact_alias = d.author
-> WHERE d.host_id = 1002
-> AND (d.service_id = 6267)
-> AND d.actual_start_time < 1728563706
-> UNION ALL SELECT
-> a.acknowledgement_id AS `id`,
-> 'acknowledgement' AS `type`,
-> a.entry_time AS `date`,
-> NULL AS `start_date`,
-> NULL AS `end_date`,
-> a.comment_data AS `content`,
-> c.contact_id AS `contact_id`,
-> a.author AS `contact_name`,
-> NULL AS `status_code`,
-> NULL AS `status_name`,
AS `t -> NULL AS `status_severity_code`,
-> NULL AS `tries`
FRO -> FROM `centreon_storage`.`acknowledgements` a
-> LEFT JOIN `centreon`.contact AS `c` ON c.contact_alias = a.author
-> WHERE a.host_id = 1002
-> AND (a.service_id = 6267)
-> UNION ALL SELECT
-> c.comment_id AS `id`,
-> 'comment' AS `type`,
-> c.entry_time AS `date`,
-> NULL AS `start_date`,
-> NULL AS `end_date`,
-> c.data AS `content`,
-> ct.contact_id AS `contact_id`,
-> c.author AS `contact_name`,
-> NULL AS `status_code`,
-> NULL AS `status_name`,
-> NULL AS `status_severity_code`,
-> NULL AS `tries`
-> FROM `centreon_storage`.`comments` c
-> LEFT JOIN `centreon`.contact AS `ct` ON ct.contact_alias = c.author
-> WHERE c.host_id = 1002
-> AND (c.service_id = 6267)
-> AND source = 1 AND c.deletion_time IS NULL
-> ) AS `log` WHERE (log.type IN ('event','notification','comment','acknowledgement','downtime') AND (log.date > 1728477305 OR log.date < 1728563705)) AND EXISTS (SELECT host_id FROM `centreon_storage`.`centreon_acl` acl WHERE acl.host_id = 1002 AND acl.service_id = 6267 AND acl.group_id IN (2)) ORDER BY log.date DESC LIMIT 0, 30;
After used EXLAIN SQL command :
Can you help me with the indexing tasks?
Do you need more informations ?
Thx
When I choice “Last day” or “Last 7 days” or “Last 31 days” the all timeline is displaying (that’s why is so long?)
Requests don't seem to work properly but when I select Graph tab all buttons work.
I have same problem , I have updated to the latest release, but the problem persists
Version Centreon : 24.04.9 , Almalinux9
1 Central + 1 BBDD + 3 Poller
BBDD 10.11.10
Any solution?
My slow query log:
# Query 1: 0.06 QPS, 5.64x concurrency, ID 0x869C5B0A871389C4DF129A144770AB22 at byte 1487868
# This item is included in the report because it matches --limit.
# Scores: V/M = 92.00
# Time range: 2025-04-05 11:01:26 to 11:09:23
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 28
# Exec time 94 2690s 75ms 317s 96s 234s 94s 102s
# Lock time 0 506ms 698us 101ms 18ms 34ms 20ms 14ms
# Rows sent 1 840 30 30 30 30 0 30
# Rows examine 0 50.18k 234 2.15k 1.79k 2.06k 720.81 2.06k
# Rows affecte 0 0 0 0 0 0 0 0
# Bytes sent 4 234.74k 3.42k 9.52k 8.38k 9.33k 2.29k 9.33k
# Query size 10 139.75k 4.99k 4.99k 4.99k 4.99k 0 4.99k
# String:
# Databases centreon
# Hosts xx.xx.xx.xx
# Users xxxxxxxxxxx
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms ##############################
# 100ms ###########
# 1s
# 10s+ ################################################################
# Tables
# SHOW TABLE STATUS FROM `centreon_storage` LIKE 'logs'\G
# SHOW CREATE TABLE `centreon_storage`.`logs`\G
# SHOW TABLE STATUS FROM `centreon` LIKE 'contact'\G
# SHOW CREATE TABLE `centreon`.`contact`\G
# SHOW TABLE STATUS FROM `centreon_storage` LIKE 'downtimes'\G
# SHOW CREATE TABLE `centreon_storage`.`downtimes`\G
# SHOW TABLE STATUS FROM `centreon_storage` LIKE 'acknowledgements'\G
# SHOW CREATE TABLE `centreon_storage`.`acknowledgements`\G
# SHOW TABLE STATUS FROM `centreon_storage` LIKE 'comments'\G
# SHOW CREATE TABLE `centreon_storage`.`comments`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT SQL_CALC_FOUND_ROWS
1 AS REALTIME,
log.id,
log.type,
log.date,
log.start_date,
log.end_date,
log.content,
log.contact_id,
log.contact_name,
log.status_code,
log.status_name,
log.status_severity_code,
log.tries
FROM (
SELECT
l.log_id AS `id`,
'event' AS `type`,
l.ctime AS `date`,
NULL AS `start_date`,
NULL AS `end_date`,
l.output AS `content`,
NULL AS `contact_id`,
NULL AS `contact_name`,
l.status AS `status_code`,
CASE
WHEN l.status = 0 THEN 'OK'
WHEN l.status = 1 THEN 'WARNING'
WHEN l.status = 2 THEN 'CRITICAL'
WHEN l.status = 3 THEN 'UNKNOWN'
WHEN l.status = 4 THEN 'PENDING'
END AS `status_name`,
CASE
WHEN l.status = 0 THEN 5
WHEN l.status = 1 THEN 2
WHEN l.status = 2 THEN 1
WHEN l.status = 3 THEN 3
WHEN l.status = 4 THEN 4
END AS `status_severity_code`,
l.retry AS `tries`
FROM `centreon_storage`.`logs` l
WHERE l.host_id = 1438
AND (l.service_id = 8388)
AND l.msg_type IN (0,1,8,9)
AND l.output NOT LIKE 'INITIAL % STATE:%'
AND l.instance_name != ''
UNION ALL SELECT
l.log_id AS `id`,
'notification' AS `type`,
l.ctime AS `date`,
NULL AS `start_date`,
NULL AS `end_date`,
l.output AS `content`,
c.contact_id AS `contact_id`,
c.contact_alias AS `contact_name`,
l.status AS `status_code`,
CASE
WHEN l.status = 0 THEN 'OK'
WHEN l.status = 1 THEN 'WARNING'
WHEN l.status = 2 THEN 'CRITICAL'
WHEN l.status = 3 THEN 'UNKNOWN'
WHEN l.status = 4 THEN 'PENDING'
END AS `status_name`,
CASE
WHEN l.status = 0 THEN 5
WHEN l.status = 1 THEN 2
WHEN l.status = 2 THEN 1
WHEN l.status = 3 THEN 3
WHEN l.status = 4 THEN 4
END AS `status_severity_code`,
NULL AS `tries`
FROM `centreon_storage`.`logs` l
LEFT JOIN `centreon`.contact AS `c` ON c.contact_name = l.notification_contact
WHERE l.host_id = 1438
AND (l.service_id = 8388)
AND l.msg_type IN (2,3)
UNION ALL SELECT
d.downtime_id AS `id`,
'downtime' AS `type`,
d.actual_start_time AS `date`,
d.actual_start_time AS `start_date`,
d.actual_end_time AS `end_date`,
d.comment_data AS `content`,
c.contact_id AS `contact_id`,
d.author AS `contact_name`,
NULL AS `status_code`,
NULL AS `status_name`,
NULL AS `status_severity_code`,
NULL AS `tries`
FROM `centreon_storage`.`downtimes` d
LEFT JOIN `centreon`.contact AS `c` ON c.contact_alias = d.author
WHERE d.host_id = 1438
AND (d.service_id = 8388)
AND d.actual_start_time < 1743843686
UNION ALL SELECT
a.acknowledgement_id AS `id`,
'acknowledgement' AS `type`,
a.entry_time AS `date`,
NULL AS `start_date`,
NULL AS `end_date`,
a.comment_data AS `content`,
c.contact_id AS `contact_id`,
a.author AS `contact_name`,
NULL AS `status_code`,
NULL AS `status_name`,
NULL AS `status_severity_code`,
NULL AS `tries`
FROM `centreon_storage`.`acknowledgements` a
LEFT JOIN `centreon`.contact AS `c` ON c.contact_alias = a.author
WHERE a.host_id = 1438
AND (a.service_id = 8388)
UNION ALL SELECT
c.comment_id AS `id`,
'comment' AS `type`,
c.entry_time AS `date`,
NULL AS `start_date`,
NULL AS `end_date`,
c.data AS `content`,
ct.contact_id AS `contact_id`,
c.author AS `contact_name`,
NULL AS `status_code`,
NULL AS `status_name`,
NULL AS `status_severity_code`,
NULL AS `tries`
FROM `centreon_storage`.`comments` c
LEFT JOIN `centreon`.contact AS `ct` ON ct.contact_alias = c.author
WHERE c.host_id = 1438
AND (c.service_id = 8388)
AND source = 1 AND c.deletion_time IS NULL
) AS `log` WHERE (log.type IN ('event','notification','comment','acknowledgement','downtime') AND (log.date > 1743757285 OR log.date < 1743843685)) ORDER BY log.date DESC LIMIT 0, 30\G
No account yet? Create an account
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.