Hi Dan. > On 12/15/2014 11:59 PM, Dan Kennedy wrote: > > On 12/15/2014 11:11 PM, Paul wrote: > >> Hello, dear developers > >> > >> Recently I've stumbled upon a very rare and strange bug. > >> The result of this is abnormal memory usage, that does not allow us > >> to remove > >> fair number of rows from a table due to the limit of memory, > >> available for 32bit > >> process. This is strange, because database size is somewhat small: 79M. > >> Digging around I finally managed to pinpoint when exactly does this > >> issue occur. > >> Another stange thing, though is that memory is successfully deallocated, > >> bacause no matter what, valgrind does not report definitely lost memory. > >> > >> I want to present you my test case. You have two options to > >> manipulate it. > >> Two defines: > >> - NO_NESTED_TRANSACTION desables nested transaction. > >> - NO_CHILD_RECORDS disables population of 'child' table with data. > >> > >> To compile: > >> # clang -o test -L/usr/local/lib -lsqlite3 test.c > >> > >> My results > >> > >> Without defines: > >> # clang -o test -L/usr/local/lib -lsqlite3 test.c > >> # ./test > >> Current mem: 0 > >> Hi mem: 1294136920 > >> > >> Not okay, 1.2GiB peak memory usage. > >> > >> > >> With NO_CHILD_RECORDS > >> # clang -o test -DNO_CHILD_RECORDS -L/usr/local/lib -lsqlite3 test.c > >> # ./test > >> Current mem: 0 > >> Hi mem: 421141176 > >> > >> A bit better, but still not ok. > >> > >> > >> With NO_NESTED_TRANSACTION: > >> # clang -o test -DNO_NESTED_TRANSACTION -L/usr/local/lib -lsqlite3 > >> test.c > >> # ./test > >> Current mem: 0 > >> Hi mem: 15100760 > >> > >> Seems ok. > >> > >> > >> With NO_NESTED_TRANSACTION and NO_CHILD_RECORDS: > >> # clang -o test -DNO_NESTED_TRANSACTION -DNO_CHILD_RECORDS > >> -L/usr/local/lib -lsqlite3 test.c > >> # ./test > >> Current mem: 0 > >> Hi mem: 2554168 > >> > >> No doubt it's even better. > > > > The memory is being used by the statement journal, which you have in > > memory. If the app did not set "journal_mode=memory" and > > "temp_store=memory", SQLite would create a really large temp file > > instead of using memory. Which would still be sub-optimal, but might > > not run into the 32-bit limit. > > > > The reason the statement journal is growing so large is that SQLite > > only truncates the statement journal when the outermost > > sub-transaction is closed. Otherwise it just keeps on appending. i.e. > > > > BEGIN; > > SAVEPOINT xxx; > > ... > > SAVEPOINT yyy; > > ... > > RELEASE yyy; -- does not truncate statement journal > > COMMIT xxx; -- truncates statement journal > > COMMIT; > > > > Your example has one explicit sub-transaction (equivalent to xxx) and > > each DELETE statement is opening a second, implicit, sub-transaction > > (equivalent to yyy). > > > > With the child records included, each DELETE statement is modifying 3 > > db pages - one from table "bar", one from table "foo" and one from the > > PRIMARY KEY index on "foo". 100,000 deletes, 4Kib page size, 3 pages > > per delete == 1.2GiB. Or without the child records, just 1 page > > modified per delete, so closer to 400MiB of memory. Without the > > sub-transaction, the implicit sub-transaction created by each DELETE > > becomes the outermost and so the statement journal doesn't grow much > > at all. So not much memory used in that case. > > Another idea would be to use a deferred foreign key constraint. That way > the DELETE operations will not need the statement journal at all. >
How can I get around implicit savepoint creation? Why doesn't savepoint commit truncate a journal? Why does journal grow even when there is nothing to delete in bar? Currently this limitation renders use of sqlite impossible, unless using ugly hacks. >From the user's perspective, this overhead is unimaginable. This is completely normal use of SQL yet overhead is above the wildest imagination :( _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
