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