/ Check-in [3cb6a879]
Login

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

Overview
Comment:Add SQLITE_DBSTATUS_CACHE_WRITE. Used to query a database connection for the cumulative number of database pages written.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | fts4-incr-merge
Files: files | file ages | folders
SHA1:3cb6a879f1220db03a66429d63330e27e8ca6e49
User & Date: dan 2012-03-24 19:44:56
Context
2012-03-26
10:36
Add an experimental integrity-check function to FTS. check-in: 40fc8804 user: dan tags: fts4-incr-merge
2012-03-24
19:44
Add SQLITE_DBSTATUS_CACHE_WRITE. Used to query a database connection for the cumulative number of database pages written. check-in: 3cb6a879 user: dan tags: fts4-incr-merge
17:29
Enable fts3 tables to use incremental merge by automatically creating the %_stat table when it is needed. check-in: cc051fc0 user: drh tags: fts4-incr-merge
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/pager.c.

   666    666     int pageSize;               /* Number of bytes in a page */
   667    667     Pgno mxPgno;                /* Maximum allowed size of the database */
   668    668     i64 journalSizeLimit;       /* Size limit for persistent journal files */
   669    669     char *zFilename;            /* Name of the database file */
   670    670     char *zJournal;             /* Name of the journal file */
   671    671     int (*xBusyHandler)(void*); /* Function to call when busy */
   672    672     void *pBusyHandlerArg;      /* Context argument for xBusyHandler */
   673         -  int nHit, nMiss;            /* Total cache hits and misses */
          673  +  int aStat[3];               /* Total cache hits, misses and writes */
   674    674   #ifdef SQLITE_TEST
   675         -  int nRead, nWrite;          /* Database pages read/written */
          675  +  int nRead;                  /* Database pages read */
   676    676   #endif
   677    677     void (*xReiniter)(DbPage*); /* Call this routine when reloading pages */
   678    678   #ifdef SQLITE_HAS_CODEC
   679    679     void *(*xCodec)(void*,void*,Pgno,int); /* Routine for en/decoding data */
   680    680     void (*xCodecSizeChng)(void*,int,int); /* Notify of page size changes */
   681    681     void (*xCodecFree)(void*);             /* Destructor for the codec */
   682    682     void *pCodec;               /* First argument to xCodec... methods */
................................................................................
   685    685     PCache *pPCache;            /* Pointer to page cache object */
   686    686   #ifndef SQLITE_OMIT_WAL
   687    687     Wal *pWal;                  /* Write-ahead log used by "journal_mode=wal" */
   688    688     char *zWal;                 /* File name for write-ahead log */
   689    689   #endif
   690    690   };
   691    691   
          692  +/*
          693  +** Indexes for use with Pager.aStat[]. The Pager.aStat[] array contains
          694  +** the values accessed by passing SQLITE_DBSTATUS_CACHE_HIT, CACHE_MISS 
          695  +** or CACHE_WRITE to sqlite3_db_status().
          696  +*/
          697  +#define PAGER_STAT_HIT   0
          698  +#define PAGER_STAT_MISS  1
          699  +#define PAGER_STAT_WRITE 2
          700  +
   692    701   /*
   693    702   ** The following global variables hold counters used for
   694    703   ** testing purposes only.  These variables do not exist in
   695    704   ** a non-testing build.  These variables are not thread-safe.
   696    705   */
   697    706   #ifdef SQLITE_TEST
   698    707   int sqlite3_pager_readdb_count = 0;    /* Number of full pages read from DB */
................................................................................
  2967   2976   static int pagerWalFrames(
  2968   2977     Pager *pPager,                  /* Pager object */
  2969   2978     PgHdr *pList,                   /* List of frames to log */
  2970   2979     Pgno nTruncate,                 /* Database size after this commit */
  2971   2980     int isCommit                    /* True if this is a commit */
  2972   2981   ){
  2973   2982     int rc;                         /* Return code */
         2983  +  int nList;                      /* Number of pages in pList */
  2974   2984   #if defined(SQLITE_DEBUG) || defined(SQLITE_CHECK_PAGES)
  2975   2985     PgHdr *p;                       /* For looping over pages */
  2976   2986   #endif
  2977   2987   
  2978   2988     assert( pPager->pWal );
  2979   2989     assert( pList );
  2980   2990   #ifdef SQLITE_DEBUG
  2981   2991     /* Verify that the page list is in accending order */
  2982   2992     for(p=pList; p && p->pDirty; p=p->pDirty){
  2983   2993       assert( p->pgno < p->pDirty->pgno );
  2984   2994     }
  2985   2995   #endif
  2986   2996   
         2997  +  assert( pList->pDirty==0 || isCommit );
  2987   2998     if( isCommit ){
  2988   2999       /* If a WAL transaction is being committed, there is no point in writing
  2989   3000       ** any pages with page numbers greater than nTruncate into the WAL file.
  2990   3001       ** They will never be read by any client. So remove them from the pDirty
  2991   3002       ** list here. */
  2992   3003       PgHdr *p;
  2993   3004       PgHdr **ppNext = &pList;
         3005  +    nList = 0;
  2994   3006       for(p=pList; (*ppNext = p); p=p->pDirty){
  2995         -      if( p->pgno<=nTruncate ) ppNext = &p->pDirty;
         3007  +      if( p->pgno<=nTruncate ){
         3008  +        ppNext = &p->pDirty;
         3009  +        nList++;
         3010  +      }
  2996   3011       }
  2997   3012       assert( pList );
         3013  +  }else{
         3014  +    nList = 1;
  2998   3015     }
         3016  +  pPager->aStat[PAGER_STAT_WRITE] += nList;
  2999   3017   
  3000   3018     if( pList->pgno==1 ) pager_write_changecounter(pList);
  3001   3019     rc = sqlite3WalFrames(pPager->pWal, 
  3002   3020         pPager->pageSize, pList, nTruncate, isCommit, pPager->walSyncFlags
  3003   3021     );
  3004   3022     if( rc==SQLITE_OK && pPager->pBackup ){
  3005   3023       PgHdr *p;
................................................................................
  4059   4077         */
  4060   4078         if( pgno==1 ){
  4061   4079           memcpy(&pPager->dbFileVers, &pData[24], sizeof(pPager->dbFileVers));
  4062   4080         }
  4063   4081         if( pgno>pPager->dbFileSize ){
  4064   4082           pPager->dbFileSize = pgno;
  4065   4083         }
         4084  +      pPager->aStat[PAGER_STAT_WRITE]++;
  4066   4085   
  4067   4086         /* Update any backup objects copying the contents of this pager. */
  4068   4087         sqlite3BackupUpdate(pPager->pBackup, pgno, (u8*)pList->pData);
  4069   4088   
  4070   4089         PAGERTRACE(("STORE %d page %d hash(%08x)\n",
  4071   4090                      PAGERID(pPager), pgno, pager_pagehash(pList)));
  4072   4091         IOTRACE(("PGOUT %p %d\n", pPager, pgno));
  4073   4092         PAGER_INCR(sqlite3_pager_writedb_count);
  4074         -      PAGER_INCR(pPager->nWrite);
  4075   4093       }else{
  4076   4094         PAGERTRACE(("NOSTORE %d page %d\n", PAGERID(pPager), pgno));
  4077   4095       }
  4078   4096       pager_set_pagehash(pList);
  4079   4097       pList = pList->pDirty;
  4080   4098     }
  4081   4099   
................................................................................
  5025   5043     assert( (*ppPage)->pgno==pgno );
  5026   5044     assert( (*ppPage)->pPager==pPager || (*ppPage)->pPager==0 );
  5027   5045   
  5028   5046     if( (*ppPage)->pPager && !noContent ){
  5029   5047       /* In this case the pcache already contains an initialized copy of
  5030   5048       ** the page. Return without further ado.  */
  5031   5049       assert( pgno<=PAGER_MAX_PGNO && pgno!=PAGER_MJ_PGNO(pPager) );
  5032         -    pPager->nHit++;
         5050  +    pPager->aStat[PAGER_STAT_HIT]++;
  5033   5051       return SQLITE_OK;
  5034   5052   
  5035   5053     }else{
  5036   5054       /* The pager cache has created a new page. Its content needs to 
  5037   5055       ** be initialized.  */
  5038   5056   
  5039   5057       pPg = *ppPage;
................................................................................
  5067   5085           testcase( rc==SQLITE_NOMEM );
  5068   5086           sqlite3EndBenignMalloc();
  5069   5087         }
  5070   5088         memset(pPg->pData, 0, pPager->pageSize);
  5071   5089         IOTRACE(("ZERO %p %d\n", pPager, pgno));
  5072   5090       }else{
  5073   5091         assert( pPg->pPager==pPager );
  5074         -      pPager->nMiss++;
         5092  +      pPager->aStat[PAGER_STAT_MISS]++;
  5075   5093         rc = readDbPage(pPg);
  5076   5094         if( rc!=SQLITE_OK ){
  5077   5095           goto pager_acquire_err;
  5078   5096         }
  5079   5097       }
  5080   5098       pager_set_pagehash(pPg);
  5081   5099     }
................................................................................
  5652   5670         /* If running in direct mode, write the contents of page 1 to the file. */
  5653   5671         if( DIRECT_MODE ){
  5654   5672           const void *zBuf;
  5655   5673           assert( pPager->dbFileSize>0 );
  5656   5674           CODEC2(pPager, pPgHdr->pData, 1, 6, rc=SQLITE_NOMEM, zBuf);
  5657   5675           if( rc==SQLITE_OK ){
  5658   5676             rc = sqlite3OsWrite(pPager->fd, zBuf, pPager->pageSize, 0);
         5677  +          pPager->aStat[PAGER_STAT_WRITE]++;
  5659   5678           }
  5660   5679           if( rc==SQLITE_OK ){
  5661   5680             pPager->changeCountDone = 1;
  5662   5681           }
  5663   5682         }else{
  5664   5683           pPager->changeCountDone = 1;
  5665   5684         }
................................................................................
  6095   6114     static int a[11];
  6096   6115     a[0] = sqlite3PcacheRefCount(pPager->pPCache);
  6097   6116     a[1] = sqlite3PcachePagecount(pPager->pPCache);
  6098   6117     a[2] = sqlite3PcacheGetCachesize(pPager->pPCache);
  6099   6118     a[3] = pPager->eState==PAGER_OPEN ? -1 : (int) pPager->dbSize;
  6100   6119     a[4] = pPager->eState;
  6101   6120     a[5] = pPager->errCode;
  6102         -  a[6] = pPager->nHit;
  6103         -  a[7] = pPager->nMiss;
         6121  +  a[6] = pPager->aStat[PAGER_STAT_HIT];
         6122  +  a[7] = pPager->aStat[PAGER_STAT_MISS];
  6104   6123     a[8] = 0;  /* Used to be pPager->nOvfl */
  6105   6124     a[9] = pPager->nRead;
  6106         -  a[10] = pPager->nWrite;
         6125  +  a[10] = pPager->aStat[PAGER_STAT_WRITE];
  6107   6126     return a;
  6108   6127   }
  6109   6128   #endif
  6110   6129   
  6111   6130   /*
  6112   6131   ** Parameter eStat must be either SQLITE_DBSTATUS_CACHE_HIT or
  6113   6132   ** SQLITE_DBSTATUS_CACHE_MISS. Before returning, *pnVal is incremented by the
  6114   6133   ** current cache hit or miss count, according to the value of eStat. If the 
  6115   6134   ** reset parameter is non-zero, the cache hit or miss count is zeroed before 
  6116   6135   ** returning.
  6117   6136   */
  6118   6137   void sqlite3PagerCacheStat(Pager *pPager, int eStat, int reset, int *pnVal){
  6119         -  int *piStat;
  6120   6138   
  6121   6139     assert( eStat==SQLITE_DBSTATUS_CACHE_HIT
  6122   6140          || eStat==SQLITE_DBSTATUS_CACHE_MISS
         6141  +       || eStat==SQLITE_DBSTATUS_CACHE_WRITE
  6123   6142     );
  6124         -  if( eStat==SQLITE_DBSTATUS_CACHE_HIT ){
  6125         -    piStat = &pPager->nHit;
  6126         -  }else{
  6127         -    piStat = &pPager->nMiss;
  6128         -  }
  6129   6143   
  6130         -  *pnVal += *piStat;
         6144  +  assert( SQLITE_DBSTATUS_CACHE_HIT+1==SQLITE_DBSTATUS_CACHE_MISS );
         6145  +  assert( SQLITE_DBSTATUS_CACHE_HIT+2==SQLITE_DBSTATUS_CACHE_WRITE );
         6146  +  assert( PAGER_STAT_HIT==0 && PAGER_STAT_MISS==1 && PAGER_STAT_WRITE==2 );
         6147  +
         6148  +  *pnVal += pPager->aStat[eStat - SQLITE_DBSTATUS_CACHE_HIT];
  6131   6149     if( reset ){
  6132         -    *piStat = 0;
         6150  +    pPager->aStat[eStat - SQLITE_DBSTATUS_CACHE_HIT] = 0;
  6133   6151     }
  6134   6152   }
  6135   6153   
  6136   6154   /*
  6137   6155   ** Return true if this is an in-memory pager.
  6138   6156   */
  6139   6157   int sqlite3PagerIsMemdb(Pager *pPager){

Changes to src/sqlite.h.in.

  5997   5997   ** </dd>
  5998   5998   **
  5999   5999   ** [[SQLITE_DBSTATUS_CACHE_MISS]] ^(<dt>SQLITE_DBSTATUS_CACHE_MISS</dt>
  6000   6000   ** <dd>This parameter returns the number of pager cache misses that have
  6001   6001   ** occurred.)^ ^The highwater mark associated with SQLITE_DBSTATUS_CACHE_MISS 
  6002   6002   ** is always 0.
  6003   6003   ** </dd>
         6004  +**
         6005  +** [[SQLITE_DBSTATUS_CACHE_WRITE]] ^(<dt>SQLITE_DBSTATUS_CACHE_WRITE</dt>
         6006  +** <dd>This parameter returns the number of dirty cache entries that have
         6007  +** been written to disk. Specifically, the number of pages written to the
         6008  +** wal file in wal mode databases, or the number of pages written to the
         6009  +** database file in rollback mode databases. Any pages written as part of
         6010  +** transaction rollback or database recovery operations are not included.
         6011  +** If an IO or other error occurs while writing a page to disk, the effect
         6012  +** on subsequent SQLITE_DBSTATUS_CACHE_WRITE requests is undefined). ^The
         6013  +** highwater mark associated with SQLITE_DBSTATUS_CACHE_WRITE is always 0.
         6014  +** </dd>
  6004   6015   ** </dl>
  6005   6016   */
  6006   6017   #define SQLITE_DBSTATUS_LOOKASIDE_USED       0
  6007   6018   #define SQLITE_DBSTATUS_CACHE_USED           1
  6008   6019   #define SQLITE_DBSTATUS_SCHEMA_USED          2
  6009   6020   #define SQLITE_DBSTATUS_STMT_USED            3
  6010   6021   #define SQLITE_DBSTATUS_LOOKASIDE_HIT        4
  6011   6022   #define SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE  5
  6012   6023   #define SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL  6
  6013   6024   #define SQLITE_DBSTATUS_CACHE_HIT            7
  6014   6025   #define SQLITE_DBSTATUS_CACHE_MISS           8
  6015         -#define SQLITE_DBSTATUS_MAX                  8   /* Largest defined DBSTATUS */
         6026  +#define SQLITE_DBSTATUS_CACHE_WRITE          9
         6027  +#define SQLITE_DBSTATUS_MAX                  9   /* Largest defined DBSTATUS */
  6016   6028   
  6017   6029   
  6018   6030   /*
  6019   6031   ** CAPI3REF: Prepared Statement Status
  6020   6032   **
  6021   6033   ** ^(Each prepared statement maintains various
  6022   6034   ** [SQLITE_STMTSTATUS counters] that measure the number

Changes to src/status.c.

   220    220   
   221    221       /*
   222    222       ** Set *pCurrent to the total cache hits or misses encountered by all
   223    223       ** pagers the database handle is connected to. *pHighwater is always set 
   224    224       ** to zero.
   225    225       */
   226    226       case SQLITE_DBSTATUS_CACHE_HIT:
   227         -    case SQLITE_DBSTATUS_CACHE_MISS: {
          227  +    case SQLITE_DBSTATUS_CACHE_MISS:
          228  +    case SQLITE_DBSTATUS_CACHE_WRITE:{
   228    229         int i;
   229    230         int nRet = 0;
   230    231         assert( SQLITE_DBSTATUS_CACHE_MISS==SQLITE_DBSTATUS_CACHE_HIT+1 );
          232  +      assert( SQLITE_DBSTATUS_CACHE_WRITE==SQLITE_DBSTATUS_CACHE_HIT+2 );
   231    233   
   232    234         for(i=0; i<db->nDb; i++){
   233    235           if( db->aDb[i].pBt ){
   234    236             Pager *pPager = sqlite3BtreePager(db->aDb[i].pBt);
   235    237             sqlite3PagerCacheStat(pPager, op, resetFlag, &nRet);
   236    238           }
   237    239         }

Changes to src/test_malloc.c.

  1319   1319       { "CACHE_USED",          SQLITE_DBSTATUS_CACHE_USED          },
  1320   1320       { "SCHEMA_USED",         SQLITE_DBSTATUS_SCHEMA_USED         },
  1321   1321       { "STMT_USED",           SQLITE_DBSTATUS_STMT_USED           },
  1322   1322       { "LOOKASIDE_HIT",       SQLITE_DBSTATUS_LOOKASIDE_HIT       },
  1323   1323       { "LOOKASIDE_MISS_SIZE", SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE },
  1324   1324       { "LOOKASIDE_MISS_FULL", SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL },
  1325   1325       { "CACHE_HIT",           SQLITE_DBSTATUS_CACHE_HIT           },
  1326         -    { "CACHE_MISS",          SQLITE_DBSTATUS_CACHE_MISS          }
         1326  +    { "CACHE_MISS",          SQLITE_DBSTATUS_CACHE_MISS          },
         1327  +    { "CACHE_WRITE",         SQLITE_DBSTATUS_CACHE_WRITE         }
  1327   1328     };
  1328   1329     Tcl_Obj *pResult;
  1329   1330     if( objc!=4 ){
  1330   1331       Tcl_WrongNumArgs(interp, 1, objv, "DB PARAMETER RESETFLAG");
  1331   1332       return TCL_ERROR;
  1332   1333     }
  1333   1334     if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;

Changes to test/dbstatus2.test.

     5      5   #
     6      6   #    May you do good and not evil.
     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   #
    12         -# Tests for the sqlite3_stmt_status() function
           12  +# Tests for the sqlite3_db_status() function
    13     13   #
    14     14   
    15     15   set testdir [file dirname $argv0]
    16     16   source $testdir/tester.tcl
    17     17   
    18     18   set ::testprefix dbstatus2
    19     19   
................................................................................
    28     28   }
    29     29   
    30     30   proc db_hit_miss {db {reset 0}} {
    31     31     set nHit  [sqlite3_db_status $db CACHE_HIT $reset]
    32     32     set nMiss [sqlite3_db_status $db CACHE_MISS $reset]
    33     33     list $nHit $nMiss
    34     34   }
           35  +
           36  +proc db_write {db {reset 0}} {
           37  +  sqlite3_db_status $db CACHE_WRITE $reset
           38  +}
    35     39   
    36     40   do_test 1.1 {
    37     41     db close
    38     42     sqlite3 db test.db
    39     43     expr {[file size test.db] / 1024}
    40     44   } 6
    41     45   
................................................................................
    68     72     set len [string length [read $fd]]
    69     73     close $fd
    70     74     set len
    71     75   } 600
    72     76   do_test 1.8 { sqlite3_db_status db CACHE_HIT  0 } {0 2 0}
    73     77   do_test 1.9 { sqlite3_db_status db CACHE_MISS 0 } {0 1 0}
    74     78   
           79  +do_test 2.1 { db_write db } {0 0 0}
           80  +do_test 2.2 { 
           81  +  execsql { INSERT INTO t1 VALUES(4, randomblob(600)) }
           82  +  db_write db
           83  +} {0 4 0}
           84  +do_test 2.3 { db_write db 1 } {0 4 0}
           85  +do_test 2.4 { db_write db 0 } {0 0 0}
           86  +do_test 2.5 { db_write db 1 } {0 0 0}
           87  +
           88  +do_test 2.6 { 
           89  +  execsql { PRAGMA journal_mode = WAL }
           90  +  db_write db 1
           91  +} {0 1 0}
           92  +do_test 2.7 { 
           93  +  execsql { INSERT INTO t1 VALUES(5, randomblob(600)) }
           94  +  db_write db
           95  +} {0 4 0}
           96  +do_test 2.8 { db_write db 1 } {0 4 0}
           97  +do_test 2.9 { db_write db 0 } {0 0 0}
    75     98    
    76     99   finish_test