/ Check-in [823fe7f5]
Login

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

Overview
Comment:Begin purging dirty pages from the cache once 90% of the cache is dirty (insteadof waiting until it is 100% dirty). This improves performance in some circumstances by effectively reserving 10% of the configured page-cache for frequently reused read-only pages. (CVS 6341)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:823fe7f5551e121e211d1ede606a7ce7487ffe0d
User & Date: danielk1977 2009-03-05 14:59:40
Context
2009-03-12
14:43
Make calls to sqlite3BtreeRollbackStmt() no-ops when passed a Btree* handle that does not have an open statement transaction. Ticket #3718. (CVS 6342) check-in: a1bb1aef user: danielk1977 tags: trunk
2009-03-05
14:59
Begin purging dirty pages from the cache once 90% of the cache is dirty (insteadof waiting until it is 100% dirty). This improves performance in some circumstances by effectively reserving 10% of the configured page-cache for frequently reused read-only pages. (CVS 6341) check-in: 823fe7f5 user: danielk1977 tags: trunk
14:53
Comment out a recently added assert statement that is failing. (CVS 6340) check-in: d0b2015f user: danielk1977 tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/pcache1.c.

12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
...
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
**
** This file implements the default page cache implementation (the
** sqlite3_pcache interface). It also contains part of the implementation
** of the SQLITE_CONFIG_PAGECACHE and sqlite3_release_memory() features.
** If the default page cache implementation is overriden, then neither of
** these two features are available.
**
** @(#) $Id: pcache1.c,v 1.8 2009/01/23 16:45:01 danielk1977 Exp $
*/

#include "sqliteInt.h"

typedef struct PCache1 PCache1;
typedef struct PgHdr1 PgHdr1;
typedef struct PgFreeslot PgFreeslot;
................................................................................
    goto fetch_out;
  }

  /* Step 3 of header comment. */
  nPinned = pCache->nPage - pCache->nRecyclable;
  if( createFlag==1 && pCache->bPurgeable && (
        nPinned>=(pcache1.nMaxPage+pCache->nMin-pcache1.nMinPage)
     || nPinned>=(pCache->nMax)
  )){
    goto fetch_out;
  }

  if( pCache->nPage>=pCache->nHash && pcache1ResizeHash(pCache) ){
    goto fetch_out;
  }







|







 







|







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
...
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
**
** This file implements the default page cache implementation (the
** sqlite3_pcache interface). It also contains part of the implementation
** of the SQLITE_CONFIG_PAGECACHE and sqlite3_release_memory() features.
** If the default page cache implementation is overriden, then neither of
** these two features are available.
**
** @(#) $Id: pcache1.c,v 1.9 2009/03/05 14:59:40 danielk1977 Exp $
*/

#include "sqliteInt.h"

typedef struct PCache1 PCache1;
typedef struct PgHdr1 PgHdr1;
typedef struct PgFreeslot PgFreeslot;
................................................................................
    goto fetch_out;
  }

  /* Step 3 of header comment. */
  nPinned = pCache->nPage - pCache->nRecyclable;
  if( createFlag==1 && pCache->bPurgeable && (
        nPinned>=(pcache1.nMaxPage+pCache->nMin-pcache1.nMinPage)
     || nPinned>=(pCache->nMax * 9 / 10)
  )){
    goto fetch_out;
  }

  if( pCache->nPage>=pCache->nHash && pcache1ResizeHash(pCache) ){
    goto fetch_out;
  }

Changes to test/pcache.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
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
..
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
#    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.3 2009/01/07 15:33:46 drh 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
................................................................................
  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;
    PRAGMA auto_vacuum=0;
  }
  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.1 {
  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.2 {
  execsql {
................................................................................
    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;







|







 







|



|











|









|





|







|







 







>
>


|






|









|




|




|




|







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
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
..
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
#    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.4 2009/03/05 14:59:40 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
................................................................................
  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=12;
    PRAGMA auto_vacuum=0;
  }
  pcache_stats
} {current 1 max 12 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 12 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 12 min 10 recyclable 0}

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

do_test pcache-1.6.1 {
  execsql {
    BEGIN;
    SELECT * FROM sqlite_master;
  } db2
  pcache_stats
} {current 11 max 22 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.2 {
  execsql {
................................................................................
    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);
    CREATE INDEX i10 ON t9(a, b);
    CREATE INDEX i11 ON t9(a, b);
  } 
  pcache_stats
} {current 23 max 22 min 20 recyclable 0}

do_test pcache-1.7 {
  execsql {
    CREATE TABLE t10(a, b, c);
  } 
  pcache_stats
} {current 24 max 22 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 23 max 22 min 20 recyclable 0}

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

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

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

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;