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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



- 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@xxxxxx> 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@xxxxxxxxx> 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:


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.


Thanks,
Petr Praus

PS:


--

Marcos Luis Ortíz Valmaseda
about.me/marcosortiz
@marcosluis2186





[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux