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: |
954e5c994519a1edf043b32bd375fb51 |
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
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 | [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. } | | | > > | | > | 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><N></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><1 or 4></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 | are available by default. The default value is 1 ([SQLITE_CONFIG_MEMSTATUS] related features enabled). } COMPILE_OPTION {SQLITE_DEFAULT_PAGE_SIZE=<i><bytes></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 | | | 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><bytes></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><pages></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 | 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. | | > | 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 | 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> | | | | > > | | < < < < < < < < | < < < < < < < < < < < | < | 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 | [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> | | | 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 |
︙ | ︙ |