A developer was asking me questions about auto_vacuum I/O characteristics,
because they were worried about "churn", where a page is moved to fill a
freelist gap, then soon enough a new page is allocated anyhow, so the move
wasn't really necessary. This made me wonder if auto_vacuum recognized
that in some cases SQLITE_FCNTL_CHUNK_SIZE would make the move pointless
(because it wouldn't even save any space).
Below is a patch which implements [PRAGMA auto_vacuum_slack_pages = N].
This setting allows client code to signal that auto_vacuum can leave pages
on the freelist until releasing them would allow a db size change. I think
this would reduce the number of auto_vacuum moves for many databases. The
FCNTL and PRAGMA could obviously be integrated into one setting, I haven't
done that in this patch. Also, the test in autoVacuumCommit() could
certainly be more cleanly combined with the existing code, I left it
distinct to make the logic easier to see. I'd be happy to adjust the code
to be cleaner and write tests, mostly I didn't want to invest time in that
without floating a prototype to see if it would be an interesting addition.
Thanks,
scott
--- Patch follows ---
Index: src/btree.c
==================================================================
--- src/btree.c
+++ src/btree.c
@@ -2810,10 +2810,50 @@
sqlite3BtreeLeave(p);
return rc;
#endif
}
+/*
+** Change the 'auto-vacuum-slack-pages' property of the database. If auto
vacuum
+** is enabled, this is the number of chunks of slack to allow before
+** automatically running an incremental vacuum.
+*/
+int sqlite3BtreeSetAutoVacuumSlackPages(Btree *p, int autoVacuumSlack){
+#ifdef SQLITE_OMIT_AUTOVACUUM
+ return SQLITE_READONLY;
+#else
+ BtShared *pBt = p->pBt;
+ int rc = SQLITE_OK;
+ u8 cc = (u8)autoVacuumSlack;
+ if( autoVacuumSlack>cc ){
+ cc = 0xFF;
+ }
+
+ sqlite3BtreeEnter(p);
+ pBt->autoVacuumSlack = cc;
+ sqlite3BtreeLeave(p);
+ return rc;
+#endif
+}
+
+/*
+** Return the value of the 'auto-vacuum-slack-pages' property.
+*/
+int sqlite3BtreeGetAutoVacuumSlackPages(Btree *p){
+#ifdef SQLITE_OMIT_AUTOVACUUM
+ return 0;
+#else
+ int rc = 0;
+ sqlite3BtreeEnter(p);
+ if( p->pBt->autoVacuum!=0 ){
+ rc = p->pBt->autoVacuumSlack;
+ }
+ sqlite3BtreeLeave(p);
+ return rc;
+#endif
+}
+
/*
** Get a reference to pPage1 of the database file. This will
** also acquire a readlock on that file.
**
@@ -3651,17 +3691,31 @@
** i.e. the database has been reorganized so that only the first *pnTrunc
** pages are in use.
*/
static int autoVacuumCommit(BtShared *pBt){
int rc = SQLITE_OK;
+ int bShouldVacuum = pBt->autoVacuum && !pBt->incrVacuum;
Pager *pPager = pBt->pPager;
VVA_ONLY( int nRef = sqlite3PagerRefcount(pPager); )
assert( sqlite3_mutex_held(pBt->mutex) );
invalidateAllOverflowCache(pBt);
assert(pBt->autoVacuum);
- if( !pBt->incrVacuum ){
+ if( bShouldVacuum && pBt->autoVacuumSlack ){
+ Pgno nOrig; /* Database size before freeing */
+ Pgno nFree; /* Number of pages on the freelist initially */
+
+ nOrig = btreePagecount(pBt);
+ nFree = get4byte(&pBt->pPage1->aData[36]);
+ bShouldVacuum =
+ (nOrig-nFree)/pBt->autoVacuumSlack < nOrig/pBt->autoVacuumSlack;
+ /* TODO: When integrating this test with the following code, contrive
to
+ ** trim to the integral chunk boundary, rather than trimming the
entire free
+ ** list.
+ */
+ }
+ if( bShouldVacuum ){
Pgno nFin; /* Number of pages in database after autovacuuming
*/
Pgno nFree; /* Number of pages on the freelist initially */
Pgno iFree; /* The next page to be freed */
Pgno nOrig; /* Database size before freeing */
Index: src/btree.h
==================================================================
--- src/btree.h
+++ src/btree.h
@@ -76,10 +76,12 @@
int sqlite3BtreeSecureDelete(Btree*,int);
int sqlite3BtreeGetOptimalReserve(Btree*);
int sqlite3BtreeGetReserveNoMutex(Btree *p);
int sqlite3BtreeSetAutoVacuum(Btree *, int);
int sqlite3BtreeGetAutoVacuum(Btree *);
+int sqlite3BtreeSetAutoVacuumSlackPages(Btree *, int);
+int sqlite3BtreeGetAutoVacuumSlackPages(Btree *);
int sqlite3BtreeBeginTrans(Btree*,int);
int sqlite3BtreeCommitPhaseOne(Btree*, const char *zMaster);
int sqlite3BtreeCommitPhaseTwo(Btree*, int);
int sqlite3BtreeCommit(Btree*);
int sqlite3BtreeRollback(Btree*,int,int);
Index: src/btreeInt.h
==================================================================
--- src/btreeInt.h
+++ src/btreeInt.h
@@ -410,10 +410,11 @@
BtCursor *pCursor; /* A list of all open cursors */
MemPage *pPage1; /* First page of the database */
u8 openFlags; /* Flags to sqlite3BtreeOpen() */
#ifndef SQLITE_OMIT_AUTOVACUUM
u8 autoVacuum; /* True if auto-vacuum is enabled */
+ u8 autoVacuumSlack; /* Optional pages of slack for auto-vacuum */
u8 incrVacuum; /* True if incr-vacuum is enabled */
u8 bDoTruncate; /* True to truncate db on commit */
#endif
u8 inTransaction; /* Transaction state */
u8 max1bytePayload; /* Maximum first byte of cell for a 1-byte payload
*/
Index: src/pragma.c
==================================================================
--- src/pragma.c
+++ src/pragma.c
@@ -734,10 +734,32 @@
sqlite3VdbeJumpHere(v, addr);
break;
}
#endif
+ /*
+ ** PRAGMA [schema.]auto_vacuum_slack_pages(N)
+ **
+ ** Control chunk size of auto-vacuum.
+ */
+#ifndef SQLITE_OMIT_AUTOVACUUM
+ case PragTyp_AUTO_VACUUM_SLACK_PAGES: {
+ Btree *pBt = pDb->pBt;
+ assert( sqlite3SchemaMutexHeld(db, iDb, 0) );
+ assert( pBt!=0 );
+ if( !zRight ){
+ returnSingleInt(v, sqlite3BtreeGetAutoVacuumSlackPages(pBt));
+ }else{
+ int nPages = 8;
+ if( sqlite3GetInt32(zRight, &nPages) ){
+ sqlite3BtreeSetAutoVacuumSlackPages(pBt, nPages);
+ }
+ }
+ break;
+ }
+#endif
+
#ifndef SQLITE_OMIT_PAGER_PRAGMAS
/*
** PRAGMA [schema.]cache_size
** PRAGMA [schema.]cache_size=N
**
Index: src/pragma.h
==================================================================
--- src/pragma.h
+++ src/pragma.h
@@ -46,10 +46,11 @@
#define PragTyp_HEXKEY 38
#define PragTyp_KEY 39
#define PragTyp_REKEY 40
#define PragTyp_LOCK_STATUS 41
#define PragTyp_PARSER_TRACE 42
+#define PragTyp_AUTO_VACUUM_SLACK_PAGES 43
/* Property flags associated with various pragma. */
#define PragFlg_NeedSchema 0x01 /* Force schema load before running */
#define PragFlg_NoColumns 0x02 /* OP_ResultRow called with zero columns */
#define PragFlg_NoColumns1 0x04 /* zero columns if RHS argument is present
*/
@@ -142,10 +143,16 @@
{/* zName: */ "auto_vacuum",
/* ePragTyp: */ PragTyp_AUTO_VACUUM,
/* ePragFlg: */
PragFlg_NeedSchema|PragFlg_Result0|PragFlg_SchemaReq|PragFlg_NoColumns1,
/* ColNames: */ 0, 0,
/* iArg: */ 0 },
+#endif
+#if !defined(SQLITE_OMIT_AUTOVACUUM)
+ { /* zName: */ "auto_vacuum_slack_pages",
+ /* ePragTyp: */ PragTyp_AUTO_VACUUM_SLACK_PAGES,
+ /* ePragFlag: */ 0,
+ /* iArg: */ 0 },
#endif
#if !defined(SQLITE_OMIT_FLAG_PRAGMAS)
#if !defined(SQLITE_OMIT_AUTOMATIC_INDEX)
{/* zName: */ "automatic_index",
/* ePragTyp: */ PragTyp_FLAG,
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
