I am running the PostgreSQL 7.1.3 server on a Debian GNU/Linux
(testing) system. The computer is more-or-less a dedicated PostgreSQL
se and Zope server. It has 1 GB of memory (DIMMs are really cheap
these days) and a single 7200 rpm ATA-100 IDE drive. The processor is
a 1.2 GHz Athlon, in case that matters.
I read Bruce Momjian's supplement "PostgreSQL Performance Tuning" to
his Addison-Wesley book. He mentions two parameters, shared_buffers
and sort_mem, that you should consider increasing
when you have a reasonable amount of memory available.
My postgresql.conf file currently contains
debug_level = 0
hostname_lookup = on
log_connections = on
log_pid = on
log_timestamp = on
syslog = 2
tcpip_socket = on
fsync = off
# if syslog is 0, turn silent_mode off!
silent_mode = off
syslog_facility = LOCAL0
trace_notify = off
max_connections = 64
# shared_buffers must be at least twice max_connections, and not less than 16
shared_buffers = 512
sort_mem = 2048
Yesterday we were loading a database that has about a dozen tables.
The largest table (about 250,000 rows) has referential integrity
constraints relative to two other tables. The load went reasonably
quickly but I did notice that the postmaster process was using about
85% of the cpu time and seemed to be doing a lot of system calls. I
was surprised that it used very little memory, about 8 MB I believe.
I suspect that if I increase the sort_mem parameter or the
shared_buffers parameter it will run much faster at the expense of
using more memory. I really wouldn't object if the postmaster process
used a couple hundred megabytes of memory since the machine has enough
memory and is more-or-less dedicated to PostgreSQL. Would it do any
good for me to bump up the sort_mem parameter? If so, how big should
I make it? Are there other tuning parameters that I could profitably
tweak?
--
Douglas Bates bates@stat.wisc.edu
Statistics Department 608/262-2598
University of Wisconsin - Madison http://www.stat.wisc.edu/~bates/