[Csync2] recursive selects with sqlite

[Art -kwaak- van Breemen]

Hello,
On Wed, Oct 22, 2008 at 08:16:38PM +0200, Lars Ellenberg wrote:
> can you please repeat these test, for four variations:
>   > /dev/null
>   export LANG=C LC_ALL=C ; > /dev/null
>   | wc -l 
>   export LANG=C LC_ALL=C ; | wc -l
> I expect some interessting results...

Well, yes, the difference between with or without wc is about
80ms ;-).
The lang settings were already undefined, so defaulting to C, but
I've exported them anyways.

The numbers:
+ sqlite3 /var/lib/csync2/lain.db3 'select count(1) from file;select count(1) from dirty;select count(1) from hint;'
10775968
0
0
lain:~# ls -hal /var/lib/csync2/lain.db3 
-rw-r--r-- 1 root root 2.2G Oct 23 09:14 /var/lib/csync2/lain.db3


I've reversed the testing order: union goes before or test, but
it really doesn't matter with 4G :-).


| file                         | rows   | union-wc       | union           | or-wc               | or
| /asdf/1                      | 0      | 0.003/0/0      | 0.002/0/0       | 8.649/7.120/1.520   | 8.522/6.870/1.560
| 0.big.gen.jpg                | 1      | 0.003/0.010/0  | 0.002/0/0       | 11.966/10.270/1.700 | 11.899/10.320/1.570
| 0                            | 841344 | 1.049/0.9/0.13 | 0.988/0.92/0.07 | 12.347/10.870/1.480 | 12.253/10.660/1.570
| 0/0/0/10012000.2.big.gen.jpg | 1      | 0.029/0/0      | 0.003/0/0       | 11.927/10.310/1.600 | 12.086/10.690/1.400
| 0/0/0                        | 8726   | 0.017/0.010/0  | 0.012/0.010/0   | 12.379/10.630/1.720 | 11.878/10.430/1.450

So, what probably is happening is that sqlite somehow manages to optimize
the query into dumping table "file" into a temporary table, and performing
the select on that, and joins that with the other match, or something similar.
Irrespective of the resultset it seems to touch the entire file table
instead of doing what we want it to do.
For the union case, it will probably do the selects consecutively, and
output them immediately.
The fun part of course is that the seperate queries are fast.
If you don't like the union-part, the biggest optimization is to split the
query in two, which actually makes it 2 times faster than the union-all
8-D.

> won't hurt to know for both sqlite3 and sqlite2.
Well, I had my csync2 day yesterday, I will probably have no time until
next week :-(.
/me needs to find a testing system.
Those numbers above are from live systems ;-0.

Ow, btw: the csync2id.pl that's actually working is attached.
Now I hope that someone that does know how to program perl will fix it, or
rewrite it into something beautiful :-).
-- 
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing in email?
-------------- next part --------------
A non-text attachment was scrubbed...
Name: csync2id.pl
Type: text/x-perl
Size: 6232 bytes
Desc: not available
Url : http://lists.linbit.com/pipermail/csync2/attachments/20081023/54e6420d/csync2id-0001.pl