sqlite sqlite3_close() does not release the memory acquired

Question

We are trying to Integrate SQLite in our Application and are trying to populate as a Cache. We are planning to use it as a In Memory Database. Using it for the first time. Our Application is C++ based.

Our Application interacts with the Master Database to fetch data and performs numerous operations. These Operations are generally concerned with one Table which is quite huge in size. We replicated this Table in SQLite and following are the observations:

Number of Fields: 60 Number of Records: 1,00,000

As the data population starts, the memory of the Application, shoots up drastically to ~1.4 GB from 120MB. At this time our application is in idle state and not doing any major operations. But normally, once the Operations start, the Memory Utilization shoots up. Now with SQLite as in Memory DB and this high memory usage, we don’t think we will be able to support these many records.

Now when i close the db using sqlite3_close(), then the memory is not released? I even tried dropping the table, but still the memory remains high? What needs to be done so that sqlite releases the acquired memory and memory of the applications comes back to normal?

Answer 1

Forget the sqlite part of your question as this issue applies to any user process under Linux.

A process can grow its data segment by a system call to brk(2). In principle, a process could later release that memory by making the appropriate call to shrink the data segment. In practice, this is a fabulous way to create bus errors because it is very hard to ensure that pointers to the larger data space are never dereferenced.

However virtual memory comes to the rescue. There is a difference between the size of a process and the in-core resident set size (sz and rss respectively as shown by ps -F). For processes that have memory that is no recently being accessed, the rss can be much smaller than the sz, and for some processes that are just waiting for something to happen (e.g. inactive getty processes) the rss can be zero which means the whole process address space has been swapped out so that active programs can use the memory.

Getting back to your question, you don't explain why you want to use an in-memory database, but either way you run it, that table will wind up on disk either explicitly through sqlite with a disk-based store or through the virtual memory system.