To illustrate my point, here's the effect of having
icinga_customvariablestatus updates for each host/service check.
In this case, I have 4 custom variables attached to the service, and there
are 4 separate updates to the customvariablestatus table for each executed
host/service check.
Add in a bunch more custom variables and host/service checks, and a
significant chunk of the queries hitting the DB are timestamp updates on a
table that isn't used.
Ideally, it would be great to tie the customvariablestatus table to a IDO
DB category config, where we could filter these out.
They are currently categorized under 'DbState', which lumps them in with a
bunch of other more useful things.
Otherwise, i'll just create a patch to comment out the status table update.
Thanks,
James
[2015-02-20 15:14:29 -0500] debug/IdoPgsqlConnection: Query: INSERT INTO
icinga_servicechecks (check_type, command_line, command_object_id,
current_check_attempt, end_time, end_time_usec, endpoint_object_id,
execution_time, instance_id, latency, long_output, max_check_attempts,
output, perfdata, return_code, service_object_id, start_time,
start_time_usec, state, state_type) VALUES (E'0', E'Object of type
''icinga::Array''', 102, E'1', TO_TIMESTAMP(1424463269), E'792261', 1,
E'0.4107210636138916', 1, E'0', E'', E'15', E'', E'', E'0', 107,
TO_TIMESTAMP(1424463269), E'381540', E'0', E'1')
[2015-02-20 15:14:29 -0500] debug/DbObject: Endpoint node: '
tor-monitor-dev1.colonynetworks.com' status update for
'Agilink_Test3!Agilink_State'
[2015-02-20 15:14:29 -0500] debug/IdoPgsqlConnection: Query: UPDATE
icinga_customvariablestatus SET has_been_modified = E'0', instance_id = 1,
is_json = E'0', object_id = 107, status_update_time =
TO_TIMESTAMP(1424463269), varname = E'check_type', varvalue = E'1' WHERE
object_id = 107 AND varname = E'check_type'
[2015-02-20 15:14:29 -0500] debug/IdoPgsqlConnection: Query: UPDATE
icinga_customvariablestatus SET has_been_modified = E'0', instance_id = 1,
is_json = E'0', object_id = 107, status_update_time =
TO_TIMESTAMP(1424463269), varname = E'http_port', varvalue = E'4431'
WHERE object_id = 107 AND varname = E'http_port'
[2015-02-20 15:14:29 -0500] debug/IdoPgsqlConnection: Query: UPDATE
icinga_customvariablestatus SET has_been_modified = E'0', instance_id = 1,
is_json = E'0', object_id = 107, status_update_time =
TO_TIMESTAMP(1424463269), varname = E'network_device_id', varvalue =
E'12347' WHERE object_id = 107 AND varname = E'network_device_id'
[2015-02-20 15:14:29 -0500] debug/IdoPgsqlConnection: Query: UPDATE
icinga_customvariablestatus SET has_been_modified = E'0', instance_id = 1,
is_json = E'0', object_id = 107, status_update_time =
TO_TIMESTAMP(1424463269), varname = E'network_interface_id', varvalue =
E'525' WHERE object_id = 107 AND varname = E'network_interface_id'
[2015-02-20 15:14:29 -0500] debug/IdoPgsqlConnection: Query: UPDATE
icinga_servicestatus SET acknowledgement_type = E'0',
active_checks_enabled = E'1', check_command = E'state_wan_https',
check_source = E'tor-monitor-dev1.colonynetworks.com', check_type = E'0',
current_check_attempt = E'1', current_notification_number = E'0',
current_state = E'0', endpoint_object_id = 1, event_handler = E'',
event_handler_enabled = E'1', execution_time = E'0.4107210636138916',
flap_detection_enabled = E'1', has_been_checked = E'1', instance_id = 1,
is_flapping = E'0', is_reachable = E'1', last_check =
TO_TIMESTAMP(1424463269), last_hard_state = E'0', last_hard_state_change
= TO_TIMESTAMP(1424462814), last_state_change = TO_TIMESTAMP(1424462814),
last_time_ok = TO_TIMESTAMP(1424463269), last_time_unknown =
TO_TIMESTAMP(1424462305), latency = E'0', long_output = E'',
max_check_attempts = E'15', modified_service_attributes = E'0',
next_check = TO_TIMESTAMP(1424463328), normal_check_interval = E'1',
notifications_enabled = E'1', output = E'', passive_checks_enabled =
E'1', percent_state_change = E'24', perfdata = E'',
problem_has_been_acknowledged = E'0', process_performance_data = E'1',
retry_check_interval = E'1', scheduled_downtime_depth = E'0',
service_object_id = 107, should_be_scheduled = E'1', state_type = E'1',
status_update_time = TO_TIMESTAMP(1424463269) WHERE service_object_id = 107
Index: icinga2-2.2.4~wheezy/lib/db_ido/dbobject.cpp
===================================================================
--- icinga2-2.2.4~wheezy.orig/lib/db_ido/dbobject.cpp 2015-02-05
05:15:24.000000000 -0500
+++ icinga2-2.2.4~wheezy/lib/db_ido/dbobject.cpp 2015-02-20
15:33:42.000000000 -0500
@@ -109,7 +109,7 @@
void DbObject::SendStatusUpdate(void)
{
/* update custom var status for all objects */
- SendVarsStatusUpdate();
+ /* SendVarsStatusUpdate(); */
/* status objects */
Dictionary::Ptr fields = GetStatusFields();
@@ -355,5 +355,5 @@
if (!dbobj)
return;
- dbobj->SendVarsStatusUpdate();
+ /* dbobj->SendVarsStatusUpdate(); */
}
On 16 February 2015 at 16:15, James Nedila <[email protected]> wrote:
> Resending as the mail server rejected this message.
> James
>
>
> On 16 February 2015 at 16:09, James Nedila <[email protected]> wrote:
>
>> Hi Michael,
>>
>>
>> We are now in the process of moving to Icinga2 for the many performance
>>>> improvements, and have noticed that this table is getting updated with
>>>> every hostcheck/servicecheck.
>>>>
>>>
>>> *check tables are merely useless, disable them afteral.
>>>
>>
>> We use these tables quite heavily, and it is not reasonable to just
>> disable them.
>> They hold a lot of the historical data, which is more valuable to us than
>> just current state data.
>>
>>
>>
>>>
>>>
>>>>
>>>> I don't see a way to turn this off in Icinga2.
>>>> Will this be added to Icinga2 anytime soon?
>>>>
>>>
>>> I don't see any valid readon to do so. Database operations have gotten
>>> even better considering i2.
>>>
>>>>
>>>> Or is there another way to achieve the same effect?
>>>>
>>>
>>>> I've tried revoking the update privilege on that table, but Icinga2
>>>> realizes there is a DB error, and it performs the whole DB reconnection
>>>> routine in response - which is not ideal.
>>>>
>>>
>>> What' s your point/issue?
>>>
>>
>>
>> My point is the DB is getting hit with with an extra customvariablestatus
>> update on every check result.
>> When we identify what the choke points are in the whole monitoring
>> system, it's always the DB when we scale up.
>>
>> Anything we can do to drop queries to the DB is a plus... especially when
>> we start loading up thousands of hosts and more service checks.
>>
>> We do not use customvariablestatus, as the custom variables are set at
>> server start time and we really don't care if they're updated after that.
>>
>> (A separate question I have is why Icinga is hitting the DB with a
>> BEGIN/COMMIT every second...)
>>
>> Thanks,
>> James
>>
>>
>
_______________________________________________
icinga-users mailing list
[email protected]
https://lists.icinga.org/mailman/listinfo/icinga-users
