Question on maximum connections in connection pool

Question

We have been using the default maximum connections (10) for our connection pool, and I would like to try to increase performance by making this number more realistic.

This is the formula that I have seen: total users * percent of users active at any one time * max number of reports on a dashboard

This seems pretty simple enough, but when it comes to our situation, I am a little confused.

We merge RPD's from several departments into one final deployment RPD. Each of the departmental RPD's have their own

physical data base and connection pool, however, they all point to the same database.

So, for sake of example, here is a simple representation of what is in our final RPD:

Subject Area 1                 Business Layer 1                  Physical Data Base 1 (Connection Pool 1 pointing to Data Warehouse)

Subject Area 2                 Business Layer 2                  Physical Data Base 2 (Connection Pool 2 pointing to Data Warehouse)

Subject Area 3                 Business Layer 3                  Physical Data Base 3 (Connection Pool 3 pointing to Data Warehouse)

There are a total of 2000 OBIEE users: 500 can access Subject Area 1, 1000 can access Subject Area 2, 500 can access Subject Area 3.

When determining the total users for Connection Pool 1, since users from all three connection pools access the same real data base, should I be using Total Users = 2000, or Total users = 500?

rmoff · Answer

It all depends on how the LTS resolve from the business layer. If an LTS from each of the three BMM hit the same physical table, they'll use the same CP.

It's easy to see your capacity/usage though - use the DMS metrics. They'll tell you how many connections there are, high watermark of usage, high watermark of queuing, and so on -- it's all there.

You can hit them on http://localhost:7001/dms, or collect and store them properly with obi-metrics-agent. And then, combine them with a bunch of others to get these kind of cool insights to your OBIEE system top to bottom: http://ritt.md/performance-dashboard-videos

Gianni Ceresa · Answer

From the OBIEE point of view if you have independent connection pool, even if they point to the same DB, it's like different things. So in your case it looks like Subject Area 1 has its own connection pool and so you are supposed to use 500 for your calculation.

(I'm not saying the rule you found is right or not)

Just also keep in mind that OBIEE is only half of the equation as on the other side you have your DB, and if your DB can't manage connections increasing the numbers in OBIEE will not really help or improve things.

I guess it would be a good idea to measure things a bit to see if your current 10 connections are an issue or not and to keep an eye on all the metrics around connections, number of waiting queries etc. to be able to better adjust your settings instead of a fairly generic formula.