Re: VLDB, RAID, and clustering

Was there supposed to be a reply? I think you hit enter too soon.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"craig_amtdatatechnologies@xxxxxxxxxxxxxx" <craigamtdatatechnologiesdiscussionsmi@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:2DD0032A-1403-47F4-9F9E-71D7A6D17CF8@xxxxxxxxxxxxxxxx


"Andrew J. Kelly" wrote:

50GB is by no means a VLDB. It is rather small in today's SQL world. And
clustering for windows and SQL Server is a hardware failover technology
only, not a load sharing one. You can not have data or log files on disks
outside the cluster resource groups. You haven't given any information as
to what the actual bottlenecks are? Is it strictly disk or do you have
memory or CPU issues as well? How much memory do you have and how is it
configured? You really need to determine those before you can come up with
a proper solution. But lets assume it is I/O that is the bottleneck. You
absolutely need to ensure the transaction logs for all user and tempdb
databases are separate from the data files and on a raid 1 or 10. You may
need to segregate the tempdb data files onto their own array as well
depending on usage. A raid 10 is recommended for the data files. The more
disks in the array the better if there is high usage. You are probably
better off having one larger raid 10 array than splitting it into two
smaller ones for each db. Bottom line is that there are so many factors that
can be affecting performance that you can't just assume adding disks will
take care of the issue. You need more details.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"craig_amtdatatechnologies@xxxxxxxxxxxxxx"
<craigamtdatatechnologiesdiscussionsmi@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:CD38193E-6571-4352-BB15-1AED79058D1B@xxxxxxxxxxxxxxxx
> Hi,
>
> The workload of a system I am developing/supporting is about to > increase
> substantially.
>
> The system revolves around one db with many lookup tables built around > a >
> 50Gb table whose workload largely consists of SELECTs againt this, and > a
> users db tracking each hit against the main Db (doing lots of
> SELECT/UPDATE/INSERT statements for each hit).
>
> The users Db is starting to drag system performance. So, to head off a
> future crisis, I'm thinking of recomending a clustered SQL solution, > with
> an
> extrernal disk array setup as as (a) stripped and mirrored array(s).
>
> Basically, I'm looking for some comment on possible disk configurations
> (with another system of comparable size I have had much success with
> splitting indexes, lookups, and data tables across physically and
> logically
> distinct drives).
>
> Internal to the clusters, host O/S, program files, and swap files on > RAID
> 1
> drives (partioned to have swap files on own sensibly sized logical > disks).
>
> External array:-
>
> - TempDb (and misc storage) RAID 1 pair of disks
> - Transaction log on RAID 1 pair of disks
>
> I don't really yet have the detailed knowledge to know which of the
> following 2 options are best (if indeed they make any difference):-
>
> option a - Host user and main db on logically and physically > distinct
> RAID 0 + 1 drives
>
> option b - Have 1 big RAID 0 + 1 drive to host both user and main > db
>
> (I know the larger the number of drives within a particular array , the
> greater the chance of array failure)
>
> I guess the issue lying behind this is:-
>
> If I host main tables, lookup tables, indexes etc. across a stripped
> and
> mirrored array, will the parrallelism this offers render superfluous > the
> idea
> of hosting separate databases (which I don't wish to be in contention) > or
> different components of a database on logically and physically distinct
> disks.
>
>
> Thanks in advance for help, comment, suggestions, and pointers :)
>
>
> Craig
>
>
>
>
>
>
> - N.b. Backups hosted on separate NAS (or at least copied there)
>
>
>
>
>
>
>
>
>
>
>



.

Relevant Pages

  • Re: difference between striping using mdadm and LVM
    ... way!), is "thou shalt make a plan for restoring from backups, test ... another bonus of software raid - if your hardware raid card dies, ... additional backups on - I store my other backups on Iomega REV disks. ... is part of the live array, and both data and parity blocks are ...
    (comp.os.linux.misc)
  • Re: LSI MegaRAID SAS 9240-4i hangs system at boot
    ... TB disks to it. ... Should I first create an empty array with "mdadm --create ... Don't partition the drives before creating your md array. ... RAID arrays containing partitions are bad. ...
    (Debian-User)
  • Re: LSI MegaRAID SAS 9240-4i hangs system at boot
    ... TB disks to it. ... Should I first create an empty array with "mdadm --create ... Don't partition the drives before creating your md array. ... RAID arrays containing partitions are bad. ...
    (Debian-User)
  • Re: Making a RAID array
    ... hard it might be to set up my own RAID array. ... and then set it up to do RAID. ... disks to that, but I'd like to see how easy it is to do it in the Mac ... If I add disks to the mirror set, ...
    (uk.comp.sys.mac)
  • Re: insufficient space in super block for rotational layout
    ... Occurs typically on very high density disks. ... the file system structure cannot ... Is this really an issue with a RAID 5 storage ... It doesn't sound like a problem with the array, but the size of the array. ...
    (comp.unix.solaris)