Too many sleep connections in MySQL
I am using MySQL database which is hosted on Google Compute Engine and is accessed from another compute engine via PHP.
Whenever i run
SHOW PROCESSLIST
There are about 5-6 sleep connections in the result which is OK with me as it does not downgrade the performance of the system. But whenever there is heavy traffic on the website, the sleep connection increases to 100+ thus causing the entire system to slow down.
I checked online and found that these sleep connections are open mysql connections waiting for command.
But i close my SQL connection after every query. Here is an example of how i am using PHP to access MySQL DB.
function db_connect()
{
$host = "100.100.100.100"; // dummy
$username = "abc";
$pass = "1234";
$db = "database";
$connection = new mysqli($host, $username, $pass, $db);
$connection->set_charset("utf8");
return $connection;
}
function closeConnection($db)
{
$db->close();
}
function runQuery($query)
{
$db = db_connect();
$sql = $db->query($query);
closeConnection($db);
return $sql;
}
I pass the SQL statement to the runQuery function which opens up a new connection and closes it after executing the query. I know opening a new connection for every query on the page will reduce the performance of the page but this way i am sure that i am not leaving any connection open.
Still, when the traffic is high, i am getting sleep connections.
I have attached a sample of SHOW PROCESSLIST below.
Please help me resolve this issue.
mysql
asked Nov 23 '18 at 12:05
Vitul GoyalVitul Goyal
126211
add a comment |
I am using MySQL database which is hosted on Google Compute Engine and is accessed from another compute engine via PHP.
Whenever i run
SHOW PROCESSLIST
There are about 5-6 sleep connections in the result which is OK with me as it does not downgrade the performance of the system. But whenever there is heavy traffic on the website, the sleep connection increases to 100+ thus causing the entire system to slow down.
I checked online and found that these sleep connections are open mysql connections waiting for command.
But i close my SQL connection after every query. Here is an example of how i am using PHP to access MySQL DB.
function db_connect()
{
$host = "100.100.100.100"; // dummy
$username = "abc";
$pass = "1234";
$db = "database";
$connection = new mysqli($host, $username, $pass, $db);
$connection->set_charset("utf8");
return $connection;
}
function closeConnection($db)
{
$db->close();
}
function runQuery($query)
{
$db = db_connect();
$sql = $db->query($query);
closeConnection($db);
return $sql;
}
I pass the SQL statement to the runQuery function which opens up a new connection and closes it after executing the query. I know opening a new connection for every query on the page will reduce the performance of the page but this way i am sure that i am not leaving any connection open.
Still, when the traffic is high, i am getting sleep connections.
I have attached a sample of SHOW PROCESSLIST below.
Please help me resolve this issue.
mysql
asked Nov 23 '18 at 12:05
Vitul GoyalVitul Goyal
126211
You can reducewait_timeoutvariable value if you have access to config files of your MySQL server.
– Madhur Bhaiya
Nov 23 '18 at 12:07
Having burnt with something like this before; Too many sleep connections is generally due to few query(s) being not efficient. Once such an inefficient query gets fired multiple times in concurrent manner, it is going to affect everything else. In case of heavy traffic, every other query starts hanging because previous queries are not yet finished . Enableslow_query_logand check for the bad queries.
– Madhur Bhaiya
Nov 23 '18 at 12:15
add a comment |
I am using MySQL database which is hosted on Google Compute Engine and is accessed from another compute engine via PHP.
Whenever i run
SHOW PROCESSLIST
There are about 5-6 sleep connections in the result which is OK with me as it does not downgrade the performance of the system. But whenever there is heavy traffic on the website, the sleep connection increases to 100+ thus causing the entire system to slow down.
I checked online and found that these sleep connections are open mysql connections waiting for command.
But i close my SQL connection after every query. Here is an example of how i am using PHP to access MySQL DB.
function db_connect()
{
$host = "100.100.100.100"; // dummy
$username = "abc";
$pass = "1234";
$db = "database";
$connection = new mysqli($host, $username, $pass, $db);
$connection->set_charset("utf8");
return $connection;
}
function closeConnection($db)
{
$db->close();
}
function runQuery($query)
{
$db = db_connect();
$sql = $db->query($query);
closeConnection($db);
return $sql;
}
I pass the SQL statement to the runQuery function which opens up a new connection and closes it after executing the query. I know opening a new connection for every query on the page will reduce the performance of the page but this way i am sure that i am not leaving any connection open.
Still, when the traffic is high, i am getting sleep connections.
I have attached a sample of SHOW PROCESSLIST below.
Please help me resolve this issue.
mysql
asked Nov 23 '18 at 12:05
Vitul GoyalVitul Goyal
126211
I am using MySQL database which is hosted on Google Compute Engine and is accessed from another compute engine via PHP.
Whenever i run
SHOW PROCESSLIST
There are about 5-6 sleep connections in the result which is OK with me as it does not downgrade the performance of the system. But whenever there is heavy traffic on the website, the sleep connection increases to 100+ thus causing the entire system to slow down.
I checked online and found that these sleep connections are open mysql connections waiting for command.
But i close my SQL connection after every query. Here is an example of how i am using PHP to access MySQL DB.
function db_connect()
{
$host = "100.100.100.100"; // dummy
$username = "abc";
$pass = "1234";
$db = "database";
$connection = new mysqli($host, $username, $pass, $db);
$connection->set_charset("utf8");
return $connection;
}
function closeConnection($db)
{
$db->close();
}
function runQuery($query)
{
$db = db_connect();
$sql = $db->query($query);
closeConnection($db);
return $sql;
}
I pass the SQL statement to the runQuery function which opens up a new connection and closes it after executing the query. I know opening a new connection for every query on the page will reduce the performance of the page but this way i am sure that i am not leaving any connection open.
Still, when the traffic is high, i am getting sleep connections.
I have attached a sample of SHOW PROCESSLIST below.
Please help me resolve this issue.
mysql
mysql
asked Nov 23 '18 at 12:05
Vitul GoyalVitul Goyal
126211
asked Nov 23 '18 at 12:05
Vitul GoyalVitul Goyal
126211
asked Nov 23 '18 at 12:05
Vitul GoyalVitul Goyal
126211
asked Nov 23 '18 at 12:05
Vitul GoyalVitul Goyal
126211
asked Nov 23 '18 at 12:05
Vitul GoyalVitul Goyal
126211
126211
You can reducewait_timeoutvariable value if you have access to config files of your MySQL server.
– Madhur Bhaiya
Nov 23 '18 at 12:07
Having burnt with something like this before; Too many sleep connections is generally due to few query(s) being not efficient. Once such an inefficient query gets fired multiple times in concurrent manner, it is going to affect everything else. In case of heavy traffic, every other query starts hanging because previous queries are not yet finished . Enableslow_query_logand check for the bad queries.
– Madhur Bhaiya
Nov 23 '18 at 12:15
add a comment |
You can reducewait_timeoutvariable value if you have access to config files of your MySQL server.
– Madhur Bhaiya
Nov 23 '18 at 12:07
Having burnt with something like this before; Too many sleep connections is generally due to few query(s) being not efficient. Once such an inefficient query gets fired multiple times in concurrent manner, it is going to affect everything else. In case of heavy traffic, every other query starts hanging because previous queries are not yet finished . Enableslow_query_logand check for the bad queries.
– Madhur Bhaiya
Nov 23 '18 at 12:15
You can reduce
wait_timeout variable value if you have access to config files of your MySQL server.– Madhur Bhaiya
Nov 23 '18 at 12:07
You can reduce
wait_timeout variable value if you have access to config files of your MySQL server.– Madhur Bhaiya
Nov 23 '18 at 12:07
Having burnt with something like this before; Too many sleep connections is generally due to few query(s) being not efficient. Once such an inefficient query gets fired multiple times in concurrent manner, it is going to affect everything else. In case of heavy traffic, every other query starts hanging because previous queries are not yet finished . Enable
slow_query_log and check for the bad queries.– Madhur Bhaiya
Nov 23 '18 at 12:15
Having burnt with something like this before; Too many sleep connections is generally due to few query(s) being not efficient. Once such an inefficient query gets fired multiple times in concurrent manner, it is going to affect everything else. In case of heavy traffic, every other query starts hanging because previous queries are not yet finished . Enable
slow_query_log and check for the bad queries.– Madhur Bhaiya
Nov 23 '18 at 12:15
add a comment |
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%2f53446428%2ftoo-many-sleep-connections-in-mysql%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%2f53446428%2ftoo-many-sleep-connections-in-mysql%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
You can reduce
wait_timeoutvariable value if you have access to config files of your MySQL server.– Madhur Bhaiya
Nov 23 '18 at 12:07
Having burnt with something like this before; Too many sleep connections is generally due to few query(s) being not efficient. Once such an inefficient query gets fired multiple times in concurrent manner, it is going to affect everything else. In case of heavy traffic, every other query starts hanging because previous queries are not yet finished . Enable
slow_query_logand check for the bad queries.– Madhur Bhaiya
Nov 23 '18 at 12:15