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.

1

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_connections lines 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 .

| improve this answer | |
  • 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
0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.