List:General Discussion« Previous MessageNext Message »
From:Travis Ard Date:February 17 2011 9:01pm
Subject:RE: mysql TIME_WAIT
View as plain text  
After a TCP connection has been closed, the state changes to TIME_WAIT. If I
recall correctly, this is a way to prevent the same port from being
immediately reused, and allow any duplicate packets from the previous
connection that might still be floating around the network time to expire.
In general, the presence of TIME_WAIT isn't something to be too concerned
about. 

If you see a large number of these, it may be because your application is
opening a new database connection for each web server request.  You might be
able to save yourself a bit of overhead by refactoring your application to
use a database connection pool.

-Travis

-----Original Message-----
From: Madan Thapa [mailto:madan.feedback@stripped] 
Sent: Wednesday, February 16, 2011 3:35 PM
To: mysql@stripped
Subject: mysql TIME_WAIT

Hi,

When i do a netstat and grep port 3306 , i can see lots of TIME_WAIT.    Can
you please advise , what might be the issue for so many TIME_WAIT ?

Here are the logs and related files:

My.CNF on DB Server
##############################
##
[root@sql mysql]# cat /etc/my.cnf
[mysqld]
safe-show-database
skip-locking
skip-bdb


port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 16M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4

max_connections=5000

long_query_time = 20
log-slow-queries=/var/log/mysqld-slow.log

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
safe-updates

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[root@sql mysql]#








NETSTAT on Web Server
#################################
I have changed acutal IP to WEBSERVERIP ( for the webserver ) and DBSERVERIP
( for the db server ip )

[root@web temphotos]# netstat -an |  grep DBSERVERIP
tcp        0      0 WEBSERVERIP:57666         DBSERVERIP:3306
TIME_WAIT
tcp        0      0 WEBSERVERIP:54315         DBSERVERIP:3306
ESTABLISHED
tcp        0      0 WEBSERVERIP:53293         DBSERVERIP:3306
TIME_WAIT
tcp        0      0 WEBSERVERIP:53295         DBSERVERIP:3306
TIME_WAIT
tcp        0      0 WEBSERVERIP:53305         DBSERVERIP:3306
TIME_WAIT
tcp        0      0 WEBSERVERIP:53304         DBSERVERIP:3306
TIME_WAIT
tcp        0      0 WEBSERVERIP:53307         DBSERVERIP:3306
TIME_WAIT
tcp        0      0 WEBSERVERIP:53306         DBSERVERIP:3306
TIME_WAIT
tcp        0      0 WEBSERVERIP:53309         DBSERVERIP:3306
TIME_WAIT
tcp        0      0 WEBSERVERIP:53308         DBSERVERIP:3306
TIME_WAIT
tcp        0      0 WEBSERVERIP:53311         DBSERVERIP:3306
TIME_WAIT
tcp        0      0 WEBSERVERIP:53310         DBSERVERIP:3306
TIME_WAIT
tcp        0      0 WEBSERVERIP:53297         DBSERVERIP:3306
TIME_WAIT
tcp        0      0 WEBSERVERIP:53296         DBSERVERIP:3306
TIME_WAIT
tcp        0      0 WEBSERVERIP:53299         DBSERVERIP:3306
TIME_WAIT
tcp        0      0 WEBSERVERIP:53298         DBSERVERIP:3306
TIME_WAIT
tcp        0      0 WEBSERVERIP:53301         DBSERVERIP:3306
TIME_WAIT
tcp        0      0 WEBSERVERIP:53300         DBSERVERIP:3306
TIME_WAIT
tcp        0      0 WEBSERVERIP:53303         DBSERVERIP:3306
TIME_WAIT
tcp        0      0 WEBSERVERIP:53302         DBSERVERIP:3306
TIME_WAIT
tcp        0      0 WEBSERVERIP:53257         DBSERVERIP:3306
TIME_WAIT
tcp        0      0 WEBSERVERIP:53256         DBSERVERIP:3306
TIME_WAIT
tcp        0      0 WEBSERVERIP:53259         DBSERVERIP:3306
TIME_WAIT
tcp        0      0 WEBSERVERIP:53258         DBSERVERIP:3306
TIME_WAIT
tcp        0      0 WEBSERVERIP:53462         DBSERVERIP:3306
TIME_WAIT
tcp        0      0 WEBSERVERIP:56250         DBSERVERIP:3306
ESTABLISHED
tcp        0      0 WEBSERVERIP:50288         DBSERVERIP:3306
ESTABLISHED
tcp        0      0 WEBSERVERIP:52286         DBSERVERIP:3306
TIME_WAIT
tcp        0      0 WEBSERVERIP:53116         DBSERVERIP:3306
TIME_WAIT
tcp        0      0 WEBSERVERIP:48573         DBSERVERIP:3306
TIME_WAIT
tcp        0      0 WEBSERVERIP:48574         DBSERVERIP:3306
TIME_WAIT
tcp        0      0 WEBSERVERIP:48575         DBSERVERIP:3306
TIME_WAIT
tcp        0      0 WEBSERVERIP:48568         DBSERVERIP:3306
TIME_WAIT
tcp        0      0 WEBSERVERIP:48569         DBSERVERIP:3306
TIME_WAIT
tcp        0      0 WEBSERVERIP:48570         DBSERVERIP:3306
TIME_WAIT
tcp        0      0 WEBSERVERIP:48571         DBSERVERIP:3306
TIME_WAIT
tcp        0      0 WEBSERVERIP:48300         DBSERVERIP:3306
ESTABLISHED
tcp        0      0 WEBSERVERIP:47460         DBSERVERIP:3306
TIME_WAIT
tcp        0      0 WEBSERVERIP:42983         DBSERVERIP:3306
ESTABLISHED
tcp        0      0 WEBSERVERIP:44035         DBSERVERIP:3306
ESTABLISHED
tcp        0      0 WEBSERVERIP:44974         DBSERVERIP:3306
ESTABLISHED
tcp        0      0 WEBSERVERIP:43896         DBSERVERIP:3306
ESTABLISHED
tcp        0      0 WEBSERVERIP:37081         DBSERVERIP:3306
ESTABLISHED
tcp        0      0 WEBSERVERIP:40781         DBSERVERIP:3306
ESTABLISHED
tcp        0      0 WEBSERVERIP:36109         DBSERVERIP:3306
ESTABLISHED
tcp        0      0 WEBSERVERIP:35119         DBSERVERIP:3306
ESTABLISHED
[root@web temphotos]#


Lots of TIME_WAIT , i have truncated the logs.








Both servers have 1000M interface and db server has 3G ram 2cpu  @ 2.27GHz
(Intel(R) Xeon(R) CPU).  Sql server is not loaded at all , infact load never
goes beyond 1.  Can you advise how can we get rid of TIME_WAIT or is this
normal?

Thread
mysql TIME_WAITMadan Thapa16 Feb
  • RE: mysql TIME_WAITTravis Ard17 Feb