[GNUnet-developers] dropping support for SQlite from GNUnet due to bug? workaround?

[Christian Grothoff]

Hi everyone!

I've just been through some rather unnerving realization, which is that the 
SQlite DB binding for GNUnet has some serious issue: memory utilization. I'm 
writing here just to get some feedback, hoping that someone will step up and 
say "sure, no problem, do X and the problem will go away", but I am not very 
optimistic at this point. Anyway, it can't hurt to ask.

I first thought it was some kind of leak, a bad way we use the API or 
something.  However, that is not the case -- after stepping through the 
SQlite code with GDB I think it is no leak at all, but worse: a serious, in 
my opinion fatal SQlite limitation. What goes on is this.

GNUnet creates (over time), say a 1 GB database (which is not very big for us, 
and considering that SQlite is supposed to only use 256 bytes per 4 MB, this 
should be no problem). Then GNUnet executes a statement of the form:

SELECT * FROM gn070 WHERE condition ORDER BY criteria LIMIT 1;

Note that LIMIT 1 pretty much ensures that the result size is less than 64k of 
data.  What does SQlite do?  As far as I can tell, it loads the ENTIRE 
database into memory (using 1 GB+ of memory).  All of this happens during the 
simple, single "sqlite3_step" call which is supposed to produce the one 64k 
result row.  Not only is this killing in terms of IO performance but it is 
totally unacceptable for memory consumption.

The order by and condition criteria are all on elements for which SQlite was 
asked to keep an index (so a seemingly full search should never be required; 
but even if that was the case, it should obviously not load the entire DB 
into memory).  

I ran both with SQlite 3.2.1 and 3.2.2; same result.  I also disabled the 
pragma "temp_store=MEMORY", this seemed to have no effect.  This did probably 
not show up in GNUnet 0.6.x because we did not execute this particular type 
of query (but we need it now).

The code is at 

https://gnunet.org/svn/GNUnet/src/applications/sqstore_sqlite/

including a testcase (needs the rest of GNUnet to run, though; and the 
testcase only creates a relatively small DB, so the extreme memory 
utilization does not show up).

Please let me know if you have any suggestions for fixing this; if there are 
none, we'll have to kill SQlite support for 0.7.0 and go just with MySQL.

Happy hacking

Christian