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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users