Re: VLDB, RAID, and clustering



"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: 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)
  • Re: VLDB, RAID, and clustering
    ... And clustering for windows and SQL Server is a hardware failover technology only, ... You can not have data or log files on disks outside the cluster resource groups. ... 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. ...
    (microsoft.public.sqlserver.security)
  • Re: A8N SLI Deluxe Question
    ... > Currently the drives are partitioned and my apps are on one partition. ... Someone suggested using RAID 0. ... The media rates for disks are still in the ~70MB/sec range ... the array reports certain errors. ...
    (alt.comp.periphs.mainboard.asus)
  • Re: Problems spinning down drives in RAID5 array
    ... so I guess this is a problem with the array ... *Assuming that the RAID is handled in software by the kernel's Device ... Wait - and see if the disks *do* spin down. ...
    (comp.os.linux.hardware)