it's that all jdbc operation must blocking the thread? why there's no async jdbc driver. I saw a project called PostgresAsync, but didn't update for a while

Morning

I’m using jdbc for my first time with clojure

The company I work, have a lot of databases (500+), which represents each store (ecommerce), can I somehow work with c3p0 on top of that or just jdbc (https://github.com/clojure/java.jdbc) is enough?

I tried c3p0 but it was returning outofmemory, creating so many threads (new pool for each store)

@rcustodio 500+ live connections to different databases may take quite a bit of memory no matter which way you do it. You might have better results with Hikari-CP (https://github.com/brettwooldridge/HikariCP), clojure wrapper here: https://github.com/tomekw/hikari-cp It's known for being lighter weight and faster. You might see if you can get the behavior you want by setting the minimumIdle for each pool to 0 (anyone know if this is a valid setting for this?). Your use case might also allow you to just make connections as needed with clojure.java.jdbc and pay the initial connection performance hit (always test this in your setup--it maybe acceptable).

Thanks @curtis.summers for the tip.. gonna check about this hikari, and how to make it work with a lot of dbs so far im just using jdbc and not having any trouble with it, gonna ask the server team to monitor the mysql

@rcustodio if you're getting OoM errors sure to thread creation, you could always increase the number of threads your process is allowed to create. The default for that on most systems is very conservative.

@seancorfield how? @curtis.summers 1 db is always 1 pool? So that means I have to create new instance of hikari (or c3p0) for each db, right?

The OoM error for threads come from an operating system applied level. So you just change that before running your process (assuming you're on Linux/Mac).

I'm sure it can also be done for Windows but I've no idea how.

It's the ulimit command on Linux/Mac.

how does increasing the thread count help oom?

I see, thanks @seancorfield

gonna check this hikari out, see if I can do anything

so many dbs

@jgh there are two types of OoM: regular out of heap memory and out of thread space. The latter is something you'll hit if the O/S denies the thread request based on the ulimit threshold.

ah, i didnt realize you would see the same error in both cases.

[root@actors02-00k ~]# ulimit -u
78361

The basic exception is the same but the message is different.

Hmm, 78k ought to be enough.

But you can tune the connection pool libraries to use fewer threads than their default.

yeah… need to decrease, and allow 0 to the pool, so when not using the db, doesnt leave any opened

I'd try to setup 100 pools and see how many threads your app is using and go from there.

Thannks @seancorfield gonna try it out

is it not possible to have the connection pools use a single thread pool? I can't imagine every connection pool needs a bunch of threads to themselves...

It's a bit of a black art I'm afraid. We messed with our connection pool settings a lot to get where we are.

yeah, seems so. i guess with the abstractions libraries provide it makes it more challenging since they aren't necessarily designed for your precise use-case

The connection went like this (didnt have any error so far)

(defn- conn [state db-name]
  (locking state
    (if (contains? @(:dbs state) (keyword db-name))
      (get @(:dbs state) (keyword db-name))
      (let [config (:config state)
            datasource (hikari/make-datasource {:jdbc-url (format (:jdbc-url config)
                                                                  (:server-name config)
                                                                  (:port-number config)
                                                                  db-name)
                                                :username (:username config)
                                                :password (:password config)
                                                :pool-name (str db-name "-pool")
                                                :minimum-idle 0
                                                :idle-timeout (* 10 1000)
                                                :maximum-pool-size 10})]
        (swap! (:dbs state) assoc (keyword db-name) {:datasource datasource})
        (get @(:dbs state) (keyword db-name))))))

its rmq

something like

(do
  (swap! state (fn [m] (if (contains? m db-name) m (assoc m db-name (delay code-to-create-datasource)))))
  @(get @state db-name)) 

I see, using delay

With delay I wont need the locking? that means more fast? performance?

Better for threads?

no

delay has a lock internally that it uses

but delay encapsulates the locking

I see, thanks

@hiredman like this?

(defn- conn [state db-name]
  (log/info "here")
  (if (contains? @(:dbs state) (keyword db-name))
    {:datasource @(get @(:dbs state) (keyword db-name))}
    (let [config (:config state)
          spec {:jdbc-url (format (:jdbc-url config)
                                    (:server-name config)
                                    (:port-number config)
                                    db-name)
                  :username (:username config)
                  :password (:password config)
                  :pool-name (str db-name "-pool")
                  :minimum-idle 0
                  :idle-timeout (* 10 1000)
                  :maximum-pool-size 10}]
      (swap! (:dbs state) (fn [databases]
                            (if (contains? databases (keyword db-name))
                              databases
                              (assoc databases
                                     (keyword db-name)
                                     (delay (hikari/make-datasource spec))))))
      {:datasource @(get @(:dbs state) (keyword db-name))})))

you don't need the outer if

but.. swap everytime it calls the conn, isnt that bad?

the if inside the function passed to swap convers it

cuz it means that would call swap everytime, and a lot (`jdbc/query (conn state db-name).....`)

W/ clojure.java.jdbc 0.7.7, the following db-spec doesn't work: I get Invalid Oracle URL specified

{:subprotocol "oracle",
 :classname "oracle.jdbc.OracleDriver",
 :subname "thin:@localhost:50161:xe",
 :user "system",
 :password "oracle"}

Both are w/ [com.oracle/ojdbc8 "12.2.0.1"]

@xiongtx I'd strongly recommend using the preferred db-spec map form instead of the one with subprotocol/subname etc if you can...

Which one's preferred?

The one described in the official documentation with :dbtype and :dbname.

http://clojure-doc.org/articles/ecosystem/java_jdbc/home.html

Hmm, I'll take a look, thanks

I think for your case it would be

{:dbtype "oracle"
 :dbname "xe" ;; your DB name?
 :user "system"
 :password "oracle"
 :port 50161}

I have no idea why the above would be accepted on 0.6.2-alpha1 but not on 0.7.7 (and I don't have Oracle available to try it).

@xiongtx OK, for 0.7.7 you'll need to remove thin: from your :subname as that is automatically added now, based on the :subprotocol. That's the difference from 0.6.2-alpha1 (and that's because Oracle is directly supported now in 0.7.7 via the :dbtype style map where it wasn't in 0.6.x).

Here's the JDBC URL created by 0.7.7 from your spec jdbc:oracle:thin:thin:@localhost:50161:xe (note the double thin: part)

Here's what my spec produces in 0.7.7 jdbc:oracle:thin:@127.0.0.1:50161/xe (default :host is 127.0.0.1 rather than localhost -- also note the /xe for the DB name rather than :xe... let me know if Oracle rejects that... I've not had any reports that it fails).

Oh, interesting. I'll give it a try

Just FYI @xiongtx this is the full list of changes from 0.6.2-alpha1 to 0.7.7 https://github.com/clojure/java.jdbc/compare/820f4c9834cd8f49761b4fbc504405a8357ca47d...master