Jul 27, 2011 7:28 PM
Shrinking the zenoss database
-
Like (0)
Hello there. it has been about 6 months since i last rebuilt zenoss. In that time, the database has filled the 5gb VM disk, so i gave it 10GB, it filled that so i gave it 30GB, and in a month, it has gone from 10 to 22gb.
How the hell do i purge this database?!?! i have tried MULTIPLE suggestions from websites but nothing seems to make a difference. Surely there must be a proceedure to do this?
I almost had success with the follow command, but it just seems to time out for hours:
"DELETE FROM events.detail WHERE evid NOT IN (SELECT evid FROM events.status UNION SELECT evid FROM events.history);"
I have also tried a few other fixes. Would it just be possible to drop the history table whichis having the most problems? how do i recreate it?
I dont care about history!!! but i do care about using up all my disk space! i mean 22gb database??? come on zenoss!!
any ideas.?
I believe others have dropped the history table. I have not personally, so take this reply with a grain of salt. Here is a forum post from someone that did it:
Hi,
You can cleanup all your database tables, but if your using a mysql innodb it wont shrink the size on disk!
http://forums.mysql.com/read.php?35,121880,121886
You can create a new database and import all the data, this way you will get rid of al the filled up empty space in your database!
Cheers,
Martijn
Wow what a horrible design flaw! how can so many people use and recomend mysql if it has this horrible feature? anyways I am backing up the DB now and then will try recreating, except the history table.
I will try and document the proceedure if i have time.
well it was a lot of work, but here is what i did to get it going. I mostly used SSH command line to do all of this, and the server is a linux VM:
1.) drop events table and recreate (log into mysql, 'drop database events;')
1a.) Recreate by using the following command, logged in as ZENOSS user not root or the command will not work 'zeneventbuild localhost zenoss PASSWORD events'. (should tell you that its rebuilding shit, permissions errors mean that it doesnt have directory permissions on the OS, chmod 777 is your friend)
2.) check that zenoss is still working by logging into the web frontend. history table will be blank now.
3.) mysqldump all tables to file (mysqldump -pPASSWORD --all-databses > /temp/all.sql)
4.) shut down mysqld (/etc/init.d/mysqld stop)
5.) rename or move the ibdata file and ib_logfile1 and ib_logfile0 (i renamed so that i could swithc back if something went horribly wrong)
6.) start up mysqld and it should start cleanly. the log will say that its regenerating the ibdata file.
7.) restore tables from backup you made ('mysql -pPASSWORD < /temp/all.sql')
If any errors are detected, make sure that the two directories '/var/lib/mysql/mysql' and '/var/lib/mysql/events' are set to be read write by your database or database user (if you dont want to frack around, just chmod those puppies 777) ('chmod 777 /var/lib/mysql/mysql' etc)
8.) shut down mysql and start it up again. if it starts fine, great! if not, check /var/log/mysqld.log for errors and if nessecary run the check database command on the MYI files (eg 'myisamchk *.MYI -s' in /var/lib/mysql/events and /var/lib/mysql/mysql)
9.) restart zenoss VM. (reboot)
worked for me! although there were various linux issues i ran into on the way which needed to be delt with. Doesnt seem like i can use mysql workbench to log in as root to mysql, so i had to change the zenoss users password. Anyways thats probably my environemnt, but there you have it. Not sure if this is the right way, but now i have a 100meg database as opposed to 23 gigs.
WOOT!
Might want to wikify this ... Or if you prefer I can shelp it into the Forum FAQ Part 2.
--
James Pulver
Information Technology Area Supervisor
LEPP Computer Group
Cornell University
thats fine if you want. its obviously public domain
i think someone else should test it though. zenoss seems to be working (it sends alerts), but who knows, maybe i just got lucky.
Remotesyslog, some of those instructions look to be in the wrong order. You dropped the events database and THEN backed it up? Huh? Maybe you had not commited the drop? I am not sure. Despite, I have a solidly tested, Zenoss supported set of instructions for this that I will put in the next post. Sorry I didnt get them to you in time, but others can use it I am sure.
The reason that the mysql DB cant be shrunk is size, is because we are using the innodb storage engine. Other engines dont necessarily act like this. See the article I post.
This article explains how to dump and restore the MySQL events database used by Zenoss. The main reason you would need to perform this proceedure is to recover disk space used by the Events database.
Zenoss leverages the Innodb storage engine because of several distintinct advanages over other storage types:
The main disadvantage to Innodb is that it will use more disk space than some of the other storage methods, and deleting data (specifically historical events in terms of Zenoss) will not free up used disk space. To recover space you must dump and reload the database.
Before proceeding with the dump of the database, you will need to first stop Zenoss.You must perform these steps as the zenoss user.
If you see any zenoss processes still running as a result of the ps command, kill the processes before proceeding.
Dump and drop the events database:
$ su - zenoss
$ mysqldump -uzenoss -pzenoss events > events.sql
$ mysql -u root
mysql> drop database events;
mysql> \q
Execute the following commands to delete the database and create a new, empty database.
$ sudo /etc/init.d/mysqld stop
$ sudo rm /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile*
$ sudo /etc/init.d/mysqld start
$ mysql -u root
mysql> create database events;
mysql> grant super on *.* to 'zenoss'@'localhost' identified by 'zenoss';
mysql> flush privileges;
mysql> \q
Execute the following commands to restore the data that was previously dumped. Note that we are expecting to be in the $ZENHOME/Products/ZenEvents/db directory.
$ mysql -uzenoss -pzenoss events
mysql> \. events.sqlmysql> exit
$ mysql -uzenoss -pzenoss events < /opt/zenoss/Products/ZenEvents/db/zenprocs.sql
More information on the Innodb storage engine can be found in the MySQL documentation: http://dev.mysql.com/doc/refman/5.1/en/innodb.html
"You dropped the events database and THEN backed it up?"
yes so that it would be blank. perhaps you are missing the point of this exercise. Drop and then recreate, then back up.
Ahh ok, then what you did is the right order :-) I had thought you said you wanted to just remove history and to reduce the size of the DB - I assumed you wanted to keep your events.
If you want to keep your events and shrink the DB, do what I posted. If you want to clear completly, do what remotesyslog did.
Is this something that has to be done on a regular basis?
I have the same issue with the innodb data file growing to 18gb. I have temporarily moved the file to another filesystem and have to make a decision regarding it's permanent location.
2 years later and i never had to do it again. I just checked and my zenoss DB is about 1gb.
However I may have changed some stuff on the frontened to make it log less events or something. I really cant remember, however I have not run into this problem again.
Follow Us On Twitter »
|
Latest from the Zenoss Blog » | Community | Products | Services Resources | Customers Partners | About Us | ||
Copyright © 2005-2011 Zenoss, Inc.
|
||||||||