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. [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
