Hi Romaneev,
it would be great if you could report this on dev.icinga.org, we are
currently trying to improve IDO indices. I was missing one detail in
your mail, how long did the query take with the new index? Could you
show a processlist output where such a query is hanging around? Is it
waiting "alone" or waiting for other queries to complete?
While your index seems to perfectly fit this query I'd first prefer to
find out if there is a chance to tweak the query itself.
@dnsmichi: any reason we are not using object_id in this query? And
what's the reason for filtering also by scheduled_start/end_time? Here
is what the query should look like to fit current indices:
mysql> EXPLAIM SELECT actual_end_time, actual_end_time_usec,
was_cancelled FROM icinga_downtimehistory WHERE instance_id = 1 AND
entry_time = FROM_UNIXTIME(1360657630) AND internal_downtime_id =
'77733' and object_id = 12107\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: icinga_downtimehistory
type: const
possible_keys: instance_id,tom_index
key: instance_id
key_len: 31
ref: const,const,const,const
rows: 1
Extra:
1 row in set (0.00 sec)
Query execution time is also 0.00 sec, in a live environment with real
data and slightly more than 100k records.
Cheers,
Thomas
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!
--
Thomas Gelf
Principal Consultant
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 | thomas.g...@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
