On Mon, 2005-01-03 at 23:03, Bruce Momjian wrote:
> Simon Riggs wrote:
> > On Mon, 2005-01-03 at 20:09, Bruce Momjian wrote:
> > > OK, we have a submitted patch that attempts to improve bgwriter by
> > > making bgwriter_percent control what percentage of the buffer is
> > > scanned.
> > >
> > > The patch still needs doc changes and a change to the default value but
> > > at this point we need a vote on the patch. Is it:
> > >
> > > * too late for 8.0
> > > * not the right improvement
> > > * to be applied with doc/default additions
> > >
> > > Comments?
> > >
> > > ---------------------------------------------------------------------------
> > >
> > > Simon Riggs wrote:
> > > > On Sat, 2005-01-01 at 17:47, Simon Riggs wrote:
> > > > > On Sat, 2005-01-01 at 17:01, Bruce Momjian wrote:
> > > > > > Simon Riggs wrote:
> > > > > >
> > > > > > > Well, I think we're saying: its not in 8.0 now, and we take our
> > > > > > > time to
> > > > > > > consider patches for 8.1 and accept the situation that the
> > > > > > > parameter
> > > > > > > names/meaning will change in next release.
> > > > > >
> >
> > I hear veto ... so the above situation stands then: 8.1 it is.
> >
> > Not unhappy...I want this thing released as much as the next man...
>
> Well, we went through the process and that's the best we can do.
Here's my bgwriter instrumentation patch, which gives info that could
allow the bgwriter settings to be tuned.
--
Best Regards, Simon Riggs
Index: src/backend/storage/buffer/bufmgr.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/storage/buffer/bufmgr.c,v
retrieving revision 1.182
diff -d -c -r1.182 bufmgr.c
*** src/backend/storage/buffer/bufmgr.c 24 Nov 2004 02:56:17 -0000 1.182
--- src/backend/storage/buffer/bufmgr.c 4 Jan 2005 00:04:18 -0000
***************
*** 440,445 ****
--- 440,446 ----
UnpinBuffer(buf, true);
inProgress = FALSE;
buf = NULL;
+ StrategyBufferStatWastedIO();
}
}
} while (buf == NULL);
***************
*** 682,687 ****
--- 683,689 ----
BufferDesc **dirty_buffers;
BufferTag *buftags;
int num_buffer_dirty;
+ int num_buffer_cleaned = 0;
int i;
/* If either limit is zero then we are disabled from doing anything... */
***************
*** 770,775 ****
--- 772,778 ----
TerminateBufferIO(bufHdr, 0);
UnpinBuffer(bufHdr, true);
+ num_buffer_cleaned++;
}
LWLockRelease(BufMgrLock);
***************
*** 777,782 ****
--- 780,787 ----
pfree(dirty_buffers);
pfree(buftags);
+ StrategyBufferStatCleaned(num_buffer_cleaned);
+
return num_buffer_dirty;
}
Index: src/backend/storage/buffer/freelist.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/storage/buffer/freelist.c,v
retrieving revision 1.48
diff -d -c -r1.48 freelist.c
*** src/backend/storage/buffer/freelist.c 16 Sep 2004 16:58:31 -0000 1.48
--- src/backend/storage/buffer/freelist.c 4 Jan 2005 00:04:18 -0000
***************
*** 115,120 ****
--- 115,133 ----
} while(0)
+ void
+ StrategyBufferStatWastedIO(void)
+ {
+ StrategyControl->num_wasted++;
+ }
+
+ void
+ StrategyBufferStatCleaned(long num_cleaned)
+ {
+ StrategyControl->num_cleaned += num_cleaned;
+ }
+
+
/*
* Printout for use when DebugSharedBuffers is enabled
*/
***************
*** 130,159 ****
t1_hit,
t2_hit,
b2_hit;
- int id,
- t1_clean,
- t2_clean;
ErrorContextCallback *errcxtold;
- id = StrategyControl->listHead[STRAT_LIST_T1];
- t1_clean = 0;
- while (id >= 0)
- {
- if (BufferDescriptors[StrategyCDB[id].buf_id].flags & BM_DIRTY)
- break;
- t1_clean++;
- id = StrategyCDB[id].next;
- }
- id = StrategyControl->listHead[STRAT_LIST_T2];
- t2_clean = 0;
- while (id >= 0)
- {
- if (BufferDescriptors[StrategyCDB[id].buf_id].flags & BM_DIRTY)
- break;
- t2_clean++;
- id = StrategyCDB[id].next;
- }
-
if (StrategyControl->num_lookup == 0)
all_hit = b1_hit = t1_hit = t2_hit = b2_hit = 0;
else
--- 143,150 ----
***************
*** 166,185 ****
StrategyControl->num_lookup);
b2_hit = (StrategyControl->num_hit[STRAT_LIST_B2] * 100 /
StrategyControl->num_lookup);
! all_hit = b1_hit + t1_hit + t2_hit + b2_hit;
}
errcxtold = error_context_stack;
error_context_stack = NULL;
elog(DEBUG1, "ARC T1target=%5d B1len=%5d T1len=%5d T2len=%5d B2len=%5d",
T1_TARGET, B1_LENGTH, T1_LENGTH, T2_LENGTH, B2_LENGTH);
! elog(DEBUG1, "ARC total =%4ld%% B1hit=%4ld%% T1hit=%4ld%% T2hit=%4ld%% B2hit=%4ld%%",
all_hit, b1_hit, t1_hit, t2_hit, b2_hit);
! elog(DEBUG1, "ARC clean buffers at LRU T1= %5d T2= %5d",
! t1_clean, t2_clean);
! error_context_stack = errcxtold;
StrategyControl->num_lookup = 0;
StrategyControl->num_hit[STRAT_LIST_B1] = 0;
StrategyControl->num_hit[STRAT_LIST_T1] = 0;
StrategyControl->num_hit[STRAT_LIST_T2] = 0;
--- 157,188 ----
StrategyControl->num_lookup);
b2_hit = (StrategyControl->num_hit[STRAT_LIST_B2] * 100 /
StrategyControl->num_lookup);
! all_hit = t1_hit + t2_hit;
}
errcxtold = error_context_stack;
error_context_stack = NULL;
elog(DEBUG1, "ARC T1target=%5d B1len=%5d T1len=%5d T2len=%5d B2len=%5d",
T1_TARGET, B1_LENGTH, T1_LENGTH, T2_LENGTH, B2_LENGTH);
! elog(DEBUG1, "ARC total =%5ld%% B1hit=%4ld%% T1hit=%4ld%% T2hit=%4ld%% B2hit=%4ld%%",
all_hit, b1_hit, t1_hit, t2_hit, b2_hit);
!
! if (StrategyControl->num_lookup == 0)
! all_hit = 0;
! else
! all_hit = (StrategyControl->num_dirty * 100 /
! (StrategyControl->num_lookup -
! StrategyControl->num_hit[STRAT_LIST_T1] -
! StrategyControl->num_hit[STRAT_LIST_T2]));
!
! elog(DEBUG1, "ARC buffer dirty misses=%5ld%% (wasted=%5ld); cleaned=%9ld",
! all_hit, StrategyControl->num_wasted,
! StrategyControl->num_cleaned);
StrategyControl->num_lookup = 0;
+ StrategyControl->num_dirty = 0;
+ StrategyControl->num_cleaned = 0;
+ StrategyControl->num_wasted = 0;
StrategyControl->num_hit[STRAT_LIST_B1] = 0;
StrategyControl->num_hit[STRAT_LIST_T1] = 0;
StrategyControl->num_hit[STRAT_LIST_T2] = 0;
***************
*** 386,391 ****
--- 389,396 ----
{
*cdb_replace_index = cdb_id;
Assert(StrategyCDB[cdb_id].list == STRAT_LIST_T1);
+ if ((buf->flags & BM_DIRTY) || (buf->cntxDirty))
+ StrategyControl->num_dirty++;
return buf;
}
cdb_id = StrategyCDB[cdb_id].next;
***************
*** 402,407 ****
--- 407,414 ----
{
*cdb_replace_index = cdb_id;
Assert(StrategyCDB[cdb_id].list == STRAT_LIST_T2);
+ if ((buf->flags & BM_DIRTY) || (buf->cntxDirty))
+ StrategyControl->num_dirty++;
return buf;
}
cdb_id = StrategyCDB[cdb_id].next;
***************
*** 425,430 ****
--- 432,439 ----
{
*cdb_replace_index = cdb_id;
Assert(StrategyCDB[cdb_id].list == STRAT_LIST_T2);
+ if ((buf->flags & BM_DIRTY) || (buf->cntxDirty))
+ StrategyControl->num_dirty++;
return buf;
}
cdb_id = StrategyCDB[cdb_id].next;
***************
*** 441,446 ****
--- 450,457 ----
{
*cdb_replace_index = cdb_id;
Assert(StrategyCDB[cdb_id].list == STRAT_LIST_T1);
+ if ((buf->flags & BM_DIRTY) || (buf->cntxDirty))
+ StrategyControl->num_dirty++;
return buf;
}
cdb_id = StrategyCDB[cdb_id].next;
Index: src/include/storage/buf_internals.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/storage/buf_internals.h,v
retrieving revision 1.74
diff -d -c -r1.74 buf_internals.h
*** src/include/storage/buf_internals.h 16 Oct 2004 18:05:07 -0000 1.74
--- src/include/storage/buf_internals.h 4 Jan 2005 00:04:20 -0000
***************
*** 153,158 ****
--- 153,161 ----
long num_lookup; /* Some hit statistics */
long num_hit[STRAT_NUM_LISTS];
+ long num_dirty;
+ long num_cleaned;
+ long num_wasted;
time_t stat_report;
/* Array of CDB's starts here */
***************
*** 187,192 ****
--- 190,199 ----
int max_buffers);
extern void StrategyInitialize(bool init);
+ extern void StrategyBufferStatWastedIO(void);
+ extern void StrategyBufferStatCleaned(long num_cleaned);
+
+
/* buf_table.c */
extern void InitBufTable(int size);
extern int BufTableLookup(BufferTag *tagPtr);
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
