Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TU0dV-0002aG-KV for pgsql-performance@postgresql.org; Thu, 01 Nov 2012 19:41:06 +0000 Received: from mx3.uci.cu ([200.55.140.180]) by makus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1TU0dO-0006p9-Jr for pgsql-performance@postgresql.org; Thu, 01 Nov 2012 19:41:05 +0000 Received: (qmail 32443 invoked by uid 507); 1 Nov 2012 19:40:39 -0000 Received: from 10.0.0.184 by ns3.uci.cu (envelope-from , uid 501) with qmail-scanner-2.01st (avp: 5.0.2.0. spamassassin: 3.0.6. perlscan: 2.01st. Clear:RC:1(10.0.0.184):. Processed in 0.785708 secs); 01 Nov 2012 19:40:39 -0000 Received: from unknown (HELO ucimail3.uci.cu) (10.0.0.184) by 0 with SMTP; 1 Nov 2012 19:40:38 -0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by ucimail3.uci.cu (Postfix) with ESMTP id 9680C1E8C022 for ; Thu, 1 Nov 2012 15:40:38 -0400 (EDT) X-Virus-Scanned: amavisd-new at uci.cu Received: from ucimail3.uci.cu ([127.0.0.1]) by localhost (ucimail3.uci.cu [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id G6KwwScaF6GL; Thu, 1 Nov 2012 15:40:34 -0400 (EDT) Received: by ucimail3.uci.cu (Postfix, from userid 101) id 77CD51E8C019; Thu, 1 Nov 2012 15:40:34 -0400 (EDT) Received: from [10.56.30.93] (unknown [10.56.30.93]) (Authenticated sender: mlortiz@uci.cu) by ucimail3.uci.cu (Postfix) with ESMTPSA id 636F61E8C019; Thu, 1 Nov 2012 15:40:34 -0400 (EDT) Message-ID: <5092DE42.5060206@uci.cu> Date: Thu, 01 Nov 2012 15:40:34 -0500 From: Marcos Ortiz User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:16.0) Gecko/20121028 Thunderbird/16.0.2 MIME-Version: 1.0 To: Petr Praus CC: pgsql-performance@postgresql.org Subject: Re: Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries References: In-Reply-To: Content-Type: multipart/alternative; boundary="------------030001090508090203010606" X-Pg-Spam-Score: -2.6 (--) X-Archive-Number: 201211/7 X-Sequence-Number: 48373 This is a multi-part message in MIME format. --------------030001090508090203010606 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 > 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 10mo. ANIVERSARIO DE LA CREACION DE LA UNIVERSIDAD DE LAS CIENCIAS INFORMATICAS... CONECTADOS AL FUTURO, CONECTADOS A LA REVOLUCION http://www.uci.cu http://www.facebook.com/universidad.uci http://www.flickr.com/photos/universidad_uci --------------030001090508090203010606 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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@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:


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:


--


--------------030001090508090203010606--