<html><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><div><br class="webkit-block-placeholder"></div><div>Hello RTGers,</div><div><br class="webkit-block-placeholder"></div><div>I'm new to RTG so I beg forgiveness should I ramble too far off into the bushes.</div><div><br class="webkit-block-placeholder"></div><div>We have a fairly large RTG installation with about 23,000 ports being monitored. We have a number of polling machines that poll and feed data back into a central MySQL database. For the most part, this works pretty well. However, we have a bit of a problem in that our database size it getting out of hand. We're up to 184GB of bandwidth data and running out of disk space. </div><div><br class="webkit-block-placeholder"></div><div>As you can imagine, having that much data causes problems, such as taking a really long time to generate reports. We replicate the databases to a mysql slave and offload all the reporting to it. To give you an idea of what I mean by slow; I just wrote a report that exports a CSV file containing every interface and how much bandwidth it passed during a given time range. I chose CSV because our bean counters can easily import the billing system. The network team and managers can click the email attachment and open it in Excel. </div><div><br class="webkit-block-placeholder"></div><div>To generate that report for a one month period takes about 26 hours to complete. On AMD Opterons with 8GB RAM. I have heavily tuned MySQL to perform adequately under this workload. Tuning helped. A lot. But it can only go so far.</div><div><br class="webkit-block-placeholder"></div><div>So, we're faced with the choice of buying some really expensive hardware to move RTG onto or find some way to reduce the size oft the databases. We really don't want to simply discard old data.</div><div><br class="webkit-block-placeholder"></div><div>I've done a bit of work with rrdtool in the past and got the bright idea that I could implement some data aggregation. Rather than do averaging like rrdtool does, I could reduce the data set significantly by writing a script that condensed older bandwidth data. I chose to condense an hours worth of bandwidth data into a single record which reduces the record count by one twelfth.</div><div><br class="webkit-block-placeholder"></div><div>I wrote a perl script that loops over every interface. It finds the oldest record and then generates a list of 1 hour intervals. Then it queries the database for all the records in each 1 hour interval and generates a summary entry. After successfully writing the summary entry into the database, it deletes all the entries that comprised it. Here's an example:</div><div><br class="webkit-block-placeholder"></div><div><div> range: 20070228220000 - 20070228230000 - 12 records.</div><div> id: 609 count: 15418917957 time: 2007-02-28 22:03:04</div><div> id: 609 count: 15322162907 time: 2007-02-28 22:08:04</div><div> id: 609 count: 16916402934 time: 2007-02-28 22:13:00</div><div> id: 609 count: 15361727173 time: 2007-02-28 22:17:59</div><div> id: 609 count: 15906038566 time: 2007-02-28 22:23:09</div><div> id: 609 count: 16745419536 time: 2007-02-28 22:28:01</div><div> id: 609 count: 16628650088 time: 2007-02-28 22:33:02</div><div> id: 609 count: 15940862962 time: 2007-02-28 22:38:00</div><div> id: 609 count: 15126799678 time: 2007-02-28 22:42:59</div><div> id: 609 count: 16288752555 time: 2007-02-28 22:47:59</div><div> id: 609 count: 14757762970 time: 2007-02-28 22:52:59</div><div> id: 609 count: 14268752126 time: 2007-02-28 22:58:01</div><div> INSERT INTO ifOutOctets_5 (id,counter,dtime) VALUES (609,188682249452,20070228225959);</div><div> DELETE FROM ifOutOctets_5 WHERE dtime='2007-02-28 22:03:04' AND id=609 AND counter=15418917957;</div><div> DELETE FROM ifOutOctets_5 WHERE dtime='2007-02-28 22:08:04' AND id=609 AND counter=15322162907;</div><div> DELETE FROM ifOutOctets_5 WHERE dtime='2007-02-28 22:13:00' AND id=609 AND counter=16916402934;</div><div> DELETE FROM ifOutOctets_5 WHERE dtime='2007-02-28 22:17:59' AND id=609 AND counter=15361727173;</div><div> DELETE FROM ifOutOctets_5 WHERE dtime='2007-02-28 22:23:09' AND id=609 AND counter=15906038566;</div><div> DELETE FROM ifOutOctets_5 WHERE dtime='2007-02-28 22:28:01' AND id=609 AND counter=16745419536;</div><div> DELETE FROM ifOutOctets_5 WHERE dtime='2007-02-28 22:33:02' AND id=609 AND counter=16628650088;</div><div> DELETE FROM ifOutOctets_5 WHERE dtime='2007-02-28 22:38:00' AND id=609 AND counter=15940862962;</div><div> DELETE FROM ifOutOctets_5 WHERE dtime='2007-02-28 22:42:59' AND id=609 AND counter=15126799678;</div><div> DELETE FROM ifOutOctets_5 WHERE dtime='2007-02-28 22:47:59' AND id=609 AND counter=16288752555;</div><div> DELETE FROM ifOutOctets_5 WHERE dtime='2007-02-28 22:52:59' AND id=609 AND counter=14757762970;</div><div> DELETE FROM ifOutOctets_5 WHERE dtime='2007-02-28 22:58:01' AND id=609 AND counter=14268752126;</div><div><br class="webkit-block-placeholder"></div></div><div>I test ran this on one interface and then previewed the changes shown in RTG.</div><div>BEFORE:</div><div><br class="webkit-block-placeholder"></div><div><img height="240" width="574" apple-width="yes" apple-height="yes" src="cid:1E67B0B3-4BB9-4EB3-8022-AB032F0FEDDB@simerson.net"></div><div><br class="webkit-block-placeholder"></div><div><br class="webkit-block-placeholder"></div><div>and AFTER:</div><div><br class="webkit-block-placeholder"></div><div><img height="240" width="574" apple-width="yes" apple-height="yes" src="cid:95B974D4-9A32-4454-88E3-FFF7C58C764B@simerson.net"></div><div><br class="webkit-block-placeholder"></div><div><br class="webkit-block-placeholder"></div><div>As you can see, the transfer amounts are still calculated and shown, but none of the rate info. I read through the PHP code, which calls rtgplot.cgi and then I perused the rtgplot.h and rtgplot.c files trying to figure out what I'd have to alter to get lines to print. I didn't get very far.</div><div><br class="webkit-block-placeholder"></div><div>Anyone got an idea how I might be able to accomplish this? The data consolidation script is one I'd be happy to contribute back to the RTG community if others might find it useful. Unless rtgplot.cgi can be coerced into printing this aggregated data, its usefulness is somewhat limited.</div><br><div apple-content-edited="true"> <span class="Apple-style-span" style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-align: auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0; "><div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><span class="Apple-style-span" style="border-collapse: separate; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; -webkit-text-decorations-in-effect: none; text-indent: 0px; -webkit-text-size-adjust: auto; text-transform: none; orphans: 2; white-space: normal; widows: 2; word-spacing: 0px; "><div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><font class="Apple-style-span" face="Lucida Grande"><span class="Apple-style-span" style="font-family: 'Lucida Grande'; ">Matt Simerson</span></font></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><font class="Apple-style-span" face="Lucida Grande"><span class="Apple-style-span" style="font-family: 'Lucida Grande'; ">Unix Automation Developer</span></font></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><font class="Apple-style-span" face="Lucida Grande"><span class="Apple-style-span" style="font-family: 'Lucida Grande'; ">Email: <a href="mailto:matt@layeredtech.com">matt@layeredtech.com</a></span></font></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><font class="Apple-style-span" face="Lucida Grande"><span class="Apple-style-span" style="font-family: 'Lucida Grande'; ">Phone: 214.564.6085</span></font></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal 'Lucida Grande'; min-height: 15px; font-family: 'Lucida Grande'; "><br style="font-family: 'Lucida Grande'; "></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><font class="Apple-style-span" face="Arial" size="3"><span class="Apple-style-span" style="font-size: 13px; font-family: Arial; "><span class="Apple-style-span" style="font-family: Arial; font-size: 13px; ">Layered Technologies, Inc.</span></span></font></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><font class="Apple-style-span" face="Arial" size="3"><span class="Apple-style-span" style="font-size: 13px; font-family: Arial; "><span class="Apple-style-span" style="font-family: Arial; font-size: 13px; ">On-Demand Utility Computing & Hosting Solutions</span></span></font></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><font class="Apple-style-span" face="Arial" size="3"><span class="Apple-style-span" style="font-size: 13px; font-family: Arial; "><span class="Apple-style-span" style="font-family: Arial; font-size: 13px; ">Learn more>> </span></span></font><a href="http://www.layeredtechnologies.com/"><font class="Apple-style-span" face="Arial" size="3"><span class="Apple-style-span" style="font-size: 13px; color: rgb(0, 0, 238); font-family: Arial; -webkit-text-decorations-in-effect: underline; "><font class="Apple-style-span" color="#0000FF"><span class="Apple-style-span" style="color: rgb(0, 0, 255); font-family: Arial; font-size: 13px; -webkit-text-decorations-in-effect: underline; ">http://www.layeredtech.com</span></font></span></font></a></div></div><br class="Apple-interchange-newline"></span></div></span><br class="Apple-interchange-newline"> </div><br></body></html>