Re: XLOG_BLCKSZ vs. wal_buffers table

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

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

On Tue, 02 May 2006 10:52:38 +0100
Simon Riggs <simon(at)2ndquadrant(dot)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(at)2ndquadrant(dot)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(at)2ndquadrant(dot)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(at)2ndquadrant(dot)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