"Tom Lane" <[EMAIL PROTECTED]> writes: > Gregory Stark <[EMAIL PROTECTED]> writes: >> Heikki Linnakangas <[EMAIL PROTECTED]> writes: >>> I'm still struggling to understand why and how bgwriter increases >>> performance. >>> Under what circumstances, what workload? >>> >>> The only benefit I can see is that it moves the write() of a page out of the >>> critical path. But as long as the OS cache can absorb the write, it should >>> be >>> very cheap compared to doing real I/O. > >> Well you can't keep writing indefinitely faster than the i/o subsystem can >> execute the writes. Eventually the kernel will block your write until a >> kernel >> buffer becomes free. Ie, throttle your writes to the actual write bandwidth >> available. > > Right. Also, a buffer write isn't "merely" a kernel call --- for > instance, you might have to flush some more WAL before you can execute > the write, and there are cases where you'd have to fsync the write > yourself (ie, if you can't pass it off to the bgwriter). The more of > that we can take out of foreground query paths, the better.
So it sounds like a good place to start to try to benchmark something where bgwriter helps might be a setup which starts with a very large table (like 1-10M rows) and each transaction deletes a large number of random tuples (~ 1k rows). Possibly waiting briefly before committing to give a chance for the dirty pages to be needed before commit flushes the wal. That way each transaction dirties a large number of pages and the next transaction is likely to need a fresh page and find one which has been dirtied and not had its wal record flushed. Deletes mean not much wal will be generated so wal won't be a bottleneck and you won't get checkpoints due to checkpoint_segments being reached. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster