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

[8 Nov 2010 10:44] Oliver Welter
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.
[10 Nov 2010 20:39] Sveta Smirnova
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.
[10 Nov 2010 20:57] Oliver Welter
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
[10 Nov 2010 21:28] Sveta Smirnova
Thank you for the feedback.

Do you use only Connector/PHP? Which exact version? Do you use Connector/J also?
[11 Nov 2010 7:32] Oliver Welter
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)
[24 Nov 2010 13:02] Susanne Ebrecht
This also could  be a hardware problem.

Are you sure that your hardware is ok?
[24 Nov 2010 14:02] Oliver Welter
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.
[1 Dec 2010 22:58] Sveta Smirnova
Thank you for the feedback.

Please send us output of

netstat -nat| grep MYSQL_PORT

in time when problem occurs.
[2 Dec 2010 7:25] Oliver Welter
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
[2 Dec 2010 11:53] Sveta Smirnova
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?
[21 Dec 2010 17:30] Oliver Welter
I do not know what kind of connector is used. 
The application is typo3 cms and as far as I know this uses mysqli.
[2 Aug 2011 22:16] MySQL Verification Team
Please reply last question from Sveta: Do you explicitly close connections to MySQL?. Thanks.
[2 Sep 2011 23:00] Bugs System
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".