From: Greg Smith <gsmith@gregsmith.com>
To: pgsql-patches@postgresql.org
Subject: Re: Automatic adjustment of bgwriter_lru_maxpages
Date: Sat, 12 May 2007 21:32:29 -0400 (EDT)
Message-ID: <Pine.GSO.4.64.0705121926420.9615@westnet.com> (raw)
In-Reply-To: <461E108D.6080709@enterprisedb.com>
References: <20070227141958.638D.ITAGAKI.TAKAHIRO@oss.ntt.co.jp>
<20070227170613.GK29041@nasby.net>
<20070305131046.C4A6.ITAGAKI.TAKAHIRO@oss.ntt.co.jp>
<461E108D.6080709@enterprisedb.com>
Attached are two patches that try to recast the ideas of Itagaki
Takahiro's auto bgwriter_lru_maxpages patch in the direction I think this
code needs to move. Epic-length commentary follows.
The original code came from before there was a pg_stat_bgwriter. The
first patch (buf-alloc-stats) takes the two most interesting pieces of
data the original patch collected, the number of buffers allocated
recently and the number that the clients wrote out, and ties all that into
the new stats structure. With this patch applied, you can get a feel for
things like churn/turnover in the buffer pool that were very hard to
quantify before. Also, it makes it easy to measure how well your
background writer is doing at writing buffers so the clients don't have
to. Applying this would complete one of my personal goals for the 8.3
release, which was having stats to track every type of buffer write.
I split this out because I think it's very useful to have regardless of
whether the automatic tuning portion is accepted, and I think these
smaller patches make the review easier. The main thing I would recommend
someone check is how am_bg_writer is (mis?)used here. I spliced some of
the debugging-only code from the original patch, and I can't tell if the
result is a robust enough approach to solving the problem of having every
client indirectly report their activity to the background writer. Other
than that, I think this code is ready for review and potentially
comitting.
The second patch (limit-lru) adds on top of that a constraint of the LRU
writer so that it doesn't do any more work than it has to. Note that I
left verbose debugging code in here because I'm much less confident this
patch is complete.
It predicts upcoming buffer allocations using a 16-period weighted moving
average of recent activity, which you can think of as the last 3.2 seconds
at the default interval. After testing a few systems that seemed a decent
compromise of smoothing in both directions. I found the 2X overallocation
fudge factor of the original patch way too aggressive, and just pick the
larger of the most recent allocation amount or the smoothed value. The
main thing that throws off the allocation estimation is when you hit a
checkpoint, which can give a big spike after the background writer returns
to BgBufferSync and notices all the buffers that were allocated during the
checkpoint write; the code then tries to find more buffers it can recycle
than it needs to. Since the checkpoint itself normally leaves a large
wake of reusable buffers behind it, I didn't find this to be a serious
problem.
There's another communication issue here, which is that SyncOneBuffer
needs to return more information about the buffer than it currently does
once it gets it locked. The background writer needs to know more than
just if it was written to tune itself. The original patch used a clever
trick for this which worked but I found confusing. I happen to have a
bunch of other background writer tuning code I'm working on, and I had to
come up with a more robust way to communicate buffer internals back via
this channel. I used that code here, it's a bitmask setup similar to how
flags like BM_DIRTY are used. It's overkill for solving this particular
problem, but I think the interface is clean and it helps support future
enhancements in intelligent background writing.
Now we get to the controversial part. The original patch removed the
bgwriter_lru_maxpages parameter and updated the documentation accordingly.
I didn't do that here. The reason is that after playing around in this
area I'm not convinced yet I can satisfy all the tuning scenarios I'd like
to be able to handle that way. I describe this patch as enforcing a
constraint instead; it allows you to set the LRU parameters much higher
than was reasonable before without having to be as concerned about the LRU
writer wasting resources.
I already brought up some issues in this area on -hackers (
http://archives.postgresql.org/pgsql-hackers/2007-04/msg00781.php ) but my
work hasn't advanced as fast as I'd hoped. I wanted to submit what I've
finished anyway because I think any approach here is going to have cope
with the issues addressed in these two patches, and I'm happy now with how
they're solved here. It's only a one-line delete to disable the LRU
limiting behavior of the second patch, at which point it's strictly
internals code with no expected functional impact that alternate
approaches might be built on.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
Attachments:download all (2 files, .tar.gz)limit-lru.patch (text/plain, 9.4K) download
Index: src/backend/storage/buffer/bufmgr.c
===================================================================
RCS file: /d3/pgsql/cvs/pgsql-local/src/backend/storage/buffer/bufmgr.c,v
retrieving revision 1.1.1.1
diff -c -r1.1.1.1 bufmgr.c
*** src/backend/storage/buffer/bufmgr.c 7 May 2007 01:48:49 -0000 1.1.1.1
--- src/backend/storage/buffer/bufmgr.c 12 May 2007 22:26:56 -0000
***************
*** 67,72 ****
--- 67,79 ----
/* interval for calling AbsorbFsyncRequests in BufferSync */
#define WRITES_PER_ABSORB 1000
+ /* Return codes describing what SyncOneBuffer found out and did with the+ * buffer it processed. The way code here tests for whether a write+ * was done depends on BUF_WRITTEN being the highest bit value in this set. */+ #define BUF_WRITTEN 0x80+ #define BUF_CLEAN 0x40+ #define BUF_REUSABLE 0x20+ #define BUF_USAGE_COUNT 0x1F
/* GUC variables */
bool zero_damaged_pages = false;
***************
*** 101,107 ****
static void PinBuffer_Locked(volatile BufferDesc *buf);
static void UnpinBuffer(volatile BufferDesc *buf,
bool fixOwner, bool normalAccess);
! static bool SyncOneBuffer(int buf_id, bool skip_pinned);
static void WaitIO(volatile BufferDesc *buf);
static bool StartBufferIO(volatile BufferDesc *buf, bool forInput);
static void TerminateBufferIO(volatile BufferDesc *buf, bool clear_dirty,
--- 108,114 ----
static void PinBuffer_Locked(volatile BufferDesc *buf);
static void UnpinBuffer(volatile BufferDesc *buf,
bool fixOwner, bool normalAccess);
! static int SyncOneBuffer(int buf_id, bool skip_recently_used);
static void WaitIO(volatile BufferDesc *buf);
static bool StartBufferIO(volatile BufferDesc *buf, bool forInput);
static void TerminateBufferIO(volatile BufferDesc *buf, bool clear_dirty,
***************
*** 1007,1013 ****
absorb_counter = WRITES_PER_ABSORB;
while (num_to_scan-- > 0)
{
! if (SyncOneBuffer(buf_id, false))
{
BgWriterStats.m_buf_written_checkpoints++;
--- 1014,1020 ----
absorb_counter = WRITES_PER_ABSORB;
while (num_to_scan-- > 0)
{
! if (SyncOneBuffer(buf_id, false)>=BUF_WRITTEN)
{
BgWriterStats.m_buf_written_checkpoints++;
***************
*** 1040,1047 ****
int buf_id2;
int num_to_scan;
int num_written;
! int recent_alloc;
int num_client_writes;
/* Make sure we can handle the pin inside SyncOneBuffer */
ResourceOwnerEnlargeBuffers(CurrentResourceOwner);
--- 1047,1063 ----
int buf_id2;
int num_to_scan;
int num_written;
!
! /* Statistics returned by the freelist strategy code */
int num_client_writes;
+ int recent_alloc;+ + + /* Used to estimate the upcoming LRU eviction activity */+ static int smoothed_alloc = 0;+ int upcoming_alloc_estimate;+ int reusable_buffers;+ int buffer_state;
/* Make sure we can handle the pin inside SyncOneBuffer */
ResourceOwnerEnlargeBuffers(CurrentResourceOwner);
***************
*** 1073,1079 ****
{
if (++buf_id1 >= NBuffers)
buf_id1 = 0;
! if (SyncOneBuffer(buf_id1, false))
{
if (++num_written >= bgwriter_all_maxpages)
{
--- 1089,1095 ----
{
if (++buf_id1 >= NBuffers)
buf_id1 = 0;
! if (SyncOneBuffer(buf_id1, false)>=BUF_WRITTEN)
{
if (++num_written >= bgwriter_all_maxpages)
{
***************
*** 1092,1097 ****
--- 1108,1142 ----
BgWriterStats.m_buf_alloc+=recent_alloc;
BgWriterStats.m_buf_written_client+=num_client_writes;
+ /* Estimate number of buffers to write based on a smoothed weighted+ * average of previous and recent buffer allocations */+ smoothed_alloc = smoothed_alloc * 15 / 16 + recent_alloc / 16;+ + /* Expect we will soon need either the smoothed amount or the recent allocation amount, + * whichever is larger */+ upcoming_alloc_estimate = smoothed_alloc;+ if (recent_alloc > upcoming_alloc_estimate)+ upcoming_alloc_estimate = recent_alloc;+ + /**** DEBUG show the smoothing in action ***/+ if (1) + {+ static int count = 0;+ static int alloc[10];+ static int smoothed[10];+ alloc[count % 10]=recent_alloc;+ smoothed[count % 10]=smoothed_alloc;+ if (++count % 10 == 9)+ {+ elog(LOG,"alloc = %d %d %d %d %d %d %d %d %d %d",+ alloc[0],alloc[1],alloc[2],alloc[3],alloc[4],+ alloc[5],alloc[6],alloc[7],alloc[8],alloc[9]);+ elog(LOG,"smoothed = %d %d %d %d %d %d %d %d %d %d",+ smoothed[0],smoothed[1],smoothed[2],smoothed[3],smoothed[4],+ smoothed[5],smoothed[6],smoothed[7],smoothed[8],smoothed[9]);+ }+ }+
/*
* This loop considers only unpinned buffers close to the clock sweep
* point.
***************
*** 1100,1139 ****
{
num_to_scan = (int) ((NBuffers * bgwriter_lru_percent + 99) / 100);
num_written = 0;
!
while (num_to_scan-- > 0)
{
! if (SyncOneBuffer(buf_id2, true))
{
if (++num_written >= bgwriter_lru_maxpages)
{
BgWriterStats.m_maxwritten_lru++;
break;
}
}
if (++buf_id2 >= NBuffers)
buf_id2 = 0;
}
BgWriterStats.m_buf_written_lru += num_written;
}
}
/*
* SyncOneBuffer -- process a single buffer during syncing.
*
! * If skip_pinned is true, we don't write currently-pinned buffers, nor
* buffers marked recently used, as these are not replacement candidates.
*
! * Returns true if buffer was written, else false. (This could be in error
! * if FlushBuffers finds the buffer clean after locking it, but we don't
! * care all that much.)
*
* Note: caller must have done ResourceOwnerEnlargeBuffers.
*/
! static bool
! SyncOneBuffer(int buf_id, bool skip_pinned)
{
volatile BufferDesc *bufHdr = &BufferDescriptors[buf_id];
/*
* Check whether buffer needs writing.
--- 1145,1207 ----
{
num_to_scan = (int) ((NBuffers * bgwriter_lru_percent + 99) / 100);
num_written = 0;
! reusable_buffers = 0;
while (num_to_scan-- > 0)
{
! buffer_state=SyncOneBuffer(buf_id2, true);
! if (buffer_state>=BUF_WRITTEN)
{
+ reusable_buffers++;
if (++num_written >= bgwriter_lru_maxpages)
{
BgWriterStats.m_maxwritten_lru++;
break;
}
}
+ else if (buffer_state & BUF_REUSABLE) reusable_buffers++;+
if (++buf_id2 >= NBuffers)
buf_id2 = 0;
+ + /* Exit when target for upcoming allocations reached */+ if (reusable_buffers>=upcoming_alloc_estimate) break;
}
BgWriterStats.m_buf_written_lru += num_written;
+ + if (1 && num_written>0) /**** DEBUG Show what happened this pass */+ {+ elog(LOG,"scanned=%d written=%d client write=%d alloc_est=%d reusable=%d",+ (int) ((NBuffers * bgwriter_lru_percent + 99) / 100) - num_to_scan,+ num_written,num_client_writes,upcoming_alloc_estimate,reusable_buffers);+ }+
}
}
/*
* SyncOneBuffer -- process a single buffer during syncing.
*
! * If skip_recently_used is true, we don't write currently-pinned buffers, nor
* buffers marked recently used, as these are not replacement candidates.
*
! * Returns an integer code describing both the state the buffer was
! * in when examined and what was done with it. The lower-order bits
! * are set to the usage_count of the buffer, and the following
! * bit masks are set accordingly: BUF_WRITTEN, BUF_CLEAN, BUF_REUSABLE
! *
! * (This could be in error if FlushBuffers finds the buffer clean after
! * locking it, but we don't care all that much.)
! *
! * The results are ordered such that the simple test for whether a buffer was
! * written is to check whether the return code is >=BUF_WRITTEN
*
* Note: caller must have done ResourceOwnerEnlargeBuffers.
*/
! static int
! SyncOneBuffer(int buf_id, bool skip_recently_used)
{
volatile BufferDesc *bufHdr = &BufferDescriptors[buf_id];
+ int buffer_state;
/*
* Check whether buffer needs writing.
***************
*** 1145,1160 ****
* upcoming changes and so we are not required to write such dirty buffer.
*/
LockBufHdr(bufHdr);
if (!(bufHdr->flags & BM_VALID) || !(bufHdr->flags & BM_DIRTY))
{
UnlockBufHdr(bufHdr);
! return false;
}
! if (skip_pinned &&
! (bufHdr->refcount != 0 || bufHdr->usage_count != 0))
{
UnlockBufHdr(bufHdr);
! return false;
}
/*
--- 1213,1237 ----
* upcoming changes and so we are not required to write such dirty buffer.
*/
LockBufHdr(bufHdr);
+ + /* Starting state says this buffer is dirty, not reusable, and unwritten */+ buffer_state = bufHdr->usage_count;+
if (!(bufHdr->flags & BM_VALID) || !(bufHdr->flags & BM_DIRTY))
+ buffer_state|=BUF_CLEAN;+ + if (bufHdr->refcount == 0 && bufHdr->usage_count == 0) + buffer_state|=BUF_REUSABLE;+ else if (skip_recently_used)
{
UnlockBufHdr(bufHdr);
! return buffer_state;
}
!
! if (buffer_state & BUF_CLEAN)
{
UnlockBufHdr(bufHdr);
! return buffer_state;
}
/*
***************
*** 1169,1175 ****
LWLockRelease(bufHdr->content_lock);
UnpinBuffer(bufHdr, true, false /* don't change freelist */ );
! return true;
}
--- 1246,1252 ----
LWLockRelease(bufHdr->content_lock);
UnpinBuffer(bufHdr, true, false /* don't change freelist */ );
! return buffer_state | BUF_WRITTEN;
}
buf-alloc-stats.patch (application/octet-stream, 24.5K) download
Index: doc/src/sgml/monitoring.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/monitoring.sgml,v
retrieving revision 1.50
diff -c -r1.50 monitoring.sgml
*** doc/src/sgml/monitoring.sgml 27 Apr 2007 20:08:43 -0000 1.50
--- doc/src/sgml/monitoring.sgml 7 May 2007 01:30:19 -0000
***************
*** 250,258 ****
<row>
<entry><structname>pg_stat_bgwriter</></entry>
<entry>One row only, showing cluster-wide statistics from the
! background writer: number of scheduled checkpoints, requested
! checkpoints, buffers written by checkpoints, lru-scans and all-scans,
! and the number of times the bgwriter aborted a round because it had
written too many buffers during lru-scans and all-scans.
</entry>
</row>
--- 250,259 ----
<row>
<entry><structname>pg_stat_bgwriter</></entry>
<entry>One row only, showing cluster-wide statistics from the
! background writer and shared buffer pool: number of scheduled
! checkpoints, requested checkpoints, buffers written by checkpoints,
! lru-scans, all-scans, and clients, total buffers allocated, and the
! number of times the bgwriter aborted a round because it had
written too many buffers during lru-scans and all-scans.
</entry>
</row>
***************
*** 815,820 ****
--- 816,839 ----
</row>
<row>
+ <entry><literal><function>pg_stat_get_bgwriter_buf_written_client</function>()</literal></entry>+ <entry><type>bigint</type></entry>+ <entry>+ The number of buffers written by clients because they needed+ to allocate a new buffer+ </entry>+ </row>+ + <row>+ <entry><literal><function>pg_stat_get_bgwriter_buf_alloc</function>()</literal></entry>+ <entry><type>bigint</type></entry>+ <entry>+ The total number of buffers allocated into the shared buffer + cache+ </entry>+ </row>+ + <row>
<entry><literal><function>pg_stat_clear_snapshot</function>()</literal></entry>
<entry><type>void</type></entry>
<entry>
Index: src/backend/catalog/system_views.sql
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/catalog/system_views.sql,v
retrieving revision 1.37
diff -c -r1.37 system_views.sql
*** src/backend/catalog/system_views.sql 30 Mar 2007 18:34:55 -0000 1.37
--- src/backend/catalog/system_views.sql 7 May 2007 01:30:20 -0000
***************
*** 373,376 ****
pg_stat_get_bgwriter_buf_written_lru() AS buffers_lru,
pg_stat_get_bgwriter_buf_written_all() AS buffers_all,
pg_stat_get_bgwriter_maxwritten_lru() AS maxwritten_lru,
! pg_stat_get_bgwriter_maxwritten_all() AS maxwritten_all;
--- 373,379 ----
pg_stat_get_bgwriter_buf_written_lru() AS buffers_lru,
pg_stat_get_bgwriter_buf_written_all() AS buffers_all,
pg_stat_get_bgwriter_maxwritten_lru() AS maxwritten_lru,
! pg_stat_get_bgwriter_maxwritten_all() AS maxwritten_all,
! pg_stat_get_bgwriter_buf_written_client() AS buffers_client,
! pg_stat_get_bgwriter_buf_alloc() AS buffers_alloc
! ;
Index: src/backend/postmaster/bgwriter.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/postmaster/bgwriter.c,v
retrieving revision 1.37
diff -c -r1.37 bgwriter.c
*** src/backend/postmaster/bgwriter.c 30 Mar 2007 18:34:55 -0000 1.37
--- src/backend/postmaster/bgwriter.c 7 May 2007 01:30:21 -0000
***************
*** 147,156 ****
static volatile sig_atomic_t shutdown_requested = false;
/*
! * Private state
*/
! static bool am_bg_writer = false;
static bool ckpt_active = false;
static time_t last_checkpoint_time;
--- 147,160 ----
static volatile sig_atomic_t shutdown_requested = false;
/*
! * Buffer and fsync activity and statistics nned to work differently
! * when the current process is the background writer
*/
! bool am_bg_writer = false;
+ /*+ * Private state+ */
static bool ckpt_active = false;
static time_t last_checkpoint_time;
Index: src/backend/postmaster/pgstat.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/postmaster/pgstat.c,v
retrieving revision 1.155
diff -c -r1.155 pgstat.c
*** src/backend/postmaster/pgstat.c 30 Apr 2007 16:37:08 -0000 1.155
--- src/backend/postmaster/pgstat.c 7 May 2007 01:30:23 -0000
***************
*** 1736,1742 ****
BgWriterStats.m_buf_written_lru == 0 &&
BgWriterStats.m_buf_written_all == 0 &&
BgWriterStats.m_maxwritten_lru == 0 &&
! BgWriterStats.m_maxwritten_all == 0)
return;
/*
--- 1736,1744 ----
BgWriterStats.m_buf_written_lru == 0 &&
BgWriterStats.m_buf_written_all == 0 &&
BgWriterStats.m_maxwritten_lru == 0 &&
! BgWriterStats.m_maxwritten_all == 0 &&
! BgWriterStats.m_buf_written_client == 0 &&
! BgWriterStats.m_buf_alloc == 0)
return;
/*
***************
*** 2805,2808 ****
--- 2807,2812 ----
globalStats.buf_written_all += msg->m_buf_written_all;
globalStats.maxwritten_lru += msg->m_maxwritten_lru;
globalStats.maxwritten_all += msg->m_maxwritten_all;
+ globalStats.buf_written_client += msg->m_buf_written_client;+ globalStats.buf_alloc += msg->m_buf_alloc;
}
Index: src/backend/storage/buffer/bufmgr.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/storage/buffer/bufmgr.c,v
retrieving revision 1.218
diff -c -r1.218 bufmgr.c
*** src/backend/storage/buffer/bufmgr.c 2 May 2007 23:34:48 -0000 1.218
--- src/backend/storage/buffer/bufmgr.c 7 May 2007 01:30:24 -0000
***************
*** 987,997 ****
int buf_id;
int num_to_scan;
int absorb_counter;
/*
* Find out where to start the circular scan.
*/
! buf_id = StrategySyncStart();
/* Make sure we can handle the pin inside SyncOneBuffer */
ResourceOwnerEnlargeBuffers(CurrentResourceOwner);
--- 987,1001 ----
int buf_id;
int num_to_scan;
int absorb_counter;
+ int recent_alloc;+ int num_client_writes;
/*
* Find out where to start the circular scan.
*/
! buf_id = StrategySyncStart(&recent_alloc,&num_client_writes);
! BgWriterStats.m_buf_alloc+=recent_alloc;
! BgWriterStats.m_buf_written_client+=num_client_writes;
/* Make sure we can handle the pin inside SyncOneBuffer */
ResourceOwnerEnlargeBuffers(CurrentResourceOwner);
***************
*** 1036,1041 ****
--- 1040,1047 ----
int buf_id2;
int num_to_scan;
int num_written;
+ int recent_alloc;+ int num_client_writes;
/* Make sure we can handle the pin inside SyncOneBuffer */
ResourceOwnerEnlargeBuffers(CurrentResourceOwner);
***************
*** 1080,1085 ****
--- 1086,1098 ----
}
/*
+ * Find out where to start the circular scan.+ */+ buf_id2 = StrategySyncStart(&recent_alloc,&num_client_writes);+ BgWriterStats.m_buf_alloc+=recent_alloc;+ BgWriterStats.m_buf_written_client+=num_client_writes;+ + /*
* This loop considers only unpinned buffers close to the clock sweep
* point.
*/
***************
*** 1088,1095 ****
num_to_scan = (int) ((NBuffers * bgwriter_lru_percent + 99) / 100);
num_written = 0;
- buf_id2 = StrategySyncStart();-
while (num_to_scan-- > 0)
{
if (SyncOneBuffer(buf_id2, true))
--- 1101,1106 ----
***************
*** 1451,1456 ****
--- 1462,1468 ----
false);
BufferFlushCount++;
+ StrategyReportWrite();
/*
* Mark the buffer as clean (unless BM_JUST_DIRTIED has become set) and
Index: src/backend/storage/buffer/freelist.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/storage/buffer/freelist.c,v
retrieving revision 1.58
diff -c -r1.58 freelist.c
*** src/backend/storage/buffer/freelist.c 5 Jan 2007 22:19:37 -0000 1.58
--- src/backend/storage/buffer/freelist.c 7 May 2007 01:30:24 -0000
***************
*** 29,34 ****
--- 29,36 ----
int firstFreeBuffer; /* Head of list of unused buffers */
int lastFreeBuffer; /* Tail of list of unused buffers */
+ int numGetBuffer; /* Calls to BufferAlloc since last reset */+ int numClientWrites; /* Buffers written by clients since last reset */
/*
* NOTE: lastFreeBuffer is undefined when firstFreeBuffer is -1 (that is,
***************
*** 42,47 ****
--- 44,51 ----
/* Backend-local state about whether currently vacuuming */
bool strategy_hint_vacuum = false;
+ /* Used to determine which type of process we're running as */+ extern bool am_bg_writer;
/*
* StrategyGetBuffer
***************
*** 62,67 ****
--- 66,72 ----
int trycounter;
LWLockAcquire(BufFreelistLock, LW_EXCLUSIVE);
+ StrategyControl->numGetBuffer++;
/*
* Try to get a buffer from the freelist. Note that the freeNext fields
***************
*** 176,182 ****
* BufferSync() will proceed circularly around the buffer array from there.
*/
int
! StrategySyncStart(void)
{
int result;
--- 181,187 ----
* BufferSync() will proceed circularly around the buffer array from there.
*/
int
! StrategySyncStart(int *num_buf_alloc,int *num_client_writes)
{
int result;
***************
*** 186,196 ****
--- 191,220 ----
*/
LWLockAcquire(BufFreelistLock, LW_EXCLUSIVE);
result = StrategyControl->nextVictimBuffer;
+ + /* Return and reset statistics for activity since last call */+ if (num_buf_alloc!=NULL) *num_buf_alloc=StrategyControl->numGetBuffer;+ if (num_client_writes!=NULL) *num_client_writes=StrategyControl->numClientWrites;+ StrategyControl->numGetBuffer = 0;+ StrategyControl->numClientWrites = 0;+
LWLockRelease(BufFreelistLock);
return result;
}
/*
+ * StrategyReportWrite -- After a buffer is written out, update+ * local statistics based on who did the writing+ */+ void+ StrategyReportWrite(void)+ {+ /* The background writer keeps track of buffers it writes already,+ only count client writes */+ if (!am_bg_writer) StrategyControl->numClientWrites++;+ }+ + /*
* StrategyHintVacuum -- tell us whether VACUUM is active
*/
void
***************
*** 270,275 ****
--- 294,301 ----
/* Initialize the clock sweep pointer */
StrategyControl->nextVictimBuffer = 0;
+ StrategyControl->numGetBuffer = 0;+ StrategyControl->numClientWrites = 0;
}
else
Assert(!init);
Index: src/backend/utils/adt/pgstatfuncs.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/pgstatfuncs.c,v
retrieving revision 1.41
diff -c -r1.41 pgstatfuncs.c
*** src/backend/utils/adt/pgstatfuncs.c 30 Mar 2007 18:34:55 -0000 1.41
--- src/backend/utils/adt/pgstatfuncs.c 7 May 2007 01:30:25 -0000
***************
*** 68,73 ****
--- 68,75 ----
extern Datum pg_stat_get_bgwriter_buf_written_all(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_bgwriter_maxwritten_lru(PG_FUNCTION_ARGS);
extern Datum pg_stat_get_bgwriter_maxwritten_all(PG_FUNCTION_ARGS);
+ extern Datum pg_stat_get_bgwriter_buf_written_client(PG_FUNCTION_ARGS);+ extern Datum pg_stat_get_bgwriter_buf_alloc(PG_FUNCTION_ARGS);
extern Datum pg_stat_clear_snapshot(PG_FUNCTION_ARGS);
extern Datum pg_stat_reset(PG_FUNCTION_ARGS);
***************
*** 808,813 ****
--- 810,826 ----
PG_RETURN_INT64(pgstat_fetch_global()->maxwritten_all);
}
+ Datum+ pg_stat_get_bgwriter_buf_written_client(PG_FUNCTION_ARGS)+ {+ PG_RETURN_INT64(pgstat_fetch_global()->buf_written_client);+ }+ + Datum+ pg_stat_get_bgwriter_buf_alloc(PG_FUNCTION_ARGS)+ {+ PG_RETURN_INT64(pgstat_fetch_global()->buf_alloc);+ }
/* Discard the active statistics snapshot */
Datum
Index: src/include/pgstat.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/pgstat.h,v
retrieving revision 1.58
diff -c -r1.58 pgstat.h
*** src/include/pgstat.h 30 Apr 2007 16:37:08 -0000 1.58
--- src/include/pgstat.h 7 May 2007 01:30:26 -0000
***************
*** 228,233 ****
--- 228,235 ----
PgStat_Counter m_buf_written_all;
PgStat_Counter m_maxwritten_lru;
PgStat_Counter m_maxwritten_all;
+ PgStat_Counter m_buf_written_client;+ PgStat_Counter m_buf_alloc;
} PgStat_MsgBgWriter;
***************
*** 329,334 ****
--- 331,338 ----
PgStat_Counter buf_written_all;
PgStat_Counter maxwritten_lru;
PgStat_Counter maxwritten_all;
+ PgStat_Counter buf_written_client;+ PgStat_Counter buf_alloc;
} PgStat_GlobalStats;
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.454
diff -c -r1.454 pg_proc.h
*** src/include/catalog/pg_proc.h 2 Apr 2007 03:49:40 -0000 1.454
--- src/include/catalog/pg_proc.h 7 May 2007 01:30:33 -0000
***************
*** 3001,3006 ****
--- 3001,3010 ----
DESCR("Statistics: Number of times the bgwriter stopped processing when it had written too many buffers during LRU scans");
DATA(insert OID = 2775 ( pg_stat_get_bgwriter_maxwritten_all PGNSP PGUID 12 1 0 f f t f s 0 20 "" _null_ _null_ _null_ pg_stat_get_bgwriter_maxwritten_all - _null_ ));
DESCR("Statistics: Number of times the bgwriter stopped processing when it had written too many buffers during all-buffer scans");
+ DATA(insert OID = 2776 ( pg_stat_get_bgwriter_buf_written_client PGNSP PGUID 12 1 0 f f t f s 0 20 "" _null_ _null_ _null_ pg_stat_get_bgwriter_buf_written_client - _null_ ));+ DESCR("Statistics: Number of buffers written by client backends");+ DATA(insert OID = 2777 ( pg_stat_get_bgwriter_buf_alloc PGNSP PGUID 12 1 0 f f t f s 0 20 "" _null_ _null_ _null_ pg_stat_get_bgwriter_buf_alloc - _null_ ));+ DESCR("Statistics: Number of buffers allocated for the shared buffer cache");
DATA(insert OID = 2230 ( pg_stat_clear_snapshot PGNSP PGUID 12 1 0 f f f f v 0 2278 "" _null_ _null_ _null_ pg_stat_clear_snapshot - _null_ ));
DESCR("Statistics: Discard current transaction's statistics snapshot");
DATA(insert OID = 2274 ( pg_stat_reset PGNSP PGUID 12 1 0 f f f f v 0 2278 "" _null_ _null_ _null_ pg_stat_reset - _null_ ));
Index: src/include/storage/buf_internals.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/storage/buf_internals.h,v
retrieving revision 1.89
diff -c -r1.89 buf_internals.h
*** src/include/storage/buf_internals.h 5 Jan 2007 22:19:57 -0000 1.89
--- src/include/storage/buf_internals.h 7 May 2007 01:30:33 -0000
***************
*** 186,192 ****
/* freelist.c */
extern volatile BufferDesc *StrategyGetBuffer(void);
extern void StrategyFreeBuffer(volatile BufferDesc *buf, bool at_head);
! extern int StrategySyncStart(void);
extern Size StrategyShmemSize(void);
extern void StrategyInitialize(bool init);
--- 186,193 ----
/* freelist.c */
extern volatile BufferDesc *StrategyGetBuffer(void);
extern void StrategyFreeBuffer(volatile BufferDesc *buf, bool at_head);
! extern int StrategySyncStart(int *num_buf_alloc, int *num_client_writes);
! extern void StrategyReportWrite();
extern Size StrategyShmemSize(void);
extern void StrategyInitialize(bool init);
Index: src/test/regress/expected/rules.out
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/rules.out,v
retrieving revision 1.127
diff -c -r1.127 rules.out
*** src/test/regress/expected/rules.out 30 Mar 2007 18:34:56 -0000 1.127
--- src/test/regress/expected/rules.out 7 May 2007 01:30:36 -0000
***************
*** 1292,1298 ****
pg_stat_activity | SELECT d.oid AS datid, d.datname, pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_userid(s.backendid) AS usesysid, u.rolname AS usename, pg_stat_get_backend_activity(s.backendid) AS current_query, pg_stat_get_backend_waiting(s.backendid) AS waiting, pg_stat_get_backend_txn_start(s.backendid) AS txn_start, pg_stat_get_backend_activity_start(s.backendid) AS query_start, pg_stat_get_backend_start(s.backendid) AS backend_start, pg_stat_get_backend_client_addr(s.backendid) AS client_addr, pg_stat_get_backend_client_port(s.backendid) AS client_port FROM pg_database d, (SELECT pg_stat_get_backend_idset() AS backendid) s, pg_authid u WHERE ((pg_stat_get_backend_dbid(s.backendid) = d.oid) AND (pg_stat_get_backend_userid(s.backendid) = u.oid));
pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"]));
pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS seq_scan, pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan, ((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del, pg_stat_get_live_tuples(c.oid) AS n_live_tup, pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum, pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, pg_stat_get_last_analyze_time(c.oid) AS last_analyze, pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) GROUP BY c.oid, n.nspname, c.relname;
! pg_stat_bgwriter | SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed, pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req, pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint, pg_stat_get_bgwriter_buf_written_lru() AS buffers_lru, pg_stat_get_bgwriter_buf_written_all() AS buffers_all, pg_stat_get_bgwriter_maxwritten_lru() AS maxwritten_lru, pg_stat_get_bgwriter_maxwritten_all() AS maxwritten_all;
pg_stat_database | SELECT d.oid AS datid, d.datname, pg_stat_get_db_numbackends(d.oid) AS numbackends, pg_stat_get_db_xact_commit(d.oid) AS xact_commit, pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback, (pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid)) AS blks_read, pg_stat_get_db_blocks_hit(d.oid) AS blks_hit, pg_stat_get_db_tuples_returned(d.oid) AS tup_returned, pg_stat_get_db_tuples_fetched(d.oid) AS tup_fetched, pg_stat_get_db_tuples_inserted(d.oid) AS tup_inserted, pg_stat_get_db_tuples_updated(d.oid) AS tup_updated, pg_stat_get_db_tuples_deleted(d.oid) AS tup_deleted FROM pg_database d;
pg_stat_sys_indexes | SELECT pg_stat_all_indexes.relid, pg_stat_all_indexes.indexrelid, pg_stat_all_indexes.schemaname, pg_stat_all_indexes.relname, pg_stat_all_indexes.indexrelname, pg_stat_all_indexes.idx_scan, pg_stat_all_indexes.idx_tup_read, pg_stat_all_indexes.idx_tup_fetch FROM pg_stat_all_indexes WHERE (pg_stat_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::"name", 'pg_toast'::"name", 'information_schema'::"name"]));
pg_stat_sys_tables | SELECT pg_stat_all_tables.relid, pg_stat_all_tables.schemaname, pg_stat_all_tables.relname, pg_stat_all_tables.seq_scan, pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan, pg_stat_all_tables.idx_tup_fetch, pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup_upd, pg_stat_all_tables.n_tup_del, pg_stat_all_tables.n_live_tup, pg_stat_all_tables.n_dead_tup, pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autovacuum, pg_stat_all_tables.last_analyze, pg_stat_all_tables.last_autoanalyze FROM pg_stat_all_tables WHERE (pg_stat_all_tables.schemaname = ANY (ARRAY['pg_catalog'::"name", 'pg_toast'::"name", 'information_schema'::"name"]));
--- 1292,1298 ----
pg_stat_activity | SELECT d.oid AS datid, d.datname, pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_userid(s.backendid) AS usesysid, u.rolname AS usename, pg_stat_get_backend_activity(s.backendid) AS current_query, pg_stat_get_backend_waiting(s.backendid) AS waiting, pg_stat_get_backend_txn_start(s.backendid) AS txn_start, pg_stat_get_backend_activity_start(s.backendid) AS query_start, pg_stat_get_backend_start(s.backendid) AS backend_start, pg_stat_get_backend_client_addr(s.backendid) AS client_addr, pg_stat_get_backend_client_port(s.backendid) AS client_port FROM pg_database d, (SELECT pg_stat_get_backend_idset() AS backendid) s, pg_authid u WHERE ((pg_stat_get_backend_dbid(s.backendid) = d.oid) AND (pg_stat_get_backend_userid(s.backendid) = u.oid));
pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"]));
pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS seq_scan, pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan, ((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del, pg_stat_get_live_tuples(c.oid) AS n_live_tup, pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum, pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, pg_stat_get_last_analyze_time(c.oid) AS last_analyze, pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) GROUP BY c.oid, n.nspname, c.relname;
! pg_stat_bgwriter | SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed, pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req, pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint, pg_stat_get_bgwriter_buf_written_lru() AS buffers_lru, pg_stat_get_bgwriter_buf_written_all() AS buffers_all, pg_stat_get_bgwriter_maxwritten_lru() AS maxwritten_lru, pg_stat_get_bgwriter_maxwritten_all() AS maxwritten_all, pg_stat_get_bgwriter_buf_written_client() AS buffers_client, pg_stat_get_bgwriter_buf_alloc() AS buffers_alloc;
pg_stat_database | SELECT d.oid AS datid, d.datname, pg_stat_get_db_numbackends(d.oid) AS numbackends, pg_stat_get_db_xact_commit(d.oid) AS xact_commit, pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback, (pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid)) AS blks_read, pg_stat_get_db_blocks_hit(d.oid) AS blks_hit, pg_stat_get_db_tuples_returned(d.oid) AS tup_returned, pg_stat_get_db_tuples_fetched(d.oid) AS tup_fetched, pg_stat_get_db_tuples_inserted(d.oid) AS tup_inserted, pg_stat_get_db_tuples_updated(d.oid) AS tup_updated, pg_stat_get_db_tuples_deleted(d.oid) AS tup_deleted FROM pg_database d;
pg_stat_sys_indexes | SELECT pg_stat_all_indexes.relid, pg_stat_all_indexes.indexrelid, pg_stat_all_indexes.schemaname, pg_stat_all_indexes.relname, pg_stat_all_indexes.indexrelname, pg_stat_all_indexes.idx_scan, pg_stat_all_indexes.idx_tup_read, pg_stat_all_indexes.idx_tup_fetch FROM pg_stat_all_indexes WHERE (pg_stat_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::"name", 'pg_toast'::"name", 'information_schema'::"name"]));
pg_stat_sys_tables | SELECT pg_stat_all_tables.relid, pg_stat_all_tables.schemaname, pg_stat_all_tables.relname, pg_stat_all_tables.seq_scan, pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan, pg_stat_all_tables.idx_tup_fetch, pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup_upd, pg_stat_all_tables.n_tup_del, pg_stat_all_tables.n_live_tup, pg_stat_all_tables.n_dead_tup, pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autovacuum, pg_stat_all_tables.last_analyze, pg_stat_all_tables.last_autoanalyze FROM pg_stat_all_tables WHERE (pg_stat_all_tables.schemaname = ANY (ARRAY['pg_catalog'::"name", 'pg_toast'::"name", 'information_schema'::"name"]));
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gsmith@gregsmith.com, pgsql-patches@postgresql.org
Subject: Re: Automatic adjustment of bgwriter_lru_maxpages
In-Reply-To: <Pine.GSO.4.64.0705121926420.9615@westnet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox