Re: [HACKERS] XLOG_BLCKSZ vs. wal_buffers table
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] XLOG_BLCKSZ vs. wal_buffers table
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] XLOG_BLCKSZ vs. wal_buffers table
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] XLOG_BLCKSZ vs. wal_buffers table
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] XLOG_BLCKSZ vs. wal_buffers table
On Fri, 2006-05-05 at 16:00 -0700, Mark Wong wrote: On Tue, 02 May 2006 10:52:38 +0100 Simon Riggs [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] XLOG_BLCKSZ vs. wal_buffers table
On Mon, 08 May 2006 19:08:59 +0100 Simon Riggs [EMAIL PROTECTED] wrote: On Fri, 2006-05-05 at 16:00 -0700, Mark Wong wrote: On Tue, 02 May 2006 10:52:38 +0100 Simon Riggs [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] XLOG_BLCKSZ vs. wal_buffers table
On Tue, 02 May 2006 10:52:38 +0100 Simon Riggs [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] XLOG_BLCKSZ vs. wal_buffers table
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] XLOG_BLCKSZ vs. wal_buffers table
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] XLOG_BLCKSZ vs. wal_buffers table
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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
