More complex connection pooling?

BIZ TMS XData
1

Got a project underway and doing a bit of brainstorming about how best to architect the database connection pooling aspects. So please bear with me, trying to present a concise explanation is not always so easy.

Got an XDataServer that will have hundreds of endpoints grouped together in a bunch of different services, with each service corresponding to a particular part of a database schema. Think of an ERP system. One service deals with workers, one service deals with inventory, etc.

On the database side, there are a handful of DB2 servers and a handful of MySQL servers. Lets call them DB2-A, DB2-B, DB2-C, and MYSQL-01 through MySQL-10. Each DB2 database can have a max of 25 concurrent things going on, and each MySQL database is more like 5 things. So from a database pooling perspective, those would be the numbers I'd start with.

All the databases have the same schemas (more or less). I've got a separate DataModule defined fore each part of the schema as mentioned (Workers vs. Inventory for example). And each of these defines an FDConnection and a pile of SQL statements. Let's assume FireDAC and FDQuery everywhere for now.

(As an aside, I really, really do appreciate all that is Aurelius, but I also really, really like SQL and the thought of rewriting thousands of queries into Aurelius is, well, something I'll worry about after this is up and running).

So. Connection pooling. I'm assuming I'll need to create, in the XDataServer, a connection pool for each of the 13 databases, as outlined in the very last example in the XDataServer User Guide's Other Tasks section. I'm assuming that for each of these 13 connection pools, they'll instantiate a copy of the same DataModule, just setting the FDConnection parameters specifically for each different connection pool. If I've got 5 different DataModules, then I'll be looking at (5x13) 65 distinct connection pools.

When writing the code for the service endpoints, anything that requires a database connection (well, all of them most likely) will then be able to use a value in the JWT to determine which database is being used. All the connections in the same service will use the same DataModule, so it will then grab a connection from the appropriate pool (one of the 60). Once the work of the endpoint is done, the connection would be released back to the pool. I think there are examples of this "grab a connection" and "release a connection" here in the support center, so all good so far.

So am I on the right track here? Should I instead setup 13 FDManager components in the XDataServer itself and use its connection pooling mechanism, in addition to the pooling system for the DataModules? I kinda like the idea that the DataModules contain the connections and thus abstract away that kind of complexity in this IDBConnectionFactory business.

Finally, I'm assuming this is all transparent to the client side - clients can fire off all the async requests to endpoints they like and the XDataServer will happily chug away at them all, keeping the connection pool limits in mind. Kind of like doing client multithreading the hard way. Or easy way depending on your point of view.

In any event, as an old Delphi developer new to TMS and all this WebCore/XDataServer/JSON business, I've been having an amazing amount of fun getting all this up and running. Webcore in particular, mixing Pascal and JavaScript, has produced more fiendish grins than anything I can remember! Looking forward to more!

3

Hi there.

I guess you could use something like the MultiDBConnectionPool found in MultiTenant demos dir. I wouldn't create 'N' datamodules, just ONE (or max 2, one for each DBMS flavor) and setup in code (run-time) each new DB connections as needed.

Regards,

1 Like
4

The "problem" here is that you really have lots of options. First of all:

The XData connection pool is just a helper for users. And it is required only if you use automatic CRUD endpoints based on Aurelius. Since you are going to use service operations (regardless if using Aurelius or direct SQL using FireDAC), you can manage your own pool the way you want.

So you can have one multitenant connection pool, as @Farias_Anderson mentioned, you can have one pool for each database/data module, and you can even manually create one instance of the data module for processing the request, use it, and destroy it. FireDAC also has some pooling mechanism that improves performance even when you create/destroy a TFDConnection each time.

You can maybe start simple - create a connection (data module), use, destroy it, in each request. Then if you run into performance issues you can later improve it if you need to.

5

Yes, many good options it seems. The reason for wanting a connection pool is that the setup time for the physical database connection is substantial, particularly if there are many quick transactions for a single user. The reason for wanting to setup multiple DataModules was to help separate out some of the database work as users tend to work in one area. So while this might increase the number of connection pools, it would reduce the size of the pools as well as the size of the DataModules in each pool, making things a little more fluid resource-wise. Got some testing and a bit of experimenting to do!

6

Just to follow up on this, it turns out that FireDAC's connection pooling works pretty well. I can create a different connection pool for each particular combination of interest very easily and just dynamically create the connection and queries in the service implementation calls and voila - threadsafe database access that appears to be scalable and quite workable, without having to worry so much about object factories and instantiating datamodules. The datamodules can hold the query code but just used as a read-only template of sorts for the query created in the service implementation. Early days yet, but this works great so far!

2 Likes
7

Hello Andrew,

I'm trying to use pooling.

How did you use Firedac's connection pooling with Xdata ?

Do you create and release the fdconnection for each request ?

Thank you.

8

In the Template Demo project, I don't think any FireDAC connection pooling is used (yet), but that just uses SQLite at the moment, so I was more concerned about having multi-threaded access to the database than I was about connection pooling. Here's the code that is used there.

procedure TDBSupport.ConnectQuery(var conn: TFDConnection; var qry: TFDQuery; DatabaseName: String; DatabaseEngine: String);
begin
  try
    // Establish a new connection for each endpoint invocation (not ideal!)
    if DatabaseEngine = 'sqlite' then
    begin
      conn := TFDConnection.Create(nil);
      conn.Params.Clear;
      conn.Params.DriverID := 'SQLite';
      conn.Params.Database := DatabaseName;
      conn.Params.Add('DateTimeFormat=String');
      conn.Params.Add('Synchronous=Full');
      conn.Params.Add('LockingMode=Normal');
      conn.Params.Add('SharedCache=False');
      conn.Params.Add('UpdateOptions.LockWait=True');
      conn.Params.Add('BusyTimeout=10000');
      conn.Params.Add('SQLiteAdvanced=page_size=4096');
      // Extras
      conn.FormatOptions.StrsEmpty2Null := True;
      with conn.FormatOptions do
      begin
        StrsEmpty2Null := true;
        OwnMapRules := True;
        with MapRules.Add do begin
          SourceDataType := dtWideMemo;
          TargetDataType := dtWideString;
        end;
      end;
    end;

    conn.Open;

    // Create a query to do our work
    qry := TFDQuery.Create(nil);
    qry.Connection := conn;

  except on E: Exception do
    begin
      // If the above fails, not a good thing, but at least try and make a note as to why
      Mainform.mmInfo.Lines.Add('[ '+E.ClassName+' ] '+E.Message);
    end;
  end;
end;

In another project, different databases are used by the same XData server (DB2 and MySQL) with endpoints using one or both depending on what they are up to. For example, account data is stored in DB2 (called a control database below) but individual customer datasets might be stored in separate MySQL databases. A perhaps amusing (or horrifying) side note is that MySQL "databases" are actually not really different databases at all, at least in the DB2 sense of different databases. Kind of making that effort pointless. Here's a bit of what that looks like.

  // Ok, here we use the above information to establish the Control Database connection pool
  // Delete it if it exists already (can only have one control database per server in our case)
  try
    FDManager.Close;
    while FDManager.State <> dmsInactive do Sleep(0);

    FDManager.Open;
  except on E: Exception do
    begin
//      DialogBoxAutoClose('Database Error', 'Shutdown of databse connections has failed', 5);
    end;
  end;

  if FDManager.IsConnectionDef('ControlPool') then FDManager.DeleteConnectionDef('ControlPool');

  ControlDBParams := TStringList.Create;
  ControlDBParams.Add('Database=' +AppControlDBName);
  ControlDBParams.Add('Alias='    +AppControlDBName);
  ControlDBParams.Add('User_Name='+AppControlDBUser);
  ControlDBParams.Add('Password=' +AppControlDBPass);
  ControlDBParams.Add('Server='   +AppControlDBAddr);
  ControlDBParams.Add('Port='     +AppControlDBport);
  ControlDBParams.Add('Pooled=True');
  ControlDBParams.Add('POOL_MaximumItems=100');
  ControlDBParams.Add('POOL_ExpireTimeout=30000');
  ControlDBParams.Add('POOL_CleanupTimeout=30000');
  FDManager.AddConnectionDef('ControlPool', 'DB2', ControlDBParams);
  ControlDBParams.Free;

  // Ok, We only have one connection in the main app here so let's set the connection to point
  // at this new pool and make it active.
  conControl.Connected := False;
  conControl.ConnectionDefName := 'ControlPool';
  conControl.Connected := True;

  // So.  Let's now create a connection pool for every database listed in the Control database table.
  ValidDatabases.Clear;
  qryDBPools := TFDQuery.Create(nil);
  qryDBPools.SQL.Text := 'select DBNUM, DBNAME, DBTYPE, DBCONNECTION from CONTROL.DBDEFS where (LICENSESTATUS = 0) order by DBNAME';
  qryDBPools.Connection := conControl;
  qryDBPools.Open;

  while not(QryDBPools.EOF) do
  begin
    btnDBCount.Tag := btnDBCount.Tag + 1;
    btnDBCount.Caption := RightSTr('00000'+IntToStr(btnDBCount.Tag),5)+' DBs';

    // MySQL Databases
    if (qryDBPools.FieldByName('DBTYPE').AsInteger = 2) then
    begin
      PoolName := 'MySQL_'+qryDBPools.FieldByName('DBNUM').AsString+'_'+qryDBPools.FieldByName('DBCONNECTION').AsString;
      if FDManager.IsConnectionDef('PoolName') then FDManager.DeleteConnectionDef(PoolName);
      ControlDBParams := TStringList.Create;
      ControlDBParams.Add('DriverID=MySQL');
      ControlDBParams.Add('Database=' +qryDBPools.FieldByName('DBCONNECTION').AsString);
      ControlDBParams.Add('User_Name='+AppControlDBUser);
      ControlDBParams.Add('Password=' +AppControlDBPass);
      ControlDBParams.Add('Server='   +AppControlMySQLaddr);
      ControlDBParams.Add('Port='     +AppControlMySQLport);
      ControlDBParams.Add('Pooled=True');
      FDManager.AddConnectionDef(PoolName, 'MySQL', ControlDBParams);
      ControlDBParams.Free;
    end;
    qryDBPools.Next;

  end;
  qryDBPools.Close;
  qryDBPools.Free;

In this scenario, when an endpoint wants to run a query, a similar setup call is made. Here, though, there is a lot of extra stuff related to pulling the query text itself from somewhere else (another DataModule - one of several).

function TDMSupport.SetupClientQuery(var conn: TFDConnection; var qry: TFDQuery;  name: String; Pool: String; DBType: Integer):String;
var
  QryName: String;
  i: integer;
begin
  Result := 'Success';

  if      (DBType = 1) then QryName := name+'_db2'
  else if (DBType = 2) then QryName := name+'_mysql'
  else                      Result  := 'Undefined DBType [ '+IntToStr(DBType)+' ] for Query [ '+name+' ]';
  if (Result <> 'Success') then Exit;

  i := 0;
  while (i < Length(Qry_Name_Array)) do
  begin
    if Uppercase(QryName) = Uppercase(Qry_Name_Array[i]) then break;
    i := i + 1;
  end;

  if ((i = Length(Qry_Name_Array)) or (Uppercase(qry_Name_Array[i]) <> Uppercase(QryName))) then
  begin
    Result := 'ERROR: Search Failed for Query [ '+QryName+' ]';
    MainForm.memDebug.Lines.Add(Result);
    Exit;
  end;

  if not(Assigned(conn)) then
  begin
    try
      conn := TFDConnection.Create(nil);
      conn.ConnectionDefName := Pool;
      conn.ResourceOptions.SilentMode := True;
      conn.Connected := True;
    except on E: Exception do
      begin
        Result := 'ERROR: '+E.ClassName+' / '+E.Message;
        MainForm.memDebug.Lines.Add(Result);
      end;
    end;
  end;

  if not(Assigned(qry)) then
  begin
    try
      qry := TFDQuery.Create(nil);
      qry.Connection := conn;
    except on E: Exception do
      begin
        Result := 'ERROR: '+E.ClassName+' / '+E.Message;
        MainForm.memDebug.Lines.Add(Result);
      end;
    end;
  end;

  try
    qry.SQL.Assign(Qry_Object_Array[i].SQL);
  except on E: Exception do
    begin
      Result := 'ERROR: '+E.ClassName+' / '+E.Message;
      MainForm.memDebug.Lines.Add(Result);
    end;
  end;

end;

Apologies for the length and the quality of the code and the extraneous bits you don't care about most likely. This is just mostly copied and pasted from a working project.

9

Thank you for taking the time to respond, that’s clear.

When i call SetupClientQuery from service operation, i have to release the connection ?

10

Yes, that's what I do. In the endpoint, it starts with a call to SetupClientQuery and then at the end, there is a bit of cleanup where the query and the connection are released. Something like this.

function TSomeService.SomeEndpoint(SomeParameter: Integer): TStream;
var
  SupportCheck: String;
  clientDB: TFDConnection;
  qry: TFDQuery;

begin
  clientDB := nil;
  qry := nil;

  SupportCheck := DMSupport.SetupClientQuery(clientDB, qry, 'SomeDataModule.SomeQuery', DBConn, DBType);
  if (SupportCheck <> 'Success') then raise EXDataHttpUnauthorized.Create(SupportCheck);

  // Endpoint does stuff with qry

  // Cleanup afterwards
  qry.Free;
  clientDB.Connected := False;
  clientDB.Free;

end;