Skip navigation
8236 Views 12 Replies Latest reply: Jan 23, 2013 1:47 PM by remotesyslog RSS
remotesyslog Rank: White Belt 22 posts since
Apr 27, 2009
Currently Being Moderated

Jul 27, 2011 7:28 PM

Shrinking the zenoss database

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

Attachments:
  • dpetzel Rank: Brown Belt 1,141 posts since
    Oct 17, 2010
    Currently Being Moderated
    1. Jul 27, 2011 7:50 PM (in response to remotesyslog)
    Re: Shrinking the zenoss database

    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:

    http://community.zenoss.org/message/32969#32969

  • martl20 Rank: White Belt 9 posts since
    Feb 17, 2011
    Currently Being Moderated
    2. Jul 28, 2011 8:02 AM (in response to dpetzel)
    Re: Shrinking the zenoss database

    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

  • jmp242 ZenossMaster 4,060 posts since
    Mar 7, 2007
    Currently Being Moderated
    5. Jul 29, 2011 8:55 AM (in response to remotesyslog)
    Re: Shrinking the zenoss database

    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

  • Rank: Brown Belt 260 posts since
    Mar 30, 2009
    Currently Being Moderated
    7. Jul 29, 2011 4:20 PM (in response to remotesyslog)
    Re: Shrinking the zenoss database

    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.

  • Rank: Brown Belt 260 posts since
    Mar 30, 2009
    Currently Being Moderated
    8. Jul 29, 2011 4:20 PM (in response to Nick Yeates)
    Re: Shrinking the zenoss database

    Summary

    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:

    • transaction-safe storage engine for MySQL that has commit, rollback, and     crash-recovery capabilities
    • row-level locking and Oracle-style consistent non-locking     reads (increases multi-user concurrency and performance)
    • stores data in clustered indexes to     reduce I/O for common queries based on primary keys

    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.

    Stopping Zenoss

    Before proceeding with the dump of the database, you will need to  first stop Zenoss.You must perform these steps as the zenoss user.

    1. zenoss stop
    2. ps -ef |grep py

    If you see any zenoss processes still running as a result of the ps command, kill the processes before proceeding.

    Dumping the Database

    Dump and drop the events database:

    $ su - zenoss
    $ mysqldump -uzenoss -pzenoss events > events.sql
    $ mysql -u root
    mysql> drop database events;
    mysql> \q

    Delete the Database and Create a New Database

    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

    Restore the Database

    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.sql
    mysql> exit 
    $ mysql -uzenoss -pzenoss events < /opt/zenoss/Products/ZenEvents/db/zenprocs.sql

    More Information

    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

  • Rank: Brown Belt 260 posts since
    Mar 30, 2009
    Currently Being Moderated
    10. Jul 29, 2011 5:48 PM (in response to remotesyslog)
    Re: Shrinking the zenoss database

    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.

  • Mahtaj Khamneian Newbie 1 posts since
    Jan 23, 2013
    Currently Being Moderated
    11. Jan 23, 2013 12:25 PM (in response to Nick Yeates)
    Re: Shrinking the zenoss database

    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.

More Like This

  • Retrieving data ...

Legend

  • Correct Answers - 4 points
  • Helpful Answers - 2 points