2

I run a couple of websites but I believe one of them is causing too many Connections Failed Attempts that causes max_connections variable to hit.

Currently, max_connections=2500 (already large) but total connections per hour due to failed attempts have become greater than 3000. How do I debug this? The httpd error log for the website does show a couple of PHP warnings/notices though. For example, using mysqli instead in the database config.

2
  • Maybe this? May 24, 2016 at 1:59
  • You need to check your code that opens DB connections. Number of failed connections is just a counter, I wouldn't worry too much about it but its high number suggests something is not right in the code.
    – Allen King
    Jun 10, 2017 at 1:56

2 Answers 2

2

I don't think the failed connections are causing the max connections issue, but can you set log_warnings=2 and after a short time, view the error log to see if there is more information there? The error log would record something like "access denied for user 'user'@'host'..."

The command to set log_warnings=2 is:

set global log_warnings=2;

You can find the error log like this:

show global variables like 'log_error';

What version of mysql are you running?

Can you also show the output of:

show global variables like 'skip_name_resolve';

and:

show global status like 'max_used_connections';
1

Decrease the maximum number of connections to your web server. 20 is probably a realistic number. For Apache, this is MaxClients.

Do not, without a good reason, have more than one connection to MySQL from each PHP script.

Your screen shot shows only 111 as "max concurrent conections". (Presumably this comes from SHOW GLOBAL STATUS LIKE 'Max_used_connections';) So, max_connections is much too high. I rarely see a need for more than 1000. In your case, there is evidence that 111 is high enough.

Since it is dangerous (for RAM usage) to set max_connections too high, I suggest you decrease it to 200.

"Failed attempts" sounds a bit like the STATUS Aborted_connects. For that, 453/hour is moderately high, but I don't see it as a red flag. It could be that some hacker has tried to get in. Is port 3306 exposed outside your machine/LAN?

If you would like further analysis, please provide SHOW GLOBAL STATUS; and SHOW VARIABLES;, plus how much RAM. (Probably need a postit-like site.)

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.