2 Re: [Bacula-users] [Bacula-devel] What a difference a database makes
5 Thu, 28 Dec 2006 21:20:56 -0800
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
12 I believe that the "default_synchronous" PRAGMA that Kern
13 mentions in his reply has not been included in sqlite 3.*
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.
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:
24 configure ... --enable-extra-sqlite3-init="pragma synchronous=0;" ...
26 with the result that bacula-dir is compiled to issue that SQL
27 pragma every time it connects to the database.
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
38 PRAGMA synchronous = 0
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").
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
55 >>>>> "kern" == Kern Sibbald <[EMAIL PROTECTED]> writes:
59 >> Is anyone out there using SQLite3? Does anyone have any suggestions on
61 >> we could change the default build defines or database creation scripts to
62 >> improve the performance?
66 >> If there are any suggestions I'd appreciate getting them quickly so that
68 >> can incorporate them into 1.40.
70 kern> When SQLite3 was first released, I looked into this and seems to me
72 kern> handled synchronization differently. The two pragmas that I saw
74 kern> that made a difference were:
76 kern> PRAGMA default_synchronous
77 kern> PRAGMA default_cache_size
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
85 kern> opinions on IT & business topics through brief surveys - and earn cash
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
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
98 -AC_DEFUN(BA_CHECK_MYSQL_DB,
99 +AC_DEFUN([BA_CHECK_MYSQL_DB],
102 AC_MSG_CHECKING(for MySQL support)
107 -AC_DEFUN(BA_CHECK_SQLITE_DB,
108 +AC_DEFUN([BA_CHECK_SQLITE_DB],
111 AC_MSG_CHECKING(for SQLite support)
116 -AC_DEFUN(BA_CHECK_SQLITE3_DB,
117 +AC_DEFUN([BA_CHECK_SQLITE3_DB],
120 AC_MSG_CHECKING(for SQLite3 support)
125 -AC_DEFUN(BA_CHECK_POSTGRESQL_DB,
126 +AC_DEFUN([BA_CHECK_EXTRA_SQLITE3_INIT],
128 +AC_ARG_ENABLE([extra_sqlite3_init],
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"),],
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
139 + *) enableval="\"$enableval\"" ;;
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".])
151 +AC_DEFUN([BA_CHECK_POSTGRESQL_DB],
154 AC_MSG_CHECKING(for PostgreSQL support)
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)
168 -AC_DEFUN(AM_CONDITIONAL,
169 +AC_DEFUN([AM_CONDITIONAL],
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
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
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 @@
195 +BA_CHECK_EXTRA_SQLITE3_INIT
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
205 --disable-readline disable readline support disable
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
215 @@ -16974,6 +16870,27 @@
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"
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
227 + *) enableval="\"$enableval\"" ;;
230 +cat >>confdefs.h <<_ACEOF
231 +#define SQLITE3_EXTRA_INITIALIZATION_QUERY $enableval
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
251 +#ifdef SQLITE3_EXTRA_INITIALIZATION_QUERY
252 +static void exec_sqlite3_pragma(B_DB *mdb);
253 +#endif /* SQLITE3_EXTRA_INITIALIZATION_QUERY */
256 * Now actually open the database. This can generate errors,
257 * which are returned in the errmsg
259 if (stat != SQLITE_OK) {
260 mdb->sqlite_errmsg = (char *)sqlite3_errmsg(mdb->db);
262 +#ifdef SQLITE3_EXTRA_INITIALIZATION_QUERY
263 + exec_sqlite3_pragma(mdb);
264 +#endif /* SQLITE3_EXTRA_INITIALIZATION_QUERY */
265 mdb->sqlite_errmsg = NULL;
272 +#if defined(HAVE_SQLITE3) && defined(SQLITE3_EXTRA_INITIALIZATION_QUERY)
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
280 + * Pragma failures should not be fatal, so we report
281 + * errors for logging, but otherwise ignore them.
283 +static void exec_sqlite3_pragma(B_DB *mdb)
285 + const char *query = SQLITE3_EXTRA_INITIALIZATION_QUERY;
286 + struct rh_data rh_data;
288 + rh_data.result_handler = NULL;
289 + rh_data.ctx = NULL;
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;
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;
306 +#endif /* HAVE_SQLITE3 && SQLITE3_EXTRA_INITIALIZATION_QUERY */
309 * Submit a general SQL command (cmd), and for each row returned,
310 * the sqlite_handler is called with the ctx.
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
318 _______________________________________________
319 Bacula-users mailing list
320 Bacula-users@lists.sourceforge.net
321 https://lists.sourceforge.net/lists/listinfo/bacula-users