389762 - should we delay creation of indexes until after import of history/bookmarks from fx 2?

Status: RESOLVED WONTFIX

(Firefox :: Bookmarks & History, defect, P4)

Description

[(not reading, please use [e-mail] instead)]

creating indexes before import

looking over the patch from bug #319455, I noticed dietrich added the call to create the moz_places_titleindex index in three places, one of them being nsNavHistory::CreateLookupIndexes()

It appears the point of that is, for new profiles, to delay the creation of indexes until after we've imported.  (That makes sense, as we'll be doing a ton of writes during import.)

Running trunk on a fx 2 profile, here's what I see:

1) In nsNavHistory::InitDB(), we create the moz_places_titleindex index (and other indexes)
2) then we call nsNavHistory::CreateLookupIndexes(), which creates them again.  (note, we don't call "CREATE INDEX IF NOT EXISTS moz_places_titleindex ON moz_places (title)" but we should, and then we can check the return values

// nsNavHistory::CreateLookupIndexes
//
// This creates some indexes on the history tables which are expensive to
// update when we're doing many insertions, as with history import.  Instead,
// we defer creation of the index until import is finished.
//
//    FIXME: We should check if the index exists (bug 327317) and then not
//    try to create it. That way we can check for errors. Currently we ignore
//    errors since the indeices may already exist.

3) then in nsNavHistory::Init() we import from mork

4) then from nsBrowserGlue.js do we import bookmarks

I think we should:

a) confirm that not creating indexes will improve performance
b) if so, delay our index creation until after import from history / bookmarks.
c) then do "CREATE INDEX IF NOT EXISTS" where necessary so we can check return values.

Note, I don't believe this is the big import performance issue (bug #380134).  after chatting with sdwilsh a few weeks ago (about transactions and "PRAGMA synchronous = OFF", I think our big perf issue is something else.)

Comment 1

[(not reading, please use [e-mail] instead)]

> It appears the point of that is, for new profiles, to delay the creation of
> indexes until after we've imported.  (That makes sense, as we'll be doing 
> a ton of writes during import.)

actually, I might be assuming to much here.

from our real world testing / timing of bug #389876, dietrich and I found that creating indexes and then inserting into a table was faster than inserting into and then creating indexes.

so, we should test and verify which is faster for import / migration from fx 2.

we might still need to keep the CreateLookupIndexes(), but make it EnsureIndexes() and use CREATE INDEX IF NOT EXISTS, for some upgrade scenarios.  (not sure what those scenarios would be yet, however).

Comment 2

[(not reading, please use [e-mail] instead)]

our other perf import issue (bug #380134) is targeted for m8 as well.

Comment 3

[Vladimir Vukicevic [:vlad] [:vladv] (needinfo me, slow to respond)]

This does't seem to be a blocker any longer, especially based on comment #1.

Comment 4

[Dietrich Ayala (:dietrich)]

WONTFIX per comment #1.

Comment 5

[Gervase Markham [:gerv]]

Bug 451915 - move Firefox/Places bugs to Firefox/Bookmarks and History. Remove all bugspam from this move by filtering for the string "places-to-b-and-h".

In Thunderbird 3.0b, you do that as follows:
Tools | Message Filters
Make sure the correct account is selected. Click "New"
Conditions: Body   contains   places-to-b-and-h
Change the action to "Delete Message".
Select "Manually Run" from the dropdown at the top.
Click OK.

Select the filter in the list, make sure "Inbox" is selected at the bottom, and click "Run Now". This should delete all the bugspam. You can then delete the filter.

Gerv