I have a Clojure web application that serves multiple clients (~80 and growing), each client is a research project that has their own MySQL database on our server. So we have ~80 databases that the application connects to (each client has their own subdomain, so the web request is mapped from domain to database).
I started the app using the Luminus template that uses the HikariCP connection pool. We had much fewer clients then so I created one pool for each client. When the number of MySQL connections grew, I reduced the number of connections per pool to 5, but that still means that we have 400 open connections on our server. I don’t think that is optimal.
I would like to solve this. I asked a question a few years ago on Stackoverflow (mysql - HikariCP number of connections in multiple pools - Stack Overflow) and got the suggestion to create a new connection for each request (so no connection pooling). I have not actually tried that yet, because I (erroneously?) believe that it would be preferable if I can use connection pool with a fixed number of max connections so that my connections don’t grow beyond control if multiple simultaneous requests are made to the server. And also, although the load is not super high on the server, sometimes we get quite a few simultaneous requests and if a connection pool would reduce response time compared to creating new connections, I would of course prefer that.
My idea is instead to have only one connection pool with a database user that can access all client databases. And to wrap each query like this
(defn wrap-query [pool db-name query] (jdbc/with-db-connection [conn pool] (jdbc/execute! conn (str "USE " db-name)) (jdbc/query conn query)))
But that requires that there is no race condition and no other process can use the connection between the
USE query and the actual query. But if I understand correctly,
with-db-connection “checks out” the connection from the pool and returns it when it is “closed”?
Does it make sense to use a connection pool like that? Or is there any other way that I can reduce the number of open connections without opening one connection on each request?