Documentation Source Text

</tcl>

<h1>Recommended Compile-time Options</h1>

<p>The following compile-time options are recommended for applications that
are able to use them, in order to minimized the number of CPU cycles and
the bytes of memory used by SQLite.
Note all of these compile-time options are usable by every application.
For example, the SQLITE_THREADSAFE=0 option is only usable by applications
that never access SQLite from more than one thread at a time.  And the
SQLITE_OMIT_PROGESS_CALLBACK option is only usable by applications that
doe not use the [sqlite3_progress_handler()] interface.  And so forth.

<p>It is impossible to test every possible combination of compile-time
options for SQLite.  But the following set of compile-time options is

</tcl>

<h1>Recommended Compile-time Options</h1>

<p>The following compile-time options are recommended for applications that
are able to use them, in order to minimized the number of CPU cycles and
the bytes of memory used by SQLite.
Not all of these compile-time options are usable by every application.
For example, the SQLITE_THREADSAFE=0 option is only usable by applications
that never access SQLite from more than one thread at a time.  And the
SQLITE_OMIT_PROGESS_CALLBACK option is only usable by applications that
doe not use the [sqlite3_progress_handler()] interface.  And so forth.

<p>It is impossible to test every possible combination of compile-time
options for SQLite.  But the following set of compile-time options is

<p>^If M is -1 in the initial journal header, then the number of page records
that follow is computed by computing how many page records will fit in
the available space of the remainder of the journal file.</p>

<tcl>hd_fragment walformat {WAL format}</tcl>
<h1>The Write-Ahead Log</h1>

<p>Beginning with [version 3.7.0] (dateof:3.7.0), 
SQLite supports a new transaction
control mechanism called "[WAL | write-ahead log]" or "[WAL]".
^When a database is in WAL mode, all connections to that database must
use the WAL.  ^A particular database will use either a rollback journal
or a WAL, but not both at the same time.
^The WAL is always located in the same directory as the database
file and has the same name as the database file but with the string

<p>^If M is -1 in the initial journal header, then the number of page records
that follow is computed by computing how many page records will fit in
the available space of the remainder of the journal file.</p>

<tcl>hd_fragment walformat {WAL format}</tcl>
<h1>The Write-Ahead Log</h1>

<p>Beginning with [version 3.7.0] ([dateof:3.7.0]), 
SQLite supports a new transaction
control mechanism called "[WAL | write-ahead log]" or "[WAL]".
^When a database is in WAL mode, all connections to that database must
use the WAL.  ^A particular database will use either a rollback journal
or a WAL, but not both at the same time.
^The WAL is always located in the same directory as the database
file and has the same name as the database file but with the string

<tcl>hd_fragment pagecache {pagecache memory allocator}</tcl>
<h2> Page cache memory</h2>

<p>In most applications, the database page cache subsystem within 
SQLite uses more dynamically allocated memory than all other parts
of SQLite combined.  It is not unusual to see the database page cache
consumes over 10 times more memory than the rest of SQLite combined.</p>

<p>SQLite can be configured to make page cache memory allocations from
a separate and distinct memory pool of fixed-size
slots.  This can have two advantages:</p>

<ul>
<li><p>
................................................................................

<a name="heaplimit"></a>
<h2> Setting memory usage limits</h2>

<p>The [sqlite3_soft_heap_limit64()] interface can be used to set an
upper bound on the total amount of outstanding memory that the
general-purpose memory allocator for SQLite will allow to be outstanding
at one time.  If attempts are made to allocate more memory that specified
by the soft heap limit, then SQLite will first attempt to free cache
memory before continuing with the allocation request.  The soft heap
limit mechanism only works if [memory statistics] are enabled and
it works best
if the SQLite library is compiled with the [SQLITE_ENABLE_MEMORY_MANAGEMENT]
compile-time option.</p>

<p>The soft heap limit is "soft" in this sense:  If SQLite is not able
to free up enough auxiliary memory to stay below the limit, it goes
ahead and allocations the extra memory and exceeds its limit.  This occurs
under the theory that it is better to use additional memory than to fail
outright.</p>

<p>As of SQLite [version 3.6.1] ([dateof:3.6.1]), 
the soft heap limit only applies to the
general-purpose memory allocator.  The soft heap limit does not know
about or interact with the [scratch memory allocator], 

<tcl>hd_fragment pagecache {pagecache memory allocator}</tcl>
<h2> Page cache memory</h2>

<p>In most applications, the database page cache subsystem within 
SQLite uses more dynamically allocated memory than all other parts
of SQLite combined.  It is not unusual to see the database page cache
consume over 10 times more memory than the rest of SQLite combined.</p>

<p>SQLite can be configured to make page cache memory allocations from
a separate and distinct memory pool of fixed-size
slots.  This can have two advantages:</p>

<ul>
<li><p>
................................................................................

<a name="heaplimit"></a>
<h2> Setting memory usage limits</h2>

<p>The [sqlite3_soft_heap_limit64()] interface can be used to set an
upper bound on the total amount of outstanding memory that the
general-purpose memory allocator for SQLite will allow to be outstanding
at one time.  If attempts are made to allocate more memory than specified
by the soft heap limit, then SQLite will first attempt to free cache
memory before continuing with the allocation request.  The soft heap
limit mechanism only works if [memory statistics] are enabled and
it works best
if the SQLite library is compiled with the [SQLITE_ENABLE_MEMORY_MANAGEMENT]
compile-time option.</p>

<p>The soft heap limit is "soft" in this sense:  If SQLite is not able
to free up enough auxiliary memory to stay below the limit, it goes
ahead and allocates the extra memory and exceeds its limit.  This occurs
under the theory that it is better to use additional memory than to fail
outright.</p>

<p>As of SQLite [version 3.6.1] ([dateof:3.6.1]), 
the soft heap limit only applies to the
general-purpose memory allocator.  The soft heap limit does not know
about or interact with the [scratch memory allocator], 

Pragma {automatic_index} {
    <p>^(<b>PRAGMA automatic_index;
     <br>PRAGMA automatic_index = </b><i>boolean</i><b>;</b></p>

    <p>Query, set, or clear the [automatic indexing] capability.)^
    <p>[Automatic indexing] is enabled by default as of 
    [version 3.7.17] (dateof:3.7.17]),
    but this might change in future releases of SQLite.
}

Pragma {auto_vacuum} {
    <p><b>PRAGMA DB.auto_vacuum;<br>
          PRAGMA DB.auto_vacuum = </b>
           <i>0 | NONE | 1 | FULL | 2 | INCREMENTAL</i><b>;</b></p>

Pragma {automatic_index} {
    <p>^(<b>PRAGMA automatic_index;
     <br>PRAGMA automatic_index = </b><i>boolean</i><b>;</b></p>

    <p>Query, set, or clear the [automatic indexing] capability.)^
    <p>[Automatic indexing] is enabled by default as of 
    [version 3.7.17] ([dateof:3.7.17]),
    but this might change in future releases of SQLite.
}

Pragma {auto_vacuum} {
    <p><b>PRAGMA DB.auto_vacuum;<br>
          PRAGMA DB.auto_vacuum = </b>
           <i>0 | NONE | 1 | FULL | 2 | INCREMENTAL</i><b>;</b></p>

of any current reader.  The checkpoint has to stop at that point because
otherwise it might overwrite part of the database file that the reader
is actively using.  The checkpoint remembers (in the wal-index) how far
it got and will resume transferring content from the WAL to the database
from where it left off on the next invocation.</p>

<p>Thus a long-running read transaction can prevent a checkpointer from
making progress.  But presumably every read transactions will eventually
end and the checkpointer will be able to continue.</p>

<p>Whenever a write operation occurs, the writer checks how much progress
the checkpointer has made, and if the entire WAL has been transferred into
the database and synced and if no readers are making use of the WAL, then
the writer will rewind the WAL back to the beginning and start putting new
transactions at the beginning of the WAL.  This mechanism prevents a WAL

of any current reader.  The checkpoint has to stop at that point because
otherwise it might overwrite part of the database file that the reader
is actively using.  The checkpoint remembers (in the wal-index) how far
it got and will resume transferring content from the WAL to the database
from where it left off on the next invocation.</p>

<p>Thus a long-running read transaction can prevent a checkpointer from
making progress.  But presumably every read transaction will eventually
end and the checkpointer will be able to continue.</p>

<p>Whenever a write operation occurs, the writer checks how much progress
the checkpointer has made, and if the entire WAL has been transferred into
the database and synced and if no readers are making use of the WAL, then
the writer will rewind the WAL back to the beginning and start putting new
transactions at the beginning of the WAL.  This mechanism prevents a WAL