[icinga-devel] Antwort: Re: Antwort: [RFC] SQL Queries

Sascha.Runschke at gfkl.com Sascha.Runschke at gfkl.com
Tue May 19 17:42:11 CEST 2009


Greetings,

you might check on the source again, I think you are mistaken on some
of your assumptions. I'm currently in a hurry and can't fetch the
the code to post it, sorry.

> > It is just _totally insane_ to run database trimming every 60 seconds 
in 
> > ndo.
> > 
> who does that? you may set it to that value that is true, but that would 

> be small hack to override that to default 50 minutes (which has been a 
> good value in bigger stages over here)

IDO2DB does that. It issues the following statement for tables containing
historical data _every_60_seconds_:

"DELETE * from put_favourite_historical_table_here where startdate < 
[now()-max_timedevents_age]"

Sidenote: the above statement is simplified and abstracted and of
course looks not valid in this form and I just used it to describe what
happens.

> # Keep timed events for 24 hours
> max_timedevents_age=1440
> 
> # Keep system commands for 1 week
> max_systemcommands_age=10080
> 
> # Keep service checks for 1 week
> max_servicechecks_age=10080
> 
> # Keep host checks for 1 week
> max_hostchecks_age=10080
> 
> # Keep event handlers for 31 days
> max_eventhandlers_age=44640

Those ages are just meant for the DB housekeeper to know when the
data is old enough to get dropped - for calculation in the above
mentioned query. It has absolutely nothing to do with the time
the housekeeper starts running.

> Keeping historical data for nothing and then breaking the housekeeper 
> with dropping e.g. 3,7mio rows - and using oracle - that's just more 
insane.
> (even with modified broker options).

You wouldn't think so, if you'd know that currently IDO2DB already
does that every minute ;)

> trimming may block the ido from writing and could lead to a deadlock 
> concerning an external program / script. Even though if applications use 

> the database they may be locked out during the cleaning. Right now it is 

> a process done while inserting and updating - for sure a perfomance 
> killer, but not that locking as it could appear when removing the 
> housekeeper from core.

No it doesn't.
IDO2DB is implemented in the module which actually feeds the DB.
If the housekeeper is running, then IDO2DB will not push a single
query into the DB, because the housekeeper is not threaded, but
blocking. In fact idomod hands the data to ido2db, which then
in turn buffers the query. ido2db has a default buffer of 5000
queries I think, after that it starts dropping the data.

> The next problem I see - what if the external housekeeper is dying, 
> database is filling up - you will need another watchdog/cronjob to 
> re-activate it and check if the last run has been successfully finished. 

> if not, in a smaller environment thats not that bad but larger companies 

> and networks may suffer from that.

I've never talked about an external housekeeper. Let IDO2DB do
the housekeeping - but please not every minute, but instead at
a configurable exact time. You might want to reread what I wrote,
or maybe I just wrote it in a confuse manner ;) Please have a look
at the code and you'll see what I mean.

Regards
        Sascha



GFKL Financial Services AG
Vorstand: Dr. Peter Jänsch (Vors.), Jürgen Baltes, Dr. Tom Haverkamp
Vorsitzender des Aufsichtsrats: Dr. Georg F. Thoma
Sitz: Limbecker Platz 1, 45127 Essen, Amtsgericht Essen, HRB 13522




More information about the icinga-devel mailing list