mORMot Open Source

JD

Member

Registered: 2015-08-20

Posts: 118

Using ReconnectAfterConnectionError and ConnectionTimeOutMinutes well

Hi there everyone,

I would like some advice on how to properly use ReconnectAfterConnectionError and ConnectionTimeOutMinutes. My code snippet is shown below.

  //
  try
      DbProps := TSqlDBZEOSConnectionProperties.Create(..... PostgreSQL connection string.....);

      // To prevent PostgreSQL from hitting the default maximum connection limit of 100
      // and after rejecting subsequent connections
      TSqlDBZEOSConnectionProperties(DbProps).ThreadingMode := tmMainConnection;

      // Automatically reconnect after a reconnection error
      DBProps.ReconnectAfterConnectionError := true;

      // Timeout unused connection after 12 hours
      DbProps.ConnectionTimeOutMinutes := 720;       // Try 60 minutes or more e.g 60 * 12 i.e 12 hours
  except
    on E: Exception do begin
      WriteLn(StringToUTF8(E.Message));
    end;
  end;

Basically, what I want to achieve is

a) automatically reconnect with the database if the connection is lost

b) timeout unused connection after 12 hours because the server is on 24/7, so this may help with long term stability

Am I doing the right thing in the code snippet above? If not, what should I be doing?

Thanks for your kind advice and input.

Cheers,

JD

Last edited by JD (2024-05-19 16:37:35)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: Using ReconnectAfterConnectionError and ConnectionTimeOutMinutes well

Sounds fine.

Some remarks

1) ThreadingMode := tmMainConnection would create a single connection, so all requests would be pipelined (executed in a single queue).
So a huge SELECT may block other requests, for a multithread process.

2) Try our direct postgresql unit: it is lighter than PostgreSQL, and faster for most usecases. Even if ZDBC/Zeos is fast, it is still a huge set of units, and add another layer to the data client.

3) Other parameters sound fine. But you need to test it properly.

JD

Member

Registered: 2015-08-20

Posts: 118

Re: Using ReconnectAfterConnectionError and ConnectionTimeOutMinutes well

Hi there ab,

Thanks a lot for your reply and clarification.

This is what it looks like now after applying your suggestions. I replaced tmMainConnection with tmThreadPool, and TSqlDBZEOSConnectionProperties with TSqlDBPostgresConnectionProperties.

  //
  try
      DbProps := TSqlDBPostgresConnectionProperties.Create(..... PostgreSQL connection string.....);

      // Use a threadpool for connections
      TSqlDBPostgresConnectionProperties(DbProps).ThreadingMode := tmThreadPool;

      // Automatically reconnect after a reconnection error
      DBProps.ReconnectAfterConnectionError := true;

      // Timeout unused connection after 12 hours
      DbProps.ConnectionTimeOutMinutes := 720;       // Try 60 minutes or more e.g 60 * 12 i.e 12 hours
  except
    on E: Exception do begin
      WriteLn(StringToUTF8(E.Message));
    end;
  end;

Cheers,

JD

Last edited by JD (2024-05-20 09:17:23)