mysql TIME_WAIT

mysql TIME_WAIT

am 16.02.2011 23:35:25 von Madan Thapa

--90e6ba61536eb19703049c6de517
Content-Type: text/plain; charset=ISO-8859-1

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?

--90e6ba61536eb19703049c6de517--

RE: mysql TIME_WAIT

am 17.02.2011 22:01:10 von Travis Ard

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@gmail.com]
Sent: Wednesday, February 16, 2011 3:35 PM
To: mysql@lists.mysql.com
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?


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org