Why MySQL connections remain in CLOSE_WAIT state for long time?
In my Spring MVC web application I use C3p0 for MySQL connection pooling. C3p0's maxIdleTime is set to 1500 seconds and at MySQL configuration wait_timeout is set to 1800 seconds.
My observation is, whenever I execute command netstat -tanop on ubuntu web server I see too many CLOSE_WAIT tcp connections to MySQL DB server.
If I run netstat on DB server to check whats going on then, the connection port which in CLOSE_WAIT state on web server is on FIN_WAIT2 state on DB server. It remain for some time (60 Sec) and it get closed from DB server but on web server that connection is still remain in CLOSE_WAIT (mostly near about 7200 secs).
Why connections on web server remain in CLOSE_WAIT state even if the same connection on DB server gets closed?
I tried setting net.ipv4.tcp_tw_reuse to 1 but nothing has changed.
I am confident about there is no DB connection leak in web application, because I enabled C3p0's properties that may help in finding DB leaks i.e.
debugUnreturnedConnectionStackTraces=true
unreturnedConnectionTimeout=30
This setting logs exception if connection is not closed for 30 seconds and it will consider as DB leak.
UPDATE
C3p0 properties configured in web App:
minPoolSize=10
acquireIncrement=1
maxPoolSize=500
initialPoolSize=10
numHelperThreads=100
maxIdleTime=2400
maxStatements=20
maxStatementsPerConnection=5
idleConnectionTestPeriod=120
acquireRetryAttempts=10
acquireRetryDelay=100
autoCommitOnClose=false
breakAfterAcquireFailure=false
testConnectionOnCheckout=true
testConnectionOnCheckin=true
preferredTestQuery =SELECT 1
debugUnreturnedConnectionStackTraces=true
unreturnedConnectionTimeout=30
java mysql tomcat jdbc c3p0
asked Nov 28 '18 at 7:27
AmoghAmogh
2,63783174
|
show 2 more comments
In my Spring MVC web application I use C3p0 for MySQL connection pooling. C3p0's maxIdleTime is set to 1500 seconds and at MySQL configuration wait_timeout is set to 1800 seconds.
My observation is, whenever I execute command netstat -tanop on ubuntu web server I see too many CLOSE_WAIT tcp connections to MySQL DB server.
If I run netstat on DB server to check whats going on then, the connection port which in CLOSE_WAIT state on web server is on FIN_WAIT2 state on DB server. It remain for some time (60 Sec) and it get closed from DB server but on web server that connection is still remain in CLOSE_WAIT (mostly near about 7200 secs).
Why connections on web server remain in CLOSE_WAIT state even if the same connection on DB server gets closed?
I tried setting net.ipv4.tcp_tw_reuse to 1 but nothing has changed.
I am confident about there is no DB connection leak in web application, because I enabled C3p0's properties that may help in finding DB leaks i.e.
debugUnreturnedConnectionStackTraces=true
unreturnedConnectionTimeout=30
This setting logs exception if connection is not closed for 30 seconds and it will consider as DB leak.
UPDATE
C3p0 properties configured in web App:
minPoolSize=10
acquireIncrement=1
maxPoolSize=500
initialPoolSize=10
numHelperThreads=100
maxIdleTime=2400
maxStatements=20
maxStatementsPerConnection=5
idleConnectionTestPeriod=120
acquireRetryAttempts=10
acquireRetryDelay=100
autoCommitOnClose=false
breakAfterAcquireFailure=false
testConnectionOnCheckout=true
testConnectionOnCheckin=true
preferredTestQuery =SELECT 1
debugUnreturnedConnectionStackTraces=true
unreturnedConnectionTimeout=30
java mysql tomcat jdbc c3p0
asked Nov 28 '18 at 7:27
AmoghAmogh
2,63783174
Did you tried to use also different connection pool,as hikari? It also has a leak detection option
– user7294900
Nov 28 '18 at 7:35
c3p0 ultimately callsclose()on Connections asynchronously, in its internal thread pool. It is possible that the thread pool is getting backed up, leaving the Connections logicallyclose()ed but not physicallyclose()ed. if this is the issue (it is just a guess, and i'm not sure it quite fits), increasing c3p0'snumHelperThreadssetting might help.
– Steve Waldman
Nov 28 '18 at 8:27
@SteveWaldman well, currentlynumHelperThreadsis set to 100. Do you still think I have to check after increasing it bit more? Post is updated will all C3p0 properties configured in web app.
– Amogh
Nov 28 '18 at 8:56
I'd sooner suspect the MySQL Connector/J driver than this being a problem in C3P0, the socket can't reach CLOSE_WAIT unless the socket was closed, which would only happen if C3P0 has already closed the connection. Which version of MySQL Connector/J are you using?
– Mark Rotteveel
Nov 28 '18 at 9:46
@Amogh no, if anything i’d adjustnumHelperThreadsdown from that, the context switching overhead probably outweighs any additional concurrency long before 100. but i’d defer to Mark onCLOSE_WAIT, i don’t fully understand the conditions under which you would see that.
– Steve Waldman
Nov 28 '18 at 9:54
|
show 2 more comments
In my Spring MVC web application I use C3p0 for MySQL connection pooling. C3p0's maxIdleTime is set to 1500 seconds and at MySQL configuration wait_timeout is set to 1800 seconds.
My observation is, whenever I execute command netstat -tanop on ubuntu web server I see too many CLOSE_WAIT tcp connections to MySQL DB server.
If I run netstat on DB server to check whats going on then, the connection port which in CLOSE_WAIT state on web server is on FIN_WAIT2 state on DB server. It remain for some time (60 Sec) and it get closed from DB server but on web server that connection is still remain in CLOSE_WAIT (mostly near about 7200 secs).
Why connections on web server remain in CLOSE_WAIT state even if the same connection on DB server gets closed?
I tried setting net.ipv4.tcp_tw_reuse to 1 but nothing has changed.
I am confident about there is no DB connection leak in web application, because I enabled C3p0's properties that may help in finding DB leaks i.e.
debugUnreturnedConnectionStackTraces=true
unreturnedConnectionTimeout=30
This setting logs exception if connection is not closed for 30 seconds and it will consider as DB leak.
UPDATE
C3p0 properties configured in web App:
minPoolSize=10
acquireIncrement=1
maxPoolSize=500
initialPoolSize=10
numHelperThreads=100
maxIdleTime=2400
maxStatements=20
maxStatementsPerConnection=5
idleConnectionTestPeriod=120
acquireRetryAttempts=10
acquireRetryDelay=100
autoCommitOnClose=false
breakAfterAcquireFailure=false
testConnectionOnCheckout=true
testConnectionOnCheckin=true
preferredTestQuery =SELECT 1
debugUnreturnedConnectionStackTraces=true
unreturnedConnectionTimeout=30
java mysql tomcat jdbc c3p0
asked Nov 28 '18 at 7:27
AmoghAmogh
2,63783174
In my Spring MVC web application I use C3p0 for MySQL connection pooling. C3p0's maxIdleTime is set to 1500 seconds and at MySQL configuration wait_timeout is set to 1800 seconds.
My observation is, whenever I execute command netstat -tanop on ubuntu web server I see too many CLOSE_WAIT tcp connections to MySQL DB server.
If I run netstat on DB server to check whats going on then, the connection port which in CLOSE_WAIT state on web server is on FIN_WAIT2 state on DB server. It remain for some time (60 Sec) and it get closed from DB server but on web server that connection is still remain in CLOSE_WAIT (mostly near about 7200 secs).
Why connections on web server remain in CLOSE_WAIT state even if the same connection on DB server gets closed?
I tried setting net.ipv4.tcp_tw_reuse to 1 but nothing has changed.
I am confident about there is no DB connection leak in web application, because I enabled C3p0's properties that may help in finding DB leaks i.e.
debugUnreturnedConnectionStackTraces=true
unreturnedConnectionTimeout=30
This setting logs exception if connection is not closed for 30 seconds and it will consider as DB leak.
UPDATE
C3p0 properties configured in web App:
minPoolSize=10
acquireIncrement=1
maxPoolSize=500
initialPoolSize=10
numHelperThreads=100
maxIdleTime=2400
maxStatements=20
maxStatementsPerConnection=5
idleConnectionTestPeriod=120
acquireRetryAttempts=10
acquireRetryDelay=100
autoCommitOnClose=false
breakAfterAcquireFailure=false
testConnectionOnCheckout=true
testConnectionOnCheckin=true
preferredTestQuery =SELECT 1
debugUnreturnedConnectionStackTraces=true
unreturnedConnectionTimeout=30
java mysql tomcat jdbc c3p0
java mysql tomcat jdbc c3p0
asked Nov 28 '18 at 7:27
AmoghAmogh
2,63783174
asked Nov 28 '18 at 7:27
AmoghAmogh
2,63783174
edited Nov 28 '18 at 8:56
Amogh
asked Nov 28 '18 at 7:27
AmoghAmogh
2,63783174
asked Nov 28 '18 at 7:27
AmoghAmogh
2,63783174
asked Nov 28 '18 at 7:27
AmoghAmogh
2,63783174
2,63783174
Did you tried to use also different connection pool,as hikari? It also has a leak detection option
– user7294900
Nov 28 '18 at 7:35
c3p0 ultimately callsclose()on Connections asynchronously, in its internal thread pool. It is possible that the thread pool is getting backed up, leaving the Connections logicallyclose()ed but not physicallyclose()ed. if this is the issue (it is just a guess, and i'm not sure it quite fits), increasing c3p0'snumHelperThreadssetting might help.
– Steve Waldman
Nov 28 '18 at 8:27
@SteveWaldman well, currentlynumHelperThreadsis set to 100. Do you still think I have to check after increasing it bit more? Post is updated will all C3p0 properties configured in web app.
– Amogh
Nov 28 '18 at 8:56
I'd sooner suspect the MySQL Connector/J driver than this being a problem in C3P0, the socket can't reach CLOSE_WAIT unless the socket was closed, which would only happen if C3P0 has already closed the connection. Which version of MySQL Connector/J are you using?
– Mark Rotteveel
Nov 28 '18 at 9:46
@Amogh no, if anything i’d adjustnumHelperThreadsdown from that, the context switching overhead probably outweighs any additional concurrency long before 100. but i’d defer to Mark onCLOSE_WAIT, i don’t fully understand the conditions under which you would see that.
– Steve Waldman
Nov 28 '18 at 9:54
|
show 2 more comments
Did you tried to use also different connection pool,as hikari? It also has a leak detection option
– user7294900
Nov 28 '18 at 7:35
c3p0 ultimately callsclose()on Connections asynchronously, in its internal thread pool. It is possible that the thread pool is getting backed up, leaving the Connections logicallyclose()ed but not physicallyclose()ed. if this is the issue (it is just a guess, and i'm not sure it quite fits), increasing c3p0'snumHelperThreadssetting might help.
– Steve Waldman
Nov 28 '18 at 8:27
@SteveWaldman well, currentlynumHelperThreadsis set to 100. Do you still think I have to check after increasing it bit more? Post is updated will all C3p0 properties configured in web app.
– Amogh
Nov 28 '18 at 8:56
I'd sooner suspect the MySQL Connector/J driver than this being a problem in C3P0, the socket can't reach CLOSE_WAIT unless the socket was closed, which would only happen if C3P0 has already closed the connection. Which version of MySQL Connector/J are you using?
– Mark Rotteveel
Nov 28 '18 at 9:46
@Amogh no, if anything i’d adjustnumHelperThreadsdown from that, the context switching overhead probably outweighs any additional concurrency long before 100. but i’d defer to Mark onCLOSE_WAIT, i don’t fully understand the conditions under which you would see that.
– Steve Waldman
Nov 28 '18 at 9:54
Did you tried to use also different connection pool,as hikari? It also has a leak detection option
– user7294900
Nov 28 '18 at 7:35
Did you tried to use also different connection pool,as hikari? It also has a leak detection option
– user7294900
Nov 28 '18 at 7:35
c3p0 ultimately calls
close() on Connections asynchronously, in its internal thread pool. It is possible that the thread pool is getting backed up, leaving the Connections logically close()ed but not physically close()ed. if this is the issue (it is just a guess, and i'm not sure it quite fits), increasing c3p0's numHelperThreads setting might help.– Steve Waldman
Nov 28 '18 at 8:27
c3p0 ultimately calls
close() on Connections asynchronously, in its internal thread pool. It is possible that the thread pool is getting backed up, leaving the Connections logically close()ed but not physically close()ed. if this is the issue (it is just a guess, and i'm not sure it quite fits), increasing c3p0's numHelperThreads setting might help.– Steve Waldman
Nov 28 '18 at 8:27
@SteveWaldman well, currently
numHelperThreads is set to 100. Do you still think I have to check after increasing it bit more? Post is updated will all C3p0 properties configured in web app.– Amogh
Nov 28 '18 at 8:56
@SteveWaldman well, currently
numHelperThreads is set to 100. Do you still think I have to check after increasing it bit more? Post is updated will all C3p0 properties configured in web app.– Amogh
Nov 28 '18 at 8:56
I'd sooner suspect the MySQL Connector/J driver than this being a problem in C3P0, the socket can't reach CLOSE_WAIT unless the socket was closed, which would only happen if C3P0 has already closed the connection. Which version of MySQL Connector/J are you using?
– Mark Rotteveel
Nov 28 '18 at 9:46
I'd sooner suspect the MySQL Connector/J driver than this being a problem in C3P0, the socket can't reach CLOSE_WAIT unless the socket was closed, which would only happen if C3P0 has already closed the connection. Which version of MySQL Connector/J are you using?
– Mark Rotteveel
Nov 28 '18 at 9:46
@Amogh no, if anything i’d adjust
numHelperThreads down from that, the context switching overhead probably outweighs any additional concurrency long before 100. but i’d defer to Mark on CLOSE_WAIT, i don’t fully understand the conditions under which you would see that.– Steve Waldman
Nov 28 '18 at 9:54
@Amogh no, if anything i’d adjust
numHelperThreads down from that, the context switching overhead probably outweighs any additional concurrency long before 100. but i’d defer to Mark on CLOSE_WAIT, i don’t fully understand the conditions under which you would see that.– Steve Waldman
Nov 28 '18 at 9:54
|
show 2 more comments
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53514212%2fwhy-mysql-connections-remain-in-close-wait-state-for-long-time%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53514212%2fwhy-mysql-connections-remain-in-close-wait-state-for-long-time%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Did you tried to use also different connection pool,as hikari? It also has a leak detection option
– user7294900
Nov 28 '18 at 7:35
c3p0 ultimately calls
close()on Connections asynchronously, in its internal thread pool. It is possible that the thread pool is getting backed up, leaving the Connections logicallyclose()ed but not physicallyclose()ed. if this is the issue (it is just a guess, and i'm not sure it quite fits), increasing c3p0'snumHelperThreadssetting might help.– Steve Waldman
Nov 28 '18 at 8:27
@SteveWaldman well, currently
numHelperThreadsis set to 100. Do you still think I have to check after increasing it bit more? Post is updated will all C3p0 properties configured in web app.– Amogh
Nov 28 '18 at 8:56
I'd sooner suspect the MySQL Connector/J driver than this being a problem in C3P0, the socket can't reach CLOSE_WAIT unless the socket was closed, which would only happen if C3P0 has already closed the connection. Which version of MySQL Connector/J are you using?
– Mark Rotteveel
Nov 28 '18 at 9:46
@Amogh no, if anything i’d adjust
numHelperThreadsdown from that, the context switching overhead probably outweighs any additional concurrency long before 100. but i’d defer to Mark onCLOSE_WAIT, i don’t fully understand the conditions under which you would see that.– Steve Waldman
Nov 28 '18 at 9:54