Hi all,
I've done a quick hack to implement PCTFREE on PostgreSQL.
As you know, it's inspired by Oracle's PCTFREE.
http://www.csee.umbc.edu/help/oracle8/server.815/a67772/schema.htm#990
http://www.comp.hkbu.edu.hk/docs/o/oracle10g/server.101/b10743/cncpt031.gif
Pre-allocated space for each block(page) can improve heap_update() performance,
because heap_update() looks for the free space in same block
to insert new row.
According to my experiments, pgbench score was improved 10% or more
with 1024 bytes free space.
Any comments? Is this idea good, or not?
Thanks.
--
NAGAYASU Satoshi <[EMAIL PROTECTED]>
diff -rc postgresql-8.0.0.orig/src/backend/access/heap/heapam.c
postgresql-8.0.0.pctfree/src/backend/access/heap/heapam.c
*** postgresql-8.0.0.orig/src/backend/access/heap/heapam.c 2005-01-01
06:59:16.000000000 +0900
--- postgresql-8.0.0.pctfree/src/backend/access/heap/heapam.c 2005-08-20
23:20:45.017901208 +0900
***************
*** 1151,1157 ****
heap_tuple_toast_attrs(relation, tup, NULL);
/* Find buffer to insert this tuple into */
! buffer = RelationGetBufferForTuple(relation, tup->t_len, InvalidBuffer);
/* NO EREPORT(ERROR) from here till changes are logged */
START_CRIT_SECTION();
--- 1151,1160 ----
heap_tuple_toast_attrs(relation, tup, NULL);
/* Find buffer to insert this tuple into */
! buffer = RelationGetBufferForTuple(relation,
!
tup->t_len,
!
InvalidBuffer,
!
true);
/* NO EREPORT(ERROR) from here till changes are logged */
START_CRIT_SECTION();
***************
*** 1671,1678 ****
if (newtupsize > pagefree)
{
/* Assume there's no chance to put newtup on same page.
*/
! newbuf = RelationGetBufferForTuple(relation,
newtup->t_len,
!
buffer);
}
else
{
--- 1674,1683 ----
if (newtupsize > pagefree)
{
/* Assume there's no chance to put newtup on same page.
*/
! newbuf = RelationGetBufferForTuple(relation,
!
newtup->t_len,
!
buffer,
!
false);
}
else
{
***************
*** 1688,1695 ****
* should seldom be taken.
*/
LockBuffer(buffer, BUFFER_LOCK_UNLOCK);
! newbuf = RelationGetBufferForTuple(relation,
newtup->t_len,
!
buffer);
}
else
{
--- 1693,1702 ----
* should seldom be taken.
*/
LockBuffer(buffer, BUFFER_LOCK_UNLOCK);
! newbuf = RelationGetBufferForTuple(relation,
!
newtup->t_len,
!
buffer,
!
false);
}
else
{
diff -rc postgresql-8.0.0.orig/src/backend/access/heap/hio.c
postgresql-8.0.0.pctfree/src/backend/access/heap/hio.c
*** postgresql-8.0.0.orig/src/backend/access/heap/hio.c 2005-01-01
06:59:16.000000000 +0900
--- postgresql-8.0.0.pctfree/src/backend/access/heap/hio.c 2005-08-20
23:35:44.986085248 +0900
***************
*** 89,95 ****
*/
Buffer
RelationGetBufferForTuple(Relation relation, Size len,
! Buffer otherBuffer)
{
Buffer buffer = InvalidBuffer;
Page pageHeader;
--- 89,95 ----
*/
Buffer
RelationGetBufferForTuple(Relation relation, Size len,
! Buffer otherBuffer, bool
forInsert)
{
Buffer buffer = InvalidBuffer;
Page pageHeader;
***************
*** 136,142 ****
* We have no cached target page, so ask the FSM for an initial
* target.
*/
! targetBlock = GetPageWithFreeSpace(&relation->rd_node, len);
/*
* If the FSM knows nothing of the rel, try the last page before
--- 136,142 ----
* We have no cached target page, so ask the FSM for an initial
* target.
*/
! targetBlock = GetPageWithFreeSpace(&relation->rd_node, len,
forInsert);
/*
* If the FSM knows nothing of the rel, try the last page before
***************
*** 192,198 ****
*/
pageHeader = (Page) BufferGetPage(buffer);
pageFreeSpace = PageGetFreeSpace(pageHeader);
! if (len <= pageFreeSpace)
{
/* use this page as future insert target, too */
relation->rd_targblock = targetBlock;
--- 192,198 ----
*/
pageHeader = (Page) BufferGetPage(buffer);
pageFreeSpace = PageGetFreeSpace(pageHeader);
! if ((forInsert ? (len+1024) : len) <= pageFreeSpace)
{
/* use this page as future insert target, too */
relation->rd_targblock = targetBlock;
***************
*** 221,227 ****
targetBlock = RecordAndGetPageWithFreeSpace(&relation->rd_node,
targetBlock,
pageFreeSpace,
!
len);
}
/*
--- 221,228 ----
targetBlock = RecordAndGetPageWithFreeSpace(&relation->rd_node,
targetBlock,
pageFreeSpace,
!
len,
!
forInsert);
}
/*
diff -rc postgresql-8.0.0.orig/src/backend/storage/freespace/freespace.c
postgresql-8.0.0.pctfree/src/backend/storage/freespace/freespace.c
*** postgresql-8.0.0.orig/src/backend/storage/freespace/freespace.c
2005-01-01 07:00:54.000000000 +0900
--- postgresql-8.0.0.pctfree/src/backend/storage/freespace/freespace.c
2005-08-20 23:18:21.413732360 +0900
***************
*** 229,235 ****
static void unlink_fsm_rel_usage(FSMRelation *fsmrel);
static void link_fsm_rel_storage(FSMRelation *fsmrel);
static void unlink_fsm_rel_storage(FSMRelation *fsmrel);
! static BlockNumber find_free_space(FSMRelation *fsmrel, Size spaceNeeded);
static BlockNumber find_index_free_space(FSMRelation *fsmrel);
static void fsm_record_free_space(FSMRelation *fsmrel, BlockNumber page,
Size spaceAvail);
--- 229,237 ----
static void unlink_fsm_rel_usage(FSMRelation *fsmrel);
static void link_fsm_rel_storage(FSMRelation *fsmrel);
static void unlink_fsm_rel_storage(FSMRelation *fsmrel);
! static BlockNumber find_free_space(FSMRelation *fsmrel,
! Size
spaceNeeded,
! bool
forInsert);
static BlockNumber find_index_free_space(FSMRelation *fsmrel);
static void fsm_record_free_space(FSMRelation *fsmrel, BlockNumber page,
Size spaceAvail);
***************
*** 359,365 ****
* extend the relation.
*/
BlockNumber
! GetPageWithFreeSpace(RelFileNode *rel, Size spaceNeeded)
{
FSMRelation *fsmrel;
BlockNumber freepage;
--- 361,367 ----
* extend the relation.
*/
BlockNumber
! GetPageWithFreeSpace(RelFileNode *rel, Size spaceNeeded, bool forInsert)
{
FSMRelation *fsmrel;
BlockNumber freepage;
***************
*** 384,390 ****
cur_avg += ((int) spaceNeeded - cur_avg) / 32;
fsmrel->avgRequest = (Size) cur_avg;
}
! freepage = find_free_space(fsmrel, spaceNeeded);
LWLockRelease(FreeSpaceLock);
return freepage;
}
--- 386,392 ----
cur_avg += ((int) spaceNeeded - cur_avg) / 32;
fsmrel->avgRequest = (Size) cur_avg;
}
! freepage = find_free_space(fsmrel, spaceNeeded, forInsert);
LWLockRelease(FreeSpaceLock);
return freepage;
}
***************
*** 399,405 ****
RecordAndGetPageWithFreeSpace(RelFileNode *rel,
BlockNumber oldPage,
Size oldSpaceAvail,
! Size spaceNeeded)
{
FSMRelation *fsmrel;
BlockNumber freepage;
--- 401,408 ----
RecordAndGetPageWithFreeSpace(RelFileNode *rel,
BlockNumber oldPage,
Size oldSpaceAvail,
! Size spaceNeeded,
! bool forInsert)
{
FSMRelation *fsmrel;
BlockNumber freepage;
***************
*** 429,435 ****
fsmrel->avgRequest = (Size) cur_avg;
}
/* Do the Get */
! freepage = find_free_space(fsmrel, spaceNeeded);
LWLockRelease(FreeSpaceLock);
return freepage;
}
--- 432,438 ----
fsmrel->avgRequest = (Size) cur_avg;
}
/* Do the Get */
! freepage = find_free_space(fsmrel, spaceNeeded, forInsert);
LWLockRelease(FreeSpaceLock);
return freepage;
}
***************
*** 1204,1210 ****
* if no success.
*/
static BlockNumber
! find_free_space(FSMRelation *fsmrel, Size spaceNeeded)
{
FSMPageData *info;
int pagesToCheck, /* outer loop counter */
--- 1207,1213 ----
* if no success.
*/
static BlockNumber
! find_free_space(FSMRelation *fsmrel, Size spaceNeeded, bool forInsert)
{
FSMPageData *info;
int pagesToCheck, /* outer loop counter */
***************
*** 1225,1231 ****
Size spaceAvail = FSMPageGetSpace(page);
/* Check this page */
! if (spaceAvail >= spaceNeeded)
{
/*
* Found what we want --- adjust the entry, and update
--- 1228,1234 ----
Size spaceAvail = FSMPageGetSpace(page);
/* Check this page */
! if (spaceAvail >= (forInsert ? (spaceNeeded + 1024) :
spaceNeeded) )
{
/*
* Found what we want --- adjust the entry, and update
diff -rc postgresql-8.0.0.orig/src/include/access/hio.h
postgresql-8.0.0.pctfree/src/include/access/hio.h
*** postgresql-8.0.0.orig/src/include/access/hio.h 2005-01-01
07:03:21.000000000 +0900
--- postgresql-8.0.0.pctfree/src/include/access/hio.h 2005-08-20
23:13:20.267513544 +0900
***************
*** 19,24 ****
extern void RelationPutHeapTuple(Relation relation, Buffer buffer,
HeapTuple tuple);
extern Buffer RelationGetBufferForTuple(Relation relation, Size len,
! Buffer otherBuffer);
#endif /* HIO_H */
--- 19,24 ----
extern void RelationPutHeapTuple(Relation relation, Buffer buffer,
HeapTuple tuple);
extern Buffer RelationGetBufferForTuple(Relation relation, Size len,
! Buffer otherBuffer, bool
forInsert);
#endif /* HIO_H */
diff -rc postgresql-8.0.0.orig/src/include/storage/freespace.h
postgresql-8.0.0.pctfree/src/include/storage/freespace.h
*** postgresql-8.0.0.orig/src/include/storage/freespace.h 2005-01-01
07:03:42.000000000 +0900
--- postgresql-8.0.0.pctfree/src/include/storage/freespace.h 2005-08-20
23:18:46.661894056 +0900
***************
*** 39,49 ****
extern void InitFreeSpaceMap(void);
extern int FreeSpaceShmemSize(void);
! extern BlockNumber GetPageWithFreeSpace(RelFileNode *rel, Size spaceNeeded);
extern BlockNumber RecordAndGetPageWithFreeSpace(RelFileNode *rel,
BlockNumber oldPage,
Size oldSpaceAvail,
! Size spaceNeeded);
extern Size GetAvgFSMRequestSize(RelFileNode *rel);
extern void RecordRelationFreeSpace(RelFileNode *rel,
int nPages,
--- 39,52 ----
extern void InitFreeSpaceMap(void);
extern int FreeSpaceShmemSize(void);
! extern BlockNumber GetPageWithFreeSpace(RelFileNode *rel,
!
Size spaceNeeded,
!
bool forInsert);
extern BlockNumber RecordAndGetPageWithFreeSpace(RelFileNode *rel,
BlockNumber oldPage,
Size oldSpaceAvail,
! Size spaceNeeded,
!
bool forInsert);
extern Size GetAvgFSMRequestSize(RelFileNode *rel);
extern void RecordRelationFreeSpace(RelFileNode *rel,
int nPages,
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
