XLOG_BLCKSZ vs. wal_buffers table

On Sun, 2006-04-30 at 22:14 -0700, Mark Wong wrote:

I would have gotten this out sooner but I'm having trouble with our
infrastructure. Here's a link to a table of data I've started putting
together regarding XLOG_BLCKSZ and wal_buffers on a 4-way Opteron
system:
http://developer.osdl.org/markw/pgsql/xlog_blcksz.html

There are a couple of holes in the table but I think it shows enough
evidence to say that with dbt2 having a larger XLOG_BLCKSZ improves the
overall throughput of the test.

I'm planning on continuing to increase XLOG_BLCKSZ and wal_buffers to
determine when the throughput starts to level out or drop off, and then
start experimenting with varying BLCKSZ. Let me know if there are other
things that would be more interesting to experiment with first.

IMHO you should be testing with higher wal_buffers settings. ISTM likely
that the improved performance is due to there being more buffer space,
rather than actually improving I/O. Setting wal_buffers to something
fairly high say 4096 would completely remove any such effect so we are
left with a view on the I/O.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

I'm planning on continuing to increase XLOG_BLCKSZ and wal_buffers to
determine when the throughput starts to level out or drop

I think for an even better comparison you should scale wal_buffers
down with increasing XLOG_BLCKSZ, so that the xlog buffer has a fixed
size in kb.

Reasonable wal_buffers imho amount to at least 256kb, better yet 512 or
1 Mb,
with sufficiently large transactions (and to try to factor out the
difference
between blocksizes).

Andreas

On Tue, May 02, 2006 at 05:00:58PM +0200, Zeugswetter Andreas DCP SD wrote:

I'm planning on continuing to increase XLOG_BLCKSZ and wal_buffers to
determine when the throughput starts to level out or drop

I think for an even better comparison you should scale wal_buffers
down with increasing XLOG_BLCKSZ, so that the xlog buffer has a fixed
size in kb.

Reasonable wal_buffers imho amount to at least 256kb, better yet 512 or
1 Mb,
with sufficiently large transactions (and to try to factor out the
difference
between blocksizes).

AFAIK all the transactions in DBT2 are pretty small. I think all DML is
single-row in fact, so I'm not sure that having wal_buffers much larger
than the number of connections would help much.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

I'm planning on continuing to increase XLOG_BLCKSZ and wal_buffers

to

determine when the throughput starts to level out or drop

I think for an even better comparison you should scale wal_buffers
down with increasing XLOG_BLCKSZ, so that the xlog buffer has a

fixed

size in kb.

Reasonable wal_buffers imho amount to at least 256kb, better yet 512

or 1 Mb,

with sufficiently large transactions (and to try to factor out the

difference

between blocksizes).

AFAIK all the transactions in DBT2 are pretty small. I think all DML

is

single-row in fact, so I'm not sure that having wal_buffers much

larger

than the number of connections would help much.

Well, but those updates wander around the whole table/index, so you will
have a lot of
before images to write. So I take back the "sufficiently large
transactions" part
of my comment. You want more wal_buffers in all higher load scenarios.

(one test had 8 buffers of 2k each, this is not enough in any high load
scenario)

Andreas

On Tue, 02 May 2006 10:52:38 +0100
Simon Riggs <simon@2ndquadrant.com> wrote:

On Sun, 2006-04-30 at 22:14 -0700, Mark Wong wrote:

I would have gotten this out sooner but I'm having trouble with our
infrastructure. Here's a link to a table of data I've started putting
together regarding XLOG_BLCKSZ and wal_buffers on a 4-way Opteron
system:
http://developer.osdl.org/markw/pgsql/xlog_blcksz.html

There are a couple of holes in the table but I think it shows enough
evidence to say that with dbt2 having a larger XLOG_BLCKSZ improves the
overall throughput of the test.

I'm planning on continuing to increase XLOG_BLCKSZ and wal_buffers to
determine when the throughput starts to level out or drop off, and then
start experimenting with varying BLCKSZ. Let me know if there are other
things that would be more interesting to experiment with first.

IMHO you should be testing with higher wal_buffers settings. ISTM likely
that the improved performance is due to there being more buffer space,
rather than actually improving I/O. Setting wal_buffers to something
fairly high say 4096 would completely remove any such effect so we are
left with a view on the I/O.

I ran another few tests at the 600 scale factor just in case I was
getting close to peaking at 500 warehouses. (Link above has updated
data.) With wal_buffers set to 4096 the difference between 2048, 8192,
and 32768 seem negligible. Some of the disks are at 90% utilization so
perhaps I need to take a close look to make sure none of the other
system resources are pegged.

Thanks,
Mark

On Fri, 2006-05-05 at 16:00 -0700, Mark Wong wrote:

On Tue, 02 May 2006 10:52:38 +0100
Simon Riggs <simon@2ndquadrant.com> wrote:

On Sun, 2006-04-30 at 22:14 -0700, Mark Wong wrote:

I would have gotten this out sooner but I'm having trouble with our
infrastructure. Here's a link to a table of data I've started putting
together regarding XLOG_BLCKSZ and wal_buffers on a 4-way Opteron
system:
http://developer.osdl.org/markw/pgsql/xlog_blcksz.html

There are a couple of holes in the table but I think it shows enough
evidence to say that with dbt2 having a larger XLOG_BLCKSZ improves the
overall throughput of the test.

I'm planning on continuing to increase XLOG_BLCKSZ and wal_buffers to
determine when the throughput starts to level out or drop off, and then
start experimenting with varying BLCKSZ. Let me know if there are other
things that would be more interesting to experiment with first.

IMHO you should be testing with higher wal_buffers settings. ISTM likely
that the improved performance is due to there being more buffer space,
rather than actually improving I/O. Setting wal_buffers to something
fairly high say 4096 would completely remove any such effect so we are
left with a view on the I/O.

I ran another few tests at the 600 scale factor just in case I was
getting close to peaking at 500 warehouses. (Link above has updated
data.) With wal_buffers set to 4096 the difference between 2048, 8192,
and 32768 seem negligible. Some of the disks are at 90% utilization so
perhaps I need to take a close look to make sure none of the other
system resources are pegged.

The profiles are fairly different though.

Could you turn full_page_writes = off and do a few more tests? I think
the full page writes is swamping the xlog and masking the performance we
might see for normal small xlog writes.
I'd try XLOG_BLCKSZ = 4096 and 8192 to start with. Thanks.

(Is VACUUM running at the start of these tests?)

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

On Mon, 08 May 2006 19:08:59 +0100
Simon Riggs <simon@2ndquadrant.com> wrote:

On Fri, 2006-05-05 at 16:00 -0700, Mark Wong wrote:

On Tue, 02 May 2006 10:52:38 +0100
Simon Riggs <simon@2ndquadrant.com> wrote:

On Sun, 2006-04-30 at 22:14 -0700, Mark Wong wrote:

I would have gotten this out sooner but I'm having trouble with our
infrastructure. Here's a link to a table of data I've started putting
together regarding XLOG_BLCKSZ and wal_buffers on a 4-way Opteron
system:
http://developer.osdl.org/markw/pgsql/xlog_blcksz.html

There are a couple of holes in the table but I think it shows enough
evidence to say that with dbt2 having a larger XLOG_BLCKSZ improves the
overall throughput of the test.

I'm planning on continuing to increase XLOG_BLCKSZ and wal_buffers to
determine when the throughput starts to level out or drop off, and then
start experimenting with varying BLCKSZ. Let me know if there are other
things that would be more interesting to experiment with first.

IMHO you should be testing with higher wal_buffers settings. ISTM likely
that the improved performance is due to there being more buffer space,
rather than actually improving I/O. Setting wal_buffers to something
fairly high say 4096 would completely remove any such effect so we are
left with a view on the I/O.

I ran another few tests at the 600 scale factor just in case I was
getting close to peaking at 500 warehouses. (Link above has updated
data.) With wal_buffers set to 4096 the difference between 2048, 8192,
and 32768 seem negligible. Some of the disks are at 90% utilization so
perhaps I need to take a close look to make sure none of the other
system resources are pegged.

The profiles are fairly different though.

Could you turn full_page_writes = off and do a few more tests? I think
the full page writes is swamping the xlog and masking the performance we
might see for normal small xlog writes.
I'd try XLOG_BLCKSZ = 4096 and 8192 to start with. Thanks.

Ok, will get on it.

(Is VACUUM running at the start of these tests?)

VACUUM is run immediately after the database tables are loaded. I've
been reloading the database prior to each test.

Mark

Simon Riggs wrote:

Could you turn full_page_writes = off and do a few more tests? I think
the full page writes is swamping the xlog and masking the performance we
might see for normal small xlog writes.
I'd try XLOG_BLCKSZ = 4096 and 8192 to start with. Thanks.

Ok, got data for XLOG_BLCKXZ at 4096, 8192, and 32768 with
full_page_wirtes = off. The new data is at the bottom of the page:
http://developer.osdl.org/markw/pgsql/xlog_blcksz.html

Mark

On Wed, 2006-05-10 at 09:55 -0700, Mark Wong wrote:

Simon Riggs wrote:

Could you turn full_page_writes = off and do a few more tests? I think
the full page writes is swamping the xlog and masking the performance we
might see for normal small xlog writes.
I'd try XLOG_BLCKSZ = 4096 and 8192 to start with. Thanks.

Ok, got data for XLOG_BLCKXZ at 4096, 8192, and 32768 with
full_page_wirtes = off. The new data is at the bottom of the page:
http://developer.osdl.org/markw/pgsql/xlog_blcksz.html

Mark,

What do you think is causing the step changes at 30+ and 60+ minutes on
these tests?

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

Simon Riggs wrote:

On Wed, 2006-05-10 at 09:55 -0700, Mark Wong wrote:

Simon Riggs wrote:

Could you turn full_page_writes = off and do a few more tests? I think
the full page writes is swamping the xlog and masking the performance we
might see for normal small xlog writes.
I'd try XLOG_BLCKSZ = 4096 and 8192 to start with. Thanks.

Ok, got data for XLOG_BLCKXZ at 4096, 8192, and 32768 with
full_page_wirtes = off. The new data is at the bottom of the page:
http://developer.osdl.org/markw/pgsql/xlog_blcksz.html

Mark,

What do you think is causing the step changes at 30+ and 60+ minutes on
these tests?

I'm at a loss to explain this myself. The increase in the i/o wait and
decrease in the response time charts looks backwards to me. And the
vmstat charts show that the i/o seems fairly consistent other than
spikes in i/o chart. It sort of looks like the operating system stopped
doing something but I'm not sure how to isolate that any further.

Anyone else have a theory?

Mark

On Thu, 2006-06-01 at 16:46 -0700, Mark Wong wrote:

Simon Riggs wrote:

On Wed, 2006-05-10 at 09:55 -0700, Mark Wong wrote:

Simon Riggs wrote:

Could you turn full_page_writes = off and do a few more tests? I think
the full page writes is swamping the xlog and masking the performance we
might see for normal small xlog writes.
I'd try XLOG_BLCKSZ = 4096 and 8192 to start with. Thanks.

Ok, got data for XLOG_BLCKXZ at 4096, 8192, and 32768 with
full_page_wirtes = off. The new data is at the bottom of the page:
http://developer.osdl.org/markw/pgsql/xlog_blcksz.html

What do you think is causing the step changes at 30+ and 60+ minutes on
these tests?

I took some time to start charting the sar data and to break down the
iostat data by tablespaces. I've updated the web pages form the link
above. Well, none of the charts helped me make any better guesses but
perhaps someone else will see something.

When I get back from my short break, I'm planning on taking a look at
Tau, a project from the University of Oregon
(http://www.cs.uoregon.edu/research/tau/home.php) that's capable of
breaking down profiles per process and gathering hardware counters. I'm
hoping that will shed some light on the behavior.

Sounds useful. Let me know what you find.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com