Index: src/pager.c
==================================================================
--- src/pager.c
+++ src/pager.c
@@ -697,11 +697,11 @@
i64 journalSizeLimit; /* Size limit for persistent journal files */
char *zFilename; /* Name of the database file */
char *zJournal; /* Name of the journal file */
int (*xBusyHandler)(void*); /* Function to call when busy */
void *pBusyHandlerArg; /* Context argument for xBusyHandler */
- int aStat[3]; /* Total cache hits, misses and writes */
+ int aStat[4]; /* Total cache hits, misses, writes, spills */
#ifdef SQLITE_TEST
int nRead; /* Database pages read */
#endif
void (*xReiniter)(DbPage*); /* Call this routine when reloading pages */
int (*xGet)(Pager*,Pgno,DbPage**,int); /* Routine to fetch a patch */
@@ -725,10 +725,11 @@
** or CACHE_WRITE to sqlite3_db_status().
*/
#define PAGER_STAT_HIT 0
#define PAGER_STAT_MISS 1
#define PAGER_STAT_WRITE 2
+#define PAGER_STAT_SPILL 3
/*
** The following global variables hold counters used for
** testing purposes only. These variables do not exist in
** a non-testing build. These variables are not thread-safe.
@@ -4616,10 +4617,11 @@
|| (pPg->flags & PGHDR_NEED_SYNC)!=0)
){
return SQLITE_OK;
}
+ pPager->aStat[PAGER_STAT_SPILL]++;
pPg->pDirty = 0;
if( pagerUseWal(pPager) ){
/* Write a single frame for this page to the log. */
rc = subjournalPageIfRequired(pPg);
if( rc==SQLITE_OK ){
@@ -6736,30 +6738,37 @@
return a;
}
#endif
/*
-** Parameter eStat must be either SQLITE_DBSTATUS_CACHE_HIT or
-** SQLITE_DBSTATUS_CACHE_MISS. Before returning, *pnVal is incremented by the
+** Parameter eStat must be one of SQLITE_DBSTATUS_CACHE_HIT, _MISS, _WRITE,
+** or _WRITE+1. The SQLITE_DBSTATUS_CACHE_WRITE+1 case is a translation
+** of SQLITE_DBSTATUS_CACHE_SPILL. The _SPILL case is not contiguous because
+** it was added later.
+**
+** Before returning, *pnVal is incremented by the
** current cache hit or miss count, according to the value of eStat. If the
** reset parameter is non-zero, the cache hit or miss count is zeroed before
** returning.
*/
void sqlite3PagerCacheStat(Pager *pPager, int eStat, int reset, int *pnVal){
assert( eStat==SQLITE_DBSTATUS_CACHE_HIT
|| eStat==SQLITE_DBSTATUS_CACHE_MISS
|| eStat==SQLITE_DBSTATUS_CACHE_WRITE
+ || eStat==SQLITE_DBSTATUS_CACHE_WRITE+1
);
assert( SQLITE_DBSTATUS_CACHE_HIT+1==SQLITE_DBSTATUS_CACHE_MISS );
assert( SQLITE_DBSTATUS_CACHE_HIT+2==SQLITE_DBSTATUS_CACHE_WRITE );
- assert( PAGER_STAT_HIT==0 && PAGER_STAT_MISS==1 && PAGER_STAT_WRITE==2 );
+ assert( PAGER_STAT_HIT==0 && PAGER_STAT_MISS==1
+ && PAGER_STAT_WRITE==2 && PAGER_STAT_SPILL==3 );
- *pnVal += pPager->aStat[eStat - SQLITE_DBSTATUS_CACHE_HIT];
+ eStat -= SQLITE_DBSTATUS_CACHE_HIT;
+ *pnVal += pPager->aStat[eStat];
if( reset ){
- pPager->aStat[eStat - SQLITE_DBSTATUS_CACHE_HIT] = 0;
+ pPager->aStat[eStat] = 0;
}
}
/*
** Return true if this is an in-memory or temp-file backed pager.
Index: src/shell.c.in
==================================================================
--- src/shell.c.in
+++ src/shell.c.in
@@ -2351,10 +2351,13 @@
raw_printf(pArg->out, "Page cache misses: %d\n", iCur);
iHiwtr = iCur = -1;
sqlite3_db_status(db, SQLITE_DBSTATUS_CACHE_WRITE, &iCur, &iHiwtr, 1);
raw_printf(pArg->out, "Page cache writes: %d\n", iCur);
iHiwtr = iCur = -1;
+ sqlite3_db_status(db, SQLITE_DBSTATUS_CACHE_SPILL, &iCur, &iHiwtr, 1);
+ raw_printf(pArg->out, "Page cache spills: %d\n", iCur);
+ iHiwtr = iCur = -1;
sqlite3_db_status(db, SQLITE_DBSTATUS_SCHEMA_USED, &iCur, &iHiwtr, bReset);
raw_printf(pArg->out, "Schema Heap Usage: %d bytes\n",
iCur);
iHiwtr = iCur = -1;
sqlite3_db_status(db, SQLITE_DBSTATUS_STMT_USED, &iCur, &iHiwtr, bReset);
Index: src/sqlite.h.in
==================================================================
--- src/sqlite.h.in
+++ src/sqlite.h.in
@@ -7227,10 +7227,19 @@
** transaction rollback or database recovery operations are not included.
** If an IO or other error occurs while writing a page to disk, the effect
** on subsequent SQLITE_DBSTATUS_CACHE_WRITE requests is undefined.)^ ^The
** highwater mark associated with SQLITE_DBSTATUS_CACHE_WRITE is always 0.
**
+**
+** [[SQLITE_DBSTATUS_CACHE_SPILL]] ^(SQLITE_DBSTATUS_CACHE_SPILL
+** This parameter returns the number of dirty cache entries that have
+** been written to disk in the middle of a transaction due to the page
+** cache overflowing. Transactions are more efficient if they are written
+** to disk all at once. When pages spill mid-transaction, that introduces
+** additional overhead. This parameter can be used help identify
+** inefficiencies that can be resolve by increasing the cache size.
+**
**
** [[SQLITE_DBSTATUS_DEFERRED_FKS]] ^(SQLITE_DBSTATUS_DEFERRED_FKS
** This parameter returns zero for the current value if and only if
** all foreign key constraints (deferred or immediate) have been
** resolved.)^ ^The highwater mark is always 0.
@@ -7247,11 +7256,12 @@
#define SQLITE_DBSTATUS_CACHE_HIT 7
#define SQLITE_DBSTATUS_CACHE_MISS 8
#define SQLITE_DBSTATUS_CACHE_WRITE 9
#define SQLITE_DBSTATUS_DEFERRED_FKS 10
#define SQLITE_DBSTATUS_CACHE_USED_SHARED 11
-#define SQLITE_DBSTATUS_MAX 11 /* Largest defined DBSTATUS */
+#define SQLITE_DBSTATUS_CACHE_SPILL 12
+#define SQLITE_DBSTATUS_MAX 12 /* Largest defined DBSTATUS */
/*
** CAPI3REF: Prepared Statement Status
** METHOD: sqlite3_stmt
Index: src/status.c
==================================================================
--- src/status.c
+++ src/status.c
@@ -335,10 +335,13 @@
/*
** Set *pCurrent to the total cache hits or misses encountered by all
** pagers the database handle is connected to. *pHighwater is always set
** to zero.
*/
+ case SQLITE_DBSTATUS_CACHE_SPILL:
+ op = SQLITE_DBSTATUS_CACHE_WRITE+1;
+ /* Fall through into the next case */
case SQLITE_DBSTATUS_CACHE_HIT:
case SQLITE_DBSTATUS_CACHE_MISS:
case SQLITE_DBSTATUS_CACHE_WRITE:{
int i;
int nRet = 0;
Index: src/test_malloc.c
==================================================================
--- src/test_malloc.c
+++ src/test_malloc.c
@@ -1381,10 +1381,11 @@
{ "CACHE_HIT", SQLITE_DBSTATUS_CACHE_HIT },
{ "CACHE_MISS", SQLITE_DBSTATUS_CACHE_MISS },
{ "CACHE_WRITE", SQLITE_DBSTATUS_CACHE_WRITE },
{ "DEFERRED_FKS", SQLITE_DBSTATUS_DEFERRED_FKS },
{ "CACHE_USED_SHARED", SQLITE_DBSTATUS_CACHE_USED_SHARED },
+ { "CACHE_SPILL", SQLITE_DBSTATUS_CACHE_SPILL },
};
Tcl_Obj *pResult;
if( objc!=4 ){
Tcl_WrongNumArgs(interp, 1, objv, "DB PARAMETER RESETFLAG");
return TCL_ERROR;
Index: test/dbstatus2.test
==================================================================
--- test/dbstatus2.test
+++ test/dbstatus2.test
@@ -34,10 +34,14 @@
}
proc db_write {db {reset 0}} {
sqlite3_db_status $db CACHE_WRITE $reset
}
+
+proc db_spill {db {reset 0}} {
+ sqlite3_db_status $db CACHE_SPILL $reset
+}
do_test 1.1 {
db close
sqlite3 db test.db
execsql { PRAGMA mmap_size = 0 }
@@ -96,7 +100,16 @@
execsql { INSERT INTO t1 VALUES(5, randomblob(600)) }
db_write db
} {0 4 0}
do_test 2.8 { db_write db 1 } {0 4 0}
do_test 2.9 { db_write db 0 } {0 0 0}
+
+do_test 3.0 { db_spill db 1 } {0 0 0}
+do_test 3.1 { db_spill db 0 } {0 0 0}
+do_execsql_test 3.2 {
+ PRAGMA journal_mode=DELETE;
+ PRAGMA cache_size=3;
+ UPDATE t1 SET b=randomblob(1000);
+} {delete}
+do_test 3.2 { db_spill db 0 } {0 8 0}
finish_test