/ Check-in [97d88a86]
Login

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

Overview
Comment:Keep additional state information in the pager in an effort to reduce the number of SQLITE_FCNTL_SIZE_HINTs. (Note: This change was found to make no performance difference and so has been moved onto a side branch. Use the parent check-in instead of this one.)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | branch-3.6.1-exp
Files: files | file ages | folders
SHA1: 97d88a86e45e7a4269efd2fafdd4e6081d1475f8
User & Date: drh 2010-05-18 12:37:05
Original Comment: Keep additional state information in the pager in an effort to reduce the number of SQLITE_FCNTL_SIZE_HINTs.
Context
2010-05-18
12:37
Keep additional state information in the pager in an effort to reduce the number of SQLITE_FCNTL_SIZE_HINTs. (Note: This change was found to make no performance difference and so has been moved onto a side branch. Use the parent check-in instead of this one.) Closed-Leaf check-in: 97d88a86 user: drh tags: branch-3.6.1-exp
2010-05-17
15:52
An improvement to the SQLITE_FCNTL_SIZE_HINT change that invokes the hint less often and only when really needed. check-in: a1d20ceb user: drh tags: branch-3.6.1
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/pager.c.

354
355
356
357
358
359
360

361
362
363
364
365
366
367
....
1354
1355
1356
1357
1358
1359
1360

1361
1362
1363
1364
1365
1366
1367
....
1453
1454
1455
1456
1457
1458
1459

1460
1461
1462
1463
1464
1465
1466
....
3028
3029
3030
3031
3032
3033
3034


3035
3036
3037
3038
3039
3040
3041
....
3057
3058
3059
3060
3061
3062
3063


3064
3065
3066
3067
3068
3069
3070
3071
3072
3073
3074
3075



3076
3077
3078

3079
3080
3081
3082
3083
3084
3085
....
4021
4022
4023
4024
4025
4026
4027
4028
4029
4030
4031
4032
4033
4034
4035
....
4079
4080
4081
4082
4083
4084
4085
4086
4087
4088
4089
4090
4091
4092
4093
....
4108
4109
4110
4111
4112
4113
4114

4115
4116
4117
4118
4119
4120
4121
4122
4123
4124
4125
4126
4127
4128
4129
4130
....
4911
4912
4913
4914
4915
4916
4917
4918
4919
4920
4921
4922
4923
4924
4925
  u8 journalMode;             /* On of the PAGER_JOURNALMODE_* values */
  u8 dbModified;              /* True if there are any changes to the Db */
  u8 changeCountDone;         /* Set after incrementing the change-counter */
  u32 vfsFlags;               /* Flags for sqlite3_vfs.xOpen() */
  int errCode;                /* One of several kinds of errors */
  int dbSize;                 /* Number of pages in the file */
  int origDbSize;             /* dbSize before the current change */

  int stmtSize;               /* Size of database (in pages) at stmt_begin() */
  int nRec;                   /* Number of pages written to the journal */
  u32 cksumInit;              /* Quasi-random value added to every checksum */
  int stmtNRec;               /* Number of records in stmt subjournal */
  int nExtra;                 /* Add this many bytes to each in-memory page */
  int pageSize;               /* Number of bytes in a page */
  int nPage;                  /* Total number of in-memory pages */
................................................................................
        }
        pPager->stmtOpen = 0;
        pPager->stmtInUse = 0;
        pPager->journalOff = 0;
        pPager->journalStarted = 0;
        pPager->stmtAutoopen = 0;
        pPager->origDbSize = 0;

      }
    }

    if( !MEMDB || pPager->errCode==SQLITE_OK ){
      pPager->state = PAGER_UNLOCK;
      pPager->changeCountDone = 0;
    }
................................................................................
  if( !pPager->exclusiveMode ){
    rc2 = osUnlock(pPager->fd, SHARED_LOCK);
    pPager->state = PAGER_SHARED;
  }else if( pPager->state==PAGER_SYNCED ){
    pPager->state = PAGER_EXCLUSIVE;
  }
  pPager->origDbSize = 0;

  pPager->setMaster = 0;
  pPager->needSync = 0;
  lruListSetFirstSynced(pPager);
  pPager->dbSize = -1;
  pPager->dbModified = 0;

  return (rc==SQLITE_OK?rc2:rc);
................................................................................
** every one of those pages out to the database file and mark them all
** as clean.
*/
static int pager_write_pagelist(PgHdr *pList){
  Pager *pPager;
  PgHdr *p;
  int rc;



  if( pList==0 ) return SQLITE_OK;
  pPager = pList->pPager;

  /* At this point there may be either a RESERVED or EXCLUSIVE lock on the
  ** database file. If there is already an EXCLUSIVE lock, the following
  ** calls to sqlite3OsLock() are no-ops.
................................................................................
    return rc;
  }

  pList = sort_pagelist(pList);
  for(p=pList; p; p=p->pDirty){
    assert( p->dirty );
    p->dirty = 0;


  }

  /* If the file has not yet been opened, open it now. */
  if( !pPager->fd->pMethods ){
    assert(pPager->tempFile);
    rc = sqlite3PagerOpentemp(pPager, pPager->fd, pPager->vfsFlags);
    if( rc ) return rc;
  }

  /* Before the first write, give the VFS a hint of what the final
  ** file size will be.
  */



  if( pPager->dbSize > (pPager->origDbSize+1) ){
    sqlite3_int64 szFile = pPager->pageSize * (sqlite3_int64)pPager->dbSize;
    sqlite3OsFileControl(pPager->fd, SQLITE_FCNTL_SIZE_HINT, &szFile);

  }

  while( pList ){
    /* If there are dirty pages in the page cache with page numbers greater
    ** than Pager.dbSize, this means sqlite3PagerTruncate() was called to
    ** make the file smaller (presumably by auto-vacuum code). Do not write
    ** any such pages to the file.
................................................................................
  pPager->needSync = 0;
  pPager->alwaysRollback = 0;
  pPager->nRec = 0;
  if( pPager->errCode ){
    rc = pPager->errCode;
    goto failed_to_open_journal;
  }
  pPager->origDbSize = pPager->dbSize;

  rc = writeJournalHdr(pPager);

  if( pPager->stmtAutoopen && rc==SQLITE_OK ){
    rc = sqlite3PagerStmtBegin(pPager);
  }
  if( rc!=SQLITE_OK && rc!=SQLITE_NOMEM && rc!=SQLITE_IOERR_NOMEM ){
................................................................................
  pagerEnter(pPager);
  assert( pPg->nRef>0 );
  assert( pPager->state!=PAGER_UNLOCK );
  if( pPager->state==PAGER_SHARED ){
    assert( pPager->pInJournal==0 );
    if( MEMDB ){
      pPager->state = PAGER_EXCLUSIVE;
      pPager->origDbSize = pPager->dbSize;
    }else{
      rc = sqlite3OsLock(pPager->fd, RESERVED_LOCK);
      if( rc==SQLITE_OK ){
        pPager->state = PAGER_RESERVED;
        if( exFlag ){
          rc = pager_wait_on_lock(pPager, EXCLUSIVE_LOCK);
        }
................................................................................
    ** time a (read or write) transaction was successfully concluded
    ** by this connection. Instead of deleting the journal file it was 
    ** kept open and either was truncated to 0 bytes or its header was
    ** overwritten with zeros.
    */
    assert( pPager->nRec==0 );
    assert( pPager->origDbSize==0 );

    assert( pPager->pInJournal==0 );
    sqlite3PagerPagecount(pPager, 0);
    pagerLeave(pPager);
    pPager->pInJournal = sqlite3BitvecCreate( pPager->dbSize );
    pagerEnter(pPager);
    if( !pPager->pInJournal ){
      rc = SQLITE_NOMEM;
    }else{
      pPager->origDbSize = pPager->dbSize;
      rc = writeJournalHdr(pPager);
    }
  }
  assert( !pPager->journalOpen || pPager->journalOff>0 || rc!=SQLITE_OK );
  pagerLeave(pPager);
  return rc;
}
................................................................................
      pHist->pPrevStmt = pHist->pNextStmt = 0;
      if( pPager->xReiniter ){
        pPager->xReiniter(p, pPager->pageSize);
      }
    }
    pPager->pDirty = 0;
    pPager->pStmt = 0;
    pPager->dbSize = pPager->origDbSize;
    pager_truncate_cache(pPager);
    pPager->stmtInUse = 0;
    pPager->state = PAGER_SHARED;
    return SQLITE_OK;
  }

  pagerEnter(pPager);







>







 







>







 







>







 







>
>







 







>
>












>
>
>
|
|

>







 







|







 







|







 







>








|







 







|







354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
....
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
....
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
....
3031
3032
3033
3034
3035
3036
3037
3038
3039
3040
3041
3042
3043
3044
3045
3046
....
3062
3063
3064
3065
3066
3067
3068
3069
3070
3071
3072
3073
3074
3075
3076
3077
3078
3079
3080
3081
3082
3083
3084
3085
3086
3087
3088
3089
3090
3091
3092
3093
3094
3095
3096
....
4032
4033
4034
4035
4036
4037
4038
4039
4040
4041
4042
4043
4044
4045
4046
....
4090
4091
4092
4093
4094
4095
4096
4097
4098
4099
4100
4101
4102
4103
4104
....
4119
4120
4121
4122
4123
4124
4125
4126
4127
4128
4129
4130
4131
4132
4133
4134
4135
4136
4137
4138
4139
4140
4141
4142
....
4923
4924
4925
4926
4927
4928
4929
4930
4931
4932
4933
4934
4935
4936
4937
  u8 journalMode;             /* On of the PAGER_JOURNALMODE_* values */
  u8 dbModified;              /* True if there are any changes to the Db */
  u8 changeCountDone;         /* Set after incrementing the change-counter */
  u32 vfsFlags;               /* Flags for sqlite3_vfs.xOpen() */
  int errCode;                /* One of several kinds of errors */
  int dbSize;                 /* Number of pages in the file */
  int origDbSize;             /* dbSize before the current change */
  int hintDbSize;             /* dbSize sent to SQLITE_FCNTL_SIZE_HINT */
  int stmtSize;               /* Size of database (in pages) at stmt_begin() */
  int nRec;                   /* Number of pages written to the journal */
  u32 cksumInit;              /* Quasi-random value added to every checksum */
  int stmtNRec;               /* Number of records in stmt subjournal */
  int nExtra;                 /* Add this many bytes to each in-memory page */
  int pageSize;               /* Number of bytes in a page */
  int nPage;                  /* Total number of in-memory pages */
................................................................................
        }
        pPager->stmtOpen = 0;
        pPager->stmtInUse = 0;
        pPager->journalOff = 0;
        pPager->journalStarted = 0;
        pPager->stmtAutoopen = 0;
        pPager->origDbSize = 0;
        pPager->hintDbSize = 0;
      }
    }

    if( !MEMDB || pPager->errCode==SQLITE_OK ){
      pPager->state = PAGER_UNLOCK;
      pPager->changeCountDone = 0;
    }
................................................................................
  if( !pPager->exclusiveMode ){
    rc2 = osUnlock(pPager->fd, SHARED_LOCK);
    pPager->state = PAGER_SHARED;
  }else if( pPager->state==PAGER_SYNCED ){
    pPager->state = PAGER_EXCLUSIVE;
  }
  pPager->origDbSize = 0;
  pPager->hintDbSize = 0;
  pPager->setMaster = 0;
  pPager->needSync = 0;
  lruListSetFirstSynced(pPager);
  pPager->dbSize = -1;
  pPager->dbModified = 0;

  return (rc==SQLITE_OK?rc2:rc);
................................................................................
** every one of those pages out to the database file and mark them all
** as clean.
*/
static int pager_write_pagelist(PgHdr *pList){
  Pager *pPager;
  PgHdr *p;
  int rc;
  Pgno mxPgno;           /* Maximum page number to be written */
  int nExtend = 0;       /* Number of pages on pList that extend the file */

  if( pList==0 ) return SQLITE_OK;
  pPager = pList->pPager;

  /* At this point there may be either a RESERVED or EXCLUSIVE lock on the
  ** database file. If there is already an EXCLUSIVE lock, the following
  ** calls to sqlite3OsLock() are no-ops.
................................................................................
    return rc;
  }

  pList = sort_pagelist(pList);
  for(p=pList; p; p=p->pDirty){
    assert( p->dirty );
    p->dirty = 0;
    mxPgno = p->pgno;
    if( p->pgno>pPager->hintDbSize ) nExtend++;
  }

  /* If the file has not yet been opened, open it now. */
  if( !pPager->fd->pMethods ){
    assert(pPager->tempFile);
    rc = sqlite3PagerOpentemp(pPager, pPager->fd, pPager->vfsFlags);
    if( rc ) return rc;
  }

  /* Before the first write, give the VFS a hint of what the final
  ** file size will be.
  */
  if( nExtend>1 || (nExtend==1 && pPager->dbSize>mxPgno) ){
    sqlite3_int64 szFile;
    assert( pPager->hintDbSize<mxPgno );
    assert( mxPgno<=pPager->dbSize );
    szFile = pPager->pageSize * (sqlite3_int64)pPager->dbSize;
    sqlite3OsFileControl(pPager->fd, SQLITE_FCNTL_SIZE_HINT, &szFile);
    pPager->hintDbSize = pPager->dbSize;
  }

  while( pList ){
    /* If there are dirty pages in the page cache with page numbers greater
    ** than Pager.dbSize, this means sqlite3PagerTruncate() was called to
    ** make the file smaller (presumably by auto-vacuum code). Do not write
    ** any such pages to the file.
................................................................................
  pPager->needSync = 0;
  pPager->alwaysRollback = 0;
  pPager->nRec = 0;
  if( pPager->errCode ){
    rc = pPager->errCode;
    goto failed_to_open_journal;
  }
  pPager->hintDbSize = pPager->origDbSize = pPager->dbSize;

  rc = writeJournalHdr(pPager);

  if( pPager->stmtAutoopen && rc==SQLITE_OK ){
    rc = sqlite3PagerStmtBegin(pPager);
  }
  if( rc!=SQLITE_OK && rc!=SQLITE_NOMEM && rc!=SQLITE_IOERR_NOMEM ){
................................................................................
  pagerEnter(pPager);
  assert( pPg->nRef>0 );
  assert( pPager->state!=PAGER_UNLOCK );
  if( pPager->state==PAGER_SHARED ){
    assert( pPager->pInJournal==0 );
    if( MEMDB ){
      pPager->state = PAGER_EXCLUSIVE;
      pPager->hintDbSize = pPager->origDbSize = pPager->dbSize;
    }else{
      rc = sqlite3OsLock(pPager->fd, RESERVED_LOCK);
      if( rc==SQLITE_OK ){
        pPager->state = PAGER_RESERVED;
        if( exFlag ){
          rc = pager_wait_on_lock(pPager, EXCLUSIVE_LOCK);
        }
................................................................................
    ** time a (read or write) transaction was successfully concluded
    ** by this connection. Instead of deleting the journal file it was 
    ** kept open and either was truncated to 0 bytes or its header was
    ** overwritten with zeros.
    */
    assert( pPager->nRec==0 );
    assert( pPager->origDbSize==0 );
    assert( pPager->hintDbSize==0 );
    assert( pPager->pInJournal==0 );
    sqlite3PagerPagecount(pPager, 0);
    pagerLeave(pPager);
    pPager->pInJournal = sqlite3BitvecCreate( pPager->dbSize );
    pagerEnter(pPager);
    if( !pPager->pInJournal ){
      rc = SQLITE_NOMEM;
    }else{
      pPager->hintDbSize = pPager->origDbSize = pPager->dbSize;
      rc = writeJournalHdr(pPager);
    }
  }
  assert( !pPager->journalOpen || pPager->journalOff>0 || rc!=SQLITE_OK );
  pagerLeave(pPager);
  return rc;
}
................................................................................
      pHist->pPrevStmt = pHist->pNextStmt = 0;
      if( pPager->xReiniter ){
        pPager->xReiniter(p, pPager->pageSize);
      }
    }
    pPager->pDirty = 0;
    pPager->pStmt = 0;
    pPager->dbSize = pPager->hintDbSize = pPager->origDbSize;
    pager_truncate_cache(pPager);
    pPager->stmtInUse = 0;
    pPager->state = PAGER_SHARED;
    return SQLITE_OK;
  }

  pagerEnter(pPager);

Changes to test/io.test.

397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
      INSERT INTO abc SELECT * FROM abc;
      INSERT INTO abc SELECT * FROM abc;
      INSERT INTO abc SELECT * FROM abc;
    }
    # File has grown - showing there was a cache-spill - but there 
    # have been no calls to fsync():
    list [file size test.db] [nSync]
  } {38912 0}
  do_test io-3.3 {
    # The COMMIT requires a single fsync() - to the database file.
    execsql { COMMIT }
    list [file size test.db] [nSync]
  } {39936 1}
}








|







397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
      INSERT INTO abc SELECT * FROM abc;
      INSERT INTO abc SELECT * FROM abc;
      INSERT INTO abc SELECT * FROM abc;
    }
    # File has grown - showing there was a cache-spill - but there 
    # have been no calls to fsync():
    list [file size test.db] [nSync]
  } {36864 0}
  do_test io-3.3 {
    # The COMMIT requires a single fsync() - to the database file.
    execsql { COMMIT }
    list [file size test.db] [nSync]
  } {39936 1}
}