Re: Large event database table and timeouts

[Ryan Revels]

Martin,

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,

Ryan

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


--
To unsubscribe:
https://lists.nongnu.org/mailman/listinfo/monit-general