Обсуждение: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries

I just found one particularly interesting fact: when I perform the same test on my mid-2010 iMac (OSX 10.7.5) also with Postgres 9.2.1 and 16GB RAM, I don't experience the slow down.

Regards, Petr.
Tuning PostgreSQL is not just change the postgresql.conf, it includes more things like:
- the filesystem that you are using
- the kernel version that you using (particularly in Linux systems)
- the tuning to kernel variables
- the type of discs that you are using (SSDs are very fast, like you saw in your iMac system)

I just found one particularly interesting fact: when I perform the same test on my mid-2010 iMac (OSX 10.7.5) also with Postgres 9.2.1 and 16GB RAM, I don't experience the slow down.

Specifically:

set work_mem='1MB';

select ...; // running time is ~1800 ms

set work_mem='96MB';

select ...' // running time is ~1500 ms

When I do exactly the same query (the one from my previous post) with exactly the same data on the server:

I get 2100 ms with work_mem=1MB and 3200 ms with 96 MB.

The Mac has SSD so it's understandably faster, but it exhibits a behavior I would expect. What am I doing wrong here?

Thanks.

On 30 October 2012 14:08, Petr Praus <petr@praus.net> wrote:

Hello,

I have a PostgreSQL 9.2 instance running on RHEL 6.3, 8-core machine with 16GB of RAM. The server is dedicated to this database, the disks are local RAID10. Given that the default postgresql.conf is quite conservative regarding memory settings, I thought it might be a good idea to allow Postgres to use more memory. To my surprise, following advice in the performance tuning guide on Postgres wiki[2] significantly slowed down practically every query I run but it's more noticeable on the more complex queries.

I also tried running pgtune[1] which gave the following recommendation with more parameters tuned, but that didn't change anything. It suggests shared_buffers of 1/4 of RAM size which seems to in line with advice elsewhere (and on PG wiki in particular).

    default_statistics_target = 50

    maintenance_work_mem = 960MB

    constraint_exclusion = on

    checkpoint_completion_target = 0.9

    effective_cache_size = 11GB

    work_mem = 96MB

    wal_buffers = 8MB

    checkpoint_segments = 16

    shared_buffers = 3840MB

    max_connections = 80

I tried reindexing the whole database after changing the settings (using REINDEX DATABASE), but that didn't help either. I played around with shared_buffers and work_mem. Gradually changing them from the very conservative default values (128k / 1MB) also gradually decreased performance.

I ran EXPLAIN (ANALYZE,BUFFERS) on a few queries and the culprit seems to be that Hash Join is significantly slower. It's not clear to me why.

To give some specific example, I have the following query. It runs in ~2100ms on the default configuration and ~3300ms on the configuration with increased buffer sizes:

    select count(*) from contest c

    left outer join contestparticipant cp on c.id=cp.contestId

    left outer join teammember tm on tm.contestparticipantid=cp.id

    left outer join staffmember sm on cp.id=sm.contestparticipantid

    left outer join person p on p.id=cp.personid

    left outer join personinfo pi on pi.id=cp.personinfoid

    where pi.lastname like '%b%' or pi.firstname like '%a%';

EXPLAIN (ANALYZE,BUFFERS) for the query above:

 - Default buffers: http://explain.depesz.com/s/xaHJ

 - Bigger buffers: http://explain.depesz.com/s/Plk

The tables don't have anything special in them

The question is why am I observing decreased performance when I increase buffer sizes? The machine is definitely not running out of memory. Allocation if shared memory in OS is (`shmmax` and `shmall`) is set to very large values, that should not be a problem. I'm not getting any errors in the Postgres log either. I'm running autovacuum in the default configuration but I don't expect that has anything to do with it. All queries were run on the same machine few seconds apart, just with changed configuration (and restarted PG).

I also found a blog post [3] which experiments with various work_mem values that run into similar behavior I'm experiencing but it doesn't really explain it.

  [1]: http://pgfoundry.org/projects/pgtune/

  [2]: http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

  [3]: http://www.depesz.com/2011/07/03/understanding-postgresql-conf-work_mem/

Thanks,

Petr Praus

PS:

I also posted the question here: http://dba.stackexchange.com/questions/27893/increasing-work-mem-and-shared-buffers-on-postgres-9-2-significantly-slows-down but a few people suggested 

Regards, Petr.
Tuning PostgreSQL is not just change the postgresql.conf, it includes more things like:
- the filesystem that you are using
- the kernel version that you using (particularly in Linux systems)
- the tuning to kernel variables
- the type of discs that you are using (SSDs are very fast, like you saw in your iMac system)

On 10/30/2012 02:44 PM, Petr Praus wrote:

I just found one particularly interesting fact: when I perform the same test on my mid-2010 iMac (OSX 10.7.5) also with Postgres 9.2.1 and 16GB RAM, I don't experience the slow down.

Specifically:

set work_mem='1MB';

select ...; // running time is ~1800 ms

set work_mem='96MB';

select ...' // running time is ~1500 ms

When I do exactly the same query (the one from my previous post) with exactly the same data on the server:

I get 2100 ms with work_mem=1MB and 3200 ms with 96 MB.

Just some thoughts (interested in this, once seen a Sybase ASE come close to a halt when we threw a huge lot of SHM at it...).

8 cores, so probably on 2 sockets? What CPU generation?

Both explain outputs show an amount of "read" buffers. Did you warm the caches before testing?

Maybe you're hitting a NUMA issue there? If those reads come from the OS' cache, the scheduler might decide to move your process to a different core (that can access the cache better), then moves it back when you access the SHM segment more (the ~4GB get allocated at startup, so probably "close" to the CPU the postmaster ist running on). A migration to a different cacheline is very expensive.

The temp reads/writes (i.e., the OS cache for the temp files) would probably be allocated close to the CPU requesting the temp file.

Just groping about in the dark though... but the iMac is obviously not affected by this, with one socket/memory channel/cache line.

Might be worth to
- manually pin (with taskset) the session you test this in to a particular CPU (once on each socket) to see if the times change
- try reducing work_mem in the session you're testing in (so you have large SHM, but small work mem)

Cheers,

-- 
Gunnar "Nick" Bluth
RHCE/SCLA

Mobil   +49 172 8853339
Email: gunnar.bluth@pro-open.de
__________________________________________________________________________
In 1984 mainstream users were choosing VMS over UNIX.  Ten years later
they are choosing Windows over UNIX.  What part of that message aren't you
getting? - Tom Payne

Two possibilities:

caching.  make sure to run each query several times in a row.

zone reclaim mode. If this has gotten turned on turn it back off.

How to tell:

sysctl -n vm.zone_reclaim_mode

Output should be 0.  If it's not, then add this to /etc/sysctl.conf:

vm.zone_reclaim_mode=0

and run: sudo sysctl -p

and see if that helps.

Might be worth to
- manually pin (with taskset) the session you test this in to a particular CPU (once on each socket) to see if the times change

I tested this and it does not seem to have any effect (assuming I used taskset correctly but I think so: taskset 02 psql to pin down to CPU #1 and taskset 01 psql to pin to CPU #0).

Well, that pinned your _client_ to the CPUs, not the server side session ;-)
You'd have to spot for the PID of the new "IDLE" server process and pin that using "taskset -p". Also, 01 and 02 are probably cores in the same package/socket. Try "lscpu" first and spot for "NUMA node*" lines at the bottom.
But anyway... let's try something else first:

 

- try reducing work_mem in the session you're testing in (so you have large SHM, but small work mem)

Did this and it indicates to me that shared_buffers setting actually does not have an effect on this behaviour as I previously thought it has. It really boils down to work_mem: when I set shared_buffers to something large (say 4GB) and just play with work_mem the problem persists.

This only confirms what we've seen before. As soon as your work_mem permits an in-memory sort of the intermediate result set (which at that point in time is where? In the SHM, or in the private memory of the backend? I can't tell, tbth), the sort takes longer than when it's using a temp file.

What if you reduce the shared_buffers to your original value and only increase/decrease the session's work_mem? Same behaviour?

Cheers,

-- 
Gunnar "Nick" Bluth
RHCE/SCLA

Mobil   +49 172 8853339
Email: gunnar.bluth@pro-open.de
__________________________________________________________________________
In 1984 mainstream users were choosing VMS over UNIX.  Ten years later
they are choosing Windows over UNIX.  What part of that message aren't you
getting? - Tom Payne

Your CPUs are indeed pretty oldschool. FSB based, IIRC, not NUMA. A process migration would be even more expensive there.

Ok, I've actually looked these up now... at the time these were current, I was in the lucky situation to only deal with Opterons. And actually, with these CPUs it is pretty possible that Scott Marlowe's hint (check vm.zone_reclaim_mode) was pointing in the right direction. Did you check that?

Yes, same behaviour. I let the shared_buffers be the default (which is 8MB). With work_mem 1MB the query runs fast, with 96MB it runs slow (same times as before). It really seems that the culprit is work_mem.

Well, I'm pretty sure that having more work_mem is a good thing (tm) normally ;-)

-- 
Gunnar "Nick" Bluth
RHCE/SCLA

Mobil   +49 172 8853339
Email: gunnar.bluth@pro-open.de
__________________________________________________________________________
In 1984 mainstream users were choosing VMS over UNIX.  Ten years later
they are choosing Windows over UNIX.  What part of that message aren't you
getting? - Tom Payne

On 3 November 2012 12:09, Gunnar "Nick" Bluth <gunnar.bluth@pro-open.de> wrote:

Am 03.11.2012 16:20, schrieb Petr Praus:

Your CPUs are indeed pretty oldschool. FSB based, IIRC, not NUMA. A process migration would be even more expensive there.

Ok, I've actually looked these up now... at the time these were current, I was in the lucky situation to only deal with Opterons. And actually, with these CPUs it is pretty possible that Scott Marlowe's hint (check vm.zone_reclaim_mode) was pointing in the right direction. Did you check that?

I did check that, it's zero. I responded to his message, but my messages to the mailing list are getting delayed by ~24 hours because somebody has to always bless them.

 

Yes, same behaviour. I let the shared_buffers be the default (which is 8MB). With work_mem 1MB the query runs fast, with 96MB it runs slow (same times as before). It really seems that the culprit is work_mem.

Well, I'm pretty sure that having more work_mem is a good thing (tm) normally ;-)

Well, that's what I always thought too! :-) 

 

So, to sum this up (and make someone more competent bite on it maybe ;-), on your SMP, FSB, "fake-multicore" system all "hash"-related works that potentially switch to different implementations internally (but w/out telling us so) when given more work_mem are slower.

I'm pretty sure you're hitting some subtle, memory-access-related cornercase here.

The L2 cache of your X7350 CPUs is 2MB, could you run the tests with, say, 1, 2, 4 and 8MB of work_mem and post the results?

-- 
Gunnar "Nick" Bluth
RHCE/SCLA

Mobil   +49 172 8853339
Email: gunnar.bluth@pro-open.de
__________________________________________________________________________
In 1984 mainstream users were choosing VMS over UNIX.  Ten years later
they are choosing Windows over UNIX.  What part of that message aren't you
getting? - Tom Payne

I did run each query several times, the results I posted are for ~10th run of the query.

On 3 November 2012 12:09, Gunnar "Nick" Bluth <gunnar.bluth@pro-open.de> wrote:

On 3 November 2012 05:31, Gunnar "Nick" Bluth <gunnar.bluth@pro-open.de> wrote:

On Sat, Nov 3, 2012 at 10:09 AM, Gunnar "Nick" Bluth
<gunnar.bluth@pro-open.de> wrote:

> Well, I'm pretty sure that having more work_mem is a good thing (tm)
> normally ;-)

In my experience when doing sorts in isolation, having more work_mem
is a bad thing, unless it enables you to remove a layer of
tape-merging.  I always blamed it on the L1/L2 etc. levels of caching.

Cheers,

Jeff

On Mon, Nov 5, 2012 at 1:44 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
>> Well, I'm pretty sure that having more work_mem is a good thing (tm)
>> normally ;-)
>
> In my experience when doing sorts in isolation, having more work_mem
> is a bad thing, unless it enables you to remove a layer of
> tape-merging.  I always blamed it on the L1/L2 etc. levels of caching.

Blame it on quicksort, which is quite cache-unfriendly.

Perhaps PG should consider using in-memory mergesort for the bigger chunks.

On Mon, Nov 5, 2012 at 8:48 AM, Claudio Freire <klaussfreire@gmail.com> wrote:
> On Mon, Nov 5, 2012 at 1:44 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
>>> Well, I'm pretty sure that having more work_mem is a good thing (tm)
>>> normally ;-)
>>
>> In my experience when doing sorts in isolation, having more work_mem
>> is a bad thing, unless it enables you to remove a layer of
>> tape-merging.  I always blamed it on the L1/L2 etc. levels of caching.
>
> Blame it on quicksort, which is quite cache-unfriendly.

The observation applies to heap sort.  If you can't set work_mem large
enough to do the sort in memory, then you want to set it just barely
large enough to avoid two layers of tape sorting.  Any larger than
that reduces performance rather than increasing it.  Of course that
assumes you have the luxury of knowing ahead of time exactly how large
your sort will be and can set work_mem accordingly on a case by case
basis, which is unlikely in the real world.

> Perhaps PG should consider using in-memory mergesort for the bigger chunks.

Cheers,

Jeff

On Mon, Nov 5, 2012 at 2:09 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
>>> In my experience when doing sorts in isolation, having more work_mem
>>> is a bad thing, unless it enables you to remove a layer of
>>> tape-merging.  I always blamed it on the L1/L2 etc. levels of caching.
>>
>> Blame it on quicksort, which is quite cache-unfriendly.
>
> The observation applies to heap sort.

Well, heapsort is worse, but quicksort is also quite bad.

On Mon, Nov 5, 2012 at 2:40 PM, Claudio Freire <klaussfreire@gmail.com> wrote:
> On Mon, Nov 5, 2012 at 2:09 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
>>>> In my experience when doing sorts in isolation, having more work_mem
>>>> is a bad thing, unless it enables you to remove a layer of
>>>> tape-merging.  I always blamed it on the L1/L2 etc. levels of caching.
>>>
>>> Blame it on quicksort, which is quite cache-unfriendly.
>>
>> The observation applies to heap sort.
>
> Well, heapsort is worse, but quicksort is also quite bad.

Here[0], an interesting analysis. I really believe quicksort in PG
(due to its more complex datatypes) fares a lot worse.

[0]
http://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=2&ved=0CD0QFjAB&url=http%3A%2F%2Fwww.cs.auckland.ac.nz%2F~mcw%2FTeaching%2Frefs%2Fsorting%2Fladner-lamarca-cach-sorting.pdf&ei=PPqXUMnEL9PaqQHntoDgDQ&usg=AFQjCNE3mDf6ydj1MHUzfQw13TccOa895A

Am 05.11.2012 18:09, schrieb Jeff Janes:
> On Mon, Nov 5, 2012 at 8:48 AM, Claudio Freire <klaussfreire@gmail.com> wrote:
>> On Mon, Nov 5, 2012 at 1:44 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
>>>> Well, I'm pretty sure that having more work_mem is a good thing (tm)
>>>> normally ;-)
>>> In my experience when doing sorts in isolation, having more work_mem
>>> is a bad thing, unless it enables you to remove a layer of
>>> tape-merging.  I always blamed it on the L1/L2 etc. levels of caching.
>> Blame it on quicksort, which is quite cache-unfriendly.
> The observation applies to heap sort.  If you can't set work_mem large
> enough to do the sort in memory, then you want to set it just barely
> large enough to avoid two layers of tape sorting.  Any larger than
> that reduces performance rather than increasing it.  Of course that
> assumes you have the luxury of knowing ahead of time exactly how large
> your sort will be and can set work_mem accordingly on a case by case
> basis, which is unlikely in the real world.
>
>> Perhaps PG should consider using in-memory mergesort for the bigger chunks.
I don't want to be the party pooper here, but when you have another look
at the EXPLAINs, you'll realize that there's not a single sort involved.
The expensive parts are HASH, HASH JOIN and HASH RIGHT JOIN (although
the SeqScan takes longer as well, for whatever reason). In those parts,
the difference is clearly in the # of buckets and batches. So to a
degree, PG even does tell us that it uses a different code path (sorry,
PG ;-)...

Greg Smith mentions an optimization wrt. Hash Joins that can become a
pitfall. His advise is to increase the statistic targets on the hashed
outer relation. Might be worth a try.

--
Gunnar "Nick" Bluth
RHCE/SCLA

Mobil   +49 172 8853339
Email: gunnar.bluth@pro-open.de
__________________________________________________________________________
In 1984 mainstream users were choosing VMS over UNIX.  Ten years later
they are choosing Windows over UNIX.  What part of that message aren't you
getting? - Tom Payne

Yes, but note that this happens only in Linux. Increasing work_mem on my iMac increases performance (but the queries are slower under OSX than on virtualized Ubuntu on the same machine). Over the weekend, I tried the same test on my Ubuntu home machine with Ivy Bridge i5 3570K and it also slows down (from ~900ms with work_mem=1MB to ~1200ms with work_mem=96MB).

OS X is rather different from a memory access point of view, IIRC. So the direct comparison actually only shows how well the Linux FS cache works (for the temp files created with small work_mem ;-).

The i5 puzzles me a bit though...

I'm pretty sure you're hitting some subtle, memory-access-related cornercase here.

The L2 cache of your X7350 CPUs is 2MB, could you run the tests with, say, 1, 2, 4 and 8MB of work_mem and post the results?

I made a pgbench test with the same query and run it 25 times (5 clients, 5 transactions each):

work_mem   speed

1MB        1794ms

2MB        1877ms

4MB        2084ms

8MB        2141ms

10MB       2124ms

12MB       3018ms

16MB       3004ms

32MB       2999ms

64MB       3015ms

It seems that there is some sort of "plateau".

Two, afaics. The 1->2 change hints towards occasionally breaching your L2 cache, so it can probably be ignored. The actual plateaus thus seem to be 0-2, 2-12, >= 12.
It'd be interesting to see the EXPLAIN ANALYSE outputs for these levels, the buckets and batches in particular. I'd reckon we'll see significant changes at 2->4 and 10->12MB work_mem.

So, to sum this up (and make someone more competent bite on it maybe ;-), on your SMP, FSB, "fake-multicore" system all "hash"-related works that potentially switch to different implementations internally (but w/out telling us so) when given more work_mem are slower.

See other post... it actually does tell us (# of buckets/batches). However, the result is not good and could potentially be improved be twealing the statistic_targets of the joined tables/columns.

I wonder why noone actually understanding the implementation chipped in yet... Andres, Greg, Tom, whoever actually understands what's happening here, anyone reading this? ;-)

Cheers,

-- 
Gunnar "Nick" Bluth
RHCE/SCLA

Mobil   +49 172 8853339
Email: gunnar.bluth@pro-open.de
__________________________________________________________________________
In 1984 mainstream users were choosing VMS over UNIX.  Ten years later
they are choosing Windows over UNIX.  What part of that message aren't you
getting? - Tom Payne

Am 06.11.2012 21:08, schrieb Petr Praus:
>
> 2MB: http://explain.depesz.com/s/ul1
> 4MB: http://explain.depesz.com/s/IlVu
> 10MB: http://explain.depesz.com/s/afx3
> 12MB: http://explain.depesz.com/s/i0vQ
>
See the change in the plan between 10MB and 12MB, directly at top level?
That narrows the thing down quite a bit.

Though I wonder why this didn't show in the original plans...

--
Gunnar "Nick" Bluth
RHCE/SCLA

Mobil   +49 172 8853339
Email: gunnar.bluth@pro-open.de
__________________________________________________________________________
In 1984 mainstream users were choosing VMS over UNIX.  Ten years later
they are choosing Windows over UNIX.  What part of that message aren't you
getting? - Tom Payne

On 6 November 2012 14:17, Gunnar "Nick" Bluth <gunnar.bluth@pro-open.de> wrote:

Am 06.11.2012 21:08, schrieb Petr Praus:

2MB: http://explain.depesz.com/s/ul1
4MB: http://explain.depesz.com/s/IlVu
10MB: http://explain.depesz.com/s/afx3
12MB: http://explain.depesz.com/s/i0vQ

See the change in the plan between 10MB and 12MB, directly at top level? That narrows the thing down quite a bit.

Though I wonder why this didn't show in the original plans...

Yes, the 2,4 and 10 are the same, the only difference is number of buckets. But with 12, it makes completely different choices, it decides to make sequential scans and hash right joins instead of merge joins. And those sequential scans take a loong time. Could this be caused by some missing indices perhaps?

Well, you do have indices, as we can clearly see.

The original plans I posted at the start are the same as the 12MB plan, I'm not sure why is that, I really hope I didn't make some sort of mistake there.

I had been wondering why you didn't have any indices, tbth. However, the execution times still grow with work_mem, which is interesting independent of the actual plan change...

Thanks for your help by the way! :-)

 

Oh, no worries there... this is by far the most interesting challenge I've encountered in months ;-)

But I do admit that I've reached the end of the ladder now. No idea how you can improve your runtime yet. Probably
- using full text search on "personinfo"
- try different join_collapse_limit / from_collapse_limit / enable_hashjoin values

The most pragmatic approach is probably to just stick with work_mem = 1MB (or less) ;-), but that may potentially bite you later.

-- 
Gunnar "Nick" Bluth
RHCE/SCLA

Mobil   +49 172 8853339
Email: gunnar.bluth@pro-open.de
__________________________________________________________________________
In 1984 mainstream users were choosing VMS over UNIX.  Ten years later
they are choosing Windows over UNIX.  What part of that message aren't you
getting? - Tom Payne

On 2012-10-30 14:08:56 -0500, Petr Praus wrote:
>     select count(*) from contest c
>     left outer join contestparticipant cp on c.id=cp.contestId
>     left outer join teammember tm on tm.contestparticipantid=cp.id
>     left outer join staffmember sm on cp.id=sm.contestparticipantid
>     left outer join person p on p.id=cp.personid
>     left outer join personinfo pi on pi.id=cp.personinfoid
>     where pi.lastname like '%b%' or pi.firstname like '%a%';

Btw, not really related to the question, but the way you use left joins
here doesn't really make sense and does lead to inferior plans.
As you restrict on 'pi', the rightmost table in a chain of left joins,
there is no point in all those left joins. I would guess the overall
plan is better if use straight joins.

Greetings,

Andres Freund

--
 Andres Freund                       http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On 4 November 2012 02:48, Gunnar "Nick" Bluth <gunnar.bluth@pro-open.de> wrote:

On 6 November 2012 13:38, Gunnar "Nick" Bluth <gunnar.bluth@pro-open.de> wrote:

On 6 November 2012 14:17, Gunnar "Nick" Bluth <gunnar.bluth@pro-open.de> wrote:

On 6 November 2012 14:50, Gunnar "Nick" Bluth <gunnar.bluth@pro-open.de> wrote: