Re: Disappointing performance in db migrated from MS SQL Server

All, thanks for your suggestions. I've tweaked my configuration, and I 
think I've squeezed a little more performance out of the setup. I also 
tried running several tests simultaneously against postgres and SQL 
Server, and postgres did much better with the heavy load.

My new settings are:
max_connections = 50
shared_buffers = 120000         # min 16, at least max_connections*2, 
8KB each - default is 1000
sort_mem = 8000                 # min 64, size in KB - default is 1024 
(commented out)
effective_cache_size = 375000   # typically 8KB each - default is 1000 
(commented out)
random_page_cost = 1            # units are one sequential page fetch 
cost - default is 4 (commented out)
geqo = true


Josh, the disks in the new system should be substantially faster than 
the old. Both are Ultra160 SCSI RAID 5 arrays, but the new system has 
15k RPM disks, as opposed to the 10k RPM disks in the old system.

On Feb 12, 2004, at 3:26 PM, Josh Berkus wrote:

> Leon,
>
>> 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.
>
> Your settings look ok to start, but we'll probably want to tune them 
> further.
> Can you post some details of the tests?   Include:
>
> 1) the query
> 2) the EXPLAIN ANALYZE results of the query
> 3) Whether you ran the test as the only connection, or whether you 
> tested
> multi-user load.
>
> The last is fairly important for a SQL Server vs. PostgreSQL test; SQL 
> Server
> is basically a single-user-database, so like MySQL it appears very 
> fast until
> you get a bunch o' users on it.
>
> Finally, for most queries the disk I/O and the RAM are more important 
> than the
> CPU clock speed.   From the looks of it, you upgraded the CPU + RAM, 
> but did
> downgraded the disk array as far as database writes are concered; not a
> terrible effective way to gain performance on your hardware.
>
> -- 
> -Josh Berkus
>  Aglio Database Solutions
>  San Francisco
>