Re: VLDB, RAID, and clustering
- From: "Andrew J. Kelly" <sqlmvpnooospam@xxxxxxxxxxxx>
- Date: Sun, 30 Sep 2007 15:58:52 -0400
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)
.
- References:
- VLDB, RAID, and clustering
- From: craig_amtdatatechnologies@discussions.mi
- VLDB, RAID, and clustering
- Prev by Date: VLDB, RAID, and clustering
- Previous by thread: VLDB, RAID, and clustering
- Index(es):
Relevant Pages
|
