0

I'm in process of upgrading our client-server ERP app to multi-tier. We want to offer our customers the possibility having their databases in cloud(hosted in our server).So, clients are written in Delphi, server is a http IOCP server written also in Delphi (from mORMot framework), for dbs we use Firebird embedded.

Our customers(let's say 200), can have 25-30 Firebird databases (total 5000-6000 databases), accessed by 4-5 user per each customer. This is not happening all at once. One user can work in one db, other 2 users can work in another db, but all the dbs should be available and online. So, I can have 800-1000 users working at 700-900 dbs. Databases are not big, typically 20-30 MB each but can go to 200 MB.

This is not data sharding so please don't suggest to merge all databases together, I really need them individually with possibility to backup/restore/replace each one of them.

So, I need multiple pools of connections - for every database I need a pool of let's say 2 connections. I read about Firedac connection pooling. It seems that TFDManager should be perfect for me. I define multiple "ConnectionDef"s with "Pooled=true" and it can maintain multiple pools of connections (each connection lasting until some minutes of inactivity).

Questions:

  1. I have to create all "ConnectionDef"s before server starts serving requests?

  2. Can TFDManager "handle" requests (and time-out connections on inactivity), while in other thread I need to create a new database , so automatically I need to create a new pool of connections and start serving requests from newly created database. Practically can I call FDManager.AddConnectionDef(..) while other pools are in use?

Arnaud Bouchez
  • 40,947
  • 3
  • 66
  • 152
emk
  • 11
  • 1
  • 3

2 Answers2

2

AFAIK Firebird embedded does not have any "connection". As its name states, it is embedded within the same process, so there is no connection pool needed. Connection pools are needed when several clients connect/disconnect to the same DB over the network, whereas here all is embedded, and you would have direct access to the Firebird engine.

As such, you may:

  • Define one "connnection" per Firebird embedded database;
  • Protect your SOA code via a mutex (aka critical section) per DB. In fact, mORMot's HTTP IOCP server would run the incoming requests from a thread pool, so ensure that all DB access is safely made.
  • Ensure you use at least Firebird 2.5 since the embedded version is told to be threadsafe only since revision 2.5 (see the release notes).
  • Instead of FireDAC, consider using ZDBC/Zeos (in latest 7.2/7.3 branch), which has nice features, together with the native mORMot SynDB libraries.
Arnaud Bouchez
  • 40,947
  • 3
  • 66
  • 152
  • Agree to disagree - connection pools are used to to facilitate acquiring a resource when normally creating/establishing a new one will take time and/or resources. Even attaching a local database file to the process like fbembed.dll does, will take some time and resources - SQL privileges will be checked, separate buffers will be allocated per every attachment even it's the same process and the same database file (>= FB 2.5). So, connection pooling apply here. – emk Apr 10 '16 at 14:55
  • Defining "connections" and protecting them with a critical section is not connection pooling? So, why invent the wheel when there is available a tested one. Even if your scenario seems simple because it's only one "connection" per database, I think I need at least two of them because sometimes one client can acquire the connection for a longer period of time because has to synchronize a bigger change log since it was last time online. Anyway your solution is good one because it has one critical section per db and Firedac will have one critical section per all databases. – emk Apr 10 '16 at 15:23
0

Looking at Firedac sources, seems that all about adding connection definitions and acquiring connections in pooled mode is thread-safe.

Adding a connection definition or matching one is guarded by a TMultiReadExclusiveWriteSynchronizer and acquiring a connection from the pool is guarded by a TCriticalSection.

So, answers:

  1. I don't have to create all "ConnectionDef"s before server starts serving requests.
  2. Yes, I can call safely FDManager.AddConnectionDef(..) while other pools are in use.

Using Firedac, acquiring a connection for any of those databases will be guarded by one TCriticalSection. The solution proposed by @Arnaud Bouchez presents a more grained access by creating one TCriticalSection per database and I think will scale better, but you should be aware of a bug when using multiple TCriticalSection, especially that all will be initiated at once:

https://www.delphitools.info/2011/11/30/fixing-tcriticalsection/

In that article present a very simple fix for this bug.

emk
  • 11
  • 1
  • 3
  • Since you are using mORMot, you can use the `TSynLocker` record, or the `IAutoLocker`/`TAutoLocker` interface/class, which fixes the `TCriticalSection` bug, allows to protect a whole method in one call, and uses the padding space to store some values, which would be protected by the lock. See [TAutoLocker](http://synopse.info/files/html/api-1.18/SynCommons.html#TAUTOLOCKER) and [TSynLocker](http://synopse.info/files/html/api-1.18/SynCommons.html#TSYNLOCKER) documentation. – Arnaud Bouchez Apr 12 '16 at 18:07