[Top][All Lists]

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

Re: Large event database table and timeouts

From: Ryan Revels
Subject: Re: Large event database table and timeouts
Date: Thu, 17 May 2012 17:57:18 -0500


Thank you; this is great information.
I think a feature like you mention would be very useful (ability to configure auto-purging of events after x years).

Thanks again,

On Thu, May 17, 2012 at 4:10 PM, Martin Pala <address@hidden> wrote:
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   WHERE AND AND    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:

To unsubscribe:

reply via email to

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