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 2CA43D1D1B2 for ; Thu, 12 Feb 2004 19:02:31 +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 49975-05 for ; Thu, 12 Feb 2004 15:02:30 -0400 (AST) Received: from gomez.justsportsusa.com (unknown [208.29.195.72]) by svr1.postgresql.org (Postfix) with ESMTP id CD8DED1CAF3 for ; Thu, 12 Feb 2004 15:02:28 -0400 (AST) Received: from ehpg.net (gavin.justsportsusa.com [192.168.254.20] (may be forged)) by gomez.justsportsusa.com (8.12.9/8.12.4) with ESMTP id i1CJ16WD019209; Thu, 12 Feb 2004 11:01:09 -0800 Message-ID: <402BCDEA.2040207@ehpg.net> Date: Thu, 12 Feb 2004 11:03:06 -0800 From: "Gavin M. Roy" User-Agent: Mozilla Thunderbird 0.5 (Windows/20040207) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Leon Out Cc: pgsql-performance@postgresql.org Subject: Re: Disappointing performance in db migrated from MS SQL References: <70CC4F08-5D89-11D8-AB22-0030658FB514@comvision.com> In-Reply-To: <70CC4F08-5D89-11D8-AB22-0030658FB514@comvision.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/148 X-Sequence-Number: 5672 It might be helpful to include a sample query that is running slower than you expect, with the table structure, and the output of explain {query}. Gavin Leon Out wrote: > 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 > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend