[rtg] RTG Indexing

[Leech, Jonathan]

We started off with RTG and then created
JRTG(http://jrtg.sourceforge.net/) to address issues we had.
The impact to the database schema and indexing was that it let us
flatten our database schema into something like:

id time ifinoctets ifoutoctets ifindiscards ifinerrors ...
------------------------------------------------------------

which results in many fewer database objects, more efficient indexing,
easier summarization processes (we do hourly and daily), easier database
maintentance including purging old data, and simplified querying to
build summary queries across interfaces and routers.

Also we've built a driver for JRTG to write data to RRD4J (similar to
the RRD files in MRTG). These naturally solve the problem of truncating
and summarizing data. This code will be released to the JRTG project
soon.

Sincerely,
Jonathan Leech
Virtela Communications


-----Original Message-----
From: rtg-bounces at lists.grdata.com [mailto:rtg-bounces at lists.grdata.com]
On Behalf Of Drennan, Stacy L
Sent: Thursday, January 08, 2009 11:59 AM
To: rtg at lists.grdata.com
Subject: Re: [rtg] RTG Indexing

I performed a ton of tests several years ago to find the most efficient
indexing scheme I could for large graphs. What turned out to be the best
was to simply index the entire table:

PRIMARY (dtime, id, counter)

Basically, no query for graphing ever has to hit the original table,
even the values are read out of the index. It's extremely fast, even for
a yearly graph. The tradeoff, of course, is that it more than doubles
the space needed for storage. We burn through terabytes fairly quickly
:)

If speed is extremely important, though, you should give it a test.


Stacy Drennan

CNIC Administrator, Verizon Federal
Navy Marine Corps Intranet (NMCI)
stacy.drennan at verizonbusiness.com


-----Original Message-----

Message: 1
Date: Wed, 7 Jan 2009 19:53:09 -0800
From: Clay Fiske <clay at bloomcounty.org>
Subject: Re: [rtg] rtg status?
To: rtg at lists.grdata.com
Message-ID: <E24EFA32-B6B0-47D8-B500-34B23F689805 at bloomcounty.org>
Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes


On Jan 7, 2009, at 5:44 PM, Bryan Wann wrote:

> On a side note, I'm interested in what other people are doing for 
> table indexing/optimization.  I realize some people may optimize for 
> graphing, others may optimize for something else like billing.
>
> I started out with two KEYs, one based on 'id' and another based on 
> 'dtime'.  Trying to display large number of graphs (i.e. a loaded
> switch) simutaneously started to be unpossible by the time a table had

> 20-30 million rows.  The optimizer was only using the 'id'
> column and then performing a filesort on the rest.  (select blah from 
> ifOctets where id=x and dtime >y1 and dtime <y2 order by dtime).
>
> I decided I should never have more than one row for a given interface 
> and time.  I blew away my two KEYs and replaced them with "UNIQUE KEY 
> (id,dtime)".  This got rid of the filesort and made graphs render an 
> order of magnitude faster.

_______________________________________________
RTG mailing list
RTG at lists.grdata.com
http://lists.grdata.com/mailman/listinfo/rtg