Thread: wal_buffers

wal_buffers

From
Ian Westmacott
Date:
Can anyone tell me what precisely a WAL buffer contains,
so that I can compute an appropriate setting for
wal_buffers (in 8.0.3)?

I know the documentation suggests there is little
evidence that supports increasing wal_buffers, but we
are inserting a large amount of data that, I believe,
easily exceeds the default 64K in a single transaction.
We are also very sensitive to write latency.

As background, we are doing a sustained insert of 2.2
billion rows in 1.3 million transactions per day.  Thats
about 1700 rows per transaction, at (roughly) 50 bytes
per row.


Re: wal_buffers

From
"Thomas F. O'Connell"
Date:
On Oct 5, 2005, at 8:23 AM, Ian Westmacott wrote:

> Can anyone tell me what precisely a WAL buffer contains,
> so that I can compute an appropriate setting for
> wal_buffers (in 8.0.3)?
>
> I know the documentation suggests there is little
> evidence that supports increasing wal_buffers, but we
> are inserting a large amount of data that, I believe,
> easily exceeds the default 64K in a single transaction.
> We are also very sensitive to write latency.
>
> As background, we are doing a sustained insert of 2.2
> billion rows in 1.3 million transactions per day.  Thats
> about 1700 rows per transaction, at (roughly) 50 bytes
> per row.

Ian,

The WAL Configuration chapter (25.2) has a pretty good discussion of
how wal_buffers is used:

http://www.postgresql.org/docs/8.0/static/wal-configuration.html

You might also take a look at Josh Berkus' recent testing on this
setting:

http://www.powerpostgresql.com/

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Strategic Open Source: Open Your i™

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)

Re: wal_buffers

From
Ian Westmacott
Date:
On Thu, 2005-10-06 at 02:39, Thomas F. O'Connell wrote:
> The WAL Configuration chapter (25.2) has a pretty good discussion of
> how wal_buffers is used:
>
> http://www.postgresql.org/docs/8.0/static/wal-configuration.html
>
> You might also take a look at Josh Berkus' recent testing on this
> setting:
>
> http://www.powerpostgresql.com/

Thanks; I'd seen the documentation, but not Josh Berkus'
testing.

For my part, I don't have a large number of concurrent
connections, only one.  But it is doing large writes,
and XLogInsert is number 2 on the profile (with
LWLockAcquire and LWLockRelease close behind).  I suppose
that is expected, but lead by the documentation I wanted
to make sure XLogInsert always had some buffer space to
play with.

    --Ian



Re: wal_buffers

From
Alvaro Herrera
Date:
On Thu, Oct 06, 2005 at 08:56:31AM -0400, Ian Westmacott wrote:
> On Thu, 2005-10-06 at 02:39, Thomas F. O'Connell wrote:
> > The WAL Configuration chapter (25.2) has a pretty good discussion of
> > how wal_buffers is used:
> >
> > http://www.postgresql.org/docs/8.0/static/wal-configuration.html
> >
> > You might also take a look at Josh Berkus' recent testing on this
> > setting:
> >
> > http://www.powerpostgresql.com/
>
> Thanks; I'd seen the documentation, but not Josh Berkus'
> testing.
>
> For my part, I don't have a large number of concurrent
> connections, only one.  But it is doing large writes,
> and XLogInsert is number 2 on the profile (with
> LWLockAcquire and LWLockRelease close behind).  I suppose
> that is expected, but lead by the documentation I wanted
> to make sure XLogInsert always had some buffer space to
> play with.

If you are using a single connection, you are wasting lots of cycles
just waiting for the disk to spin.  Were you to use multiple
connections, some transactions could be doing some useful work while
others are waiting for their transaction to be committed.

--
Alvaro Herrera                  http://www.amazon.com/gp/registry/5ZYLFMCVHXC
"I suspect most samba developers are already technically insane...
Of course, since many of them are Australians, you can't tell." (L. Torvalds)

Re: wal_buffers

From
Josh Berkus
Date:
Ian, Thomas,

> Thanks; I'd seen the documentation, but not Josh Berkus'
> testing.

BTW, that's still an open question for me.  I'm now theorizing that it's
best to set wal_buffers to the expected maximum number of concurrent write
connections.   However, I don't have enough test systems to test that
meaningfully.

Your test results will help.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco