Posts Tagged ‘connection timeout’

Fix MySQL connection error – Host ” is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’

Wednesday, July 2nd, 2014

fix-mysql-too-many-connection-errors-explained

If you get a MySQL error like:

Host '' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

This most likely means your PHP / Java whatever programming language application connecting to MySQL is failing to authenticate with the application created (existing) or that the application is trying too many connections to MySQL in a rate where MySQL server can't serve all the requests.

Some common errors for Too many Connection errors are:
 

  • Networking Problem
  • Server itself could be down
  • Authentication Problems
  • Maximum Connection Errors allowed.

The value of the max_connection_errors system variable determines how many successive interrupted connection requests are permitted to myqsl server.
 

Well anyways if you get the:

Host '' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

You can consider this a sure sign application connections to MySQLis logging a lot of error connections, for some reason.
This error could also appear on very busy websites where high amount of separete connections are used – I've seen the error occur on PHP websites whether mysql_pconnect(); is selected in favour of the prooved working mysql_connect();

The first thing to do before changing / increasing default set of max connection errors is to check how many max connection errors are set within MySQL?

For that connect with MySQL CLI and issue:
 

mysql> SHOW VARIABLES LIKE '%error%';


+——————–+————————————————————-+
| Variable_name      | Value                                                           |
+——————–+————————————————————-+
| error_count        | 0                                                                     |
| log_error          | /var/log/mysql//mysqld.log                                |
| max_connect_errors | 10000                                                      |
| max_error_count    | 64                                                               |
| slave_skip_errors  | OFF                                                             |
+——————–+————————————————————-+


A very useful mysql cli command in debugging max connection errors reached problem is

mysql> SHOW PROCESSLIST;

 

To solve the error, try to tune in /etc/my.cnf, /etc/mysql/my.cnf or wherever my.cnf is located:

[mysqld]
max_connect_errors variable

and

wait_timeout var. Some reasonable variable size would be:

max_connect_errors = 100000
wait_timeout = 60

If such (anyways) high values is still not high enough you can raise mysql config connection timeout

 

to

max_connect_errors = 100000000

Also if you want to try raise max_connect_errors var without making it permanenty (i.e. remember var setting after MySQL service restart), set it from MySQL cli with:

SET GLOBAL max_connect_errors


If you want to keep the set default max_connection_errors and fix it temporary, you can try to follow the error

Host '' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

suggestion and issue in root console:

mysqladmin flush-hosts

Same could also be done from MySQL Cli with cmd:
 

FLUSH HOSTS;

Tags: , , , , , , , , , , , , , , ,
Posted in Everyday Life, MySQL, System Administration, Various | 3 Comments »

Few nginx.conf configuration options for Nginx to improve webserver performance

Tuesday, April 12th, 2011

Nginx server main logo with russian star
From my previous two articles How to install nginx webserver from source on Debian Linux / Install Latest Nginx on Debian and How to enable output compression (gzipfile content compression) in nginx webserver , I have explained how the Nginx server can be installed and configured easily.

As I’m continuing my nginx adventures this days, by trying to take the best out of the installed nginx server, I’ve found few configuration options, which does improve nginx’s server performance and thought it might be nice to share it here in hope that some other nginx novice might benefit out if them.
To setup and start using the options you will have of course to place the conf directives in /usr/local/nginx/conf/nginx.conf or wherever your nginx.conf is located.

The configuration options should be placed in nginx’s conf section which starts up with:

http {

Here are the configuration options useful in hastening my nginx’s performance:

1. General options nginx settings

## General Options
ignore_invalid_headers on;
keepalive_requests 2000;
recursive_error_pages on;
server_name_in_redirect off;
server_tokens off;

2. Connection timeout nginx settings

## Timeouts
client_body_timeout 60;
client_header_timeout 60;
keepalive_timeout 60 60;
send_timeout 60;
expires 24h;

3. server options for better nginx tcp/ip performance

## TCP options
tcp_nodelay on;
tcp_nopush on;

4. Increase the number of nginx worker processes

Somewhere near the beginning of nginx.conf file you should have the directive option:

worker_processes 1;

Make sure you change this option to:

worker_processes 4;

This will increase the number of spawned nginx worker processes in a way that more spawned threaded servers will await for client connections:

Being done with all the above settings, as a next step you have to restart the nginx server, in my case via the init script:

debian:~# /etc/init.d/nginx restart
Restarting nginx: nginx.

Now to check everything is fine with nginx and more specific that the worker_processes 4 options has taken place issue the command:

debian:~# ps axu |grep -i nginx|grep -v grep
root 20456 0.0 0.0 25280 816 ? Ss 10:35 0:00 nginx: master process /usr/local/nginx/sbin/nginx
nobody 20457 0.0 0.0 25844 1820 ? S 10:35 0:00 nginx: worker process
nobody 20458 0.0 0.0 25624 1376 ? S 10:35 0:00 nginx: worker process
nobody 20459 0.0 0.0 25624 1376 ? S 10:35 0:00 nginx: worker process
nobody 20460 0.0 0.0 25624 1368 ? S 10:35 0:00 nginx: worker process

Above you notice the 4 nginx processes running with user nobody, they’re the same configured worker_processes I just pointed out above.

Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,
Posted in Linux, System Administration | 2 Comments »