Nagios and SQL -- bad combination?

[Dhawal Doshy]

Youngblood, Gregory (SAIC) wrote:

>Can someone clarify how Nagios uses the SQL database? Unless I'm missing
>something, it appears data is updated in the database by deleting all rows
>and inserting them again, for each test. That can't be right, can it? 
>  
>
That is actually right.. nagios stores only the last service / host 
check results in the database.

>Either something is wrong with my config (if so, I'd welcome any
>corrections), or Nagios and SQL (Postgres) has a few issues.
>  
>
Marc Powell had earlier suggested using pgsql triggers that'll copy the 
data to a history table on every insert / update. I could share the 
trigger with you with marc's permission (or marc could do it himself).

>Nagios appears to run fine for a while, and then bogs down. Looking at the
>log files, it appears that nagios is trying to delete all the records in
>hoststatus and servicestatus over and over and over again. Since I'm only
>logging transactions that take longer than about 1 second, I don't know if
>it is deleting all the records and immediately reinserting them, or if it is
>deleting the records without sending a commit. Either way, the data is still
>in the tables when I query it via psql.
>
>Here's an example from the logs:
>2004-08-13 10:49:13 [1277] LOG:  duration: 7987.568 ms  statement: DELETE
>FROM servicestatus;
>2004-08-13 10:49:20 [1277] LOG:  duration: 3184.407 ms  statement: DELETE
>FROM hoststatus;
>2004-08-13 10:49:27 [1277] LOG:  duration: 6341.825 ms  statement: DELETE
>FROM servicestatus;
>2004-08-13 10:49:35 [1277] LOG:  duration: 3052.289 ms  statement: DELETE
>FROM hoststatus;
>2004-08-13 10:49:41 [1277] LOG:  duration: 6235.310 ms  statement: DELETE
>FROM servicestatus;
>2004-08-13 10:49:50 [1277] LOG:  duration: 3060.550 ms  statement: DELETE
>FROM hoststatus;
>
>It just repeats this, over and over and over, occasionally with a long
>select statement as well. Given the short delay between statements, and the
>length of each transaction, there is enough time for inserts to repopulate
>the tables. There are only 26 hosts monitored and 158 services.
>
>In case these problems are of my own creation, here is my resources.cfg
>file, if anyone has some suggestions:
>
>$USER1$=/opt/nagios/libexec
>
>xsddb_host=127.0.0.1
>xsddb_port=5432
>xsddb_database=nagios
>xsddb_username=xxxxxxxx
>xsddb_password=xxxxxxxxxxx
>xsddb_optimize_data=1
>xsddb_optimize_interval=3600
>
>xcddb_host=127.0.0.1
>xcddb_port=5432
>xcddb_database=nagios
>xcddb_username=xxxxxxxx
>xcddb_password=xxxxxxxxxxx
>xcddb_optimize_data=1
>
>xdddb_host=127.0.0.1
>xdddb_port=5432
>xdddb_database=nagios
>xdddb_username=xxxxxxxx
>xdddb_password=xxxxxxxxxxx
>xdddb_optimize_data=1
>
>xrddb_host=127.0.0.1
>xrddb_port=5432
>xrddb_database=nagios
>xrddb_username=xxxxxxxx
>xrddb_password=xxxxxxxxxxx
>xrddb_optimize_data=1
>
>Any thoughts or suggestions?
>
>  
>
no idea here :-(

>Thanks,
>Greg
>  
>
regards,
- dhawal

>
>-------------------------------------------------------
>SF.Net email is sponsored by Shop4tech.com-Lowest price on Blank Media
>100pk Sonic DVD-R 4x for only $29 -100pk Sonic DVD+R for only $33
>Save 50% off Retail on Ink & Toner - Free Shipping and Free Gift.
>http://www.shop4tech.com/z/Inkjet_Cartridges/9_108_r285
>_______________________________________________
>Nagios-users mailing list
>Nagios-users at lists.sourceforge.net
>https://lists.sourceforge.net/lists/listinfo/nagios-users
>::: Please include Nagios version, plugin version (-v) and OS when reporting any issue. 
>::: Messages without supporting info will risk being sent to /dev/null
>  
>


-------------------------------------------------------
SF.Net email is sponsored by Shop4tech.com-Lowest price on Blank Media
100pk Sonic DVD-R 4x for only $29 -100pk Sonic DVD+R for only $33
Save 50% off Retail on Ink & Toner - Free Shipping and Free Gift.
http://www.shop4tech.com/z/Inkjet_Cartridges/9_108_r285
_______________________________________________
Nagios-users mailing list
Nagios-users at lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/nagios-users
::: Please include Nagios version, plugin version (-v) and OS when reporting any issue. 
::: Messages without supporting info will risk being sent to /dev/null