Sample db problem: sata

Showing posts with label sata. Show all posts

Friday, March 9, 2012

SATA RAID 5 Performance

Hi all,
I'm in the process of researching a fast disk array for our new database
server.
I've been looking at SATA RAID 5. I found a review on Tom's Hardware:
http://www20.tomshardware.com/storage/20040831/sata-raid-controller-18.html
A 12 Disk RAID 5 array is only able to handle 6MB/S?! Is that number
correct... I belive they're using IOMeter.
Assuming I have 6 - 12 fast disks, what type of performance should I expect
from a RAID 5 array? Are there any RAID vendors I should look at in
particular?
Thanks.
--
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/Lucas Tam wrote:
> Hi all,
> I'm in the process of researching a fast disk array for our new
> database server.
> I've been looking at SATA RAID 5. I found a review on Tom's Hardware:
>
http://www20.tomshardware.com/storage/20040831/sata-raid-controller-18.html
> A 12 Disk RAID 5 array is only able to handle 6MB/S?! Is that number
> correct... I belive they're using IOMeter.
> Assuming I have 6 - 12 fast disks, what type of performance should I
> expect from a RAID 5 array? Are there any RAID vendors I should look
> at in particular?
> Thanks.
RAID 5 has the worst write performance of all RAID options. I don't know
what figures you read or how they compare to other ATA or SATA
implementations, but you should know that if your databases are highly
transactional, then a RAID 5 solution may not be the best choice. You
may want to purchase a RAID1+0 solution. Even so, you'll likely want
your OS, Log files, and tempdb on mirrored drives because of their
increased write performance.
In my experience most RAID 5 arrays have far less than 12 disks. Most I
have seen use 5 per array.
David G.|||RAID5 certainly isn't designed to perform write operations as fast as
RAID1+0, but then again writes aren't written transactionally to disk on
data volumes (lazy writes) which often mitigates this significantly,
assuming there is a relatively large amount of memory available. RAID5 is a
poor choice for Log files, but not always a bad choice for Data files. Sure,
if you can throw half of your disks at mirroring, RAID1+0 is the way to go,
but RAID5 often cuts the mustard for many small to mid range transaction
processing systems.
Regards,
Greg Linwood
SQL Server MVP
"David G." <david_nospam@.nospam.com> wrote in message
news:OK0WIS6jEHA.3724@.TK2MSFTNGP11.phx.gbl...
> Lucas Tam wrote:
>> Hi all,
>> I'm in the process of researching a fast disk array for our new
>> database server.
>> I've been looking at SATA RAID 5. I found a review on Tom's Hardware:
>>
> http://www20.tomshardware.com/storage/20040831/sata-raid-controller-18.html
>> A 12 Disk RAID 5 array is only able to handle 6MB/S?! Is that number
>> correct... I belive they're using IOMeter.
>> Assuming I have 6 - 12 fast disks, what type of performance should I
>> expect from a RAID 5 array? Are there any RAID vendors I should look
>> at in particular?
>> Thanks.
> RAID 5 has the worst write performance of all RAID options. I don't know
> what figures you read or how they compare to other ATA or SATA
> implementations, but you should know that if your databases are highly
> transactional, then a RAID 5 solution may not be the best choice. You
> may want to purchase a RAID1+0 solution. Even so, you'll likely want
> your OS, Log files, and tempdb on mirrored drives because of their
> increased write performance.
> In my experience most RAID 5 arrays have far less than 12 disks. Most I
> have seen use 5 per array.
>
> --
> David G.
>|||See ftp://ftp.research.microsoft.com/pub/tr/tr-2003-70.pdf for some detailed
SATA disk test results.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2004 All rights reserved.
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:eLYskcBkEHA.704@.TK2MSFTNGP12.phx.gbl...
> RAID5 certainly isn't designed to perform write operations as fast as
> RAID1+0, but then again writes aren't written transactionally to disk on
> data volumes (lazy writes) which often mitigates this significantly,
> assuming there is a relatively large amount of memory available. RAID5 is
> a poor choice for Log files, but not always a bad choice for Data files.
> Sure, if you can throw half of your disks at mirroring, RAID1+0 is the way
> to go, but RAID5 often cuts the mustard for many small to mid range
> transaction processing systems.
> Regards,
> Greg Linwood
> SQL Server MVP
> "David G." <david_nospam@.nospam.com> wrote in message
> news:OK0WIS6jEHA.3724@.TK2MSFTNGP11.phx.gbl...
>> Lucas Tam wrote:
>> Hi all,
>> I'm in the process of researching a fast disk array for our new
>> database server.
>> I've been looking at SATA RAID 5. I found a review on Tom's Hardware:
>>
>> http://www20.tomshardware.com/storage/20040831/sata-raid-controller-18.html
>> A 12 Disk RAID 5 array is only able to handle 6MB/S?! Is that number
>> correct... I belive they're using IOMeter.
>> Assuming I have 6 - 12 fast disks, what type of performance should I
>> expect from a RAID 5 array? Are there any RAID vendors I should look
>> at in particular?
>> Thanks.
>> RAID 5 has the worst write performance of all RAID options. I don't know
>> what figures you read or how they compare to other ATA or SATA
>> implementations, but you should know that if your databases are highly
>> transactional, then a RAID 5 solution may not be the best choice. You
>> may want to purchase a RAID1+0 solution. Even so, you'll likely want
>> your OS, Log files, and tempdb on mirrored drives because of their
>> increased write performance.
>> In my experience most RAID 5 arrays have far less than 12 disks. Most I
>> have seen use 5 per array.
>>
>> --
>> David G.
>

SATA RAID 5 Performance

Hi all,
I'm in the process of researching a fast disk array for our new database
server.
I've been looking at SATA RAID 5. I found a review on Tom's Hardware:
http://www20.tomshardware.com/stora...troller-18.html
A 12 Disk RAID 5 array is only able to handle 6MB/S?! Is that number
correct... I belive they're using IOMeter.
Assuming I have 6 - 12 fast disks, what type of performance should I expect
from a RAID 5 array? Are there any RAID vendors I should look at in
particular?
Thanks.
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/Lucas Tam wrote:
> Hi all,
> I'm in the process of researching a fast disk array for our new
> database server.
> I've been looking at SATA RAID 5. I found a review on Tom's Hardware:
>
http://www20.tomshardware.com/stora...troller-18.html
> A 12 Disk RAID 5 array is only able to handle 6MB/S?! Is that number
> correct... I belive they're using IOMeter.
> Assuming I have 6 - 12 fast disks, what type of performance should I
> expect from a RAID 5 array? Are there any RAID vendors I should look
> at in particular?
> Thanks.
RAID 5 has the worst write performance of all RAID options. I don't know
what figures you read or how they compare to other ATA or SATA
implementations, but you should know that if your databases are highly
transactional, then a RAID 5 solution may not be the best choice. You
may want to purchase a RAID1+0 solution. Even so, you'll likely want
your OS, Log files, and tempdb on mirrored drives because of their
increased write performance.
In my experience most RAID 5 arrays have far less than 12 disks. Most I
have seen use 5 per array.
David G.|||RAID5 certainly isn't designed to perform write operations as fast as
RAID1+0, but then again writes aren't written transactionally to disk on
data volumes (lazy writes) which often mitigates this significantly,
assuming there is a relatively large amount of memory available. RAID5 is a
poor choice for Log files, but not always a bad choice for Data files. Sure,
if you can throw half of your disks at mirroring, RAID1+0 is the way to go,
but RAID5 often cuts the mustard for many small to mid range transaction
processing systems.
Regards,
Greg Linwood
SQL Server MVP
"David G." <david_nospam@.nospam.com> wrote in message
news:OK0WIS6jEHA.3724@.TK2MSFTNGP11.phx.gbl...
> Lucas Tam wrote:
> [url]http://www20.tomshardware.com/storage/20040831/sata-raid-controller-18.html[/url
]
> RAID 5 has the worst write performance of all RAID options. I don't know
> what figures you read or how they compare to other ATA or SATA
> implementations, but you should know that if your databases are highly
> transactional, then a RAID 5 solution may not be the best choice. You
> may want to purchase a RAID1+0 solution. Even so, you'll likely want
> your OS, Log files, and tempdb on mirrored drives because of their
> increased write performance.
> In my experience most RAID 5 arrays have far less than 12 disks. Most I
> have seen use 5 per array.
>
> --
> David G.
>|||See ftp://ftp.research.microsoft.com/pub/tr/tr-2003-70.pdf for some detailed
SATA disk test results.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:eLYskcBkEHA.704@.TK2MSFTNGP12.phx.gbl...
> RAID5 certainly isn't designed to perform write operations as fast as
> RAID1+0, but then again writes aren't written transactionally to disk on
> data volumes (lazy writes) which often mitigates this significantly,
> assuming there is a relatively large amount of memory available. RAID5 is
> a poor choice for Log files, but not always a bad choice for Data files.
> Sure, if you can throw half of your disks at mirroring, RAID1+0 is the way
> to go, but RAID5 often cuts the mustard for many small to mid range
> transaction processing systems.
> Regards,
> Greg Linwood
> SQL Server MVP
> "David G." <david_nospam@.nospam.com> wrote in message
> news:OK0WIS6jEHA.3724@.TK2MSFTNGP11.phx.gbl...
>

SATA RAID 5 Performance

Hi all,
I'm in the process of researching a fast disk array for our new database
server.
I've been looking at SATA RAID 5. I found a review on Tom's Hardware:
http://www20.tomshardware.com/storag...roller-18.html
A 12 Disk RAID 5 array is only able to handle 6MB/S?! Is that number
correct... I belive they're using IOMeter.
Assuming I have 6 - 12 fast disks, what type of performance should I expect
from a RAID 5 array? Are there any RAID vendors I should look at in
particular?
Thanks.
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/
Lucas Tam wrote:
> Hi all,
> I'm in the process of researching a fast disk array for our new
> database server.
> I've been looking at SATA RAID 5. I found a review on Tom's Hardware:
>
http://www20.tomshardware.com/storag...roller-18.html
> A 12 Disk RAID 5 array is only able to handle 6MB/S?! Is that number
> correct... I belive they're using IOMeter.
> Assuming I have 6 - 12 fast disks, what type of performance should I
> expect from a RAID 5 array? Are there any RAID vendors I should look
> at in particular?
> Thanks.
RAID 5 has the worst write performance of all RAID options. I don't know
what figures you read or how they compare to other ATA or SATA
implementations, but you should know that if your databases are highly
transactional, then a RAID 5 solution may not be the best choice. You
may want to purchase a RAID1+0 solution. Even so, you'll likely want
your OS, Log files, and tempdb on mirrored drives because of their
increased write performance.
In my experience most RAID 5 arrays have far less than 12 disks. Most I
have seen use 5 per array.
David G.
|||RAID5 certainly isn't designed to perform write operations as fast as
RAID1+0, but then again writes aren't written transactionally to disk on
data volumes (lazy writes) which often mitigates this significantly,
assuming there is a relatively large amount of memory available. RAID5 is a
poor choice for Log files, but not always a bad choice for Data files. Sure,
if you can throw half of your disks at mirroring, RAID1+0 is the way to go,
but RAID5 often cuts the mustard for many small to mid range transaction
processing systems.
Regards,
Greg Linwood
SQL Server MVP
"David G." <david_nospam@.nospam.com> wrote in message
news:OK0WIS6jEHA.3724@.TK2MSFTNGP11.phx.gbl...
> Lucas Tam wrote:
> http://www20.tomshardware.com/storag...roller-18.html
> RAID 5 has the worst write performance of all RAID options. I don't know
> what figures you read or how they compare to other ATA or SATA
> implementations, but you should know that if your databases are highly
> transactional, then a RAID 5 solution may not be the best choice. You
> may want to purchase a RAID1+0 solution. Even so, you'll likely want
> your OS, Log files, and tempdb on mirrored drives because of their
> increased write performance.
> In my experience most RAID 5 arrays have far less than 12 disks. Most I
> have seen use 5 per array.
>
> --
> David G.
>
|||See ftp://ftp.research.microsoft.com/pub/tr/tr-2003-70.pdf for some detailed
SATA disk test results.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:eLYskcBkEHA.704@.TK2MSFTNGP12.phx.gbl...
> RAID5 certainly isn't designed to perform write operations as fast as
> RAID1+0, but then again writes aren't written transactionally to disk on
> data volumes (lazy writes) which often mitigates this significantly,
> assuming there is a relatively large amount of memory available. RAID5 is
> a poor choice for Log files, but not always a bad choice for Data files.
> Sure, if you can throw half of your disks at mirroring, RAID1+0 is the way
> to go, but RAID5 often cuts the mustard for many small to mid range
> transaction processing systems.
> Regards,
> Greg Linwood
> SQL Server MVP
> "David G." <david_nospam@.nospam.com> wrote in message
> news:OK0WIS6jEHA.3724@.TK2MSFTNGP11.phx.gbl...
>

SATA for SQL Server data

Hi all,
Currently we have database system that logs readings from sensors at an
interval from 5 minutes to 1 hour (more on the 5 minute side), from which
customers can view statistical data, graphs, etc.
For storage, we currently connect to a fiber-channel SAN with 150GB 15k
drives. As you would expect, the amounts of data for this system is getting
large enough that to continue with FC drives might not be cost effective. Is
SATA an option for this type of system, or will performance be too degraded.
I read that SQL 2k can only do about 300Mbps (true?), so perhaps the existing
config is already too much.
Thanks!
-ckWhere is you read that? SQL Server is not the limiting factor by any means.
It is the hardware and configuration that will most likely be the bottleneck
for I/O. You say you are only collecting data every 5 minutes or more. How
much data can that be? Can you give more details? I doubt that you should
have a performance issue either way but we would really need to know more
about the amount and type of data you are talking about writing and reading.
--
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:327E30E1-A54C-4B9D-A128-7DA2BE6876DF@.microsoft.com...
> Hi all,
> Currently we have database system that logs readings from sensors at an
> interval from 5 minutes to 1 hour (more on the 5 minute side), from which
> customers can view statistical data, graphs, etc.
> For storage, we currently connect to a fiber-channel SAN with 150GB 15k
> drives. As you would expect, the amounts of data for this system is
> getting
> large enough that to continue with FC drives might not be cost effective.
> Is
> SATA an option for this type of system, or will performance be too
> degraded.
> I read that SQL 2k can only do about 300Mbps (true?), so perhaps the
> existing
> config is already too much.
> Thanks!
> -ck|||Perhaps I am misreading.
Here is the article:
http://www.sql-server-performance.com/jc_system_storage_configuration.asp
Skip to the part about System Storage:
"Now it turns out SQL Server 2000 has limited ability to read from disk in a
table scan operation. Without hints, SQL Server 2000 table scan operation in
most systems will generate between 300-400MB/sec in disk reads."
As for the system:
I am not collecting data every 5 minutes; I am inserting data every 5
minutes. The scenario is a sensor wakes up every five minutes and collects
temperature data, etc. and inserts a row containing sensor information (id,
date time, firmware) plus the measurements (temp, %RH, etc.). Currently we
see about 16,000 new rows per hour with expect growth to be 12 - 20 X what we
currently are doing.
Customers can login to a website and view statistics, graphs, etc. based on
that sensor data. So you can possibly be displaying a graph for hundreds of
thousands of retrieved sensor readings.
I feel this is an I/O intense application, and I am afraid to "scale back"
from Fiber channel to SATA just based on the fact that FC is faster. But is
it REALLY that much better?
Does anyone else deal with such large amounts of data and how do they store
it?
-ck
"Andrew J. Kelly" wrote:
> Where is you read that? SQL Server is not the limiting factor by any means.
> It is the hardware and configuration that will most likely be the bottleneck
> for I/O. You say you are only collecting data every 5 minutes or more. How
> much data can that be? Can you give more details? I doubt that you should
> have a performance issue either way but we would really need to know more
> about the amount and type of data you are talking about writing and reading.
> --
> Andrew J. Kelly SQL MVP
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:327E30E1-A54C-4B9D-A128-7DA2BE6876DF@.microsoft.com...
> > Hi all,
> >
> > Currently we have database system that logs readings from sensors at an
> > interval from 5 minutes to 1 hour (more on the 5 minute side), from which
> > customers can view statistical data, graphs, etc.
> >
> > For storage, we currently connect to a fiber-channel SAN with 150GB 15k
> > drives. As you would expect, the amounts of data for this system is
> > getting
> > large enough that to continue with FC drives might not be cost effective.
> > Is
> > SATA an option for this type of system, or will performance be too
> > degraded.
> > I read that SQL 2k can only do about 300Mbps (true?), so perhaps the
> > existing
> > config is already too much.
> >
> > Thanks!
> >
> > -ck
>
>|||"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:A4FFD3A4-2EA8-4C9C-9EA6-FCB2BA4CC65A@.microsoft.com...
> As for the system:
> I am not collecting data every 5 minutes; I am inserting data every 5
> minutes. The scenario is a sensor wakes up every five minutes and collects
> temperature data, etc. and inserts a row containing sensor information
> (id,
> date time, firmware) plus the measurements (temp, %RH, etc.). Currently we
> see about 16,000 new rows per hour with expect growth to be 12 - 20 X what
> we
> currently are doing.
> Customers can login to a website and view statistics, graphs, etc. based
> on
> that sensor data. So you can possibly be displaying a graph for hundreds
> of
> thousands of retrieved sensor readings.
>
How big is each row? 5 bytes, 5,000, 5 million?
That's really going to drive things. Typically a row can't exceed 8060
bytes. (unless you use large text).
SQL will read/write a page (8K) at time.
So if a row is 8000 bytes, you need to write a row per page.
If it's 800 bytes, you can write 10 rows per page, so it'll be that much
faster.
Ultimately you just really need to figure out the size of the rows.
Now the site at my former employer, we were recording millions of banner ad
impressions an hour. On fairly old hardware.
> I feel this is an I/O intense application, and I am afraid to "scale back"
> from Fiber channel to SATA just based on the fact that FC is faster. But
> is
> it REALLY that much better?
> Does anyone else deal with such large amounts of data and how do they
> store
> it?
By doing the math. ;-)
Figure out what you really need and you can scale from there.
> -ck
>
> "Andrew J. Kelly" wrote:
>> Where is you read that? SQL Server is not the limiting factor by any
>> means.
>> It is the hardware and configuration that will most likely be the
>> bottleneck
>> for I/O. You say you are only collecting data every 5 minutes or more.
>> How
>> much data can that be? Can you give more details? I doubt that you
>> should
>> have a performance issue either way but we would really need to know more
>> about the amount and type of data you are talking about writing and
>> reading.
>> --
>> Andrew J. Kelly SQL MVP
>> "Chris" <Chris@.discussions.microsoft.com> wrote in message
>> news:327E30E1-A54C-4B9D-A128-7DA2BE6876DF@.microsoft.com...
>> > Hi all,
>> >
>> > Currently we have database system that logs readings from sensors at an
>> > interval from 5 minutes to 1 hour (more on the 5 minute side), from
>> > which
>> > customers can view statistical data, graphs, etc.
>> >
>> > For storage, we currently connect to a fiber-channel SAN with 150GB 15k
>> > drives. As you would expect, the amounts of data for this system is
>> > getting
>> > large enough that to continue with FC drives might not be cost
>> > effective.
>> > Is
>> > SATA an option for this type of system, or will performance be too
>> > degraded.
>> > I read that SQL 2k can only do about 300Mbps (true?), so perhaps the
>> > existing
>> > config is already too much.
>> >
>> > Thanks!
>> >
>> > -ck
>>|||Chris,
Inserting 16K rows an hour or even 20 x that much is trivial with a well
designed db and application. I deal with applications every day that do that
many in a second or two. Reading hundreds of thousands of rows on a regular
basis may certainly be I/O intensive and I think you can handle it with both
FC or SATA given a proper design and hardware. Having said that if you try
to cut too much cost you end up with what you paid for. The only way to know
for sure if the system can handle the expected load is to test it under your
exact conditions. I know nothing of your app but it seems pretty
unrealistic to think that someone can get something meaningful out of
hundreds of thousands of rows displayed on a web page without any
aggregation. Might it be possible to aggregate this data when storing it or
in addition to the data you collect row by row? Then you can read much less
data and require much less I/O. Again I am not going to pretend I
understand your app and its requirement but that is the key here. You must
determine exactly how much and what type of I/O you will actually need and
do the math and test.
--
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:A4FFD3A4-2EA8-4C9C-9EA6-FCB2BA4CC65A@.microsoft.com...
> Perhaps I am misreading.
> Here is the article:
> http://www.sql-server-performance.com/jc_system_storage_configuration.asp
> Skip to the part about System Storage:
> "Now it turns out SQL Server 2000 has limited ability to read from disk in
> a
> table scan operation. Without hints, SQL Server 2000 table scan operation
> in
> most systems will generate between 300-400MB/sec in disk reads."
> As for the system:
> I am not collecting data every 5 minutes; I am inserting data every 5
> minutes. The scenario is a sensor wakes up every five minutes and collects
> temperature data, etc. and inserts a row containing sensor information
> (id,
> date time, firmware) plus the measurements (temp, %RH, etc.). Currently we
> see about 16,000 new rows per hour with expect growth to be 12 - 20 X what
> we
> currently are doing.
> Customers can login to a website and view statistics, graphs, etc. based
> on
> that sensor data. So you can possibly be displaying a graph for hundreds
> of
> thousands of retrieved sensor readings.
> I feel this is an I/O intense application, and I am afraid to "scale back"
> from Fiber channel to SATA just based on the fact that FC is faster. But
> is
> it REALLY that much better?
> Does anyone else deal with such large amounts of data and how do they
> store
> it?
> -ck
>
> "Andrew J. Kelly" wrote:
>> Where is you read that? SQL Server is not the limiting factor by any
>> means.
>> It is the hardware and configuration that will most likely be the
>> bottleneck
>> for I/O. You say you are only collecting data every 5 minutes or more.
>> How
>> much data can that be? Can you give more details? I doubt that you
>> should
>> have a performance issue either way but we would really need to know more
>> about the amount and type of data you are talking about writing and
>> reading.
>> --
>> Andrew J. Kelly SQL MVP
>> "Chris" <Chris@.discussions.microsoft.com> wrote in message
>> news:327E30E1-A54C-4B9D-A128-7DA2BE6876DF@.microsoft.com...
>> > Hi all,
>> >
>> > Currently we have database system that logs readings from sensors at an
>> > interval from 5 minutes to 1 hour (more on the 5 minute side), from
>> > which
>> > customers can view statistical data, graphs, etc.
>> >
>> > For storage, we currently connect to a fiber-channel SAN with 150GB 15k
>> > drives. As you would expect, the amounts of data for this system is
>> > getting
>> > large enough that to continue with FC drives might not be cost
>> > effective.
>> > Is
>> > SATA an option for this type of system, or will performance be too
>> > degraded.
>> > I read that SQL 2k can only do about 300Mbps (true?), so perhaps the
>> > existing
>> > config is already too much.
>> >
>> > Thanks!
>> >
>> > -ck
>>|||Hi,
I would looked at partitioning the data. The current data on the FC and the
historical data stored in SATA device. This will give you the preformance
needed for realtime data and cost saving by storing historical data on SATA
device.
Regards,
Kueh.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:327E30E1-A54C-4B9D-A128-7DA2BE6876DF@.microsoft.com...
> Hi all,
> Currently we have database system that logs readings from sensors at an
> interval from 5 minutes to 1 hour (more on the 5 minute side), from which
> customers can view statistical data, graphs, etc.
> For storage, we currently connect to a fiber-channel SAN with 150GB 15k
> drives. As you would expect, the amounts of data for this system is
> getting
> large enough that to continue with FC drives might not be cost effective.
> Is
> SATA an option for this type of system, or will performance be too
> degraded.
> I read that SQL 2k can only do about 300Mbps (true?), so perhaps the
> existing
> config is already too much.
> Thanks!
> -ck|||Thanks all. I figured it would come down to a "depends" but I just wanted to
make sure that using SATA for an app like this did not throw up any big red
flags.
-ck
"Greg D. Moore (Strider)" wrote:
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:A4FFD3A4-2EA8-4C9C-9EA6-FCB2BA4CC65A@.microsoft.com...
> > As for the system:
> > I am not collecting data every 5 minutes; I am inserting data every 5
> > minutes. The scenario is a sensor wakes up every five minutes and collects
> > temperature data, etc. and inserts a row containing sensor information
> > (id,
> > date time, firmware) plus the measurements (temp, %RH, etc.). Currently we
> > see about 16,000 new rows per hour with expect growth to be 12 - 20 X what
> > we
> > currently are doing.
> >
> > Customers can login to a website and view statistics, graphs, etc. based
> > on
> > that sensor data. So you can possibly be displaying a graph for hundreds
> > of
> > thousands of retrieved sensor readings.
> >
> How big is each row? 5 bytes, 5,000, 5 million?
> That's really going to drive things. Typically a row can't exceed 8060
> bytes. (unless you use large text).
> SQL will read/write a page (8K) at time.
> So if a row is 8000 bytes, you need to write a row per page.
> If it's 800 bytes, you can write 10 rows per page, so it'll be that much
> faster.
> Ultimately you just really need to figure out the size of the rows.
> Now the site at my former employer, we were recording millions of banner ad
> impressions an hour. On fairly old hardware.
>
> > I feel this is an I/O intense application, and I am afraid to "scale back"
> > from Fiber channel to SATA just based on the fact that FC is faster. But
> > is
> > it REALLY that much better?
> >
> > Does anyone else deal with such large amounts of data and how do they
> > store
> > it?
> By doing the math. ;-)
> Figure out what you really need and you can scale from there.
>
> >
> > -ck
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Where is you read that? SQL Server is not the limiting factor by any
> >> means.
> >> It is the hardware and configuration that will most likely be the
> >> bottleneck
> >> for I/O. You say you are only collecting data every 5 minutes or more.
> >> How
> >> much data can that be? Can you give more details? I doubt that you
> >> should
> >> have a performance issue either way but we would really need to know more
> >> about the amount and type of data you are talking about writing and
> >> reading.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> >> news:327E30E1-A54C-4B9D-A128-7DA2BE6876DF@.microsoft.com...
> >> > Hi all,
> >> >
> >> > Currently we have database system that logs readings from sensors at an
> >> > interval from 5 minutes to 1 hour (more on the 5 minute side), from
> >> > which
> >> > customers can view statistical data, graphs, etc.
> >> >
> >> > For storage, we currently connect to a fiber-channel SAN with 150GB 15k
> >> > drives. As you would expect, the amounts of data for this system is
> >> > getting
> >> > large enough that to continue with FC drives might not be cost
> >> > effective.
> >> > Is
> >> > SATA an option for this type of system, or will performance be too
> >> > degraded.
> >> > I read that SQL 2k can only do about 300Mbps (true?), so perhaps the
> >> > existing
> >> > config is already too much.
> >> >
> >> > Thanks!
> >> >
> >> > -ck
> >>
> >>
> >>
>
>|||"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:53906716-C57E-4454-B796-E24A9D76C936@.microsoft.com...
> Thanks all. I figured it would come down to a "depends" but I just wanted
> to
> make sure that using SATA for an app like this did not throw up any big
> red
> flags.
Not really.
(though some SATA drives are clearly "consumer" level and have lower MTBF
than you might want. So just keep an eye on that.)
> -ck
> "Greg D. Moore (Strider)" wrote:
>> "Chris" <Chris@.discussions.microsoft.com> wrote in message
>> news:A4FFD3A4-2EA8-4C9C-9EA6-FCB2BA4CC65A@.microsoft.com...
>> > As for the system:
>> > I am not collecting data every 5 minutes; I am inserting data every 5
>> > minutes. The scenario is a sensor wakes up every five minutes and
>> > collects
>> > temperature data, etc. and inserts a row containing sensor information
>> > (id,
>> > date time, firmware) plus the measurements (temp, %RH, etc.). Currently
>> > we
>> > see about 16,000 new rows per hour with expect growth to be 12 - 20 X
>> > what
>> > we
>> > currently are doing.
>> >
>> > Customers can login to a website and view statistics, graphs, etc.
>> > based
>> > on
>> > that sensor data. So you can possibly be displaying a graph for
>> > hundreds
>> > of
>> > thousands of retrieved sensor readings.
>> >
>> How big is each row? 5 bytes, 5,000, 5 million?
>> That's really going to drive things. Typically a row can't exceed 8060
>> bytes. (unless you use large text).
>> SQL will read/write a page (8K) at time.
>> So if a row is 8000 bytes, you need to write a row per page.
>> If it's 800 bytes, you can write 10 rows per page, so it'll be that much
>> faster.
>> Ultimately you just really need to figure out the size of the rows.
>> Now the site at my former employer, we were recording millions of banner
>> ad
>> impressions an hour. On fairly old hardware.
>>
>> > I feel this is an I/O intense application, and I am afraid to "scale
>> > back"
>> > from Fiber channel to SATA just based on the fact that FC is faster.
>> > But
>> > is
>> > it REALLY that much better?
>> >
>> > Does anyone else deal with such large amounts of data and how do they
>> > store
>> > it?
>> By doing the math. ;-)
>> Figure out what you really need and you can scale from there.
>>
>> >
>> > -ck
>> >
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Where is you read that? SQL Server is not the limiting factor by any
>> >> means.
>> >> It is the hardware and configuration that will most likely be the
>> >> bottleneck
>> >> for I/O. You say you are only collecting data every 5 minutes or more.
>> >> How
>> >> much data can that be? Can you give more details? I doubt that you
>> >> should
>> >> have a performance issue either way but we would really need to know
>> >> more
>> >> about the amount and type of data you are talking about writing and
>> >> reading.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >> "Chris" <Chris@.discussions.microsoft.com> wrote in message
>> >> news:327E30E1-A54C-4B9D-A128-7DA2BE6876DF@.microsoft.com...
>> >> > Hi all,
>> >> >
>> >> > Currently we have database system that logs readings from sensors at
>> >> > an
>> >> > interval from 5 minutes to 1 hour (more on the 5 minute side), from
>> >> > which
>> >> > customers can view statistical data, graphs, etc.
>> >> >
>> >> > For storage, we currently connect to a fiber-channel SAN with 150GB
>> >> > 15k
>> >> > drives. As you would expect, the amounts of data for this system is
>> >> > getting
>> >> > large enough that to continue with FC drives might not be cost
>> >> > effective.
>> >> > Is
>> >> > SATA an option for this type of system, or will performance be too
>> >> > degraded.
>> >> > I read that SQL 2k can only do about 300Mbps (true?), so perhaps the
>> >> > existing
>> >> > config is already too much.
>> >> >
>> >> > Thanks!
>> >> >
>> >> > -ck
>> >>
>> >>
>> >>
>>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Friday, March 9, 2012

SATA RAID 5 Performance

SATA RAID 5 Performance

SATA RAID 5 Performance

SATA for SQL Server data

SATA for SQL Server data

SATA for SQL Server data

Sample db problem

Blog Archive

About Me