[Csync2] recursive selects with sqlite

[Lars Ellenberg]

On Thu, Oct 23, 2008 at 09:36:04AM +0200, Art -kwaak- van Breemen wrote:
> 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.

great. I like those real world numbers.
thank you.

> > 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 :-).

I see what I can do.
... when I find the time later this year ;)

-- 
: Lars Ellenberg
: LINBIT | Your Way to High Availability
: DRBD/HA support and consulting http://www.linbit.com

DRBD® and LINBIT® are registered trademarks of LINBIT, Austria.