From: | Simon Riggs <simon(at)2ndQuadrant(dot)com> |
---|---|
To: | Cody Caughlan <toolbag(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Large values for duration of COMMITs and slow queries. Due to large WAL config values? |
Date: | 2011-11-13 07:14:28 |
Message-ID: | CA+U5nMJhXSx2KkW9HfY--o2iA-DOdtN5qiCCw9KN3wS-=Gkpag@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sat, Nov 12, 2011 at 7:04 AM, Cody Caughlan <toolbag(at)gmail(dot)com> wrote:
> Postgres 9.1.1, master with 2 slaves via streaming replication.
>
> I've enabled slow query logging of 150ms and am seeing a large number
> of slow COMMITs:
>
> 2011-11-12 06:55:02 UTC pid:30897 (28/0-0) LOG: duration: 232.398 ms
> statement: COMMIT
> 2011-11-12 06:55:08 UTC pid:30896 (27/0-0) LOG: duration: 1078.789 ms
> statement: COMMIT
Could well be related to checkpoints. Please try log_checkpoints = on
and see if there is a correlation.
> Recently we have bumped up wal_keep_segments and checkpoint_segments
> because we wanted to run long running queries on the slaves and we're
> receiving cancellation errors on the slaves. I think the master was
> recycling WAL logs from underneath the slave and thus canceling the
> queries. Hence, I believed I needed to crank up those values. It seems
> to work, I can run long queries (for statistics / reports) on the
> slaves just fine.
That reasoning isn't sound because it doesn't work like that.
Recycling WAL files has nothing to do with query cancelation on hot
standby.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
From | Date | Subject | |
---|---|---|---|
Next Message | Simon Riggs | 2011-11-13 07:33:44 | Re: CLONE DATABASE (with copy on write?) |
Previous Message | Igor Polishchuk | 2011-11-13 02:49:46 | Re: CLONE DATABASE (with copy on write?) |