Dear gurus,
We wish a good migration, faster and the less traumatic possible. So that
after five years with the same logical configuration, hardware and so on the
main point is to improve considerably the availability and drop once for all
our bottlenecks.
We’ve got an A-A cluster running with W2000k and Sql2000k. There are
approximately 50 databases and on paper and thinking in the upcoming change
s
we’ve created three logical groups according five variables: criticity,
usage, size, growing, volatility data
A: Critical
B: Medium usage
C: Historical data or database with little load.
Well, currently LDF are living along with NDF in the same volume.
Idea is moving to a SAN (fitted with faster disks) and isolating of course
all those LDF in separated volumes.
Probably there will be one controller with four channels and one though at
the moment is having in one channel the group A and the second channel group
B. Third channel will be reserved for LOG and the last one for the group C.
I don’t know if I my explanations has not been very clear. Let me know if
I
can provide you more relevant information.
How many thing must I take into account in this tricky migration?
Does anyone has ever experienced with former and similar plan?
Thanks in advance for any input or comment.
Regards,Hi Enric,
Seperating log files is a good starting point, also if you have high tempdb
usage consider to separate temp db as well. Also Raid 1 for log files and
Raid 10 (Raid 1 + 0) if possible if not Raid 5 is for Data files are ideal.
If you want to spread IO across your disks to prevent bottlenecks so LUN
configuration is very importnant and it really depends on your SAN
configuration. But we ususally use two methods:
1) Analyze data files usage using fn_virtualfilestats (see BOL for details)
and spread the data files across different LUNs, controllers etc. by moving
them (You have to analyze for all 50 db's you have.)
2) Creating the LUNS across multiple arrays to spread disk IO
As you can see it really depends on your SAN configuration and also your db
usage. You have to analyze well in the beginning becouse it's hard to change
to configuration later. And it really depends on your priorities
(performance, isolation of env., availability etc..)
And i also recomment you to upgrade Windows 2003 OS as well.
Regards.
"Enric" wrote:
> Dear gurus,
> We wish a good migration, faster and the less traumatic possible. So that
> after five years with the same logical configuration, hardware and so on t
he
> main point is to improve considerably the availability and drop once for a
ll
> our bottlenecks.
> We’ve got an A-A cluster running with W2000k and Sql2000k. There are
> approximately 50 databases and on paper and thinking in the upcoming chan
ges
> we’ve created three logical groups according five variables: criticity,
> usage, size, growing, volatility data
> A: Critical
> B: Medium usage
> C: Historical data or database with little load.
> Well, currently LDF are living along with NDF in the same volume.
> Idea is moving to a SAN (fitted with faster disks) and isolating of course
> all those LDF in separated volumes.
> Probably there will be one controller with four channels and one though at
> the moment is having in one channel the group A and the second channel gro
up
> B. Third channel will be reserved for LOG and the last one for the group C
.
> I don’t know if I my explanations has not been very clear. Let me know i
f I
> can provide you more relevant information.
> How many thing must I take into account in this tricky migration?
> Does anyone has ever experienced with former and similar plan?
> Thanks in advance for any input or comment.
> Regards,
>|||http://www.microsoft.com/technet/pr...ver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:A763B6DC-6130-43F0-B153-78CC5301E8CC@.microsoft.com...
> Dear gurus,
> We wish a good migration, faster and the less traumatic possible. So that
> after five years with the same logical configuration, hardware and so on t
he
> main point is to improve considerably the availability and drop once for a
ll
> our bottlenecks.
> We’ve got an A-A cluster running with W2000k and Sql2000k. There are
> approximately 50 databases and on paper and thinking in the upcoming chan
ges
> we’ve created three logical groups according five variables: criticity,
> usage, size, growing, volatility data
> A: Critical
> B: Medium usage
> C: Historical data or database with little load.
> Well, currently LDF are living along with NDF in the same volume.
> Idea is moving to a SAN (fitted with faster disks) and isolating of course
> all those LDF in separated volumes.
> Probably there will be one controller with four channels and one though at
> the moment is having in one channel the group A and the second channel gro
up
> B. Third channel will be reserved for LOG and the last one for the group C
.
> I don’t know if I my explanations has not been very clear. Let me know i
f I
> can provide you more relevant information.
> How many thing must I take into account in this tricky migration?
> Does anyone has ever experienced with former and similar plan?
> Thanks in advance for any input or comment.
> Regards,
>|||Thanks a lot for your comments,
"Umut Nazlica" wrote:
> Hi Enric,
> Seperating log files is a good starting point, also if you have high tempd
b
> usage consider to separate temp db as well. Also Raid 1 for log files and
> Raid 10 (Raid 1 + 0) if possible if not Raid 5 is for Data files are ideal
.
> If you want to spread IO across your disks to prevent bottlenecks so LUN
> configuration is very importnant and it really depends on your SAN
> configuration. But we ususally use two methods:
> 1) Analyze data files usage using fn_virtualfilestats (see BOL for details
)
> and spread the data files across different LUNs, controllers etc. by movin
g
> them (You have to analyze for all 50 db's you have.)
> 2) Creating the LUNS across multiple arrays to spread disk IO
> As you can see it really depends on your SAN configuration and also your d
b
> usage. You have to analyze well in the beginning becouse it's hard to chan
ge
> to configuration later. And it really depends on your priorities
> (performance, isolation of env., availability etc..)
> And i also recomment you to upgrade Windows 2003 OS as well.
> Regards.
> "Enric" wrote:
>
Showing posts with label hardware. Show all posts
Showing posts with label hardware. Show all posts
Sunday, February 19, 2012
Monday, February 13, 2012
About how many writes per sec for a single drive?
Deos anybody have a rough estimate for how many writes per second a
single drive system (hardware mirrored to a second drive) should be
able to handle? (ie: Win2000, IIS, apps, SQL, db + log files -- all on
one drive)
I know that's a slippery question, but I'm just looking for an order
of magnitude... Should I expect to max out at about 1-5 writes/sec?
20-50? 100-200?
On the other hand, if I were to bump that up to a RAID-10 with 3
stripped drives, mirrored to a second set of three -- in that case,
about how many writes per second should I expect?
I'm trying to build a relatively inexpensive box to do the job,
without going crazy and over-engineering it. Even a rough sense of
writes/sec should help me better understand what sort of disk system I
might need...
Many Thanks,
Scott> I know that's a slippery question, but I'm just looking for an order
> of magnitude... Should I expect to max out at about 1-5 writes/sec?
> 20-50? 100-200?
The hardware drives themselves can do in the order 100 write/second.
Depends a bit on how near the writes are. Most drives nowadays
have a 'seek' time around 8 milliseconds. So if the drive has to
do a 'seek' for each write and one complete rotation (depending
on the structure it could be less or more). A rotation (7200 pm =around 8 millisecond for one rotation) is also 8 milli's this would
be 16 milli's for a write. This comes to 62 writes a second. If
the drive is a bit intelligent and the writes occure close to eachother
or are sorted by the drive 100 writes per second is a fair number.
For raid devices were 3 disks can be used at the same time.
the number of 'small' writes can be 3 times as high as a single
disk. (Using 3 disks). (But only if data is needed from the 3 different
devices). This is seldom the case.
Cheap raid solutions can not handle reads or writes at the
same time on different positions on the three disks. With cheap
read devices you only win when accessing large chunks.
If accesses are short, raid wil perform better than a single drive,
but this is often expressed in a percentage and not in a factor.
This report is produced from memory, and as we all know memory
is not build to keep data accurately but to survive. So reading my
numbers this should be taken into account.
ben brugman
> On the other hand, if I were to bump that up to a RAID-10 with 3
> stripped drives, mirrored to a second set of three -- in that case,
> about how many writes per second should I expect?
> I'm trying to build a relatively inexpensive box to do the job,
> without going crazy and over-engineering it. Even a rough sense of
> writes/sec should help me better understand what sort of disk system I
> might need...
> Many Thanks,
> Scott|||There is a great book on I/O performance, its called SQL
Server 2000 performance tuning from MS Press by Edward
Whalen, et al. -- ISBN 0-7356-1270-6. Go to page 73 and
it discusses all that you are asking about. For a general
ball park answer on a RAID 10, you should track the
following with System Monitor:
Disk Reads/sec
Disk Writes/sec
Avg. Disk Queue Length
Avg. Disk Sec/Read
Avg. Disk Sec/Write
I/Os per disk = [Reads + (2 * Writes)]/Number of Disks
For example, a 9GB/10,000 RPM disk should have the
capacity of 100 I/Os per sond for random I/O. Your milage
may vary depending on you disk subsystem.
Later,
Chris
>--Original Message--
>Deos anybody have a rough estimate for how many writes
per second a
>single drive system (hardware mirrored to a second drive)
should be
>able to handle? (ie: Win2000, IIS, apps, SQL, db + log
files -- all on
>one drive)
>I know that's a slippery question, but I'm just looking
for an order
>of magnitude... Should I expect to max out at about 1-5
writes/sec?
>20-50? 100-200?
>On the other hand, if I were to bump that up to a RAID-10
with 3
>stripped drives, mirrored to a second set of three -- in
that case,
>about how many writes per second should I expect?
>I'm trying to build a relatively inexpensive box to do
the job,
>without going crazy and over-engineering it. Even a rough
sense of
>writes/sec should help me better understand what sort of
disk system I
>might need...
>Many Thanks,
>Scott
>.
>|||An excellent article here.
http://research.microsoft.com/research/pubs/view.aspx?
tr_id=686
>--Original Message--
>Deos anybody have a rough estimate for how many writes
per second a
>single drive system (hardware mirrored to a second drive)
should be
>able to handle? (ie: Win2000, IIS, apps, SQL, db + log
files -- all on
>one drive)
>I know that's a slippery question, but I'm just looking
for an order
>of magnitude... Should I expect to max out at about 1-5
writes/sec?
>20-50? 100-200?
>On the other hand, if I were to bump that up to a RAID-10
with 3
>stripped drives, mirrored to a second set of three -- in
that case,
>about how many writes per second should I expect?
>I'm trying to build a relatively inexpensive box to do
the job,
>without going crazy and over-engineering it. Even a rough
sense of
>writes/sec should help me better understand what sort of
disk system I
>might need...
>Many Thanks,
>Scott
>.
>|||Hi
If you want to read up on this sort of thing then Microsoft SQL Server 2000
Performance Tuning Technical Referencby Whelan, Garcia, DeLuca and Thompson
ISBN 0-7356-1270-6 is worth reading.
The actual numbers will be dependent on the drives specifications. The
examples quoted in the above book work out at about 125 I/Os per second, but
reading and writing would both have to be considered for as system (assuming
that data is retrieved) therefore the distribution of these will effect the
overall figures. This factor will be the same for both RAID1 and RAID10, but
the number of discs will reduce this value proportionaly for RAID10.
John
"Scott Matthews" <scott@.turnstyle.com> wrote in message
news:f11b846.0311280630.78dd010c@.posting.google.com...
> Deos anybody have a rough estimate for how many writes per second a
> single drive system (hardware mirrored to a second drive) should be
> able to handle? (ie: Win2000, IIS, apps, SQL, db + log files -- all on
> one drive)
> I know that's a slippery question, but I'm just looking for an order
> of magnitude... Should I expect to max out at about 1-5 writes/sec?
> 20-50? 100-200?
> On the other hand, if I were to bump that up to a RAID-10 with 3
> stripped drives, mirrored to a second set of three -- in that case,
> about how many writes per second should I expect?
> I'm trying to build a relatively inexpensive box to do the job,
> without going crazy and over-engineering it. Even a rough sense of
> writes/sec should help me better understand what sort of disk system I
> might need...
> Many Thanks,
> Scott
single drive system (hardware mirrored to a second drive) should be
able to handle? (ie: Win2000, IIS, apps, SQL, db + log files -- all on
one drive)
I know that's a slippery question, but I'm just looking for an order
of magnitude... Should I expect to max out at about 1-5 writes/sec?
20-50? 100-200?
On the other hand, if I were to bump that up to a RAID-10 with 3
stripped drives, mirrored to a second set of three -- in that case,
about how many writes per second should I expect?
I'm trying to build a relatively inexpensive box to do the job,
without going crazy and over-engineering it. Even a rough sense of
writes/sec should help me better understand what sort of disk system I
might need...
Many Thanks,
Scott> I know that's a slippery question, but I'm just looking for an order
> of magnitude... Should I expect to max out at about 1-5 writes/sec?
> 20-50? 100-200?
The hardware drives themselves can do in the order 100 write/second.
Depends a bit on how near the writes are. Most drives nowadays
have a 'seek' time around 8 milliseconds. So if the drive has to
do a 'seek' for each write and one complete rotation (depending
on the structure it could be less or more). A rotation (7200 pm =around 8 millisecond for one rotation) is also 8 milli's this would
be 16 milli's for a write. This comes to 62 writes a second. If
the drive is a bit intelligent and the writes occure close to eachother
or are sorted by the drive 100 writes per second is a fair number.
For raid devices were 3 disks can be used at the same time.
the number of 'small' writes can be 3 times as high as a single
disk. (Using 3 disks). (But only if data is needed from the 3 different
devices). This is seldom the case.
Cheap raid solutions can not handle reads or writes at the
same time on different positions on the three disks. With cheap
read devices you only win when accessing large chunks.
If accesses are short, raid wil perform better than a single drive,
but this is often expressed in a percentage and not in a factor.
This report is produced from memory, and as we all know memory
is not build to keep data accurately but to survive. So reading my
numbers this should be taken into account.
ben brugman
> On the other hand, if I were to bump that up to a RAID-10 with 3
> stripped drives, mirrored to a second set of three -- in that case,
> about how many writes per second should I expect?
> I'm trying to build a relatively inexpensive box to do the job,
> without going crazy and over-engineering it. Even a rough sense of
> writes/sec should help me better understand what sort of disk system I
> might need...
> Many Thanks,
> Scott|||There is a great book on I/O performance, its called SQL
Server 2000 performance tuning from MS Press by Edward
Whalen, et al. -- ISBN 0-7356-1270-6. Go to page 73 and
it discusses all that you are asking about. For a general
ball park answer on a RAID 10, you should track the
following with System Monitor:
Disk Reads/sec
Disk Writes/sec
Avg. Disk Queue Length
Avg. Disk Sec/Read
Avg. Disk Sec/Write
I/Os per disk = [Reads + (2 * Writes)]/Number of Disks
For example, a 9GB/10,000 RPM disk should have the
capacity of 100 I/Os per sond for random I/O. Your milage
may vary depending on you disk subsystem.
Later,
Chris
>--Original Message--
>Deos anybody have a rough estimate for how many writes
per second a
>single drive system (hardware mirrored to a second drive)
should be
>able to handle? (ie: Win2000, IIS, apps, SQL, db + log
files -- all on
>one drive)
>I know that's a slippery question, but I'm just looking
for an order
>of magnitude... Should I expect to max out at about 1-5
writes/sec?
>20-50? 100-200?
>On the other hand, if I were to bump that up to a RAID-10
with 3
>stripped drives, mirrored to a second set of three -- in
that case,
>about how many writes per second should I expect?
>I'm trying to build a relatively inexpensive box to do
the job,
>without going crazy and over-engineering it. Even a rough
sense of
>writes/sec should help me better understand what sort of
disk system I
>might need...
>Many Thanks,
>Scott
>.
>|||An excellent article here.
http://research.microsoft.com/research/pubs/view.aspx?
tr_id=686
>--Original Message--
>Deos anybody have a rough estimate for how many writes
per second a
>single drive system (hardware mirrored to a second drive)
should be
>able to handle? (ie: Win2000, IIS, apps, SQL, db + log
files -- all on
>one drive)
>I know that's a slippery question, but I'm just looking
for an order
>of magnitude... Should I expect to max out at about 1-5
writes/sec?
>20-50? 100-200?
>On the other hand, if I were to bump that up to a RAID-10
with 3
>stripped drives, mirrored to a second set of three -- in
that case,
>about how many writes per second should I expect?
>I'm trying to build a relatively inexpensive box to do
the job,
>without going crazy and over-engineering it. Even a rough
sense of
>writes/sec should help me better understand what sort of
disk system I
>might need...
>Many Thanks,
>Scott
>.
>|||Hi
If you want to read up on this sort of thing then Microsoft SQL Server 2000
Performance Tuning Technical Referencby Whelan, Garcia, DeLuca and Thompson
ISBN 0-7356-1270-6 is worth reading.
The actual numbers will be dependent on the drives specifications. The
examples quoted in the above book work out at about 125 I/Os per second, but
reading and writing would both have to be considered for as system (assuming
that data is retrieved) therefore the distribution of these will effect the
overall figures. This factor will be the same for both RAID1 and RAID10, but
the number of discs will reduce this value proportionaly for RAID10.
John
"Scott Matthews" <scott@.turnstyle.com> wrote in message
news:f11b846.0311280630.78dd010c@.posting.google.com...
> Deos anybody have a rough estimate for how many writes per second a
> single drive system (hardware mirrored to a second drive) should be
> able to handle? (ie: Win2000, IIS, apps, SQL, db + log files -- all on
> one drive)
> I know that's a slippery question, but I'm just looking for an order
> of magnitude... Should I expect to max out at about 1-5 writes/sec?
> 20-50? 100-200?
> On the other hand, if I were to bump that up to a RAID-10 with 3
> stripped drives, mirrored to a second set of three -- in that case,
> about how many writes per second should I expect?
> I'm trying to build a relatively inexpensive box to do the job,
> without going crazy and over-engineering it. Even a rough sense of
> writes/sec should help me better understand what sort of disk system I
> might need...
> Many Thanks,
> Scott
Subscribe to:
Posts (Atom)