More efficient connection management, i.e. reusing connections between calls)

[Mike Huniewicz]

Hi, my API uses JDBI, and each call to it results in a simple read-only query.

final ContentDao contentDao = jdbi.onDemand(ContentDao.class);

and then

@SqlQuery("select content_id from content_by_user_industry " +
        "where industry = :industry and period = :period order by users desc limit :limit")
abstract List<Content> findTopContentByUserIndustry(@Bind("industry") String userIndustry,
                                           @Bind("period") String periodString,
                                           @Bind("limit") int limit);

public List<Content> findTopContentByUserIndustry(String userIndustry, Period period, int limit) {
    return findTopContentByUserIndustry(userIndustry, period.toSql(), limit);
}

If I understand correctly, each call to that method results in a new connection being created and then discarded. That's not working so well when the load is heavy, because even though the call itself is inexpensive, the overhead of creating and closing the connection becomes significant.

What's the way around this?

[Christopher Currie]

Use a connection pool. Tomcat JDBC pool is a good choice.

---

This email was sent by a company owned by Financial Times Group Limited ("FT Group"), registered office at Number One Southwark Bridge, London SE1 9HL.  Registered in England and Wales with company number 879531. This e-mail may contain confidential information. If you are not the intended recipient, please notify the sender immediately, delete all copies and do not distribute it further.  It could also contain personal views which are not necessarily those of the FT Group.  We may monitor outgoing or incoming emails as permitted by law.

--
You received this message because you are subscribed to the Google Groups "jDBI" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+uns...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Mike Huniewicz]

It is using a connection pool already, I thought? The JDBI (or Dropwizard, can't remember) docs advise against using the Tomcat one.

--

Mike Huniewicz

BI Integration & API Developer

Technology BI & Data

Number One Southwark Bridge, London, SE1 9HL

[Christopher Currie]

IIRC, Dropwizard uses the tomcat pool in its default implementation, but it's been a while since I checked.

If you're using a connection pool, then you might need to tune its parameters. If you're having trouble with heavy load, you can increase the minimum number of idle connections so that you don't have to allocate new ones when load spikes. You might also need to extend the timeout for closing idle connections, to prevent a connection being closed before it can be reused.

If you're using Dropwizard metrics, I believe it collects stats on the pool, so you might be able to gain insight from them on where tuning might be needed.

HTH,
Christopher

[Matthew Hall]

Correct: Dropwizard's ManagedPoolDataSource extends Tomcat's DataSourceProxy.

[Mike Huniewicz]

Thanks, Christopher. Below are my connection parameters.

maxWaitForConnection: 1s
validationQuery: "/* MyService Health Check */ SELECT 1"
# the minimum number of connections to keep open
minSize: 8
# the maximum number of connections to keep open
maxSize: 32
# whether or not idle connections should be validated
checkConnectionWhileIdle: true
# the amount of time to sleep between runs of the idle connection validation, abandoned cleaner and idle pool resizing
evictionInterval: 10s
# the minimum amount of time an connection must sit idle in the pool before it is eligible for eviction
minIdleTime: 1 minute
maxConnectionAge: 1 minute
checkConnectionOnConnect: true
checkConnectionOnReturn: true
checkConnectionOnBorrow: true
logAbandonedConnections: true
logValidationErrors: true

[Mike Huniewicz]

Matthew, are you saying I don't need to change the connection pool?

[Brian McCallister]

FWIW, I default to Hikari nowadays

[Matthew Hall]

I'm relatively new to the JDBI project, so I can't speak to any prior comments that have been made discouraging use of Tomcat pool. I was not aware of any such comment before this thread.

My team has been using JDBI with the standard Dropwizard connection pool for over a year, and haven't found ManagedPooledDataSource itself to be a problem.

We have occasionally found we needed to tune the pool parameters. In one case we found it advantageous to reduce the minimum pool size to 1, because Amazon RDS couldn't cope with all the new connections at once when rolling a new version of the app. Extending the idle timeout as Christopher suggested might also help.

Another suggestion: we recently had some connection leaked discovered and patched. If you're not already using the latest release of JDBI, try upgrading to it now and see if that makes a difference.

Lastly: Try doing without DBI.onDemand, and favor DBI.open() instead. If you need to make successive database calls for the same request, this will cut down on churn waiting for the connection pool.

[Steven Schlansker]

On Feb 23, 2016, at 10:01 AM, Matthew Hall <quali...@gmail.com> wrote:

> I'm relatively new to the JDBI project, so I can't speak to any prior comments that have been made discouraging use of Tomcat pool. I was not aware of any such comment before this thread.

I think this is probably something from the Dropwizard project or another unrelated blog. Even if it was said by a JDBI contributor at some point, I don't think it's an official project stance.

There were a lot of problems with DBCP back in the day, but it was rewritten to DBCP2 and I believe Tomcat DBCP is based on that. So there's some leftover hatred for DBCP that may not be deserved nowadays (I personally have not used it)

[Mike Huniewicz]

Matt, we are connecting to RDS as well, that's not causing any problems. It's the connection to a relatively fast and small Postgres which is causing issues.

I was wrong, sorry for causing a misunderstanding - it's another connection pool (Apache I think) which was discouraged. Anyway, since Dropwizard is using the Tomcat one anyway, no changes to be done here, I understand?

I will see if upgrading jdbi is an option, but I would also need to upgrade Dropwizard itself.

Other than that, I will try to fiddle with idle time and minimum connections, see what happens. Thanks everyone, I will come back with the results.

---

This email was sent by a company owned by Financial Times Group Limited ("FT Group"), registered office at Number One Southwark Bridge, London SE1 9HL.  Registered in England and Wales with company number 879531. This e-mail may contain confidential information. If you are not the intended recipient, please notify the sender immediately, delete all copies and do not distribute it further.  It could also contain personal views which are not necessarily those of the FT Group.  We may monitor outgoing or incoming emails as permitted by law.

[Steven Schlansker]

You shouldn't need to upgrade DropWizard wholesale, assuming you use Maven you can add a

<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.jdbi</groupId>
<artifactId>jdbi</artifactId>
<version>2.71</version>
</dependency>
</dependencies>
</dependencyManagement>

or the like -- you can individually upgrade jdbi independently. (You should upgrade DropWizard
too probably, just be aware it's not strictly necessary!)