Re: Sun Fire T2000 and PostgreSQL 8.1.3 - Mailing list pgsql-performance

On 4/6/06, Juan Casero (FL FLC) <Juan.Casero@wholefoods.com> wrote:
> Because I plan to develop a rather large (for us anyway) data warehouse
> with PostgreSQL.  I am looking for the right hardware that can handle
> queries on a database that might grow to over a 100 gigabytes.

You need to look for a server that has fast I/O. 100 GB of data will
take a long time to scan through and won't fit in RAM.

> Right
> now our decision support system based on postgresql 8.1.3 stores retail
> sales information for about 4 four years back *but* only as weekly
> summaries.  I want to build the system so it can handle daily sales
> transactions also.  You can imagine how many more records this will
> involve so I am looking for hardware that can give me the performance I
> need to make this project useable.

Sounds like you need to be doing a few heavy queries when you do this,
not tons of small queries. That likely means you need fewer CPUs that
are very fast.

> In other words parsing and loading
> the daily transaction logs for our stores is likely to take huge amounts
> of effort.  I need a machine that can complete the task in a reasonable
> amount of time.

See my previous comment

> As people start to query the database to find sales
> related reports and information I need to make sure the queries will run
> reasonably fast for them.

Get more than one CPU core and make sure you have a lot of drive
spindles. You will definately want to be able to ensure a long running
query doesn't hog your i/o system. I have a server with a single disk
and when we do a long query the server load will jump from about .2 to
10 until the long query finishes. More cpus won't help this because
the bottle neck is the disk.

>  I have already hand optimized all of my
> queries on the current system.  But currently I only have weekly sales
> summaries.  Other divisions in our company have done a similar project
> using MS SQL Server on SMP hardware far outclassing the database server
> I currently use and they report heavy loads on the server with less than
> ideal query run times.  I am sure I can do my part to optimize the
> queries once I start this project but there is only so much you can do.
> At some point you just need more powerful hardware.  This is where I am
> at right now.

You say "this is where I am at right __now__" but where will you be in
9 months? Sounds like you will be i/o bound by the time you get above
10GB.

> Apart from that since I will only get this one chance to
> buy a new server for data processing I need to make sure that I buy
> something that can grow over time as our needs change.  I don't want to
> buy a server only to find out later that it cannot meet our needs with
> future database projects.  I have to balance a limited budget, room for
> future performance growth, and current system requirements.  Trust me it
> isn't easy.

Isn't it about time we had our annual "what kind of server can I get
for $8k" thread?

--
Matthew Nuzum
www.bearfruit.org