[svn.haxx.se] · SVN Dev · SVN Users · SVN Org · TSVN Dev · TSVN Users · Subclipse Dev · Subclipse Users · this month's index

Re: svn commit: r1296868 - /subversion/trunk/subversion/libsvn_fs_fs/rep-cache-db.sql

From: Trent Nelson <trent_at_snakebite.org>
Date: Tue, 6 Mar 2012 12:11:57 -0800

On 3/6/12 5:35 PM, "Philip Martin" <philip.martin_at_wandisco.com> wrote:

>Philip Martin <philip.martin_at_wandisco.com> writes:
>
>> It may be TEXT but it is also PRIMARY KEY and according to the SQLite
>> docs:
>>
>> http://sqlite.org/lang_createtable.html
>>
>> INTEGER PRIMARY KEY columns aside, both UNIQUE and PRIMARY KEY
>> constraints are implemented by creating an index in the database (in
>> the same way as a "CREATE UNIQUE INDEX" statement would). Such an
>> index is used like any other index in the database to optimize
>> queries. As a result, there often no advantage (but significant
>> overhead) in creating an index on a set of columns that are already
>> collectively subject to a UNIQUE or PRIMARY KEY constraint.
>
>If I create a repository using 1.7 and look at the rep-cache.db I see:
>
>$ sqlite3 rep-cache.db "select * from sqlite_master" | grep index
>index|sqlite_autoindex_rep_cache_1|rep_cache|4|
>
>An index has been created automatically and so adding another index can
>only slow things down.

Yeah this is interesting; you've provided a wealth of information contrary
to everything I said to Daniel yesterday. I did some SQLite index tuning
a month ago and I could have sworn having a TEXT field as primary key
inhibited index creation -- but, as you've demonstrated with your explain
plan and sqlite_master query, that's clearly not the case.

To rewind things a little: I was manually repairing my asf mirror
yesterday that happened to sync earlier last week at the wrong time and
picked up a dodgy revision. I manually deleted the affected rows from
rep-cache.db and noticed that all my select queries seemed to be taking an
inordinate amount of time to complete (5s+ at least). I explain plan the
problematic query, saw no indexes, created one manually, and wallah,
problem solved, all queries returned instantly and explain plan showed
index usage.

I've got a bunch of zfs snapshots of the repo I can have a play around
with tomorrow to see if I can replicate.

Plausible theories off the top of my head:

a) There's something different with my env and indexes were not being
created automatically. (I do remember having a lot of trouble getting the
asf repo to load from dumps and then complete from subsequent syncs.)
b) There's something else going on.

(Plausible theories? Yes. Good theories? Debatable ;-)

        Trent.
Received on 2012-03-06 21:12:52 CET

This is an archived mail posted to the Subversion Dev mailing list.