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