Re: Large event database table and timeouts

[Martin Pala]

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 
event.hostid=host.id);
        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.


Regards,
Martin



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, 
> nh.name AS hostname, event.collectedsec, ns.name AS servicename, event.state, 
> event.message, event.hasnotice, event.id FROM event, host, name nh, name ns   
> WHERE event.hostid=host.id AND host.nameid=nh.id AND 
> event.service_nameid=ns.id    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:
> https://lists.nongnu.org/mailman/listinfo/monit-general