#1 2021-05-15 15:35:36

okoba
Member
Registered: 2019-09-29
Posts: 106

SQLite memory usage

Hi,

I have a problem with SQLite memory status. I am trying to get the DB memory usage, but it will be always zero.
Do you have any idea why?
Also zero even with memory DB.
status64 results non-zero value for SQLITE_STATUS_PAGECACHE_OVERFLOW flag.


var
  DB: TSqlDataBase;
  Req: TSqlRequest;
  Current, HighWater: Int64;
  I: Integer;
begin
  DB := TSqlDataBase.Create('test.db');
  sqlite3.config(SQLITE_CONFIG_MEMSTATUS, Integer(True));
  WriteLn('VersionNumber: ', sqlite3.VersionNumber);

  DB.TransactionBegin;
  DB.Execute('CREATE TABLE IF NOT EXISTS "Test" ("ID" INTEGER NOT NULL PRIMARY KEY, "Value" INTEGER);');
  Req.Prepare(DB.DB, 'INSERT INTO "Test"("Value") VALUES(?);');
  with Req do
    for I := 1 to 10000 do
    begin
      Bind(1, I);
      Step;
      Reset;
    end;
  Req.Close;
  DB.Commit;
  WriteLn('RecordCount: ', DB.ExecuteNoExceptionInt64('SELECT COUNT(*) FROM Test;'));

  Current := 0;
  HighWater := 0;
  WriteLn('status64: ', sqlite3.status64(SQLITE_STATUS_MEMORY_USED, @Current, @HighWater, Integer(False)));
  WriteLn('Current: ', Current);
  WriteLn('HighWater: ', HighWater);
  WriteLn('memory_used: ', sqlite3.memory_used);
  WriteLn('memory_highwater: ', sqlite3.memory_highwater(Integer(False)));

  DB.Free;
end.                  

Offline

#2 2021-05-15 15:49:40

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: SQLite memory usage

Please don't put such code in the forum, as stated by the forum rules.

At library initialization, we replace the SQLite3 memory manager and directly redirect to RTL GetMem/FreeMem for performance.
So I guess the SQlite3 statistics are not available.

IIRC SQLite3 won't never use more memory than the number of cached pages you give it at initialization for each DB, plus some KB of transitory memory buffers.
A good read is https://sqlite.org/malloc.html

Offline

#3 2021-05-15 15:54:56

okoba
Member
Registered: 2019-09-29
Posts: 106

Re: SQLite memory usage

I thought that is not a huge code as rules stated.
If you mean ForceToUseSharedMemoryManager, I tried disabling that too, but no change.
I like to get status of the DB to have a monitor on the DB, that was one of the reasons I've ported many of these functions previously for the sqlite3 units.

Last edited by okoba (2021-05-15 15:56:20)

Offline

#4 2021-05-15 16:02:49

okoba
Member
Registered: 2019-09-29
Posts: 106

Re: SQLite memory usage

I like to have this for memory tables too, as it helps to monitor my cache.
May it be something in the amalgamation?

Offline

#5 2021-05-15 17:29:32

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: SQLite memory usage

This is at SQLite3 library initialization, as I wrote, in our main SQLite3 unit.

Offline

#6 2021-05-15 17:38:33

okoba
Member
Registered: 2019-09-29
Posts: 106

Re: SQLite memory usage

mormot.db.raw.sqlite3 has no initialization.
mormot.db.raw.sqlite3.static has one, at it creates TSqlite3LibraryStatic. In the Create, it calls ForceToUseSharedMemoryManager and I did disable it as a test an no changes.
Am I checking a wrong place?

Offline

#7 2021-07-19 12:00:54

okoba
Member
Registered: 2019-09-29
Posts: 106

Re: SQLite memory usage

@ab, can you please let me know if you meant ForceToUseSharedMemoryManager or something else? I tired to disable it and again SQLite report zero.
As documented (https://www.sqlite.org/c3ref/c_status_malloc_count.html) SQLITE_STATUS_MEMORY_USED retunes xSize results and I checked your versions, the result is non zero, so the issue may be somewhere else?
I like to have a check on memory usage and it seems the standard way for SQLite.

Offline

#8 2021-07-20 06:04:21

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: SQLite memory usage

There is a

#define SQLITE_DEFAULT_MEMSTATUS 0
// don't need any debug here, and don't even define sqlite3_status()

in sqlite3mc.c so the memory status is disabled at compilation of the SQLite3 c source code itself.

I guess you can re-enable it again by setting the SQLITE_CONFIG_MEMSTATUS in the new BeforeInitialization virtual method.

Offline

#9 2021-07-20 07:04:08

okoba
Member
Registered: 2019-09-29
Posts: 106

Re: SQLite memory usage

Great. I inherited TSqlite3LibraryStatic with and override the BeforeInitialization with this:

  procedure TCustomSqlite3LibraryStatic.BeforeInitialization;
  begin
    sqlite3.config(SQLITE_CONFIG_MEMSTATUS, Integer(True));
    inherited BeforeInitialization;
  end;         

Now it works.
I will make a pull request for the config comments and show how it must be used to prevent future misunderstandings.

Offline

#10 2021-07-20 07:59:30

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: SQLite memory usage

I guess I have already added some comments, in the last commits.

Offline

#11 2021-07-20 08:01:25

okoba
Member
Registered: 2019-09-29
Posts: 106

Re: SQLite memory usage

Yes and I tried to propose a pull request but it had conflicts so I need to redo and check again.
As I added Configuration Options comments too.

Offline

#12 2021-07-20 08:07:06

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: SQLite memory usage

Sorry for the cross-over.
smile

Offline

#13 2021-07-20 08:16:07

okoba
Member
Registered: 2019-09-29
Posts: 106

Re: SQLite memory usage

Nothing much, that was my fault anyway.
I made the pull request, please review.

Offline

#14 2021-07-20 09:33:42

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: SQLite memory usage

I have merged it, with some fixes and extensions.

Offline

#15 2021-07-20 09:51:42

okoba
Member
Registered: 2019-09-29
Posts: 106

Re: SQLite memory usage

Thank you. I will try to match the style better next time.

Offline

#16 2021-07-20 13:02:12

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: SQLite memory usage

No worry - for the comments, it was pointless details.

Offline

Board footer

Powered by FluxBB