[Top][All Lists]

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: Large event database table and timeouts

From: Martin Pala
Subject: Re: Large event database table and timeouts
Date: Thu, 17 May 2012 23:10:47 +0200

Hello Ryan,

yes, it's possible to increase the timeout: the data are fetched via JSON, you 
can modify the value in the "docroot/reports/events/index.csp" file (for 
M/Monit 2.4):

        myDataSource.connTimeout = 5000;

The value is in milliseconds => 5000ms = 5s.

The direct delete from the event table is possible:

        DELETE FROM event WHERE collectedsec > <unixtime_timestamp>;

You can do the delete online - in such case the events count link for host and 
its services in the status page table won't match the real count, as the counts 
are cached to offload the expensive query. The event counts can be fixed too, 
but you'll need to stop M/Monit during the process, so it the in-memory and 
database counts will be consistent:

1.) stop M/Monit
2.) run:
        UPDATE host SET eventscount=(SELECT COUNT(*) FROM event WHERE;
        UPDATE service SET eventscount=(SELECT COUNT(*) FROM event WHERE 
event.hostid=service.hostid AND event.service_nameid=service.nameid);
3.) start M/Monit

We can implement automatic purging of events older then given time if it will 
help you => default can be keep all events, optionally set to delete events 
older then for example 2 years.


On May 16, 2012, at 8:22 PM, Ryan Revels wrote:

> I'm using PostgreSQL as my backend for M/Monit. The table event is currently 
> over 1M lines. With a table this large, the Events page on the GUI times out 
> with a JSON error:
> Load error: Internal Server error
> I've tried tweaking PostgreSQL and adding additional indexes to make the 
> query faster but have had no luck. Is there a way to increase the 5 second 
> timeout for this query when building the GUI table: SELECT event.hostid, 
> AS hostname, event.collectedsec, AS servicename, event.state, 
> event.message, event.hasnotice, FROM event, host, name nh, name ns   
>    ORDER BY event.collectedsec desc, 
> event.collectedusec desc LIMIT 15 OFFSET 0;
> If not, what is the proper way to remove old data to decrease the size of the 
> table?
> Is there another option I'm not considering?
> Thanks,
> Ryan
> --
> To unsubscribe:

reply via email to

[Prev in Thread] Current Thread [Next in Thread]