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:


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 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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
     joining column's datatypes do not match

Reply via email to