Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
"I tried everything": it is often useful in these circumstances to list in your question what you currently consider "everything" to be, both so people don't spend time suggesting things that you have already tried and so other people finding this question later have extra information for a better understanding. –  David Spillett Jan 21 at 10:49
    
I edited the question. Now, What are the steps to track the issue. –  user23750 Jan 21 at 10:52
    
Maybe increase max_connections. –  Mihai Jan 21 at 11:34
    
Informations on your OS would be helpful too –  Jeredepp Jan 21 at 11:38
    
OS - 3.4.57-48.42.amzn1.x86_64, RAM - 15 GB, CPU - 4 –  user23750 Jan 21 at 11:51

1 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.

share|improve this answer
    
Suddenly I get this error and whenever I check ESTABLISHED connections it remains always less than 50. I think I am not running out of connection. open files limit already set to 40k. –  user23750 Jan 21 at 12:01
    
how are you trying to acces your DB? (terminal, software on remote ....) –  Jeredepp Jan 21 at 12:39
    
we are using by software –  user23750 Jan 23 at 6:21
    
When you're using software, this counts as normal connection (correct me if i'm wrong), and you won't be able to connect if max-connections is reached. Check your webserver logs for hints on your error, for example 'too many connections' –  Jeredepp Jan 24 at 7:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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