TZQuery memory issues (possible leaks)

[sftf]

Lazarus 1.2.6+ZEOSDBO-7.1.4+PostgreSQL

Consider simple project (code below).
With TZReadOnlyQuery application memory usage after open/close cycles is the same (don't grows).

With TZQuery application memory usage grows with each open/close cycle and I can't find any way to free that memory.
There is the same issue if I create/free all objects dynamically in the code.

See screenshots in attachments:
before ZQuery1.Open - 19640K (1-zquery-before-open.PNG)
after ZQuery1.Open - 34224K (2-zquery-after-open.PNG)
after DBGrid1.DataSource := DataSource1 - 58020K (3-zquery-after-datasource.PNG)
after ZQuery1.Close - 22729K (4-zquery-after-close.PNG)

With TZQuery, for example, after 3 open/close cycles I have 46288K memory usage instead of the initial 19640K or about.

Unlike TZReadOnlyQuery with TZQuery there is additional memory grows on execution of

Code: Select all

DBGrid1.DataSource := DataSource1

Code: Select all

unit zquery_main;

{$mode objfpc}{$H+}

interface

uses
    Classes, SysUtils, db, FileUtil, Forms, Controls, Graphics, Dialogs,
    DBGrids, StdCtrls, ZConnection, ZDataset;

type

    { TForm1 }

    TForm1 = class(TForm)
        Button1: TButton;
        Button2: TButton;
        Button3: TButton;
        Button4: TButton;
        Button5: TButton;
        DataSource1: TDataSource;
        DBGrid1: TDBGrid;
        ZConnection1: TZConnection;
        ZQuery1: TZQuery;
        ZReadOnlyQuery1: TZReadOnlyQuery;
        procedure Button1Click(Sender: TObject);
        procedure Button2Click(Sender: TObject);
        procedure Button3Click(Sender: TObject);
        procedure Button4Click(Sender: TObject);
        procedure Button5Click(Sender: TObject);
    private
        { private declarations }
    public
        { public declarations }
    end;

var
    Form1: TForm1;

implementation

{$R *.lfm}

{ TForm1 }

procedure TForm1.Button1Click(Sender: TObject);
begin
    ZConnection1.HostName:='192.168.0.8';
    ZConnection1.Protocol:='postgresql-9';
    ZConnection1.Database:='du';
    ZConnection1.User:='du_admin';
    ZConnection1.Password:='du_admin';
    ZConnection1.UseMetadata:=false;
    ZConnection1.AutoCommit := false;
    ZConnection1.Connect;
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
    DataSource1.DataSet := ZQuery1;
    ZQuery1.Connection := ZConnection1;
    ZQuery1.SQL.Clear;
    ZQuery1.SQL.Add('select * from contracts;');
    ZQuery1.Prepare;
    ZQuery1.Open;
    DataSource1.DataSet := ZQuery1;
    DBGrid1.DataSource := DataSource1;
end;

procedure TForm1.Button4Click(Sender: TObject);
begin
    ZQuery1.Close;
end;

procedure TForm1.Button5Click(Sender: TObject);
begin
    DataSource1.DataSet := ZReadOnlyQuery1;
    ZReadOnlyQuery1.Connection := ZConnection1;
    ZReadOnlyQuery1.SQL.Clear;
    ZReadOnlyQuery1.SQL.Add('select * from contracts;');
    ZReadOnlyQuery1.Prepare;
    ZReadOnlyQuery1.Open;
    DataSource1.DataSet := ZReadOnlyQuery1;
    DBGrid1.DataSource := DataSource1;
end;

procedure TForm1.Button3Click(Sender: TObject);
begin
    ZReadOnlyQuery1.Close;
end;

end.

[EgonHugeist]

Could you try to use:

Code: Select all

TZDataSet.Unprepare.

After closing? We keep the prepared stmt alive until SQL changes. If this helps the memory consumtion happens in libpg.dll
Note i did check for memoryleaks and there are none inside Zeos-Code

[sftf]

Back to the subject...
Recently I switch to using dbc layer of ZeosLib insteed of datasets, but with the same issue:
with SetResultSetConcurrency(rcUpdatable) some memory is not freed.
With SetResultSetConcurrency(rcReadOnly) memory freeing seems ok.
While debugging ZeosLib I could not figure out what class is responsible for allocating/freeing that memory.
At begin I suspect it was TZCachedResultSet, but it free allocated memory at first glance.

Could you help to understand what ZeosLib class(es) responsible for allocating/freeing memory in rcUpdatable mode.

I wrote example Lazarus dbc application - it just connect to database, execute query, go last record and close ResultSet (with ZeosLib-7.1.4-stable). Download reset-updateble.zip at https://yadi.sk/d/gmsAEezejPrXK

In rcReadOnly mode
1. Compile with in Statement.SetResultSetConcurrency(rcReadOnly) in ru_main.pas and run.
2. After connect to database reset-updatable.exe process use 18596K.

rcreadonly-connected.PNG

3. After execute query process use 32828K.

rcreadonly-opened1.PNG

4. After close resultset process use 19280K which is pretty close to 18596K.

rcreadonly-closed1.PNG

5. After execute query 2nd time process use 32860K.

rcreadonly-opened2.PNG

6. After close resultset process use 18808K which again is pretty close to 18596K.

rcreadonly-closed2.PNG

7 And so on...
What we see: after each open-close iteration memory usage drops to initial value - so all memory allocated during query is freed.

Now compiled with statement's rcUpdatable mode
1. Compile with in Statement.SetResultSetConcurrency(rcUpdatable) in ru_main.pas and run.
2. After connect to database reset-updatable.exe process use 18628K.


3. After execute query process use 56000K: NativeResultSet+CachedResultSet - its ok.


4. After close resultset process use 21684K.


5. After execute query 2nd time process use 56980K.


6. After close resultset process use 35364K (why not about 21684K ?).


7. After execute query 3d time process use 57812K.


8. After close resultset process use 43508K (why not about 21684K ?).

What we see: after each open-close iteration memory usage never drops to initial value, which should be approximately equal to the memory usage right after connection was established.
What ZeosLib code responsible for freeing that 43508K-21684K=21824K ?
How I can free this sensible amount of memory?

I disagree with yours "If this helps the memory consumption happens in libpg.dll" - its not libpg.dll issue.
When ResultSet with rcUpdatable created we have NativeResultSet and CachedResultSet in memory.
When we moves to some row CachedResultSet fetch (simply copy) this row from NativeResultSet.

Code: Select all

function TZPostgreSQLStatement.CreateResultSet(const SQL: string;
  QueryHandle: PZPostgreSQLResult): IZResultSet;
var
  NativeResultSet: TZPostgreSQLResultSet;
  CachedResultSet: TZCachedResultSet;
  ConnectionHandle: PZPostgreSQLConnect;
begin
  ConnectionHandle := GetConnectionHandle();
  NativeResultSet := TZPostgreSQLResultSet.Create(FPlainDriver, Self, SQL,
  ConnectionHandle, QueryHandle, ChunkSize);

  NativeResultSet.SetConcurrency(rcReadOnly);
  if GetResultSetConcurrency = rcUpdatable then
  begin
    CachedResultSet := TZCachedResultSet.Create(NativeResultSet, SQL, nil, ConSettings);
    CachedResultSet.SetConcurrency(rcUpdatable);
    CachedResultSet.SetResolver(TZPostgreSQLCachedResolver.Create(
      Self,  NativeResultSet.GetMetadata));
    Result := CachedResultSet;
  end
  else
    Result := NativeResultSet;
end;

Now when we close ResultSet, opened in rcUpdatable mode, code shown below executed:

Code: Select all

procedure TZCachedResultSet.Close;
begin
  inherited Close;
  ColumnsInfo.Clear;
  If Assigned(FResultset) then
    FResultset.Close;
  FResultSet := nil;
end;

Here:
inherited Close call TZAbstractCachedResultSet.Close where CachedResultSet freed.
And the FResultset.Close close NativeResultSet (in my case - TZPostgreSQLResultSet).
Also, debugging TZCachedResultSet.Close shows me that closing NativeResultSet freeing NativeResultSet's memory as expected.

Lets see at closing NativeResultSet:

Code: Select all

procedure TZPostgreSQLResultSet.Close;
begin
  if FQueryHandle <> nil then
    FPlainDriver.Clear(FQueryHandle);
  FHandle := nil;
  FQueryHandle := nil;
  inherited Close;
end;

procedure TZPostgreSQLBaseDriver.Clear(Res: PZPostgreSQLResult);
begin
  POSTGRESQL_API.PQclear(Res);
end;

Here POSTGRESQL_API.PQclear(Res) free memory, allocated for results of our query.
Res is just pointer (PPGresult) to memory region allocated by libpg.dll.

So after all memory allocated by our ResultSet (which is the sum of NativeResultSet and CachedResultSet) should freed and memory usage should be like before executing this query but it's not...

[marsupilami]

Hello stft,

puh - that one should be hard. I am interested in fixing this too because I use PostgreSQL for some of my projects. Please give me some time to read into this.
With best regards,

Jan

[sftf]

Some extra debugging with rcUpdatable shown the following.
1. After connection etablished memory usage is 21336K.
2. After receiving data from server into NativeResultSet memory usage is 35612K, so data size is about 14276K.
3. Now we go to the last record of NativeResultSet. During this all records copied from NativeResultSet into CachedResultSet and memory usage rises to 56236K. So CachedResultSet size is about 20624K.
That's ok.
So we have approximately:
NativeResultSet size = 14276K - memory region allocated by TPQexec = function(Handle: PPGconn; Query: PAnsiChar): PPGresult; cdecl; in ZPlainPostgreSqlDriver.pas, which is imported from libpq.dll,
CachedResultSet size = 20624K - memory allocated by TZCachedResultSet.Fetch function: for each row copied from NativeResultSet TZCachedResultSet.Fetch allocate memory with TZRowAccessor helper class.

4. Now we call ResultSet.Close.
5. CachedResultSet is closed in TZAbstractCachedResultSet.Close and memory usage drops only to 49316K, but should to about 56236K-20624K=35612K
6. Than, after NativeResultSet is closed, memory usage drops to 35500K. So 49316K-35500K=14016K freed which approximately equal to allocated 14276K in step #2.

These numbers show me:
- that in step #5 less memory is freed than it should. And I couldn't find class methods to force that memory freeing;
- closing NativeResultSet in step #6 freeing all memory allocated for it in step #2

[sftf]

I have reviewed the source code and could not find any problems with memory allocating/freeing.

Each row is represented by a record TZRowBuffer which stores row values in 'Columns: TZByteArray' field by offsets.
Strings and blobs not stored directly in row: for each string/blob TZAbstractBlob is created.
TZAbstractBlob allocate memory for string/blob data and TZAbstractBlob (pointer) stored in row at corresponding offset.

For each row memory is allocated with RowAccessor.Alloc call in TZCachedResultSet.Fetch during copying row from NativeResultSet into CachedResultSet.
For each row in CachedResultSet memory is freed In TZAbstractCachedResultSet.Close with FRowAccessor.DisposeBuffer(PZRowBuffer(FRowsList)) call.

For each blob memory is allocated in TZAbstractBlob.CreateWithStream called from TZAbstractResultSet.GetBlob called from TZCachedResultSet.Fetch (RowAccessor.SetBlob(I, ResultSet.GetBlob(I)) call).

For each blob memory is freed in TZAbstractCachedResultSet.Close -> TZRowAccessor.DisposeBuffer -> TZRowAccessor.ClearBuffer -> TZRowAccessor.SetBlobObject (line IZBlob(BlobPtr^) := nil) -> TZAbstractBlob.Destroy -> TZAbstractBlob.Clear.
So It looks like everything is OK (except application memory usage).

My next guess was about runtime FPC memory manager: perhaps its strategy - to not return the previously allocated memory to OS (something like keep heap size for future allocations).
I wrote simple application which GetMem-FreeMem about 1,000,000 of 17 bytes blocks several times.
Also I use SysGetFPCHeapStatus to determine heap size before/after allocation and after freeing.
And with it I got similar application memory usage pattern:
usage in task manager was 16428K-57644K-21432K-58064K- 25972K-58092K-22752K-58096K-42044K-58128K-42476K...
But heap size returned by SysGetFPCHeapStatus is stable: 13041644 bytes after FreeMem all blocks and 50003968 bytes after GetMem all blocks.

From this I concluded:
- there is no memory leaks in TZCachedResultSet class;
- It does not look like it's FPC memory manager behavior (more investigations is needed...);
- looks like it's OS memory management feature.