+++ /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