[AccessD] Needs analysis

Always, always, always, the biggest bang for you buck is memory.

I would not call myself an SQL expert, but I believe SQL will consume as
much memory as it can to execute things.  I don't think there is an upper
limit to that outside of what the system can address.

Jim. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Monday, June 21, 2010 2:28 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Needs analysis

LOL, yep one terabyte, not one gigabyte.

As for 10, I got it.  Span to do the RAID 0, then mirror to do the RAID 1.
So that is 2X.

I went back in and looked at what I actually have.  I have eight 1 terabyte
drives.  I use two of 
them Raid 1.  That leaves six which I use to create a single RAID 6 array.
That leaves four 
terabytes of data storage.  Due to the limitations of Windows, I break that
into two 2 terabyte 
volumes.

 > You said you had a budget for upgrading your machine.  John, I'm serious,
when reading and 
writing from a database, your absolute most limiting factor is going to be
drive speed!

Of course this is true.  However there is more to the story.  For example


Imagine I have a database.  The whole thing is only 500 megs of data indexes
and all.  I have 16 
gigs of ram.  Yes, the slowest thing is the disk but... SQL Server loads the
whole damn thing into 
ram and then executes ten thousand queries pulling this that and the other,
all "instantly".

Next scenario, imagine same server, but the database is 40 gigs.  Now only a
piece can be loaded at 
a time.

You get the point.  At what point does it pay to go to more memory vs the
disk thing?  If I invest 
heavily in memory, now I have the whole thing loaded in memory but I have 47
potential threads but 
only 3 available cores.  At what point does it pay to invest in a ton of
cores?

Drew, I understand the disk thing, and I know that I absolutely am hitting
an I/O bottleneck. 
However it seems not useful to just say "get faster disks".  I know that
needs to happen but there 
is much much more to this than "more disks".  Understand that these
databases are freakin huge. 
Just reading the data off a raid 0 or 10 will still take a ton of time if
the disks in the raid are 
magnetic.

OTOH I do not write back to these big databases.  During processing they are
literally read-only. 
And much of the time I hit two specific databases, and then write a "small"
(few thousand to few 
million) record order table.  My plan (in the absence of explicit bottleneck
data) is to go get some 
SSDs to hold those two databases, probably two 60 gig SSDs for each
database, raid 0 (120 gig total 
per database).

That effectively does what you are discussing, but just for my two most
frequently used databases. 
After that, providing a processor / memory pipe that can handle the flood of
data will still be a 
requirement.

John W. Colby
www.ColbyConsulting.com


Drew Wutka wrote:
> Ok, a little fuzzy about your math here.
> 
> First of all, a RAID 1+0 (RAID 10) is actually 2x used for x storage
> space.  It's a striped mirror set, so you use up 2 drives for each
> mirror set, and then stripe those.
> 
> Second, 1 gig drives...really?  That's VERY small by today's standards!
> Hopefully you meant terabyte.
> 
> How much space do you actually need?  From what you are saying, it looks
> like you have 16 drives. (2 sets of 6 disk RAID 6, (which is 12 drives
> as you said), plus a 2 drive striped set, plus a 2 drive mirrored set).
> The way you SHOULD have this setup is with a 2 drive mirror, and a 14
> drive RAID 1+0.  That first mirrored set should be your operating
> system.  Put NOTHING else on there.  Then install everything else to the
> RAID 1+0.  
> 
> You said you had a budget for upgrading your machine.  John, I'm
> serious, when reading and writing from a database, your absolute most
> limiting factor is going to be drive speed!  
> 
> So what kinda storage space do you need, and how big are your drives?
> (Again, I'm assuming your way off on the 1 gig part).  I guess you could
> be getting very small solid state drives, but those aren't idea for a
> database either.  If you went with fairly cheap drives (260 gigs are
> like $35 range, depending on the interface), for $560, you could have a
> 260 gig root drive for your OS, and then 1.8 terrabyte data drive.
> 
> Drew

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com