pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: Update performance ... Recommended configuration changes?
2+ messages / 2 participants
[nested] [flat]

* Re: Update performance ... Recommended configuration changes?
@ 2003-12-03 21:22  erik@norvelle.net
  0 siblings, 1 reply; 2+ messages in thread

From: erik@norvelle.net @ 2003-12-03 21:22 UTC (permalink / raw)
  To: pgsql-performance


Thanks to Greg Stark, Tom Lane and Stephan Szabo for their advice on 
rewriting my query... the revised query plan claims it should only take 
about half the time my original query did.

Now for a somewhat different question:  How might I improve my DB 
performance by adjusting the various parameters in postgresql.conf and 
kernel config?  Again, TKA.

Here's what I've currently got (hardware, kernel config. and 
postgresql.conf)

Hardware: Mac iBook, G3 900Mhz, 640MB memory (This is my research machine :p 
)
OS:  OS X 10.2.6
Postgresql version: 7.3.2
Kernel Config:
    sysctl -w kern.sysv.shmmax=4194304
    sysctl -w kern.sysv.shmmin=1
    sysctl -w kern.sysv.shmmni=32
    sysctl -w kern.sysv.shmseg=8
    sysctl -w kern.sysv.shmall=1024

========================= Snip of postgresql.conf =================

#
#   Shared Memory Size
#
shared_buffers = 128        # min max_connections*2 or 16, 8KB each
max_fsm_relations = 2000    # min 10, fsm is free space map, ~40 bytes
max_fsm_pages = 20000       # min 1000, fsm is free space map, ~6 bytes
max_locks_per_transaction = 128 # min 10
wal_buffers = 16        # min 4, typically 8KB each
#
#   Non-shared Memory Sizes
#
sort_mem = 65535        # min 64, size in KB
vacuum_mem = 8192       # min 1024, size in KB

#
#   Write-ahead log (WAL)
#
#checkpoint_segments = 3    # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 300   # range 30-3600, in seconds
#
#commit_delay = 0       # range 0-100000, in microseconds
#commit_siblings = 5        # range 1-1000
#
fsync = false
#wal_sync_method = fsync    # the default varies across platforms:
#               # fsync, fdatasync, open_sync, or open_datasync
#wal_debug = 0          # range 0-16

========================== End Snip =======================

Saludos,
Erik Norvelle





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Update performance ... Recommended configuration
@ 2003-12-03 21:33  Rod Taylor <pg@rbt.ca>
  parent: erik@norvelle.net
  0 siblings, 0 replies; 2+ messages in thread

From: Rod Taylor @ 2003-12-03 21:33 UTC (permalink / raw)
  To: erik@norvelle.net; +Cc: pgsql-performance

> shared_buffers = 128        # min max_connections*2 or 16, 8KB each

Try 1500.

> sort_mem = 65535        # min 64, size in KB

I'd pull this in. You only have 640MB ram, which means about 8 large
sorts to swap.

How about 16000?

> fsync = false

I presume you understand the risks involved with this setting and
dataloss.




^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2003-12-03 21:33 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-12-03 21:22 Re: Update performance ... Recommended configuration changes? erik@norvelle.net
2003-12-03 21:33 ` Re: Update performance ... Recommended configuration Rod Taylor <pg@rbt.ca>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox