Re: Hardware performance tuning

Take a look at what Bruce Momjian has to say.  One thing is certain
however, your shared buffers are pitifully low for the amount of
memory you have available.

http://www2.linuxjournal.com/lj-issues/issue88/4791.html

Jason

Douglas Bates <bates@stat.wisc.edu> writes:

> 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/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org