/ Check-in [3faeb851]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add the SQLITE_DBSTATUS_CACHE_SPILL option to sqlite3_db_status()
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | dbstatus-cache-spill
Files: files | file ages | folders
SHA3-256: 3faeb851374471a6f94a6fab3a62c73d03330eae6fc72cd1a277b03ad12dcdd0
User & Date: drh 2018-03-14 14:53:50
Context
2018-03-14
15:25
Add the SQLITE_DBSTATUS_CACHE_SPILL option to sqlite3_db_status(). check-in: 48a06eb0 user: drh tags: trunk
14:53
Add the SQLITE_DBSTATUS_CACHE_SPILL option to sqlite3_db_status() Closed-Leaf check-in: 3faeb851 user: drh tags: dbstatus-cache-spill
08:27
Improve detection of out-of-range parameters in sqlite3_stmt_status() for SQLITE_ENABLE_API_ARMOR builds. check-in: 21ecbce1 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/pager.c.

   695    695     int pageSize;               /* Number of bytes in a page */
   696    696     Pgno mxPgno;                /* Maximum allowed size of the database */
   697    697     i64 journalSizeLimit;       /* Size limit for persistent journal files */
   698    698     char *zFilename;            /* Name of the database file */
   699    699     char *zJournal;             /* Name of the journal file */
   700    700     int (*xBusyHandler)(void*); /* Function to call when busy */
   701    701     void *pBusyHandlerArg;      /* Context argument for xBusyHandler */
   702         -  int aStat[3];               /* Total cache hits, misses and writes */
          702  +  int aStat[4];               /* Total cache hits, misses, writes, spills */
   703    703   #ifdef SQLITE_TEST
   704    704     int nRead;                  /* Database pages read */
   705    705   #endif
   706    706     void (*xReiniter)(DbPage*); /* Call this routine when reloading pages */
   707    707     int (*xGet)(Pager*,Pgno,DbPage**,int); /* Routine to fetch a patch */
   708    708   #ifdef SQLITE_HAS_CODEC
   709    709     void *(*xCodec)(void*,void*,Pgno,int); /* Routine for en/decoding data */
................................................................................
   723    723   ** Indexes for use with Pager.aStat[]. The Pager.aStat[] array contains
   724    724   ** the values accessed by passing SQLITE_DBSTATUS_CACHE_HIT, CACHE_MISS 
   725    725   ** or CACHE_WRITE to sqlite3_db_status().
   726    726   */
   727    727   #define PAGER_STAT_HIT   0
   728    728   #define PAGER_STAT_MISS  1
   729    729   #define PAGER_STAT_WRITE 2
          730  +#define PAGER_STAT_SPILL 3
   730    731   
   731    732   /*
   732    733   ** The following global variables hold counters used for
   733    734   ** testing purposes only.  These variables do not exist in
   734    735   ** a non-testing build.  These variables are not thread-safe.
   735    736   */
   736    737   #ifdef SQLITE_TEST
................................................................................
  4614   4615     if( pPager->doNotSpill
  4615   4616      && ((pPager->doNotSpill & (SPILLFLAG_ROLLBACK|SPILLFLAG_OFF))!=0
  4616   4617         || (pPg->flags & PGHDR_NEED_SYNC)!=0)
  4617   4618     ){
  4618   4619       return SQLITE_OK;
  4619   4620     }
  4620   4621   
         4622  +  pPager->aStat[PAGER_STAT_SPILL]++;
  4621   4623     pPg->pDirty = 0;
  4622   4624     if( pagerUseWal(pPager) ){
  4623   4625       /* Write a single frame for this page to the log. */
  4624   4626       rc = subjournalPageIfRequired(pPg); 
  4625   4627       if( rc==SQLITE_OK ){
  4626   4628         rc = pagerWalFrames(pPager, pPg, 0, 0);
  4627   4629       }
................................................................................
  6734   6736     a[9] = pPager->nRead;
  6735   6737     a[10] = pPager->aStat[PAGER_STAT_WRITE];
  6736   6738     return a;
  6737   6739   }
  6738   6740   #endif
  6739   6741   
  6740   6742   /*
  6741         -** Parameter eStat must be either SQLITE_DBSTATUS_CACHE_HIT or
  6742         -** SQLITE_DBSTATUS_CACHE_MISS. Before returning, *pnVal is incremented by the
         6743  +** Parameter eStat must be one of SQLITE_DBSTATUS_CACHE_HIT, _MISS, _WRITE,
         6744  +** or _WRITE+1.  The SQLITE_DBSTATUS_CACHE_WRITE+1 case is a translation
         6745  +** of SQLITE_DBSTATUS_CACHE_SPILL.  The _SPILL case is not contiguous because
         6746  +** it was added later.
         6747  +**
         6748  +** Before returning, *pnVal is incremented by the
  6743   6749   ** current cache hit or miss count, according to the value of eStat. If the 
  6744   6750   ** reset parameter is non-zero, the cache hit or miss count is zeroed before 
  6745   6751   ** returning.
  6746   6752   */
  6747   6753   void sqlite3PagerCacheStat(Pager *pPager, int eStat, int reset, int *pnVal){
  6748   6754   
  6749   6755     assert( eStat==SQLITE_DBSTATUS_CACHE_HIT
  6750   6756          || eStat==SQLITE_DBSTATUS_CACHE_MISS
  6751   6757          || eStat==SQLITE_DBSTATUS_CACHE_WRITE
         6758  +       || eStat==SQLITE_DBSTATUS_CACHE_WRITE+1
  6752   6759     );
  6753   6760   
  6754   6761     assert( SQLITE_DBSTATUS_CACHE_HIT+1==SQLITE_DBSTATUS_CACHE_MISS );
  6755   6762     assert( SQLITE_DBSTATUS_CACHE_HIT+2==SQLITE_DBSTATUS_CACHE_WRITE );
  6756         -  assert( PAGER_STAT_HIT==0 && PAGER_STAT_MISS==1 && PAGER_STAT_WRITE==2 );
         6763  +  assert( PAGER_STAT_HIT==0 && PAGER_STAT_MISS==1
         6764  +           && PAGER_STAT_WRITE==2 && PAGER_STAT_SPILL==3 );
  6757   6765   
  6758         -  *pnVal += pPager->aStat[eStat - SQLITE_DBSTATUS_CACHE_HIT];
         6766  +  eStat -= SQLITE_DBSTATUS_CACHE_HIT;
         6767  +  *pnVal += pPager->aStat[eStat];
  6759   6768     if( reset ){
  6760         -    pPager->aStat[eStat - SQLITE_DBSTATUS_CACHE_HIT] = 0;
         6769  +    pPager->aStat[eStat] = 0;
  6761   6770     }
  6762   6771   }
  6763   6772   
  6764   6773   /*
  6765   6774   ** Return true if this is an in-memory or temp-file backed pager.
  6766   6775   */
  6767   6776   int sqlite3PagerIsMemdb(Pager *pPager){

Changes to src/shell.c.in.

  2349   2349       iHiwtr = iCur = -1;
  2350   2350       sqlite3_db_status(db, SQLITE_DBSTATUS_CACHE_MISS, &iCur, &iHiwtr, 1);
  2351   2351       raw_printf(pArg->out, "Page cache misses:                   %d\n", iCur);
  2352   2352       iHiwtr = iCur = -1;
  2353   2353       sqlite3_db_status(db, SQLITE_DBSTATUS_CACHE_WRITE, &iCur, &iHiwtr, 1);
  2354   2354       raw_printf(pArg->out, "Page cache writes:                   %d\n", iCur);
  2355   2355       iHiwtr = iCur = -1;
         2356  +    sqlite3_db_status(db, SQLITE_DBSTATUS_CACHE_SPILL, &iCur, &iHiwtr, 1);
         2357  +    raw_printf(pArg->out, "Page cache spills:                   %d\n", iCur);
         2358  +    iHiwtr = iCur = -1;
  2356   2359       sqlite3_db_status(db, SQLITE_DBSTATUS_SCHEMA_USED, &iCur, &iHiwtr, bReset);
  2357   2360       raw_printf(pArg->out, "Schema Heap Usage:                   %d bytes\n",
  2358   2361               iCur);
  2359   2362       iHiwtr = iCur = -1;
  2360   2363       sqlite3_db_status(db, SQLITE_DBSTATUS_STMT_USED, &iCur, &iHiwtr, bReset);
  2361   2364       raw_printf(pArg->out, "Statement Heap/Lookaside Usage:      %d bytes\n",
  2362   2365               iCur);

Changes to src/sqlite.h.in.

  7225   7225   ** wal file in wal mode databases, or the number of pages written to the
  7226   7226   ** database file in rollback mode databases. Any pages written as part of
  7227   7227   ** transaction rollback or database recovery operations are not included.
  7228   7228   ** If an IO or other error occurs while writing a page to disk, the effect
  7229   7229   ** on subsequent SQLITE_DBSTATUS_CACHE_WRITE requests is undefined.)^ ^The
  7230   7230   ** highwater mark associated with SQLITE_DBSTATUS_CACHE_WRITE is always 0.
  7231   7231   ** </dd>
         7232  +**
         7233  +** [[SQLITE_DBSTATUS_CACHE_SPILL]] ^(<dt>SQLITE_DBSTATUS_CACHE_SPILL</dt>
         7234  +** <dd>This parameter returns the number of dirty cache entries that have
         7235  +** been written to disk in the middle of a transaction due to the page
         7236  +** cache overflowing. Transactions are more efficient if they are written
         7237  +** to disk all at once. When pages spill mid-transaction, that introduces
         7238  +** additional overhead. This parameter can be used help identify
         7239  +** inefficiencies that can be resolve by increasing the cache size.
         7240  +** </dd>
  7232   7241   **
  7233   7242   ** [[SQLITE_DBSTATUS_DEFERRED_FKS]] ^(<dt>SQLITE_DBSTATUS_DEFERRED_FKS</dt>
  7234   7243   ** <dd>This parameter returns zero for the current value if and only if
  7235   7244   ** all foreign key constraints (deferred or immediate) have been
  7236   7245   ** resolved.)^  ^The highwater mark is always 0.
  7237   7246   ** </dd>
  7238   7247   ** </dl>
................................................................................
  7245   7254   #define SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE  5
  7246   7255   #define SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL  6
  7247   7256   #define SQLITE_DBSTATUS_CACHE_HIT            7
  7248   7257   #define SQLITE_DBSTATUS_CACHE_MISS           8
  7249   7258   #define SQLITE_DBSTATUS_CACHE_WRITE          9
  7250   7259   #define SQLITE_DBSTATUS_DEFERRED_FKS        10
  7251   7260   #define SQLITE_DBSTATUS_CACHE_USED_SHARED   11
  7252         -#define SQLITE_DBSTATUS_MAX                 11   /* Largest defined DBSTATUS */
         7261  +#define SQLITE_DBSTATUS_CACHE_SPILL         12
         7262  +#define SQLITE_DBSTATUS_MAX                 12   /* Largest defined DBSTATUS */
  7253   7263   
  7254   7264   
  7255   7265   /*
  7256   7266   ** CAPI3REF: Prepared Statement Status
  7257   7267   ** METHOD: sqlite3_stmt
  7258   7268   **
  7259   7269   ** ^(Each prepared statement maintains various

Changes to src/status.c.

   333    333       }
   334    334   
   335    335       /*
   336    336       ** Set *pCurrent to the total cache hits or misses encountered by all
   337    337       ** pagers the database handle is connected to. *pHighwater is always set 
   338    338       ** to zero.
   339    339       */
          340  +    case SQLITE_DBSTATUS_CACHE_SPILL:
          341  +      op = SQLITE_DBSTATUS_CACHE_WRITE+1;
          342  +      /* Fall through into the next case */
   340    343       case SQLITE_DBSTATUS_CACHE_HIT:
   341    344       case SQLITE_DBSTATUS_CACHE_MISS:
   342    345       case SQLITE_DBSTATUS_CACHE_WRITE:{
   343    346         int i;
   344    347         int nRet = 0;
   345    348         assert( SQLITE_DBSTATUS_CACHE_MISS==SQLITE_DBSTATUS_CACHE_HIT+1 );
   346    349         assert( SQLITE_DBSTATUS_CACHE_WRITE==SQLITE_DBSTATUS_CACHE_HIT+2 );

Changes to src/test_malloc.c.

  1379   1379       { "LOOKASIDE_MISS_SIZE", SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE },
  1380   1380       { "LOOKASIDE_MISS_FULL", SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL },
  1381   1381       { "CACHE_HIT",           SQLITE_DBSTATUS_CACHE_HIT           },
  1382   1382       { "CACHE_MISS",          SQLITE_DBSTATUS_CACHE_MISS          },
  1383   1383       { "CACHE_WRITE",         SQLITE_DBSTATUS_CACHE_WRITE         },
  1384   1384       { "DEFERRED_FKS",        SQLITE_DBSTATUS_DEFERRED_FKS        },
  1385   1385       { "CACHE_USED_SHARED",   SQLITE_DBSTATUS_CACHE_USED_SHARED   },
         1386  +    { "CACHE_SPILL",         SQLITE_DBSTATUS_CACHE_SPILL         },
  1386   1387     };
  1387   1388     Tcl_Obj *pResult;
  1388   1389     if( objc!=4 ){
  1389   1390       Tcl_WrongNumArgs(interp, 1, objv, "DB PARAMETER RESETFLAG");
  1390   1391       return TCL_ERROR;
  1391   1392     }
  1392   1393     if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;

Changes to test/dbstatus2.test.

    32     32     set nMiss [sqlite3_db_status $db CACHE_MISS $reset]
    33     33     list $nHit $nMiss
    34     34   }
    35     35   
    36     36   proc db_write {db {reset 0}} {
    37     37     sqlite3_db_status $db CACHE_WRITE $reset
    38     38   }
           39  +
           40  +proc db_spill {db {reset 0}} {
           41  +  sqlite3_db_status $db CACHE_SPILL $reset
           42  +}
    39     43   
    40     44   do_test 1.1 {
    41     45     db close
    42     46     sqlite3 db test.db
    43     47     execsql { PRAGMA mmap_size = 0 }
    44     48     expr {[file size test.db] / 1024}
    45     49   } 6
................................................................................
    94     98   }
    95     99   do_test 2.7 { 
    96    100     execsql { INSERT INTO t1 VALUES(5, randomblob(600)) }
    97    101     db_write db
    98    102   } {0 4 0}
    99    103   do_test 2.8 { db_write db 1 } {0 4 0}
   100    104   do_test 2.9 { db_write db 0 } {0 0 0}
          105  +
          106  +do_test 3.0 { db_spill db 1 } {0 0 0}
          107  +do_test 3.1 { db_spill db 0 } {0 0 0}
          108  +do_execsql_test 3.2 {
          109  +  PRAGMA journal_mode=DELETE;
          110  +  PRAGMA cache_size=3;
          111  +  UPDATE t1 SET b=randomblob(1000);
          112  +} {delete}
          113  +do_test 3.2 { db_spill db 0 } {0 8 0}
   101    114    
   102    115   finish_test