Re: Disappointing performance in db migrated from MS SQL

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