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