MySQL Bugs: #58043: high loads possibly caused by improper connection handling / time

Bug #58043	high loads possibly caused by improper connection handling / time_wait
Submitted:	8 Nov 2010 10:44	Modified:	2 Sep 2011 22:16
Reporter:	Oliver Welter	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.1.51	OS:	Linux (Gentoo / x86_64)
Assigned to:		CPU Architecture:	Any
Tags:	High load, loopback, time_wait

Description:
I have a box running a mid-volume apache/php and a mysql server, the webserver runs in a jail and therefore accesses the mysql server via a tcp socket using the ip which is attached to the local network.

The performance is reasonable in general but from time to time mysql fails, in a way that it does not handle any more requests.

What I can see is:
* 100% CPU load of mysqld
* stalled connections to the database server from the webserver
* no response on new connection requests
* a lot of connections to mysql in TIME_WAIT state

According to fre/top the system is not swapping and the system keeps responsing as it has a second cpu which is not used by mysql.

If I:
* move the Apache server to another box and use a "real" network connection. 
* _or_ set the tcp_tw_recycle (TIME_WAIT recycle) to true

The problem disappears.

How to repeat:
Put a Webserver and Database-Server on a box, connect using a TCP Socket and produce a bit of load.
See a lot of time_wait conenctions appearing and after some time the mysql process goes into 100% load.

Thank you for the report.

This looks either as duplicate of bug #56979 for me or even not a bug if both Apache and MySQL use a lot of connections.

Hi,

I also saw Bug #56979 and used a recent version to check if it is fixed but it seems to be not the issue.

As far as I can see, the apache does not keep the connection but fails to get new ones.

Oliver

Thank you for the feedback.

Do you use only Connector/PHP? Which exact version? Do you use Connector/J also?

I do not use Java, only php. My version is PHP 5.2.14-pl0-gentoo with Suhosin-Patch 0.9.7 (cli) (built: Nov  5 2010 22:53:15)

This also could  be a hardware problem.

Are you sure that your hardware is ok?

A memcheck did not show up any errors, the system health monitoring is good and the rollback version is running flawlessly on the box, so I dont think the hardware i broken.

Thank you for the feedback.

Please send us output of

netstat -nat| grep MYSQL_PORT

in time when problem occurs.

It looks like this, about 200 lines of it:

tcp        0      0 192.168.200.130:3306    192.168.200.133:59800   TIME_WAIT  
tcp        0      0 192.168.200.130:3306    192.168.200.133:59807   TIME_WAIT  
tcp        0      0 192.168.200.130:3306    192.168.200.133:59789   TIME_WAIT  
tcp        0      0 192.168.200.130:3306    192.168.200.133:59747   TIME_WAIT  
tcp        0      0 192.168.200.130:3306    192.168.200.133:59781   TIME_WAIT  
tcp        0      0 192.168.200.130:3306    192.168.200.133:59756   TIME_WAIT  
tcp        0      0 192.168.200.130:3306    192.168.200.133:59732   TIME_WAIT  
tcp        0      0 192.168.200.130:3306    192.168.200.133:59821   TIME_WAIT  
tcp        0      0 192.168.200.130:3306    192.168.200.133:59801   TIME_WAIT  
tcp        0      0 192.168.200.130:3306    192.168.200.133:59780   TIME_WAIT  
tcp        0      0 192.168.200.130:3306    192.168.200.133:59818   TIME_WAIT

Thank you for the feedback.

TIME_WAIT is on server side which is bad. How do you use PHP? Which extension do you use (mysql, mysqli, PDO, odbc)? Do you explicitly close connections to MySQL?

I do not know what kind of connector is used. 
The application is typo3 cms and as far as I know this uses mysqli.

Please reply last question from Sveta: Do you explicitly close connections to MySQL?. Thanks.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".