[rtg] rtg status?

[Clay Fiske]

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.

I'm using PgSQL instead of MySQL, but I've found that table  
partitioning on a monthly basis combined with a PgSQL feature called  
constraint exclusion has made graphing performance quite reasonable.  
For large switches, each month's table (per stat) tops out around 1.4  
million rows. Indexes for id,dtime have been doing the job, but I will  
have to see if I can achieve the gains you did by playing with that. I  
build aggregate tables separately so graphing is about the same speed  
as individual ports (for preset aggregates).

-c