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
>