--- /dev/null
+
+Re: [Bacula-users] [Bacula-devel] What a difference a database makes
+
+John Jorgensen
+Thu, 28 Dec 2006 21:20:56 -0800
+
+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
+@@ -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
+Bacula-users@lists.sourceforge.net
+https://lists.sourceforge.net/lists/listinfo/bacula-users
projects / packages / bacula.git / commitdiff