summaryrefslogtreecommitdiffstats
path: root/www/pgszchng2016.html
blob: e6821918162812ee138df88eb2c76cc78e0645d6 (plain)
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
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
<!DOCTYPE html>
<html><head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<link href="sqlite.css" rel="stylesheet">
<title>Change in Default Page Size in SQLite Version 3.12.0</title>
<!-- path= -->
</head>
<body>
<div class=nosearch>
<a href="index.html">
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
</a>
<div><!-- IE hack to prevent disappearing logo --></div>
<div class="tagline desktoponly">
Small. Fast. Reliable.<br>Choose any three.
</div>
<div class="menu mainmenu">
<ul>
<li><a href="index.html">Home</a>
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
<li class='wideonly'><a href='about.html'>About</a>
<li class='desktoponly'><a href="docs.html">Documentation</a>
<li class='desktoponly'><a href="download.html">Download</a>
<li class='wideonly'><a href='copyright.html'>License</a>
<li class='desktoponly'><a href="support.html">Support</a>
<li class='desktoponly'><a href="prosupport.html">Purchase</a>
<li class='search' id='search_menubutton'>
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
</ul>
</div>
<div class="menu submenu" id="submenu">
<ul>
<li><a href='about.html'>About</a>
<li><a href='docs.html'>Documentation</a>
<li><a href='download.html'>Download</a>
<li><a href='support.html'>Support</a>
<li><a href='prosupport.html'>Purchase</a>
</ul>
</div>
<div class="searchmenu" id="searchmenu">
<form method="GET" action="search">
<select name="s" id="searchtype">
<option value="d">Search Documentation</option>
<option value="c">Search Changelog</option>
</select>
<input type="text" name="q" id="searchbox" value="">
<input type="submit" value="Go">
</form>
</div>
</div>
<script>
function toggle_div(nm) {
var w = document.getElementById(nm);
if( w.style.display=="block" ){
w.style.display = "none";
}else{
w.style.display = "block";
}
}
function toggle_search() {
var w = document.getElementById("searchmenu");
if( w.style.display=="block" ){
w.style.display = "none";
} else {
w.style.display = "block";
setTimeout(function(){
document.getElementById("searchbox").focus()
}, 30);
}
}
function div_off(nm){document.getElementById(nm).style.display="none";}
window.onbeforeunload = function(e){div_off("submenu");}
/* Disable the Search feature if we are not operating from CGI, since */
/* Search is accomplished using CGI and will not work without it. */
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
document.getElementById("search_menubutton").style.display = "none";
}
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
function hideorshow(btn,obj){
var x = document.getElementById(obj);
var b = document.getElementById(btn);
if( x.style.display!='none' ){
x.style.display = 'none';
b.innerHTML='show';
}else{
x.style.display = '';
b.innerHTML='hide';
}
return false;
}
var antiRobot = 0;
function antiRobotGo(){
if( antiRobot!=3 ) return;
antiRobot = 7;
var j = document.getElementById("mtimelink");
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
}
function antiRobotDefense(){
document.body.onmousedown=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousedown=null;
}
document.body.onmousemove=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousemove=null;
}
setTimeout(function(){
antiRobot |= 1;
antiRobotGo();
}, 100)
antiRobotGo();
}
antiRobotDefense();
</script>



<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 <a href="pragma.html#pragma_page_size">page size</a> 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 <a href="releaselog/3_12_0.html">version 3.12.0</a> (2016-03-29)) the default 
page size for new database files has been increased to 4096 bytes.
</p>

<p>
The upper bound on the database <a href="pragma.html#pragma_cache_size">cache size</a> has 
traditionally defaulted to 2000 pages.  SQLite <a href="releaselog/3_12_0.html">version 3.12.0</a> 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
<a href="pragma.html#pragma_page_size">page_size pragma</a> and <a href="pragma.html#pragma_cache_size">cache_size pragma</a>, 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>
<p align="center"><small><i>This page last modified on  <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink"  data-href="https://sqlite.org/docsrc/finfo/pages/pgszchng2016.in?m=174c2a23e21a72342">2016-09-14 18:40:10</a> UTC </small></i></p>