I've spent several days chasing URLs to find information on what the
various settings can and should be in our postmaster '.conf' file.
Mostly, I'm finding that many changes don't seem to affect our
performance all that much. I thought I might describe some of the
salient features of our setup, and if anyone wants to take a whack at
suggesting parameters they would use, I'd be grateful.
System: 1GHz dual-processors, 4GB memory, lots and lots of disk, RAIDed
OS: Linux, 2.4 kernel.
Tables: about 175. The largest tables about:
3.5 million rows
2.2 million rows
0.9 million rows
and they trail off rapidly from there. (There are 10 tables total with
100,000 rows or more.)
Applications: there are about 12 daemons running Perl/DBI scripts. 6
daemons do 90%+ of the work against the database. Most of that is spent
adding rows to those three biggest tables. There are a varying number of
connections from Apache processes, either locally or (more often) from
another server. Usually, we have 60-80 Postgres backends running at a
time.
Presently, I'm tinkering with increasing the shared_buffers parameter by
chunks. We'd been running with about 8k, then I took it up to 16k and
64k. I have max_connections set to 192, wal_buffers 32, sort_mem 64k,
and vacuum_mem 64k. Comments on these settings are welcome. In
addition, any suggestions on how to measure the impact of the changes
would also be welcome. (At present, I'm reduced to muttering, "Well, it
*seems* faster ...". I do have a way to measure the total wallclock time
of every SQL statement executed by the daemons and web clients, which is
my measurement stick if nothing else presents itself.)
--
Jeff Boes vox 269.226.9550 ext 24
Database Engineer fax 269.349.9076
Nexcerpt, Inc. http://www.nexcerpt.com
...Nexcerpt... Extend your Expertise
