Re: in-memory sorting

On Thu, Aug 19, 2010 at 12:06 AM, Samuel Gendler
<sgendler(at)ideasculptor(dot)com>wrote:

> Incidentally, if I set values on the connection before querying, is there
> an easy way to get things back to default values or will my code need to
> know the prior value and explicitly set it back? Something like
>
> <get connection from pool>
> set work_mem = '512MB'
> query
> set value = 'default'
> <return connection to pool>
>
> or maybe
>
> <get connection from pool>
> BEGIN;
> set work_mem='512MB'
> select query
> ROLLBACK;
> <return connection to pool>
>
>
I guess I'm getting the hang of this whole postgres thing because those were
both wild guesses and both of them appear to work.

set work_mem=default sets it to the value in the config file, and setting
within a transaction and rolling back also restores the original value.

>
> On Wed, Aug 18, 2010 at 11:52 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>wrote:
>
>> Exactly, it's about the concurrency. I have a server with 128G ram
>> but it runs dozens of queries at a time for hundreds of clients a
>> second. The chance that something big for work_mem might jump up and
>> bite me are pretty good there. Even so, at 16Meg it's not really big
>> for that machine, and I might test cranking it up. Note that large
>> work_mem can cause the kernel to flush its cache, which means going to
>> disk for everybody's data, and all the queries are slow instead of
>> one. Keep an eye on how high work_mem affects your kernel cache.
>>
>> On Thu, Aug 19, 2010 at 12:38 AM, Samuel Gendler
>> <sgendler(at)ideasculptor(dot)com> wrote:
>> > Yeah, although with 48GB of available memory and not that much
>> concurrency,
>> > I'm not sure it matters that much. But point taken, I'll see about
>> modifying
>> > the app such that work_mem gets set on a per-query basis.
>> >
>> > On Wed, Aug 18, 2010 at 11:24 PM, Scott Marlowe <
>> scott(dot)marlowe(at)gmail(dot)com>
>> > wrote:
>> >>
>> >> On Wed, Aug 18, 2010 at 11:45 PM, Samuel Gendler
>> >> <sgendler(at)ideasculptor(dot)com> wrote:
>> >> > Answered my own question. Cranking work_mem up to 350MB revealed
>> that
>> >> > the in-memory sort requires more memory than the disk sort.
>> >>
>> >> Note that unless you run VERY few client connections, it's usually
>> >> better to leave work_mem somewhere in the 1 to 32Meg range and have
>> >> the connection or user or database that needs 350Meg be set there.
>> >>
>> >> I.e.
>> >>
>> >> <connect>
>> >> set work_mem='512MB';
>> >> <execute query
>> >>
>> >> OR
>> >>
>> >> alter user memoryhog set work_mem='512MB';
>> >>
>> >> OR
>> >>
>> >> alter database memhogdb set work_mem='512MB';
>> >
>> >
>>
>>
>>
>> --
>> To understand recursion, one must first understand recursion.
>>
>
>