Nagios and SQL -- bad combination?

[Youngblood, Gregory (SAIC)]

Thanks for the quick reply. Just for clarification, Nagios does work by
deleting and inserting, instead of doing updates? If so, that confirms what
I was suspecting.

I'm using Pg 7.4.3, but I believe the behavior is the same as you describe.
It is normally not an issue, since normal use of a database would be to
update existing records instead of deleting them and inserting them again.
Sounds like poor design at the database layer. Or, is this just the way
Nagios works for text files too, so it is just moving the same operations to
the database.

MySQL should handle millions of rows fairly well, assuming the tables are
indexed sanely and that your not using more than one process that touches
the tables. The only serious problems I had with MySQL and lots of data were
when more than one process or user needed to update/select/insert/delete
data from the same table (this includes using the table in a join). If your
not using the ISAM table type, then that should be even less of an issue
now. When I was doing it, ISAM was the only thing I'd trust for production,
Inno was still too new. For me anyway -- sometimes I don't like living on
the bleeding edge. :)

I think I'm going to go to text files and take it out of the database all
together. At least for now. MySQL is not an option for various reasons, and
I do not wish to have two databases running.

I'm kind of disappointed by this though. It's not the first time I've seen
something similar when an application is extended to use a database as an
alternative to text or DBM files.

Thanks again for the quick response. I probably won't waste any more time
trying to get this to work cleanly with pgsql.
 
Greg

> -----Original Message-----
> From: Dan Hopkins [mailto:dan.hopkins at uk.easynet.net]
> Sent: Friday, August 13, 2004 11:15 AM
> To: Youngblood, Gregory (SAIC); nagios-users at lists.sourceforge.net
> Subject: RE: [Nagios-users] Nagios and SQL -- bad combination?
> 
> 
> > -----Original Message-----
> > From: nagios-users-admin at lists.sourceforge.net
> > [mailto:nagios-users-admin at lists.sourceforge.net]On Behalf Of
> > Youngblood, Gregory (SAIC)
> > Sent: 13 August 2004 16:50
> > To: nagios-users at lists.sourceforge.net
> > Subject: [Nagios-users] Nagios and SQL -- bad combination?
> > 
> > 
> > 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? 
> > 
> > Either something is wrong with my config (if so, I'd welcome any
> > corrections), or Nagios and SQL (Postgres) has a few issues.
> > 
> > Nagios appears to run fine for a while, and then bogs down. 
> 
> <snip>
> 
> We had the same problem with PgSQL. I believe it's more to do 
> with the way postgres handles updates/deletes than with 
> nagios. Deletions aren't removed from tables until you 
> perform a full vacuum, so until then the table will continue 
> to grow each time a change is made, eventually leaving you 
> with massive chains in the table structure. Since a full 
> vacuum needs an exclusive lock, this means nagios downtime - 
> which isn't particularily useful for a monitoring system...
> 
> We spent a few days wrestling with this (even vacuums every 
> minute or two weren't enough to solve the problem) and after 
> 24 hours or so, everything would grind to a halt. So 
> eventually we switched to MySQL which runs perfectly (mind 
> you, we are logging ALL service alerts to db, so it remains 
> to be seen how well MySQL will cope with millions of rows ... 
> not as well as Pg I assume)
> 
> FYI our experience was based on Pg 7.3 (things may have 
> changed with recent releases?) We were running the DB on a 
> 3Gig xeon with hyperthreading, 2Gig RAM and some fast disks. 
> Monitoring around 2500 services across 2000 hosts.
> 
> Please let us know if you do have any success with it
> 
> Kind Regards,
> -- 
> Dan Hopkins
> 


-------------------------------------------------------
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