From: Greg Smith <gsmith@gregsmith.com>
To: Douglas J Hunley <doug@hunley.homeip.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x
Date: Sun, 3 Jun 2007 18:30:17 -0400 (EDT)
Message-ID: <Pine.GSO.4.64.0706031755370.7604@westnet.com> (raw)
In-Reply-To: <200706031324.15605.doug@hunley.homeip.net>
References: <200706020913.32241.doug@hunley.homeip.net>
<20070602152141.GA38653@winnie.fuhr.org>
<200706031324.15605.doug@hunley.homeip.net>
On Sun, 3 Jun 2007, Douglas J Hunley wrote:
>> commit_delay = 20000
>> commit_siblings = 3
> Those are based on a thread their (non-pgsql) DBA found online. I'm perfectly
> willing to discount him if so advised.
Those likely came indirectly from the otherwise useful recommendations at
http://www.wlug.org.nz/PostgreSQLNotes , that's the first place I saw that
particular combination recommended at. The fact that you mention a thread
makes me guess your DBA found
https://kb.vasoftware.com/index.php?x=&mod_id=2&id=20 , which is a
completely bogus set of suggestions. Anyone who gives out a blanket
recommendation for any PostgreSQL performance parameter without asking
questions first about things like your memory and your disk setup doesn't
really know what they're doing, and I'd suggest discounting the entirety
of that advice.
Those commit_ values are completely wrong for many workloads; they're
introducing a 20ms delay into writes as soon as there are more then 3
clients writing things at once. If someone just took those values from a
web page without actually testing them out, you'd be better off turning
both values back to the defaults (which disables the feature) and waiting
until you have some time to correctly determine useful settings for your
system.
Note that with fsync=off, I don't think that's actually doing anything
right now so it's kind of irrelevant to get excited about; should be
addressed before fsync gets turned back on though.
Also: some of the recommendations you've been getting for shared_buffers
are on the low side as far as I'm concerned. You should consider maxing
that value out at 262143 (2GB of RAM) on your server with 8GB of RAM
available, then putting effective_cache_size at 5GB or so. That may
require just a bit more upward tweaking of your kernel parameters to
support.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
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: gsmith@gregsmith.com, doug@hunley.homeip.net
Subject: Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x
In-Reply-To: <Pine.GSO.4.64.0706031755370.7604@westnet.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