Mysql – How to handle connection pooling for massive multi-tenancy/multi-schema environment

In general the max number of running sessions has a relation with the number of CPU cores on the database server. If you have 32 cores it does not make sense to allow more connections and expect them all to run concurrently.

Because in most of the times there will be some waiting, you could allow a few more but the database system itself also needs some CPU to work for you.

So your database connection pool should not have many more connections than there are available CPU cores to run them on.

When all CPU's are loaded, the application will have to wait for connections to return results and putting more stress on the database will slowdown the application more.

The optimal connection pool size can be smaller but depends on the transaction length and the number of concurrent transactions. If every transaction returns it's connection to the pool the system can handle a lot of work.

You can get a little closer if you add host process id but I don't think you'll be able to get an actual connection pool count 100% from server data because many connection string attributes are processed on the client and never sent to the server. This might be close enough for your needs, though.

SELECT
      s.login_name
    , s.host_name
    , s.program_name
    , s.host_process_id
    , COUNT(*) AS connection_count
FROM sys.dm_exec_sessions AS s
GROUP BY
      s.login_name
    , s.host_name
    , s.program_name
    , s.host_process_id;

A client-side exception like "timeout period elapsed prior to obtaining a connection from the pool" should be raised when the pool size is reached an another connection cannot be acquired within the connection timeout limit.