I am facing a problem in configuring multiple servers with a single connection pool.
Currently, we have 5 App servers on different machines and we have 5 database nodes.
All the databases have similar schema but data is different for different customers i.e. horizontal partitioning based on customers.
We use postgres as the database which does not supprot clustering ability.
Now the problem is that, each app server has around 100 connections to each of the database. We have observed that after 500 connections database server performance starts spiralling down. Since we dont have horizontal partitioning on the app server side, each app server can serve all the customers. This results in inefficient usage of connections.
Is there any known solution to this problem. We were thinking of creating connection pools to these database at a single node and all other nodes will start using this pool. But it seems this is not a suggested way to do it. Following link explains the problem.
It also suggests that we should use Remote Session Facade Bean for this purpose. I am not sure how to do that. Since Connection is not serialiazable, how will that happen?
Any help will be really appreciated.