Unable to connect to Mysql at peak time

Question

I have two servers one is application server and another is database server. Continuously I am firing query to mysql. At peak time I get error -

Can\'t connect to MySQL server on \'IP Address\' (99)

I couldn't track the issue.

Below is my my.cnf file-

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
default-time-zone = '+05:30'
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,


innodb_file_per_table=1
skip-name-resolve
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_buffer_pool_size = 4096M

log-error=/var/log/mysql/mysql_error.log
long_query_time=2
slow_query_log=1
log-warnings=2
slow-query-log-file=/var/log/mysql/log-slow-queries.log
max_connections = 500
wait_timeout=10
interactive_timeout=10
general_log_file=/var/log/mysql/general.log
max_allowed_packet=256M
#log_queries_not_using_indexes = 1

#starting
#old_passwords=1

binlog-format=MIXED

server-id=1
#ending
key_buffer_size = 2048M

thread_cache_size = 128M

table_cache = 75000
table_definition_cache = 40000
open_files_limit= 75000


sort_buffer_size = 3M 
read_buffer_size = 5M
read_rnd_buffer_size = 3M
myisam_sort_buffer_size = 20M
join_buffer_size=5M


query_cache_limit = 100M
query_cache_size = 2048M
query_cache_type=1

tmp_table_size = 256M
max_heap_table_size = 256M

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
#log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#log-warnings=2

At a particular time around 7000 tcp connections in TIME_WAIT and 1 or 2 in ESTABLISHED from application server to database server.

No server load, No Memory Issue, No firewall on both the server.

Please help me to solve this issue.

Answer 1

In your configuration you got max-connecions = 500 this means you acutally have 500 connections available to your server, and mysql adds an extraslot for and adminuser who's able to access over a terminal.

So you actually should always be able to connect to your Database even when all connections are used up.

Try to evaluate who/what is using up your administration connection, and get rid of it. Reasons i encountered are poorly written cronjobs which connect via shellscript to your DB (for example a self written backupscript) this takes a long time to run and locks you out of your Database.

Also check your open files limit on your OS in most distribution these values need to be raised when you have some heavy traffic on your server.