Following patch implements a simple mechanism to keep a buffer pinned while we are bulk loading.
Performance gains measured as +20% gain for CREATE TABLE as SELECT, and 15-17% for COPY on very short rows. Measurable difference drops away and is not measurable at all at 1000/bytes per row. As a result, patch doesn't bother to implement buffer pinning for TOAST operations. Can I ask for some independent performance results please? -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
Index: src/backend/access/heap/heapam.c =================================================================== RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/backend/access/heap/heapam.c,v retrieving revision 1.249 diff -c -r1.249 heapam.c *** src/backend/access/heap/heapam.c 30 Jan 2008 18:35:55 -0000 1.249 --- src/backend/access/heap/heapam.c 26 Feb 2008 14:29:35 -0000 *************** *** 1732,1737 **** --- 1732,1752 ---- } } + /* + * Begin/End Bulk Inserts + * + */ + void + heap_begin_bulk_insert(void) + { + ReleaseBulkInsertBufferIfAny(); + } + + void + heap_end_bulk_insert(void) + { + ReleaseBulkInsertBufferIfAny(); + } /* * heap_insert - insert tuple into a heap *************** *** 1759,1769 **** */ Oid heap_insert(Relation relation, HeapTuple tup, CommandId cid, ! bool use_wal, bool use_fsm) { TransactionId xid = GetCurrentTransactionId(); HeapTuple heaptup; Buffer buffer; if (relation->rd_rel->relhasoids) { --- 1774,1785 ---- */ Oid heap_insert(Relation relation, HeapTuple tup, CommandId cid, ! bool use_wal, bool use_fsm, bool bulk_insert_request) { TransactionId xid = GetCurrentTransactionId(); HeapTuple heaptup; Buffer buffer; + bool bulk_insert = bulk_insert_request && !relation->rd_istemp; if (relation->rd_rel->relhasoids) { *************** *** 1816,1824 **** else heaptup = tup; ! /* Find buffer to insert this tuple into */ ! buffer = RelationGetBufferForTuple(relation, heaptup->t_len, ! InvalidBuffer, use_fsm); /* NO EREPORT(ERROR) from here till changes are logged */ START_CRIT_SECTION(); --- 1832,1849 ---- else heaptup = tup; ! /* ! * Find buffer to insert this tuple into ! */ ! if (bulk_insert) ! { ! buffer = RelationGetBufferForTuple(relation, heaptup->t_len, ! GetBulkInsertBuffer(), use_fsm, true); ! SetBulkInsertBuffer(buffer); ! } ! else ! buffer = RelationGetBufferForTuple(relation, heaptup->t_len, ! InvalidBuffer, use_fsm, false); /* NO EREPORT(ERROR) from here till changes are logged */ START_CRIT_SECTION(); *************** *** 1897,1903 **** END_CRIT_SECTION(); ! UnlockReleaseBuffer(buffer); /* * If tuple is cachable, mark it for invalidation from the caches in case --- 1922,1934 ---- END_CRIT_SECTION(); ! /* ! * Keep buffer pinned if we are in bulk insert mode ! */ ! if (bulk_insert) ! LockBuffer(buffer, BUFFER_LOCK_UNLOCK); ! else ! UnlockReleaseBuffer(buffer); /* * If tuple is cachable, mark it for invalidation from the caches in case *************** *** 1934,1940 **** Oid simple_heap_insert(Relation relation, HeapTuple tup) { ! return heap_insert(relation, tup, GetCurrentCommandId(true), true, true); } /* --- 1965,1971 ---- Oid simple_heap_insert(Relation relation, HeapTuple tup) { ! return heap_insert(relation, tup, GetCurrentCommandId(true), true, true, false); } /* *************** *** 2557,2563 **** { /* Assume there's no chance to put heaptup on same page. */ newbuf = RelationGetBufferForTuple(relation, heaptup->t_len, ! buffer, true); } else { --- 2588,2594 ---- { /* Assume there's no chance to put heaptup on same page. */ newbuf = RelationGetBufferForTuple(relation, heaptup->t_len, ! buffer, true, false); } else { *************** *** 2574,2580 **** */ LockBuffer(buffer, BUFFER_LOCK_UNLOCK); newbuf = RelationGetBufferForTuple(relation, heaptup->t_len, ! buffer, true); } else { --- 2605,2611 ---- */ LockBuffer(buffer, BUFFER_LOCK_UNLOCK); newbuf = RelationGetBufferForTuple(relation, heaptup->t_len, ! buffer, true, false); } else { Index: src/backend/access/heap/hio.c =================================================================== RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/backend/access/heap/hio.c,v retrieving revision 1.68 diff -c -r1.68 hio.c *** src/backend/access/heap/hio.c 1 Jan 2008 19:45:46 -0000 1.68 --- src/backend/access/heap/hio.c 26 Feb 2008 14:29:35 -0000 *************** *** 103,109 **** */ Buffer RelationGetBufferForTuple(Relation relation, Size len, ! Buffer otherBuffer, bool use_fsm) { Buffer buffer = InvalidBuffer; Page pageHeader; --- 103,109 ---- */ Buffer RelationGetBufferForTuple(Relation relation, Size len, ! Buffer otherBuffer, bool use_fsm, bool bulk_insert) { Buffer buffer = InvalidBuffer; Page pageHeader; *************** *** 198,216 **** buffer = otherBuffer; LockBuffer(buffer, BUFFER_LOCK_EXCLUSIVE); } ! else if (otherBlock < targetBlock) { ! /* lock other buffer first */ buffer = ReadBuffer(relation, targetBlock); - LockBuffer(otherBuffer, BUFFER_LOCK_EXCLUSIVE); LockBuffer(buffer, BUFFER_LOCK_EXCLUSIVE); } ! else { ! /* lock target buffer first */ ! buffer = ReadBuffer(relation, targetBlock); ! LockBuffer(buffer, BUFFER_LOCK_EXCLUSIVE); ! LockBuffer(otherBuffer, BUFFER_LOCK_EXCLUSIVE); } /* --- 198,225 ---- buffer = otherBuffer; LockBuffer(buffer, BUFFER_LOCK_EXCLUSIVE); } ! else if (bulk_insert) { ! ReleaseBuffer(otherBuffer); buffer = ReadBuffer(relation, targetBlock); LockBuffer(buffer, BUFFER_LOCK_EXCLUSIVE); } ! else if (!bulk_insert) { ! if (otherBlock < targetBlock) ! { ! /* lock other buffer first */ ! buffer = ReadBuffer(relation, targetBlock); ! LockBuffer(otherBuffer, BUFFER_LOCK_EXCLUSIVE); ! LockBuffer(buffer, BUFFER_LOCK_EXCLUSIVE); ! } ! else ! { ! /* lock target buffer first */ ! buffer = ReadBuffer(relation, targetBlock); ! LockBuffer(buffer, BUFFER_LOCK_EXCLUSIVE); ! LockBuffer(otherBuffer, BUFFER_LOCK_EXCLUSIVE); ! } } /* *************** *** 265,270 **** --- 274,282 ---- */ needLock = !RELATION_IS_LOCAL(relation); + if (bulk_insert && otherBuffer != InvalidBuffer) + ReleaseBuffer(otherBuffer); + if (needLock) LockRelationForExtension(relation, ExclusiveLock); *************** *** 280,286 **** * We can be certain that locking the otherBuffer first is OK, since it * must have a lower page number. */ ! if (otherBuffer != InvalidBuffer) LockBuffer(otherBuffer, BUFFER_LOCK_EXCLUSIVE); /* --- 292,298 ---- * We can be certain that locking the otherBuffer first is OK, since it * must have a lower page number. */ ! if (!bulk_insert && otherBuffer != InvalidBuffer) LockBuffer(otherBuffer, BUFFER_LOCK_EXCLUSIVE); /* Index: src/backend/access/heap/tuptoaster.c =================================================================== RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/backend/access/heap/tuptoaster.c,v retrieving revision 1.82 diff -c -r1.82 tuptoaster.c *** src/backend/access/heap/tuptoaster.c 23 Feb 2008 19:11:45 -0000 1.82 --- src/backend/access/heap/tuptoaster.c 26 Feb 2008 14:29:35 -0000 *************** *** 1174,1180 **** if (!HeapTupleIsValid(toasttup)) elog(ERROR, "failed to build TOAST tuple"); ! heap_insert(toastrel, toasttup, mycid, use_wal, use_fsm); /* * Create the index entry. We cheat a little here by not using --- 1174,1180 ---- if (!HeapTupleIsValid(toasttup)) elog(ERROR, "failed to build TOAST tuple"); ! heap_insert(toastrel, toasttup, mycid, use_wal, use_fsm, false); /* * Create the index entry. We cheat a little here by not using Index: src/backend/access/transam/xact.c =================================================================== RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/backend/access/transam/xact.c,v retrieving revision 1.257 diff -c -r1.257 xact.c *** src/backend/access/transam/xact.c 15 Jan 2008 18:56:59 -0000 1.257 --- src/backend/access/transam/xact.c 26 Feb 2008 14:29:35 -0000 *************** *** 2010,2015 **** --- 2010,2016 ---- RESOURCE_RELEASE_BEFORE_LOCKS, false, true); AtEOXact_Buffers(false); + AtEOXact_BulkInsert(); AtEOXact_RelationCache(false); AtEOXact_Inval(false); smgrDoPendingDeletes(false); Index: src/backend/commands/copy.c =================================================================== RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/backend/commands/copy.c,v retrieving revision 1.295 diff -c -r1.295 copy.c *** src/backend/commands/copy.c 1 Jan 2008 19:45:48 -0000 1.295 --- src/backend/commands/copy.c 26 Feb 2008 14:29:35 -0000 *************** *** 1656,1661 **** --- 1656,1662 ---- CommandId mycid = GetCurrentCommandId(true); bool use_wal = true; /* by default, use WAL logging */ bool use_fsm = true; /* by default, use FSM for free space */ + bool bulk_insert = true; /* by default, use bulk inserts */ Assert(cstate->rel); *************** *** 1900,1905 **** --- 1901,1909 ---- done = CopyReadLine(cstate); } + if (bulk_insert) + heap_begin_bulk_insert(); + while (!done) { bool skip_tuple; *************** *** 2112,2118 **** ExecConstraints(resultRelInfo, slot, estate); /* OK, store the tuple and create index entries for it */ ! heap_insert(cstate->rel, tuple, mycid, use_wal, use_fsm); if (resultRelInfo->ri_NumIndices > 0) ExecInsertIndexTuples(slot, &(tuple->t_self), estate, false); --- 2116,2122 ---- ExecConstraints(resultRelInfo, slot, estate); /* OK, store the tuple and create index entries for it */ ! heap_insert(cstate->rel, tuple, mycid, use_wal, use_fsm, bulk_insert); if (resultRelInfo->ri_NumIndices > 0) ExecInsertIndexTuples(slot, &(tuple->t_self), estate, false); *************** *** 2129,2134 **** --- 2133,2141 ---- } } + if (bulk_insert) + heap_end_bulk_insert(); + /* Done, clean up */ error_context_stack = errcontext.previous; Index: src/backend/executor/execMain.c =================================================================== RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/backend/executor/execMain.c,v retrieving revision 1.303 diff -c -r1.303 execMain.c *** src/backend/executor/execMain.c 7 Feb 2008 17:09:51 -0000 1.303 --- src/backend/executor/execMain.c 26 Feb 2008 14:29:35 -0000 *************** *** 1527,1533 **** */ newId = heap_insert(resultRelationDesc, tuple, estate->es_output_cid, ! true, true); IncrAppended(); (estate->es_processed)++; --- 1527,1533 ---- */ newId = heap_insert(resultRelationDesc, tuple, estate->es_output_cid, ! true, true, false); IncrAppended(); (estate->es_processed)++; *************** *** 2720,2726 **** static void intorel_startup(DestReceiver *self, int operation, TupleDesc typeinfo) { ! /* no-op */ } /* --- 2720,2726 ---- static void intorel_startup(DestReceiver *self, int operation, TupleDesc typeinfo) { ! heap_begin_bulk_insert(); } /* *************** *** 2739,2745 **** tuple, estate->es_output_cid, estate->es_into_relation_use_wal, ! false); /* never any point in using FSM */ /* We know this is a newly created relation, so there are no indexes */ --- 2739,2746 ---- tuple, estate->es_output_cid, estate->es_into_relation_use_wal, ! false, /* never any point in using FSM */ ! true); /* always run a bulk insert */ /* We know this is a newly created relation, so there are no indexes */ *************** *** 2754,2760 **** static void intorel_shutdown(DestReceiver *self) { ! /* no-op */ } /* --- 2755,2761 ---- static void intorel_shutdown(DestReceiver *self) { ! heap_end_bulk_insert(); } /* Index: src/backend/storage/buffer/bufmgr.c =================================================================== RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/backend/storage/buffer/bufmgr.c,v retrieving revision 1.228 diff -c -r1.228 bufmgr.c *** src/backend/storage/buffer/bufmgr.c 1 Jan 2008 19:45:51 -0000 1.228 --- src/backend/storage/buffer/bufmgr.c 26 Feb 2008 14:29:35 -0000 *************** *** 75,80 **** --- 75,82 ---- /* local state for LockBufferForCleanup */ static volatile BufferDesc *PinCountWaitBuf = NULL; + /* local state for bulk inserts */ + static Buffer BulkInsertBuffer = InvalidBuffer; static Buffer ReadBuffer_common(Relation reln, BlockNumber blockNum, bool zeroPage, *************** *** 2087,2092 **** --- 2089,2124 ---- } /* + * BulkInsertBuffer manipulation + */ + Buffer + GetBulkInsertBuffer(void) + { + return BulkInsertBuffer; + } + + void + SetBulkInsertBuffer(Buffer buffer) + { + BulkInsertBuffer = buffer; + } + + void + ReleaseBulkInsertBufferIfAny(void) + { + if (BufferIsValid(BulkInsertBuffer)) + ReleaseBuffer(BulkInsertBuffer); + + BulkInsertBuffer = InvalidBuffer; + } + + void + AtEOXact_BulkInsert(void) + { + BulkInsertBuffer = InvalidBuffer; + } + + /* * IncrBufferRefCount * Increment the pin count on a buffer that we have *already* pinned * at least once. Index: src/include/access/heapam.h =================================================================== RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/include/access/heapam.h,v retrieving revision 1.130 diff -c -r1.130 heapam.h *** src/include/access/heapam.h 14 Jan 2008 01:39:09 -0000 1.130 --- src/include/access/heapam.h 26 Feb 2008 14:29:35 -0000 *************** *** 165,172 **** ItemPointer tid); extern void setLastTid(const ItemPointer tid); extern Oid heap_insert(Relation relation, HeapTuple tup, CommandId cid, ! bool use_wal, bool use_fsm); extern HTSU_Result heap_delete(Relation relation, ItemPointer tid, ItemPointer ctid, TransactionId *update_xmax, CommandId cid, Snapshot crosscheck, bool wait); --- 165,174 ---- ItemPointer tid); extern void setLastTid(const ItemPointer tid); + void heap_begin_bulk_insert(void); + void heap_end_bulk_insert(void); extern Oid heap_insert(Relation relation, HeapTuple tup, CommandId cid, ! bool use_wal, bool use_fsm, bool bulk_insert); extern HTSU_Result heap_delete(Relation relation, ItemPointer tid, ItemPointer ctid, TransactionId *update_xmax, CommandId cid, Snapshot crosscheck, bool wait); Index: src/include/access/hio.h =================================================================== RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/include/access/hio.h,v retrieving revision 1.35 diff -c -r1.35 hio.h *** src/include/access/hio.h 1 Jan 2008 19:45:56 -0000 1.35 --- src/include/access/hio.h 26 Feb 2008 14:29:35 -0000 *************** *** 21,26 **** extern void RelationPutHeapTuple(Relation relation, Buffer buffer, HeapTuple tuple); extern Buffer RelationGetBufferForTuple(Relation relation, Size len, ! Buffer otherBuffer, bool use_fsm); #endif /* HIO_H */ --- 21,26 ---- extern void RelationPutHeapTuple(Relation relation, Buffer buffer, HeapTuple tuple); extern Buffer RelationGetBufferForTuple(Relation relation, Size len, ! Buffer otherBuffer, bool use_fsm, bool bulk_insert); #endif /* HIO_H */ Index: src/include/storage/bufmgr.h =================================================================== RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/include/storage/bufmgr.h,v retrieving revision 1.111 diff -c -r1.111 bufmgr.h *** src/include/storage/bufmgr.h 1 Jan 2008 19:45:58 -0000 1.111 --- src/include/storage/bufmgr.h 26 Feb 2008 14:29:35 -0000 *************** *** 166,171 **** --- 166,176 ---- extern void AtProcExit_LocalBuffers(void); + extern Buffer GetBulkInsertBuffer(void); + extern void SetBulkInsertBuffer(Buffer buffer); + extern void ReleaseBulkInsertBufferIfAny(void); + extern void AtEOXact_BulkInsert(void); + /* in freelist.c */ extern BufferAccessStrategy GetAccessStrategy(BufferAccessStrategyType btype); extern void FreeAccessStrategy(BufferAccessStrategy strategy);
---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings