RAM consumption inside a C++ program using Sqlite3 blob

Question

I'm using sqlite3 dbms inside a C++ program that I use mainly to store files as blob objects (I know that's not the best option).

Obviously I write them incrementally since they can be sometimes large (40-80MB) in order to do so I have to create first a placeholder of the blob using the binding function sqlite3_bind_zeroblob(...) and after that I open the blob writing and reading incrementally from and to it.

The problem I'm facing is that when i create the blob placeholder (during sqlite3_step) the RAM consumption of my app reaches 80-160MB for 2-3 seconds, once it has been created the RAM consumption goes back to 2-3MB at most.

I do not get why! If they created a way to write to a blob incrementally there is for sure a way to create that stupid placeholder without wasting 160MB of RAM, but I didn't find it. Have you got any suggestion?

sqlite3_stmt* stm = NULL;
sqlite3_blob *BLOB = NULL;

rc = sqlite3_prepare_v2(db, sql.c_str(), -1, &stm, NULL);

rc = sqlite3_bind_blob(stm, 1, wpath.c_str(), wpath.size()*sizeof(wchar_t), SQLITE_STATIC);
rc = sqlite3_bind_text(stm, 2, hash.c_str(), hash.size(), SQLITE_STATIC);
rc = sqlite3_bind_zeroblob(stm, 3, size);
rc = sqlite3_bind_int(stm, 4, versione);
rc = sqlite3_bind_blob(stm, 5, last.c_str(), last.size()*sizeof(wchar_t), SQLITE_STATIC);

rc = sqlite3_step(stm);

if (rc != SQLITE_DONE) {
    fprintf(stderr, " This file was already present in the database!\n", rc);
    return;
}
else {
    fprintf(stdout, "Record FILE created successfully\n");
}

May be this is cache usage for zero write, try to set cache size to minimum? — fghj
– fghj, Commented Nov 23, 2015 at 1:49
I modified the cache value but nothing seems to change. Anyway the sql query is the following std::string sql = "INSERT INTO FILES (PATH , HASH , DATI , VER, LAST) VALUES (?1, ?2, ?3, ?4,?5);"; — GalloCedrone
– GalloCedrone, Commented Nov 23, 2015 at 9:38
You're supposed to use sqlite3_blob_open & friends to benefit from incremental I/O. I don't see that in your code. — Simon Mourier
– Simon Mourier, Commented Dec 15, 2017 at 12:00
I wrote it in the question, first I need to create the placeholder (3rd parameter) then I open it and write with incremental I/O, the issue is that the creation of the placeholder wastes a lot of RAM. From the documentation --> " Zeroblobs are intended to serve as placeholders for BLOBs whose content is LATER written using incremental BLOB I/O routines. A negative value for the zeroblob results in a zero-length BLOB." Myabe you got confuse by the binding of the 5th parameter. — GalloCedrone
– GalloCedrone, Commented Dec 15, 2017 at 12:24

Rama · Accepted Answer · 2017-12-21 15:17:01Z

3

+50

It is an issue reported HERE.
And the oficial answer is:

In order for zeroblobs to work as above (using a fixed amount of memory no matter how big they are) all zeroblobs must be at the end of the row. In other words, the columns of the table that are receiving the zeroblobs must be the last columns in the table. If any non-zero content follows the zeroblob, then the zeroblob is expanded into a literal sequence of zero bytes, meaning memory must be allocated for the entire zeroblob.

So you need to change the order to fix it:

sqlite3_stmt* stm = NULL;
sqlite3_blob *BLOB = NULL;

rc = sqlite3_prepare_v2(db, sql.c_str(), -1, &stm, NULL);

rc = sqlite3_bind_blob(stm, 1, wpath.c_str(), wpath.size()*sizeof(wchar_t), SQLITE_STATIC);
rc = sqlite3_bind_text(stm, 2, hash.c_str(), hash.size(), SQLITE_STATIC);
rc = sqlite3_bind_int(stm, 3, versione);
rc = sqlite3_bind_blob(stm, 4, last.c_str(), last.size()*sizeof(wchar_t), SQLITE_STATIC);
rc = sqlite3_bind_zeroblob(stm, 5, size);

rc = sqlite3_step(stm);

if (rc != SQLITE_DONE) {
    fprintf(stderr, " This file was already present in the database!\n", rc);
    return;
}
else {
    fprintf(stdout, "Record FILE created successfully\n");
}

answered Dec 21, 2017 at 15:17

Rama

3,3052 gold badges13 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

GalloCedrone Over a year ago

Thanks a lot, I knew there were a explanation for that behaviour and a possible workaround!

Collectives™ on Stack Overflow

RAM consumption inside a C++ program using Sqlite3 blob

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related