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