X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id B3B1ED1BB95 for ; Thu, 12 Feb 2004 18:30:13 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 33263-08 for ; Thu, 12 Feb 2004 14:30:10 -0400 (AST) Received: from arkanoid.comvision.com (arkanoid.comvision.com [63.108.120.12]) by svr1.postgresql.org (Postfix) with ESMTP id 8D734D1CACD for ; Thu, 12 Feb 2004 14:30:09 -0400 (AST) Received: by arkanoid.comvision.com (Postfix, from userid 98) id EBA77179E0; Thu, 12 Feb 2004 13:51:45 -0500 (EST) Received: from [63.108.121.47] (calendar.comvision.com [63.108.121.47]) by arkanoid.comvision.com (Postfix) with ESMTP id 928E717931 for ; Thu, 12 Feb 2004 13:51:44 -0500 (EST) Mime-Version: 1.0 (Apple Message framework v612) Content-Transfer-Encoding: 7bit Message-Id: <70CC4F08-5D89-11D8-AB22-0030658FB514@comvision.com> Content-Type: text/plain; charset=US-ASCII; format=flowed To: pgsql-performance@postgresql.org From: Leon Out Subject: Disappointing performance in db migrated from MS SQL Server Date: Thu, 12 Feb 2004 13:29:49 -0500 X-Mailer: Apple Mail (2.612) X-Sanitizer: Comvision mail filter X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/145 X-Sequence-Number: 5669 Hello all. I am in the midst of porting a large web application from a MS SQL Server backend to PostgreSQL. The migration work is basically complete, and we're at the testing and optimization phase of the project. The results so far have been disappointing, with Postgres performing queries in about the same time as SQL Server even though Postgres is running on a dedicated box with about 4 times the clock speed of the SQL Server box. For a chart of my results, please see http://leonout.com/pggraph.pdf for a graph of some test results. Here are the specs of the systems: SQL Server Dell PowerEdge 2400 Windows 2000 Advanced Server Dual Pentium III 667 2 GB Registered PC133 SDRAM MS SQL Server 2000 SP2 - shared database (although to be fair, this app is by far the heaviest) RAID 1 for system / RAID 5 for data (10k RPM Ultra160 SCSI drives) PostgreSQL Dell PowerEdge 2650 RedHat Enterprise Linux 3.1 Dual Xeon 3.06 GHz (Hyperthreading currently disabled) 4 GB DDR SDRAM PostgreSQL 7.4 - dedicated to this app, with no other apps running on system RAID 5 (15k RPM Ultra160 SCSI drives) The database is about 4.3 GB in size. My postgresql.conf is as follows: max_connections = 50 shared_buffers = 10000 # min 16, at least max_connections*2, 8KB each - default is 1000 sort_mem = 2000 # min 64, size in KB - default is 1024 (commented out) effective_cache_size = 250000 # typically 8KB each - default is 1000 (commented out) geqo = true lc_messages = 'en_US.UTF-8' # locale for system error message strings lc_monetary = 'en_US.UTF-8' # locale for monetary formatting lc_numeric = 'en_US.UTF-8' # locale for number formatting lc_time = 'en_US.UTF-8' # locale for time formatting I hope that someone can help with this. Thanks in advance for your help! Leon