does anyone have a good rule of thumb or some articles about how to set the size of their connection pool?
@dpsutton I think that's something that is so application-specific you won't find much advice beyond "measure and adjust".
It's going to depend on so many factors in how your code interacts with the database.
yeah figured. wondered if there was a good starting off point. also looking into how to measure. using Hikari i can set registerMbeans=true
it seems?
we're using the default of 15 as max. just wondering if there was a good guide to get in the ballpark before tuning
We monitor how production behaves with New Relic so we look at it from a holistic performance p.o.v.
but now i'm reading
> You can see from the video that reducing the connection pool size alone, in the absence of any other change, decreased the response times of the application from 100ms to 2ms -- over 50x improvement.
That's interesting... it's a bit of a "black art" TBH. Much like JVM tuning 🙂
yeah. so i'm trying to figure out the rules of thumb before refining further
We have different connection pool sizes in each app. We probably should do another round of monitoring and tuning -- it's been a while since we last did it and our traffic patterns have changed quite a bit. Although most of our response times are predicated on Elastic Search latency and response times nowadays 🙂
well i'm sorry to have added to your backlog then 🙂
FWIW, our production connection pool sizes range from minimums of 5-20 and maximums of 15-90 across a dozen apps.
@seancorfield i took that quote above from here: https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing . and it seems like quite a nice document. gives a good jumping off point to begin experiments as well
We're still using c3p0. I've been tempted to switch to HikariCP for years. But we tried to switch to BoneCP at one point and that was a horrible experience so it made us a bit gunshy about such future changes 🙂
Our DB servers are 24 core SSD machines so, based on that, we could pull back from 90 to about 50 and be fine. Although that would just be our high-traffic app. We could probably pull the connection pools size way down in the lower traffic apps. Goes off and creates an issue in JIRA at work!
hikaricp is really good. I remember bonecp too
hikaricp is also the preferred pool library for spring boot projects too.
it's my pool of choice. currently I have an app that consumes over 250,000 trips a day into postgresql. hikaricp is one thing I don't worry about 😄