Skip site navigation (1) Skip section navigation (2)

Re: What's the best hardver for PostgreSQL 8.1?

From: Juan Casero <caseroj(at)comcast(dot)net>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: What's the best hardver for PostgreSQL 8.1?
Date: 2005-12-21 00:50:47
Message-ID: 200512201950.47671.caseroj@comcast.net (view raw or flat)
Thread:
Lists: pgsql-performance
Can you elaborate on the reasons the opteron is better than the Xeon when it 
comes to disk io?   I have a PostgreSQL 7.4.8 box running a DSS.   One of our 
tables is about 13 million rows.   I had a number of queries against this 
table that used innner joins on 5 or 6 tables including the 13 million row 
one.  The performance was atrocious.  The database itself is about 20 gigs 
but I want it to scale to 100 gigs.  I tuned postgresql as best I could and 
gave the server huge amounts of memory for caching as well.  I also tweaked 
the cost parameters for a sequential scan vs an index scan of the query 
optimizer and used the query explain mechanism to get some idea of what the 
optimizer was doing and where I should index the tables.  When I added the 
sixth table to the inner join the query performance took a nose dive.  
Admittedly this system is a single PIII 1000Mhz with 1.2 gigs of ram and no 
raid.  I do have two Ultra 160 scsi drives with the database tables mount 
point on a partition on one physical drive and pg_xlog mount point on another 
partition of the second drive.    I have been trying to get my employer to 
spring for new hardware ($8k to $10k) which I had planned to be a dual - dual 
core opteron system from HP.  Until they agree to spend the money I resorted 
to writing a plpgsql functions to handle the queries.  Inside plpgsql I can 
break the query apart into seperate stages each of which runs much faster.  I 
can use temporary tables to store intermediate results without worrying about 
temp table collisions with different users thanks to transaction isolation.    
I am convinced we need new hardware to scale this application *but* I agree 
with the consensus voiced here that it is more important to optimize the 
query first before going out to buy new hardware.   I was able to do things 
with PostgreSQL on this cheap server that I could never imagine doing with 
SQL server or even oracle on such a low end box.  My OS is Fedora Core 3 but 
I wonder if anyone has tested and benchmarked PostgreSQL on the new Sun x64 
servers running Solaris 10 x86.

Thanks,
Juan

On Tuesday 20 December 2005 16:08, Vivek Khera wrote:
> On Dec 20, 2005, at 1:27 PM, Antal Attila wrote:
> > The budget line is about 30 000$ - 40 000$.
>
> Like Jim said, without more specifics it is hard to give more
> specific recommendations, but I'm architecting something like this
> for my current app which needs ~100GB disk space.  I made room to
> grow in my configuration:
>
> dual opteron 2.2GHz
> 4GB RAM
> LSI MegaRAID 320-2X
> 14-disk SCSI U320 enclosure with 15k RPM drives, 7 connected to each
> channel on the RAID.
>    1 pair in RAID1 mirror for OS + pg_xlog
>    rest in RAID10 with each mirrored pair coming from opposite SCSI
> channels for data
>
> I run FreeBSD but whatever you prefer should be sufficient if it is
> not windows.
>
> I don't know how prices are in Hungary, but around here something
> like this with 36GB drives comes to around $11,000 or $12,000.
>
> The place I concentrate on is the disk I/O bandwidth which is why I
> prefer Opteron over Intel XEON.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match

In response to

Responses

pgsql-performance by date

Next:From: David LangDate: 2005-12-21 02:46:31
Subject: Re: What's the best hardver for PostgreSQL 8.1?
Previous:From: Tom LaneDate: 2005-12-20 21:44:47
Subject: Re: Any way to optimize GROUP BY queries?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group