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
