From: | Andres Freund <andres(at)2ndquadrant(dot)com> |
---|---|
To: | Joe Conway <mail(at)joeconway(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "Hackers (PostgreSQL)" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Excessive WAL generation and related performance issue |
Date: | 2014-04-14 23:25:32 |
Message-ID: | 20140414232532.GB4161@awork2.anarazel.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 2014-04-14 16:22:48 -0700, Joe Conway wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On 04/14/2014 04:17 PM, Tom Lane wrote:
> > Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> >> On 2014-04-14 14:33:03 -0700, Joe Conway wrote:
> >>> checkpoint_segments = 96 checkpoint_timeout = 10min
> >
> >> I bet you'll see noticeably - while still not great - better
> >> performance by setting checkpoint_timeout to an hour (with a
> >> corresponding increase in checkpoint_segments). Have you checked
> >> how often checkpoints are actually created? I'd bet it's far more
> >> frequent than every 10min with that _segments setting and such a
> >> load.
> >
> > My thoughts exactly. It's not hard to blow through WAL at
> > multiple megabytes per second with modern machines. I'd turn on
> > checkpoint logging and then do whatever you need to do to get the
> > actual intercheckpoint time up to 10-15 minutes at least.
>
> That'll help performance, but lets say I generally keep WAL files for
> PITR and don't turn that off before starting -- shouldn't I be very
> surprised to need over 3TB of archive storage when loading a 50GB
> table with a couple of indexes?
The point is that more frequent checkpoints will increase the WAL volume
*significantly* because more full page writes will have to be generated.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
From | Date | Subject | |
---|---|---|---|
Next Message | Joe Conway | 2014-04-14 23:34:56 | Re: Excessive WAL generation and related performance issue |
Previous Message | Joe Conway | 2014-04-14 23:22:48 | Re: Excessive WAL generation and related performance issue |