Thread: Speed / Server

All:

We have a web-application which is growing ... fast.  We're currently
running on (1) quad-core Xeon 2.0Ghz with a RAID-1 setup, and 8GB of RAM.

Our application collects a lot of sensor data, which means that we have 1
table which has about 8 million rows, and we're adding about 2.5 million
rows per month.

The problem is, this next year we're anticipating significant growth,
where we may be adding more like 20 million rows per month (roughly 15GB
of data).

A row of data might have:
  The system identifier (int)
  Date/Time read (timestamp)
  Sensor identifier (int)
  Data Type (int)
  Data Value (double)

The nasty part of this problem is that the data needs to be "readily"
available for reports, and we cannot consolidate the data for reporting
purposes.

We generate real time graphs from this data, usually running reports
across multiple date/time ranges for any given system.  Reports and graphs
do not span more than 1 system, and we have indexes on the applicable
columns.

I know we need a LOT of RAM (as much as we can afford), and we're looking
at a couple of Nehalem systems w/ a large, and fast, RAID-10 disk set up.

So far, we're seeing some slowness in reading from our table - queries are
in the "seconds" range.  No issues, yet, with inserting volumes of data.

Two questions:

1.  Other than partitioning (by system, and/or date), and splitting up the
data into multiple tables (by data type), what could be done within
Postgresql to help with this type of set up (1 large table)?

2.  Before going out and buying a beast of a system, we'd like to get some
idea of performance on a "high-end" system.  We may need to split this up,
or move into some other type of architecture.  Do you know of anyone who
would let us "play" with a couple of systems to see what would be an
applicable purchase?

Thanks!


--
Anthony

On Sun, Oct 4, 2009 at 4:45 PM,  <anthony@resolution.com> wrote:
> All:
>
> We have a web-application which is growing ... fast.  We're currently
> running on (1) quad-core Xeon 2.0Ghz with a RAID-1 setup, and 8GB of RAM.
>
> Our application collects a lot of sensor data, which means that we have 1
> table which has about 8 million rows, and we're adding about 2.5 million
> rows per month.
>
> The problem is, this next year we're anticipating significant growth,
> where we may be adding more like 20 million rows per month (roughly 15GB
> of data).
>
> A row of data might have:
>  The system identifier (int)
>  Date/Time read (timestamp)
>  Sensor identifier (int)
>  Data Type (int)
>  Data Value (double)
>
> The nasty part of this problem is that the data needs to be "readily"
> available for reports, and we cannot consolidate the data for reporting
> purposes.
>
> We generate real time graphs from this data, usually running reports
> across multiple date/time ranges for any given system.  Reports and graphs
> do not span more than 1 system, and we have indexes on the applicable
> columns.
>
> I know we need a LOT of RAM (as much as we can afford), and we're looking
> at a couple of Nehalem systems w/ a large, and fast, RAID-10 disk set up.
>
> So far, we're seeing some slowness in reading from our table - queries are
> in the "seconds" range.  No issues, yet, with inserting volumes of data.
>
> Two questions:
>
> 1.  Other than partitioning (by system, and/or date), and splitting up the
> data into multiple tables (by data type), what could be done within
> Postgresql to help with this type of set up (1 large table)?
>
> 2.  Before going out and buying a beast of a system, we'd like to get some
> idea of performance on a "high-end" system.  We may need to split this up,
> or move into some other type of architecture.  Do you know of anyone who
> would let us "play" with a couple of systems to see what would be an
> applicable purchase?

Most of the producers of big bad database servers have a trial period
you can try stuff out for.  My supplier has something like a 30 day
trial.  I'm sure the bigger the system the more they'd need to charge
you for playing on it then returning it.

But you should plan on partitioning to multiple db servers up front
and save pain of conversion later on.  A dual socket motherboard with
16 to 32 SAS drives and a fast RAID controller is WAY cheaper than a
similar machine with 4 to 8 sockets is gonna be.  And if you gotta go
there anyway, might as well spend your money on other stuff.

On Mon, Oct 5, 2009 at 7:30 AM, Nikolas Everett <nik9000@gmail.com> wrote:
>
>> But you should plan on partitioning to multiple db servers up front
>> and save pain of conversion later on.  A dual socket motherboard with
>> 16 to 32 SAS drives and a fast RAID controller is WAY cheaper than a
>> similar machine with 4 to 8 sockets is gonna be.  And if you gotta go
>> there anyway, might as well spend your money on other stuff.
>>
>
> I agree.  If you can partition that sensor data across multiple DBs and have
> your application do the knitting you might be better off.  If I may be so
> bold, you might want to look at splaying the systems out across your
> backends.  I'm just trying to think of a dimension that you won't want to
> aggregate across frequently.

Agreed back.  If there's a logical dimension to split data on, it
becomes much easier to throw x machines at it than to try and build
one ubermachine to handle it all.

> On the other hand, one of these 16 to 32 SAS drive systems with a raid card
> will likely get you a long way.

Yes they can.  We're about to have to add a third db server, cause
this is the load on our main slave db:

procs -----------memory---------- ---swap-- -----io---- --system--
-----cpu------
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
22  0    220 633228 229556 28432976    0    0   638   304    0    0 21
 3 73  3  0
19  1    220 571980 229584 28435180    0    0    96  1111 7091 9796 90
 6  4  0  0
20  0    220 532208 229644 28440244    0    0   140  3357 7110 9175 90
 6  3  0  0
19  1    220 568440 229664 28443688    0    0   146  1527 7765 10481
90  7  3  0  0
 9  1    220 806668 229688 28445240    0    0    99   326 6661 10326
89  6  5  0  0
 9  0    220 814016 229712 28446144    0    0    54  1544 7456 10283
90  6  4  0  0
11  0    220 782876 229744 28447628    0    0    96   406 6619 9354 90
 5  5  0  0
29  1    220 632624 229784 28449964    0    0   113   994 7109 9958 90
 7  3  0  0

It's working fine.  This has a 16 15k5 SAS disks.  A 12 Disk RAID-10,
a 2 disk mirror for pg_xlog / OS, and two spares. It has 8 opteron
cores and 32Gig ram. We're completely CPU bound because of the type of
app we're running.  So time for slave number 2...

On Tue, Oct 6, 2009 at 7:21 AM, Nikolas Everett <nik9000@gmail.com> wrote:
> If my un-word wrapping is correct your running ~90% user cpu.  Yikes.  Could
> you get away with fewer disks for this kind of thing?

Probably, but the same workload on a 6 disk RAID-10 is 20% or so
IOWAIT.  So somewhere between 6 and 12 disks we go from significant
IOWAIT to nearly none.  Given that CPU bound workloads deteriorate
more gracefully than IO Bound, I'm pretty happy having enough extra IO
bandwidth on this machine.

On Tue, Oct 6, 2009 at 8:26 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Tue, Oct 6, 2009 at 7:21 AM, Nikolas Everett <nik9000@gmail.com> wrote:
>> If my un-word wrapping is correct your running ~90% user cpu.  Yikes.  Could
>> you get away with fewer disks for this kind of thing?
>
> Probably, but the same workload on a 6 disk RAID-10 is 20% or so
> IOWAIT.  So somewhere between 6 and 12 disks we go from significant
> IOWAIT to nearly none.  Given that CPU bound workloads deteriorate
> more gracefully than IO Bound, I'm pretty happy having enough extra IO
> bandwidth on this machine.

note that spare IO also means we can subscribe a slony slave midday or
run a query on a large data set midday and not overload our servers.
Spare CPU capacity is nice, spare IO is a necessity.

On Tue, Oct 6, 2009 at 8:26 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote: 

On Tue, Oct 6, 2009 at 7:21 AM, Nikolas Everett <nik9000@gmail.com> wrote:   

If my un-word wrapping is correct your running ~90% user cpu.  Yikes.  Could
you get away with fewer disks for this kind of thing?     

Probably, but the same workload on a 6 disk RAID-10 is 20% or so
IOWAIT.  So somewhere between 6 and 12 disks we go from significant
IOWAIT to nearly none.  Given that CPU bound workloads deteriorate
more gracefully than IO Bound, I'm pretty happy having enough extra IO
bandwidth on this machine.   

note that spare IO also means we can subscribe a slony slave midday or
run a query on a large data set midday and not overload our servers.
Spare CPU capacity is nice, spare IO is a necessity.
 

On Tue, Oct 6, 2009 at 1:59 PM, Karl Denninger <karl@denninger.net> wrote:
>
> More importantly when you run out of I/O bandwidth "bad things" tend to
> happen very quickly; the degradation of performance when you hit the IO wall
> is extreme to the point of being essentially a "zeropoint event."

Or as I like to put it IO bandwidth has sharp knees.

On Sun, Oct 4, 2009 at 6:45 PM,  <anthony@resolution.com> wrote:
> All:
>
> We have a web-application which is growing ... fast.  We're currently
> running on (1) quad-core Xeon 2.0Ghz with a RAID-1 setup, and 8GB of RAM.
>
> Our application collects a lot of sensor data, which means that we have 1
> table which has about 8 million rows, and we're adding about 2.5 million
> rows per month.
>
> The problem is, this next year we're anticipating significant growth,
> where we may be adding more like 20 million rows per month (roughly 15GB
> of data).
>
> A row of data might have:
>  The system identifier (int)
>  Date/Time read (timestamp)
>  Sensor identifier (int)
>  Data Type (int)
>  Data Value (double)

One approach that can sometimes help is to use arrays to pack data.
Arrays may or may not work for the data you are collecting: they work
best when you always pull the entire array for analysis and not a
particular element of the array.  Arrays work well because they pack
more data into index fetches and you get to skip the 20 byte tuple
header.  That said, they are an 'optimization trade off'...you are
making one type of query fast at the expense of others.

In terms of hardware, bulking up memory will only get you so
far...sooner or later you have to come to terms with the fact that you
are dealing with 'big' data and need to make sure your storage can cut
the mustard.  Your focus on hardware upgrades should probably be size
and quantity of disk drives in a big raid 10.

Single user or 'small number of user'  big data queries tend to
benefit more from fewer core, fast cpus.

Also, with big data, you want to make sure your table design and
indexing strategy is as tight as possible.

merlin

On Tue, 2009-10-06 at 17:16 -0400, Merlin Moncure wrote:
> On Sun, Oct 4, 2009 at 6:45 PM,  <anthony@resolution.com> wrote:
> > All:
> >
> > We have a web-application which is growing ... fast.  We're currently
> > running on (1) quad-core Xeon 2.0Ghz with a RAID-1 setup, and 8GB of RAM.
> >
> > Our application collects a lot of sensor data, which means that we have 1
> > table which has about 8 million rows, and we're adding about 2.5 million
> > rows per month.
> >
> > The problem is, this next year we're anticipating significant growth,
> > where we may be adding more like 20 million rows per month (roughly 15GB
> > of data).
> >
> > A row of data might have:
> >  The system identifier (int)
> >  Date/Time read (timestamp)
> >  Sensor identifier (int)
> >  Data Type (int)
> >  Data Value (double)
>
> One approach that can sometimes help is to use arrays to pack data.
> Arrays may or may not work for the data you are collecting: they work
> best when you always pull the entire array for analysis and not a
> particular element of the array.  Arrays work well because they pack
> more data into index fetches and you get to skip the 20 byte tuple
> header.  That said, they are an 'optimization trade off'...you are
> making one type of query fast at the expense of others.
>
> In terms of hardware, bulking up memory will only get you so
> far...sooner or later you have to come to terms with the fact that you
> are dealing with 'big' data and need to make sure your storage can cut
> the mustard.  Your focus on hardware upgrades should probably be size
> and quantity of disk drives in a big raid 10.
>
> Single user or 'small number of user'  big data queries tend to
> benefit more from fewer core, fast cpus.
>
> Also, with big data, you want to make sure your table design and
> indexing strategy is as tight as possible.

Thanks for all of the input.  One thing we're going to try is to slice
up the data based on the data type ... so that we can spread the data
rows into about 15 different tables.  This should produce 15 tables, the
largest which will have about 50% of the data, with the rest having an
uneven distribution of the remaining data.

Most of the graphs / reports that we're doing need to only use one type
of data at a time, but several will need to stitch / combine data from
multiple data tables.

These combined with some new processors, and a fast RAID-10 system
should give us what we need going forward.

Thanks again!


--
Anthony

On Sun, 4 Oct 2009, anthony@resolution.com wrote:

> The nasty part of this problem is that the data needs to be "readily"
> available for reports, and we cannot consolidate the data for reporting
> purposes.

Just because you have to store the detailed data doesn't mean you can't
store a conslidated view on it too.  Have you considered driving the
primary reporting off of materialized views, so you only compute those
once?

> I know we need a LOT of RAM (as much as we can afford), and we're looking
> at a couple of Nehalem systems w/ a large, and fast, RAID-10 disk set up.

There is a lot of variation in RAID-10 setups that depends on the
controller used.  Make sure you're careful to consider the controller card
and performance of its battery-backed cache a critical component here;
performance does not scale well with additional drives if your controller
isn't good.

What card are you using now for your RAID-1 implementation?

> 1.  Other than partitioning (by system, and/or date), and splitting up the
> data into multiple tables (by data type), what could be done within
> Postgresql to help with this type of set up (1 large table)?

This seems like a perfect fit for partitioning by date.

> 2.  Before going out and buying a beast of a system, we'd like to get some
> idea of performance on a "high-end" system.  We may need to split this up,
> or move into some other type of architecture.  Do you know of anyone who
> would let us "play" with a couple of systems to see what would be an
> applicable purchase?

Find vendors who sell things you like and ask if they have an eval system
available.  As prices move up, those become more common.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

> -----Original Message-----
<snip>
> >
> > The problem is, this next year we're anticipating significant growth,
> > where we may be adding more like 20 million rows per month (roughly
> 15GB
> > of data).
> >
> > A row of data might have:
> >  The system identifier (int)
> >  Date/Time read (timestamp)
> >  Sensor identifier (int)
> >  Data Type (int)
> >  Data Value (double)
>
> One approach that can sometimes help is to use arrays to pack data.
> Arrays may or may not work for the data you are collecting: they work
> best when you always pull the entire array for analysis and not a
> particular element of the array.  Arrays work well because they pack
> more data into index fetches and you get to skip the 20 byte tuple
> header.  That said, they are an 'optimization trade off'...you are
> making one type of query fast at the expense of others.
>

I recently used arrays for a 'long and thin' table very like those
described here. The tuple header became increasingly significant in our
case. There are some details in my post:

http://www.nabble.com/optimizing-for-temporal-data-behind-a-view-td25490818.html

As Merlin points out: one considerable side-effect of using arrays
is that it reduces the sort of queries which we could perform -
i.e. querying data is was in an array becomes costly.
So, we needed to make sure our user scenarios were (requirements)
were well understood.

richard

--
Scanned by iCritical.