pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedwal_buffers
5+ messages / 4 participants
[nested] [flat]
* wal_buffers
@ 2005-10-05 13:23 Ian Westmacott <ianw@intellivid.com>
0 siblings, 1 reply; 5+ messages in thread
From: Ian Westmacott @ 2005-10-05 13:23 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: wal_buffers
@ 2005-10-06 06:39 Thomas F. O'Connell <tfo@sitening.com>
parent: Ian Westmacott <ianw@intellivid.com>
0 siblings, 1 reply; 5+ messages in thread
From: Thomas F. O'Connell @ 2005-10-06 06:39 UTC (permalink / raw)
To: Ian Westmacott <ianw@intellivid.com>; +Cc: pgsql-performance
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)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: wal_buffers
@ 2005-10-06 12:56 Ian Westmacott <ianw@intellivid.com>
parent: Thomas F. O'Connell <tfo@sitening.com>
0 siblings, 2 replies; 5+ messages in thread
From: Ian Westmacott @ 2005-10-06 12:56 UTC (permalink / raw)
To: Thomas F. O'Connell <tfo@sitening.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: wal_buffers
@ 2005-10-06 13:23 Alvaro Herrera <alvherre@alvh.no-ip.org>
parent: Ian Westmacott <ianw@intellivid.com>
1 sibling, 0 replies; 5+ messages in thread
From: Alvaro Herrera @ 2005-10-06 13:23 UTC (permalink / raw)
To: Ian Westmacott <ianw@intellivid.com>; +Cc: Thomas F. O'Connell <tfo@sitening.com>; pgsql-performance
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)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: wal_buffers
@ 2005-10-07 18:51 Josh Berkus <josh@agliodbs.com>
parent: Ian Westmacott <ianw@intellivid.com>
1 sibling, 0 replies; 5+ messages in thread
From: Josh Berkus @ 2005-10-07 18:51 UTC (permalink / raw)
To: pgsql-performance; +Cc: Ian Westmacott <ianw@intellivid.com>; Thomas F. O'Connell <tfo@sitening.com>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-10-07 18:51 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-05 13:23 wal_buffers Ian Westmacott <ianw@intellivid.com>
2005-10-06 06:39 ` Thomas F. O'Connell <tfo@sitening.com>
2005-10-06 12:56 ` Ian Westmacott <ianw@intellivid.com>
2005-10-06 13:23 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2005-10-07 18:51 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox