Thread: XLOG_BLCKSZ vs. wal_buffers table

XLOG_BLCKSZ vs. wal_buffers table

From
Mark Wong
Date:
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.

Mark


Re: XLOG_BLCKSZ vs. wal_buffers table

From
Simon Riggs
Date:
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



Re: XLOG_BLCKSZ vs. wal_buffers table

From
"Zeugswetter Andreas DCP SD"
Date:
> 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


Re: XLOG_BLCKSZ vs. wal_buffers table

From
"Jim C. Nasby"
Date:
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


Re: XLOG_BLCKSZ vs. wal_buffers table

From
"Zeugswetter Andreas DCP SD"
Date:
> > > 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


Re: XLOG_BLCKSZ vs. wal_buffers table

From
Mark Wong
Date:
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


Re: XLOG_BLCKSZ vs. wal_buffers table

From
Simon Riggs
Date:
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



Re: XLOG_BLCKSZ vs. wal_buffers table

From
Mark Wong
Date:
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


Re: XLOG_BLCKSZ vs. wal_buffers table

From
Mark Wong
Date:
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


Re: XLOG_BLCKSZ vs. wal_buffers table

From
Simon Riggs
Date:
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



Re: XLOG_BLCKSZ vs. wal_buffers table

From
Mark Wong
Date:
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


Re: XLOG_BLCKSZ vs. wal_buffers table

From
Simon Riggs
Date:
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