From: Bill Moran <wmoran@collaborativefusion.com>
To: pgsql-performance@postgresql.org
Subject: Advice on selecting good values for work_mem?
Date: Thu, 7 Dec 2006 11:35:31 -0500
Message-ID: <20061207113531.98095b27.wmoran@collaborativefusion.com> (raw)
I'm gearing up to do some serious investigation into performance for
PostgreSQL with regard to our application. I have two issues that I've
questions about, and I'll address them in two seperate emails.
This email regards the tuning of work_mem.
I'm planning on going through all of the queries our application does,
under various load scenarios and approaching each performance issue as
it appears.
What I'm fuzzy on is how to discretely know when I'm overflowing
work_mem? Obviously, if work_mem is exhausted by a particular
query, temp files will be created and performance will begin to suck,
but it would be nice to have some more information -- how large was
the resultant temp file, for example.
Does the creation of a temp file trigger any logging? I've yet to
see any, but we may not have hit any circumstances where work_mem
was exhausted. I've been looking through the docs at the various
pg_stat* views and functions, but it doesn't look as if there's
anything in there about this.
That leads to my other question. Assuming I've got lots of
connections (which I do), how can I determine if work_mem is too
high? Do server processes allocated it even if they don't actually
use it? Is the only way to find out to reduce it and see when it
starts to be a problem? If so, that leads back to my first question:
how can I be sure whether temp files were created or not?
My goal is to set work_mem as small as is possible for the most
common queries, then force the developers to use "set work_mem to x"
to adjust it for big queries.
--
Bill Moran
Collaborative Fusion Inc.
wmoran@collaborativefusion.com
Phone: 412-422-3463x4023
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: wmoran@collaborativefusion.com
Subject: Re: Advice on selecting good values for work_mem?
In-Reply-To: <20061207113531.98095b27.wmoran@collaborativefusion.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