Notification when freespaces empty
Hello all,
I think that this patch is useful to decide when to vacuum.
It notifies when freespace empties as follows:
$ ./pgbench -i
$ ./pgbench -n -t 1000
LOG: FreeSpace for "public.accounts" becomes empty. (stored=1, avg=159, min=128)
LOG: FreeSpace for "public.tellers" becomes empty. (stored=1, avg=238, min=40)
LOG: FreeSpace for "public.branches" becomes empty. (stored=1, avg=238, min=36)
$ vacuumdb
VACUUM
$ ./pgbench -n -t 2000
LOG: FreeSpace for "public.history" becomes empty. (stored=1, avg=208, min=52)
LOG: FreeSpace for "public.accounts" becomes empty. (stored=758, avg=159, min=128)
LOG: FreeSpace for "public.branches" becomes empty. (stored=5, avg=191, min=36)
LOG: FreeSpace for "public.tellers" becomes empty. (stored=6, avg=184, min=40)
Furthermore, this patch detaches empty fsmpages then.
Freespaces keep being scanned after they empties,
but it seems to be bootless effort.
Is this useful?
---
ITAGAKI Takahiro <itagaki.takahiro@lab.ntt.co.jp>
NTT Cyber Space Laboratories
Nippon Telegraph and Telephone Corporation.
Attachments:
freespace.diffapplication/octet-stream; name=freespace.diffDownload
*** freespace.c Sun Apr 24 12:51:49 2005
--- freespace.new.c Fri May 20 13:13:37 2005
***************
*** 70,75 ****
--- 70,78 ----
#include "storage/lwlock.h"
#include "storage/shmem.h"
+ #include "utils/relcache.h"
+ #include "utils/lsyscache.h"
+
/* Initial value for average-request moving average */
#define INITIAL_AVERAGE ((Size) (BLCKSZ / 32))
*************** typedef BlockIdData IndexFSMPageData;
*** 123,128 ****
--- 126,132 ----
* relfilenode
* isIndex
* avgRequest
+ * minRequest
* lastPageCount
* storedPages
* arena data array of storedPages FSMPageData or IndexFSMPageData
*************** typedef struct FsmCacheRelHeader
*** 152,157 ****
--- 156,162 ----
RelFileNode key; /* hash key (must be first) */
bool isIndex; /* if true, we store only page numbers */
uint32 avgRequest; /* moving average of space requests */
+ uint32 minRequest; /* minimum of space requests */
int32 lastPageCount; /* pages passed to RecordRelationFreeSpace */
int32 storedPages; /* # of pages stored in arena */
} FsmCacheRelHeader;
*************** struct FSMRelation
*** 206,211 ****
--- 211,217 ----
FSMRelation *priorPhysical; /* prior rel in arena-storage order */
bool isIndex; /* if true, we store only page numbers */
Size avgRequest; /* moving average of space requests */
+ Size minRequest; /* minimum of space requests */
int lastPageCount; /* pages passed to RecordRelationFreeSpace */
int firstChunk; /* chunk # of my first chunk in arena */
int storedPages; /* # of pages stored in arena */
*************** GetPageWithFreeSpace(RelFileNode *rel, S
*** 385,390 ****
--- 391,398 ----
cur_avg += ((int) spaceNeeded - cur_avg) / 32;
fsmrel->avgRequest = (Size) cur_avg;
+ if(fsmrel->minRequest > spaceNeeded)
+ fsmrel->minRequest = spaceNeeded;
}
freepage = find_free_space(fsmrel, spaceNeeded);
LWLockRelease(FreeSpaceLock);
*************** RecordAndGetPageWithFreeSpace(RelFileNod
*** 429,434 ****
--- 437,444 ----
cur_avg += ((int) spaceNeeded - cur_avg) / 32;
fsmrel->avgRequest = (Size) cur_avg;
+ if(fsmrel->minRequest > spaceNeeded)
+ fsmrel->minRequest = spaceNeeded;
}
/* Do the Get */
freepage = find_free_space(fsmrel, spaceNeeded);
*************** DumpFreeSpaceMap(int code, Datum arg)
*** 793,798 ****
--- 803,809 ----
relheader.key = fsmrel->key;
relheader.isIndex = fsmrel->isIndex;
relheader.avgRequest = fsmrel->avgRequest;
+ relheader.minRequest = fsmrel->minRequest;
relheader.lastPageCount = fsmrel->lastPageCount;
relheader.storedPages = fsmrel->storedPages;
if (fwrite(&relheader, 1, sizeof(relheader), fp) != sizeof(relheader))
*************** LoadFreeSpaceMap(void)
*** 902,907 ****
--- 913,919 ----
if (fread(&relheader, 1, sizeof(relheader), fp) != sizeof(relheader) ||
(relheader.isIndex != false && relheader.isIndex != true) ||
relheader.avgRequest >= BLCKSZ ||
+ relheader.minRequest >= BLCKSZ ||
relheader.lastPageCount < 0 ||
relheader.storedPages < 0)
{
*************** LoadFreeSpaceMap(void)
*** 936,941 ****
--- 948,954 ----
*/
fsmrel = create_fsm_rel(&relheader.key);
fsmrel->avgRequest = relheader.avgRequest;
+ fsmrel->minRequest = relheader.minRequest;
curAlloc = realloc_fsm_rel(fsmrel, relheader.lastPageCount,
relheader.isIndex);
*************** create_fsm_rel(RelFileNode *rel)
*** 1046,1051 ****
--- 1059,1065 ----
/* New hashtable entry, initialize it (hash_search set the key) */
fsmrel->isIndex = false; /* until we learn different */
fsmrel->avgRequest = INITIAL_AVERAGE;
+ fsmrel->minRequest = BLCKSZ-1;
fsmrel->lastPageCount = 0;
fsmrel->firstChunk = -1; /* no space allocated */
fsmrel->storedPages = 0;
*************** find_free_space(FSMRelation *fsmrel, Siz
*** 1268,1273 ****
--- 1282,1315 ----
pageIndex = 0;
}
+ if(spaceNeeded >= fsmrel->minRequest && fsmrel->storedPages > 0)
+ {
+ /* Free space becomes empty. */
+
+ /* Notify. */
+ Relation r;
+ r = RelationIdGetRelation(fsmrel->key.relNode);
+ if(r)
+ {
+ elog(LOG, "FreeSpace for \"%s.%s\" becomes empty. (stored=%d, avg=%d, min=%d)",
+ get_namespace_name(RelationGetNamespace(r)),
+ RelationGetRelationName(r),
+ fsmrel->storedPages, fsmrel->avgRequest, fsmrel->minRequest);
+ RelationClose(r);
+ }
+ else
+ {
+ elog(LOG, "FreeSpace for %u/%u/%u becomes empty. (stored=%d, avg=%d, min=%d)",
+ fsmrel->key.spcNode, fsmrel->key.dbNode, fsmrel->key.relNode,
+ fsmrel->storedPages, fsmrel->avgRequest, fsmrel->minRequest);
+ }
+
+ /* Detach empty fsm relation. */
+ fsmrel->storedPages = 0;
+ /* XXX: or delete_fsm_rel(fsmrel) */
+ /* XXX: or kick autovacuum */
+ }
+
return InvalidBlockNumber; /* nothing found */
}
*************** DumpFreeSpace(void)
*** 1876,1885 ****
for (fsmrel = FreeSpaceMap->usageList; fsmrel; fsmrel = fsmrel->nextUsage)
{
relNum++;
! fprintf(stderr, "Map %d: rel %u/%u/%u isIndex %d avgRequest %u lastPageCount %d nextPage %d\nMap= ",
relNum,
fsmrel->key.spcNode, fsmrel->key.dbNode, fsmrel->key.relNode,
! (int) fsmrel->isIndex, fsmrel->avgRequest,
fsmrel->lastPageCount, fsmrel->nextPage);
if (fsmrel->isIndex)
{
--- 1918,1927 ----
for (fsmrel = FreeSpaceMap->usageList; fsmrel; fsmrel = fsmrel->nextUsage)
{
relNum++;
! fprintf(stderr, "Map %d: rel %u/%u/%u isIndex %d avgRequest %d minRequest %d lastPageCount %d nextPage %d\nMap= ",
relNum,
fsmrel->key.spcNode, fsmrel->key.dbNode, fsmrel->key.relNode,
! (int) fsmrel->isIndex, fsmrel->avgRequest, fsmrel->minRequest,
fsmrel->lastPageCount, fsmrel->nextPage);
if (fsmrel->isIndex)
{
On Fri, 2005-05-20 at 14:41 +0900, ITAGAKI Takahiro wrote:
LOG: FreeSpace for "public.accounts" becomes empty. (stored=1, avg=159, min=128)
Looks useful to me, until we patch up FSMs more fully in the future.
Might need rewording. Stored -> stored pages, Avg -> Avg Row Length.
Probably should be a DEBUG1 log line also.
It would be even more useful if this was signalled (perhaps to stats) in
such a way that autovacuum could act upon this knowledge.
Furthermore, this patch detaches empty fsmpages then.
Freespaces keep being scanned after they empties,
but it seems to be bootless effort.
Does that do anything useful though?
I thought we don't reallocate until VACUUM time, whereupon we identify
any empty slots and reuse them. Who cares whether we deallocate earlier?
Best Regards, Simon Riggs
ITAGAKI Takahiro <itagaki.takahiro@lab.ntt.co.jp> writes:
I think that this patch is useful to decide when to vacuum.
It notifies when freespace empties as follows:
No, it doesn't complain that there is no freespace, it complains when
a specific request can't be fulfilled; which for a large request might
not mean much of anything. I haven't quite worked out what you are
trying to do with the minRequest restriction but I don't think that
really makes things better. Also, aren't you throwing away remaining
free space in order to prevent the log message from appearing repeatedly?
$ ./pgbench -n -t 1000
LOG: FreeSpace for "public.accounts" becomes empty. (stored=1, avg=159, min=128)
LOG: FreeSpace for "public.tellers" becomes empty. (stored=1, avg=238, min=40)
LOG: FreeSpace for "public.branches" becomes empty. (stored=1, avg=238, min=36)
A bigger issue is what is the point of logging such a transient
condition --- who's going to read it? It's possible that autovacuum
would like to know about this, and after we get autovacuum integrated
into the backend I'm sure we'll be taking a look at letting it use FSM
information. But I can't see that anyone is going to sit and watch
the postmaster log to decide to trigger vacuums.
regards, tom lane
Simon Riggs <simon@2ndquadrant.com> wrote:
Furthermore, this patch detaches empty fsmpages then.
Does that do anything useful though?
I thought we don't reallocate until VACUUM time, whereupon we identify
any empty slots and reuse them. Who cares whether we deallocate earlier?
Yes, we cannot reuse them until next VACUUM.
But I think it is a problem that FSMs keep being scanned after they are almost empty.
In such a case, most stored pages are touched whenever new pages
are requested. I intended to cut FSMs earlier in order to omit the scans.
---
ITAGAKI Takahiro
NTT Cyber Space Laboratories