On 12/16/2014 03:08 PM, Paul wrote:
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 :(
Also, I don not understand, how does it become outermost? Journal vener grows
if there is only single transaction (or savepoint) aroun 'delete loop'.
Why in case of just single transaction around deletes this does not happen?
Are you saying there is no YYY savepoint? Or the journal can be truncated
when omiting BEGIN oe XXX but not when they are both present?
Please don't mind my last message.
I understand now, what is going on. Yet this limitation is pretty depressing.
Is there no way in the future for things to change?
Can't journal be truncated, or modified pages be merged, after each
consequential
implicit sub-transaction (YYY) release, while they are still in the cache?
Is there any hope for me, except deferred FKs or DELETE FROM foo WHERE id IN
(...)?
There is now an update on the fossil trunk that should fix the problem
with ON DELETE CASCADE:
http://www.sqlite.org/src/info/8c5dd6cc259e0cdaaddaa52ccfa96fee6b166906
Dan.
Best regards,
Paul
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
