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
first release with
nightly linux32
nightly linux64
nightly mac
nightly win32
nightly win64
last release without
nightly linux32
nightly linux64
nightly mac
nightly win32
nightly win64
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();
 
   /**