/ Check-in [4b12922d]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:If a page is made eligible for recycling when more than the configured maximum number of pages are allocated, free it immediately instead of adding it to the LRU list. (CVS 5638)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 4b12922dcb4547bf3a7276d0542b2e1d12ad338d
User & Date: danielk1977 2008-08-29 09:10:03
Context
2008-08-29
12:00
In permutations.test, disable tests that depend on soft-heap-limit functionality when running the memsubsys2 variant. It disables the soft-heap-limit. (CVS 5639) check-in: 047c7bdb user: danielk1977 tags: trunk
09:10
If a page is made eligible for recycling when more than the configured maximum number of pages are allocated, free it immediately instead of adding it to the LRU list. (CVS 5638) check-in: 4b12922d user: danielk1977 tags: trunk
02:14
Avoid reevaluating WHERE and ORDER BY expressions that alias terms in the result set. Ticket #3343. Note that aliased GROUP BY expressions are still evaluated twice. (CVS 5637) check-in: ab0292ca user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/pcache.c.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
744
745
746
747
748
749
750





751

752
753
754
755
756
757
758
...
926
927
928
929
930
931
932











933
934
935
936
937
938
939
940
941
942
943
944

945
946
947
948
949
950
951
952
953
954
....
1189
1190
1191
1192
1193
1194
1195

1196
1197
1198
1199
1200
1201
1202
....
1218
1219
1220
1221
1222
1223
1224





















**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file implements that page cache.
**
** @(#) $Id: pcache.c,v 1.23 2008/08/28 17:46:19 drh Exp $
*/
#include "sqliteInt.h"

/*
** A complete page cache is an instance of this structure.
**
** A cache may only be deleted by its owner and while holding the
................................................................................
    if( p->pCache->xDestroy ){
      p->pCache->xDestroy(p);
    }
    pCache->nRef--;
    if( (p->flags&PGHDR_DIRTY)==0 ){
      pCache->nPinned--;
      pcacheEnterMutex();





      pcacheAddToLruList(p);

      pcacheExitMutex();
    }else{
      /* Move the page to the head of the caches dirty list. */
      pcacheRemoveFromList(&pCache->pDirty, p);
      pcacheAddToList(&pCache->pDirty, p);
    }
  }
................................................................................
        memset(p->pData, 0, pCache->szPage);
      }
    }
  }
  pcacheExitMutex();
}













/*
** Close a cache.
*/
void sqlite3PcacheClose(PCache *pCache){
  pcacheEnterMutex();

  /* Free all the pages used by this pager and remove them from the LRU list. */
  pcacheClear(pCache);
  if( pCache->bPurgeable ){
    pcache.nMaxPage -= pCache->nMax;
    pcache.nMinPage -= pCache->nMin;

  }
  sqlite3_free(pCache->apHash);

  pcacheExitMutex();
}

/*
** Preserve the content of the page.  It is assumed that the content
** has not been preserved already.
**
................................................................................
  if( mxPage<10 ){
    mxPage = 10;
  }
  if( pCache->bPurgeable ){
    pcacheEnterMutex();
    pcache.nMaxPage -= pCache->nMax;
    pcache.nMaxPage += mxPage;

    pcacheExitMutex();
  }
  pCache->nMax = mxPage;
}

#ifdef SQLITE_ENABLE_MEMORY_MANAGEMENT
/*
................................................................................
      pcachePageFree(p);
    }
    pcacheExitMutex();
  }
  return nFree;
}
#endif /* SQLITE_ENABLE_MEMORY_MANAGEMENT */




























|







 







>
>
>
>
>
|
>







 







>
>
>
>
>
>
>
>
>
>
>












>


<







 







>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
...
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964

965
966
967
968
969
970
971
....
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
....
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file implements that page cache.
**
** @(#) $Id: pcache.c,v 1.24 2008/08/29 09:10:03 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** A complete page cache is an instance of this structure.
**
** A cache may only be deleted by its owner and while holding the
................................................................................
    if( p->pCache->xDestroy ){
      p->pCache->xDestroy(p);
    }
    pCache->nRef--;
    if( (p->flags&PGHDR_DIRTY)==0 ){
      pCache->nPinned--;
      pcacheEnterMutex();
      if( pcache.nCurrentPage>pcache.nMaxPage ){
        pcacheRemoveFromList(&pCache->pClean, p);
        pcacheRemoveFromHash(p);
        pcachePageFree(p);
      }else{
        pcacheAddToLruList(p);
      }
      pcacheExitMutex();
    }else{
      /* Move the page to the head of the caches dirty list. */
      pcacheRemoveFromList(&pCache->pDirty, p);
      pcacheAddToList(&pCache->pDirty, p);
    }
  }
................................................................................
        memset(p->pData, 0, pCache->szPage);
      }
    }
  }
  pcacheExitMutex();
}

/*
** If there are currently more than pcache.nMaxPage pages allocated, try
** to recycle pages to reduce the number allocated to pcache.nMaxPage.
*/
static void pcacheEnforceMaxPage(){
  PgHdr *p;
  assert( sqlite3_mutex_held(pcache.mutex) );
  while( pcache.nCurrentPage>pcache.nMaxPage && (p = pcacheRecyclePage()) ){
    pcachePageFree(p);
  }
}

/*
** Close a cache.
*/
void sqlite3PcacheClose(PCache *pCache){
  pcacheEnterMutex();

  /* Free all the pages used by this pager and remove them from the LRU list. */
  pcacheClear(pCache);
  if( pCache->bPurgeable ){
    pcache.nMaxPage -= pCache->nMax;
    pcache.nMinPage -= pCache->nMin;
    pcacheEnforceMaxPage();
  }
  sqlite3_free(pCache->apHash);

  pcacheExitMutex();
}

/*
** Preserve the content of the page.  It is assumed that the content
** has not been preserved already.
**
................................................................................
  if( mxPage<10 ){
    mxPage = 10;
  }
  if( pCache->bPurgeable ){
    pcacheEnterMutex();
    pcache.nMaxPage -= pCache->nMax;
    pcache.nMaxPage += mxPage;
    pcacheEnforceMaxPage();
    pcacheExitMutex();
  }
  pCache->nMax = mxPage;
}

#ifdef SQLITE_ENABLE_MEMORY_MANAGEMENT
/*
................................................................................
      pcachePageFree(p);
    }
    pcacheExitMutex();
  }
  return nFree;
}
#endif /* SQLITE_ENABLE_MEMORY_MANAGEMENT */

#ifdef SQLITE_TEST
void sqlite3PcacheStats(
  int *pnCurrent,
  int *pnMax,
  int *pnMin,
  int *pnRecyclable
){
  PgHdr *p;
  int nRecyclable = 0;
  for(p=pcache.pLruHead; p; p=p->pNextLru){
    nRecyclable++;
  }

  *pnCurrent = pcache.nCurrentPage;
  *pnMax = pcache.nMaxPage;
  *pnMin = pcache.nMinPage;
  *pnRecyclable = nRecyclable;
}
#endif

Changes to src/pcache.h.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
156
157
158
159
160
161
162
163


164
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This header file defines the interface that the sqlite page cache
** subsystem. 
**
** @(#) $Id: pcache.h,v 1.8 2008/08/28 02:26:07 drh Exp $
*/

#ifndef _PCACHE_H_

typedef struct PgHdr PgHdr;
typedef struct PCache PCache;

................................................................................
** of the suggested cache-sizes.
*/
int sqlite3PcacheGetCachesize(PCache *);
void sqlite3PcacheSetCachesize(PCache *, int);

/* Try to return memory used by the pcache module to the main memory heap */
int sqlite3PcacheReleaseMemory(int);



#endif /* _PCACHE_H_ */







|







 








>
>

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
156
157
158
159
160
161
162
163
164
165
166
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This header file defines the interface that the sqlite page cache
** subsystem. 
**
** @(#) $Id: pcache.h,v 1.9 2008/08/29 09:10:03 danielk1977 Exp $
*/

#ifndef _PCACHE_H_

typedef struct PgHdr PgHdr;
typedef struct PCache PCache;

................................................................................
** of the suggested cache-sizes.
*/
int sqlite3PcacheGetCachesize(PCache *);
void sqlite3PcacheSetCachesize(PCache *, int);

/* Try to return memory used by the pcache module to the main memory heap */
int sqlite3PcacheReleaseMemory(int);

void sqlite3PcacheStats(int*,int*,int*,int*);

#endif /* _PCACHE_H_ */

Changes to src/test1.c.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
....
4497
4498
4499
4500
4501
4502
4503
































4504
4505
4506
4507
4508
4509
4510
....
4671
4672
4673
4674
4675
4676
4677

4678
4679
4680
4681
4682
4683
4684
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Code for testing all sorts of SQLite interfaces.  This code
** is not included in the SQLite library.  It is used for automated
** testing of the SQLite library.
**
** $Id: test1.c,v 1.320 2008/08/27 15:21:34 drh Exp $
*/
#include "sqliteInt.h"
#include "tcl.h"
#include <stdlib.h>
#include <string.h>

/*
................................................................................
  int objc,              /* Number of arguments */
  Tcl_Obj *CONST objv[]  /* Command arguments */
){
  sqlite3_test_control(SQLITE_TESTCTRL_PRNG_RESET);
  return TCL_OK;
}


































/*
** Register commands with the TCL interpreter.
*/
int Sqlitetest1_Init(Tcl_Interp *interp){
  extern int sqlite3_search_count;
  extern int sqlite3_interrupt_count;
................................................................................
#ifdef SQLITE_ENABLE_COLUMN_METADATA
     { "sqlite3_table_column_metadata", test_table_column_metadata, 0  },
#endif
#ifndef SQLITE_OMIT_INCRBLOB
     { "sqlite3_blob_read",  test_blob_read, 0  },
     { "sqlite3_blob_write", test_blob_write, 0  },
#endif

  };
  static int bitmask_size = sizeof(Bitmask)*8;
  int i;
  extern int sqlite3_sync_count, sqlite3_fullsync_count;
  extern int sqlite3_opentemp_count;
  extern int sqlite3_like_count;
  extern int sqlite3_xferopt_count;







|







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
....
4497
4498
4499
4500
4501
4502
4503
4504
4505
4506
4507
4508
4509
4510
4511
4512
4513
4514
4515
4516
4517
4518
4519
4520
4521
4522
4523
4524
4525
4526
4527
4528
4529
4530
4531
4532
4533
4534
4535
4536
4537
4538
4539
4540
4541
4542
....
4703
4704
4705
4706
4707
4708
4709
4710
4711
4712
4713
4714
4715
4716
4717
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Code for testing all sorts of SQLite interfaces.  This code
** is not included in the SQLite library.  It is used for automated
** testing of the SQLite library.
**
** $Id: test1.c,v 1.321 2008/08/29 09:10:03 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "tcl.h"
#include <stdlib.h>
#include <string.h>

/*
................................................................................
  int objc,              /* Number of arguments */
  Tcl_Obj *CONST objv[]  /* Command arguments */
){
  sqlite3_test_control(SQLITE_TESTCTRL_PRNG_RESET);
  return TCL_OK;
}

/*
** tclcmd:  pcache_stats
*/
static int test_pcache_stats(
  ClientData clientData, /* Pointer to sqlite3_enable_XXX function */
  Tcl_Interp *interp,    /* The TCL interpreter that invoked this command */
  int objc,              /* Number of arguments */
  Tcl_Obj *CONST objv[]  /* Command arguments */
){
  int nMin;
  int nMax;
  int nCurrent;
  int nRecyclable;
  Tcl_Obj *pRet;

  sqlite3PcacheStats(&nCurrent, &nMax, &nMin, &nRecyclable);

  pRet = Tcl_NewObj();
  Tcl_ListObjAppendElement(interp, pRet, Tcl_NewStringObj("current", -1));
  Tcl_ListObjAppendElement(interp, pRet, Tcl_NewIntObj(nCurrent));
  Tcl_ListObjAppendElement(interp, pRet, Tcl_NewStringObj("max", -1));
  Tcl_ListObjAppendElement(interp, pRet, Tcl_NewIntObj(nMax));
  Tcl_ListObjAppendElement(interp, pRet, Tcl_NewStringObj("min", -1));
  Tcl_ListObjAppendElement(interp, pRet, Tcl_NewIntObj(nMin));
  Tcl_ListObjAppendElement(interp, pRet, Tcl_NewStringObj("recyclable", -1));
  Tcl_ListObjAppendElement(interp, pRet, Tcl_NewIntObj(nRecyclable));

  Tcl_SetObjResult(interp, pRet);

  return TCL_OK;
}


/*
** Register commands with the TCL interpreter.
*/
int Sqlitetest1_Init(Tcl_Interp *interp){
  extern int sqlite3_search_count;
  extern int sqlite3_interrupt_count;
................................................................................
#ifdef SQLITE_ENABLE_COLUMN_METADATA
     { "sqlite3_table_column_metadata", test_table_column_metadata, 0  },
#endif
#ifndef SQLITE_OMIT_INCRBLOB
     { "sqlite3_blob_read",  test_blob_read, 0  },
     { "sqlite3_blob_write", test_blob_write, 0  },
#endif
     { "pcache_stats",       test_pcache_stats, 0  },
  };
  static int bitmask_size = sizeof(Bitmask)*8;
  int i;
  extern int sqlite3_sync_count, sqlite3_fullsync_count;
  extern int sqlite3_opentemp_count;
  extern int sqlite3_like_count;
  extern int sqlite3_xferopt_count;

Added test/pcache.test.

































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
# 2008 August 29
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# This file is focused on testing the pcache module.
#
# $Id: pcache.test,v 1.1 2008/08/29 09:10:03 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl


# The pcache module limits the number of pages available to purgeable
# caches to the sum of the 'cache_size' values for the set of open
# caches. This block of tests, pcache-1.*, test that the library behaves
# corrctly when it is forced to exceed this limit.
#
do_test pcache-1.1 {
  db close
  pcache_stats
} {current 0 max 0 min 0 recyclable 0}

do_test pcache-1.2 {
  sqlite3 db test.db
  execsql "PRAGMA cache_size=10"
  pcache_stats
} {current 1 max 10 min 10 recyclable 1}

do_test pcache-1.3 {
  execsql {
    BEGIN;
    CREATE TABLE t1(a, b, c);
    CREATE TABLE t2(a, b, c);
    CREATE TABLE t3(a, b, c);
    CREATE TABLE t4(a, b, c);
    CREATE TABLE t5(a, b, c);
  }
  pcache_stats
} {current 6 max 10 min 10 recyclable 0}

do_test pcache-1.4 {
  execsql {
    CREATE TABLE t6(a, b, c);
    CREATE TABLE t7(a, b, c);
    CREATE TABLE t8(a, b, c);
    CREATE TABLE t9(a, b, c);
  }
  pcache_stats
} {current 10 max 10 min 10 recyclable 0}

do_test pcache-1.5 {
  sqlite3 db2 test.db
  execsql "PRAGMA cache_size=10" db2
  pcache_stats
} {current 11 max 20 min 20 recyclable 1}

do_test pcache-1.6 {
  execsql {
    BEGIN;
    SELECT * FROM sqlite_master;
  } db2
  pcache_stats
} {current 11 max 20 min 20 recyclable 0}

# At this point connection db2 has a read lock on the database file and a 
# single pinned page in its cache. Connection [db] is holding 10 dirty 
# pages. It cannot recycle them because of the read lock held by db2.
#
do_test pcache-1.6 {
  execsql {
    CREATE INDEX i1 ON t1(a, b);
    CREATE INDEX i2 ON t2(a, b);
    CREATE INDEX i3 ON t3(a, b);
    CREATE INDEX i4 ON t4(a, b);
    CREATE INDEX i5 ON t5(a, b);
    CREATE INDEX i6 ON t6(a, b);
    CREATE INDEX i7 ON t7(a, b);
    CREATE INDEX i8 ON t8(a, b);
    CREATE INDEX i9 ON t9(a, b);
  } 
  pcache_stats
} {current 20 max 20 min 20 recyclable 0}

do_test pcache-1.7 {
  execsql {
    CREATE TABLE t10(a, b, c);
  } 
  pcache_stats
} {current 21 max 20 min 20 recyclable 0}

# Rolling back the transaction held by db2 at this point releases a pinned
# page. Because the number of allocated pages is greater than the 
# configured maximum, this page should be freed immediately instead of
# recycled.
#
do_test pcache-1.8 {
  execsql {ROLLBACK} db2
  pcache_stats
} {current 20 max 20 min 20 recyclable 0}

do_test pcache-1.9 {
  execsql COMMIT
  pcache_stats
} {current 20 max 20 min 20 recyclable 20}

do_test pcache-1.10 {
  db2 close
  pcache_stats
} {current 10 max 10 min 10 recyclable 10}

do_test pcache-1.11 {
  execsql { PRAGMA cache_size = 20 }
  pcache_stats
} {current 10 max 20 min 10 recyclable 10}

do_test pcache-1.12 {
  execsql { 
    SELECT * FROM t1 ORDER BY a; SELECT * FROM t1;
    SELECT * FROM t2 ORDER BY a; SELECT * FROM t2;
    SELECT * FROM t3 ORDER BY a; SELECT * FROM t3;
    SELECT * FROM t4 ORDER BY a; SELECT * FROM t4;
    SELECT * FROM t5 ORDER BY a; SELECT * FROM t5;
    SELECT * FROM t6 ORDER BY a; SELECT * FROM t6;
    SELECT * FROM t7 ORDER BY a; SELECT * FROM t7;
    SELECT * FROM t8 ORDER BY a; SELECT * FROM t8;
    SELECT * FROM t9 ORDER BY a; SELECT * FROM t9;
  }
  pcache_stats
} {current 19 max 20 min 10 recyclable 19}

do_test pcache-1.13 {
  execsql { PRAGMA cache_size = 15 }
  pcache_stats
} {current 15 max 15 min 10 recyclable 15}

finish_test