Debian Codegraph @

www/useovernet.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>SQLite Over a Network, Caveats and Considerations</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
121
122 <h1 align="center">SQLite Over a Network,<br/>Caveats and Considerations</h1>
123
124
125 <h1>Introduction</h1>
126 <p>
127 Users of the SQLite library, particularly application developers,
128 who want to access a SQLite database
129 from different systems connected by a network are often
130 tempted to simply open a <a href="c3ref/sqlite3.html">database connection</a> by specifying
131 a filename which references a database file somewhere within
132 a network filesystem. ("remote database" here)
133 This "file" is then accessed by means of
134 OS API's which permit the illusion of I/O from/to a local file.
135 The illusion is good but imperfect in important ways.
136 </p><p>
137 This simple, "remote database" approach is usually not the best way
138 to use a single SQLite database from multiple systems,
139 (even if it appears to "work"),
140 as it often leads to various kinds of trouble and grief.
141 Because these problems are inevitable with some usages,
142 but not frequent or repeatable,
143 it behooves application developers to not rely
144 on early testing success to decide
145 that their remote database use will work as desired.
146 </p>
147 <h1>Issues Arising with Remote Database Files</h1>
148 <p>
149 This diagram shows components and their linkages
150 for reference in the discussion following:
151 </p>
152
153 <div class="center imgcontainer">
154 <div style="max-width:705px;"><svg xmlns='http://www.w3.org/2000/svg' style='font-size:initial;' class="pikchr" width="705" height="84" viewBox="0 0 940.32 112.32" data-pikchr-date="20250319161943">
155 <path d="M2.16,110.16L182.16,110.16L182.16,2.16L2.16,2.16Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
156 <text x="92.16" y="39.2256" text-anchor="middle" fill="rgb(0,0,0)" font-size="140%" dominant-baseline="central">Client</text>
157 <text x="92.16" y="73.0944" text-anchor="middle" fill="rgb(0,0,0)" font-size="140%" dominant-baseline="central">Application</text>
158 <path d="M380.16,110.16L560.16,110.16L560.16,2.16L380.16,2.16Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
159 <text x="470.16" y="22.2912" text-anchor="middle" fill="rgb(0,0,0)" font-size="140%" dominant-baseline="central">SQLite</text>
160 <text x="470.16" y="56.16" text-anchor="middle" fill="rgb(0,0,0)" font-size="140%" dominant-baseline="central">Database</text>
161 <text x="470.16" y="90.0288" text-anchor="middle" fill="rgb(0,0,0)" font-size="140%" dominant-baseline="central">Engine</text>
162 <path d="M758.16,12.96L758.16,99.36A90 10.8 0 0 0 938.16 99.36L938.16,12.96A90 10.8 0 0 0 758.16 12.96A90 10.8 0 0 0 938.16 12.96" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
163 <text x="848.16" y="47.3256" text-anchor="middle" fill="rgb(0,0,0)" font-size="140%" dominant-baseline="central">Database</text>
164 <text x="848.16" y="81.1944" text-anchor="middle" fill="rgb(0,0,0)" font-size="140%" dominant-baseline="central">File(s)</text>
165 <polygon points="182.16,56.16 193.68,51.84 193.68,60.48" style="fill:rgb(0,0,0)"/>
166 <polygon points="380.16,56.16 368.64,60.48 368.64,51.84" style="fill:rgb(0,0,0)"/>
167 <path d="M187.92,56.16L374.4,56.16" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
168 <text x="281.16" y="37.6056" text-anchor="middle" fill="rgb(0,0,0)" font-size="140%" dominant-baseline="central">SQLite API</text>
169 <text x="281.16" y="74.7144" text-anchor="middle" fill="rgb(0,0,0)" font-size="140%" dominant-baseline="central">Calls</text>
170 <polygon points="560.16,56.16 571.68,51.84 571.68,60.48" style="fill:rgb(0,0,0)"/>
171 <polygon points="758.16,56.16 746.64,60.48 746.64,51.84" style="fill:rgb(0,0,0)"/>
172 <path d="M565.92,56.16L752.4,56.16" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
173 <text x="659.16" y="37.6056" text-anchor="middle" fill="rgb(0,0,0)" font-size="140%" dominant-baseline="central">DB Engine</text>
174 <text x="659.16" y="74.7144" text-anchor="middle" fill="rgb(0,0,0)" font-size="140%" dominant-baseline="central">File I/O</text>
175 </svg>
176 </div>
177 </div>
178 <p>
179 The issues arise from the properties and utilization
180 of the two data/control channels between the above three blocks.
181 </p>
182
183 <h2>Channel Traffic Volume</h2>
184 <p>
185 The "API Call" channel carries less information
186 than the "File I/O" channel.
187 API calls to submit queries or specify data modification
188 normally require substantially fewer bits to be passed
189 back and forth than are transferred to/from
190 the database file to store or find the data.
191 Query result retrieval will normally require much more file traffic
192 than API traffic because the data to be returned is rarely
193 to be found without reading unrequested data.
194 </p>
195 <h2>Channel Bandwidth</h2>
196 <p>
197 The API Call channel operates at processor main memory speeds
198 (Giga-words/second), with data often passed by reference (and so not copied.)
199 In contrast, even the fastest File I/O channels are slower.
200 They require the data to be copied, usually over a medium
201 requiring bit-serialization. For spinning magnetic media,
202 transfers await platter rotation and head movement, then
203 are limited by spin velocity.
204 </p>
205 <p>
206 When the File I/O channel includes a network connection,
207 (in addition to some genuine File I/O at its far end),
208 additional slowness is imposed. Even where raw transfer
209 rate does not limit bandwidth, the traffic must still be
210 packetized and buffered at both ends.
211 Additional layers of I/O handlers add scheduling delays.
212 However, slowed transfers are the least significant
213 issue with network filesystems.
214 </p>
215 <h2>Channel Reliability</h2>
216 <p>
217 The "API Call" channel is highly reliable, to the extent
218 that error rates are unstated and ignored as negligible.
219 The channel fails only when the system loses power
220 (excepting meteorites, etc.)
221 </p>
222 <p>
223 The "File I/O" channel, when it directly reaches a local storage device,
224 is also highly reliable.
225 (Spinning storage MTBF exceeds 1 million hours,
226 and NVRAM lasts longer.)
227 Local devices also have a characteristic
228 which is critical for enabling database management software
229 to be designed to ensure <a href="transactional.html">ACID</a> behavior:
230 When all process writes to the device have completed,
231 (when POSIX fsync() or Windows FlushFileBuffers() calls return),
232 the filesystem then either has
233 stored the "written" data or will do so
234 before storing any subsequently written data.
235 </p>
236 <p>
237 When network filesystem apparatus and software layers are interposed
238 between filesystem clients and a filesystem over an actual storage device,
239 significant sources of failure and misbehavior are introduced.
240 While network data transfers are error-checked well, transfer packets
241 do not all reliably arrive at their destination once sent.
242 Some packets are clobbered by other packets and must be resent.
243 Under packet clobbering conditions, repeated retries
244 can impose delays exceeding
245 what is needed for similar data to reach local storage.
246 Some portions of what a client writes can end up stored
247 out of time order relative to other portions written.
248 </p>
249 <p>
250 Because of the disordering and outright data loss
251 which occur in network filesystem writes, it is critical
252 that sets of file writes can be accurately known to be done
253 before a subsequent set of file writes begins.
254 This assurance is obtained by use of robustly designed
255 and correctly implemented fsync() (or equivalent) OS functions.
256 Unfortunately for some applications, network filesystem sync
257 operation can be less robust than local filesystem sync.
258 Attaining robust sync in the face of network packet transport errors
259 is hard, and safeguards are sometimes relaxed in favor of performance.
260 </p>
261 <p>
262 A similar hazard arises with file locking in network filesystems.
263 SQLite relies on exclusive locks for write operations, and those have
264 been known to operate incorrectly for some network filesystems. This
265 has led to database corruption. That may happen again as the designers
266 of such change their implementation to suit more common use cases.
267 </p>
268 <p>
269 The bottom line is that network filesystem sync and locking reliability
270 vary among implementations and installations. The design
271 assumptions upon which it relies may hold more true where
272 an application is tested than where it is relied upon.
273 <b>Rely upon it at your (and your customers') peril.</b>
274 See <a href="lockingv3.html#how_to_corrupt">How To Corrupt Your Database Files</a>.
275 </p>
276
277 <h1>Performance and Reliability Issues</h1>
278 <p>
279 From the above diagram and discussion, it is obvious that
280 performance (aka "speed") is degraded by insertion
281 of a network link into one of the two channels.
282 Consideration of relative traffic volumes between
283 the API Call channel and the File I/O channel
284 reveals that such insertion will have less performance
285 impact at the API Call channel.
286 </p>
287 <p>
288 Consideration of reliability impact is easier, with a clearer outcome:
289 Inserting a network link into the API Call channel may also result
290 in call failures at times. But if the Client Application
291 has bothered to use SQL/SQLite transactions properly,
292 such failures will only cause a transaction to fail
293 and be rolled back, without compromising the integrity
294 of the data. In contrast, if the network link is
295 inserted into the File I/O channel, transactions may fail
296 (as for the API Call insertion) but with the additional
297 effect that the remote database is corrupted.
298 </p>
299 <p>
300 These network unreliability issues can be mitigated,
301 completely or to an acceptable degree,
302 by using SQLite in rollback mode.
303 However, the SQLite library is not tested in across-a-network
304 scenarios, nor is that reasonably possible.
305 Hence, use of a remote database is done <b>at the user's risk</b>.
306 </p>
307
308 <h1>Recommendations</h1>
309 <p>
310 Generally, if your data is separated from the application
311 by a network, you want to use a client/server database.
312 This is due to the fact that the database engine acts
313 as a bandwidth-reducing filter on the database traffic.
314 </p><p>
315 If your data is separated from the application by a network,
316 you want the low-traffic link to be across the network,
317 not the high-traffic link. This means that the database engine
318 needs to be on the same machine as the database itself.
319 Such is the case with a client/server database like PostgreSQL.
320 SQLite is different in that the database engine runs on
321 the same machine as the application, which forces the
322 higher-traffic link to traverse the network in remote
323 database scenarios. That normally results in lower performance.
324 </p><p>
325 Network filesystems do not support the ability to do
326 simultaneous reads and writes while at the same time
327 keeping the database consistent.
328 So if you have multiple clients on multiple different
329 machines which need to do simultaneous database
330 reads and writes, you have these choices:
331 </p><p>
332 1. Use a client/server database engine.
333 <a href=https://postgresql.org/>PostgreSQL</a>
334 is an excellent choice. A variation of this is:
335 </p><p>
336 2. Host an SQLite database in <a href="wal.html">WAL mode</a>, but do
337 all reads and writes from processes on the same machine
338 that stores the database file.
339 Implement a proxy that runs on the database machine that
340 relays read/write requests from remote machines.
341 </p><p>
342 3. Use SQLite in <a href="isolation.html">rollback mode</a>.
343 This means you can have multiple simultaneous readers or one writer,
344 but not simultaneous readers and writers.
345 </p><p>
346 Application programmers should be cognizant of the possibility
347 that their application's users will elect to use a remote database
348 if they can do so. Unless one of the above choices
349 has been effected, or one at a time, exclusive access is used,
350 a programmer should consider blocking that
351 election unless reliability is of little importance.
352 </p>
353 <h1>Summary</h1>
354 <p>
355 Choose the technology that is right for you and your customers.
356 If your data lives on a different machine from your application,
357 then you should consider a client/server database.
358 SQLite is designed for situations where the data and application
359 coexist on the same machine.
360 SQLite can still be made to work in many remote database
361 situations, but a client/server solution will usually work
362 better in that scenario.
363 </p>
364 <p align="center"><small><i>This page was last updated on 2022-06-22 21:14:29Z </small></i></p>
365