Pruning mySQL of old data

[remotesyslog]

I am currently running out of disk space on my zenoss machine. I was wondering if there was any way to prune the mysql database, or if zenoss provides a way to delete events out of the database.

Anyone know any way to trim say anything over 3 months from the DB?




-------------------- m2f --------------------

Read this topic online here:
http://forums.zenoss.com/viewtopic.php?p=35155#35155

-------------------- m2f --------------------

[mjoncic]

Well, there is a stored procedure clean_history(<# month>).

the <# month> is the minimum age of an event to be cleared out of the history table.

eg. clean_history(3) cleans all events older than 3 month.

something like this, but beware, i haven't used it in the last two years.
(so maybe it's no longer the same, did you check the documentation?)

brgds
Michael




-------------------- m2f --------------------

Read this topic online here:
http://forums.zenoss.com/viewtopic.php?p=35184#35184

-------------------- m2f --------------------

[mwcotton]

When does that stored procedure run?




-------------------- m2f --------------------

Read this topic online here:
http://forums.zenoss.com/viewtopic.php?p=35190#35190

-------------------- m2f --------------------

[rebelinux]

you can specified the deleted history time on Event Manager under Delete Historical Events Older Than (days) this can automatically delete the old events that you specified in the day variable

Zenoss Admin Guide Event Manager

http://www.zenoss.com/community/docs/zenoss-guide/2.4.0/ch07s01.html#d4e3774

or you can manually delete the events using the python script

/usr/local/zenoss/zenoss/Products/ZenUtils/ZenDeleteHistory.py --numDays=15

where --numDays is the day of events pruning. You can run it becoming zenoss user and run

/usr/local/zenoss/python/bin/.python.bin /usr/local/zenoss/zenoss/Products/ZenUtils/ZenDeleteHistory.py --numDays=15

Post by remotesyslog
I am currently running out of disk space on my zenoss machine. I was wondering if there was any way to prune the mysql database, or if zenoss provides a way to delete events out of the database.
Anyone know any way to trim say anything over 3 months from the DB?

-------------------- m2f --------------------

Read this topic online here:
http://forums.zenoss.com/viewtopic.php?p=35193#35193

-------------------- m2f --------------------

[remotesyslog]

Didnt seem to make an impact on my database size. I was getting


Code:

mysql> call clean_history(3);
Query OK, 0 rows affected (7 min 6.62 sec)




Perhaps i am not executing it correctly? On msSQL the command to run stored procedures is 'exec'.

does look like its running, just not modifying anything.




-------------------- m2f --------------------

Read this topic online here:
http://forums.zenoss.com/viewtopic.php?p=35195#35195

-------------------- m2f --------------------

[remotesyslog]

Post by rebelinux
you can specified the deleted history time on Event Manager under Delete Historical Events Older Than (days) this can automatically delete the old events that you specified in the day variable

It is currently set at 45 days. I do not think that is the same thing. Regardless, I have set it to 30 and see no difference in the disk space used by the zenoss database.

Post by rebelinux
or you can manually delete the events using the python script
/usr/local/zenoss/zenoss/Products/ZenUtils/ZenDeleteHistory.py --numDays=15

/usr/local/zenoss does not exsist.

Since there is no "locate" on this zenoss appliance, I am unable to search the drive for this script. Any other ideas where it might be?[/quote]




-------------------- m2f --------------------

Read this topic online here:
http://forums.zenoss.com/viewtopic.php?p=35201#35201

-------------------- m2f --------------------

[rebelinux]

you can use the $ZENHOME from the zenoss user shell to point you to the correct zenoss install location.

Exp:

[***@rebelde share]$ echo $ZENHOME
/usr/local/zenoss/zenoss

also looking in to the mysql events database there is a age_events store procedure. Second using mysql administrator you can view the tables data length that specified the size of the history table. Mine was 1.5 GB before i set the "Delete Historical Events Older Than (days)" to 15 days. Now the size of the event database is 100 MB.

you can install mysql administrator or mysql query browser to make custom sql delete statements that fits your needs

Post by remotesyslog

Post by rebelinux
you can specified the deleted history time on Event Manager under Delete Historical Events Older Than (days) this can automatically delete the old events that you specified in the day variable

It is currently set at 45 days. I do not think that is the same thing. Regardless, I have set it to 30 and see no difference in the disk space used by the zenoss database.

Post by rebelinux
or you can manually delete the events using the python script
/usr/local/zenoss/zenoss/Products/ZenUtils/ZenDeleteHistory.py --numDays=15

/usr/local/zenoss does not exsist.
Since there is no "locate" on this zenoss appliance, I am unable to search the drive for this script. Any other ideas where it might be?

-------------------- m2f --------------------

Read this topic online here:
http://forums.zenoss.com/viewtopic.php?p=35202#35202

-------------------- m2f --------------------

[rebelinux]

i forgot something yo can search the filesystem for the ZenDeleteHistory.py file like this:

find / | grep ZenDeleteHistory.py

Exp:

[***@rebelde share]$ find / | grep ZenDeleteHistory.py
/usr/local/zenoss/zenoss/Products/ZenUtils/ZenDeleteHistory.py

Post by remotesyslog

Post by rebelinux
you can specified the deleted history time on Event Manager under Delete Historical Events Older Than (days) this can automatically delete the old events that you specified in the day variable

It is currently set at 45 days. I do not think that is the same thing. Regardless, I have set it to 30 and see no difference in the disk space used by the zenoss database.

Post by rebelinux
or you can manually delete the events using the python script
/usr/local/zenoss/zenoss/Products/ZenUtils/ZenDeleteHistory.py --numDays=15

/usr/local/zenoss does not exsist.
Since there is no "locate" on this zenoss appliance, I am unable to search the drive for this script. Any other ideas where it might be?

-------------------- m2f --------------------

Read this topic online here:
http://forums.zenoss.com/viewtopic.php?p=35203#35203

-------------------- m2f --------------------

[remotesyslog]

Thanks.


using mysql admin, i can see that the history tables data length is 6.1gb and the index length is 1.4gb. It has 4.8 million rows.


I just tried changing it down to 15 days, but the GB values have not changed.

However, it must have done something because not i have 500k less rows in the database.




-------------------- m2f --------------------

Read this topic online here:
http://forums.zenoss.com/viewtopic.php?p=35204#35204

-------------------- m2f --------------------

[rebelinux]

this delete statement erase all data before February 25 of 2009 2009-02-25

delete FROM history where history.stateChange <= '2009-02-25'


this kind of delete statement can be executed from mysql query browser
or directly from mysql shell.

mysql> use events;
Database changed
mysql> delete from history where history.stateChange <= '2009-02-25';
Query OK, 48 rows affected (0.01 sec)
mysql>

Post by remotesyslog
Thanks.
using mysql admin, i can see that the history tables data length is 6.1gb and the index length is 1.4gb. It has 4.8 million rows.
I just tried changing it down to 15 days, but the GB values have not changed.
However, it must have done something because not i have 500k less rows in the database.

-------------------- m2f --------------------

Read this topic online here:
http://forums.zenoss.com/viewtopic.php?p=35206#35206

-------------------- m2f --------------------

[psmith]

After pruning data from history table don't forget to clean the detail table as well which contains the detail fields for your events. You can do that by running the following query (it may take a while on big tables)

Code:
DELETE from events.detail where evid NOT IN (SELECT evid FROM events.status UNION SELECT evid FROM events.history)



After you delete old data you should clean the affected tables by running

Code:
OPTIMIZE table history
OPTIMIZE table detail
OPTIMIZE table status



If you are using InnoDB storage engine on mysql it will not decrease the data file size after table pruning. The only way to reclaim the space is to backup the data, stop DB, remove InnoDB data and log files, start DB and restore the data from backup. More details on this procedure here
http://dev.mysql.com/doc/refman/5.1/en/adding-and-removing.html




-------------------- m2f --------------------

Read this topic online here:
http://forums.zenoss.com/viewtopic.php?p=35209#35209

-------------------- m2f --------------------

[trondah]

Another way is to recreate the events database:

$ mysqladmin drop events
$ zeneventbuild localhost zenoss password events




-------------------- m2f --------------------

Read this topic online here:
http://forums.zenoss.com/viewtopic.php?p=35213#35213

-------------------- m2f --------------------

[remotesyslog]

Actually i was able to get it down to 2.8 GB yesterday. The internet went down so i was not able to respond. What i did, was run the "optimize database" command from the Mysql administrator.
It ran for about 30 minutes and then when i looked at the DB again it was 2.8 gb.

Not sure if that did it, or if it just took its time with changing the setting from 30 days to 15 which i also did.

Anyway, problem solved! for now....




-------------------- m2f --------------------

Read this topic online here:
http://forums.zenoss.com/viewtopic.php?p=35234#35234

-------------------- m2f --------------------

[Gabe]

Post by trondah
$ mysqladmin drop events
$ zeneventbuild localhost zenoss password events

***@zenoss:/usr/local/zenoss/mysql/data$ zeneventbuild localhost zenoss zenoss events
creating database
dropping any triggers that my already exist
loading schema
ERROR 1146 (42S02) at line 86: Table 'events.status' doesn't exist
loading stored procedures
events database created and loaded

sounded good but eeeh oops !
im using zenoss 2.4.1
what should i do now ?




-------------------- m2f --------------------

Read this topic online here:
http://forums.zenoss.com/viewtopic.php?p=35505#35505

-------------------- m2f --------------------

[ewall]

Ahh! Here I go necroing another old thread...

In an effort to trim my historical Zenoss data sooner than later, I ran the ZenDeleteHistory.py to prune to 30 days, and then re-optimized the tables.

In the process, my MySQL 'ibdata1' file grew over 50% from 3gb to 4.7gb. And my Data.fs file (I still don't really know what that is?) grew from 18gb to 19.7gb.

This unchecked data growth is becoming a big problem. Anyone have other tips on trimming this?




-------------------- m2f --------------------

Read this topic online here:
http://forums.zenoss.com/viewtopic.php?p=37663#37663

-------------------- m2f --------------------