From: Nick Fankhauser - Doxpop <nickf@doxpop.com>
To: Justin Long <justinlong@strategicnetwork.org>
To: pgsql-performance@postgresql.org
Subject: Re: Optimization
Date: Mon, 28 Jul 2003 13:25:55 -0500
Message-ID: <NEBBLAAHGLEEPCGOBHDGOEHNHNAA.nickf@doxpop.com> (raw)
In-Reply-To: <000f01c35523$f13dee90$ecd36044@tarzan>
Justin-
It sounds like you're on a system similar to ours, so I'll pass along the
changes that I made, which seem to have increased performance, and most
importantly, haven't hurt anything. The main difference in our environment
is that we are less Update/Insert intensive than you are- in our
application, 90% of our information (court cases) is static (Closed) and 10%
are frequently being updated (Pending/Active). This means I only vacuum once
a week. I haven't had chance to churn out objective tests yet, but my
subjective judgment is that this set of params works well:
Set SHMMAX and SHMALL in the kernel to 134217728 (128MB)
Set shared_buffers to 8192 (64MB)
Set sort_mem to 16384 (16MB)
Set effective_cache_size to 65536 (1/2 GB)
The Hardware is a dual-processor Athlon 1.2 Ghz box with 1 GB of RAM and the
DB on SCSI RAID drives.
The database size is about 8GB, with the largest table 2.5 GB, and the two
most commonly queried tables at 1 GB each.
The OS is Debian Linux kernel 2.4.x (recompiled custom kernel for dual
processor support)
The PostgreSQL version is 7.3.2
My reasoning was to increase shared_buffers based on anecdotal
recommendations I've seen on this list to 64MB and boost the OS SHMMAX to
twice that value to allow adequate room for other shared memory needs, thus
reserving 128MB. Of the remaining memory, 256MB goes to 16MB sort space
times
a guesstimate of 16 simultaneous sorts at any given time. If I leave about
128 MB for headroom, then 1/2 GB should be left available for the effective
cache size.
I've never been tempted to turn fsync off. That seems like a risky move.
Regards,
-Nick
---------------------------------------------------------------------
Nick Fankhauser
nickf@doxpop.com Phone 1.765.965.7363 Fax 1.765.962.9788
doxpop - Court records at your fingertips - http://www.doxpop.com/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: nickf@doxpop.com, justinlong@strategicnetwork.org
Subject: Re: Optimization
In-Reply-To: <NEBBLAAHGLEEPCGOBHDGOEHNHNAA.nickf@doxpop.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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