Fork me on GitHub
#sql
<
2017-12-27
>
plins19:12:07

can anyone recommends me a JDBC connection pool? im having strange issues using c3p0 like msgs `com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure The last packet successfully received from the server was 125,692 milliseconds ago. The last packet sent successfully to the server was 7 milliseconds ago.`

seancorfield20:12:13

@plins We use c3p0 in production and have no problems -- but you have to configure it properly.

seancorfield20:12:28

Which database are you using?

plins20:12:09

anywhere where i could find some good production examples?

plins20:12:23

right now i think im going with the defaults

seancorfield20:12:05

Here's the configuration we use in production -- the main thing to look at are your timeouts for idle and test connections

cpds    (doto (ComboPooledDataSource.)
                      (.setDataSourceName dsn-name)
                      (.setDriverClass "com.mysql.jdbc.Driver")
                      (.setJdbcUrl jdbc-url)
                      (.setUser user)
                      (.setPassword password)
                      ;; pool size (default is 3 and 15):
                      (.setMinPoolSize min-pool)
                      (.setMaxPoolSize max-pool)
                      (.setNumHelperThreads num-helpers)
                      ;; initial pool size default is 3 but actual pool size
                      ;; must be between minimum and maximum pool size
                      (.setInitialPoolSize min-pool)
                      ;; expire excess connections after 10 mins of inactivity:
                      (.setMaxIdleTimeExcessConnections (* 10 60))
                      ;; expire connections after 61 minutes of inactivity:
                      (.setMaxIdleTime (* 61 60))
                      ;; idle connection testing
                      (.setAutomaticTestTable "c3p0connection")
                      (.setTestConnectionOnCheckin true)
                      (.setIdleConnectionTestPeriod (* 11 60))
                      ;; unreturned (app leakage) get zapped after an hour:
                      (.setUnreturnedConnectionTimeout (* 59 60)))]

noisesmith20:12:16

@plins also, generally, when hitting a problem like this I’ve had the most success with googling how to address the issue in java - the translation to clojure is always trivial and the problems are identical

seancorfield20:12:49

MySQL also has an issue that if you don't explicitly time things out it messes up connections after eight hours I believe (we hit that initially before we added all those timeouts).

noisesmith20:12:55

(I’ve also used c3p0 with no issue)

seancorfield20:12:43

But we've had this configuration in production for years now with heavy traffic -- powering over 100 dating sites with millions of users 🙂

plins21:12:09

thanks everybody for the comments 🙂

jgh21:12:42

100 dating sites? Must be get pretty niche

seancorfield21:12:15

It's an... interesting... business to be in. With some very specific data challenges at times 🙂

jgh22:12:07

huh, interesting