Why is my threads_connected ALWAYS = 1?
I run a website with fairly high traffic. I've been having a lot of issues lately with "too many connections" or "could not connect" to MySQL and I think it's because of bots but I'm not 100% positive. I connect to the database using one username. So anyone who visits the site connects with the same script (and MySQL user).
If I have 1,000 people on the site at one time and I show processlist, the threads_connected is always 1. Is that normal? Could this be the cause of the too many connections issue?
The strange thing is, I can have 20,000 people on the site and it runs fine. But MySQL has been crashing a lot lately with an average of 200 people on the site - which my Amazon instance can EASILY handle. And there are always bots in the access_log when it goes down. I have tried so many things to stop MySQL from crashing and nothing is working.
mysql
edited Aug 16 '17 at 18:23
Jefferson Arrubla H
20718
asked Aug 16 '17 at 14:09
Chris FilippouChris Filippou
1061
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I run a website with fairly high traffic. I've been having a lot of issues lately with "too many connections" or "could not connect" to MySQL and I think it's because of bots but I'm not 100% positive. I connect to the database using one username. So anyone who visits the site connects with the same script (and MySQL user).
If I have 1,000 people on the site at one time and I show processlist, the threads_connected is always 1. Is that normal? Could this be the cause of the too many connections issue?
The strange thing is, I can have 20,000 people on the site and it runs fine. But MySQL has been crashing a lot lately with an average of 200 people on the site - which my Amazon instance can EASILY handle. And there are always bots in the access_log when it goes down. I have tried so many things to stop MySQL from crashing and nothing is working.
mysql
edited Aug 16 '17 at 18:23
Jefferson Arrubla H
20718
asked Aug 16 '17 at 14:09
Chris FilippouChris Filippou
1061
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I run a website with fairly high traffic. I've been having a lot of issues lately with "too many connections" or "could not connect" to MySQL and I think it's because of bots but I'm not 100% positive. I connect to the database using one username. So anyone who visits the site connects with the same script (and MySQL user).
If I have 1,000 people on the site at one time and I show processlist, the threads_connected is always 1. Is that normal? Could this be the cause of the too many connections issue?
The strange thing is, I can have 20,000 people on the site and it runs fine. But MySQL has been crashing a lot lately with an average of 200 people on the site - which my Amazon instance can EASILY handle. And there are always bots in the access_log when it goes down. I have tried so many things to stop MySQL from crashing and nothing is working.
mysql
edited Aug 16 '17 at 18:23
Jefferson Arrubla H
20718
asked Aug 16 '17 at 14:09
Chris FilippouChris Filippou
1061
I run a website with fairly high traffic. I've been having a lot of issues lately with "too many connections" or "could not connect" to MySQL and I think it's because of bots but I'm not 100% positive. I connect to the database using one username. So anyone who visits the site connects with the same script (and MySQL user).
If I have 1,000 people on the site at one time and I show processlist, the threads_connected is always 1. Is that normal? Could this be the cause of the too many connections issue?
The strange thing is, I can have 20,000 people on the site and it runs fine. But MySQL has been crashing a lot lately with an average of 200 people on the site - which my Amazon instance can EASILY handle. And there are always bots in the access_log when it goes down. I have tried so many things to stop MySQL from crashing and nothing is working.
mysql
mysql
edited Aug 16 '17 at 18:23
Jefferson Arrubla H
20718
asked Aug 16 '17 at 14:09
Chris FilippouChris Filippou
1061
edited Aug 16 '17 at 18:23
Jefferson Arrubla H
20718
asked Aug 16 '17 at 14:09
Chris FilippouChris Filippou
1061
edited Aug 16 '17 at 18:23
Jefferson Arrubla H
20718
edited Aug 16 '17 at 18:23
Jefferson Arrubla H
20718
edited Aug 16 '17 at 18:23
Jefferson Arrubla H
20718
20718
asked Aug 16 '17 at 14:09
Chris FilippouChris Filippou
1061
asked Aug 16 '17 at 14:09
Chris FilippouChris Filippou
1061
asked Aug 16 '17 at 14:09
Chris FilippouChris Filippou
1061
1061
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
There are two different issues -- threads connected and threads running.
In a typical, well oiled, production server, you will see this in SHOW PROCESSLIST:
- Lots of "Sleep" connections,
- Fewer than
max_connectionslines in the processlist, - One, or a small number, of non-Sleep connections.
- Most queries are so fast that they go back to Sleep before you notice it. I often see "thousands of Queries per second", yet "nothing running". This is good.
"Sleep" means that the connection is not running a query at the moment. Instead it might be doing application processing, or it might be part of a "connection pool".
The reason you always see "1" is that you have to run a query (SHOW PROCESSLIST or something else) to get the number. So the "1" is itself.
You said Threads_connected is always 1. Did you mean Threads_running? Note also the capitalization.
What is the value of thread_cache_size? 8 is typical, but the exact value does not matter a lot.
What about Max_used_connections and max_connections?
Is there any sort of "connection pooling"? Are there multiple clients?
Consider providing me with the info mentioned in tuning here .
answered Aug 20 '17 at 19:45
Rick JamesRick James
41.7k22258
I notice when the website goes down, (mysql stops responding or connection times out), that there is a lot of bot traffic. Applebot, twitterbot, bingbot, googlebot, pinterestbot are common browsers. Now it shows that they request the robots.txt file and get the sitemap - I thought that should alleviate them from browsing the entire site randomly. What can I do to keep these bots from overloading the connections to mysql? I have spent months trying to solve this. Slowing crawl rate, a dynamic sitemap, editing the settings on max connections etc,. I do not use connection pooling.
– Chris Filippou
Aug 22 '17 at 14:33
Limit the number of connections to the client. What are you using for your web server? Apache? Nginx? Tomcat? etc. Find the setting it has; decrease it. But wait! Why would a bot be touching the database??
– Rick James
Aug 22 '17 at 16:31
Apache Amazon. Doesn't the bot have to connect to the database to pull the content? Every page is loaded from the database using a single connection.
– Chris Filippou
Aug 23 '17 at 18:36
add a comment |
To STOP all the bots, check out this link. http://www.inmotionhosting.com/support/website/restricting-bots/how-to-stop-search-engines-from-crawling-your-website
Recap, robots.txt needs simply two lines.
User-agent: *
Disallow: /
in the TXT file at the root of your site.
answered Sep 6 '17 at 13:21
Wilson HauckWilson Hauck
75349
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdba.stackexchange.com%2fquestions%2f183636%2fwhy-is-my-threads-connected-always-1%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
There are two different issues -- threads connected and threads running.
In a typical, well oiled, production server, you will see this in SHOW PROCESSLIST:
- Lots of "Sleep" connections,
- Fewer than
max_connectionslines in the processlist, - One, or a small number, of non-Sleep connections.
- Most queries are so fast that they go back to Sleep before you notice it. I often see "thousands of Queries per second", yet "nothing running". This is good.
"Sleep" means that the connection is not running a query at the moment. Instead it might be doing application processing, or it might be part of a "connection pool".
The reason you always see "1" is that you have to run a query (SHOW PROCESSLIST or something else) to get the number. So the "1" is itself.
You said Threads_connected is always 1. Did you mean Threads_running? Note also the capitalization.
What is the value of thread_cache_size? 8 is typical, but the exact value does not matter a lot.
What about Max_used_connections and max_connections?
Is there any sort of "connection pooling"? Are there multiple clients?
Consider providing me with the info mentioned in tuning here .
answered Aug 20 '17 at 19:45
Rick JamesRick James
41.7k22258
I notice when the website goes down, (mysql stops responding or connection times out), that there is a lot of bot traffic. Applebot, twitterbot, bingbot, googlebot, pinterestbot are common browsers. Now it shows that they request the robots.txt file and get the sitemap - I thought that should alleviate them from browsing the entire site randomly. What can I do to keep these bots from overloading the connections to mysql? I have spent months trying to solve this. Slowing crawl rate, a dynamic sitemap, editing the settings on max connections etc,. I do not use connection pooling.
– Chris Filippou
Aug 22 '17 at 14:33
Limit the number of connections to the client. What are you using for your web server? Apache? Nginx? Tomcat? etc. Find the setting it has; decrease it. But wait! Why would a bot be touching the database??
– Rick James
Aug 22 '17 at 16:31
Apache Amazon. Doesn't the bot have to connect to the database to pull the content? Every page is loaded from the database using a single connection.
– Chris Filippou
Aug 23 '17 at 18:36
add a comment |
There are two different issues -- threads connected and threads running.
In a typical, well oiled, production server, you will see this in SHOW PROCESSLIST:
- Lots of "Sleep" connections,
- Fewer than
max_connectionslines in the processlist, - One, or a small number, of non-Sleep connections.
- Most queries are so fast that they go back to Sleep before you notice it. I often see "thousands of Queries per second", yet "nothing running". This is good.
"Sleep" means that the connection is not running a query at the moment. Instead it might be doing application processing, or it might be part of a "connection pool".
The reason you always see "1" is that you have to run a query (SHOW PROCESSLIST or something else) to get the number. So the "1" is itself.
You said Threads_connected is always 1. Did you mean Threads_running? Note also the capitalization.
What is the value of thread_cache_size? 8 is typical, but the exact value does not matter a lot.
What about Max_used_connections and max_connections?
Is there any sort of "connection pooling"? Are there multiple clients?
Consider providing me with the info mentioned in tuning here .
answered Aug 20 '17 at 19:45
Rick JamesRick James
41.7k22258
I notice when the website goes down, (mysql stops responding or connection times out), that there is a lot of bot traffic. Applebot, twitterbot, bingbot, googlebot, pinterestbot are common browsers. Now it shows that they request the robots.txt file and get the sitemap - I thought that should alleviate them from browsing the entire site randomly. What can I do to keep these bots from overloading the connections to mysql? I have spent months trying to solve this. Slowing crawl rate, a dynamic sitemap, editing the settings on max connections etc,. I do not use connection pooling.
– Chris Filippou
Aug 22 '17 at 14:33
Limit the number of connections to the client. What are you using for your web server? Apache? Nginx? Tomcat? etc. Find the setting it has; decrease it. But wait! Why would a bot be touching the database??
– Rick James
Aug 22 '17 at 16:31
Apache Amazon. Doesn't the bot have to connect to the database to pull the content? Every page is loaded from the database using a single connection.
– Chris Filippou
Aug 23 '17 at 18:36
add a comment |
There are two different issues -- threads connected and threads running.
In a typical, well oiled, production server, you will see this in SHOW PROCESSLIST:
- Lots of "Sleep" connections,
- Fewer than
max_connectionslines in the processlist, - One, or a small number, of non-Sleep connections.
- Most queries are so fast that they go back to Sleep before you notice it. I often see "thousands of Queries per second", yet "nothing running". This is good.
"Sleep" means that the connection is not running a query at the moment. Instead it might be doing application processing, or it might be part of a "connection pool".
The reason you always see "1" is that you have to run a query (SHOW PROCESSLIST or something else) to get the number. So the "1" is itself.
You said Threads_connected is always 1. Did you mean Threads_running? Note also the capitalization.
What is the value of thread_cache_size? 8 is typical, but the exact value does not matter a lot.
What about Max_used_connections and max_connections?
Is there any sort of "connection pooling"? Are there multiple clients?
Consider providing me with the info mentioned in tuning here .
answered Aug 20 '17 at 19:45
Rick JamesRick James
41.7k22258
There are two different issues -- threads connected and threads running.
In a typical, well oiled, production server, you will see this in SHOW PROCESSLIST:
- Lots of "Sleep" connections,
- Fewer than
max_connectionslines in the processlist, - One, or a small number, of non-Sleep connections.
- Most queries are so fast that they go back to Sleep before you notice it. I often see "thousands of Queries per second", yet "nothing running". This is good.
"Sleep" means that the connection is not running a query at the moment. Instead it might be doing application processing, or it might be part of a "connection pool".
The reason you always see "1" is that you have to run a query (SHOW PROCESSLIST or something else) to get the number. So the "1" is itself.
You said Threads_connected is always 1. Did you mean Threads_running? Note also the capitalization.
What is the value of thread_cache_size? 8 is typical, but the exact value does not matter a lot.
What about Max_used_connections and max_connections?
Is there any sort of "connection pooling"? Are there multiple clients?
Consider providing me with the info mentioned in tuning here .
answered Aug 20 '17 at 19:45
Rick JamesRick James
41.7k22258
answered Aug 20 '17 at 19:45
Rick JamesRick James
41.7k22258
answered Aug 20 '17 at 19:45
Rick JamesRick James
41.7k22258
answered Aug 20 '17 at 19:45
Rick JamesRick James
41.7k22258
41.7k22258
I notice when the website goes down, (mysql stops responding or connection times out), that there is a lot of bot traffic. Applebot, twitterbot, bingbot, googlebot, pinterestbot are common browsers. Now it shows that they request the robots.txt file and get the sitemap - I thought that should alleviate them from browsing the entire site randomly. What can I do to keep these bots from overloading the connections to mysql? I have spent months trying to solve this. Slowing crawl rate, a dynamic sitemap, editing the settings on max connections etc,. I do not use connection pooling.
– Chris Filippou
Aug 22 '17 at 14:33
Limit the number of connections to the client. What are you using for your web server? Apache? Nginx? Tomcat? etc. Find the setting it has; decrease it. But wait! Why would a bot be touching the database??
– Rick James
Aug 22 '17 at 16:31
Apache Amazon. Doesn't the bot have to connect to the database to pull the content? Every page is loaded from the database using a single connection.
– Chris Filippou
Aug 23 '17 at 18:36
add a comment |
I notice when the website goes down, (mysql stops responding or connection times out), that there is a lot of bot traffic. Applebot, twitterbot, bingbot, googlebot, pinterestbot are common browsers. Now it shows that they request the robots.txt file and get the sitemap - I thought that should alleviate them from browsing the entire site randomly. What can I do to keep these bots from overloading the connections to mysql? I have spent months trying to solve this. Slowing crawl rate, a dynamic sitemap, editing the settings on max connections etc,. I do not use connection pooling.
– Chris Filippou
Aug 22 '17 at 14:33
Limit the number of connections to the client. What are you using for your web server? Apache? Nginx? Tomcat? etc. Find the setting it has; decrease it. But wait! Why would a bot be touching the database??
– Rick James
Aug 22 '17 at 16:31
Apache Amazon. Doesn't the bot have to connect to the database to pull the content? Every page is loaded from the database using a single connection.
– Chris Filippou
Aug 23 '17 at 18:36
I notice when the website goes down, (mysql stops responding or connection times out), that there is a lot of bot traffic. Applebot, twitterbot, bingbot, googlebot, pinterestbot are common browsers. Now it shows that they request the robots.txt file and get the sitemap - I thought that should alleviate them from browsing the entire site randomly. What can I do to keep these bots from overloading the connections to mysql? I have spent months trying to solve this. Slowing crawl rate, a dynamic sitemap, editing the settings on max connections etc,. I do not use connection pooling.
– Chris Filippou
Aug 22 '17 at 14:33
I notice when the website goes down, (mysql stops responding or connection times out), that there is a lot of bot traffic. Applebot, twitterbot, bingbot, googlebot, pinterestbot are common browsers. Now it shows that they request the robots.txt file and get the sitemap - I thought that should alleviate them from browsing the entire site randomly. What can I do to keep these bots from overloading the connections to mysql? I have spent months trying to solve this. Slowing crawl rate, a dynamic sitemap, editing the settings on max connections etc,. I do not use connection pooling.
– Chris Filippou
Aug 22 '17 at 14:33
Limit the number of connections to the client. What are you using for your web server? Apache? Nginx? Tomcat? etc. Find the setting it has; decrease it. But wait! Why would a bot be touching the database??
– Rick James
Aug 22 '17 at 16:31
Limit the number of connections to the client. What are you using for your web server? Apache? Nginx? Tomcat? etc. Find the setting it has; decrease it. But wait! Why would a bot be touching the database??
– Rick James
Aug 22 '17 at 16:31
Apache Amazon. Doesn't the bot have to connect to the database to pull the content? Every page is loaded from the database using a single connection.
– Chris Filippou
Aug 23 '17 at 18:36
Apache Amazon. Doesn't the bot have to connect to the database to pull the content? Every page is loaded from the database using a single connection.
– Chris Filippou
Aug 23 '17 at 18:36
add a comment |
To STOP all the bots, check out this link. http://www.inmotionhosting.com/support/website/restricting-bots/how-to-stop-search-engines-from-crawling-your-website
Recap, robots.txt needs simply two lines.
User-agent: *
Disallow: /
in the TXT file at the root of your site.
answered Sep 6 '17 at 13:21
Wilson HauckWilson Hauck
75349
add a comment |
To STOP all the bots, check out this link. http://www.inmotionhosting.com/support/website/restricting-bots/how-to-stop-search-engines-from-crawling-your-website
Recap, robots.txt needs simply two lines.
User-agent: *
Disallow: /
in the TXT file at the root of your site.
answered Sep 6 '17 at 13:21
Wilson HauckWilson Hauck
75349
add a comment |
To STOP all the bots, check out this link. http://www.inmotionhosting.com/support/website/restricting-bots/how-to-stop-search-engines-from-crawling-your-website
Recap, robots.txt needs simply two lines.
User-agent: *
Disallow: /
in the TXT file at the root of your site.
answered Sep 6 '17 at 13:21
Wilson HauckWilson Hauck
75349
To STOP all the bots, check out this link. http://www.inmotionhosting.com/support/website/restricting-bots/how-to-stop-search-engines-from-crawling-your-website
Recap, robots.txt needs simply two lines.
User-agent: *
Disallow: /
in the TXT file at the root of your site.
answered Sep 6 '17 at 13:21
Wilson HauckWilson Hauck
75349
edited Sep 6 '17 at 13:26
answered Sep 6 '17 at 13:21
Wilson HauckWilson Hauck
75349
answered Sep 6 '17 at 13:21
Wilson HauckWilson Hauck
75349
answered Sep 6 '17 at 13:21
Wilson HauckWilson Hauck
75349
75349
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f183636%2fwhy-is-my-threads-connected-always-1%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
