DB-backed storage service: testing database pool status?

[Wessel, Keith]

Hi, all,

We're running the database-backed storage service, and we've run into a problem twice in the past two weeks where all of our database connections are handed out and the IDP can't get a connection from the pool. For whatever reason, they all get handed out and never returned. Result is that, after submitting username and password, the IDP hangs. I'm trying to tweak my resource parameters to prevent this situation from happening, but when it does, our usual monitoring via /idp/status isn't detecting it, obviously.

Short of having our monitoring log into the IDP, is there anything I can do to hit a URL or perform some simple action that would verify that my database connection pool is behaving itself? If not, I may have to resort to a log grepping script.

FWIW, the error in the log is "Cannot get a connection, pool error timeout waiting for idle object

Any suggestions on how I can monitor this from our service monitoring and our SLB health checks?

Keith

[Cantor, Scott]

Post by Wessel, Keith
Short of having our monitoring log into the IDP, is there anything I can
do to hit a URL or perform some simple action that would verify that my
database connection pool is behaving itself? If not, I may have to resort
to a log grepping script.

I would think you'd want login monitoring anyway, but if the storage
service is used for the replay cache, it might be something you could
detect by issuing an intentionally duplicated request.

What's the pooling library here?

-- Scott

[Paul Hethmon]

On Aug 28, 2014, at 11:33 AM, Wessel, Keith <kwessel-nzINlOoChub2fBVCVOL8/***@public.gmane.org<mailto:kwessel-nzINlOoChub2fBVCVOL8/***@public.gmane.org>> wrote:

FWIW, the error in the log is "Cannot get a connection, pool error timeout waiting for idle object

Any suggestions on how I can monitor this from our service monitoring and our SLB health checks?

What type of pooling are you using? Typically the pooling service provides options for validating the pool and connections coming out of it.

Paul

Paul Hethmon
Chief Software Architect
paul.hethmon-NC06ibP+gDOju1H+chf1WFaTQe2KTcn/@public.gmane.org<mailto:paul.hethmon-NC06ibP+gDOju1H+chf1WFaTQe2KTcn/@public.gmane.org>

[Wessel, Keith]

I was planning on adding some validation parameters. Right now, my setup is truly minimal.

As for what type of pooling, I'm not sure exactly what you're asking there. Let me try to answer that: it's a container resource that looks like:
<Resource name="jdbc/idp"
Auth="Container"
Type="javax.sql.DataSource"
. />
In addition, it has my Oracle driver, connection params, and a maxWait of 10,000 ms.

I'm planning to add a validation query. I suspect that'll do the trick, but it'd certainly be good to monitor it somehow. Maybe Scott's right and we should just have the SLBs and service monitoring perform a full login.

Keith

From: users-bounces-***@public.gmane.org [mailto:users-bounces-***@public.gmane.org] On Behalf Of Paul Hethmon
Sent: Thursday, August 28, 2014 10:44 AM
To: Shibboleth Users
Subject: Re: DB-backed storage service: testing database pool status?

On Aug 28, 2014, at 11:33 AM, Wessel, Keith <kwessel-nzINlOoChub2fBVCVOL8/***@public.gmane.org> wrote:


FWIW, the error in the log is "Cannot get a connection, pool error timeout waiting for idle object

Any suggestions on how I can monitor this from our service monitoring and our  SLB health checks?

What type of pooling are you using? Typically the pooling service provides options for validating the pool and connections coming out of it.

Paul

Paul Hethmon
Chief Software Architect
paul.hethmon-NC06ibP+gDOju1H+chf1WFaTQe2KTcn/@public.gmane.org

[Cantor, Scott]

Post by Wessel, Keith
I was planning on adding some validation parameters. Right now, my setup is truly minimal.

You definitely need some stuff to make pooling work. With dbcp, I've
generally used the validate on get model, I think, and a validation query.
I'll double check my settings. But I only use this now with MySQL local to
the box, so it's not much of a risky scenario.

Post by Wessel, Keith
As for what type of pooling, I'm not sure exactly what you're asking
there. Let me try to answer that: it's a container resource that looks
<Resource name="jdbc/idp"
Auth="Container"
Type="javax.sql.DataSource"
. />
In addition, it has my Oracle driver, connection params, and a maxWait of 10,000 ms.

That means you're not pooling, or you're using something from Oracle
inside the driver, and it's probably broken. You want to wrap that driver
in a dbcp data source object. I'll post what I use when I have a chance.

Post by Wessel, Keith
I'm planning to add a validation query. I suspect that'll do the trick,
but it'd certainly be good to monitor it somehow. Maybe Scott's right and
we should just have the SLBs and service monitoring perform a full login.

I would think you'd want that anyway, for monitoring at least. webisoget
is your friend. I don't have the SLBs doing much other than an HTTP GET at
this point though.

-- Scott

[Wessel, Keith]

Ah-ha! Well, now, no pooling could be a problem. A single hung connection with no validate on borrow could gum up the whole works. I see from here:

http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html

that I'm missing:

factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"

Then, of course, add all of the appropriate parameters for validating connections. This is from the Tomcat 7 docs. Does the same apply under Tomcat 6? Looks like it:

http://people.apache.org/~fhanik/tomcat/jdbc-pool.html

A nice example with some good sample settings is on this page, too, for a starting point.

Stop me if I'm going down the wrong road. Otherwise, I think this would be the problem. Looks like a single database connection could certainly gum up the works.

Thanks,
Keith


-----Original Message-----
From: users-bounces-***@public.gmane.org [mailto:users-bounces-***@public.gmane.org] On Behalf Of Cantor, Scott
Sent: Thursday, August 28, 2014 11:04 AM
To: Shib Users
Subject: Re: DB-backed storage service: testing database pool status?

Post by Wessel, Keith
I was planning on adding some validation parameters. Right now, my setup is truly minimal.

You definitely need some stuff to make pooling work. With dbcp, I've
generally used the validate on get model, I think, and a validation query.
I'll double check my settings. But I only use this now with MySQL local to
the box, so it's not much of a risky scenario.

Post by Wessel, Keith
As for what type of pooling, I'm not sure exactly what you're asking
there. Let me try to answer that: it's a container resource that looks
<Resource name="jdbc/idp"
Auth="Container"
Type="javax.sql.DataSource"
. />
In addition, it has my Oracle driver, connection params, and a maxWait of 10,000 ms.

That means you're not pooling, or you're using something from Oracle
inside the driver, and it's probably broken. You want to wrap that driver
in a dbcp data source object. I'll post what I use when I have a chance.

Post by Wessel, Keith
I'm planning to add a validation query. I suspect that'll do the trick,
but it'd certainly be good to monitor it somehow. Maybe Scott's right and
we should just have the SLBs and service monitoring perform a full login.

I would think you'd want that anyway, for monitoring at least. webisoget
is your friend. I don't have the SLBs doing much other than an HTTP GET at
this point though.

-- Scott

[Wessel, Keith]

Okay, so maybe things weren't quite as bad as I thought.

Tomcat couldn't find said factory: org.apache.tomcat.jdbc.pool.DataSourceFactory

So I found this:

https://bugzilla.redhat.com/show_bug.cgi?id=669969

It may not be totally relevant since it's a Redhat bug report and we're running a non-OS-packaged installation of Tomcat, but it says:
Nevertheless,Tomcat internals assume
(hardcoded as a last resort) org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory as
a default.

So, it sounds like Tomcat 6, if I read that right, uses DBCP by default. Of course, since I had no query validation or pool object eviction enabled, that does me little good.

Keith

-----Original Message-----
From: Wessel, Keith
Sent: Thursday, August 28, 2014 12:16 PM
To: 'Shib Users'
Subject: RE: DB-backed storage service: testing database pool status?

Ah-ha! Well, now, no pooling could be a problem. A single hung connection with no validate on borrow could gum up the whole works. I see from here:

http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html

that I'm missing:

factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"

Then, of course, add all of the appropriate parameters for validating connections. This is from the Tomcat 7 docs. Does the same apply under Tomcat 6? Looks like it:

http://people.apache.org/~fhanik/tomcat/jdbc-pool.html

A nice example with some good sample settings is on this page, too, for a starting point.

Stop me if I'm going down the wrong road. Otherwise, I think this would be the problem. Looks like a single database connection could certainly gum up the works.

Thanks,
Keith


-----Original Message-----
From: users-bounces-***@public.gmane.org [mailto:users-bounces-***@public.gmane.org] On Behalf Of Cantor, Scott
Sent: Thursday, August 28, 2014 11:04 AM
To: Shib Users
Subject: Re: DB-backed storage service: testing database pool status?

Post by Wessel, Keith
I was planning on adding some validation parameters. Right now, my setup is truly minimal.

You definitely need some stuff to make pooling work. With dbcp, I've
generally used the validate on get model, I think, and a validation query.
I'll double check my settings. But I only use this now with MySQL local to
the box, so it's not much of a risky scenario.

Post by Wessel, Keith
As for what type of pooling, I'm not sure exactly what you're asking
there. Let me try to answer that: it's a container resource that looks
<Resource name="jdbc/idp"
Auth="Container"
Type="javax.sql.DataSource"
. />
In addition, it has my Oracle driver, connection params, and a maxWait of 10,000 ms.

That means you're not pooling, or you're using something from Oracle
inside the driver, and it's probably broken. You want to wrap that driver
in a dbcp data source object. I'll post what I use when I have a chance.

Post by Wessel, Keith
I'm planning to add a validation query. I suspect that'll do the trick,
but it'd certainly be good to monitor it somehow. Maybe Scott's right and
we should just have the SLBs and service monitoring perform a full login.

I would think you'd want that anyway, for monitoring at least. webisoget
is your friend. I don't have the SLBs doing much other than an HTTP GET at
this point though.

-- Scott

[Cantor, Scott]

Post by Wessel, Keith
So, it sounds like Tomcat 6, if I read that right, uses DBCP by default.
Of course, since I had no query validation or pool object eviction
enabled, that does me little good.

I wouldn't really count on that. Just use a recent dbcp and configure it
yourself. Tomcat never did pooling before, and I wouldn't trust them to
keep doing it. I don't even think they *should*; it makes no sense to
override the deployer's explicit choice of DataSource because they think
they know better. In fact, they don't, because a default pool is not
useful.

-- Scott

[Wessel, Keith]

Just to confirm, Scott, you're suggesting I explicitly state:

factory=" org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"

rather than relying on Tomcat's hard-coded default behavior?

I agree; no sense in relying on what someone else thinks I should be using and forces me into. Just making sure I understand your suggestion.

Keith


-----Original Message-----
From: users-bounces-***@public.gmane.org [mailto:users-bounces-***@public.gmane.org] On Behalf Of Cantor, Scott
Sent: Thursday, August 28, 2014 2:59 PM
To: Shib Users
Subject: Re: DB-backed storage service: testing database pool status?

Post by Wessel, Keith
So, it sounds like Tomcat 6, if I read that right, uses DBCP by default.
Of course, since I had no query validation or pool object eviction
enabled, that does me little good.

I wouldn't really count on that. Just use a recent dbcp and configure it
yourself. Tomcat never did pooling before, and I wouldn't trust them to
keep doing it. I don't even think they *should*; it makes no sense to
override the deployer's explicit choice of DataSource because they think
they know better. In fact, they don't, because a default pool is not
useful.

-- Scott

[Cantor, Scott]

Post by Wessel, Keith
factory=" org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
rather than relying on Tomcat's hard-coded default behavior?

I don't think that's the dbcp package names.

http://commons.apache.org/proper/commons-dbcp/

I'm suggesting you use the actual dbcp code, not whatever Tomcat's
developers imprudently decided to include in their own jars.

One reason is that it makes your deployment less dependent on Tomcat as a
container.

-- Scott

[Wessel, Keith]

Thanks, Scott. That sounds like an even better plan. Just to confirm since the examples are a broken link and the configuration docs don't talk about a factory selection, I enable this by installing the jar and adding:

factory="org.apache.commons.dbcp2.BasicDataSourceFactory"

to my resource definition.

That right?

Keith


-----Original Message-----
From: users-bounces-***@public.gmane.org [mailto:users-bounces-***@public.gmane.org] On Behalf Of Cantor, Scott
Sent: Thursday, August 28, 2014 3:33 PM
To: Shib Users
Subject: Re: DB-backed storage service: testing database pool status?

Post by Wessel, Keith
factory=" org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
rather than relying on Tomcat's hard-coded default behavior?

I don't think that's the dbcp package names.

http://commons.apache.org/proper/commons-dbcp/

I'm suggesting you use the actual dbcp code, not whatever Tomcat's
developers imprudently decided to include in their own jars.

One reason is that it makes your deployment less dependent on Tomcat as a
container.

-- Scott

[Cantor, Scott]

Post by Wessel, Keith
Thanks, Scott. That sounds like an even better plan. Just to confirm
since the examples are a broken link and the configuration docs don't
talk about a factory selection, I enable this by installing the jar and
factory="org.apache.commons.dbcp2.BasicDataSourceFactory"
to my resource definition.
That right?

Don't know. I use an older version and I don't use Tomcat. Jetty's syntax
is different. I don't have a copy of my older Tomcat files anymore.

I don't recall any factory thing. Jetty just wants the data source class
(org.apache.commons.dbcp.BasicDataSource in my case) and I pass in the
driver class and all the other details to it. I thought I did something
similar before, but that was Tomcat 5.

-- Scott

[Wessel, Keith]

Thanks, Scott. I can work with that.

Keith


-----Original Message-----
From: users-bounces-***@public.gmane.org [mailto:users-bounces-***@public.gmane.org] On Behalf Of Cantor, Scott
Sent: Thursday, August 28, 2014 4:49 PM
To: Shib Users
Subject: Re: DB-backed storage service: testing database pool status?

Post by Wessel, Keith
Thanks, Scott. That sounds like an even better plan. Just to confirm
since the examples are a broken link and the configuration docs don't
talk about a factory selection, I enable this by installing the jar and
factory="org.apache.commons.dbcp2.BasicDataSourceFactory"
to my resource definition.
That right?

Don't know. I use an older version and I don't use Tomcat. Jetty's syntax
is different. I don't have a copy of my older Tomcat files anymore.

I don't recall any factory thing. Jetty just wants the data source class
(org.apache.commons.dbcp.BasicDataSource in my case) and I pass in the
driver class and all the other details to it. I thought I did something
similar before, but that was Tomcat 5.

-- Scott