[rtg] RTG Indexing

[Drennan, Stacy L]

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.