Обсуждение: Re: Update performance ... Recommended configuration changes?

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

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