Re: Shared Buffer Size

2011/5/31 Toby Corkindale <toby(dot)corkindale(at)strategicdata(dot)com(dot)au>:
> On 30/05/11 20:41, Cédric Villemain wrote:
>>
>> 2011/5/30 Toby Corkindale<toby(dot)corkindale(at)strategicdata(dot)com(dot)au>:
>>>
>>> On 28/05/11 18:42, Carl von Clausewitz wrote:
>>>>
>>>> a few months ago, when I installed my first PostgreSQL, I have had the
>>>> same problem. I've try to get any information about optimal memory
>>>> config, and working, but there wasn't any "optimal memory setting
>>>> calculator" on the internet, just some guide in the posgre documentation
>>>>
>>>>
>>>> (http://www.postgresql.org/docs/9.0/interactive/kernel-resources.html#SYSVIPC).
>>>> I got FreeBSD 8.2 AMD64, with 8 GB of memory (this server is just for
>>>> PostgreSQL and a little PHP app with 2 user), and I have theese setting
>>>> in postgresql.conf (which are not the default):
>>>>
>>> [snip]
>>>>
>>>> work_mem = 64MB# min 64kB
>>>> maintenance_work_mem = 1024MB# min 1MB
>>>> max_stack_depth = 64MB# min 100kB
>>>
>>> Just a warning - but be careful about setting work_mem to high values.
>>> The actual memory used by a query can be many times the value, depending
>>> on
>>> the complexity of your query.
>>>
>>> In a particular query I saw last week, we were regularly exceeding the
>>> available memory on a server, because the query was requiring 80 times
>>> the
>>> value of work_mem, and work_mem had been set to a high value.
>>>
>>> Reducing work_mem back to just 4MB reduced memory usage by a couple of
>>> gigabytes, and had almost no effect on the execution time. (Actually, it
>>> was
>>> marginally faster - probably because more memory was left for the
>>> operating
>>> system's cache)
>>
>> Maybe, you're also aware that linux may decide to swap to protect its
>> buffer cache (depend of the strategy it got in its configuration) and
>> also that you may be limited by commitable memory. On a default
>> install where the swap is NOT at least twice the RAM size, you're not
>> able to commit all RAM you have. But, it protects the buffer cache for
>> the not allocatable memory.
>>
>> So maybe you've hitten a step where you did swap your work_mem...
>> anyway interesting to have a query where a large work_mem is not
>> better... Will it be hard to isolate the case and make it public ? In
>> the long term it might be a good test to add to a performance farm if
>> it is not based on a non-optimum linux configuration (I mean if the
>> issue *need* the work_mem to be reduced to be fixed).
>
>
> In this case, it was not just slowing down due to the amount of work_mem
> allocated -- it was exceeding several gigabytes of memory usage and crashing
> out. Lower values of work_mem allowed the query to succeed, but it used
> almost 3G.. Even lower values of work_mem could do the query in only a few
> hundred MB - and was faster.
>
> I note that if you exceed work_mem in a query,then I guess the temp files
> created are cached by the VM cache, so it's not like the performance hit
> will be *too* bad?

correct.

>
>
> I agree that the slowness of the 3GB version could be due to swapping or
> something like that.. or just due to the VM cache being eliminated as I
> suggested.
>
> Either way - the problem was that this (machine-generated) query was
> pivoting and joining many views-of-views. It's a pretty nasty query.
>
> The key fact is that postgres (8.3) seems to allocate the full work_mem
> amount every time it needs *some* work_mem - even if it could have happily
> got by on just a few MB. So if your query allocates work_mem a hundred
> times, it'll consume $work_mem * 100 -- or die trying.

work_mem is not allocated fully from the beginning. It is allocated
initialy at XXKB (or MB), then doubled each time we are near the end
of the allocated memory. (I am unsure of detail like when exactly we
alloc more mem but the logic is this one)

>
> I'm curious to know if Postgres 9.0 has improved this -- I'm going to try
> re-running this query on it once I get a chance, but due to contractual
> agreements this isn't quite as simple to test as you might think.
> (And running the test over a much smaller example data set might not trigger
> the same query plan)
> I'll get there eventually though :)
>

9.0 may help you for generated queries so it is worth testing it I think.

--
Cédric Villemain               2ndQuadrant
http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support