Bug 573492 - Use WAL journaling for places.sqlite.
authorMarco Bonardo <mbonardo@mozilla.com>, Shawn Wilsher <me@shawnwilsher.com>
Mon, 13 Sep 2010 09:29:42 -0700
changeset 59295 31c960a57b46dfc85997337b023d5373b793f9b1
parent 55137 1354806a3b6ccf4a4a9ff244fde14e457fb54cd2
child 59296 676388cb7893ec013b5312873f97c2abd6f76daf
push idunknown
push userunknown
push dateunknown
bugs573492
milestone2.0b8pre
Bug 573492 - Use WAL journaling for places.sqlite. r=sdwilsh r=mak a=blocking2.0
storage/public/Makefile.in
storage/public/storage.h
toolkit/components/places/src/Makefile.in
toolkit/components/places/src/nsNavHistory.cpp
toolkit/components/places/src/nsNavHistory.h
--- a/storage/public/Makefile.in
+++ b/storage/public/Makefile.in
@@ -43,16 +43,18 @@ srcdir    = @srcdir@
 VPATH   = @srcdir@
 
 include $(DEPTH)/config/autoconf.mk
 
 MODULE       = storage
 XPIDL_MODULE = storage
 GRE_MODULE   = 1
 
+# NOTE When adding something to this list, you probably need to add it to the
+#      storage.h file too.
 XPIDLSRCS = \
 	mozIStorageService.idl \
 	mozIStorageConnection.idl \
 	mozIStorageAggregateFunction.idl \
 	mozIStorageFunction.idl \
 	mozIStorageProgressHandler.idl \
 	mozIStorageStatement.idl \
 	mozIStorageStatementWrapper.idl \
@@ -64,16 +66,17 @@ XPIDLSRCS = \
   mozIStoragePendingStatement.idl \
   mozIStorageBindingParamsArray.idl \
   mozIStorageBindingParams.idl \
   mozIStorageCompletionCallback.idl \
   mozIStorageBaseStatement.idl \
   mozIStorageAsyncStatement.idl \
   mozIStorageServiceQuotaManagement.idl \
 	$(NULL)
+# SEE ABOVE NOTE!
 
 EXPORTS_NAMESPACES = mozilla
 
 EXPORTS = \
 	mozStorageHelper.h \
 	mozStorage.h \
 	$(NULL)
 
--- a/storage/public/storage.h
+++ b/storage/public/storage.h
@@ -53,16 +53,18 @@
 #include "mozIStorageResultSet.h"
 #include "mozIStorageRow.h"
 #include "mozIStorageService.h"
 #include "mozIStorageStatement.h"
 #include "mozIStorageStatementCallback.h"
 #include "mozIStorageBindingParamsArray.h"
 #include "mozIStorageBindingParams.h"
 #include "mozIStorageServiceQuotaManagement.h"
+#include "mozIStorageCompletionCallback.h"
+#include "mozIStorageAsyncStatement.h"
 
 ////////////////////////////////////////////////////////////////////////////////
 //// Native Language Helpers
 
 #include "mozStorageHelper.h"
 
 #include "mozilla/storage/Variant.h"
 
--- a/toolkit/components/places/src/Makefile.in
+++ b/toolkit/components/places/src/Makefile.in
@@ -77,16 +77,19 @@ CPPSRCS = \
 EXTRA_DSO_LDOPTS += \
 	$(DEPTH)/db/morkreader/$(LIB_PREFIX)morkreader_s.$(LIB_SUFFIX) \
 	$(MOZ_UNICHARUTIL_LIBS) \
 	$(MOZ_COMPONENT_LIBS) \
 	$(NULL)
 
 LOCAL_INCLUDES += -I$(srcdir)/../../build
 
+# This is the default value.  Must be in sync with the one defined in SQLite.
+DEFINES += -DSQLITE_DEFAULT_PAGE_SIZE=32768
+
 EXTRA_COMPONENTS = \
   toolkitplaces.manifest \
   nsLivemarkService.js \
   nsTaggingService.js \
   nsPlacesDBFlush.js \
   nsPlacesExpiration.js \
   nsMicrosummaryService.js \
   $(NULL)
--- a/toolkit/components/places/src/nsNavHistory.cpp
+++ b/toolkit/components/places/src/nsNavHistory.cpp
@@ -60,17 +60,16 @@
 #include "prsystem.h"
 #include "prtime.h"
 #include "nsEscape.h"
 #include "nsIEffectiveTLDService.h"
 #include "nsIClassInfoImpl.h"
 #include "nsThreadUtils.h"
 #include "nsAppDirectoryServiceDefs.h"
 #include "nsMathUtils.h"
-#include "mozIStorageCompletionCallback.h"
 
 #include "nsNavBookmarks.h"
 #include "nsAnnotationService.h"
 #include "nsILivemarkService.h"
 #include "nsFaviconService.h"
 
 #include "nsPlacesTables.h"
 #include "nsPlacesIndexes.h"
@@ -137,20 +136,16 @@ using namespace mozilla::places;
 #define DATABASE_SCHEMA_VERSION 10
 
 // Filename of the database.
 #define DATABASE_FILENAME NS_LITERAL_STRING("places.sqlite")
 
 // Filename used to backup corrupt databases.
 #define DATABASE_CORRUPT_FILENAME NS_LITERAL_STRING("places.sqlite.corrupt")
 
-// We use the TRUNCATE journal mode to reduce the number of fsyncs.  Without
-// this setting we had a Ts hit on Linux.  See bug 460315 for details.
-#define DATABASE_JOURNAL_MODE "TRUNCATE"
-
 // Fraction of free pages in the database to force a vacuum between
 // DATABASE_MAX_TIME_BEFORE_VACUUM and DATABASE_MIN_TIME_BEFORE_VACUUM.
 #define DATABASE_VACUUM_FREEPAGES_THRESHOLD 0.1
 // This is the maximum time (in microseconds) that can pass between 2 VACUUM
 // operations.
 #define DATABASE_MAX_TIME_BEFORE_VACUUM (PRInt64)60 * 24 * 60 * 60 * 1000 * 1000
 // This is the minimum time (in microseconds) that should pass between 2 VACUUM
 // operations.
@@ -420,16 +415,18 @@ const PRInt32 nsNavHistory::kGetInfoInde
 
 
 PLACES_FACTORY_SINGLETON_IMPLEMENTATION(nsNavHistory, gHistoryService)
 
 
 nsNavHistory::nsNavHistory()
 : mBatchLevel(0)
 , mBatchDBTransaction(nsnull)
+, mDBPageSize(0)
+, mCurrentJournalMode(JOURNAL_DELETE)
 , mCachedNow(0)
 , mExpireNowTimer(nsnull)
 , mLastSessionID(0)
 , mHistoryEnabled(PR_TRUE)
 , mNumVisitsForFrecency(10)
 , mTagsFolder(-1)
 , mInPrivateBrowsing(PRIVATEBROWSING_NOTINITED)
 , mDatabaseStatus(DATABASE_STATUS_OK)
@@ -665,35 +662,89 @@ nsNavHistory::InitDBFile(PRBool aForceIn
   }
   NS_ENSURE_SUCCESS(rv, rv);
 
   return NS_OK;
 }
 
 
 nsresult
+nsNavHistory::SetJournalMode(enum JournalMode aJournalMode)
+{
+  nsCAutoString journalMode;
+  switch (aJournalMode) {
+    default:
+      NS_NOTREACHED("Trying to set an unknown journal mode.");
+      // Fall through to the default mode of DELETE.
+    case JOURNAL_DELETE:
+      journalMode.AssignLiteral("delete");
+      break;
+    case JOURNAL_TRUNCATE:
+      journalMode.AssignLiteral("truncate");
+      break;
+    case JOURNAL_MEMORY:
+      journalMode.AssignLiteral("memory");
+      break;
+    case JOURNAL_WAL:
+      journalMode.AssignLiteral("wal");
+      break;
+  }
+
+  nsCOMPtr<mozIStorageStatement> statement;
+  nsresult rv = mDBConn->CreateStatement(NS_LITERAL_CSTRING(
+      "PRAGMA journal_mode = ") + journalMode,
+    getter_AddRefs(statement));
+  NS_ENSURE_SUCCESS(rv, rv);
+
+  mozStorageStatementScoper scoper(statement);
+  PRBool hasResult;
+  rv = statement->ExecuteStep(&hasResult);
+  NS_ENSURE_SUCCESS(rv, rv);
+  NS_ENSURE_TRUE(hasResult, NS_ERROR_FAILURE);
+
+  nsCAutoString currentJournalMode;
+  rv = statement->GetUTF8String(0, currentJournalMode);
+  NS_ENSURE_SUCCESS(rv, rv);
+  bool succeeded = currentJournalMode.Equals(journalMode);
+  if (succeeded) {
+    mCurrentJournalMode = aJournalMode;
+  }
+  else {
+    NS_WARNING(nsPrintfCString(128, "Setting journal mode failed: %s",
+                               PromiseFlatCString(journalMode).get()).get());
+    return NS_ERROR_FAILURE;
+  }
+
+  return NS_OK;
+}
+
+
+nsresult
 nsNavHistory::InitDB()
 {
   // Get the database schema version.
   PRInt32 currentSchemaVersion = 0;
   nsresult rv = mDBConn->GetSchemaVersion(&currentSchemaVersion);
   NS_ENSURE_SUCCESS(rv, rv);
-
-  // Get the page size.  This may be different than the default if the
-  // database file already existed with a different page size.
-  nsCOMPtr<mozIStorageStatement> statement;
-  rv = mDBConn->CreateStatement(NS_LITERAL_CSTRING("PRAGMA page_size"),
-                                getter_AddRefs(statement));
-  NS_ENSURE_SUCCESS(rv, rv);
-
-  PRBool hasResult;
-  mozStorageStatementScoper scoper(statement);
-  rv = statement->ExecuteStep(&hasResult);
-  NS_ENSURE_TRUE(NS_SUCCEEDED(rv) && hasResult, NS_ERROR_FAILURE);
-  PRInt32 pageSize = statement->AsInt32(0);
+  {
+    // Get the page size.  This may be different than the default if the
+    // database file already existed with a different page size.
+    nsCOMPtr<mozIStorageStatement> statement;
+    rv = mDBConn->CreateStatement(NS_LITERAL_CSTRING("PRAGMA page_size"),
+                                  getter_AddRefs(statement));
+    NS_ENSURE_SUCCESS(rv, rv);
+
+    PRBool hasResult;
+    mozStorageStatementScoper scoper(statement);
+    rv = statement->ExecuteStep(&hasResult);
+    NS_ENSURE_TRUE(NS_SUCCEEDED(rv) && hasResult, NS_ERROR_FAILURE);
+    rv = statement->GetInt32(0, &mDBPageSize);
+    NS_ENSURE_SUCCESS(rv, rv);
+    NS_ENSURE_TRUE(mDBPageSize > 0, NS_ERROR_UNEXPECTED);
+  }
 
   // Ensure that temp tables are held in memory, not on disk.  We use temp
   // tables mainly for fsync and I/O reduction.
   rv = mDBConn->ExecuteSimpleSQL(NS_LITERAL_CSTRING(
       "PRAGMA temp_store = MEMORY"));
   NS_ENSURE_SUCCESS(rv, rv);
 
   // Set pragma synchronous to FULL to ensure maximum data integrity, even in
@@ -715,31 +766,36 @@ nsNavHistory::InitDB()
     cachePercentage = 50;
   if (cachePercentage < 0)
     cachePercentage = 0;
 
   static PRInt64 physMem = PR_GetPhysicalMemorySize();
   PRInt64 cacheSize = physMem * cachePercentage / 100;
 
   // Compute number of cached pages, this will be our cache size.
-  PRInt64 cachePages = cacheSize / pageSize;
-  nsCAutoString pageSizePragma("PRAGMA cache_size = ");
-  pageSizePragma.AppendInt(cachePages);
-  rv = mDBConn->ExecuteSimpleSQL(pageSizePragma);
+  PRInt64 cachePages = cacheSize / mDBPageSize;
+  nsCAutoString cacheSizePragma("PRAGMA cache_size = ");
+  cacheSizePragma.AppendInt(cachePages);
+  rv = mDBConn->ExecuteSimpleSQL(cacheSizePragma);
   NS_ENSURE_SUCCESS(rv, rv);
 
   // Lock the database file.  This is done partly to avoid third party
   // applications to access it while it's in use, partly for performance.
   rv = mDBConn->ExecuteSimpleSQL(NS_LITERAL_CSTRING(
       "PRAGMA locking_mode = EXCLUSIVE"));
   NS_ENSURE_SUCCESS(rv, rv);
 
-  rv = mDBConn->ExecuteSimpleSQL(NS_LITERAL_CSTRING(
-      "PRAGMA journal_mode = " DATABASE_JOURNAL_MODE));
-  NS_ENSURE_SUCCESS(rv, rv);
+  // Be sure to set journal mode after page_size.  WAL would prevent the change
+  // otherwise.
+  if (NS_FAILED(SetJournalMode(JOURNAL_WAL))) {
+    // Ignore errors, if we fail here the database could be considered corrupt
+    // and we won't be able to go on, even if it's just matter of a bogus file
+    // system.  The default mode (DELETE) will be fine in such a case.
+    (void)SetJournalMode(JOURNAL_TRUNCATE);
+  }
 
   // We are going to initialize tables, so everything from now on should be in
   // a transaction for performances.
   mozStorageTransaction transaction(mDBConn, PR_FALSE);
 
   // Grow places in 10MB increments
   mDBConn->SetGrowthIncrement(10 * 1024 * 1024, EmptyCString());
 
@@ -1823,35 +1879,22 @@ nsNavHistory::MigrateV9Up(mozIStorageCon
 
     rv = mDBConn->ExecuteSimpleSQL(CREATE_IDX_MOZ_PLACES_LASTVISITDATE);
     NS_ENSURE_SUCCESS(rv, rv);
 
     // Now let's sync the column contents with real visit dates.
     // This query can be really slow due to disk access, since it will basically
     // dupe the table contents in the journal file, and then write them down
     // in the database.
-    // We will temporary use a memory journal file, this has the advantage of
-    // reducing write times by a half, but will temporary consume more memory
-    // and increase risks of corruption if we should crash in the middle of this
-    // update.
-    rv = mDBConn->ExecuteSimpleSQL(NS_LITERAL_CSTRING(
-        "PRAGMA journal_mode = MEMORY"));
-    NS_ENSURE_SUCCESS(rv, rv);
-
     rv = mDBConn->ExecuteSimpleSQL(NS_LITERAL_CSTRING(
         "UPDATE moz_places SET last_visit_date = "
           "(SELECT MAX(visit_date) "
            "FROM moz_historyvisits "
            "WHERE place_id = moz_places.id)"));
     NS_ENSURE_SUCCESS(rv, rv);
-
-    // Restore the default journal mode.
-    rv = mDBConn->ExecuteSimpleSQL(NS_LITERAL_CSTRING(
-        "PRAGMA journal_mode = " DATABASE_JOURNAL_MODE));
-    NS_ENSURE_SUCCESS(rv, rv);
   }
 
   return transaction.Commit();
 }
 
 
 nsresult
 nsNavHistory::MigrateV10Up(mozIStorageConnection *aDBConn)
@@ -5923,49 +5966,35 @@ nsNavHistory::VacuumDatabase()
     nsCOMPtr<nsIObserverService> observerService =
       do_GetService(NS_OBSERVERSERVICE_CONTRACTID);
     if (observerService) {
       (void)observerService->NotifyObservers(nsnull,
                                              TOPIC_DATABASE_VACUUM_STARTING,
                                              nsnull);
     }
 
-    // Actually vacuuming a database is a slow operation, since it could take
-    // seconds.  Part of the time is spent in updating the journal file on disk
-    // and this is particularly bad on devices with slow I/O.  Temporary
-    // moving the journal to memory could increase a bit the possibility of
-    // corruption if we crash during this time, but makes the process really
-    // faster.
-    nsCOMPtr<mozIStorageStatement> journalToMemory;
-    rv = mDBConn->CreateStatement(NS_LITERAL_CSTRING(
-        "PRAGMA journal_mode = MEMORY"),
-      getter_AddRefs(journalToMemory));
-    NS_ENSURE_SUCCESS(rv, rv);
-
-    nsCOMPtr<mozIStorageStatement> vacuum;
-    rv = mDBConn->CreateStatement(NS_LITERAL_CSTRING("VACUUM"),
-                                  getter_AddRefs(vacuum));
-    NS_ENSURE_SUCCESS(rv, rv);
-
-    nsCOMPtr<mozIStorageStatement> journalToDefault;
-    rv = mDBConn->CreateStatement(NS_LITERAL_CSTRING(
-        "PRAGMA journal_mode = " DATABASE_JOURNAL_MODE),
-      getter_AddRefs(journalToDefault));
-    NS_ENSURE_SUCCESS(rv, rv);
-
-    mozIStorageBaseStatement *stmts[] = {
-      journalToMemory,
-      vacuum,
-      journalToDefault
-    };
+    // If journal mode is WAL, a VACUUM cannot upgrade page_size value.
+    // If current page_size is not the expected one, journal mode must be
+    // changed to a rollback one.  Once done we won't be able to go back to WAL
+    // mode though, since non-reset statements exist.  Just keep using
+    // compatible mode till next restart.
+    // See http://www.sqlite.org/wal.html
+    if (mCurrentJournalMode == JOURNAL_WAL &&
+        mDBPageSize != SQLITE_DEFAULT_PAGE_SIZE) {
+      (void)SetJournalMode(JOURNAL_TRUNCATE);
+    }
+
+    nsCOMPtr<mozIStorageAsyncStatement> vacuum;
+    rv = mDBConn->CreateAsyncStatement(NS_LITERAL_CSTRING("VACUUM"),
+                                       getter_AddRefs(vacuum));
+    NS_ENSURE_SUCCESS(rv, rv);
     nsCOMPtr<mozIStoragePendingStatement> ps;
     nsRefPtr<VacuumDBListener> vacuumDBListener =
       new VacuumDBListener(mPrefBranch);
-    rv = mDBConn->ExecuteAsync(stmts, NS_ARRAY_LENGTH(stmts),
-                               vacuumDBListener, getter_AddRefs(ps));
+    rv = vacuum->ExecuteAsync(vacuumDBListener, getter_AddRefs(ps));
     NS_ENSURE_SUCCESS(rv, rv);
   }
 
   return NS_OK;
 }
 
 
 nsresult
--- a/toolkit/components/places/src/nsNavHistory.h
+++ b/toolkit/components/places/src/nsNavHistory.h
@@ -127,16 +127,28 @@ namespace places {
   , DB_RECENT_VISIT_OF_URL = 4
   , DB_GET_PAGE_VISIT_STATS = 5
   , DB_UPDATE_PAGE_VISIT_STATS = 6
   , DB_ADD_NEW_PAGE = 7
   , DB_GET_URL_PAGE_INFO = 8
   , DB_SET_PLACE_TITLE = 9
   };
 
+  enum JournalMode {
+    // Default SQLite journal mode.
+    JOURNAL_DELETE = 0
+    // Can reduce fsyncs on Linux when journal is deleted (See bug 460315).
+    // We fallback to this mode when WAL is unavailable.
+  , JOURNAL_TRUNCATE
+    // Unsafe in case of crashes on database swap or low memory.
+  , JOURNAL_MEMORY
+    // Can reduce number of fsyncs.  We try to use this mode by default.
+  , JOURNAL_WAL
+  };
+
 } // namespace places
 } // namespace mozilla
 
 
 class mozIAnnotationService;
 class nsNavHistory;
 class nsNavBookmarks;
 class QueryKeyValuePair;
@@ -474,16 +486,17 @@ protected:
   nsDataHashtable<nsStringHashKey, int> gExpandedItems;
 
   //
   // Database stuff
   //
   nsCOMPtr<mozIStorageService> mDBService;
   nsCOMPtr<mozIStorageConnection> mDBConn;
   nsCOMPtr<nsIFile> mDBFile;
+  PRInt32 mDBPageSize;
 
   nsCOMPtr<mozIStorageStatement> mDBGetURLPageInfo;   // kGetInfoIndex_* results
   nsCOMPtr<mozIStorageStatement> mDBGetIdPageInfo;     // kGetInfoIndex_* results
 
   nsCOMPtr<mozIStorageStatement> mDBRecentVisitOfURL; // converts URL into most recent visit ID/session ID
   nsCOMPtr<mozIStorageStatement> mDBRecentVisitOfPlace; // converts placeID into most recent visit ID/session ID
   nsCOMPtr<mozIStorageStatement> mDBInsertVisit; // used by AddVisit
   nsCOMPtr<mozIStorageStatement> mDBGetPageVisitStats; // used by AddVisit
@@ -542,16 +555,22 @@ protected:
    *
    * @param aForceInit
    *        Indicates if we should close an open database connection or not.
    *        Note: A valid database connection must be opened if this is true.
    */
   nsresult InitDBFile(PRBool aForceInit);
 
   /**
+   * Set journal mode on the database.
+   */
+  nsresult SetJournalMode(enum mozilla::places::JournalMode aJournalMode);
+  enum mozilla::places::JournalMode mCurrentJournalMode;
+
+  /**
    * Initializes the database.  This performs any necessary migrations for the
    * database.  All migration is done inside a transaction that is rolled back
    * if any error occurs.  Upon initialization, history is imported, and some
    * preferences that are used are set.
    */
   nsresult InitDB();
 
   /**