date:20141217

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

2014-12-17 Thread Paul


> 
> I want to confirm that issue is fixed for me.
> Thanks again, Dan!
> 
> 
> Please ignore this update, patch fixes this problem as well.
> 
> > I want to add even more input for this issue.
> > I understand why there is implicit savepoint, when I remove row from 
> > 'parent' table.
> > But why is this also true for a 'child' table when I perform 'INSERT OR 
> > REPLACE'?
> > Removing FK reference disables journal growth. I don't understand...
> > 



At the end of the day my head was so big that I, having some ten different test 
cases,
have errourneously confirmed that 'INSERT OR REPLACE' is fixed also, by running 
wrong test.
But sadly it isn't. Here, I'll drop my test program again, for clarity.

Thanks and sorry for confusion.


test.c

#include 
#include 
#include 
#include 
#include 

int main(int argc, char ** argv)
{
const char * database_file = "/tmp/memusage_test_db";

// Clear old database file is there is one.
unlink(database_file);

sqlite3 * db = NULL;
if (sqlite3_open(database_file, ) != SQLITE_OK)
return 1;

// Set busy timeout just in case...
if (sqlite3_busy_timeout(db, 1) != SQLITE_OK) {
sqlite3_close(db);
return 1;
}

// Set pragmas.
if (sqlite3_exec(db,
 " PRAGMA page_size = 4096;"
 " PRAGMA temp_store = MEMORY;"
 " PRAGMA journal_mode = MEMORY;"
 " PRAGMA cache_size = 1;"
 " PRAGMA foreign_keys = ON;"
 " PRAGMA synchronous = OFF;",
 NULL, NULL, NULL) != SQLITE_OK) {
sqlite3_close(db);
return 1;
}

// Create database structure.
if (sqlite3_exec(db,
 "CREATE TABLE foo ("
 "  id  INTEGER,"
 "  x   INTEGER,"
 "  PRIMARY KEY(id)"
 ");"
 ""
 "CREATE TABLE bar ("
 "  id  INTEGER,"
 "  y   INTEGER,"
 "  PRIMARY KEY(id, y),"
 "  FOREIGN KEY(id) REFERENCES foo(id) ON DELETE CASCADE"
 ");",
 NULL, NULL, NULL) != SQLITE_OK) {
sqlite3_close(db);
return 1;
}

// Populate database with data.
{
// Open transaction.
if (sqlite3_exec(db, "BEGIN IMMEDIATE;", NULL, NULL, NULL) != 
SQLITE_OK) {
sqlite3_close(db);
return 1;
}

if (sqlite3_exec(db, "SAVEPOINT XXX;", NULL, NULL, NULL) != SQLITE_OK) {
sqlite3_close(db);
return 1;
}

char buffer[256];
for (int i = 0; i < 10; ++i) {

snprintf(buffer, sizeof(buffer), "INSERT INTO foo(id, x) VALUES(%u, 
%u)", i + 1, 2 * i + 1);
if (sqlite3_exec(db, buffer, NULL, NULL, NULL) != SQLITE_OK) {
sqlite3_close(db);
return 1;
}

#ifndef NO_CHILD_RECORDS
snprintf(buffer, sizeof(buffer), "INSERT OR REPLACE INTO bar(id, y) 
VALUES(%u, %u)", i + 1, 2 * i + 1);
if (sqlite3_exec(db, buffer, NULL, NULL, NULL) != SQLITE_OK) {
sqlite3_close(db);
return 1;
}
#endif

}

// Rollback savepoint (no need to commit anything).
if (sqlite3_exec(db, "ROLLBACK TO SAVEPOINT XXX;", NULL, NULL, NULL) != 
SQLITE_OK) {
sqlite3_close(db);
return 1;
}

// Commit transaction.
if (sqlite3_exec(db, "COMMIT;", NULL, NULL, NULL) != SQLITE_OK) {
sqlite3_close(db);
return 1;
}
}

sqlite3_close(db);

// Dump memory usage statistics.
int current_mem = 0;
int hi_mem = 0;
sqlite3_status(SQLITE_STATUS_MEMORY_USED, _mem, _mem, 0);

printf("Current mem: %u\nHi mem: %u\n", current_mem, hi_mem);

// Do not leave database file behind.
unlink(database_file);

return 0;
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

2014-12-17 Thread Paul


I want to confirm that issue is fixed for me.
Thanks again, Dan!


Please ignore this update, patch fixes this problem as well.

> I want to add even more input for this issue.
> I understand why there is implicit savepoint, when I remove row from 'parent' 
> table.
> But why is this also true for a 'child' table when I perform 'INSERT OR 
> REPLACE'?
> Removing FK reference disables journal growth. I don't understand...
> 
> I have a new test application.
> 
> As before, to compile: 
> # clang -o test -L/usr/local/lib -lsqlite3 test.c
> 
> Still, I do not understand, why does extra transaction (nested savepoint) 
> matters.
> 
> test.c
> 
> #include 
> #include 
> #include 
> #include 
> #include 
> 
> int main(int argc, char ** argv)
> {
> const char * database_file = "/tmp/memusage_test_db";
> 
> // Clear old database file is there is one.
> unlink(database_file);
> 
> sqlite3 * db = NULL;
> if (sqlite3_open(database_file, ) != SQLITE_OK)
> return 1;
> 
> // Set busy timeout just in case...
> if (sqlite3_busy_timeout(db, 1) != SQLITE_OK) {
> sqlite3_close(db);
> return 1;
> }
> 
> // Set pragmas.
> if (sqlite3_exec(db,
> " PRAGMA page_size = 4096;"
> " PRAGMA temp_store = MEMORY;"
> " PRAGMA journal_mode = MEMORY;"
> " PRAGMA cache_size = 1;"
> " PRAGMA foreign_keys = ON;"
> " PRAGMA synchronous = OFF;",
> NULL, NULL, NULL) != SQLITE_OK) {
> sqlite3_close(db);
> return 1;
> }
> 
> // Create database structure.
> if (sqlite3_exec(db,
> "CREATE TABLE foo ("
> " id INTEGER,"
> " x INTEGER,"
> " PRIMARY KEY(id)"
> ");"
> ""
> "CREATE TABLE bar ("
> " id INTEGER,"
> " y INTEGER,"
> " PRIMARY KEY(id, y),"
> " FOREIGN KEY(id) REFERENCES foo(id) ON DELETE CASCADE"
> ");",
> NULL, NULL, NULL) != SQLITE_OK) {
> sqlite3_close(db);
> return 1;
> }
> 
> // Populate database with data.
> {
> // Open transaction.
> if (sqlite3_exec(db, "BEGIN IMMEDIATE;", NULL, NULL, NULL) != SQLITE_OK) {
> sqlite3_close(db);
> return 1;
> }
> 
> if (sqlite3_exec(db, "SAVEPOINT XXX;", NULL, NULL, NULL) != SQLITE_OK) {
> sqlite3_close(db);
> return 1;
> }
> 
> char buffer[256];
> for (int i = 0; i < 10; ++i) {
> 
> snprintf(buffer, sizeof(buffer), "INSERT INTO foo(id, x) VALUES(%u, %u)", i + 
> 1, 2 * i + 1);
> if (sqlite3_exec(db, buffer, NULL, NULL, NULL) != SQLITE_OK) {
> sqlite3_close(db);
> return 1;
> }
> 
> #ifndef NO_CHILD_RECORDS
> snprintf(buffer, sizeof(buffer), "INSERT OR REPLACE INTO bar(id, y) 
> VALUES(%u, %u)", i + 1, 2 * i + 1);
> if (sqlite3_exec(db, buffer, NULL, NULL, NULL) != SQLITE_OK) {
> sqlite3_close(db);
> return 1;
> }
> #endif
> 
> }
> 
> // Rollback savepoint (no need to commit anything).
> if (sqlite3_exec(db, "ROLLBACK TO SAVEPOINT XXX;", NULL, NULL, NULL) != 
> SQLITE_OK) {
> sqlite3_close(db);
> return 1;
> }
> 
> // Commit transaction.
> if (sqlite3_exec(db, "COMMIT;", NULL, NULL, NULL) != SQLITE_OK) {
> sqlite3_close(db);
> return 1;
> }
> }
> 
> sqlite3_close(db);
> 
> // Dump memory usage statistics.
> int current_mem = 0;
> int hi_mem = 0;
> sqlite3_status(SQLITE_STATUS_MEMORY_USED, _mem, 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

2014-12-17 Thread Paul


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

Hi Dan!

Thank you very much! I am very grateful for such a quick fix!
You won't believe it, but I've just sent an update for 'INSERT OR REPLACE' few 
seconds ago :)

Best regards, 
Paul
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

2014-12-17 Thread Paul

I want to add even more input for this issue.
I understand why there is implicit savepoint, when I remove row from 'parent' 
table.
But why is this also true for a 'child' table when I perform 'INSERT OR 
REPLACE'?
Removing FK reference disables journal growth. I don't understand...

I have a new test application.

As before, to compile: 
# clang -o test -L/usr/local/lib -lsqlite3 test.c

Still, I do not understand, why does extra transaction (nested savepoint) 
matters.

test.c

#include 
#include 
#include 
#include 
#include 

int main(int argc, char ** argv)
{
const char * database_file = "/tmp/memusage_test_db";

// Clear old database file is there is one.
unlink(database_file);

sqlite3 * db = NULL;
if (sqlite3_open(database_file, ) != SQLITE_OK)
return 1;

// Set busy timeout just in case...
if (sqlite3_busy_timeout(db, 1) != SQLITE_OK) {
sqlite3_close(db);
return 1;
}

// Set pragmas.
if (sqlite3_exec(db,
 " PRAGMA page_size = 4096;"
 " PRAGMA temp_store = MEMORY;"
 " PRAGMA journal_mode = MEMORY;"
 " PRAGMA cache_size = 1;"
 " PRAGMA foreign_keys = ON;"
 " PRAGMA synchronous = OFF;",
 NULL, NULL, NULL) != SQLITE_OK) {
sqlite3_close(db);
return 1;
}

// Create database structure.
if (sqlite3_exec(db,
 "CREATE TABLE foo ("
 "  id  INTEGER,"
 "  x   INTEGER,"
 "  PRIMARY KEY(id)"
 ");"
 ""
 "CREATE TABLE bar ("
 "  id  INTEGER,"
 "  y   INTEGER,"
 "  PRIMARY KEY(id, y),"
 "  FOREIGN KEY(id) REFERENCES foo(id) ON DELETE CASCADE"
 ");",
 NULL, NULL, NULL) != SQLITE_OK) {
sqlite3_close(db);
return 1;
}

// Populate database with data.
{
// Open transaction.
if (sqlite3_exec(db, "BEGIN IMMEDIATE;", NULL, NULL, NULL) != 
SQLITE_OK) {
sqlite3_close(db);
return 1;
}

if (sqlite3_exec(db, "SAVEPOINT XXX;", NULL, NULL, NULL) != SQLITE_OK) {
sqlite3_close(db);
return 1;
}

char buffer[256];
for (int i = 0; i < 10; ++i) {

snprintf(buffer, sizeof(buffer), "INSERT INTO foo(id, x) VALUES(%u, 
%u)", i + 1, 2 * i + 1);
if (sqlite3_exec(db, buffer, NULL, NULL, NULL) != SQLITE_OK) {
sqlite3_close(db);
return 1;
}

#ifndef NO_CHILD_RECORDS
snprintf(buffer, sizeof(buffer), "INSERT OR REPLACE INTO bar(id, y) 
VALUES(%u, %u)", i + 1, 2 * i + 1);
if (sqlite3_exec(db, buffer, NULL, NULL, NULL) != SQLITE_OK) {
sqlite3_close(db);
return 1;
}
#endif

}

// Rollback savepoint (no need to commit anything).
if (sqlite3_exec(db, "ROLLBACK TO SAVEPOINT XXX;", NULL, NULL, NULL) != 
SQLITE_OK) {
sqlite3_close(db);
return 1;
}

// Commit transaction.
if (sqlite3_exec(db, "COMMIT;", NULL, NULL, NULL) != SQLITE_OK) {
sqlite3_close(db);
return 1;
}
}

sqlite3_close(db);

// Dump memory usage statistics.
int current_mem = 0;
int hi_mem = 0;
sqlite3_status(SQLITE_STATUS_MEMORY_USED, _mem, _mem, 0);

printf("Current mem: %u\nHi mem: %u\n", current_mem, hi_mem);

// Do not leave database file behind.
unlink(database_file);

return 0;
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

2014-12-17 Thread Dan Kennedy


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

Re: [sqlite] SQLITE_FTS3_MAX_EXPR_DEPTH and upgrading sqlite

2014-12-17 Thread Clemens Ladisch

Ed Willis wrote:
> We were on a version which did not have the compile option
> SQLITE_FTS3_MAX_EXPR_DEPTH and are moving up to one that does.  As it
> turns out we have run into a problem with one of our clients where
> they hit this limit now where previously the query just worked.

Have a look at 
.
The limit existed previously, but was hardcoded.  The introduction of
the option did _not_ change the default value of this limit.

> My question is whether or not theres any guidance on how to set this
> limit at compile time?

When you have more than 2^12 search terms, you need to increase it.
(Is that your problem?)

Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_FTS3_MAX_EXPR_DEPTH and upgrading sqlite

2014-12-17 Thread Dan Kennedy


On 12/16/2014 10:57 PM, Ed Willis wrote:

Hello all,

Apologies in advance if this question has been asked and answered elsewhere – a 
(brief, admittedly) search did not turn up anything and so I’m posting this.

We’re in the process of upgrading sqlite in our service.  We were on a version 
which did not have the compile option SQLITE_FTS3_MAX_EXPR_DEPTH and are moving 
up to one that does.  As it turns out we have run into a problem with one of 
our clients where they hit this limit now where previously the query just 
worked.  My question is whether or not there’s any guidance on how to set this 
limit at compile time?  Part of my confusion is that I’m not sure what would 
have happened previously with no limit enforced (as was the case on our older 
version of sqlite) - was the risk stack exhaustion and a resulting crash or was 
it something else entirely?

Basically what I’m worried about is that we'll raise the limit to allow this 
one client to do their queries as they used to do, but will not know that we’ve 
raised it enough to allow all our clients to get the same behavior they were 
accustomed to?  How do people choose this limit?


The point of the setting is to prevent stack overflow. I guess to get 
the old behavior, set it to a very large value.


Are you able to post the FTS query that caused a problem with the 
default settings? Just the MATCH expression will be enough, we don't 
need the table schema or contents.


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

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

Re: [sqlite] SQLITE_FTS3_MAX_EXPR_DEPTH and upgrading sqlite

Re: [sqlite] SQLITE_FTS3_MAX_EXPR_DEPTH and upgrading sqlite

7 matches

Site Navigation

Mail list logo

Footer information