Bug: Buffer cache is not scan resistant
in [PgSql]
|
Prev: xlogViewer / xlogdump
Next: CVS corruption/mistagging?
From: "Simon Riggs" on 13 Mar 2007 06:08 On Tue, 2007-03-13 at 13:40 +0900, ITAGAKI Takahiro wrote: > "Simon Riggs" <simon(a)2ndquadrant.com> wrote: > > > > > With the default > > > > value of scan_recycle_buffers(=0), VACUUM seems to use all of buffers in pool, > > > > just like existing sequential scans. Is this intended? > > > > > New test version enclosed, where scan_recycle_buffers = 0 doesn't change > > existing VACUUM behaviour. > > This is a result with scan_recycle_buffers.v3.patch. I used normal VACUUM > with background load using slowdown-ed pgbench in this instance. I believe > the patch is useful in normal cases, not only for VACUUM FREEZE. > > N | time | WAL flush(*) > -----+--------+----------- > 0 | 112.8s | 44.3% > 1 | 148.9s | 52.1% > 8 | 105.1s | 17.6% > 16 | 96.9s | 8.7% > 32 | 103.9s | 6.3% > 64 | 89.4s | 6.6% > 128 | 80.0s | 3.8% Looks good. Not sure what value of N to pick for normal use. The objectives are i) don't let VACUUMs spoil the cache ii) speed up standalone VACUUMs iii) don't let VACUUM cause others to repeatedly WAL flush I'm thinking N=16 meets all 3 objectives. We could make VACUUM go faster still, but by knocking more blocks out of cache that someone doing real work might need. That will slow them down almost as much as forcing them to flush WAL, so I'd want to be conservative with VACUUM. Does anybody think we need a new parameter for this, or are we happy at 16 buffers in the recycle loop for VACUUM? At this point I should note something I haven't mentioned before. VACUUMs force other backends to flush out WAL only when we have an I/O bound workload. If the database already fits in memory then BufferAlloc never needs to run and therefore we don't need to flush WAL. So I can understand that the effect of WAL flushing may not have been noticed by many testers. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo(a)postgresql.org so that your message can get through to the mailing list cleanly
From: "Luke Lonergan" on 13 Mar 2007 10:36 Simon, On 3/13/07 2:37 AM, "Simon Riggs" <simon(a)2ndquadrant.com> wrote: >> We're planning a modification that I think you should consider: when there >> is a sequential scan of a table larger than the size of shared_buffers, we >> are allowing the scan to write through the shared_buffers cache. > > Write? For which operations? I'm actually just referring to the sequential scan "writing into" the shared buffers cache, sorry for the "write through" :-) > I was thinking to do this for bulk writes also, but it would require > changes to bgwriter's cleaning sequence. Are you saying to write say ~32 > buffers then fsync them, rather than letting bgwriter do that? Then > allow those buffers to be reused? Off topic, but we think we just found the reason(s) for the abysmal heap insert performance of pgsql and are working on a fix to that as well. It involves two main things: the ping-ponging seeks used to extend a relfile and the bgwriter not flushing aggressively enough. We're hoping to move the net heap insert rate from 12MB/s for a single stream to something more like 100 MB/s per stream, but it may take a week to get some early results and find out if we're on the right track. We've been wrong on this before ;-) - Luke ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
From: Bruce Momjian on 26 Mar 2007 21:04 Simon, is this patch ready to be added to the patch queue? I assume not. --------------------------------------------------------------------------- Simon Riggs wrote: > On Mon, 2007-03-12 at 09:14 +0000, Simon Riggs wrote: > > On Mon, 2007-03-12 at 16:21 +0900, ITAGAKI Takahiro wrote: > > > > With the default > > > value of scan_recycle_buffers(=0), VACUUM seems to use all of buffers in pool, > > > just like existing sequential scans. Is this intended? > > > > Yes, but its not very useful for testing to have done that. I'll do > > another version within the hour that sets N=0 (only) back to current > > behaviour for VACUUM. > > New test version enclosed, where scan_recycle_buffers = 0 doesn't change > existing VACUUM behaviour. > > -- > Simon Riggs > EnterpriseDB http://www.enterprisedb.com > [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq -- Bruce Momjian <bruce(a)momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo(a)postgresql.org so that your message can get through to the mailing list cleanly
From: Bruce Momjian on 2 Apr 2007 19:15
"test" version, but I am putting in the queue so we can track it there. Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --------------------------------------------------------------------------- Simon Riggs wrote: > On Mon, 2007-03-12 at 09:14 +0000, Simon Riggs wrote: > > On Mon, 2007-03-12 at 16:21 +0900, ITAGAKI Takahiro wrote: > > > > With the default > > > value of scan_recycle_buffers(=0), VACUUM seems to use all of buffers in pool, > > > just like existing sequential scans. Is this intended? > > > > Yes, but its not very useful for testing to have done that. I'll do > > another version within the hour that sets N=0 (only) back to current > > behaviour for VACUUM. > > New test version enclosed, where scan_recycle_buffers = 0 doesn't change > existing VACUUM behaviour. > > -- > Simon Riggs > EnterpriseDB http://www.enterprisedb.com > [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq -- Bruce Momjian <bruce(a)momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate |