mysql - HikariCP 多个池中的连接数

Question

我通过 hikari-cp Clojure 库在 Clojure 应用程序中使用 HikariCP。

该应用程序为多个客户端提供服务，这些客户端都有自己的 MySQL 数据库和数据库用户。每个客户端都有自己的 HikariCP 连接池，根据我的理解，这是必需的，因为每个客户端都有一个专用的数据库用户。添加新客户端后，我开始从 MySQL 收到间歇性的“连接过多”错误。我猜想每个客户端 10 个连接的默认池大小加起来会导致连接过多(超过 MySQL 中 max_connections 默认设置 151)。

总体/每个客户端数据库负载不是很大。

简单地减少每个客户端的连接数似乎很麻烦，因为每当添加新客户端时就必须减少池大小。

简单地将 max_connections 设置为与客户端数量成比例的数字(例如 50 + [客户端数量] * 10)是否安全？

或者有没有办法使用同一个池连接到不同的数据库(具有不同的数据库用户)？

Answer 1

Here is an example不使用连接池的测试文件。您可以为单个命令、一组命令或每个事务创建新的数据库连接:

(def raw-db-spec
  {:classname   "org.h2.Driver"
   :subprotocol "h2:mem"    ; the prefix `jdbc:` is added automatically
   :subname     "demo;DB_CLOSE_DELAY=-1" ; `;DB_CLOSE_DELAY=-1` very important!!!
                     ; http://www.h2database.com/html/features.html#in_memory_databases
                     ; http://makble.com/using-h2-in-memory-database-in-clojure
   :user        "sa"        ; "system admin"
   :password    ""          ; empty string by default
   })

(dotest
  ; creates & drops a connection (& transaction) for each command
  (jdbc/db-do-commands raw-db-spec ["drop table if exists langs"
                                    "drop table if exists releases"])

  ; Creates and uses a connection for all commands
  (jdbc/with-db-connection
    [conn raw-db-spec]
    (jdbc/db-do-commands
      conn
      [(jdbc/create-table-ddl :langs
                              [[:id :serial]
                               [:lang "varchar not null"]])
       (jdbc/create-table-ddl :releases
                              [[:id :serial]
                               [:desc "varchar not null"]
                               [:langId "numeric"]])]))

  ; create & use a connection for multiple commands
  (jdbc/with-db-connection
    [conn raw-db-spec]
    (jdbc/insert-multi! raw-db-spec :langs ; => ({:id 1} {:id 2})
                        [{:lang "Clojure"}
                         {:lang "Java"}])

    (let [result (jdbc/query raw-db-spec ["select * from langs"])]
      (is= result [{:id 1, :lang "Clojure"}
                   {:id 2, :lang "Java"}])))

  ; Wraps all commands in a single transaction
  (jdbc/with-db-transaction
    [tx raw-db-spec]
    (let [clj-id (grab :id (only (jdbc/query tx ["select id from langs where lang='Clojure'"])))]
      (jdbc/insert-multi! tx :releases
                          [{:desc "ancients" :langId clj-id}
                           {:desc "1.8" :langId clj-id}
                           {:desc "1.9" :langId clj-id}]))
    (let [java-id (grab :id (only (jdbc/query tx ["select id from langs where lang='Java'"])))]
      (jdbc/insert-multi! tx :releases
                          [{:desc "dusty" :langId java-id}
                           {:desc "8" :langId java-id}
                           {:desc "9" :langId java-id}
                           {:desc "10" :langId java-id}])))

Here is a different file显示 Hikari DB 池的使用。

(def datasource-options-sample {:auto-commit        true
                                :read-only          false
                                :connection-timeout 30000
                                :validation-timeout 5000
                                :idle-timeout       600000
                                :max-lifetime       1800000
                                :minimum-idle       10
                                :maximum-pool-size  10
                                :pool-name          "db-pool"
                                :adapter            "h2" ; "postgresql"
                                :username           "sa"
                                :password           ""
                                :database-name      "database"
                                :server-name        "localhost"
                                :port-number        5432
                                :register-mbeans    false})

(def datasource-options {:adapter  "h2"
                         :url      "jdbc:h2:mem:demo;DB_CLOSE_DELAY=-1"
                         :username "sa"
                         :password ""})

(def ^:dynamic db-conn nil)

(defn with-connection-pool
  "Creates and uses a connection for test function"
  [tst-fn]
  (let [datasource (pool/make-datasource datasource-options)]
    (binding [db-conn {:datasource datasource}]
      (tst-fn)
      (pool/close-datasource datasource)))) ; close the connection - also closes/destroys the in-memory database

(use-fixtures
  :once with-connection-pool) ; use the same db connection pool for all tests

(dotest
  ; creates & drops a connection (& transaction) for each command
  (jdbc/db-do-commands db-conn ["drop table if exists langs"
                                "drop table if exists releases"])

  (jdbc/db-do-commands
    db-conn
    [(jdbc/create-table-ddl :langs
                            [[:id :serial]
                             [:lang "varchar not null"]])
     (jdbc/create-table-ddl :releases
                            [[:id :serial]
                             [:desc "varchar not null"]

                             [:langId "numeric"]])])
  (jdbc/insert-multi! db-conn :langs ; => ({:id 1} {:id 2})
                      [{:lang "Clojure"}
                       {:lang "Java"}])
  (let [result (jdbc/query db-conn ["select * from langs"])]
    (is= result [{:id 1, :lang "Clojure"}
                 {:id 2, :lang "Java"}]))

我建议对您的项目进行 fork 并删除所有连接池内容并尝试运行它。如果您想添加，可以使用 (jdbc/with-db-connection ...)或(jdbc/with-db-connection ...)` 将多个命令分组到单个数据库连接中稍后再说。