Am 25.08.2015 um 23:49 schrieb Romaneev Vasily:
I have huge table icinga_downtimehistory. About 100k records.
And sometime mysql locks up to 15s on update the table.
I've found, that there's missing index, so please add this to a next release of
icinga2, thank you!
P.S> Will be appreciated if anyone sends me a link to create a bug in a
development tracker.
Fairly easy to reach from the first docs chapter ;)
http://docs.icinga.org/icinga2/latest/doc/module/icinga2/chapter/about-icinga2#development
Kind regards,
Michael
[root@monitor x-cart.hosting]# icinga2 -V
icinga2 - The Icinga 2 network monitoring daemon (version: v2.3.8)
Copyright (c) 2012-2015 Icinga Development Team (https://www.icinga.org)
License GPLv2+: GNU GPL version 2 or later <http://gnu.org/licenses/gpl2.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.
Application information:
Installation root: /usr
Sysconf directory: /etc
Run directory: /var/run
Local state directory: /var
Package data directory: /usr/share/icinga2
State path: /var/lib/icinga2/icinga2.state
Objects path: /var/cache/icinga2/icinga2.debug
Vars path: /var/cache/icinga2/icinga2.vars
PID path: /var/run/icinga2/icinga2.pid
Application type: icinga/IcingaApplication
System information:
Operating system: Linux
Operating system version: 2.6.32-504.12.2.el6.x86_64
Architecture: x86_64
Distribution: CentOS release 6.6 (Final)
mysql> show full processlist;
+-------+--------+-----------+--------+---------+------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info
|
+-------+--------+-----------+--------+---------+------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 30610 | icinga | localhost | icinga | Query | 0 | Updating | UPDATE
icinga_downtimehistory SET actual_end_time = FROM_UNIXTIME(1440538613),
actual_end_time_usec = '650816', was_cancelled = '0' WHERE entry_time =
FROM_UNIXTIME(1440538613) AND instance_id = 1 AND internal_downtime_id =
'425267' AND scheduled_end_time = FROM_UNIXTIME(1440566400) AND
scheduled_start_time = FROM_UNIXTIME(1440564900) |
| 30618 | root | localhost | NULL | Query | 0 | NULL | show full
processlist
|
+-------+--------+-----------+--------+---------+------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> select actual_end_time,actual_end_time_usec,was_cancelled from
icinga_downtimehistory WHERE entry_time = FROM_UNIXTIME(1440538613) AND
instance_id = 1 AND internal_downtime_id = '425267' AND scheduled_end_time =
FROM_UNIXTIME(1440566400) AND scheduled_start_time = FROM_UNIXTIME(1440564900);
+---------------------+----------------------+---------------+
| actual_end_time | actual_end_time_usec | was_cancelled |
+---------------------+----------------------+---------------+
| 0000-00-00 00:00:00 | 0 | 0 |
+---------------------+----------------------+---------------+
1 row in set (1.91 sec)
mysql> explain select actual_end_time,actual_end_time_usec,was_cancelled from
icinga_downtimehistory WHERE entry_time = FROM_UNIXTIME(1440538613) AND
instance_id = 1 AND internal_downtime_id = '425267' AND scheduled_end_time =
FROM_UNIXTIME(1440566400) AND scheduled_start_time = FROM_UNIXTIME(1440564900);
+----+-------------+------------------------+------+---------------+-------------+---------+-------+-------+-------------+
| id | select_type | table | type | possible_keys | key
| key_len | ref | rows | Extra |
+----+-------------+------------------------+------+---------------+-------------+---------+-------+-------+-------------+
| 1 | SIMPLE | icinga_downtimehistory | ref | instance_id |
instance_id | 9 | const | 42165 | Using where |
+----+-------------+------------------------+------+---------------+-------------+---------+-------+-------+-------------+
1 row in set (0.00 sec)
mysql> create index update_downtime USING BTREE ON icinga_downtimehistory (
entry_time, instance_id, internal_downtime_id, scheduled_end_time,
scheduled_start_time );
Query OK, 85260 rows affected (6.98 sec)
Records: 85260 Duplicates: 0 Warnings: 0
mysql> explain select actual_end_time,actual_end_time_usec,was_cancelled from
icinga_downtimehistory WHERE entry_time = FROM_UNIXTIME(1440538613) AND
instance_id = 1 AND internal_downtime_id = '425267' AND scheduled_end_time =
FROM_UNIXTIME(1440566400) AND scheduled_start_time = FROM_UNIXTIME(1440564900);
+----+-------------+------------------------+------+-----------------------------+-----------------+---------+-------------------------------+------+-------------+
| id | select_type | table | type | possible_keys
| key | key_len | ref | rows | Extra
|
+----+-------------+------------------------+------+-----------------------------+-----------------+---------+-------------------------------+------+-------------+
| 1 | SIMPLE | icinga_downtimehistory | ref |
instance_id,update_downtime | update_downtime | 30 |
const,const,const,const,const | 1 | Using where |
+----+-------------+------------------------+------+-----------------------------+-----------------+---------+-------------------------------+------+-------------+
1 row in set (0.00 sec)
------
Романеев Василий
Мобильный: +7-987-636-62-67
skype romaneev
_______________________________________________
icinga-users mailing list
icinga-users@lists.icinga.org
https://lists.icinga.org/mailman/listinfo/icinga-users
--
Michael Friedrich, DI (FH)
Senior Developer
NETWAYS GmbH | Deutschherrnstr. 15-19 | D-90429 Nuernberg
Tel: +49 911 92885-0 | Fax: +49 911 92885-77
GF: Julian Hein, Bernd Erk | AG Nuernberg HRB18461
http://www.netways.de | michael.friedr...@netways.de
** OSBConf 2015 - September - osbconf.org **
** OSMC 2015 - November - netways.de/osmc **
_______________________________________________
icinga-users mailing list
icinga-users@lists.icinga.org
https://lists.icinga.org/mailman/listinfo/icinga-users
