On 12/15/2014 11:11 PM, Paul wrote:
Hello, dear developersRecently 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.
Dan.
test.c
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#include <unistd.h>
#include <string.h>
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, &db) != SQLITE_OK)
return 1;
// Set busy timeout just in case...
if (sqlite3_busy_timeout(db, 10000) != 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 = 10000;"
" 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;
}
char buffer[256];
for (int i = 0; i < 100000; ++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 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;
}
snprintf(buffer, sizeof(buffer), "INSERT INTO bar(id, y) VALUES(%u,
%u)", i + 1, 2 * i + 3);
if (sqlite3_exec(db, buffer, NULL, NULL, NULL) != SQLITE_OK) {
sqlite3_close(db);
return 1;
}
#endif
}
// Commit transaction.
if (sqlite3_exec(db, "COMMIT;", NULL, NULL, NULL) != SQLITE_OK) {
sqlite3_close(db);
return 1;
}
}
// Create an outer transaction.
// Note: replacing transaction with another savepoint yields same
behaviour.
#ifndef NO_NESTED_TRANSACTION
// Open transaction.
if (sqlite3_exec(db, "BEGIN IMMEDIATE;", NULL, NULL, NULL) != SQLITE_OK) {
sqlite3_close(db);
return 1;
}
#endif
if (sqlite3_exec(db, "SAVEPOINT XXX;", NULL, NULL, NULL) != SQLITE_OK) {
sqlite3_close(db);
return 1;
}
// Now, inside a nested transaction we remove 'parent' table, so that
// rows from 'child' table will be removed automatically to satisfy FK
constraint.
sqlite3_stmt * statement = NULL;
const char * delete_query = "DELETE FROM foo WHERE id = ?001;";
if (sqlite3_prepare_v2(db, delete_query, strlen(delete_query) + 1,
&statement, NULL) != SQLITE_OK) {
sqlite3_close(db);
return 1;
}
for (int i = 0; i < 100000; ++i) {
// Bind id value.
if (sqlite3_bind_int(statement, 1, i) != SQLITE_OK) {
sqlite3_finalize(statement);
sqlite3_close(db);
return 1;
}
// Status must be SQLITE_DONE if everything is cool.
if (sqlite3_step(statement) != SQLITE_DONE) {
sqlite3_finalize(statement);
sqlite3_close(db);
return 1;
}
// Reset statement.
sqlite3_reset(statement);
sqlite3_clear_bindings(statement);
}
sqlite3_finalize(statement);
// 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;
}
#ifndef NO_NESTED_TRANSACTION
// Rollback transaction.
if (sqlite3_exec(db, "ROLLBACK;", NULL, NULL, NULL) != SQLITE_OK) {
sqlite3_close(db);
return 1;
}
#endif
sqlite3_close(db);
// Dump memory usage statistics.
int current_mem = 0;
int hi_mem = 0;
sqlite3_status(SQLITE_STATUS_MEMORY_USED, ¤t_mem, &hi_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
_______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
