ADSM-L

Re: A little database performance foo

2006-10-21 23:29:22
Subject: Re: A little database performance foo
From: Jason Lee <english AT ANIM.DREAMWORKS DOT COM>
To: ADSM-L AT VM.MARIST DOT EDU
Date: Sat, 21 Oct 2006 18:34:46 -0700
On Oct 21, 2006, at 6:05 PM, Allen S. Rout wrote:

On Sat, 21 Oct 2006 14:00:06 -0700, Jason Lee
<english AT ANIM.DREAMWORKS DOT COM> said:


I have N clients all starting at the same time. They all are going
to request ~100MB of data (or at least that is the number being
reported by q session as bytes sent when they have been running for
a while).  The clients are running on very lightly loaded boxes and
can consume anything the server can throw at them (or anything it's
likely to throw, anyway) from the database.

Well, the party-line answer to that problem is that you don't want to
start them at anything like 'the same time'.  This is kind of skew to
your actual question, but may help your operational problem.  If you
set the DURation of your schedules to be somewhat higher, then (since
actual start times are scattered over the first half of that duration)
your contention will go down.

:-)  that was more to illustrate the point. After a little while the
start times become pretty random, since each backup job runs after
the previous (but I have ~40 queues of jobs)


Also or alternately, you may want to run some (most?) of your clients
INCRBYDATE many days of the week. This would reduce the impact of the
initial download at the cost of some DB inflation and an opportunity
to miss, for a while, files which are new but have old dates.
(unpacked archives are the easiest example of such).


I've been thinking about that as a stopgap until I get things more
under control.


If this is the same server that has a 500G DB, then that may explain
why you're seeing this performance problem.  That's broadly considered
"Freakin' HUGE", and most folks find some other portion of their DB
performance to become unacceptable long before they get that big.  How
long does it take you to do a DB backup, anyway?

Several hours for a full... it's watching the DB restores that is
killer :-)


For ~350GB of DB, I've got 11 servers, 9 customer-facing, on one box.
Now, I'm using relatively sucky old hardware (18G 10K SSA drives), but
that means I've got 9 threads wandering around a smaller DB
displacement.

That's where I'm at... gotta break this bad boy up. I knew the day
was coming, but it was just recently that I suddenly started to get
way behind - it's a vicious cycle, the more behind you get, the more
behind you get!

I was hoping that I had my box setup incorrectly, but it appears that
in reality I have a "well performing database" - scary.

I see another late night or two coming up :-)


Jason




--
Jason Lee
DreamWorks Animation
(818) 695-3782

<Prev in Thread] Current Thread [Next in Thread>