Re: [HACKERS] Initial 9.2 pgbench write results

2012-04-10 Thread Greg Smith
On 03/06/2012 04:35 PM, Jeff Janes wrote: On my testing, this dirty-before-evict is because the bgwriter is riding too far ahead of the clock sweep, because of scan_whole_pool_milliseconds. Because it is far ahead, that leaves a lot of run between the two pointers for re-dirtying cache hits to

Re: [HACKERS] Initial 9.2 pgbench write results

2012-03-06 Thread Jeff Janes
On Tue, Feb 28, 2012 at 9:49 AM, Robert Haas robertmh...@gmail.com wrote: On Tue, Feb 28, 2012 at 11:46 AM, Robert Haas robertmh...@gmail.com wrote: This is an interesting hypothesis which I think we can test.  I'm thinking of writing a quick patch (just for testing, not for commit) to set a

Re: [HACKERS] Initial 9.2 pgbench write results

2012-03-06 Thread Robert Haas
On Tue, Mar 6, 2012 at 4:35 PM, Jeff Janes jeff.ja...@gmail.com wrote: I don't think reseting the stats has anything to do with it, it is just that the shared_buffers warmed up over time. Yes. On my testing, this dirty-before-evict is because the bgwriter is riding too far ahead of the clock

Re: [HACKERS] Initial 9.2 pgbench write results

2012-02-28 Thread Jeff Janes
On Thu, Feb 23, 2012 at 3:17 AM, Greg Smith g...@2ndquadrant.com wrote: I think an even bigger factor now is that the BGW writes can disturb write ordering/combining done at the kernel and storage levels.  It's painfully obvious now how much PostgreSQL relies on that to get good performance.  

Re: [HACKERS] Initial 9.2 pgbench write results

2012-02-28 Thread Robert Haas
On Tue, Feb 28, 2012 at 1:15 AM, Ants Aasma ants.aa...@eesti.ee wrote: My hypothesis for the TPS regression is that it is due to write combining. When the workload is mainly bound by I/O, every little bit that can be saved helps the bottomline. Larger scalefactors don't get the benefit because

Re: [HACKERS] Initial 9.2 pgbench write results

2012-02-28 Thread Robert Haas
On Tue, Feb 28, 2012 at 11:36 AM, Jeff Janes jeff.ja...@gmail.com wrote: How hard would it be to dummy up a bgwriter which, every time it wakes up, it forks off a child process to actually do the write, and then the real one just waits for the child to exit?  If it didn't have to correctly

Re: [HACKERS] Initial 9.2 pgbench write results

2012-02-28 Thread karavelov
- Цитат от Robert Haas (robertmh...@gmail.com), на 28.02.2012 в 19:25 - On Tue, Feb 28, 2012 at 11:36 AM, Jeff Janes jeff.ja...@gmail.com wrote: How hard would it be to dummy up a bgwriter which, every time it wakes up, it forks off a child process to actually do the write, and then

Re: [HACKERS] Initial 9.2 pgbench write results

2012-02-28 Thread Robert Haas
On Tue, Feb 28, 2012 at 11:46 AM, Robert Haas robertmh...@gmail.com wrote: On Tue, Feb 28, 2012 at 1:15 AM, Ants Aasma ants.aa...@eesti.ee wrote: My hypothesis for the TPS regression is that it is due to write combining. When the workload is mainly bound by I/O, every little bit that can be

Re: [HACKERS] Initial 9.2 pgbench write results

2012-02-28 Thread Tom Lane
Jeff Janes jeff.ja...@gmail.com writes: How hard would it be to dummy up a bgwriter which, every time it wakes up, it forks off a child process to actually do the write, and then the real one just waits for the child to exit? If it didn't have to correctly handle signals, SINVAL, and such, it

Re: [HACKERS] Initial 9.2 pgbench write results

2012-02-27 Thread Simon Riggs
On Mon, Feb 27, 2012 at 5:13 AM, Robert Haas robertmh...@gmail.com wrote: On Fri, Feb 24, 2012 at 5:35 AM, Simon Riggs si...@2ndquadrant.com wrote: On Thu, Feb 23, 2012 at 11:59 PM, Robert Haas robertmh...@gmail.com wrote: this doesn't feel like the right time to embark on a bunch of new

Re: [HACKERS] Initial 9.2 pgbench write results

2012-02-27 Thread Robert Haas
On Mon, Feb 27, 2012 at 3:50 AM, Simon Riggs si...@2ndquadrant.com wrote: That isn't the case. We have evidence that the current knobs are hugely ineffective in some cases. Turning the bgwriter off is hardly adjusting a setting, its admitting that there is no useful setting. I've suggested

Re: [HACKERS] Initial 9.2 pgbench write results

2012-02-27 Thread Simon Riggs
On Mon, Feb 27, 2012 at 1:08 PM, Robert Haas robertmh...@gmail.com wrote: On Mon, Feb 27, 2012 at 3:50 AM, Simon Riggs si...@2ndquadrant.com wrote: That isn't the case. We have evidence that the current knobs are hugely ineffective in some cases. Turning the bgwriter off is hardly adjusting a

Re: [HACKERS] Initial 9.2 pgbench write results

2012-02-27 Thread Greg Smith
On 02/27/2012 08:08 AM, Robert Haas wrote: OK, fair point. But I don't think any of us - Greg included - have an enormously clear idea why turning the background writer off is improving performance in some cases. I think we need to understand that better before we start changing things.

Re: [HACKERS] Initial 9.2 pgbench write results

2012-02-27 Thread Ants Aasma
On Feb 27, 2012 10:36 PM, Greg Smith g...@2ndquadrant.com wrote: One of the reasons I drilled right into this spot is because of fears that running the writer more often would sprout regressions in TPS. I can't explain exactly why exactly having backends write their own buffers out at the latest

Re: [HACKERS] Initial 9.2 pgbench write results

2012-02-26 Thread Robert Haas
On Fri, Feb 24, 2012 at 5:35 AM, Simon Riggs si...@2ndquadrant.com wrote: On Thu, Feb 23, 2012 at 11:59 PM, Robert Haas robertmh...@gmail.com wrote: this doesn't feel like the right time to embark on a bunch of new engineering projects. IMHO this is exactly the right time to do full system

Re: [HACKERS] Initial 9.2 pgbench write results

2012-02-25 Thread Jeff Janes
On Tue, Feb 14, 2012 at 12:25 PM, Greg Smith g...@2ndquadrant.com wrote: On 02/14/2012 01:45 PM, Greg Smith wrote: scale=1000, db is 94% of RAM; clients=4 Version TPS 9.0  535 9.1  491 (-8.4% relative to 9.0) 9.2  338 (-31.2% relative to 9.1) A second pass through this data noted that

Re: [HACKERS] Initial 9.2 pgbench write results

2012-02-24 Thread Simon Riggs
On Thu, Feb 23, 2012 at 11:59 PM, Robert Haas robertmh...@gmail.com wrote: this doesn't feel like the right time to embark on a bunch of new engineering projects. IMHO this is exactly the right time to do full system tuning. Only when we have major projects committed can we move towards

Re: [HACKERS] Initial 9.2 pgbench write results

2012-02-23 Thread Greg Smith
I've updated http://highperfpostgres.com/results-write-9.2-cf4/index.htm with more data including two alternate background writer configurations. Since the sensitive part of the original results was scales of 500 and 1000, I've also gone back and added scale=750 runs to all results. Quick

Re: [HACKERS] Initial 9.2 pgbench write results

2012-02-23 Thread Simon Riggs
On Thu, Feb 23, 2012 at 11:17 AM, Greg Smith g...@2ndquadrant.com wrote: A second fact that's visible from the TPS graphs over the test run, and obvious if you think about it, is that BGW writes force data to physical disk earlier than they otherwise might go there.  That's a subtle pattern in

Re: [HACKERS] Initial 9.2 pgbench write results

2012-02-23 Thread Greg Smith
On 02/23/2012 07:36 AM, Simon Riggs wrote: Since scan_whole_pool_milliseconds is set to 2 minutes we scan the whole bufferpool every 2 minutes, no matter how big the bufferpool, even when nothing else is happening. Not cool. It's not quite that bad. Once the BGW has circled around the whole

Re: [HACKERS] Initial 9.2 pgbench write results

2012-02-23 Thread Simon Riggs
On Thu, Feb 23, 2012 at 8:44 PM, Greg Smith g...@2ndquadrant.com wrote: On 02/23/2012 07:36 AM, Simon Riggs wrote: Since scan_whole_pool_milliseconds is set to 2 minutes we scan the whole bufferpool every 2 minutes, no matter how big the bufferpool, even when nothing else is happening. Not

Re: [HACKERS] Initial 9.2 pgbench write results

2012-02-23 Thread Robert Haas
On Thu, Feb 23, 2012 at 3:44 PM, Greg Smith g...@2ndquadrant.com wrote: It's not quite that bad.  Once the BGW has circled around the whole buffer pool, such that it's swept so far ahead it's reached the clock sweep strategy point, it stops.  So when the system is idle, it creeps forward until

Re: [HACKERS] Initial 9.2 pgbench write results

2012-02-19 Thread Simon Riggs
On Sun, Feb 19, 2012 at 4:17 AM, Robert Haas robertmh...@gmail.com wrote: Here's what's bugging me.  Greg seemed to be assuming that the business of the background writer might be the cause of the performance drop-off he measured on certain test cases.  But you and I both seem to feel that

Re: [HACKERS] Initial 9.2 pgbench write results

2012-02-19 Thread Simon Riggs
On Tue, Feb 14, 2012 at 6:45 PM, Greg Smith g...@2ndquadrant.com wrote: Minimal changes were made to the postgresql.conf.  shared_buffers=2GB, checkpoint_segments=64, and I left wal_buffers at its default so that 9.1 got credit for that going up.  See

Re: [HACKERS] Initial 9.2 pgbench write results

2012-02-19 Thread Greg Smith
On 02/18/2012 02:35 PM, Robert Haas wrote: I see CheckpointWriteDelay calling BgBufferSync in 9.1. Background writing would stop during the sync phase and perhaps slow down a bit during checkpoint writing, but I don't think it was stopped completely. The sync phase can be pretty long

Re: [HACKERS] Initial 9.2 pgbench write results

2012-02-19 Thread Greg Smith
On 02/19/2012 05:37 AM, Simon Riggs wrote: Please retest with wal_buffers 128MB, checkpoint_segments 1024 The test parameters I'm using aim to run through several checkpoint cycles in 10 minutes of time. Bumping up against the ugly edges of resource bottlenecks is part of the test.

Re: [HACKERS] Initial 9.2 pgbench write results

2012-02-19 Thread Robert Haas
On Sun, Feb 19, 2012 at 11:12 PM, Greg Smith g...@2ndquadrant.com wrote: I'm collecting one last bit of data before posting another full set of results, but I'm getting more comfortable the issue here is simply changes in the BGW behavior.  The performance regression tracks the background

Re: [HACKERS] Initial 9.2 pgbench write results

2012-02-18 Thread Robert Haas
On Tue, Feb 14, 2012 at 3:25 PM, Greg Smith g...@2ndquadrant.com wrote: On 02/14/2012 01:45 PM, Greg Smith wrote: scale=1000, db is 94% of RAM; clients=4 Version TPS 9.0  535 9.1  491 (-8.4% relative to 9.0) 9.2  338 (-31.2% relative to 9.1) A second pass through this data noted that the

Re: [HACKERS] Initial 9.2 pgbench write results

2012-02-18 Thread Simon Riggs
On Sat, Feb 18, 2012 at 7:35 PM, Robert Haas robertmh...@gmail.com wrote: On Tue, Feb 14, 2012 at 3:25 PM, Greg Smith g...@2ndquadrant.com wrote: On 02/14/2012 01:45 PM, Greg Smith wrote: scale=1000, db is 94% of RAM; clients=4 Version TPS 9.0  535 9.1  491 (-8.4% relative to 9.0) 9.2  338

Re: [HACKERS] Initial 9.2 pgbench write results

2012-02-18 Thread Robert Haas
On Sat, Feb 18, 2012 at 3:00 PM, Simon Riggs si...@2ndquadrant.com wrote: On Sat, Feb 18, 2012 at 7:35 PM, Robert Haas robertmh...@gmail.com wrote: On Tue, Feb 14, 2012 at 3:25 PM, Greg Smith g...@2ndquadrant.com wrote: On 02/14/2012 01:45 PM, Greg Smith wrote: scale=1000, db is 94% of RAM;

[HACKERS] Initial 9.2 pgbench write results

2012-02-14 Thread Greg Smith
Last year at this time, I was investigating things like ext3 vs xfs, how well Linux's dirty_bytes parameter worked, and how effective a couple of patches were on throughput latency. The only patch that ended up applied for 9.1 was for fsync compaction. That was measurably better in terms of

Re: [HACKERS] Initial 9.2 pgbench write results

2012-02-14 Thread Greg Smith
On 02/14/2012 01:45 PM, Greg Smith wrote: scale=1000, db is 94% of RAM; clients=4 Version TPS 9.0 535 9.1 491 (-8.4% relative to 9.0) 9.2 338 (-31.2% relative to 9.1) A second pass through this data noted that the maximum number of buffers cleaned by the background writer is =2785 in