Currently Being Moderated

How to clean your events database

VERSION 1 

Created on: Sep 14, 2009 12:00 PM by Noel Brockett - Last Modified:  Sep 14, 2009 12:01 PM by Noel Brockett

Average User Rating
(3 ratings)

My events database was growing a lot although I already had used the cleanupscripts of zenoss. Here is how to clean more.

 

In the eventsDB there is a tabel called 'details'

 

Maybe you wondered what this table contains.
The detail table is used to store the extra fields of events that you see on the Detail tab when you click the magnifying glass on an event. These would include SNMP trap varbinds as well as any other fields you extract from events using event mapping rules.

 

There is one downside on this.
It seems this table is never cleaned or cleared up.

 

This table can grow in time and now at my side it's 170MB big.

 

Now I would suggest to add this in your /etc/cron.daily/zenoss

 

execute this a the user root:

 

 

printf '# This mysql query will clean the details table\necho "DELETE FROM events.detail WHERE evid NOT IN (SELECT evid FROM events.status UNION SELECT evid FROM events.history);" | mysql -uzenoss -pzenoss\n' >> /etc/cron.daily/zenoss


This will add a query to your cron daemon and will clean the database every day.

7,517 Views Tags: database, events, eventsdb
Falk Falk  says:
Perfect, I'm trying this out now.
Thanks!
Dieter_be Dieter_be  says:

For me this wasn't enough: even though i told zenoss to prune events older then 40 days, it seems to not work.

so based on http://www.mail-archive.com/zenoss-users@zenoss.org/msg31456.html//www.mail-archive.com/zenoss-users@zenoss.org/msg31456.html

i also put the following in my zenoss crontab:

echo "DELETE FROM history WHERE lastTime < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 40 DAY));" | mysql -uzenoss -pzenoss events

the query is still running but i expect it will give me more free diskspace (at least, when innodb's auto vacuuming kicks in)

Dieter_be Dieter_be  says in response to Dieter_be:

hmm.. i'm not getting my diskspace back.

Even doing a 'ALTER TABLE <name> TYPE=InnoDB' on all innodb tables didn't help.. (this should make innodb delete it's still data)

Dieter_be Dieter_be  says in response to Dieter_be:

I finally got the diskspace back by doing a database backup, removing the data, and importing the backup again.

 

see http://crazytoon.com/2007/04/03/mysql-ibdata-files-do-not-shrink-on-database-deletion-innodb/

rmiddle rmiddle  says in response to Dieter_be:

Innodb data bases do not shrink.  That is a major glitch in that datatype in my view.  So it clears space for new records but the only way to reclaim space is to export and then reinport the data.

 

Thanks

Robert

Bookmarked By (5)

More Like This

  • Retrieving data ...