Re: [sqlite] High memory usage for in-memory db with nested transactions

[David Raymond]

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

Re: [sqlite] High memory usage for in-memory db with nested transactions

[Brendan E. Coughlan]

> Memory usage tops out at 2,066,287,984 for me.

That's pretty much all there is for a normal 32bit Windows process (2GB 
in user mode, a litte of which may be taken by user mode parts of the 
OS, the other 2GB reserved for Kernel mode). I hear 32bit Linux 
programmers get another GB of user mode memory, but I've never done 
anything memory-intensive on Linux.



> 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.

So a RELEASEd savepoint can still take a page in the log? Is there an 
approved way to work around this?


Since this happens rarely in the original context I'm thinking about 
making it a temporary database as described at 
https://www.sqlite.org/inmemorydb.html. When most of the cache is unused 
(as it usually is) that would be (roughly) as fast as an in-memory db, 
right? If so, is there any way to influence where in the filesystem the 
empty temporary database is created? And, just to make sure, a temporary 
database would also get an on-disk statement log, right?


Greetings,
Brendan E. Coughlan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] High memory usage for in-memory db with nested transactions

[Dan Kennedy]

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

Re: [sqlite] High memory usage for in-memory db with nested transactions

[David Raymond]

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?


-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.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] High memory usage for in-memory db with nested transactions

[Richard Hipp]

On 7/25/17, Brendan E. Coughlan  wrote:
>
> I compiled it with SQLite 3.19.3 under Visual Studio 2017.
>
> If I supply test.db as a command line parameter / file name (and test.db
> doesn't exist yet) the size of test.db, the size of test.db.journal and
> the program's memory usage reach a combined maximum a little short of 2MB.

I get 288KB on Linux...

>
> If I replace the supplied file name by :memory:, the program uses all
> available memory and eventually runs into a memory allocation error.
>

Memory usage tops out at 2,066,287,984 for me.  That is 7000 times
more memory, though...

> Am I doing something wrong and if yes what?
>

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.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] High memory usage for in-memory db with nested transactions

[Brendan E. Coughlan]

We’re going to presume Window 10 with all updates to date installed unless you 
tell us otherwise.


Correct.

I don't think it really matters though, since the situation I simplified 
it from runs on Window 7 and is compiled with VS2008, so it's probably 
not very new.


Greetings,
Brendan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] High memory usage for in-memory db with nested transactions

[Simon Slavin]

On 25 Jul 2017, at 6:06pm, Brendan E. Coughlan  wrote:

> I compiled it with SQLite 3.19.3 under Visual Studio 2017.

We’re going to presume Window 10 with all updates to date installed unless you 
tell us otherwise.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] High memory usage for in-memory db with nested transactions

[Brendan E. Coughlan]

Hello everybody,

The included C-Program takes a database file name as a command line 
parameter.


I compiled it with SQLite 3.19.3 under Visual Studio 2017.

If I supply test.db as a command line parameter / file name (and test.db 
doesn't exist yet) the size of test.db, the size of test.db.journal and 
the program's memory usage reach a combined maximum a little short of 2MB.


If I replace the supplied file name by :memory:, the program uses all 
available memory and eventually runs into a memory allocation error.


Am I doing something wrong and if yes what?

Program:

#include 
#include 
#include "sqlite3.h"

void runOrCrash_str(sqlite3 *db, const char *sql)
{
int err = sqlite3_exec(db, sql, NULL, NULL, NULL);
if (SQLITE_OK != err)
{
printf("Error running %s: %s.", sql, sqlite3_errmsg(db));
exit(1);
}
}

void runOrCrash_stmt(sqlite3_stmt * const stmt)
{
int err = sqlite3_reset(stmt);
if (SQLITE_OK != err)
{
printf(
"Error resetting %s: %s.",
sqlite3_expanded_sql(stmt),
sqlite3_errmsg(sqlite3_db_handle(stmt))
);
exit(1);
}
err = sqlite3_step(stmt);
if (SQLITE_DONE != err)
{
printf(
"Error running %s: %s.",
sqlite3_expanded_sql(stmt),
sqlite3_errmsg(sqlite3_db_handle(stmt))
);
exit(1);
}
}

void finalizeOrCrash(sqlite3_stmt *stmt)
{
int err = sqlite3_finalize(stmt);
if (SQLITE_OK != err)
{
printf(
"Error resetting %s: %s.",
sqlite3_expanded_sql(stmt),
sqlite3_errmsg(sqlite3_db_handle(stmt))
);
exit(1);
}
}

sqlite3_stmt *getStmtOrCrash(sqlite3 *db, const char *sql)
{
sqlite3_stmt *rv = NULL;
int err = sqlite3_prepare_v2(db, sql, -1, , NULL);
if (SQLITE_OK != err)
{
printf("Error preparing statement %s: %s.", sql, 
sqlite3_errmsg(db));

exit(1);
}
return rv;
}

int main(int argc, char* argv[])
{
sqlite3 *db = NULL;
int err = SQLITE_OK;

if (2 == argc)
{
const char *fileName = argv[1];
err = sqlite3_open(fileName, );
}
else
{
printf("The database file name should be the only command line 
parameter.");

exit(1);
}
if (SQLITE_OK != err)
{
printf("Error opening the database: %d", err);
exit(0);
}

runOrCrash_str(db, "CREATE TABLE bla(content INTEGER);");
runOrCrash_str(db, "INSERT INTO Bla(content) VALUES(1)");
sqlite3_stmt * sp2 = getStmtOrCrash(db, "SAVEPOINT SP2;");
sqlite3_stmt * sp2Release = getStmtOrCrash(db, "RELEASE SP2;");
sqlite3_stmt * update = getStmtOrCrash(db, "UPDATE bla SET content 
= 1;");


runOrCrash_str(db, "BEGIN IMMEDIATE;");
runOrCrash_str(db, "SAVEPOINT SP1;");


for (int ii = 0; ii < 50; ++ii)
{
runOrCrash_stmt(sp2);
runOrCrash_stmt(update);
runOrCrash_stmt(sp2Release);
}


runOrCrash_str(db, "RELEASE SP1;");
runOrCrash_str(db, "COMMIT;");

finalizeOrCrash(sp2);
sp2 = NULL;
finalizeOrCrash(sp2Release);
sp2Release = NULL;
finalizeOrCrash(update);
update = NULL;
err = sqlite3_close(db);
if (SQLITE_OK != err)
{
printf("Error closing database: %s", sqlite3_errmsg(db));
exit(1);
}
db = NULL;

return 0;
}

Thanks in advance,
Brendan E. Coughlan


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users