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

Michael Friedrich michael.friedrich at univie.ac.at
Tue May 19 15:16:01 CEST 2009


Sascha.Runschke at gfkl.com wrote the following on 19.05.2009 14:18:
> Hi Hendrik,
> I don't have any alive SQL queries for you at the moment, but one 
> prominent feature
> I want to see dead is the current implementation of the ido-db 
> maintainance.
I do not want it to die ... it has just to be reworked - and as always 
the databasa design to help the housekeeper. if reworking is still 
garbage and leads to nothing an external garbagekeeper could be possible.
> 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)

# Keep timed events for 24 hours

# Keep system commands for 1 week

# Keep service checks for 1 week

# Keep host checks for 1 week

# Keep event handlers for 31 days

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).
> This is not just against all well known practices, it also against common 
> sense.
> We should remove the trimming entirely from where it is right now and 
> implement
> it in another way.
> An application should not trim the database at will, it should do it when
> the DBA deems it to be an ok time if the DB get's kinda slow for a minute 
> or two.
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.
> Proposal:
> Put the DB trimming in an outer function and make it configurable via
> ido2db.cfg when to run the databasetrimming. Maybe make it something
> like crontab the time format and have it default to once per day at 
> midnight.
> That way you should be able to "borrow" the parsing code from crontab ;)
> e.g.:
> ido2db.cfg:
> # When to run DB maintainance to trim the tables and remove old data
> # Default: once per day at 23:59
> # see `man 5 crontab ` for details of the format
> #
> maintainance_time="59 23 * * *"
> That should remove a lot of load from the DB on large installations.
well using C you can just try to write via init_function to the crontab, 
or do you want to have an own watchdog daemon implemented which triggers 
the time and then runs the given housekeeper? Imho that's not a really 
good idea.

Based on the API there could be eventhandlings and calling the 
housekeepter from there by clicking and setting the config. I know some 
frameworks who are even better to handle that as a self designed watchdog.

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.

Of course right now the housekeeper brings the complete ido to fail and 
data gets lost, thats another point to get the view to.
> What do others think?
First - get the dbi working for more databases
Second - guidance to improve ido performance by tweaking
Third - Rewrite the housekeeper

Kind regards,
> 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
> ------------------------------------------------------------------------------
> Crystal Reports - New Free Runtime and 30 Day Trial
> Check out the new simplified licensing option that enables 
> unlimited royalty-free distribution of the report engine 
> for externally facing server and web deployment. 
> http://p.sf.net/sfu/businessobjects
> _______________________________________________
> icinga-devel mailing list
> icinga-devel at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/icinga-devel

DI (FH) Michael Friedrich
michael.friedrich at univie.ac.at
Tel: +43 1 4277 14359

Vienna University Computer Center
Universitaetsstrasse 7 
A-1010 Vienna, Austria  

More information about the icinga-devel mailing list