Re: Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries

- I'm using ext4
- Kernel: Linux 2.6.32-279.9.1.el6.x86_64 #1 SMP Fri Aug 31 09:04:24 EDT
2012 x86_64 x86_64 x86_64 GNU/Linux
- I haven't tuned kernel in any way except setting kernel.shmmax and
kernel.shmall to:
kernel.shmmax = 68719476736
kernel.shmall = 4294967296
- We are using 15k drives (magnetic) connected through SAS in RAID10 setup,
I don't know precise model numbers (I can find out),

On 1 November 2012 15:40, Marcos Ortiz <mlortiz(at)uci(dot)cu> wrote:

> 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.
>
> 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(at)praus(dot)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-downbut a few people suggested
>>
>
>
> --
> **
>
> Marcos Luis Ortíz Valmaseda
> about.me/marcosortiz
> @marcosluis2186 <http://twitter.com/marcosluis2186>
> **
>
> <http://www.uci.cu/>
>
>