Growing amount of the memory used on server

ASiwon · 2015-10-05 20:52:32

Hello,

I have problem with my mORMot powered serwer. I have two applications: server and client using mORMot written in Delphi XE4. Server use Oracle 11 SEO database server (with TSQLDBOracleConnectionProperties from SynDbOracle unit) Client reads a lot of data (2,5 milion rows during few hours) from server. There is no any services on server - just reading data via JSON RestFull Client-Server mechanism. Data reading is divided on portions about 100.000 reads per one logical session. Each session starts every 5 minutes. After each session amount of memory used by the server process grows. In extremal situation process is using 2GB memory and for every data request returns exception: HTTP/1.1 500 Internal Server Error (application/json)

{
"errorCode":500,
"error":
{"EOutOfMemory":{"EOutOfMemory":"Out of memory"}}
}

At first I was thinking that is simple memory leaks. I have changed memory manager (FastMM) to FullDebug mode. But after server process closing it was nothing reported. No memory leak detected. I have turned off caching using code:

Server.Db.UseCache := False;

It nothing changed. So I was thinking maybe some object allocates memory, accumulates and frees it just before server closing. I have tried to monitor memory usage while server is working. I didn't found anything special while this process. Amount of the used memory still grows but it was no new memory allocations in the process reported by FastMM. Maybe the memory is allocated outside of FastMM - in some dll library? The only external library which I'm directly using is oci.dll (Oracle client) via SynDbOracle unit. So I have changed database layer. Instead of class TSQLDBOracleConnectionProperties from SynDbOracle I have used class TOleDBOracleConnectionProperties from SynOleDb unit. After this change everything works correctly - amount of the used memory using grows up to the one value and it stays on this level during few hours (tomorrow I will make longer test)

Is it any chance to find what exactly causes this problem and fix it?

Last edited by ASiwon (2015-10-06 06:41:14)

ab · 2015-10-06 06:10:07

First of all, ensure you use the latest revision of SynDBOracle, and the framework.

I guess this is not a ORM memory consumption.
The ORM cache has a property high level of memory size, which is a few dozen of MB.

Server.Db.UseCache := False;

Change the cache at SQLite3 level, so it would not do anything if your internal requests bypass the virtual tables mechanism.

I guess this has something to do with how SynDBOracle is used.
Are you using the ORM?
Or plain SQL?
How are you doing your queries?
I guess this is the ORM.

Try to change the threading settings.
Try with

aServer.AcquireExecutionMode[execORMGet] := amBackgroundThread;
 aServer.AcquireExecutionMode[execORMWrite] := amBackgroundThread;

See http://synopse.info/files/html/Synopse% … #TITLE_269

Also try to set

TSQLDBOracleConnectionProperties.UseCache := false;

This is a cache at SynDBOracle level.

mpv · 2015-10-06 08:52:55

We turn OFF oracle statement cache TSQLDBOracleConnectionProperties.UseCache := false - we found this feature it very unpredictable on the production. Don't remember exactly why...

ASiwon · 2015-10-06 10:31:43

ab wrote:

First of all, ensure you use the latest revision of SynDBOracle, and the framework.

Ok. Today I make tests on the current version (SHA-1: 430ac9e7dab570 in GIT repository) Yesterday tests were made on version SHA-1: 33cd1280368d355.

ab wrote:

I guess this is not a ORM memory consumption.
The ORM cache has a property high level of memory size, which is a few dozen of MB.

Yes. I agree.

ab wrote:

I guess this has something to do with how SynDBOracle is used.
Are you using the ORM?
Or plain SQL?

I'm using pure ORM. Not SQL.

ab wrote:

How are you doing your queries?
I guess this is the ORM.

Here is a sample code:

      wagon := TTCWagon.Create(FTCClient, '% = ?', ['Id'], [APosition.Key]);

I have one query which selects 100.000 records on begin of the every logical session and then a lot of queries which selects one or few records from REST server.

ab wrote:

Try to change the threading settings.
Try with
aServer.AcquireExecutionMode[execORMGet] := amBackgroundThread;
 aServer.AcquireExecutionMode[execORMWrite] := amBackgroundThread;
See http://synopse.info/files/html/Synopse% … #TITLE_269

After this correction there are no changes. Amount of used memory still grows.

ab wrote:

Also try to set
TSQLDBOracleConnectionProperties.UseCache := false;
This is a cache at SynDBOracle level.

It works. Amount of used memory not grows, but execution is slowed down.

Last edited by ASiwon (2015-10-06 10:33:44)

ab · 2015-10-06 10:48:56

So it is a problem of caching statements at OCI level.

I guess the execution won't be much slowed down, if most of your process consists in returning huge amount of data.

ASiwon · 2015-10-06 11:05:00

No. I have only one query, which returns huge amount of data and a huge amount of queries which returns small pieces of data. I must to check how much it slows down.

ASiwon · 2015-10-06 13:38:38

After turn off of the caching, service is executed slower about 5-7%. Its not a problem. Maybe caching should be turned off as default for TSQLDBOracleConnectionProperties class?

mpv · 2015-10-06 14:46:30

I google a little and found OCI wrapper written on C++: https://fossies.org/linux/sqlrelay/src/ … oracle.cpp

I search for all occurrence of #ifdef OCI_STMT_CACHE there and seams we miss dozen of code during query Prepare (bool oraclecusor::prepareQuery in the C++ sources).

So we have a direction to fix a problem..

ASiwon · 2015-10-06 20:46:26

Do you mean executing function OCIStmtPrepare2 in OCI_PREP2_CACHE_SEARCHONLY mode? I have experimentally changed TSQLDBOracleStatement.Prepare procedure to the following form:

procedure TSQLDBOracleStatement.Prepare(const aSQL: RawUTF8;
  ExpectResults: Boolean);
var oSQL: RawUTF8;
    prepare: Boolean;
    Env: POCIEnv;
begin
  fTimeElapsed.ProfileCurrentMethod;
  try
    if (fStatement<>nil) or (fColumnCount>0) then
      raise ESQLDBOracle.CreateUTF8('%.Prepare should be called only once',[self]);
    // 1. process SQL
    inherited Prepare(aSQL,ExpectResults); // set fSQL + Connect if necessary
    fPreparedParamsCount := ReplaceParamsByNames(aSQL,oSQL);
    // 2. prepare statement
    Env := (Connection as TSQLDBOracleConnection).fEnv;
    with OCI do begin
      HandleAlloc(Env,fError,OCI_HTYPE_ERROR);
      if fUseServerSideStatementCache then
      begin
        prepare := StmtPrepare2(TSQLDBOracleConnection(Connection).fContext,fStatement,
          fError,pointer(oSQL),length(oSQL),nil,0,OCI_NTV_SYNTAX,OCI_PREP2_CACHE_SEARCHONLY) <> OCI_SUCCESS;

        if prepare then
          Check(nil,self,StmtPrepare2(TSQLDBOracleConnection(Connection).fContext,fStatement,
            fError,pointer(oSQL),length(oSQL),nil,0,OCI_NTV_SYNTAX,OCI_DEFAULT),fError);
      end
      else begin
        HandleAlloc(Env,fStatement,OCI_HTYPE_STMT);
        Check(nil,self,StmtPrepare(fStatement,fError,pointer(oSQL),length(oSQL),
          OCI_NTV_SYNTAX,OCI_DEFAULT),fError);
      end;
    end;
    // 3. retrieve column information and dispatch data in row buffer
    SetColumnsForPreparedStatement;
  except
    on E: Exception do begin
      SynDBLog.Add.Log(sllError,E);
      FreeHandles(True);
      raise;
    end;
  end;
end;

This version works correctly but change does not helps. Amount of the memory used by server still grows.

mpv · 2015-10-07 07:27:46

This sadness....

jaclas · 2015-10-10 20:30:51

Hi,

I have the same problem, the server eats more and more memory. But I do not use ORM, only alone REST module. I connect several clients who perform ca 20 request per second ...and memory grows a few kilobytes on a few seconds. Without end.
It does not look good :-(

best regards

ab · 2015-10-10 21:55:58

It is not in the REST or ORM, but at database layer.

Which SynDb unit are you using?

jaclas · 2015-10-11 08:36:45

I do not use any SynDB unit. Operations on DB are performed outside mORMot (in my code).
But for testing purpose I gave Exit instruction in first line of REST service method. Now the server receive only "empty" requests and do nothing. But memory allocation still slow grows. What could be causing this?

ab · 2015-10-11 12:19:42

SOA doesn't use memory during its process.
See the regression tests: thousands of requests are performed with no memory increase.

How do you access to the db?

I guess this may due to threads.
Did you try http://synopse.info/forum/viewtopic.php … 005#p18005 ?
Perhaps each SQL access from a thread does consume resources from the DB driver side.
Using a single background thread may help.

BTW which compiler do you use?
FPC under Win32 tends to reserve a lot of virtual memory, just for nothing.

jaclas · 2015-10-11 20:28:21

I access to Firebird by FireDAC. I use Delphi 10 Seattle.
I made simple app that have only REST layer (like in my app) and ...memory allocations not grow.
Now I search for reason in my code... but this is very uphill job.

ab · 2015-10-12 06:51:12

We are interested in knowing what's happening in your case.

This is a very common use case of the framework.
Try to force the SOA methods execution in a single thread.

jaclas · 2015-10-18 20:40:09

However, the problem was in my code. But it was not a memory leak, but the uncontrolled growth of data structures.
Now all is good.
Sorry for confusion.

best regards

mORMot Open Source

#1 2015-10-05 20:52:32

Growing amount of the memory used on server

#2 2015-10-06 06:10:07

Re: Growing amount of the memory used on server

#3 2015-10-06 08:52:55

Re: Growing amount of the memory used on server

#4 2015-10-06 10:31:43

Re: Growing amount of the memory used on server

#5 2015-10-06 10:48:56

Re: Growing amount of the memory used on server

#6 2015-10-06 11:05:00

Re: Growing amount of the memory used on server

#7 2015-10-06 13:38:38

Re: Growing amount of the memory used on server

#8 2015-10-06 14:46:30

Re: Growing amount of the memory used on server

#9 2015-10-06 20:46:26

Re: Growing amount of the memory used on server

#10 2015-10-07 07:27:46

Re: Growing amount of the memory used on server

#11 2015-10-10 20:30:51

Re: Growing amount of the memory used on server

#12 2015-10-10 21:55:58

Re: Growing amount of the memory used on server

#13 2015-10-11 08:36:45

Re: Growing amount of the memory used on server

#14 2015-10-11 12:19:42

Re: Growing amount of the memory used on server

#15 2015-10-11 20:28:21

Re: Growing amount of the memory used on server

#16 2015-10-12 06:51:12

Re: Growing amount of the memory used on server

#17 2015-10-18 20:40:09

Re: Growing amount of the memory used on server

Board footer