Re: NASA needs Postgres - Nagios help

Thank you for the time and thought.

I've added Brian Martin, who is my project lead for this effort.  He's a better person to converse with than I am.

-----Original Message-----
From: Michael Friedrich [mailto:michael.friedrich@univie.ac.at]
Sent: Sunday, July 18, 2010 4:35 PM
To: Duncavage, Daniel P. (JSC-OD211)
Cc: pgsql-general@postgresql.org; Stefan Kaltenbrunner
Subject: Re: [GENERAL] NASA needs Postgres - Nagios help

Hi there,

On 2010-07-15 07:06, Stefan Kaltenbrunner wrote:
> well - there was direct database support in nagios ages ago(nagios 1.x
> is ancient) and replaced with a plugin based approach based on their
> eventbroker architecture called NDOutils. Based on tracking internal
> state it can be used to export current and historical monitoring data
> from nagios for later postprocessing (or for usin a GUI or whatever).
> NODutils however has no real working support for PostgreSQL, IDOutils
> (which I mentioned elsewhere in the thread) from the icinga fork does
> have basic support.

The SQL queries used in NDOUtils are highly MySQL specific, mostly the ON DUPLICATE KEY functionality based on unique
constraintsis a bunch of work to be resolved. Next to that, the "normal" insert statements are not normalized (insert
into... set foo=bar instead of insert into ...  
() values ()), some missing time conversion procedures and naturally the last insert id on MySQL, which needs an
adaptionon sequences in Postgresql and Oracle. 

Which means, just by changing the .sql files and the column attributes, this won't work. Not even the connection will
happensince there is no C source code for that available via #ifdef. 


Some of those mentioned things have been resolved in Icinga IDOUtils, but not all since I had to focus on 1/ make
IDOUtilsmore stable, less blocking and 2/ provide initial improved Oracle support. THe Postgresql support is quite
basic,but based on libdbi it still works. In regard of bigger monitoring environments it will lack of performance for
sure.

Main reason is that the current query implementation first tries and update, and then inserts - which basically forms
theon duplicate key insert or update from MySQL, but it's not really good causing two queries instead of one procedure
inthe worst situation. An UPSERT or MERGE procedure should replace that - sth like this:  
http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql
(a far more better approach would be a common rewrite with a better db schema but that's future sound for existing
databasesetups). 


If you are planning to use NDOUtils as basis for re-implementation for Postgresql, please be advised that the current
1.4b9consists of some major bugs, next to mentioned performance issues with concurrent data inserts and housekeeping
duringstartup and running. IDOUtils provides an extended housekeeping thread not to interfere with the insertions. 


Some blogposts on Icinga's improvements, especially on IDOUtils:

http://www.icinga.org/2009/09/01/playing-with-idoutils-and-postgresql/
http://www.icinga.org/2009/10/20/icinga-idoutils-will-support-oracle-rdbm-in-1-0-rc/
http://www.icinga.org/2010/02/17/icinga-idoutils-more-improvements-part-ii/
http://www.icinga.org/2010/06/16/news-from-core-cgis-idoutils-part-i/


Our plans are to improve Postgresql support of Icinga IDOUtils within the next months mainly regarding the upsert
procedure,but also by dropping the current db abstraction layer (libdbi) in order to use direct prepared statements and
bindedparams (which is not possible with libdbi). 
This will be done right after some bigger core changes are finished, imho not in 1.0.3 but 1.0.4 in October would be
possible.

Postgresql is next to MySQL and Oracle part of RDBMS section of the unified Icinga API (written in PHP), and provides
thecurrent Icinga Core data source for the newly developed Icinga Web. 

For more information:
http://www.icinga.org/architecture/
http://www.icinga.org/faq/icinga-vs-nagios-whats-the-difference/


That's the thing in Icinga's perspective - it's still a fork of Nagios, but as you can see a lot of things happened
lately.If Icinga can be of help for you getting better Postgresql support with Icinga IDOUtils, please get in touch.
We'dlove to work together on a satisfying solution for you and the community :) 

Kind regards,
Michael

(Icinga Core & IDOUtils Developer)

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

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