More complex connection pooling?

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!

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.


1 Like

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.

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!

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!