Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD