On Mon, Dec 15, 2014 at 11:11 AM, Paul <de...@ukr.net> wrote:
>
> Hello, dear developers
>
> Recently 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.
>
What happens if you omit the "PRAGMA temp_store=MEMORY" and "PRAGMA
journal_mode=MEMORY" and allow SQLite to store (necessary) recovery
information on disk? Does memory usage go down then?
> 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.
>
>
>
> 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
>
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
