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