Documentation Source Text

Check-in [954e5c9945]
Login

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

Overview
Comment:Application note on the default page size change. Updates to the change log.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 954e5c994519a1edf043b32bd375fb51b41b471f
User & Date: drh 2016-03-04 15:33:54.022
Context
2016-03-08
13:11
Add documentation for the SQLITE_STMTJRNL_SPILL compile-time option. (check-in: 0449652cda user: drh tags: trunk)
2016-03-04
15:33
Application note on the default page size change. Updates to the change log. (check-in: 954e5c9945 user: drh tags: trunk)
2016-03-03
16:20
Add hashes to the change log. (check-in: 2fdaab5ed5 user: drh tags: branch-3.11)
2016-03-02
13:16
Tweaks to the description of the sqlite_stat1 table in the fileformat2 document. (check-in: d4c54c7ebe user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/changes.in.
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
proc chng {date desc {options {}}} {
  global nChng aChng
  set aChng($nChng) [list $date $desc $options]
  incr nChng
}

chng {2016-04-00 (3.12.0)} {





<p><b>Performance enhancements:</b>
<li>Enhancements to the [https://www.sqlite.org/src/doc/trunk/doc/lemon.html|Lemon]
    parser generator so that it creates a smaller and faster SQL parser.
<li>Only create [master journal] files if two or more attached databases are
(1) modified, (2) do not have [PRAGMA synchronous] set to OFF, and
(3) do not have the [journal_mode] set to OFF, MEMORY, or WAL.


<p><b>New Features:</b>
<li>Added the [SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER] option to [sqlite3_db_config()]
    which allows the two-argument version of the [fts3_tokenizer()] SQL function to
    be enabled or disabled at run-time.
<li>The [PRAGMA defer_foreign_keys=ON] statement now also disables 
    [foreign key actions|RESTRICT actions] on foreign key.
<p><b>Bug fixes:</b>
<li>Make sure the [sqlite3_set_auxdata()] values from multiple triggers
    within a single statement do not interfere with one another.
    Fix for ticket [https://www.sqlite.org/src/info/dc9b1c91|dc9b1c91].
}











chng {2016-02-15 (3.11.0)} {
<p><b>General improvements:</b>
<li>Enhanced [WAL mode] so that it works efficiently with transactions that are
    larger than the [cache_size].
<li>Added the [FTS5 detail option].
<li>Added the "EXTRA" option to [PRAGMA synchronous] that does a sync of the







>
>
>
>
>






>
>











>
>
>
>
>
>
>
>
>
>







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
proc chng {date desc {options {}}} {
  global nChng aChng
  set aChng($nChng) [list $date $desc $options]
  incr nChng
}

chng {2016-04-00 (3.12.0)} {
<p><b><font color='red'>Potentially Disruptive Change:</font></b>
<li>The [SQLITE_DEFAULT_PAGE_SIZE] is increased from 1024 to 4096.  
    The [SQLITE_DEFAULT_CACHE_SIZE] from 2000 to -2000 so as to continue using
    the same amount of memory.  See the application note on the
    [version 3.12.0 page size change] for further information.
<p><b>Performance enhancements:</b>
<li>Enhancements to the [https://www.sqlite.org/src/doc/trunk/doc/lemon.html|Lemon]
    parser generator so that it creates a smaller and faster SQL parser.
<li>Only create [master journal] files if two or more attached databases are
(1) modified, (2) do not have [PRAGMA synchronous] set to OFF, and
(3) do not have the [journal_mode] set to OFF, MEMORY, or WAL.
<li>Only create [statement journal] file when their size exceeds a threashold
    (currently 64KiB).  Otherwise the journal is held in memory and no I/O occurs.
<p><b>New Features:</b>
<li>Added the [SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER] option to [sqlite3_db_config()]
    which allows the two-argument version of the [fts3_tokenizer()] SQL function to
    be enabled or disabled at run-time.
<li>The [PRAGMA defer_foreign_keys=ON] statement now also disables 
    [foreign key actions|RESTRICT actions] on foreign key.
<p><b>Bug fixes:</b>
<li>Make sure the [sqlite3_set_auxdata()] values from multiple triggers
    within a single statement do not interfere with one another.
    Fix for ticket [https://www.sqlite.org/src/info/dc9b1c91|dc9b1c91].
}

chng {2016-03-03 (3.11.1)} {
<li>Improvements to the Makefiles and build scripts used by VisualStudio.
<li>Fix an [FTS5] issue in which the 'optimize' command could cause index corruption.
<li>Fix a buffer overread that might occur if [FTS5] is used to query a corrupt
    database file.
<li>Increase the maximum "scope" value for the [spellfix1] extension from 6 to 30.
<li>SQLITE_SOURCE_ID: "2016-03-03 16:17:53 f047920ce16971e573bc6ec9a48b118c9de2b3a7"
<li>SHA1 for sqlite3.c: 3da832fd2af36eaedb05d61a8f4c2bb9f3d54265
} {patchagainst 1}

chng {2016-02-15 (3.11.0)} {
<p><b>General improvements:</b>
<li>Enhanced [WAL mode] so that it works efficiently with transactions that are
    larger than the [cache_size].
<li>Added the [FTS5 detail option].
<li>Added the "EXTRA" option to [PRAGMA synchronous] that does a sync of the
Changes to pages/compile.in.
143
144
145
146
147
148
149
150
151


152
153

154
155
156
157
158
159
160
  [auto_vacuum] flag set by default to OFF (0), FULL (1), or
  INCREMENTAL (2). The default value is 0 meaning that databases
  are created with auto-vacuum turned off.
  In any case the compile-time default may be overridden by the 
  [PRAGMA auto_vacuum] command.
}

COMPILE_OPTION {SQLITE_DEFAULT_CACHE_SIZE=<i>&lt;pages&gt;</i>} {
  This macro sets the default size of the page-cache for each attached


  database, in pages. This can be overridden by the 
  [PRAGMA cache_size] command. The default value is 2000.

}

COMPILE_OPTION {SQLITE_DEFAULT_FILE_FORMAT=<i>&lt;1 or 4&gt;</i>} {
  The default [schema format number] used by SQLite when creating
  new database files is set by this macro.  The schema formats are all
  very similar.  The difference between formats 1 and 4 is that format
  4 understands [descending indices] and has a tighter encoding for







|
|
>
>
|
|
>







143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
  [auto_vacuum] flag set by default to OFF (0), FULL (1), or
  INCREMENTAL (2). The default value is 0 meaning that databases
  are created with auto-vacuum turned off.
  In any case the compile-time default may be overridden by the 
  [PRAGMA auto_vacuum] command.
}

COMPILE_OPTION {SQLITE_DEFAULT_CACHE_SIZE=<i>&lt;N&gt;</i>} {
  This macro sets the default maximum size of the page-cache for each attached
  database.  A positive value means that the limit is N page.  If N is negative
  that means to limit the cache size to -N*1024 bytes.
  The suggested maximum cache size can be overridden by the 
  [PRAGMA cache_size] command. The default value is -2000, which translates
  into a maximum of 2048000 bytes per cache.
}

COMPILE_OPTION {SQLITE_DEFAULT_FILE_FORMAT=<i>&lt;1 or 4&gt;</i>} {
  The default [schema format number] used by SQLite when creating
  new database files is set by this macro.  The schema formats are all
  very similar.  The difference between formats 1 and 4 is that format
  4 understands [descending indices] and has a tighter encoding for
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
  are available by default. The default value is 1 ([SQLITE_CONFIG_MEMSTATUS]
  related features enabled).
}

COMPILE_OPTION {SQLITE_DEFAULT_PAGE_SIZE=<i>&lt;bytes&gt;</i>} {
  This macro is used to set the default page-size used when a
  database is created. The value assigned must be a power of 2. The
  default value is 1024. The compile-time default may be overridden at 
  runtime by the [PRAGMA page_size] command.
}

COMPILE_OPTION {SQLITE_DEFAULT_WAL_AUTOCHECKPOINT=<i>&lt;pages&gt;</i>} {
  This macro sets the default page count for the [WAL]
  [checkpointing | automatic checkpointing] feature.  If unspecified,
  the default page count is 1000.







|







225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
  are available by default. The default value is 1 ([SQLITE_CONFIG_MEMSTATUS]
  related features enabled).
}

COMPILE_OPTION {SQLITE_DEFAULT_PAGE_SIZE=<i>&lt;bytes&gt;</i>} {
  This macro is used to set the default page-size used when a
  database is created. The value assigned must be a power of 2. The
  default value is 4096. The compile-time default may be overridden at 
  runtime by the [PRAGMA page_size] command.
}

COMPILE_OPTION {SQLITE_DEFAULT_WAL_AUTOCHECKPOINT=<i>&lt;pages&gt;</i>} {
  This macro sets the default page count for the [WAL]
  [checkpointing | automatic checkpointing] feature.  If unspecified,
  the default page count is 1000.
Changes to pages/news.in.
14
15
16
17
18
19
20







21
22
23
24
25
26
27
  hd_puts "<h3>$date - $title</h3>"
  regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
  regsub -all {[Tt]icket #(\d+)} $txt \
      {<a href="http://www.sqlite.org/cvstrac/tktview?tn=\1">\0</a>} txt
  hd_resolve "<blockquote>$txt</blockquote>"
  hd_puts "<hr width=\"50%\">"
}








newsitem {2016-02-15} {Release 3.11.0} {
<p>SQLite [version 3.11.0] is a regularly scheduled maintenance release.
}

newsitem {2016-01-20} {Release 3.10.2} {
<p>Yikes!  An optimization attempt gone bad resulted in a 







>
>
>
>
>
>
>







14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
  hd_puts "<h3>$date - $title</h3>"
  regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
  regsub -all {[Tt]icket #(\d+)} $txt \
      {<a href="http://www.sqlite.org/cvstrac/tktview?tn=\1">\0</a>} txt
  hd_resolve "<blockquote>$txt</blockquote>"
  hd_puts "<hr width=\"50%\">"
}

newsitem {2016-03-03} {Release 3.11.1} {
<p>SQLite [version 3.11.1] is a patch release that fixes problems in the
   new [FTS5] extension and increases a default setting in the [spellfix1]
   extension, and implements enhancements to some of the Windows makefiles.
   The SQLite core is unchanged from 3.11.0. Upgrading is optional.
}

newsitem {2016-02-15} {Release 3.11.0} {
<p>SQLite [version 3.11.0] is a regularly scheduled maintenance release.
}

newsitem {2016-01-20} {Release 3.10.2} {
<p>Yikes!  An optimization attempt gone bad resulted in a 
Added pages/pgszchng2016.in.












































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
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
<title>Change in Default Page Size in SQLite Version 3.12.0</title>
<tcl>hd_keywords {version 3.12.0 page size change}</tcl>

<h1 align="center">The Default Page Size Change of SQLite 3.12.0</h1>

<h2>1.0 Introduction</h2>

<p>
An SQLite database file consists of one or more "pages".
For a single database file, all pages are the same size, though
for different database files, the page size can any power of
two between 512 and 65536, inclusive.
</p>

<p>
Since the SQLite database file format was designed (in 2003) the
default [page_size|page size] for new databases has been 1024 bytes.  
This was a reasonable choice in 2003.  But on modern hardware, 
a 4096 byte page is a faster and better choice.
So, beginning with SQLite [version 3.12.0] (circa 2016) the default 
page size for new database files has been increased to 4096 bytes.
</p>

<p>
The upper bound on the database [cache_size|cache size] has 
traditionally defaulted to 2000 page.  SQLite [version 3.12.0] also
changes this default setting to be "-2000" which means 2000*1024
bytes, regardless of page size.  So, the upper bound on the amount
of memory used for the page cache is unchanged.
</p>

<h2>2.0 <u>Not</u> a Compatibility Break</h2>

<p>
These changes in the default behavior of SQLite are not
a compatibility break.  All legacy database files continue to be
readable and writable by newer versions of SQLite, and all newly
created database files continue to be readable and writable by
legacy versions of the SQLite library.  The only thing that is changing
is some default settings.  This should result in a performance
increase for many applications.
</p>

<p>
Though most application should not notice any change (except that
they run a little faster), if problems arise then the
legacy behavior can be restored at compile-time by using the
following options to the C-compiler:

<blockquote><pre>
-DSQLITE_DEFAULT_PAGE_SIZE=1024 
-DSQLITE_DEFAULT_CACHE_SIZE=2000
</pre></blockquote>

<p>
The page size and cache size can also be set or changed at run-time using the
[page_size pragma] and [cache_size pragma], respectively.

<h2>3.0 Possible Negative Consequences Of This Change</h2>

<p>
The minimum size of an SQLite database is one page for each table and
each index.  With a larger page size, the size of an empty database
for a given schema will grow by a factor of four, therefore.  However,
once the database begins to fill with content the size of the older
1024-byte page databases and the newer 4096-byte page databases will
quickly converge.  Due to relaxed bin-packing constraints, the 
4096-byte page size might actually result in a smaller file, once
substantial content is added.
</p>
Changes to pages/pragma.in.
269
270
271
272
273
274
275
276

277
278
279
280
281
282
283
    that SQLite will hold in memory at once per open database file.)^  Whether
    or not this suggestion is honored is at the discretion of the
    [sqlite3_pcache_methods2 | Application Defined Page Cache].
    The default page cache that is built into SQLite honors the request,
    however alternative application-defined page cache implementations
    may choose to interpret the suggested cache size in different ways
    or to ignore it all together.
    ^The default suggested cache size is 2000 pages.

    ^The default suggested cache size can be altered using the
    [SQLITE_DEFAULT_CACHE_SIZE] compile-time options.
    ^The TEMP database has a default suggested cache size of 0 pages.</p>

    <p>^If the argument N is positive then the suggested cache size is set 
    to N. ^If the argument N is negative, then the
    number of cache pages is adjusted to use approximately abs(N*1024) bytes







|
>







269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
    that SQLite will hold in memory at once per open database file.)^  Whether
    or not this suggestion is honored is at the discretion of the
    [sqlite3_pcache_methods2 | Application Defined Page Cache].
    The default page cache that is built into SQLite honors the request,
    however alternative application-defined page cache implementations
    may choose to interpret the suggested cache size in different ways
    or to ignore it all together.
    ^The default suggested cache size is -2000, which means the cache size
    is limited to 2048000 bytes of memory.
    ^The default suggested cache size can be altered using the
    [SQLITE_DEFAULT_CACHE_SIZE] compile-time options.
    ^The TEMP database has a default suggested cache size of 0 pages.</p>

    <p>^If the argument N is positive then the suggested cache size is set 
    to N. ^If the argument N is negative, then the
    number of cache pages is adjusted to use approximately abs(N*1024) bytes
790
791
792
793
794
795
796
797
798
799


800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
Pragma page_size {
   <p>^(<b>PRAGMA DB.page_size;
       <br>PRAGMA DB.page_size = </b><i>bytes</i><b>;</b></p>
    <p>Query or set the page size of the database.)^ ^The page
    size must be a power of two between 512 and 65536 inclusive.
    </p>

    <p>^When a new database is created, SQLite assigned a default page size
    based on information received from the xSectorSize and 
    xDeviceCharacteristics methods of the [sqlite3_io_methods] object


    of the newly created database file.  ^The page_size pragma will only
    cause an immediate change in the
    page size if it is issued while the database is still empty, prior 
    to the first CREATE TABLE statement.  ^(If the page_size pragma is
    used to specify a new page size just prior to
    running the [VACUUM] command and if the database is not in
    [WAL | WAL journal mode] then [VACUUM] will change the page
    size to the new value.)^</p>

    <p>^If SQLite is compiled with the SQLITE_ENABLE_ATOMIC_WRITE option,
    then the default page size is chosen to be the largest page size
    less than or equal to SQLITE_MAX_DEFAULT_PAGE_SIZE for which atomic
    write is enabled according to the
    xDeviceCharacteristics method of the [sqlite3_io_methods] object for
    the database file.  ^If the SQLITE_ENABLE_ATOMIC_WRITE option is
    disabled or if xDeviceCharacteristics reports no suitable atomic
    write page sizes, then the default page size is the larger of 
    SQLITE_DEFAULT_PAGE_SIZE
    and the sector size as reported by the xSectorSize method of the
    [sqlite3_io_methods] object, but not more than 
    SQLITE_MAX_DEFAULT_PAGE_SIZE.  ^The normal configuration for SQLite
    running on workstations is for atomic write to be
    disabled, for the maximum page size to be set to 65536, for
    SQLITE_DEFAULT_PAGE_SIZE to be 1024, and for the
    maximum default page size to be set to 8192.  The default xSectorSize
    method on unix workstation implementations always reports a sector size
    of 512 bytes.  Hence, 
    the default page size chosen by SQLite on unix is usually 1024 bytes.
    On windows, the GetDiskFreeSpace() interface is used to obtain the
    actual device sector size and hence the default page size on windows
    will sometimes be greater than 1024.</p>
}

Pragma max_page_count {
    <p>^(<b>PRAGMA DB.max_page_count;
        <br>PRAGMA DB.max_page_count = </b><i>N</i><b>;</b></p>
    <p>Query or set the maximum number of pages in the database file.)^
    ^Both forms of the pragma return the maximum page count.  ^The second







|
|
|
>
>
|
|







<
<
<
<
<
<
<
<
|
<
<
<
<
<
<
<
<
<
<
<
|
<







791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811








812











813

814
815
816
817
818
819
820
Pragma page_size {
   <p>^(<b>PRAGMA DB.page_size;
       <br>PRAGMA DB.page_size = </b><i>bytes</i><b>;</b></p>
    <p>Query or set the page size of the database.)^ ^The page
    size must be a power of two between 512 and 65536 inclusive.
    </p>

    <p>^When a new database is created, SQLite assigned a page size to
    the database based on platform and filesystem.  For many years,
    the default page size was almost always 1024 bytes, but beginning
    with SQLite [version 3.12.0] in 2016, the default page size increased
    to 4096.

    <p>^The page_size pragma will only cause an immediate change in the
    page size if it is issued while the database is still empty, prior 
    to the first CREATE TABLE statement.  ^(If the page_size pragma is
    used to specify a new page size just prior to
    running the [VACUUM] command and if the database is not in
    [WAL | WAL journal mode] then [VACUUM] will change the page
    size to the new value.)^</p>









    <p>^The [SQLITE_DEFAULT_PAGE_SIZE] compile-time option can be used











    to change the default page size assigned to new databases.

}

Pragma max_page_count {
    <p>^(<b>PRAGMA DB.max_page_count;
        <br>PRAGMA DB.max_page_count = </b><i>N</i><b>;</b></p>
    <p>Query or set the maximum number of pages in the database file.)^
    ^Both forms of the pragma return the maximum page count.  ^The second
Changes to pages/tempfiles.in.
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
[WAL mode], individual database files are updated atomically
across a power-loss, but in the case of a multi-file transactions,
some files might rollback while others roll forward after
power is restored.
</p>


<tcl>hd_fragment stmtjrnl</tcl>
<h3>2.5 Statement Journal Files</h3>

<p>
A statement journal file is used to rollback partial results of
a single statement within a larger transaction.  For example, suppose
an UPDATE statement will attempt to modify 100 rows in the database.
But after modifying the first 50 rows, the UPDATE hits







|







250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
[WAL mode], individual database files are updated atomically
across a power-loss, but in the case of a multi-file transactions,
some files might rollback while others roll forward after
power is restored.
</p>


<tcl>hd_fragment stmtjrnl {statement journal} {statement journals}</tcl>
<h3>2.5 Statement Journal Files</h3>

<p>
A statement journal file is used to rollback partial results of
a single statement within a larger transaction.  For example, suppose
an UPDATE statement will attempt to modify 100 rows in the database.
But after modifying the first 50 rows, the UPDATE hits