[rtg] MySQL DB help

[Coxen, Jack]

Back in April of 2004 I ran some tests on the server I was running then.
I was looking for a way to speed up both report and chart generation.
The results of my testing can be found here -
http://lists.grdata.com/pipermail/rtg/2004-March/000901.html - but the
upshot of it was that by changing from the standard indexing scheme
(index on dtime) to one using dual-columns indexing (index on id,
dtime), I was able to get around a 700% increase in speed.  However,
this was at the cost of indexes that were about 35% larger.  This was a
good trade for me but YMMV.  

Read through my results see what you think.  And take Brian's comments
about fast disk and lots of memory VERY seriously.  Even with the speed
increases I got from changing my index structure, I got another
incredible boost when I changed servers.  I went from an old single Sun
server not much RAM and an old, slow RAID array to a dual Intel server
setup with maxed RAM and a couple of VERY fast drives setup as RAID 10.
The front-end server handles all the polling and the user interface -
the backend server, connected via a private Gigabit Ethernet link, runs
just the database.  On the old server, my monthly reports took around
3-4 days to run depending on whatever else was going on.  On the new
servers they take about 45 minutes regardless of whatever else is
running.

Good luck,

Jack
 
 


Jack Coxen
Capacity and Planning Engineer
814-260-2705

-----Original Message-----
From: rtg-bounces at lists.grdata.com [mailto:rtg-bounces at lists.grdata.com]
On Behalf Of Brian T. O'Neill
Sent: Tuesday, April 17, 2007 10:37 AM
To: Drew Weaver
Cc: rtg at lists.grdata.com
Subject: Re: [rtg] MySQL DB help

Advice? Fast disk. Lots of memory :)

Memory: 8192M real, 1117M free, 4095M swap free
642 mysql     73  59    0 5205M 4650M sleep 393.1H 26.77% mysqld

$ du -hs rtg2
 1.8T   rtg2

It's still pretty slow for anything over 2 weeks or so on my 6509
switches. MySQL really isn't very good at very large tables. But, you do
need indexes, you need lots of cache set aside for indexes, and you want
to make sure you're using MyISAM (InnoDB gets much slower on large
tables). I had posted awhile ago on settings I had setup on my MySQL
server, dont' have it handy right now. I'll see if I can get somethign
togeather again on it.

But, MySQL does have its limits, thats why I'm in the process of moving
our RTG install to Oracle 10g with index partitioning.

Brian


Quoting Drew Weaver (drew.weaver at thenap.com) from  :
> I would also like tips / experiences on making the database more
> accessible.
> 
> We have something like 2.5 billion rows in our tables now.
> 
> Any advice is helpful ;-)
> 
> -Drew
> 
> -----Original Message-----
> From: rtg-bounces at lists.grdata.com
[mailto:rtg-bounces at lists.grdata.com]
> On Behalf Of Andy Fletcher
> Sent: Tuesday, April 17, 2007 7:04 AM
> To: Patrick Topping
> Cc: rtg at lists.grdata.com
> Subject: Re: [rtg] MySQL DB help
> 
> Sunday, April 15, 2007, 4:35:08 PM, you wrote:
> 
> > I am hoping someone can help me with this request...  I am not a DB
> > person at all and was hoping a fellow RTG user would be able to
assist
> > me in setting up indexing in RTG.
> 
> Search the archives of this list for the definitive answer, but IIRC
> you need to index on the dtime field at least. I've had some success
> combining this with other fields, too.
> 
> If you're not sure how to add indexes, install phpMyAdmin.
> 
> HTH.
> 
> Andy.
> 
> 
> 
> _______________________________________________
> RTG mailing list
> RTG at lists.grdata.com
> http://lists.grdata.com/mailman/listinfo/rtg
> _______________________________________________
> RTG mailing list
> RTG at lists.grdata.com
> http://lists.grdata.com/mailman/listinfo/rtg

-- 
btoneill at misplaced.net

************************************************************************
****
UNIX is simple and coherent, but it takes a genius (or at any rate a 
programmer) to understand and appreciate the simplicity."  - Dennis
Ritchie
************************************************************************
****
_______________________________________________
RTG mailing list
RTG at lists.grdata.com
http://lists.grdata.com/mailman/listinfo/rtg