Skip site navigation (1) Skip section navigation (2)

Disappointing performance in db migrated from MS SQL Server

From: Leon Out <leon-lists(at)comvision(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Disappointing performance in db migrated from MS SQL Server
Date: 2004-02-12 18:29:49
Message-ID: 70CC4F08-5D89-11D8-AB22-0030658FB514@comvision.com (view raw or flat)
Thread:
Lists: pgsql-performance
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


Responses

pgsql-performance by date

Next:From: PC DrewDate: 2004-02-12 18:43:57
Subject: Re: Disappointing performance in db migrated from MS SQL Server
Previous:From: Christopher Kings-LynneDate: 2004-02-12 02:01:49
Subject: Re: select count(*) from anIntColumn where int_value =

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group