mORMot Open Source

alexdmatveev

Member

Registered: 2014-09-12

Posts: 87

mormot+zeos+mysql threading

Hello

I have a rest server with mysql connected via ZEOS.

and I have some clients.

Every SQL (as I see) that is generated on a client creates new thread on the server as I understand.
It is OK... but as I see in every thread new mysql connection is created.

Is it possible to use some like connections pool?

SynDB.pas:

  TSQLDBConnectionPropertiesThreadSafeThreadingMode = (
    tmThreadPool, tmMainConnection, tmBackgroundThread);

as I understand by default tmThreadPool is used.

and if I will use tmMainConnection then every next thread has to wait while previous SQL is executing... Right?

Now I have situation when my max_connections=100 is expired after 2 minutes of client work.

My server code:

var
  aModel: TSQLModel;
  aProps: TSQLDBZEOSConnectionProperties;
  aRestServer: TSQLRestServerDB;
  aNamedPipeServer: TSQLRestServerDB;
  aRestClient: TSQLRestClientDB;
  aHttpServer: TSQLHttpServer;

begin
  with TSQLLog.Family do
  begin
    Level := LOG_VERBOSE;
    EchoToConsole := LOG_VERBOSE; // log all events to the console end;
  end;

  aProps := TSQLDBZEOSConnectionProperties.Create(
      TSQLDBZEOSConnectionProperties.URI(dMySQL,
        'localhost:3306', 'libmysql.dll'),
      '***',
      '***',
      '***');

  try
    aModel := CreateDataModel;
    VirtualTableExternalRegisterAll(aModel, aProps);

    MapFields(aModel);

    try
      DeleteFile('data1.db3');
      aRestServer := TSQLRestServerDB.Create(aModel,'data1.db3', true); // authentication=true
      try
        aRestServer.CreateMissingTables; // create tables or fields if missing
        if not aRestServer.TableHasRows(TSQLAuthGroup) then
        begin
          TSQLAuthGroup.InitializeTable(aRestServer, '', [itoNoIndex4ID, itoNoIndex4UniqueField, itoNoIndex4NestedRecord, itoNoIndex4RecordReference]);
        end;

        // serve aRestServer data over HTTP
        aHttpServer := TSQLHttpServer.Create('888',[aRestServer],'+',useHttpApiRegisteringURI, 32);
        try
          aHttpServer.AccessControlAllowOrigin := '*'; // allow cross-site AJAX queries
          writeln('Background server is running.'#10);
          write('Press [Enter] to close the server.');
          readln;
        finally
          aHttpServer.Free;
        end;
      finally
        aRestClient.Free;
        aRestServer.Free;
      end;
    finally
      aModel.Free;
    end;
  finally
    aProps.Free;
  end;
end.

Thanks a lot.

alexdmatveev

Member

Registered: 2014-09-12

Posts: 87

Re: mormot+zeos+mysql threading

For the moment the server lives with:

aProps.ThreadingMode := tmMainConnection;

BUt I think it is not very good for perfomance.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,207

Website

Re: mormot+zeos+mysql threading

Ensure you read http://synopse.info/files/html/Synopse% … #TITLE_265

Since the ORM read/write methods are blocking, a single connection, in a single thread, does make sense.
Try

aProps.ThreadingMode := tmMainConnection;

and

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

alexdmatveev

Member

Registered: 2014-09-12

Posts: 87

Re: mormot+zeos+mysql threading

Thanks a lot as always

Will study and apply.

alexdmatveev

Member

Registered: 2014-09-12

Posts: 87

Re: mormot+zeos+mysql threading

my results:

remote servers now looks much more better.

But in my applicaton I have a connection to local mysql database via ZEOS too.

procedure TOmConnectionManager.CreateLocalServerMySQL(settings: ISettings);
var
  aProps: TSQLDBZEOSConnectionProperties;
  password: String;
  Key: TSHA256Digest;
begin
  FLocalModel := LocalModel;

  password := settings.ReadString(CurrentLogonName, 'mysql_password', '');
  password := Base64ToBin(password);
  SHA256Weak(MYSQL_PASSWORD_AESKEY, Key);
  password := SynCrypto.AES(Key, 256, password, false);

  aProps := TSQLDBZEOSConnectionProperties.Create(
      TSQLDBZEOSConnectionProperties.URI(dMySQL,
        settings.ReadString(CurrentLogonName, 'mysql_host', 'localhost') +
        ':' + settings.ReadString(CurrentLogonName, 'mysql_port', MYSQL_PORT)
        , 'libmysql.dll'),
      settings.ReadString(CurrentLogonName, 'mysql_database', 'mednet_default'),
      CurrentLogonName,
      password);
  aProps.ThreadingMode := tmMainConnection;

  VirtualTableExternalRegisterAll(FLocalModel, aProps, [regMapAutoKeywordFields]);

  FLocalRestServer := TSQLRestServerDB.Create(FLocalModel, SQLITE_MEMORY_DATABASE_NAME); // authentication=true
  FLocalRestServer.AcquireExecutionMode[execORMGet] := amBackgroundORMSharedThread;
  FLocalRestServer.AcquireExecutionMode[execORMWrite] := amBackgroundORMSharedThread;
//FLocalRestServer.AcquireExecutionLockedTimeOut[execORMGet] := 120000;
//FLocalRestServer.AcquireExecutionLockedTimeOut[execORMWrite] := 120000;
  FLocalRestServer.CreateMissingTables;

  FLocalClient := TSQLRestClientDB.Create(FLocalRestServer);
end;

in the application I have several thread with some like

FConnectionManager.LocalClient.Add(link, true, true);

or

objs := FConnectionManager.Client.RetrieveList(cl, 'updated>?', [FormatDateTime('yyyy-mm-dd hh:nn:ss', lastSync)]);

After I have read the offered in previous post documentation I tryed all combinations for the local (in-app) mysql server (FLocalRestServer).
Periodicaly I get exceptions from mysql via zeos like
-Server has gone during query
-or some timeout exceptions

How should I organize the server to work with threads?

Should I try to Clone the server for every thread? Or does exists better way?

Thanks.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,207

Website

Re: mormot+zeos+mysql threading

Enable logs, and check (using the LogView tool), what is happening at DB layer.

BTW your 'updated>?' version seems not optimized, for date/time format - see http://synopse.info/files/html/Synopse% … ml#TITL_36

alexdmatveev

Member

Registered: 2014-09-12

Posts: 87

Re: mormot+zeos+mysql threading

some log rows (I can take from it nothing useful for first look, so pls take a look):

20150629 08375315 SQL   TSQLRestServerDB(0978B8C0) 46.49ms  delete from link
20150629 08375315 srvr  	TSQLRestServerDB(0978B8C0)   POST root/ ORM-Get -> 200 with outlen=0 in 46544 us
20150629 08375315  -    00.046.563
20150629 08375315  +    TSQLRestServerDB(0978B8C0).URI(GET root inlen=134)
20150629 08375315  +    TSQLDBZEOSStatement(1050D9E0).00F98A4F SynDBZeos.TSQLDBZEOSStatement.Prepare (773) 
20150629 08375315  -    00.000.046
20150629 08375315  +    TSQLDBZEOSStatement(1050D9E0).00F998B0 SynDBZeos.TSQLDBZEOSStatement.ExecutePrepared (905) 
20150629 08375315 SQL   	TSQLDBZEOSStatement(1050D9E0) select max(Updated) from Account
20150629 08375315  +    TSQLDBZEOSStatement(1050BF10).00F998B0 SynDBZeos.TSQLDBZEOSStatement.ExecutePrepared (905) 
20150629 08375315 SQL   	TSQLDBZEOSStatement(1050BF10) select ID,Updated,Created,LogonName,Personal,PublicKey,IsDeleted,Fingerprint from Account where LogonName='Test1 Alex' limit 1
20150629 08375315  -    00.001.031
20150629 08375523 srvr  	TSQLRestServerDB(0978B8C0)   GET root/ ORM-Get -> 200 with outlen=470 in 2126531 us
20150629 08375523  -    02.126.629
20150629 08375523  +    TSQLRestServerDB(0978B8C0).URI(GET root/Personal/115 inlen=0)
20150629 08375523 clnt  	TSQLHttpClientWinHTTP(09EBF980) GET root?session_signature=3C1D9D7D003C71F544318457 status=200 state=55
20150629 08375523  -    02.492.349
20150629 08375525  -    02.161.719
20150629 08375525  +    TSQLDBZEOSStatement(1050C280).00F998B0 SynDBZeos.TSQLDBZEOSStatement.ExecutePrepared (905) 
20150629 08375525 SQL   	TSQLDBZEOSStatement(1050C280) select ID,Updated,Created,Title,Name1,Name2,Strasse,Postfach,Plz,Ort,Telefon1,Telefon2,Telefon3,Fax,Handy1,Handy2,Mail1,Mail2,www,Language_ as Language,Country,Client,id_mandant from Personal where ID=115
20150629 08375625 EXC   	EZSQLException ("SQL Error: MySQL server has gone away") at 00EC8087 ZDbcMySqlUtils.CheckMySQLError (460)  stack trace API 00EC8087 ZDbcMySqlUtils.CheckMySQLError (460) 00ED5554 ZDbcMySqlStatement.TZMySQLStatement.ExecuteQuery (351) 00E6F4F8 ZDbcStatement.TZEmulatedPreparedStatement.ExecuteQuery (3342) 00E6F6DC ZDbcStatement.TZEmulatedPreparedStatement.ExecuteQueryPrepared (3409) 00F99BF5 SynDBZeos.TSQLDBZEOSStatement.ExecutePrepared (963) 00C8A90E SynDB.TSQLDBStatement.ExecutePreparedAndFetchAllAsJSON (6768) 00C92661 mORMotDB.TSQLRestStorageExternal.EngineRetrieve (1300) 00B96293 mORMot.TSQLRestServerURIContext.ExecuteORMGet (33811) 00BC38D0 mORMot.BackgroundExecuteProc (47479) 00B1663A SynCommons.TSynBackgroundThreadMethod.Process (47059) 00B160CF SynCommons.TSynBackgroundThreadAbstract.Execute (46928) 004D8C5C System.Classes.ThreadProc (14161) 0040B852 System.ThreadWrapper (23680) 
20150629 08375628  -    01.034.938
20150629 08375706 EXC   	EZSQLException ("SQL Error: MySQL server has gone away") at 00B163BF SynCommons.TSynBackgroundThreadAbstract.RunAndWait (47016)  stack trace API 

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,207

Website

Re: mormot+zeos+mysql threading

Sounds like a disconnection at ZDBC level.
Perhaps you may ask on the Zeos forum, since I'm no MySQL expert.

Also ensure that you enabled the thread information logging, so that you would be able to see what's up at thread level.
All your MySQL/ZDBC execution should be run in the very same TSynBackgroundThread.

alexdmatveev

Member

Registered: 2014-09-12

Posts: 87

Re: mormot+zeos+mysql threading

>All your MySQL/ZDBC execution should be run in the very same TSynBackgroundThread.

What is correct settings of the server pls?

they are should be as below ?

aProps.ThreadingMode := tmMainConnection;
FLocalRestServer.AcquireExecutionMode[execORMGet] := amBackgroundORMSharedThread;
FLocalRestServer.AcquireExecutionMode[execORMWrite] := amBackgroundORMSharedThread;

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,207

Website

Re: mormot+zeos+mysql threading

Those settings sound correct to me.

alexdmatveev

Member

Registered: 2014-09-12

Posts: 87

Re: mormot+zeos+mysql threading

ok, thanks a lot!

PS. DateTime parameter: thanks too. I have corrected it.