Zenoss Archival Server

[Brian S]

Brian S [http://community.zenoss.org/people/z3r0day] created the discussion

"Zenoss Archival Server"

To view the discussion, visit: http://community.zenoss.org/message/68019#68019

--------------------------------------------------------------
Hello Everyone,


I'm new to the forums and need some assistance getting a solution to my problem.

The company I work for runs two Zenoss servers - one does the SMTP checking/diagnostics and the other handles the GUI and displays the data/graphs.

The company has decided that they would like to keep ALL historical data for purposes of pattern tracking & network statistics.  The problem is that the MySQL database which stores this data is getting quite large, and will soon completely fill the partition that it exists on.

I decided it would be best to move all data older than 1 year off the server, and onto a third Zenoss server for analysis only - the server would just provide a way to display the historical/archived data in the database, and would not perform any other network diagnostics.  I'm thinking I would manually move this data every 6 months (or script it if I can get to that point).

Can some one tell me, is this the best way to go about solving the problem? And if so, can anyone provide me any steps on moving data older than 1 year off of the MySQL database and onto the new server?

Currently, I'm running Zenoss v3.0.3 on CentOS 5.5. I would think the third server should be built identically to prevent any issues caused by version differences.  Any advice on this third server setup is also welcomed!

Your thoughts and advice are greatly appreciated!


Thank you,
z3r0day
--------------------------------------------------------------

Reply to this message by replying to this email -or- go to the discussion on Zenoss Community
[http://community.zenoss.org/message/68019#68019]

Start a new discussion in zenoss-users by email
[discussions-community-forums-zenoss--***@community.zenoss.org] -or- at Zenoss Community
[http://community.zenoss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2003]

[Brian S]

Brian S [http://community.zenoss.org/people/z3r0day] created the discussion

"Re: Zenoss Archival Server"

To view the discussion, visit: http://community.zenoss.org/message/68075#68075

--------------------------------------------------------------
Hi Guys,


I'm learning a little more about my setup - I should have also mentioned that this is an environment which I inherited with no documentation.

It looks like zenoss01 is running the web GUI, holds the MySQL database, and also does half of the collecting.  Zenoss02 does the other half of the collecting.

Zenoss02 on the other hand has much more disk space, so perhaps I could archive old records for viewing on zenoss02; Is there anyway to setup the GUI on the zenoss02 collector to view a local database?

Again, any suggestions, advice, or direction is greatly appreciated!


Thank you,

z3r0day
--------------------------------------------------------------

Reply to this message by replying to this email -or- go to the discussion on Zenoss Community
[http://community.zenoss.org/message/68075#68075]

Start a new discussion in zenoss-users by email
[discussions-community-forums-zenoss--***@community.zenoss.org] -or- at Zenoss Community
[http://community.zenoss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2003]

[jmp242]

jmp242 [http://community.zenoss.org/people/jmp242] created the discussion

"Re: Zenoss Archival Server"

To view the discussion, visit: http://community.zenoss.org/message/68079#68079

--------------------------------------------------------------
It sounds like it's set up as distributed collectors. You probably cannot do what you want really, but let's see if other community members have a comment.

--
James Pulver
ZCA Member
LEPP Computer Group
Cornell University
--------------------------------------------------------------

Reply to this message by replying to this email -or- go to the discussion on Zenoss Community
[http://community.zenoss.org/message/68079#68079]

Start a new discussion in zenoss-users by email
[discussions-community-forums-zenoss--***@community.zenoss.org] -or- at Zenoss Community
[http://community.zenoss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2003]

[Shane Scott]

Shane Scott [http://community.zenoss.org/people/hackman238] created the discussion

"Re: Zenoss Archival Server"

To view the discussion, visit: http://community.zenoss.org/message/68106#68106

--------------------------------------------------------------
Brian S:

ZenOSS v4.2 supports archiving events older than so many days. This allows them to be unloaded from memory and loaded on demand. This will let you keep, in theory, as many events as you like without hampering performance. In v3 I don't think there's a way to do what you need for the events unless you simply dump them on occassion for storage. With that it'll be a hassle to read the data back. The newest product should solve this out of the box.

As for the RRDs, in theory it won't be possible to keep all the data forever since the RRDs have a defined range. I think the best solution for what you need would be to craft a small daemon that reads the RRD data in to a mysql database which can then be fed in to a reporting tool like JasperSoft or Sphinx. In this manner you can keep all the RRD performance history you like without hampering the active collection.

Am I understanding what you need or did I miss the target?

Best,
--Shane Scott (Hackman238)
--------------------------------------------------------------

Reply to this message by replying to this email -or- go to the discussion on Zenoss Community
[http://community.zenoss.org/message/68106#68106]

Start a new discussion in zenoss-users by email
[discussions-community-forums-zenoss--***@community.zenoss.org] -or- at Zenoss Community
[http://community.zenoss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2003]

[Brian S]

Brian S [http://community.zenoss.org/people/z3r0day] created the discussion

"Re: Zenoss Archival Server"

To view the discussion, visit: http://community.zenoss.org/message/68114#68114

--------------------------------------------------------------
Thank you jmp242 and Shane for the responses!

Shane,


I'm not sure I completely understand your suggestion.  Basically in short, the MySQL database is filling up the partition it lives on, and I've been instructed to keep all historical data.  Otherwise, this could easily be solved by automatically deleting events older than X days.

My initial thought is to move all cleared events and statistical data in the database off of that server, and onto a different server just for the purposes of viewing and analysis.  Now, I don't completely understand how zenoss uses the database, so I may be lacking some key information on if this is even possible.

I envision having zenoss01 and zenoss02 to keep on collecting and storing data like normal, but only storing say 6 months worth of data (event and statistics/graphs).  All data older than that would be moved to a different  server (zenoss03), where one could log onto just to view that old information.

Was your response accurate to my vision?  Again, I didn't quite understand all of it - especially the RRD part, I need to do more studying-up.


Thanks again!!!
z3r0day
--------------------------------------------------------------

Reply to this message by replying to this email -or- go to the discussion on Zenoss Community
[http://community.zenoss.org/message/68114#68114]

Start a new discussion in zenoss-users by email
[discussions-community-forums-zenoss--***@community.zenoss.org] -or- at Zenoss Community
[http://community.zenoss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2003]

[Brian S]

Brian S [http://community.zenoss.org/people/z3r0day] created the discussion

"Re: Zenoss Archival Server"

To view the discussion, visit: http://community.zenoss.org/message/68166#68166

--------------------------------------------------------------
Hi Shane & the Community,


Do you have any more advice on this?  I'm pretty stumped, and am on a time constraint as the partition continues to grow.


All help and advice are appreciated!!!


Thank you,

z3r0day
--------------------------------------------------------------

Reply to this message by replying to this email -or- go to the discussion on Zenoss Community
[http://community.zenoss.org/message/68166#68166]

Start a new discussion in zenoss-users by email
[discussions-community-forums-zenoss--***@community.zenoss.org] -or- at Zenoss Community
[http://community.zenoss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2003]

[Brian S]

Brian S [http://community.zenoss.org/people/z3r0day] created the discussion

"Re: Zenoss Archival Server"

To view the discussion, visit: http://community.zenoss.org/message/68327#68327

--------------------------------------------------------------
So I've been doing lots of digging into the events database.  I found that in the history table, the severity type "debug" (or "severity = '1' ") contains roughly 82% of all historical events.  Assuming that all events are the same size, I can conclude that these events take up about 45GB of the 55GB events database.  I'm going to take a guess and assume that if I delete all of these debug events and maintain them moving forward, I will not have the need for an event "Archival" server any time soon.

The Historical Event console crashes zenoss when trying to look up records...which then requires a "server zenoss restart" to fix it.  Therefore, it will be very diffcult to try to delete these debug events via the Event Console.  I'm guessing this is due to the size of the events database. 

Would it be recommended for me to delete these historical debug events via mysql? Is there any significant importance to these event types? If not, is there an easy way to disable the majority of them?

Any advice is welcome and appreciated! Thanks in advance!
z3r0day
--------------------------------------------------------------

Reply to this message by replying to this email -or- go to the discussion on Zenoss Community
[http://community.zenoss.org/message/68327#68327]

Start a new discussion in zenoss-users by email
[discussions-community-forums-zenoss--***@community.zenoss.org] -or- at Zenoss Community
[http://community.zenoss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2003]

[Shane Scott]

Shane Scott [http://community.zenoss.org/people/hackman238] created the discussion

"Re: Zenoss Archival Server"

To view the discussion, visit: http://community.zenoss.org/message/68409#68409

--------------------------------------------------------------
Brian:

Sorry for the delay.

I didn't realize your disk was getting full. That's a tricky problem to solve. I would definitely drop debug events older than a day or two. Debug events occur when a datasource isn't working as expected so older copies don't have any value. Give the sql query "delete from history where history.severity = 'debug' " after doing a backup to delete all debug events. I haven't tested that query, so it might need tweaking. You can drop future debug events altogether if you wish using a transform such as if evt.severity=1: evt._action = 'drop'. in event class /

Best,
--Shane Scott (Hackman238)
--------------------------------------------------------------

Reply to this message by replying to this email -or- go to the discussion on Zenoss Community
[http://community.zenoss.org/message/68409#68409]

Start a new discussion in zenoss-users by email
[discussions-community-forums-zenoss--***@community.zenoss.org] -or- at Zenoss Community
[http://community.zenoss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2003]

[Brian S]

Brian S [http://community.zenoss.org/people/z3r0day] created the discussion

"Re: Zenoss Archival Server"

To view the discussion, visit: http://community.zenoss.org/message/68437#68437

--------------------------------------------------------------
Hi Shane,


Thanks for getting back to me! I would have responded sooner, but I had a plan of action in mind and hadn't seen much activity on my thread, so haven't checked for a few days.

Actually your recommendation is exactly what I had in mind, and last night I executed the query:
*DELETE FROM events.history WHERE severity = "1";*  1 is the ID for DEBUG events.  It deleted over 31 million out of a total of about 39 million events - it took a little over 10 hours.

My next concern though is that the database didn't change in size.  More specifically, the *history.ibd* table did not shrink at all, and is still at 56GB.  On top of that, the */var/lib/mysql/ibdata1* file grew from under a gig, to 4.4GB and therefore doing the exact opposite as I was expecting.  The partition is now  88% full up from about 83%

I'm fairly new to the innards of Zenoss as well as MySQL.  I do have some database experience, so it's not all foreign.  After research, I think the database uses something called Per-Table Tablespaces.

I ran into a posting on the mysql forum that may indicate that the database will use the blank rows for new data, instead of shrinking the size after clearing out all those events.  Here's where I saw it: http://forums.mysql.com/read.php?35,121880,121886#msg-121886 http://forums.mysql.com/read.php?35,121880,121886#msg-121886

Shane, or anyone, can you confirm this?  Does this mean that the DB, particularly the *history.idb* table, will not grow as long as I keep the total events under what they were before the mass delete?

Shane, thanks for the advice on the transform - I'm going to look more into that!


Regards,

z3r0day
--------------------------------------------------------------

Reply to this message by replying to this email -or- go to the discussion on Zenoss Community
[http://community.zenoss.org/message/68437#68437]

Start a new discussion in zenoss-users by email
[discussions-community-forums-zenoss--***@community.zenoss.org] -or- at Zenoss Community
[http://community.zenoss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2003]

[Shane Scott]

Shane Scott [http://community.zenoss.org/people/hackman238] created the discussion

"Re: Zenoss Archival Server"

To view the discussion, visit: http://community.zenoss.org/message/68438#68438

--------------------------------------------------------------
Brian:

Ah yeah, its because its an innodb table. The easiest solution would be to dump the history to file using mysqldump. Dump it with the dd table structure. Once done stop zenoss and drop the history table. Finally load the dumped history back in to mysqld. This will delete and recreate the idb to fit the contents.

Yes, innodb grows to content but doesnt shrink.

Best.
--Shane Scott (Hackman238)
--------------------------------------------------------------

Reply to this message by replying to this email -or- go to the discussion on Zenoss Community
[http://community.zenoss.org/message/68438#68438]

Start a new discussion in zenoss-users by email
[discussions-community-forums-zenoss--***@community.zenoss.org] -or- at Zenoss Community
[http://community.zenoss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2003]

[Brian S]

Brian S [http://community.zenoss.org/people/z3r0day] created the discussion

"Re: Zenoss Archival Server"

To view the discussion, visit: http://community.zenoss.org/message/68439#68439

--------------------------------------------------------------
Hi Shane,


I did see some suggestions similar to what you have recommended.  That makes me feel better - I'll give that a try at some point.  My only question, what do you mean by "dump it with the dd table structure"?


Thanks for the quick response!

z3r0day
--------------------------------------------------------------

Reply to this message by replying to this email -or- go to the discussion on Zenoss Community
[http://community.zenoss.org/message/68439#68439]

Start a new discussion in zenoss-users by email
[discussions-community-forums-zenoss--***@community.zenoss.org] -or- at Zenoss Community
[http://community.zenoss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2003]

[dhopp]

dhopp [http://community.zenoss.org/people/dhopp] created the discussion

"Re: Zenoss Archival Server"

To view the discussion, visit: http://community.zenoss.org/message/68459#68459

--------------------------------------------------------------
You can remove debug events fairly easy.  Zenoss actually supplies a script to do it so you can just cron it in the Zenoss users crontab:

### Remove debug events older then 7 days
15 0 * * 0 /opt/zenoss/Products/ZenUtils/ZenDeleteHistory.py --severity=1 --numDays=7

--Dennis
--------------------------------------------------------------

Reply to this message by replying to this email -or- go to the discussion on Zenoss Community
[http://community.zenoss.org/message/68459#68459]

Start a new discussion in zenoss-users by email
[discussions-community-forums-zenoss--***@community.zenoss.org] -or- at Zenoss Community
[http://community.zenoss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2003]

[Brian S]

Brian S [http://community.zenoss.org/people/z3r0day] created the discussion

"Re: Zenoss Archival Server"

To view the discussion, visit: http://community.zenoss.org/message/68699#68699

--------------------------------------------------------------
dhopp,


Thank you - I have implemented this cronjob and have the debug events under control!

The next step I have is to backup either the history table, or the entire database, then delete the history table or entire database, and finally restore from backup.  That will create the database and/or history table to the size it should be now that most of the events have been deleted, thus creating more free space on the partition.

Since doing the mass delete of all debug events, the partition hasn't grown in size at all as I expected.

After I'm finished, I will write up a summary of what I did, as well as what I could have done had I gone through and implemented an actual "Archival" server so others may get some use from it.


Thanks again!

-z3r0day
--------------------------------------------------------------

Reply to this message by replying to this email -or- go to the discussion on Zenoss Community
[http://community.zenoss.org/message/68699#68699]

Start a new discussion in zenoss-users by email
[discussions-community-forums-zenoss--***@community.zenoss.org] -or- at Zenoss Community
[http://community.zenoss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2003]

[Brian S]

Brian S [http://community.zenoss.org/people/z3r0day] created the discussion

"Re: Zenoss Archival Server"

To view the discussion, visit: http://community.zenoss.org/message/69042#69042

--------------------------------------------------------------
Hi All,


I have regained disk space back to my partition.  I used the "optimize table" command to resize the history.ibd table ("optimize table events.history;"). After the completion of this command, which took about 35 minutes, the table shrunk from 56GB down to 6.7GB.  This is a drastic improvement, and brought my overall partition size from 89% used down to 30% used.

Alternatively, I could have backed up the history.ibd table, deleted it, and restored it.  I wasn't completely comfortable with this since I wasn't sure how large the backup would be, and couldn't tell for sure if it would fit on the remaining partition space.

I could have also followed Shane's advice and backed up all databases, deleted all databases and ibd log files, and then restored all databases.  This would have also appropriately sized the history.idb table, and also would have reset the idb log files; the ibdata1 file is currently 4.4GB, but I'm not too worried.

If all else had failed, I could have exported all events by backing them up, and moving them to a different server with the same mysql version & zenoss version.  Restoring these events to the events.history table would have worked just fine, as I tested this in a scaled down test environment. I'm not going to get into detail, just wanted to mention that had I actually implemented a dedicated historical events "archival" server, that it would have worked.

Thanks Shane & dhopp for your helpfulness!
-z3r0day
--------------------------------------------------------------

Reply to this message by replying to this email -or- go to the discussion on Zenoss Community
[http://community.zenoss.org/message/69042#69042]

Start a new discussion in zenoss-users by email
[discussions-community-forums-zenoss--***@community.zenoss.org] -or- at Zenoss Community
[http://community.zenoss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2003]