Обсуждение: sort_mem param of postgresql.conf

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 .

--
GJ

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

sort_mem is just a convenience alias for work_mem.

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 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