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.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
