Postgres Pro
Downloads
Home   >   mailing lists

Thread: Tuning/performance issue....

Tuning/performance issue....

From
David Griffiths
Date:
And finally,
 
Here's the contents of the postgresql.conf file (I've been playing with these setting the last couple of days, and using the guide @ http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html to make sure I didn't have it mis-tuned):
 
tcpip_socket = true
max_connections = 500        # We will need quite a few connections; currently only one connection to database, however
port = 5432
shared_buffers = 5000           # I've tried 5000 to 80,000 with no apparent difference
wal_buffers = 16
sort_mem = 256                    # decreased this due to the large # of connectiosn
effective_cache_size = 50000 # read that this can improve performance; hasn't done anything.
 
The machine is a dual-Pentium 3 933mhz, with 2 gigabytes of RAM and a 3Ware RAID-5 card.
 
As a reference, our production Oracle database (exactly the same hardware, but RAID-mirroring) with way more load can handle the query in 1-2 seconds. I have MySQL 4.0.14 with InnoDB on the same machine (shutdown when I am testing Postgres, and visa versa) and it does the query in 0.20 seconds.
 
Thanks for any insight.
David.

Re: Tuning/performance issue....

From
Shridhar Daithankar
Date:
David Griffiths wrote:

> And finally,
>
> Here's the contents of the postgresql.conf file (I've been playing with
> these setting the last couple of days, and using the guide @
> http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html to
> make sure I didn't have it mis-tuned):
>
> tcpip_socket = true
> max_connections = 500        # We will need quite a few connections;
> currently only one connection to database, however
> port = 5432
> shared_buffers = 5000           # I've tried 5000 to 80,000 with no
> apparent difference
> wal_buffers = 16
> sort_mem = 256                    # decreased this due to the large # of
> connectiosn
> effective_cache_size = 50000 # read that this can improve performance;
> hasn't done anything.

Reading this whole thread, I think most of the improvement you would get would
be from rethinking your schema from PG point of view and examine each query.

After you changed your last query as Tom suggested for explicit join, how much
improvement did it make? I noticed that you put
'commercial_entity.commercial_entity_id=225528' as a second codition. Does it
make any difference to put it ahead in where clause list?

  HTH

  Shridhar