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(dot)friedrich(at)univie(dot)ac(dot)at]
Sent: Sunday, July 18, 2010 4:35 PM
To: Duncavage, Daniel P. (JSC-OD211)
Cc: pgsql-general(at)postgresql(dot)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 constraints is 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 adaption on 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 happen since 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 IDOUtils more 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 the on duplicate key insert or update from MySQL, but it's not really good causing two queries instead of one procedure in the 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 database setups).

If you are planning to use NDOUtils as basis for re-implementation for Postgresql, please be advised that the current 1.4b9 consists of some major bugs, next to mentioned performance issues with concurrent data inserts and housekeeping during startup 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 binded params (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 the current 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'd love to work together on a satisfying solution for you and the community :)

Kind regards,
Michael

(Icinga Core & IDOUtils Developer)

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

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