Thank you for the explanation.
-----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Dan Kennedy Sent: Tuesday, July 25, 2017 2:21 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] High memory usage for in-memory db with nested transactions On 07/26/2017 12:58 AM, David Raymond wrote: > Would you elaborate a bit more on that? To my untrained-in-C eyes it looks > like there's the outer transaction, then a savepoint1, then a loop of > (savepoint2, update the only record, release savepoint2). Is savepoint2 there > not actually getting released each time? Wouldn't the outer transaction and > savepoint1 each only need to hold the original 2 pages? Which of those is/are > eating the memory? Is it an SQL problem or a C problem that's causing it? And > what's the correct way? It's an implementation artifact really. Each time a page is modified within a savepoint, if the page has not already been journalled within the current savepoint, a copy of it is appended to the statement journal. In case you do "ROLLBACK TO" to revert the change. But pages are never removed from the statement journal - except that if the number of open savepoint transactions drops to zero, the statement journal is truncated to zero bytes in size. So opening and closing lots of nested savepoints while writing to the database without ever closing the outermost savepoint can leave you with a statement journal many times the size of the original database. With the current implementation, the only way to avoid this is to ensure the outermost savepoint is closed from time to time. Dan. > > > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On > Behalf Of Richard Hipp > Sent: Tuesday, July 25, 2017 1:35 PM > To: SQLite mailing list > Subject: Re: [sqlite] High memory usage for in-memory db with nested > transactions > > > Because of the way you have structured your SAVEPOINTs, the statement > log (used to ROLLBACK TO a prior savepoint) must add at least one new > page for each of your 500K UPDATEs. When the database file is on > disk, the statement log is a temporary file on disk which you are not > noticing. But when the database file is ":memory:" the statement log > is also in memory. 500K transactions at 4KB per page accounts for > most of the 2GB of memory used. > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
