Hello,
Our application is intensively used in a 24x7 environment. We set a connection pool starting with MaxPoolSize=20 as an strategy to not block connections, we grow MaxPoolSize when MaxPool is reached. During the heavy use, the pool reaches around 100+ connections.
The problem is: the connections are (almost) never released from the pool, even when the application is not heavily used (late night, for instance).
Looking at TDAConnectionManager for how the connection pooling works, we found that connections are kept on a list and every time a connection is requested (NewConnection) it takes the first alive connection of the list and removes it from the top of the list. When this connection is released (ReleaseConnection) its LastUse_UTC property is updated and it is added to the end of the connections list.
Connections are checked for timeout (OnTimerTick) every PoolTimeoutSeconds. Time outed connections are dropped from the connection list.
As the connection list woks in a FIFO strategy, If connections are heavily requested, connections are “refreshed” (LastUse_UTC updated) very frequently and does not “expires” to be released by the timeout checking.
By using a LIFO approach, the last used connection will be reused next time NewConnection is called, leaving not used connections to be released after timeout (PoolTimeoutSeconds).
We changed the NewConnection method to give connections from the pool by looking for last added connections first and then we could see that connections are released from the pool as expected.
Is it possible to change that on the next release?
function TDAConnectionManager.NewConnection
...
for i := 0 to list.Count-1 do begin //<< kill dead connections starting from the begin of list.
tempconn := TCachedConnection(list[i]);
if not tempconn.Connection.IsAlive then begin
tempconn.Connection:=nil;
list.Delete(i);
end;
end;
for i := list.Count-1 downto 0 do begin //<< get pooled connections starting from the last added.
tempconn := TCachedConnection(list[i]);
if SameText(tempconn.Connection.ConnectionString, conn.ConnectionString) and
SameText(tempconn.Connection.ConnectionType, conn.ConnectionType) and
(tempconn.Connection.UserID = UserID) and
(tempconn.Connection.Password = Password) then begin
list.Delete(i);
Result := tempconn.Connection;
tempconn.Free;
if Assigned(fOnConnectionAcquired) then fOnConnectionAcquired(Self, result);
break; { for}
end;
end;