Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries - Mailing list pgsql-performance

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