You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@subversion.apache.org by Bert Huijben <be...@qqmail.nl> on 2013/11/17 13:34:40 UTC
Using Sqlite in libsvn_wc towards Subversion 1.9++
Hi,
For Subversion 1.8 I spend a lot of time optimizing our use of Sqlite in the
working copy to work as optimal as possible with the then Supported range of
Sqlite versions (3.7.12-3.7.19). Then a few months later in Sqlite 3.8.0 a
new query planner was introduced that broke quite a few of the optimizations
I/we assumed were ok, because we did things different than the Sqlite
developers intended.
For Subversion 1.8 we did:
* Try to support as many sqlite versions as possible (3.7.12-infinity)
* Try to optimize queries without describing our layout
* Add many columns to indexes to make them unique and avoid going back to
the original table when possible.
This goes against +- all the recommendations of the Sqlite designers as they
recommend
* Provide your users exactly the sqlite version you tested against -> link
statically as part of your source
* Provide statistics about your table.
* Use 'good indexes' (either with stats; or with a good first key)
As users that have upgraded to Sqlite 3.8.0 or later have found out our
design choices really affect performance on huge working copies: several
performance critical queries during operations such as commit, delete and
revert are now handled as a full table scan.
To optimize our queries for Subversion 1.7 and 1.8 we should probably write
a bit of code to fill the sqlite statistics table with the information
Sqlite needs to make the right decisions, but for Subversion 1.9 and later
we should decide what we want to do to make sure our users will never see
such a regression any more.
I think we should at least:
* Update our indexes to be closer to what the Sqlite developers call 'good'
indexes': remove unnecessary columns.
(In the sqlite implementation they are already unique via the hidden rowid)
*****
But I would also like to recommend/ask that we start bundling Sqlite with
Subversion, to allow optimizing for the specific version we use for a
release without risking future breakage.
*****
I don't expect that our *nix packagers that like to package every component
separately are going to like this, but I think we should do this to
* guarantee our users a reasonably performance.
* to allow us to diagnose user performance problems
Perhaps a few will even patch their own sqlite version in, but then we can
safely say that we didn't cause the performance problems users have in these
installations.
If we do that and/or bump our requirement to the soon to be released Sqlite
3.8.2 (or later) we can use quite a few new indexing tricks that should
improve update performance on the NODES table and the always attached
triggers more than a bit.*)
If we can get consensus around this I would like to add the Sqlite 3.8.2
amalgamation directly to our source tree about a week after its release
(Probably December 2013). We can than start optimizing our Sqlite schema and
query usage towards this version.
Bert
*)
* The PRISTINE table is the perfect candidate for a 'WITHOUT rowid' table,
which should give a 30% performance boost on update/select. (Requires Sqlite
3.8.2)
* The moved_to index on nodes is a typical example of where a partial index
would help reducing unnecessary index updates (Requires 3.8.0).
Re: Using Sqlite in libsvn_wc towards Subversion 1.9++
Posted by Branko Čibej <br...@wandisco.com>.
On 17.11.2013 14:33, Bert Huijben wrote:
>
>> -----Original Message-----
>> From: Branko Čibej [mailto:brane@wandisco.com]
>> Sent: zondag 17 november 2013 14:08
>> To: dev@subversion.apache.org
>> Subject: Re: Using Sqlite in libsvn_wc towards Subversion 1.9++
>>
>> On 17.11.2013 13:34, Bert Huijben wrote:
>>> Hi,
>>>
>>> For Subversion 1.8 I spend a lot of time optimizing our use of Sqlite in the
>>> working copy to work as optimal as possible with the then Supported range
>> of
>>> Sqlite versions (3.7.12-3.7.19). Then a few months later in Sqlite 3.8.0 a
>>> new query planner was introduced that broke quite a few of the
>> optimizations
>>> I/we assumed were ok, because we did things different than the Sqlite
>>> developers intended.
>>>
>>> For Subversion 1.8 we did:
>>> * Try to support as many sqlite versions as possible (3.7.12-infinity)
>>> * Try to optimize queries without describing our layout
>>> * Add many columns to indexes to make them unique and avoid going back
>> to
>>> the original table when possible.
>>>
>>> This goes against +- all the recommendations of the Sqlite designers as they
>>> recommend
>>> * Provide your users exactly the sqlite version you tested against -> link
>>> statically as part of your source
>>> * Provide statistics about your table.
>>> * Use 'good indexes' (either with stats; or with a good first key)
>>>
>>> As users that have upgraded to Sqlite 3.8.0 or later have found out our
>>> design choices really affect performance on huge working copies: several
>>> performance critical queries during operations such as commit, delete and
>>> revert are now handled as a full table scan.
>>>
>>>
>>> To optimize our queries for Subversion 1.7 and 1.8 we should probably
>> write
>>> a bit of code to fill the sqlite statistics table with the information
>>> Sqlite needs to make the right decisions,
>> I've looked at pre-filling the sqlite_stats1 table. I think it's not
>> going to work because apparently some of the stats columns contain index
>> row counts, which of course vary not only from one working copy to the
>> next, but also during the lifetime of a working copy. Maybe there's a
>> way to fake "infinite" size indexes; I haven't found documentation on
>> how to do that. Most of the analyze results seem to be concerned with
>> the wc_id column.
>>
>> As a data point: running ANALYZE in a wc.db for a checkout of
>> ^/subversion takes about half a minute on my mac (with SSD, fwiw), so I
>> don't think re-running it incrementally is an option.
>>
>>> but for Subversion 1.9 and later
>>> we should decide what we want to do to make sure our users will never
>> see
>>> such a regression any more.
>>>
>>> I think we should at least:
>>> * Update our indexes to be closer to what the Sqlite developers call 'good'
>>> indexes': remove unnecessary columns.
>>> (In the sqlite implementation they are already unique via the hidden rowid)
>>>
>>> *****
>>> But I would also like to recommend/ask that we start bundling Sqlite with
>>> Subversion, to allow optimizing for the specific version we use for a
>>> release without risking future breakage.
>>> *****
>> You realize that if we do that, we also have to provide a sqlite
>> command-line tool based on that same version.
> Why?
Two reasons:
* Developers need it for debugging the working copy
* Administrators (and probably users) might need it in order to
produce useful bug reports
We don't often get WC-related bug reports that require looking into the
database, but when we do, we may as well get the best possible results.
FWIW, building an amalgamated sqlite3 binary is trivial; the
amalgamation contains source for that.
> Users should have no need to look into our wc.db file, and if they want they can still use any compatible sqlite version. We don't promise performance on their code that peeks in our database.
If we start playing around with indexing tricks that rely on some
particular version of SQLite, you can't be sure that whatever the user
has installed will show correct results.
> We don't deliver sqlite3.exe on Windows, do we?
We don't deliver binaries, so I'm going to ignore this argument as
irrelevant.
-- Brane
--
Branko Čibej | Director of Subversion
WANdisco // Non-Stop Data
e. brane@wandisco.com
RE: Using Sqlite in libsvn_wc towards Subversion 1.9++
Posted by Bert Huijben <be...@qqmail.nl>.
> -----Original Message-----
> From: Branko Čibej [mailto:brane@wandisco.com]
> Sent: zondag 17 november 2013 14:08
> To: dev@subversion.apache.org
> Subject: Re: Using Sqlite in libsvn_wc towards Subversion 1.9++
>
> On 17.11.2013 13:34, Bert Huijben wrote:
> > Hi,
> >
> > For Subversion 1.8 I spend a lot of time optimizing our use of Sqlite in the
> > working copy to work as optimal as possible with the then Supported range
> of
> > Sqlite versions (3.7.12-3.7.19). Then a few months later in Sqlite 3.8.0 a
> > new query planner was introduced that broke quite a few of the
> optimizations
> > I/we assumed were ok, because we did things different than the Sqlite
> > developers intended.
> >
> > For Subversion 1.8 we did:
> > * Try to support as many sqlite versions as possible (3.7.12-infinity)
> > * Try to optimize queries without describing our layout
> > * Add many columns to indexes to make them unique and avoid going back
> to
> > the original table when possible.
> >
> > This goes against +- all the recommendations of the Sqlite designers as they
> > recommend
> > * Provide your users exactly the sqlite version you tested against -> link
> > statically as part of your source
> > * Provide statistics about your table.
> > * Use 'good indexes' (either with stats; or with a good first key)
> >
> > As users that have upgraded to Sqlite 3.8.0 or later have found out our
> > design choices really affect performance on huge working copies: several
> > performance critical queries during operations such as commit, delete and
> > revert are now handled as a full table scan.
> >
> >
> > To optimize our queries for Subversion 1.7 and 1.8 we should probably
> write
> > a bit of code to fill the sqlite statistics table with the information
> > Sqlite needs to make the right decisions,
>
> I've looked at pre-filling the sqlite_stats1 table. I think it's not
> going to work because apparently some of the stats columns contain index
> row counts, which of course vary not only from one working copy to the
> next, but also during the lifetime of a working copy. Maybe there's a
> way to fake "infinite" size indexes; I haven't found documentation on
> how to do that. Most of the analyze results seem to be concerned with
> the wc_id column.
>
> As a data point: running ANALYZE in a wc.db for a checkout of
> ^/subversion takes about half a minute on my mac (with SSD, fwiw), so I
> don't think re-running it incrementally is an option.
>
> > but for Subversion 1.9 and later
> > we should decide what we want to do to make sure our users will never
> see
> > such a regression any more.
> >
> > I think we should at least:
> > * Update our indexes to be closer to what the Sqlite developers call 'good'
> > indexes': remove unnecessary columns.
> > (In the sqlite implementation they are already unique via the hidden rowid)
> >
> > *****
> > But I would also like to recommend/ask that we start bundling Sqlite with
> > Subversion, to allow optimizing for the specific version we use for a
> > release without risking future breakage.
> > *****
>
> You realize that if we do that, we also have to provide a sqlite
> command-line tool based on that same version.
Why?
Users should have no need to look into our wc.db file, and if they want they can still use any compatible sqlite version. We don't promise performance on their code that peeks in our database.
We don't deliver sqlite3.exe on Windows, do we?
> There's another option: that we use different indexes based on the
> version of SQLite that the wc.db was created with. We can always detect
> when the sqlite binaries are downgraded (or upgraded), and could emit an
> error or warning; and we could provide 'svn cleanup --reindex', or some
> equivalent, that would rebuild indexes according to the current SQLite
> version, without having to throw away the working copy and start over.
>
> > I don't expect that our *nix packagers that like to package every
> component
> > separately are going to like this, but I think we should do this to
> > * guarantee our users a reasonably performance.
> > * to allow us to diagnose user performance problems
> > Perhaps a few will even patch their own sqlite version in, but then we can
> > safely say that we didn't cause the performance problems users have in
> these
> > installations.
>
> Whatever we do, we should always allow linking to external SQLite. The
> most we can do, IMO, is to not search for the headers and libs unless
> --with-sqlite was given explicitly during configure time.
Same question: why?
Netscape, chrome, etc. all link their own sqlite inside. That it is in there is an application detail.
They could have used their own database implementation. The fact that they use an existing database doesn't make it a supported API.
We don't support users that modify our wc.db file, but that some developers can peek inside is nice.
>
> > If we do that and/or bump our requirement to the soon to be released
> Sqlite
> > 3.8.2 (or later) we can use quite a few new indexing tricks that should
> > improve update performance on the NODES table and the always attached
> > triggers more than a bit.*)
> >
> >
> >
> > If we can get consensus around this I would like to add the Sqlite 3.8.2
> > amalgamation directly to our source tree about a week after its release
> > (Probably December 2013). We can than start optimizing our Sqlite schema
> and
> > query usage towards this version.
> >
> >
> > Bert
> >
> > *)
> > * The PRISTINE table is the perfect candidate for a 'WITHOUT rowid' table,
> > which should give a 30% performance boost on update/select. (Requires
> Sqlite
> > 3.8.2)
> > * The moved_to index on nodes is a typical example of where a partial
> index
> > would help reducing unnecessary index updates (Requires 3.8.0).
>
> As a matter of fact, all indexes that begin with wc_id are candidates
> for partial indexing (with WHERE wc_id=1).
Except that this would break all use of all these indexes. We query wc_id=?1 and then the query analyzer can't match that to '1', so the query won't use an index at all.
Sqlite is 'light' in a way that it only optimizes queries once, and never on actual values.
The 'big' sqlite engines will use additional passes to optimize for cases like these, but Sqlite never does that.
Bert
>
> -- Brane
>
>
> --
> Branko Čibej | Director of Subversion
> WANdisco // Non-Stop Data
> e. brane@wandisco.com
Re: Using Sqlite in libsvn_wc towards Subversion 1.9++
Posted by Branko Čibej <br...@wandisco.com>.
On 17.11.2013 13:34, Bert Huijben wrote:
> Hi,
>
> For Subversion 1.8 I spend a lot of time optimizing our use of Sqlite in the
> working copy to work as optimal as possible with the then Supported range of
> Sqlite versions (3.7.12-3.7.19). Then a few months later in Sqlite 3.8.0 a
> new query planner was introduced that broke quite a few of the optimizations
> I/we assumed were ok, because we did things different than the Sqlite
> developers intended.
>
> For Subversion 1.8 we did:
> * Try to support as many sqlite versions as possible (3.7.12-infinity)
> * Try to optimize queries without describing our layout
> * Add many columns to indexes to make them unique and avoid going back to
> the original table when possible.
>
> This goes against +- all the recommendations of the Sqlite designers as they
> recommend
> * Provide your users exactly the sqlite version you tested against -> link
> statically as part of your source
> * Provide statistics about your table.
> * Use 'good indexes' (either with stats; or with a good first key)
>
> As users that have upgraded to Sqlite 3.8.0 or later have found out our
> design choices really affect performance on huge working copies: several
> performance critical queries during operations such as commit, delete and
> revert are now handled as a full table scan.
>
>
> To optimize our queries for Subversion 1.7 and 1.8 we should probably write
> a bit of code to fill the sqlite statistics table with the information
> Sqlite needs to make the right decisions,
I've looked at pre-filling the sqlite_stats1 table. I think it's not
going to work because apparently some of the stats columns contain index
row counts, which of course vary not only from one working copy to the
next, but also during the lifetime of a working copy. Maybe there's a
way to fake "infinite" size indexes; I haven't found documentation on
how to do that. Most of the analyze results seem to be concerned with
the wc_id column.
As a data point: running ANALYZE in a wc.db for a checkout of
^/subversion takes about half a minute on my mac (with SSD, fwiw), so I
don't think re-running it incrementally is an option.
> but for Subversion 1.9 and later
> we should decide what we want to do to make sure our users will never see
> such a regression any more.
>
> I think we should at least:
> * Update our indexes to be closer to what the Sqlite developers call 'good'
> indexes': remove unnecessary columns.
> (In the sqlite implementation they are already unique via the hidden rowid)
>
> *****
> But I would also like to recommend/ask that we start bundling Sqlite with
> Subversion, to allow optimizing for the specific version we use for a
> release without risking future breakage.
> *****
You realize that if we do that, we also have to provide a sqlite
command-line tool based on that same version.
There's another option: that we use different indexes based on the
version of SQLite that the wc.db was created with. We can always detect
when the sqlite binaries are downgraded (or upgraded), and could emit an
error or warning; and we could provide 'svn cleanup --reindex', or some
equivalent, that would rebuild indexes according to the current SQLite
version, without having to throw away the working copy and start over.
> I don't expect that our *nix packagers that like to package every component
> separately are going to like this, but I think we should do this to
> * guarantee our users a reasonably performance.
> * to allow us to diagnose user performance problems
> Perhaps a few will even patch their own sqlite version in, but then we can
> safely say that we didn't cause the performance problems users have in these
> installations.
Whatever we do, we should always allow linking to external SQLite. The
most we can do, IMO, is to not search for the headers and libs unless
--with-sqlite was given explicitly during configure time.
> If we do that and/or bump our requirement to the soon to be released Sqlite
> 3.8.2 (or later) we can use quite a few new indexing tricks that should
> improve update performance on the NODES table and the always attached
> triggers more than a bit.*)
>
>
>
> If we can get consensus around this I would like to add the Sqlite 3.8.2
> amalgamation directly to our source tree about a week after its release
> (Probably December 2013). We can than start optimizing our Sqlite schema and
> query usage towards this version.
>
>
> Bert
>
> *)
> * The PRISTINE table is the perfect candidate for a 'WITHOUT rowid' table,
> which should give a 30% performance boost on update/select. (Requires Sqlite
> 3.8.2)
> * The moved_to index on nodes is a typical example of where a partial index
> would help reducing unnecessary index updates (Requires 3.8.0).
As a matter of fact, all indexes that begin with wc_id are candidates
for partial indexing (with WHERE wc_id=1).
-- Brane
--
Branko Čibej | Director of Subversion
WANdisco // Non-Stop Data
e. brane@wandisco.com
Re: Using Sqlite in libsvn_wc towards Subversion 1.9++
Posted by Branko Čibej <br...@wandisco.com>.
On 18.11.2013 03:50, Justin Erenkrantz wrote:
> On Sun, Nov 17, 2013 at 7:34 AM, Bert Huijben <bert@qqmail.nl
> <ma...@qqmail.nl>> wrote:
>
> But I would also like to recommend/ask that we start bundling
> Sqlite with
> Subversion, to allow optimizing for the specific version we use for a
> release without risking future breakage.
>
>
> If we use the amalgamation, are all of the symbols private to our
> usage? That is, if someone links in libsvn_wc and their application
> also links in the system (or another) sqlite, aren't we going to have
> a runtime conflict? -- justin
Nope, they're private. We force the SQLite APIs to be declared static
(that's a feature of the amalgamated code), and we only export the API
table pointer and a couple function pointers within our own namesapce;
see the last few lines in
http://svn.apache.org/viewvc/subversion/trunk/subversion/libsvn_subr/sqlite3wrapper.c?view=markup
— Brane
--
Branko Čibej | Director of Subversion
WANdisco // Non-Stop Data
e. brane@wandisco.com
Re: Using Sqlite in libsvn_wc towards Subversion 1.9++
Posted by Justin Erenkrantz <ju...@erenkrantz.com>.
On Sun, Nov 17, 2013 at 7:34 AM, Bert Huijben <be...@qqmail.nl> wrote:
> But I would also like to recommend/ask that we start bundling Sqlite with
> Subversion, to allow optimizing for the specific version we use for a
> release without risking future breakage.
>
If we use the amalgamation, are all of the symbols private to our usage?
That is, if someone links in libsvn_wc and their application also links in
the system (or another) sqlite, aren't we going to have a runtime conflict?
-- justin
Re: Using Sqlite in libsvn_wc towards Subversion 1.9++
Posted by Philip Martin <ph...@wandisco.com>.
"Bert Huijben" <be...@qqmail.nl> writes:
> As users that have upgraded to Sqlite 3.8.0 or later have found out our
> design choices really affect performance on huge working copies: several
> performance critical queries during operations such as commit, delete and
> revert are now handled as a full table scan.
This is something we should document in the release notes. I think we
should be recommending a 'best' version. I see that the 1.8 get-deps.sh
still has 3.7.15.1, is that the 'best' version or should we be
recommending the later 3.7.17?
--
Philip Martin | Subversion Committer
WANdisco // *Non-Stop Data*
Re: Using Sqlite in libsvn_wc towards Subversion 1.9++
Posted by Philip Martin <ph...@wandisco.com>.
"Bert Huijben" <be...@qqmail.nl> writes:
> *****
> But I would also like to recommend/ask that we start bundling Sqlite with
> Subversion, to allow optimizing for the specific version we use for a
> release without risking future breakage.
> *****
Are we able to get advance notice of SQLite security releases or do we
have to react to public releases? Take 3.7 for example, how many of the
releases fixed security issues and would have triggered a new Subversion
release? Do we allow developers to make changes to our copy of SQLite
or do we insist that the Subversion copy is pristine? I identified
umask as an SQLite problem in January and SQLite released in March.
Suppose we had wanted to release Subversion in February, would we have
released with the umask bug, or patched our copy of SQLite or delayed
the release?
--
Philip Martin | Subversion Committer
WANdisco // *Non-Stop Data*