Обсуждение: wal_buffers
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.
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)
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
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)
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