From: Jeremy Haile <jhaile@fastmail.fm>
To: pgsql-performance@postgresql.org
Subject: Re: Config parameters
Date: Tue, 02 Jan 2007 15:01:56 -0500
Message-ID: <1167768116.22881.282800447@webmail.messagingengine.com> (raw)
In-Reply-To: <1167767485.29707.25.camel@state.g2switchworks.com>
References: <1167754933.1316.282766279@webmail.messagingengine.com>
<459A898B.9020509@archonet.com>
<1167757584.31784.282774689@webmail.messagingengine.com>
<459AA952.80008@archonet.com>
<1167765598.16831.282794437@webmail.messagingengine.com>
<1167767485.29707.25.camel@state.g2switchworks.com>
> So, on a 4 Gig machine you could divide 1G (25%) by the total possible
> connections, then again by the average number of sorts you'd expect per
> query / connection to get an idea.
Thanks for the advice. I'll experiment with higher work_mem settings,
as I am regularly doing sorts on large datasets. I imagine the default
setting isn't very optimal in my case.
> Did you turn off stats collection as well? That's really the major
> performance issue with autovacuum, not autovacuum itself.
I did turn off stats collection. I'm not sure how much of a difference
it makes, but I was trying to squeeze every ounce of performance out of
the database.
> I.e. the cure may be worse than the disease. OTOH, if you don't delete
> / update often, then don't worry about it.
I hardly ever delete/update. I update regularly, but only on small
tables so it doesn't make as big of a difference. I do huge inserts,
which is why turning off stats/autovacuum gives me some performance
benefit. I usually only do deletes nightly in large batches, so
autovacuuming/analyzing once an hour works fairly well.
> Haven't researched temp_buffers at all.
Do you usually change temp_buffers? Mine is currently at the default
setting. I guess I could arbitrarily bump it up - but I'm not sure what
the consequences would be or how to tell if it is set correctly.
> random_page_cost is the hardest to come up with the proper setting.
This definitely sounds like the hardest to figure out. (since it seems
to be almost all trial-and-error) I'll play with some different values.
This is only used by the query planner right? How much of a
performance difference does it usually make to tweak this number? (i.e.
how much performance difference would someone usually expect when they
find that 2.5 works better than 4?)
> While you can't
> change buffers on the fly, you can change work_mem and random_page_cost
> on the fly, per connection, to see the change.
Thanks for the advice. I was aware you could change work_mem on the
fly, but didn't think about setting random_page_cost on-the-fly.
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: jhaile@fastmail.fm
Subject: Re: Config parameters
In-Reply-To: <1167768116.22881.282800447@webmail.messagingengine.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