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

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