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

[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}
Committed: https://chromium.googlesource.com/chromium/src/+/e812793e45b69ee0db22a0138113ad17d2fbca13

[Scott Hess - ex-Googler, 2017-03-03 00:10:56]

The CQ bit was checked by shess@chromium.org to run a CQ dry run

[commit-bot: I haz the power, 2017-03-03 00:11:28]

Dry run: CQ is trying da patch. Follow status at
 
https://chromium-cq-status.appspot.com/v2/patch-status/codereview.chromium.or...

[Scott Hess - ex-Googler, 2017-03-03 00:12:06]

shess@chromium.org changed reviewers:
+ mariakhomenko@chromium.org, pasko@chromium.org

[Scott Hess - ex-Googler, 2017-03-03 00:12:06]

This CL patches up SQLite.  I didn't get much love when I tossed it at SQLite
core, but maybe if we generate numbers I can be more convincing.  Anyhow, I am
comfortable having this in our patched SQLite for awhile, though I'd want to
have firm numbers before keeping it for the long term.

[commit-bot: I haz the power, 2017-03-03 01:11:52]

The CQ bit was unchecked by commit-bot@chromium.org

[commit-bot: I haz the power, 2017-03-03 01:11:54]

Dry run: Try jobs failed on following builders:
  linux_chromium_tsan_rel_ng on master.tryserver.chromium.linux (JOB_FAILED,
http://build.chromium.org/p/tryserver.chromium.linux/builders/linux_chromium_...)

[Scott Hess - ex-Googler, 2017-03-03 19:49:47]

Rebase

[Scott Hess - ex-Googler, 2017-03-03 19:49:54]

The CQ bit was checked by shess@chromium.org to run a CQ dry run

[commit-bot: I haz the power, 2017-03-03 19:50:11]

Dry run: CQ is trying da patch. Follow status at
 
https://chromium-cq-status.appspot.com/v2/patch-status/codereview.chromium.or...

[Maria, 2017-03-03 20:23:51]

lgtm

Though honestly, I don't think I would know if this did something wrong. To the
best of my limited understanding it looks fine -- is there anyone other than you
who understands sqlite to review this?

[commit-bot: I haz the power, 2017-03-03 21:42:37]

The CQ bit was unchecked by commit-bot@chromium.org

[commit-bot: I haz the power, 2017-03-03 21:42:38]

Dry run: Try jobs failed on following builders:
  win_chromium_x64_rel_ng on master.tryserver.chromium.win (JOB_FAILED,
http://build.chromium.org/p/tryserver.chromium.win/builders/win_chromium_x64_...)

[pasko, 2017-03-06 17:05:08]

non-expert lgtm, looks great from bird's-eye view!

[Scott Hess - ex-Googler, 2017-03-15 20:03:21]

rebase

[Scott Hess - ex-Googler, 2017-03-15 20:09:06]

On 2017/03/03 20:23:51, Maria wrote:
> lgtm
> 
> Though honestly, I don't think I would know if this did something wrong. To
the
> best of my limited understanding it looks fine -- is there anyone other than
you
> who understands sqlite to review this?

Unfortunately, not really.  We have modest resources WRT SQLite users, but I
don't know of anyone else who spends time working within the implementation.

The changes are based on copy-paste of the corresponding "PRAGMA auto_vacuum"
implementation code, generally just above the new code.

I had hoped that the SQLite team would be interested in the patch, but I don't
think they gave me a strong reception (the response was more about "Why even
bother with auto_vacuum in the first place?").  So to some extent I'm willing to
let the test stand alone, though ... it does occur to me that perhaps I should
expand the test while thinking about code coverage, just to be sure.

[Scott Hess - ex-Googler, 2017-03-15 21:44:46]

set/get coverage

[Scott Hess - ex-Googler, 2017-03-15 21:45:04]

The CQ bit was checked by shess@chromium.org to run a CQ dry run

[commit-bot: I haz the power, 2017-03-15 21:45:59]

Dry run: CQ is trying da patch. Follow status at
 
https://chromium-cq-status.appspot.com/v2/patch-status/codereview.chromium.or...

[commit-bot: I haz the power, 2017-03-15 22:40:45]

The CQ bit was unchecked by commit-bot@chromium.org

[commit-bot: I haz the power, 2017-03-15 22:40:46]

Dry run: Try jobs failed on following builders:
  win_chromium_x64_rel_ng on master.tryserver.chromium.win (JOB_FAILED,
http://build.chromium.org/p/tryserver.chromium.win/builders/win_chromium_x64_...)

[Scott Hess - ex-Googler, 2017-03-15 23:20:30]

On 2017/03/15 21:44:46, Scott Hess wrote:
> set/get coverage

Hmm.  OK, I have to look into why this is failing on Windows trybots.  I seem to
recall that there's something in the intersection of truncation and memory
mapping which might be relevant.

[Scott Hess - ex-Googler, 2017-03-24 20:40:11]

update patch against SQLite 3.17

[Scott Hess - ex-Googler, 2017-03-24 21:11:25]

The CQ bit was checked by shess@chromium.org to run a CQ dry run

[commit-bot: I haz the power, 2017-03-24 21:12:12]

Dry run: CQ is trying da patch. Follow status at
 
https://chromium-cq-status.appspot.com/v2/patch-status/codereview.chromium.or...

[Scott Hess - ex-Googler, 2017-03-24 21:14:38]

On 2017/03/15 23:20:30, Scott Hess wrote:
> On 2017/03/15 21:44:46, Scott Hess wrote:
> > set/get coverage
> 
> Hmm.  OK, I have to look into why this is failing on Windows trybots.  I seem
to
> recall that there's something in the intersection of truncation and memory
> mapping which might be relevant.

Yeah, https://www.sqlite.org/mmap.html has:

"Windows is unable to truncate a memory-mapped file. Hence, on Windows, if an
operation such as VACUUM or auto_vacuum tries to reduce the size of a
memory-mapped database file, the size reduction attempt will silently fail,
leaving unused space at the end of the database file."

This is neither here nor there WRT this set of changes, since Chrome for Windows
doesn't use auto_vacuum, so I'm just going to #ifdef out OS_WIN for now.

[Scott Hess - ex-Googler, 2017-03-24 22:08:10]

Omit file-size-based tests for Windows.

[Scott Hess - ex-Googler, 2017-03-24 22:08:58]

The CQ bit was checked by shess@chromium.org to run a CQ dry run

[commit-bot: I haz the power, 2017-03-24 22:09:45]

Dry run: CQ is trying da patch. Follow status at
 
https://chromium-cq-status.appspot.com/v2/patch-status/codereview.chromium.or...

[commit-bot: I haz the power, 2017-03-24 23:00:07]

The CQ bit was unchecked by commit-bot@chromium.org

[commit-bot: I haz the power, 2017-03-24 23:00:08]

Dry run: Try jobs failed on following builders:
  win_clang on master.tryserver.chromium.win (JOB_FAILED,
http://build.chromium.org/p/tryserver.chromium.win/builders/win_clang/builds/...)

[Scott Hess - ex-Googler, 2017-03-25 03:51:43]

sigh, keep clang happy on windows

[Scott Hess - ex-Googler, 2017-03-25 03:52:06]

The CQ bit was checked by shess@chromium.org to run a CQ dry run

[commit-bot: I haz the power, 2017-03-25 03:52:11]

Dry run: CQ is trying da patch. Follow status at
 
https://chromium-cq-status.appspot.com/v2/patch-status/codereview.chromium.or...

[commit-bot: I haz the power, 2017-03-25 05:02:28]

The CQ bit was unchecked by commit-bot@chromium.org

[commit-bot: I haz the power, 2017-03-25 05:02:29]

Dry run: Try jobs failed on following builders:
  linux_chromium_chromeos_rel_ng on master.tryserver.chromium.linux (JOB_FAILED,
http://build.chromium.org/p/tryserver.chromium.linux/builders/linux_chromium_...)

[Scott Hess - ex-Googler, 2017-03-27 18:08:58]

The CQ bit was checked by shess@chromium.org

[Scott Hess - ex-Googler, 2017-03-27 18:08:59]

The patchset sent to the CQ was uploaded after l-g-t-m from pasko@chromium.org,
mariakhomenko@chromium.org
Link to the patchset: https://codereview.chromium.org/2732553002/#ps120001
(title: "sigh, keep clang happy on windows")

[commit-bot: I haz the power, 2017-03-27 18:09:36]

CQ is trying da patch. Follow status at
 
https://chromium-cq-status.appspot.com/v2/patch-status/codereview.chromium.or...

[commit-bot: I haz the power, 2017-03-27 19:16:16]

CQ is committing da patch.

Bot data: {"patchset_id": 120001, "attempt_start_ts": 1490638138907330,
"parent_rev": "c205b90bd3a64f18c14fbe299af13c0de237e3cc", "commit_rev":
"e812793e45b69ee0db22a0138113ad17d2fbca13"}

[commit-bot: I haz the power, 2017-03-27 19:17:02]

Description was changed from

==========
[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
==========

to

==========
[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}
Committed:
https://chromium.googlesource.com/chromium/src/+/e812793e45b69ee0db22a0138113...
==========

[commit-bot: I haz the power, 2017-03-27 19:17:05]

Committed patchset #7 (id:120001) as
https://chromium.googlesource.com/chromium/src/+/e812793e45b69ee0db22a0138113...