[sql] Use memory-mapped I/O for sql::Connection.

sql/connection.cc

 // Copyright (c) 2012 The Chromium Authors. All rights reserved.
 // Use of this source code is governed by a BSD-style license that can be
 // found in the LICENSE file.
 
 #include "sql/connection.h"
 
 #include <string.h>
 
 #include "base/bind.h"
 #include "base/files/file_path.h"
@@ skipping 448 matching lines @@
     preload_size = file_size;
 
   scoped_ptr<char[]> buf(new char[page_size]);
   for (sqlite3_int64 pos = 0; pos < preload_size; pos += page_size) {
     rc = file->pMethods->xRead(file, buf.get(), page_size, pos);
     if (rc != SQLITE_OK)
       return;
   }
 }
 
+// SQLite keeps unused pages associated with a connection in a cache.  It asks
+// the cache for pages by an id, and if the page is present and the database is
+// unchanged, it considers the content of the page valid and doesn't read it
+// from disk.  When memory-mapped I/O is enabled, on read SQLite uses page
+// structures created from the memory map data before consulting the cache.  On
+// write SQLite creates a new in-memory page structure, copies the data from the
+// memory map, and later writes it, releasing the updated page back to the
+// cache.
+//
+// This means that in memory-mapped mode, the contents of the cached pages are
+// not re-used for reads, but they are re-used for writes if the re-written page
+// is still in the cache. The implementation of sqlite3_db_release_memory() as
+// of SQLite 3.8.7.4 frees all pages from pcaches associated with the
+// connection, so it should free these pages.
+//
+// Unfortunately, the zero page is also freed.  That page is never accessed
+// using memory-mapped I/O, and the cached copy can be re-used after verifying
+// the file change counter on disk.  Also, fresh pages from cache receive some
+// pager-level initialization before they can be used.  Since the information
+// involved will immediately be accessed in various ways, it is unclear if the
+// additional overhead is material, or just moving processor cache effects
+// around.
+//
+// TODO(shess): It would be better to release the pages immediately when they
+// are no longer needed.  This would basically happen after SQLite commits a
+// transaction.  I had implemented a pcache wrapper to do this, but it involved
+// layering violations, and it had to be setup before any other sqlite call,
+// which was brittle.  Also, for large files it would actually make sense to
+// maintain the existing pcache behavior for blocks past the memory-mapped
+// segment.  I think drh would accept a reasonable implementation of the overall
+// concept for upstreaming to SQLite core.
+//
+// TODO(shess): Another possibility would be to set the cache size small, which
+// would keep the zero page around, plus some pre-initialized pages, and SQLite
+// can manage things.  The downside is that updates larger than the cache would
+// spill to the journal.  That could be compensated by setting cache_spill to
+// false.  The downside then is that it allows open-ended use of memory for
+// large transactions.
+//
+// TODO(shess): The TrimMemory() trick of bouncing the cache size would also
+// work.  There could be two prepared statements, one for cache_size=1 one for
+// cache_size=goal.
+void Connection::ReleaseCacheMemoryIfNeeded(bool assume_changed) {

[rmcilroy, 2015/09/17 17:17:44]

nit - /s/assume_changed/force_release/ ?

[Scott Hess - ex-Googler, 2015/09/17 21:14:37]

I can see being against |assume_changed|.  |force_release| isn't really it,
since it may not release if mmap not enabled, or if in a transaction (in that
case it will release later, though).

Maybe |hint_changed|?

[rmcilroy, 2015/09/18 11:16:29]

Hmm, it's not really a hint though. How about implicit_change_performed or
similar. Btw, why do the execute operations not update sqlite3_total_changes()?

[Scott Hess - ex-Googler, 2015/09/18 15:43:41]

A statement like "UPDATE foo SET x=x+1 WHERE y<?" may or may not update any
rows, and sqlite3_total_changes() will track that.  But a statement like "CREATE
TABLE foo (c TEXT)" won't update sqlite3_total_changes(), because no rows were
changed.  Likewise with "DROP TABLE", "ALTER TABLE", etc.

DDL type statements are almost always passed to Execute().  Some DML statements
could be, but they'd have to not reference any rows, like "DELETE FROM foo", but
noticing their changes twice won't be a big deal.

sqlite3_stmt_read_only() would be a reasonable alternative in this context to
catch the DDL cases, but that would still leave things like PRAGMA, where
answering the question is _really_ deep.

Long-term, hopefully I can upstream a patch to expose this from SQLite, and it
can make informed decisions at the pager layer.

[rmcilroy, 2015/09/18 16:06:32]

SGTM, thanks for the explanation.

[Scott Hess - ex-Googler, 2015/09/21 18:08:57]

Acknowledged.

+  // If memory-mapping is not enabled, the page cache helps performance.
+  if (!mmap_enabled_)
+    return;
+
+  // On caller request, force the change comparison to fail.  Done before the
+  // transaction-nesting test so that the signal can carry to transaction
+  // commit.
+  if (assume_changed)
+    --total_changes_;
+
+  // Cached pages may be re-used within the same transaction.
+  if (transaction_nesting())
+    return;
+
+  // If no changes have been made, skip flushing.  This allows the first page of
+  // the database to remain in cache across multiple reads.
+  int current_changes = sqlite3_total_changes(db_);
+  if (current_changes == total_changes_)
+    return;
+
+  total_changes_ = current_changes;
+  sqlite3_db_release_memory(db_);
+}
+
+// NOTE(shess): When memory-mapped mode is solid, this will be a noop.
 void Connection::TrimMemory(bool aggressively) {
   if (!db_)
     return;
 
   // TODO(shess): investigate using sqlite3_db_release_memory() when possible.
   int original_cache_size;
   {
     Statement sql_get_original(GetUniqueStatement("PRAGMA cache_size"));
     if (!sql_get_original.Step()) {
       DLOG(WARNING) << "Could not get cache size " << GetErrorMessage();
@@ skipping 290 matching lines @@
 
   // Collect the commit time manually, sql::Statement would register it as query
   // time only.
   const base::TimeTicks before = Now();
   bool ret = commit.RunWithoutTimers();
   const base::TimeDelta delta = Now() - before;
 
   RecordCommitTime(delta);
   RecordOneEvent(EVENT_COMMIT);
 
+  // Release dirty cache pages after the transaction closes.
+  ReleaseCacheMemoryIfNeeded(false);
+
   return ret;
 }
 
 void Connection::RollbackAllTransactions() {
   if (transaction_nesting_ > 0) {
     transaction_nesting_ = 0;
     DoRollback();
   }
 }
 
@@ skipping 70 matching lines @@
     // sqlite3_exec() does this, presumably to avoid spinning the parser for
     // trailing whitespace.
     // TODO(shess): Audit to see if this can become a DCHECK.
     while (base::IsAsciiWhitespace(*sql)) {
       sql++;
     }
 
     const base::TimeDelta delta = Now() - before;
     RecordTimeAndChanges(delta, read_only);
   }
+
+  // Most calls to Execute() modify the database.  The main exceptions would be
+  // calls such as CREATE TABLE IF NOT EXISTS which could modify the database
+  // but sometimes don't.
+  ReleaseCacheMemoryIfNeeded(true);
+
   return rc;
 }
 
 bool Connection::Execute(const char* sql) {
   if (!db_) {
     DLOG_IF(FATAL, !poisoned_) << "Illegal use of connection without a db";
     return false;
   }
 
   int error = ExecuteAndReturnErrorCode(sql);
@@ skipping 344 matching lines @@
 
   // http://www.sqlite.org/pragma.html#pragma_journal_mode
   // DELETE (default) - delete -journal file to commit.
   // TRUNCATE - truncate -journal file to commit.
   // PERSIST - zero out header of -journal file to commit.
   // TRUNCATE should be faster than DELETE because it won't need directory
   // changes for each transaction.  PERSIST may break the spirit of using
   // secure_delete.
   ignore_result(Execute("PRAGMA journal_mode = TRUNCATE"));
 
+  // Enable memory-mapped access.  This value will be capped by
+  // SQLITE_MAX_MMAP_SIZE, which could be different between 32-bit and 64-bit
+  // platforms.
+  mmap_enabled_ = false;
+  ignore_result(Execute("PRAGMA mmap_size = 2147483648"));  // 2GB.

[rmcilroy, 2015/09/17 17:17:44]

Could we end up in a situation where this causes address space to be exhausted
on a 32-bit platform if there was a massive database mapped?

Just noticed that you cap SQLITE_MAX_MMAP_SIZE to 256 MB. Rather than setting
this to an arbitrary 2GB here, would it be possible to share a #define both here
and in third_party/sql so that both are set to the same value (not sure if this
is possible given how the gyp files are setup)?

[Scott Hess - ex-Googler, 2015/09/17 21:14:37]

I'll set both to 256MB for now.

My original intention was that on 32-bit platforms SQLITE_MAX_MMAP_SIZE might be
set conservatively to prevent fragmentation, but on 64-bit platforms it might be
more liberal, whereas this code would set it more of an on/off setting without
regard to such policy decisions.  But we control horizontal and vertical,
changing it is just a CL regardless of whether it touches both files or not,
meh.

[USE_SYSTEM_SQLITE could also change this, but I can't say how to accommodate
that, beyond the following code double-checking that the result was non-zero.]

+  {
+    Statement s(GetUniqueStatement("PRAGMA mmap_size"));
+    if (s.Step() && s.ColumnInt64(0) > 0)
+      mmap_enabled_ = true;
+  }
+
   const base::TimeDelta kBusyTimeout =
     base::TimeDelta::FromSeconds(kBusyTimeoutSeconds);
 
   if (page_size_ != 0) {
     // Enforce SQLite restrictions on |page_size_|.
     DCHECK(!(page_size_ & (page_size_ - 1)))
         << " page_size_ " << page_size_ << " is not a power of two.";
     const int kSqliteMaxPageSize = 32768;  // from sqliteLimit.h
     DCHECK_LE(page_size_, kSqliteMaxPageSize);
     const std::string sql =
@@ skipping 23 matching lines @@
 
   // Collect the rollback time manually, sql::Statement would register it as
   // query time only.
   const base::TimeTicks before = Now();
   rollback.RunWithoutTimers();
   const base::TimeDelta delta = Now() - before;
 
   RecordUpdateTime(delta);
   RecordOneEvent(EVENT_ROLLBACK);
 
+  // The cache may have been accumulating dirty pages for commit.
+  ReleaseCacheMemoryIfNeeded(false);
+
   needs_rollback_ = false;
 }
 
 void Connection::StatementRefCreated(StatementRef* ref) {
   DCHECK(open_statements_.find(ref) == open_statements_.end());
   open_statements_.insert(ref);
 }
 
 void Connection::StatementRefDeleted(StatementRef* ref) {
   StatementRefSet::iterator i = open_statements_.find(ref);
@@ skipping 99 matching lines @@
   ignore_result(Execute(kNoWritableSchema));
 
   return ret;
 }
 
 base::TimeTicks TimeSource::Now() {
   return base::TimeTicks::Now();
 }
 
 }  // namespace sql