Seeing this discussion about SQLite3 performance has finally
prompted me to share an observation that I made about a year ago,
when building bacula together with sqlite-3.3.6 for personal use
on my home machine.
I believe that the "default_synchronous" PRAGMA that Kern
mentions in his reply has not been included in sqlite 3.*
The "PRAGMA synchronous = ..." pragma still exists, but its
effect does not persist. I.e., you need to re-issue the pragma
every time you connect to the database, rather than specifying it
once when creating the database.
So I put together a patch that adds to bacula's configure script
an option to specify a snippet of SQL code to execute on every
database connection, like this:
configure ... --enable-extra-sqlite3-init="pragma synchronous=0;" ...
with the result that bacula-dir is compiled to issue that SQL
pragma every time it connects to the database.
Since you can get the same effect by using an "sqlquery" in
bconsole to issue the pragma before running the job manually, it
could be that the proper way to accomplish what I did is by using
the python interpreter (which I have never tried using) to script
the sqlquery into every job, instead of by modifying the
bacula-dir binary.
My experience is that
PRAGMA synchronous = 0
greatly improves performance, although turning off synchronous
writes completely like this demands either a reliable UPS or the
willingness to rebuild your catalog should the power fail in the
middle of a backup. "PRAGMA synchronous = 1" ("NORMAL") doesn't
help nearly as much, at least not on my machine. (The default
value for synchronous is 2, "FULL").
I'll try to attach my patch, but I made it against the 1.38.11 sources,
so it won't be directly applicable to the current
development sources (and it includes some changes that aren't
strictly necessary to do the sqlite initialization, but which
reduced the warnings I was getting from the autoconf/automake
tool chain).
>>>>> "kern" == Kern Sibbald <[EMAIL PROTECTED]> writes:
>>
>>
>>
>> Is anyone out there using SQLite3? Does anyone have any suggestions on
how
>> we could change the default build defines or database creation scripts to
>> improve the performance?
>>
>>
>>
>> If there are any suggestions I'd appreciate getting them quickly so that
we
>> can incorporate them into 1.40.
kern> When SQLite3 was first released, I looked into this and seems to me
that he
kern> handled synchronization differently. The two pragmas that I saw
(long ago)
kern> that made a difference were:
kern> PRAGMA default_synchronous
kern> PRAGMA default_cache_size
kern>
-------------------------------------------------------------------------
kern> Take Surveys. Earn Cash. Influence the Future of IT
kern> Join SourceForge.net's Techsay panel and you'll get the chance to
share your
kern> opinions on IT & business topics through brief surveys - and earn cash
kern>
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
kern> _______________________________________________
kern> Bacula-devel mailing list
kern> [EMAIL PROTECTED]
kern> https://lists.sourceforge.net/lists/listinfo/bacula-devel
diff -Naur bacula-1.38.11/autoconf/bacula-macros/db.m4
kludged-bacula-1.38.11/autoconf/bacula-macros/db.m4
--- bacula-1.38.11/autoconf/bacula-macros/db.m4 2006-05-02 08:48:07.000000000
-0600
+++ kludged-bacula-1.38.11/autoconf/bacula-macros/db.m4 2006-07-06
20:03:47.000000000 -0600
@@ -1,4 +1,4 @@
-AC_DEFUN(BA_CHECK_MYSQL_DB,
+AC_DEFUN([BA_CHECK_MYSQL_DB],
[
db_found=no
AC_MSG_CHECKING(for MySQL support)
@@ -142,7 +142,7 @@
])
-AC_DEFUN(BA_CHECK_SQLITE_DB,
+AC_DEFUN([BA_CHECK_SQLITE_DB],
[
db_found=no
AC_MSG_CHECKING(for SQLite support)
@@ -207,7 +207,7 @@
])
-AC_DEFUN(BA_CHECK_SQLITE3_DB,
+AC_DEFUN([BA_CHECK_SQLITE3_DB],
[
db_found=no
AC_MSG_CHECKING(for SQLite3 support)
@@ -274,7 +274,32 @@
-AC_DEFUN(BA_CHECK_POSTGRESQL_DB,
+AC_DEFUN([BA_CHECK_EXTRA_SQLITE3_INIT],
+[
+AC_ARG_ENABLE([extra_sqlite3_init],
+[
+ --enable-extra-sqlite3-init=SQLQUERY
+ Execute the specified SQL query immediately after
+ connecting to an SQLite3 database
+ (e.g. --enable-extra-sqlite3-init="PRAGMA
synchronous=1"),],
+[
+ if test x"$support_sqlite3" = x"yes" && test x"$enableval" != x""; then
+ # Add C string quotes to the value if they are not already there.
+ case "$enableval" in
+ \"*\") ;;
+ *) enableval="\"$enableval\"" ;;
+ esac
+ AC_DEFINE_UNQUOTED([SQLITE3_EXTRA_INITIALIZATION_QUERY], $enableval, dnl
+[Define to a string containing an SQL query to be executed at
+the outset of every connection to the sqlite3 database.
+E.g. "PRAGMA synchronous=1".])
+ fi
+],[])
+])
+
+
+
+AC_DEFUN([BA_CHECK_POSTGRESQL_DB],
[
db_found=no
AC_MSG_CHECKING(for PostgreSQL support)
@@ -350,7 +375,7 @@
-AC_DEFUN(BA_CHECK_SQL_DB,
+AC_DEFUN([BA_CHECK_SQL_DB],
[AC_MSG_CHECKING(Checking for various databases)
dnl# --------------------------------------------------------------------------
dnl# CHECKING FOR VARIOUS DATABASES (thanks to UdmSearch team)
@@ -797,7 +822,7 @@
])
-AC_DEFUN(AM_CONDITIONAL,
+AC_DEFUN([AM_CONDITIONAL],
[AC_SUBST($1_TRUE)
AC_SUBST($1_FALSE)
if $2; then
diff -Naur bacula-1.38.11/autoconf/config.h.in
kludged-bacula-1.38.11/autoconf/config.h.in
--- bacula-1.38.11/autoconf/config.h.in 2006-06-04 06:24:31.000000000 -0600
+++ kludged-bacula-1.38.11/autoconf/config.h.in 2006-07-06 21:22:07.000000000
-0600
@@ -779,6 +782,10 @@
it. */
#undef SIZE_MAX
+/* Define to a string containing an SQL query to be executed at the outset of
+ every connection to the sqlite3 database. E.g. "PRAGMA synchronous=1". */
+#undef SQLITE3_EXTRA_INITIALIZATION_QUERY
+
/* If using the C implementation of alloca, define if you know the
direction of stack growth for your system; otherwise it will be
automatically deduced at run-time.
diff -Naur bacula-1.38.11/autoconf/configure.in
kludged-bacula-1.38.11/autoconf/configure.in
--- bacula-1.38.11/autoconf/configure.in 2006-06-04 06:24:31.000000000
-0600
+++ kludged-bacula-1.38.11/autoconf/configure.in 2006-07-06
20:33:19.000000000 -0600
@@ -683,6 +683,11 @@
[ --with-tcp-wrappers=DIR enable tcpwrappers support],
[
if test "x$withval" != "xno" ; then
+ if test "x$withval" != "xyes" ; then
+ # Pay attention to the path (this lets us avoid
+ # the dynamically linked libwrap.so provided by IRIX).
+ WRAPLIBS="-L$withval"
+ fi
saved_LIBS="$LIBS"
LIBS="$saved_LIBS -lwrap"
AC_MSG_CHECKING(for libwrap)
@@ -699,10 +704,10 @@
AC_DEFINE(HAVE_LIBWRAP)
TCPW_MSG="yes"
LIBS="$saved_LIBS"
- WRAPLIBS="-lwrap"
- ], [
+ WRAPLIBS="$WRAPLIBS -lwrap"
+ ],[
LIBS="$saved_LIBS -lwrap -lnsl"
- WRAPLIBS="$saved_LIBS -lwrap -lnsl"
+ WRAPLIBS="$saved_LIBS $WRAPLIBS -lwrap -lnsl"
AC_TRY_LINK(
[ #include <sys/types.h>
#include <tcpd.h>
@@ -1138,6 +1143,8 @@
BA_CHECK_SQLITE3_DB
+BA_CHECK_EXTRA_SQLITE3_INIT
+
BA_CHECK_SQLITE_DB
AC_SUBST(cats)
diff -Naur bacula-1.38.11/configure kludged-bacula-1.38.11/configure
--- bacula-1.38.11/configure 2006-06-28 14:39:19.000000000 -0600
+++ kludged-bacula-1.38.11/configure 2006-07-06 21:22:24.000000000 -0600
@@ -882,6 +882,11 @@
--disable-readline disable readline support disable
+
+ --enable-extra-sqlite3-init=SQLQUERY
+ Execute the specified SQL query immediately after
+ connecting to an SQLite3 database
+ (e.g. --enable-extra-sqlite3-init="PRAGMA
synchronous=1"),
--disable-largefile omit support for large files
Optional Packages:
@@ -16974,6 +16870,27 @@
+# Check whether --enable-extra_sqlite3_init or --disable-extra_sqlite3_init
was given.
+if test "${enable_extra_sqlite3_init+set}" = set; then
+ enableval="$enable_extra_sqlite3_init"
+
+ if test x"$support_sqlite3" = x"yes" && test x"$enableval" != x""; then
+ # Add C string quotes to the value if they are not already there.
+ case "$enableval" in
+ \"*\") ;;
+ *) enableval="\"$enableval\"" ;;
+ esac
+
+cat >>confdefs.h <<_ACEOF
+#define SQLITE3_EXTRA_INITIALIZATION_QUERY $enableval
+_ACEOF
+
+ fi
+
+fi;
+
+
+
db_found=no
echo "$as_me:$LINENO: checking for SQLite support" >&5
echo $ECHO_N "checking for SQLite support... $ECHO_C" >&6
diff -Naur bacula-1.38.11/src/cats/sqlite.c
kludged-bacula-1.38.11/src/cats/sqlite.c
--- bacula-1.38.11/src/cats/sqlite.c 2005-05-07 11:21:58.000000000 -0600
+++ kludged-bacula-1.38.11/src/cats/sqlite.c 2006-07-06 20:40:38.000000000
-0600
@@ -97,6 +97,10 @@
return mdb;
}
+#ifdef SQLITE3_EXTRA_INITIALIZATION_QUERY
+static void exec_sqlite3_pragma(B_DB *mdb);
+#endif /* SQLITE3_EXTRA_INITIALIZATION_QUERY */
+
/*
* Now actually open the database. This can generate errors,
* which are returned in the errmsg
@@ -145,6 +149,9 @@
if (stat != SQLITE_OK) {
mdb->sqlite_errmsg = (char *)sqlite3_errmsg(mdb->db);
} else {
+#ifdef SQLITE3_EXTRA_INITIALIZATION_QUERY
+ exec_sqlite3_pragma(mdb);
+#endif /* SQLITE3_EXTRA_INITIALIZATION_QUERY */
mdb->sqlite_errmsg = NULL;
}
@@ -298,6 +305,42 @@
return 0;
}
+#if defined(HAVE_SQLITE3) && defined(SQLITE3_EXTRA_INITIALIZATION_QUERY)
+/*
+ * Execute any extra initialization required for Sqlite3; the intended
+ * use is for executing the "synchronous" pragma required to adjust the
+ * trade-off between safety and speed in sqlite3 (the setting is not
+ * persistent, so it needs to be re-executed every time the database
+ * is opened).
+ *
+ * Pragma failures should not be fatal, so we report
+ * errors for logging, but otherwise ignore them.
+ */
+static void exec_sqlite3_pragma(B_DB *mdb)
+{
+ const char *query = SQLITE3_EXTRA_INITIALIZATION_QUERY;
+ struct rh_data rh_data;
+ int stat;
+ rh_data.result_handler = NULL;
+ rh_data.ctx = NULL;
+
+ /* So long as we are only called from db_open_database(), sqlite_errmsg
+ * should never be non-NULL, but just in case ... */
+ if (mdb->sqlite_errmsg) {
+ sqlite3_free(mdb->sqlite_errmsg);
+ mdb->sqlite_errmsg = NULL;
+ }
+
+ stat = sqlite_exec(mdb->db, query, sqlite_result, (void *)&rh_data,
&mdb->sqlite_errmsg);
+ if (stat != SQLITE_OK) {
+ Mmsg(mdb->errmsg, _("Query failed: %s: ERR=%s\n"), query,
sql_strerror(mdb));
+ Emsg0(M_WARNING, 0, mdb->errmsg);
+ sqlite3_free(mdb->sqlite_errmsg);
+ mdb->sqlite_errmsg = NULL;
+ }
+}
+#endif /* HAVE_SQLITE3 && SQLITE3_EXTRA_INITIALIZATION_QUERY */
+
/*
* Submit a general SQL command (cmd), and for each row returned,
* the sqlite_handler is called with the ctx.
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys - and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Bacula-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/bacula-users
