Debian Codegraph @

www/intern-v-extern-blob.html

raw
1 <!DOCTYPE html>
2 <html><head>
3 <meta name="viewport" content="width=device-width, initial-scale=1.0">
4 <meta http-equiv="content-type" content="text/html; charset=UTF-8">
5 <link href="sqlite.css" rel="stylesheet">
6 <title>Internal Versus External BLOBs</title>
7 <!-- path= -->
8 </head>
9 <body>
10 <div class=nosearch>
11 <a href="index.html">
12 <img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0">
13 </a>
14 <div><!-- IE hack to prevent disappearing logo --></div>
15 <div class="tagline desktoponly">
16 Small. Fast. Reliable.<br>Choose any three.
17 </div>
18 <div class="menu mainmenu">
19 <ul>
20 <li><a href="index.html">Home</a>
21 <li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
22 <li class='wideonly'><a href='about.html'>About</a>
23 <li class='desktoponly'><a href="docs.html">Documentation</a>
24 <li class='desktoponly'><a href="download.html">Download</a>
25 <li class='wideonly'><a href='copyright.html'>License</a>
26 <li class='desktoponly'><a href="support.html">Support</a>
27 <li class='desktoponly'><a href="prosupport.html">Purchase</a>
28 <li class='search' id='search_menubutton'>
29 <a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
30 </ul>
31 </div>
32 <div class="menu submenu" id="submenu">
33 <ul>
34 <li><a href='about.html'>About</a>
35 <li><a href='docs.html'>Documentation</a>
36 <li><a href='download.html'>Download</a>
37 <li><a href='support.html'>Support</a>
38 <li><a href='prosupport.html'>Purchase</a>
39 </ul>
40 </div>
41 <div class="searchmenu" id="searchmenu">
42 <form method="GET" action="search">
43 <select name="s" id="searchtype">
44 <option value="d">Search Documentation</option>
45 <option value="c">Search Changelog</option>
46 </select>
47 <input type="text" name="q" id="searchbox" value="">
48 <input type="submit" value="Go">
49 </form>
50 </div>
51 </div>
52 <script>
53 function toggle_div(nm) {
54 var w = document.getElementById(nm);
55 if( w.style.display=="block" ){
56 w.style.display = "none";
57 }else{
58 w.style.display = "block";
59 }
60 }
61 function toggle_search() {
62 var w = document.getElementById("searchmenu");
63 if( w.style.display=="block" ){
64 w.style.display = "none";
65 } else {
66 w.style.display = "block";
67 setTimeout(function(){
68 document.getElementById("searchbox").focus()
69 }, 30);
70 }
71 }
72 function div_off(nm){document.getElementById(nm).style.display="none";}
73 window.onbeforeunload = function(e){div_off("submenu");}
74 /* Disable the Search feature if we are not operating from CGI, since */
75 /* Search is accomplished using CGI and will not work without it. */
76 if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
77 document.getElementById("search_menubutton").style.display = "none";
78 }
79 /* Used by the Hide/Show button beside syntax diagrams, to toggle the */
80 function hideorshow(btn,obj){
81 var x = document.getElementById(obj);
82 var b = document.getElementById(btn);
83 if( x.style.display!='none' ){
84 x.style.display = 'none';
85 b.innerHTML='show';
86 }else{
87 x.style.display = '';
88 b.innerHTML='hide';
89 }
90 return false;
91 }
92 var antiRobot = 0;
93 function antiRobotGo(){
94 if( antiRobot!=3 ) return;
95 antiRobot = 7;
96 var j = document.getElementById("mtimelink");
97 if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
98 }
99 function antiRobotDefense(){
100 document.body.onmousedown=function(){
101 antiRobot |= 2;
102 antiRobotGo();
103 document.body.onmousedown=null;
104 }
105 document.body.onmousemove=function(){
106 antiRobot |= 2;
107 antiRobotGo();
108 document.body.onmousemove=null;
109 }
110 setTimeout(function(){
111 antiRobot |= 1;
112 antiRobotGo();
113 }, 100)
114 antiRobotGo();
115 }
116 antiRobotDefense();
117 </script>
118
119
120 <body>
121 <h1 align="center">
122 Internal Versus External BLOBs in SQLite
123 </h1>
124
125 <p>
126 If you have a database of large BLOBs, do you get better read performance
127 when you store the complete
128 BLOB content directly in the database or is it faster to store each BLOB
129 in a separate file and store just the corresponding filename in the database?
130 </p>
131
132 <p>
133 To try to answer this, we ran 49 test cases with various BLOB sizes and
134 SQLite page sizes on a Linux workstation (Ubuntu circa 2011 with the
135 Ext4 filesystem on a fast SATA disk).
136 For each test case, a database was created containing 100MB of BLOB
137 content. The sizes of the BLOBs ranged from 10KB to 1MB. The number
138 of BLOBs varied in order to keep the total BLOB content at about 100MB.
139 (Hence, 100 BLOBs for the 1MB size and 10000 BLOBs for the 10K size and
140 so forth.) SQLite <a href="releaselog/3_7_8.html">version 3.7.8</a> (2011-09-19) was used.
141 </p>
142
143 <blockquote><i>
144 Update: New measurements for SQLite version 3.19.0
145 (2017-05-22) show that SQLite is about
146 <a href="fasterthanfs.html">35% faster</a> than direct disk I/O for
147 both reads and writes of 10KB blobs.
148 </i></blockquote>
149
150 <p>
151 The matrix below shows the time needed to read BLOBs stored in separate files
152 divided by the time needed to read BLOBs stored entirely in the database.
153 Hence, for numbers larger than 1.0, it is faster to store the BLOBs directly
154 in the database. For numbers smaller than 1.0, it is faster to store the BLOBs
155 in separate files.
156 </p>
157
158 <p>
159 In every case, the pager cache size was adjusted to keep the amount of
160 cache memory at about 2MB.
161 For example, a 2000 page cache was used for 1024 byte pages
162 and a 31 page cache was used for 65536 byte pages.
163 The BLOB values were read in a random order.
164 </p>
165
166 <table border="1" cellpadding="5" cellspacing="0" align="center">
167 <tr>
168 <th rowspan="2">Database Page Size<th colspan="7">BLOB size
169 <tr>
170 <th>10k<th>20k<th>50k<th>100k<th>200k<th>500k<th>1m
171
172 <tr>
173 <td>1024<td bgcolor="#b1d3a8">1.535<td bgcolor="#b1d3a8">1.020<td bgcolor="#ffc0d0">0.608<td bgcolor="#ffc0d0">0.456<td bgcolor="#ffc0d0">0.330<td bgcolor="#ffc0d0">0.247<td bgcolor="#ffc0d0">0.233
174
175 <tr>
176 <td>2048<td bgcolor="#b1d3a8">2.004<td bgcolor="#b1d3a8">1.437<td bgcolor="#ffc0d0">0.870<td bgcolor="#ffc0d0">0.636<td bgcolor="#ffc0d0">0.483<td bgcolor="#ffc0d0">0.372<td bgcolor="#ffc0d0">0.340
177
178 <tr>
179 <td>4096<td bgcolor="#b1d3a8">2.261<td bgcolor="#b1d3a8">1.886<td bgcolor="#b1d3a8">1.173<td bgcolor="#ffc0d0">0.890<td bgcolor="#ffc0d0">0.701<td bgcolor="#ffc0d0">0.526<td bgcolor="#ffc0d0">0.487
180
181 <tr>
182 <td>8192<td bgcolor="#b1d3a8">2.240<td bgcolor="#b1d3a8">1.866<td bgcolor="#b1d3a8">1.334<td bgcolor="#b1d3a8">1.035<td bgcolor="#ffc0d0">0.830<td bgcolor="#ffc0d0">0.625<td bgcolor="#ffc0d0">0.720
183
184 <tr>
185 <td>16384<td bgcolor="#b1d3a8">2.439<td bgcolor="#b1d3a8">1.757<td bgcolor="#b1d3a8">1.292<td bgcolor="#b1d3a8">1.023<td bgcolor="#ffc0d0">0.829<td bgcolor="#ffc0d0">0.820<td bgcolor="#ffc0d0">0.598
186
187 <tr>
188 <td>32768<td bgcolor="#b1d3a8">1.878<td bgcolor="#b1d3a8">1.843<td bgcolor="#b1d3a8">1.296<td bgcolor="#ffc0d0">0.981<td bgcolor="#ffc0d0">0.976<td bgcolor="#ffc0d0">0.675<td bgcolor="#ffc0d0">0.613
189
190 <tr>
191 <td>65536<td bgcolor="#b1d3a8">1.256<td bgcolor="#b1d3a8">1.255<td bgcolor="#b1d3a8">1.339<td bgcolor="#ffc0d0">0.983<td bgcolor="#ffc0d0">0.769<td bgcolor="#ffc0d0">0.687<td bgcolor="#ffc0d0">0.609
192
193 </table>
194
195
196 <p>We deduce the following rules of thumb from the matrix above:</p>
197
198 <ul>
199 <li><p>
200 A database page size of 8192 or 16384 gives the best performance
201 for large BLOB I/O.
202
203 <li><p>For BLOBs smaller than 100KB, reads are faster when
204 the BLOBs are stored directly in the database file. For BLOBs larger than
205 100KB, reads from a separate file are faster.
206 </ul>
207
208 <p>Of course, your mileage may vary depending on hardware, filesystem,
209 and operating system. Double-check these figures on target hardware
210 before committing to a particular design.
211 <p align="center"><small><i>This page was last updated on 2022-04-18 02:55:50Z </small></i></p>
212