多个池中的连接数

我正在通过hikari cp clojure库在clojure应用程序中使用hikaricp。
该应用程序服务于多个客户端，这些客户端都有自己的mysql数据库和数据库用户。每个客户端都有自己的hikaricp连接池，据我所知，这是必需的，因为每个客户端都有一个专用的数据库用户。在添加了一个新的客户端之后，我开始从mysql中得到间歇性的“连接太多”错误。我猜默认池大小（每个客户端10个连接）加起来就是太多的连接（比mysql中的max\u connections默认设置151还要多）。
总体/每客户端数据库负载不是很大。
简单地减少每个客户机的连接数似乎很麻烦，因为每当添加新客户机时，池大小就必须减小。
只需将max_connections设置为与客户端数量成比例的数字，比如50+[客户端数量]*10，安全吗？
或者有没有一种方法可以使用同一个池连接到不同的数据库（使用不同的数据库用户）？

下面是一个不使用连接池的示例测试文件。可以为单个命令、一组命令或每个事务创建新的db连接：

(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}])))

下面是一个不同的文件，展示了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"}]))

我建议你做一个分叉的项目，删除所有的连接池的东西，并尝试运行它。您可以使用（jdbc/with db connection…） or （jdbc/with db connection…）`将多个命令分组到单个db连接中（如果以后要添加）。

多个池中的连接数

1条答案

相关问题

热门标签

最新问答