sort_mem param of postgresql.conf

On Wed, Nov 12, 2008 at 7:36 AM, Grzegorz Jaśkiewicz <gryzman@gmail.com> wrote:

Hey folks,

Anyone knows why the "sort_mem" parametr isn't by default in postgresql.conf
file ? I had no idea it existed before, and we had some hudge queries
running 1-2 minutes here, doing all sorts on disc, adding sort_mem = 512MB
to config shortened that to 20s. These are run only ocasionaly, so the
memory won't be always used - but helps in these few cases .

sort_mem is right there. If you're running an older version of pgql.
I think it turned into work_mem around 8.0 or 8.1

Are you trying to use a postgresql.conf from an older version of pgsql
with a newer version of pgsql?

that's on 8.3, by default there was no sort_mem , I hadded it , changed it
to 512MB and all of the sudent everything started to fly - wherever it
required hudge ammounts of memory for queries. The queries are reports, so
they basicaly join few tables, and dump all ofthat content - so it takes a
bit of a memory to do it ;)

thanks anyway.

Grzegorz Jaśkiewicz escribió:

that's on 8.3, by default there was no sort_mem , I hadded it , changed it
to 512MB and all of the sudent everything started to fly

sort_mem is just a convenience alias for work_mem.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Wed, Nov 12, 2008 at 3:01 PM, Alvaro Herrera
<alvherre@commandprompt.com>wrote:

sort_mem is just a convenience alias for work_mem.

point taken

GJ

On Wed, 12 Nov 2008, Grzegorz Ja�~[kiewicz wrote:

that's on 8.3, by default there was no sort_mem , I hadded it , changed it
to 512MB and all of the sudent everything started to fly - wherever it
required hudge ammounts of memory for queries. The queries are reports, so
they basicaly join few tables, and dump all ofthat content - so it takes a
bit of a memory to do it ;)

Be advised that the work_mem setting (and its deprecated alias sort_mem)
are on a per-client basis. So if you have a bunch of people running
reports with that setting, you might discover your server running out of
memory; that's a really high setting. Generally, if it's only a report or
two that need a lot more working memory for sorts, you can do this at the
beginning of them instead:

set work_mem='512MB';

Which will set the value only for that session.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

On Wed, Nov 12, 2008 at 11:08 PM, Greg Smith <gsmith@gregsmith.com> wrote:

Be advised that the work_mem setting (and its deprecated alias sort_mem)
are on a per-client basis. So if you have a bunch of people running reports
with that setting, you might discover your server running out of memory;
that's a really high setting. Generally, if it's only a report or two that
need a lot more working memory for sorts, you can do this at the beginning
of them instead:

set work_mem='512MB';

so how do I change it back to default (without knowing what the previous val
was). I suppose having it in a transaction won't do :P

--
GJ

On Thu, Nov 13, 2008 at 7:59 AM, Grzegorz Jaśkiewicz <gryzman@gmail.com> wrote:

On Wed, Nov 12, 2008 at 11:08 PM, Greg Smith <gsmith@gregsmith.com> wrote:

Be advised that the work_mem setting (and its deprecated alias sort_mem)
are on a per-client basis. So if you have a bunch of people running reports
with that setting, you might discover your server running out of memory;
that's a really high setting. Generally, if it's only a report or two that
need a lot more working memory for sorts, you can do this at the beginning
of them instead:

set work_mem='512MB';

so how do I change it back to default (without knowing what the previous val
was). I suppose having it in a transaction won't do :P

default is 1M, but you can safely run 8 to 16 Meg with your setup.

2008/11/13 Scott Marlowe <scott.marlowe@gmail.com>:

On Thu, Nov 13, 2008 at 7:59 AM, Grzegorz Jaśkiewicz <gryzman@gmail.com> wrote:

On Wed, Nov 12, 2008 at 11:08 PM, Greg Smith <gsmith@gregsmith.com> wrote:

Be advised that the work_mem setting (and its deprecated alias sort_mem)
are on a per-client basis. So if you have a bunch of people running reports
with that setting, you might discover your server running out of memory;
that's a really high setting. Generally, if it's only a report or two that
need a lot more working memory for sorts, you can do this at the beginning
of them instead:

set work_mem='512MB';

so how do I change it back to default (without knowing what the previous val
was). I suppose having it in a transaction won't do :P

default is 1M, but you can safely run 8 to 16 Meg with your setup.

Wait I might be confusing you with someone else. What's your
machine's mem, how much is shared_buffers, and what's your
max_connections?

On Thu, Nov 13, 2008 at 02:59:34PM +0000, Grzegorz Jaaakiewicz wrote:

On Wed, Nov 12, 2008 at 11:08 PM, Greg Smith <gsmith@gregsmith.com> wrote:

Generally, if it's only a report or two that
need a lot more working memory for sorts, you can do this at the beginning
of them instead:

set work_mem='512MB';

so how do I change it back to default (without knowing what the previous val
was). I suppose having it in a transaction won't do :P

If by "default" you mean whatever was in the config file, you can do:

set work_mem = default;

A transaction followed by ROLLBACK appears to work as well.

Sam

Sam Mason <sam@samason.me.uk> writes:

On Thu, Nov 13, 2008 at 02:59:34PM +0000, Grzegorz Jaaakiewicz wrote:

so how do I change it back to default (without knowing what the previous val
was). I suppose having it in a transaction won't do :P

If by "default" you mean whatever was in the config file, you can do:
set work_mem = default;
A transaction followed by ROLLBACK appears to work as well.

Other alternatives worth reading about:
RESET work_mem
SET LOCAL work_mem

regards, tom lane

On Thu, Nov 13, 2008 at 7:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Other alternatives worth reading about:
RESET work_mem
SET LOCAL work_mem

nice , thanks :)

--
GJ