FTS3 works by building small inverted indices and periodically merging the
smaller indices into larger indices. The mergers are incremental. But once
you get to the point of multi-gigabyte FTS3 tables and you are combining
100,000 inserts into a single transaction, you can end up with some very,
very large transactions.
The WAL must be big enough to contain an entire transaction. Since you are
creating larger and larger transactions, the WAL is getting bigger and
bigger.
I modified your script as follows:
file delete -force grow.db grow.db-wal grow.db-journal grow.db-shm
sqlite3 db grow.db
db eval {pragma cache_size=32000}
db eval {pragma journal_mode=WAL}
db eval {PRAGMA synchronous=normal}
db eval {CREATE VIRTUAL TABLE role USING fts3(id INTEGER PRIMARY KEY, uuid
TEXT);}
for {set j 1} {$j<=2000} {incr j} {
puts -nonewline "$j... "; flush stdout
db transaction {
for {set i 0} {$i<100000} {incr i} {
db eval {insert into role (uuid) values (hex(randomblob(16)))}
}
}
puts "[file size grow.db] [file size grow.db-wal] [file size grow.db-shm]"
}
I'm running it now. It is on iteration 235. Sizes are 2.148GB, 241MB, and
1.86MB, respectively. I'll keep it running to see if I can reproduce your
corrupt database problem....
2010/7/6 Alexey Pechnikov <pechni...@mobigroup.ru>
> Oh, yes, check null size for new database is incorrect in WAL mode. I
> replace this script by more simple:
> ==========================
> http://mobigroup.ru/files/test.tcl
>
> #!/usr/bin/tclsh8.5
> package require sqlite3
>
> sqlite3 db grow.db
> db eval {pragma cache_size=32000}
> db eval {pragma journal_mode=WAL}
> db eval {PRAGMA synchronous=normal}
>
> db eval {CREATE VIRTUAL TABLE role USING fts3(id INTEGER PRIMARY KEY, uuid
> TEXT);}
>
> for {set j 1} {$j<=200} {incr j} {
> puts $j
> db transaction {
> for {set i 0} {$i<100000} {incr i} {
> db eval {insert into role (uuid) values (hex(randomblob(16)))}
> }
> }
> }
>
> db close
> ==========================
>
> After step between 40 and 45 WAL journal starts grow:
>
> $ ls -lh|grep grow
> -rw-r--r-- 1 veter veter 358M Июл 7 07:09 grow.db
> -rw-r--r-- 1 veter veter 32K Июл 7 07:08 grow.db-shm
> -rw-r--r-- 1 veter veter 23M Июл 7 07:09 grow.db-wal
> $ ls -lh|grep grow
> -rw-r--r-- 1 veter veter 358M Июл 7 07:09 grow.db
> -rw-r--r-- 1 veter veter 32K Июл 7 07:08 grow.db-shm
> -rw-r--r-- 1 veter veter 105M Июл 7 07:09 grow.db-wal
> $ ls -lh|grep grow
> -rw-r--r-- 1 veter veter 374M Июл 7 07:09 grow.db
> -rw-r--r-- 1 veter veter 256K Июл 7 07:09 grow.db-shm
> -rw-r--r-- 1 veter veter 228M Июл 7 07:09 grow.db-wal
>
> There is only single process:
>
> $ ls -lh|grep grow
> -rw-r--r-- 1 veter veter 862M Июл 7 07:11 grow.db
> -rw-r--r-- 1 veter veter 256K Июл 7 07:10 grow.db-shm
> -rw-r--r-- 1 veter veter 254M Июл 7 07:11 grow.db-wal
> $ fuser grow.db
> grow.db: 16190
> $ ps aux|grep 16190|grep -v grep
> veter 16190 84.8 3.2 304372 273136 pts/1 Dl+ 07:07 3:48
> /usr/bin/tclsh8.5 ./test.tcl
> $ ls -lh|grep grow
> -rw-r--r-- 1 veter veter 862M Июл 7 07:11 grow.db
> -rw-r--r-- 1 veter veter 256K Июл 7 07:10 grow.db-shm
> -rw-r--r-- 1 veter veter 254M Июл 7 07:11 grow.db-wal
>
> --
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
---------------------
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
