Sorry for the delayed response. Was waiting for time to dig into this a little.
Nov 18, 2007 2:05 AM Wang Yun <[EMAIL PROTECTED]>:
> I insert rfc txt files into a full text search table, 4119 txt files are
> 188MB totally. After insert, database file is 443MB.
> Logic is below, it's not the real code.
I've attached a tcl script I used in experimenting with this. I'm
doing this on a Redhat 9 box (kernel 2.4.22 with some additional
patches), running on a local disk both for input and the database.
The version of rfc I'm using has 4756 files with 252M of data. I
compiled tclsqlite3 with flags:
-O6 -DNDEBUG=1 -DHAVE_FDATASYNC=1 -DSQLITE_CORE=1
-DSQLITE_ENABLE_FTS1=1 -DSQLITE_ENABLE_BROKEN_FTS1=1
-DSQLITE_ENABLE_FTS2=1 -DSQLITE_ENABLE_BROKEN_FTS2=1
-DSQLITE_ENABLE_FTS3=1 -DHAVE_DLOPEN=1
[Listing all this detail because various things can cause various
differences. The most-relevant parameters are probably -O6
-DNDEBUG=1.]
> This cost 154 seconds, I use fts2 and my PC is Intel 2.33GHz, 2 CPUs.
> If I don't use fts, just insert into normal table, will cost 11 seconds.
Your results are a bit worse than I'd expect. I get 6.2s for the
simple-table case, and about 45s for the fts3 case, not quite your 14x
slow-down. fts2 and fts3 should be nearly identical for this kind of
test.
Basic table, one transaction:
2.190u 1.060s 0:06.20 52.4% 0+0k 0+0io 393pf+0w
-rw-r--r-- 1 shess eng 239505408 Dec 4 11:15 dbs/baseline.db
Basic table, per-insert transaction:
3.280u 1.890s 0:34.19 15.1% 0+0k 0+0io 393pf+0w
-rw-r--r-- 1 shess eng 239505408 Dec 4 11:16 dbs/baseline.db
fts3, one transaction:
40.820u 1.550s 0:44.44 95.3%0+0k 0+0io 414pf+0w
-rw-r--r-- 1 shess eng 307900416 Dec 4 11:17 dbs/baseline.db
fts3, per-insert transaction:
61.300u 3.330s 1:43.04 62.7%0+0k 0+0io 414pf+0w
-rw-r--r-- 1 shess eng 348348416 Dec 4 11:21 dbs/baseline.db
I'm not seeing as much bloat as you describe - perhaps I'm using a
bigger page size.
> I don't know when sqlite will update the full text index, after each insert?
fts2/3 update the index after each transaction, and also before each
insert where an explicit rowid is less than the maximum rowid seen in
the current transaction, and also before any query run against the
table. In this case, it should only be updating at the end of the
transaction, and as needed when the in-memory table fills up.
> How can I improve the performance?
The optimal case for fts2/3 is to do many inserts per transaction,
letting the table select rowid/docid. In that case it will collect
the new data in memory and flush it to disk less frequently. For a
test of this size, it might also help to have a bigger page cache so
that SQLite doesn't have to flush the journal file to disk. You could
define kPendingThreshold in the fts3.c (or fts2.c) source code to be
larger (this doesn't seem to help me at all, though).
Without going in and profiling things, though, my guess is that the
time in this test is dominated by tokenization. I'm basing this on
the high CPU utilization, and the lack of impact from tweaking
kPendingThreshold and pragma cache_size. Possibly there's also some
cost from segment merges, though those should generally be fairly I/O
dominated.
-scott
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
