]>
Commit | Line | Data |
---|---|---|
0aff5adb JK |
1 | |
2 | Re: [Bacula-users] [Bacula-devel] What a difference a database makes | |
3 | ||
4 | John Jorgensen | |
5 | Thu, 28 Dec 2006 21:20:56 -0800 | |
6 | ||
7 | Seeing this discussion about SQLite3 performance has finally | |
8 | prompted me to share an observation that I made about a year ago, | |
9 | when building bacula together with sqlite-3.3.6 for personal use | |
10 | on my home machine. | |
11 | ||
12 | I believe that the "default_synchronous" PRAGMA that Kern | |
13 | mentions in his reply has not been included in sqlite 3.* | |
14 | ||
15 | The "PRAGMA synchronous = ..." pragma still exists, but its | |
16 | effect does not persist. I.e., you need to re-issue the pragma | |
17 | every time you connect to the database, rather than specifying it | |
18 | once when creating the database. | |
19 | ||
20 | So I put together a patch that adds to bacula's configure script | |
21 | an option to specify a snippet of SQL code to execute on every | |
22 | database connection, like this: | |
23 | ||
24 | configure ... --enable-extra-sqlite3-init="pragma synchronous=0;" ... | |
25 | ||
26 | with the result that bacula-dir is compiled to issue that SQL | |
27 | pragma every time it connects to the database. | |
28 | ||
29 | Since you can get the same effect by using an "sqlquery" in | |
30 | bconsole to issue the pragma before running the job manually, it | |
31 | could be that the proper way to accomplish what I did is by using | |
32 | the python interpreter (which I have never tried using) to script | |
33 | the sqlquery into every job, instead of by modifying the | |
34 | bacula-dir binary. | |
35 | ||
36 | My experience is that | |
37 | ||
38 | PRAGMA synchronous = 0 | |
39 | ||
40 | greatly improves performance, although turning off synchronous | |
41 | writes completely like this demands either a reliable UPS or the | |
42 | willingness to rebuild your catalog should the power fail in the | |
43 | middle of a backup. "PRAGMA synchronous = 1" ("NORMAL") doesn't | |
44 | help nearly as much, at least not on my machine. (The default | |
45 | value for synchronous is 2, "FULL"). | |
46 | ||
47 | I'll try to attach my patch, but I made it against the 1.38.11 sources, | |
48 | so it won't be directly applicable to the current | |
49 | development sources (and it includes some changes that aren't | |
50 | strictly necessary to do the sqlite initialization, but which | |
51 | reduced the warnings I was getting from the autoconf/automake | |
52 | tool chain). | |
53 | ||
54 | ||
55 | >>>>> "kern" == Kern Sibbald <[EMAIL PROTECTED]> writes: | |
56 | >> | |
57 | >> | |
58 | >> | |
59 | >> Is anyone out there using SQLite3? Does anyone have any suggestions on | |
60 | how | |
61 | >> we could change the default build defines or database creation scripts to | |
62 | >> improve the performance? | |
63 | >> | |
64 | >> | |
65 | >> | |
66 | >> If there are any suggestions I'd appreciate getting them quickly so that | |
67 | we | |
68 | >> can incorporate them into 1.40. | |
69 | ||
70 | kern> When SQLite3 was first released, I looked into this and seems to me | |
71 | that he | |
72 | kern> handled synchronization differently. The two pragmas that I saw | |
73 | (long ago) | |
74 | kern> that made a difference were: | |
75 | ||
76 | kern> PRAGMA default_synchronous | |
77 | kern> PRAGMA default_cache_size | |
78 | ||
79 | ||
80 | kern> | |
81 | ------------------------------------------------------------------------- | |
82 | kern> Take Surveys. Earn Cash. Influence the Future of IT | |
83 | kern> Join SourceForge.net's Techsay panel and you'll get the chance to | |
84 | share your | |
85 | kern> opinions on IT & business topics through brief surveys - and earn cash | |
86 | kern> | |
87 | http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV | |
88 | kern> _______________________________________________ | |
89 | kern> Bacula-devel mailing list | |
90 | kern> [EMAIL PROTECTED] | |
91 | kern> https://lists.sourceforge.net/lists/listinfo/bacula-devel | |
92 | ||
93 | ||
94 | diff -Naur bacula-1.38.11/autoconf/bacula-macros/db.m4 kludged-bacula-1.38.11/autoconf/bacula-macros/db.m4 | |
95 | --- bacula-1.38.11/autoconf/bacula-macros/db.m4 2006-05-02 08:48:07.000000000 -0600 | |
96 | +++ kludged-bacula-1.38.11/autoconf/bacula-macros/db.m4 2006-07-06 20:03:47.000000000 -0600 | |
97 | @@ -1,4 +1,4 @@ | |
98 | -AC_DEFUN(BA_CHECK_MYSQL_DB, | |
99 | +AC_DEFUN([BA_CHECK_MYSQL_DB], | |
100 | [ | |
101 | db_found=no | |
102 | AC_MSG_CHECKING(for MySQL support) | |
103 | @@ -142,7 +142,7 @@ | |
104 | ]) | |
105 | ||
106 | ||
107 | -AC_DEFUN(BA_CHECK_SQLITE_DB, | |
108 | +AC_DEFUN([BA_CHECK_SQLITE_DB], | |
109 | [ | |
110 | db_found=no | |
111 | AC_MSG_CHECKING(for SQLite support) | |
112 | @@ -207,7 +207,7 @@ | |
113 | ||
114 | ]) | |
115 | ||
116 | -AC_DEFUN(BA_CHECK_SQLITE3_DB, | |
117 | +AC_DEFUN([BA_CHECK_SQLITE3_DB], | |
118 | [ | |
119 | db_found=no | |
120 | AC_MSG_CHECKING(for SQLite3 support) | |
121 | @@ -274,7 +274,32 @@ | |
122 | ||
123 | ||
124 | ||
125 | -AC_DEFUN(BA_CHECK_POSTGRESQL_DB, | |
126 | +AC_DEFUN([BA_CHECK_EXTRA_SQLITE3_INIT], | |
127 | +[ | |
128 | +AC_ARG_ENABLE([extra_sqlite3_init], | |
129 | +[ | |
130 | + --enable-extra-sqlite3-init=SQLQUERY | |
131 | + Execute the specified SQL query immediately after | |
132 | + connecting to an SQLite3 database | |
133 | + (e.g. --enable-extra-sqlite3-init="PRAGMA synchronous=1"),], | |
134 | +[ | |
135 | + if test x"$support_sqlite3" = x"yes" && test x"$enableval" != x""; then | |
136 | + # Add C string quotes to the value if they are not already there. | |
137 | + case "$enableval" in | |
138 | + \"*\") ;; | |
139 | + *) enableval="\"$enableval\"" ;; | |
140 | + esac | |
141 | + AC_DEFINE_UNQUOTED([SQLITE3_EXTRA_INITIALIZATION_QUERY], $enableval, dnl | |
142 | +[Define to a string containing an SQL query to be executed at | |
143 | +the outset of every connection to the sqlite3 database. | |
144 | +E.g. "PRAGMA synchronous=1".]) | |
145 | + fi | |
146 | +],[]) | |
147 | +]) | |
148 | + | |
149 | + | |
150 | + | |
151 | +AC_DEFUN([BA_CHECK_POSTGRESQL_DB], | |
152 | [ | |
153 | db_found=no | |
154 | AC_MSG_CHECKING(for PostgreSQL support) | |
155 | @@ -350,7 +375,7 @@ | |
156 | ||
157 | ||
158 | ||
159 | -AC_DEFUN(BA_CHECK_SQL_DB, | |
160 | +AC_DEFUN([BA_CHECK_SQL_DB], | |
161 | [AC_MSG_CHECKING(Checking for various databases) | |
162 | dnl# -------------------------------------------------------------------------- | |
163 | dnl# CHECKING FOR VARIOUS DATABASES (thanks to UdmSearch team) | |
164 | @@ -797,7 +822,7 @@ | |
165 | ]) | |
166 | ||
167 | ||
168 | -AC_DEFUN(AM_CONDITIONAL, | |
169 | +AC_DEFUN([AM_CONDITIONAL], | |
170 | [AC_SUBST($1_TRUE) | |
171 | AC_SUBST($1_FALSE) | |
172 | if $2; then | |
173 | diff -Naur bacula-1.38.11/autoconf/config.h.in kludged-bacula-1.38.11/autoconf/config.h.in | |
174 | --- bacula-1.38.11/autoconf/config.h.in 2006-06-04 06:24:31.000000000 -0600 | |
175 | +++ kludged-bacula-1.38.11/autoconf/config.h.in 2006-07-06 21:22:07.000000000 | |
176 | -0600 | |
177 | @@ -779,6 +782,10 @@ | |
178 | it. */ | |
179 | #undef SIZE_MAX | |
180 | ||
181 | +/* Define to a string containing an SQL query to be executed at the outset of | |
182 | + every connection to the sqlite3 database. E.g. "PRAGMA synchronous=1". */ | |
183 | +#undef SQLITE3_EXTRA_INITIALIZATION_QUERY | |
184 | + | |
185 | /* If using the C implementation of alloca, define if you know the | |
186 | direction of stack growth for your system; otherwise it will be | |
187 | automatically deduced at run-time. | |
188 | diff -Naur bacula-1.38.11/autoconf/configure.in kludged-bacula-1.38.11/autoconf/configure.in | |
189 | --- bacula-1.38.11/autoconf/configure.in 2006-06-04 06:24:31.000000000 -0600 | |
190 | +++ kludged-bacula-1.38.11/autoconf/configure.in 2006-07-06 20:33:19.000000000 -0600 | |
191 | @@ -1138,6 +1143,8 @@ | |
192 | ||
193 | BA_CHECK_SQLITE3_DB | |
194 | ||
195 | +BA_CHECK_EXTRA_SQLITE3_INIT | |
196 | + | |
197 | BA_CHECK_SQLITE_DB | |
198 | ||
199 | AC_SUBST(cats) | |
200 | diff -Naur bacula-1.38.11/configure kludged-bacula-1.38.11/configure | |
201 | --- bacula-1.38.11/configure 2006-06-28 14:39:19.000000000 -0600 | |
202 | +++ kludged-bacula-1.38.11/configure 2006-07-06 21:22:24.000000000 -0600 | |
203 | @@ -882,6 +882,11 @@ | |
204 | ||
205 | --disable-readline disable readline support disable | |
206 | ||
207 | + | |
208 | + --enable-extra-sqlite3-init=SQLQUERY | |
209 | + Execute the specified SQL query immediately after | |
210 | + connecting to an SQLite3 database | |
211 | + (e.g. --enable-extra-sqlite3-init="PRAGMA synchronous=1"), | |
212 | --disable-largefile omit support for large files | |
213 | ||
214 | Optional Packages: | |
215 | @@ -16974,6 +16870,27 @@ | |
216 | ||
217 | ||
218 | ||
219 | +# Check whether --enable-extra_sqlite3_init or --disable-extra_sqlite3_init was given. | |
220 | +if test "${enable_extra_sqlite3_init+set}" = set; then | |
221 | + enableval="$enable_extra_sqlite3_init" | |
222 | + | |
223 | + if test x"$support_sqlite3" = x"yes" && test x"$enableval" != x""; then | |
224 | + # Add C string quotes to the value if they are not already there. | |
225 | + case "$enableval" in | |
226 | + \"*\") ;; | |
227 | + *) enableval="\"$enableval\"" ;; | |
228 | + esac | |
229 | + | |
230 | +cat >>confdefs.h <<_ACEOF | |
231 | +#define SQLITE3_EXTRA_INITIALIZATION_QUERY $enableval | |
232 | +_ACEOF | |
233 | + | |
234 | + fi | |
235 | + | |
236 | +fi; | |
237 | + | |
238 | + | |
239 | + | |
240 | db_found=no | |
241 | echo "$as_me:$LINENO: checking for SQLite support" >&5 | |
242 | echo $ECHO_N "checking for SQLite support... $ECHO_C" >&6 | |
243 | diff -Naur bacula-1.38.11/src/cats/sqlite.c kludged-bacula-1.38.11/src/cats/sqlite.c | |
244 | --- bacula-1.38.11/src/cats/sqlite.c 2005-05-07 11:21:58.000000000 -0600 | |
245 | +++ kludged-bacula-1.38.11/src/cats/sqlite.c 2006-07-06 20:40:38.000000000 | |
246 | -0600 | |
247 | @@ -97,6 +97,10 @@ | |
248 | return mdb; | |
249 | } | |
250 | ||
251 | +#ifdef SQLITE3_EXTRA_INITIALIZATION_QUERY | |
252 | +static void exec_sqlite3_pragma(B_DB *mdb); | |
253 | +#endif /* SQLITE3_EXTRA_INITIALIZATION_QUERY */ | |
254 | + | |
255 | /* | |
256 | * Now actually open the database. This can generate errors, | |
257 | * which are returned in the errmsg | |
258 | @@ -145,6 +149,9 @@ | |
259 | if (stat != SQLITE_OK) { | |
260 | mdb->sqlite_errmsg = (char *)sqlite3_errmsg(mdb->db); | |
261 | } else { | |
262 | +#ifdef SQLITE3_EXTRA_INITIALIZATION_QUERY | |
263 | + exec_sqlite3_pragma(mdb); | |
264 | +#endif /* SQLITE3_EXTRA_INITIALIZATION_QUERY */ | |
265 | mdb->sqlite_errmsg = NULL; | |
266 | } | |
267 | ||
268 | @@ -298,6 +305,42 @@ | |
269 | return 0; | |
270 | } | |
271 | ||
272 | +#if defined(HAVE_SQLITE3) && defined(SQLITE3_EXTRA_INITIALIZATION_QUERY) | |
273 | +/* | |
274 | + * Execute any extra initialization required for Sqlite3; the intended | |
275 | + * use is for executing the "synchronous" pragma required to adjust the | |
276 | + * trade-off between safety and speed in sqlite3 (the setting is not | |
277 | + * persistent, so it needs to be re-executed every time the database | |
278 | + * is opened). | |
279 | + * | |
280 | + * Pragma failures should not be fatal, so we report | |
281 | + * errors for logging, but otherwise ignore them. | |
282 | + */ | |
283 | +static void exec_sqlite3_pragma(B_DB *mdb) | |
284 | +{ | |
285 | + const char *query = SQLITE3_EXTRA_INITIALIZATION_QUERY; | |
286 | + struct rh_data rh_data; | |
287 | + int stat; | |
288 | + rh_data.result_handler = NULL; | |
289 | + rh_data.ctx = NULL; | |
290 | + | |
291 | + /* So long as we are only called from db_open_database(), sqlite_errmsg | |
292 | + * should never be non-NULL, but just in case ... */ | |
293 | + if (mdb->sqlite_errmsg) { | |
294 | + sqlite3_free(mdb->sqlite_errmsg); | |
295 | + mdb->sqlite_errmsg = NULL; | |
296 | + } | |
297 | + | |
298 | + stat = sqlite_exec(mdb->db, query, sqlite_result, (void *)&rh_data, &mdb->sqlite_errmsg); | |
299 | + if (stat != SQLITE_OK) { | |
300 | + Mmsg(mdb->errmsg, _("Query failed: %s: ERR=%s\n"), query, sql_strerror(mdb)); | |
301 | + Emsg0(M_WARNING, 0, mdb->errmsg); | |
302 | + sqlite3_free(mdb->sqlite_errmsg); | |
303 | + mdb->sqlite_errmsg = NULL; | |
304 | + } | |
305 | +} | |
306 | +#endif /* HAVE_SQLITE3 && SQLITE3_EXTRA_INITIALIZATION_QUERY */ | |
307 | + | |
308 | /* | |
309 | * Submit a general SQL command (cmd), and for each row returned, | |
310 | * the sqlite_handler is called with the ctx. | |
311 | ||
312 | ------------------------------------------------------------------------- | |
313 | Take Surveys. Earn Cash. Influence the Future of IT | |
314 | Join SourceForge.net's Techsay panel and you'll get the chance to share your | |
315 | opinions on IT & business topics through brief surveys - and earn cash | |
316 | http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV | |
317 | ||
318 | _______________________________________________ | |
319 | Bacula-users mailing list | |
320 | Bacula-users@lists.sourceforge.net | |
321 | https://lists.sourceforge.net/lists/listinfo/bacula-users |