Re: Speed / Server - Mailing list pgsql-performance

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.