The following revision refers to this bug:
  https://chromium.googlesource.com/chromium/src.git/+/3998fbc50bb215733a081780b286804aa18225c3

commit 3998fbc50bb215733a081780b286804aa18225c3
Author: shess <shess@chromium.org>
Date: Wed Mar 15 16:18:31 2017

[sql] Histograms for I/O calls seen by browser VFS.

Track the common VFS calls which do disk I/O, to help analyze the impact
of any changes to how database connections are used or setup.

BUG= 698010 

Review-Url: https://codereview.chromium.org/2727103003
Cr-Commit-Position: refs/heads/master@{#457099}

[modify] https://crrev.com/3998fbc50bb215733a081780b286804aa18225c3/sql/vfs_wrapper.cc
[modify] https://crrev.com/3998fbc50bb215733a081780b286804aa18225c3/tools/metrics/histograms/histograms.xml

Now that I'm done with some other work which interrupted, I need to get back on this.  Cross-platform histogram results:

Pretty reasonable in Vfs_Events.  xFetch dominates by a lot, xWrite is ~5% of xFetch, , and xRead is half of that.  The xRead will be a combination of dbs or users who's mmap didn't work for some reason, and header reads.  Hmm, and I think Prefetch() uses xRead().

xFetch() sizes are interesting.  Mostly 4k, which makes sense, but big hits at 1k (old SQLite default page size), 2k (I don't recall, but at least one db selects 2k for some reason) and 32k (sync uses this).  Then there's a surprising number of items at "surprisingly large".  Could be noise, but might indicate that I don't understand SQLite as well as I thought.  [The sum of all the other buckets isn't even 1%, but noise seems unlikely too.]

Vfs_Read sizes are similar with 1k, 2k, 4k, 32k.  Lots of hits in the 16-byte range, I believe there's a particular kind of small log read which happens at commit if specific flags are not available on the device characteristics.  I'll try to circle back and see if that's actionable.

Vfs_Write has the 1k, 2k, 4k, 32k buckets, plus a lot of writes at 4 bytes, 16 bytes, and (I think) 512 bytes.  I expect the odd groups are journal writes.

Pages written to journal are probably slightly bigger than page size, while pages written to main db are page size, I don't think histograms can distinguish this.  I'd expect writes under rollback journal to be approximately 50/50 journal versus main db.  WAL would reduce this depending on tuning parameters used.

I don't think that tells anything about auto_vacuum costs :-).  When I isolate Android results, I see that read/write ratio is close to 1-to-1 rather than 2x as many writes as reads.  The proportion of tiny reads is _way_ higher, so maybe it's reading some additional bits to implement auto-vacuum.

The following revision refers to this bug:
  https://chromium.googlesource.com/chromium/src.git/+/e812793e45b69ee0db22a0138113ad17d2fbca13

commit e812793e45b69ee0db22a0138113ad17d2fbca13
Author: shess <shess@chromium.org>
Date: Mon Mar 27 19:16:10 2017

[sql] SQLite patch to implement "smart" auto-vacuum.

SQLITE_FCNTL_CHUNK_SIZE can advise the VFS to resize files in quantum
amounts, to reduce fragmentation from tiny appends.  This change allows
a new PRAGMA auto_vacuum_slack_pages to provide auto_vacuum with a hint
to only rearrange pages when an entire quantum can be released.

BUG= 698010 

Review-Url: https://codereview.chromium.org/2732553002
Cr-Commit-Position: refs/heads/master@{#459847}

[modify] https://crrev.com/e812793e45b69ee0db22a0138113ad17d2fbca13/sql/sqlite_features_unittest.cc
[modify] https://crrev.com/e812793e45b69ee0db22a0138113ad17d2fbca13/third_party/sqlite/amalgamation/sqlite3.c
[add] https://crrev.com/e812793e45b69ee0db22a0138113ad17d2fbca13/third_party/sqlite/patches/0011-Allow-auto-vacuum-to-work-with-chunks.patch
[modify] https://crrev.com/e812793e45b69ee0db22a0138113ad17d2fbca13/third_party/sqlite/src/src/btree.c
[modify] https://crrev.com/e812793e45b69ee0db22a0138113ad17d2fbca13/third_party/sqlite/src/src/btree.h
[modify] https://crrev.com/e812793e45b69ee0db22a0138113ad17d2fbca13/third_party/sqlite/src/src/btreeInt.h
[modify] https://crrev.com/e812793e45b69ee0db22a0138113ad17d2fbca13/third_party/sqlite/src/src/pragma.c
[modify] https://crrev.com/e812793e45b69ee0db22a0138113ad17d2fbca13/third_party/sqlite/src/src/pragma.h

This will not be happening. We've already removed the VFS histograms, and https://crrev.com/c/1362222 will remove the SQLite patch landed above. After that happens, this bug will be WontFix'ed.

The following revision refers to this bug:
  https://chromium.googlesource.com/chromium/src.git/+/4f03013104e9848b0c0d6ca0c0fdbd2c30410395

commit 4f03013104e9848b0c0d6ca0c0fdbd2c30410395
Author: Victor Costan <pwnall@chromium.org>
Date: Wed Dec 05 00:30:08 2018

sqlite: Remove custom PRAGMA auto_vacuum_slack_pages.

SQLite patch 0007-Allow-auto-vacuum-to-work-with-chunks.patch
introduces a PRAGMA that is not used outside of tests. The PRAGMA was
added for an experiment that was discontinued, referenced on the bug
above.

This CL removes the patch and the associated tests, reducing the total
amount of patches we carry on top of SQLite.

No functional changes are expected, because the patch is inert in
production. Specifically, the newly introduced autoVacuumSlack member of
the BtShared structure is zero-initialized by default, and can only be
changed by issuing the auto_vacuum_slack_pages PRAGMA. The new btree
code is gated by an if (... && pBt->autoVacuumSlack). The PRAGMA is not
used outside of testing.

Bug:  698010 
Change-Id: Id2a40badccb96389c6ff9ce1780ffa703dedce6c
Reviewed-on: https://chromium-review.googlesource.com/c/1362222
Reviewed-by: Chris Mumford <cmumford@chromium.org>
Commit-Queue: Victor Costan <pwnall@chromium.org>
Cr-Commit-Position: refs/heads/master@{#613782}
[modify] https://crrev.com/4f03013104e9848b0c0d6ca0c0fdbd2c30410395/sql/sqlite_features_unittest.cc
[modify] https://crrev.com/4f03013104e9848b0c0d6ca0c0fdbd2c30410395/third_party/sqlite/amalgamation/sqlite3.c
[modify] https://crrev.com/4f03013104e9848b0c0d6ca0c0fdbd2c30410395/third_party/sqlite/patches/0001-test-SQLite-tests-compiling-on-Linux.patch
[modify] https://crrev.com/4f03013104e9848b0c0d6ca0c0fdbd2c30410395/third_party/sqlite/patches/0002-Modify-default-VFS-to-support-WebDatabase.patch
[modify] https://crrev.com/4f03013104e9848b0c0d6ca0c0fdbd2c30410395/third_party/sqlite/patches/0003-Virtual-table-supporting-recovery-of-corrupted-datab.patch
[modify] https://crrev.com/4f03013104e9848b0c0d6ca0c0fdbd2c30410395/third_party/sqlite/patches/0004-Custom-shell.c-helpers-to-load-Chromium-s-ICU-data.patch
[modify] https://crrev.com/4f03013104e9848b0c0d6ca0c0fdbd2c30410395/third_party/sqlite/patches/0005-fts3-Disable-fts3_tokenizer-and-fts4.patch
[modify] https://crrev.com/4f03013104e9848b0c0d6ca0c0fdbd2c30410395/third_party/sqlite/patches/0006-fts3-Fix-uninit-variable-in-fts3EvalDeferredPhrase.patch
[delete] https://crrev.com/d1c6a0c1985aae3d9844fe2ee75ff7919fed0ee3/third_party/sqlite/patches/0007-Allow-auto-vacuum-to-work-with-chunks.patch
[rename] https://crrev.com/4f03013104e9848b0c0d6ca0c0fdbd2c30410395/third_party/sqlite/patches/0007-fuchsia-Use-dot-file-locking-for-sqlite.patch
[rename] https://crrev.com/4f03013104e9848b0c0d6ca0c0fdbd2c30410395/third_party/sqlite/patches/0008-Fix-ossfuzz.c-to-compile-and-run-with-our-config.patch
[rename] https://crrev.com/4f03013104e9848b0c0d6ca0c0fdbd2c30410395/third_party/sqlite/patches/0009-Backport-Windows-VFS-mmap-fix.patch
[modify] https://crrev.com/4f03013104e9848b0c0d6ca0c0fdbd2c30410395/third_party/sqlite/src/src/btree.c
[modify] https://crrev.com/4f03013104e9848b0c0d6ca0c0fdbd2c30410395/third_party/sqlite/src/src/btree.h
[modify] https://crrev.com/4f03013104e9848b0c0d6ca0c0fdbd2c30410395/third_party/sqlite/src/src/btreeInt.h
[modify] https://crrev.com/4f03013104e9848b0c0d6ca0c0fdbd2c30410395/third_party/sqlite/src/src/pragma.c
[modify] https://crrev.com/4f03013104e9848b0c0d6ca0c0fdbd2c30410395/third_party/sqlite/src/src/pragma.h
[modify] https://crrev.com/4f03013104e9848b0c0d6ca0c0fdbd2c30410395/third_party/sqlite/src/tool/mkpragmatab.tcl