On 2 May 2014 10:22, Dan Kennedy <danielk1...@gmail.com> wrote:
>
>
> A query like:
>
>   SELECT level, count(*) AS ntree FROM yourftstablename_segdir;
>
> will tell you how many b-trees there currently are at each level. Which
> might help you figure out what is going on and when you might expect
> a merge to actually start removing data from the index. More on the
> schema of the underlying data structure used by the FTS index here:
>
>   http://www.sqlite.org/fts3.html#section_9
>
>
I ran my accelerated test for about 50 hours. At the end of the test I had
a database with (393861 pages) 31.% of the space occupied by the index;
Level | # of trees
0|3
1|6
2|5
3|6
4|4
5|5
6|2
7|1
8|1

If I then manually ran merge=100,2 until it stops having an effect (my app
was using merge=300,8) I reduce the index size to (352121) 27.7% with;
Level | # of trees
0|1
1|1
4|1
5|1
8|1
9|1

Running optimise shows that this could be reduced to (145200 pages) 11.4%.
So I had about three times the optimal amount of index data.

I can see that Dan's suggestion of preventing a merge spawning a Level-N+1
b-tree could prevent this growth. I was also wondering if a new merge
function that allows the user to force merging between levels in the index
might be worthwhile.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to