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

Marcos Ortiz <mlortiz@xxxxxx> · Thu, 01 Nov 2012 15:40:34 -0500



    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:
            

             - 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 
          
        
    -- 

      
          Marcos Luis Ortíz Valmaseda

          about.me/marcosortiz

          @marcosluis2186