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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-performance by date

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