{"action":"created","issue":{"url":"https://api.github.com/repos/brettwooldridge/HikariCP/issues/315","labels_url":"https://api.github.com/repos/brettwooldridge/HikariCP/issues/315/labels{/name}","comments_url":"https://api.github.com/repos/brettwooldridge/HikariCP/issues/315/comments","events_url":"https://api.github.com/repos/brettwooldridge/HikariCP/issues/315/events","html_url":"https://github.com/brettwooldridge/HikariCP/issues/315","id":76092691,"number":315,"title":"Massive Load, Pool does not recover. ","user":{"login":"djerraballi","id":2452655,"avatar_url":"https://avatars.githubusercontent.com/u/2452655?v=3","gravatar_id":"","url":"https://api.github.com/users/djerraballi","html_url":"https://github.com/djerraballi","followers_url":"https://api.github.com/users/djerraballi/followers","following_url":"https://api.github.com/users/djerraballi/following{/other_user}","gists_url":"https://api.github.com/users/djerraballi/gists{/gist_id}","starred_url":"https://api.github.com/users/djerraballi/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/djerraballi/subscriptions","organizations_url":"https://api.github.com/users/djerraballi/orgs","repos_url":"https://api.github.com/users/djerraballi/repos","events_url":"https://api.github.com/users/djerraballi/events{/privacy}","received_events_url":"https://api.github.com/users/djerraballi/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":9,"created_at":"2015-05-13T19:18:10Z","updated_at":"2015-05-14T19:34:18Z","closed_at":null,"body":"Hi again, \r\n I am currently load testing my application. The behavior that i am seeing is that application and pool behave fine under reasonable load. When we start ramping up however, the db connections break down. Even after stopping the load, we do not recover until we restart the server. \r\n\r\nI suspect that configuration for the pool/db/dns are incorrect.\r\n\r\nHere is our DataSource config:\r\nhttps://gist.github.com/djerraballi/46b46ff89f5635af1c3e\r\n\r\nHere are the errors we start seeing when we start pushing the load up: \r\nhttps://gist.github.com/djerraballi/9ecb8e7727e3dac51769\r\n\r\nRestarting the jetty server fixed the issue. In an earlier question, we talked about dns caching at the jvm layer. \r\nHere our jvm settings for network address cacheing: \r\nnetworkaddress.cache.ttl=120\r\nnetworkaddress.cache.negative.ttl=10\r\n"},"comment":{"url":"https://api.github.com/repos/brettwooldridge/HikariCP/issues/comments/102145662","html_url":"https://github.com/brettwooldridge/HikariCP/issues/315#issuecomment-102145662","issue_url":"https://api.github.com/repos/brettwooldridge/HikariCP/issues/315","id":102145662,"user":{"login":"djerraballi","id":2452655,"avatar_url":"https://avatars.githubusercontent.com/u/2452655?v=3","gravatar_id":"","url":"https://api.github.com/users/djerraballi","html_url":"https://github.com/djerraballi","followers_url":"https://api.github.com/users/djerraballi/followers","following_url":"https://api.github.com/users/djerraballi/following{/other_user}","gists_url":"https://api.github.com/users/djerraballi/gists{/gist_id}","starred_url":"https://api.github.com/users/djerraballi/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/djerraballi/subscriptions","organizations_url":"https://api.github.com/users/djerraballi/orgs","repos_url":"https://api.github.com/users/djerraballi/repos","events_url":"https://api.github.com/users/djerraballi/events{/privacy}","received_events_url":"https://api.github.com/users/djerraballi/received_events","type":"User","site_admin":false},"created_at":"2015-05-14T19:34:18Z","updated_at":"2015-05-14T19:34:18Z","body":"Hey brett, yes, we have run a few tests and noted that at times the pool took a very long time to recover.I will try 2.3.7 and test today. I can also enable debug logging and see whats up, we also have added a metrics registry, so it is easy to take a host and see the pool size, pending/active conns, and rate-meanWaitTime.\r\n\r\n What i suspect is that our webserver is queing incoming requests seperately from the applications internal pool (only true if server doesn't have a timeout fi the request doesn't obtain a thread). In addition to not having this timeout, i also think our jetty threadpool size is much higher than what our cp is capable of serving, so when our jetty thread pool is saturated, the requests that obtain threads will still timeout at the cp level. This is just a suspcian\r\n\r\ngood catch on useLocalTransactionState, based on the bug looks like its fixed in latest version of connectorJ? I cant tell based on the comments. but i feel like umesh is saying its fixed. \r\n\r\nOn a side note with the network issuing:\r\n\r\nIn addition, i have been trying to reduce chattiness of our connections with the db. Connector/J provides a lot of nice features for reducing chattiness, (useConfigs=maxPeformance && useLocalTransactionState=\"true\") but even with all the settings turned on, i am still seeing quite a few of these:\r\n...1.../#....set session transaction read write\r\n\r\nI have also tried to use MariaDB, and i am seeing a bit more chattiness (unexpected): \r\n..=...=......set autocommit=0\r\n..=...=......SELECT @@tx_isolation\r\n..=...=......$....def....@@tx_isolation..!.*.......................READ-COMMITTED.........\r\n\r\nThese are essentially no-ops since those settings don't change between requests. Is there a setting that basically gets us this behavior when the connection is obtained: \r\n```\r\nif \r\n(\r\n con.getTransactionIsolation() !=\r\nConnection.TRANSACTION_READ_COMMITTED\r\n)\r\n{\r\n con.setTransactionIsolation\r\n(Connection.TRANSACTION_READ_COMMITTED);\r\n}\r\n```\r\nand I found this slide deck to be super informative: \r\nhttp://www.percona.com/live/mysql-conference-2015/sites/default/files/slides/Java%20MySQL%20Connector%20%26%20Connection%20Pool%20Features%20%26%20Optimization-Version2.pdf\r\n"}}