mysql socket issue on heavy load

[ajji]

Hi,

I got a setup of mysql/apache/php running on my fedora server. The server is not the most powerfull 512Mo of memory 2.66 Ghz cpu. It is running under Fedora core 3.
This should still be enough for the hosting of my home website and forum...

the issue I have is that when doing a stress testing using apache benchmark (or openSTA) (for 50 - 100 concurrent users) there some mysql connections issues...

The error message are the following :
PHP Warning: mysql_connect(): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (11) in /var/www/html/lib/init_variables.php on line 30

Do you have any idea of the reason of this problem? to reproduce the issue it is easy i just have to try running the apache benchmark (sometime more concurrent users are needed...) :
ab -c 50 -n 5000 http://192.168.0.1/testPage.php"

this is really annoying for the visitors of my website and it seems that this is also happening on some other website : doing a google search with mysql.sock' (11) show many results ... but no answers !

What I notice as well is that this conenction issue appear when there are mysql zombie process (mysql <defunct> when viewing ps -aux command)

For information I was having this issue on the 3.23.x version of mysql from the FC3 rpm and also on the 4.24 official mysql rpm.

thank you in advance for your help.

[AndyGreen]

Interesting observations. Anything in

/var/log/mysqld.log
/var/log/messages

?

[ajji]

Thanks for the quick reply ,

the 2 log files you mentionned :
/var/log/mysqld.log
/var/log/messages

don't have any new entry...
the error message just appear in the error_log of apache... and in the browser if you access the site at the bad time.

I was thinking this might be due to the number of file descriptors therefor I increased it in my.cnf and also in fedora. But this didn't change anything...

As mentionned in my first post this seems to appear quite often on some others website, by doing a google search I can easily find that i am not the only one having this issue but no answer is given anywhere...

What I notice as well is that there are mysql zombie process when the issue occurs, so this is probably the reason of the error message, but what I want to know is : how to avoid having many mysql process being <defunct> when there is an heavy load on the site...

Thank s for your help if you need some more information or config files content from my box don t hesitate to ask me. actually I got two box one with mysql 4.0.24 and one wit the latest fedora rpm 3.23.x and both fail the same way I believe any version from, 3.23 to 4.0.24 should have the same issue...

If anyone could also try to reproduce the issue by using three or for time in a row the same kind of apache benchmark command line that I use this could be helpfull !

[AndyGreen]

Is it possible that your php is not closing handles to the MySQL results in a timely way? If it has to wait for a deferred garbage collection maybe that can cause an exhaustion of handles?

[ajji]

this don't knoz do meen a php configuration or the use of mysql.close ?

I tried to do my test on very simple page which contains only one querry (not too complex) and just after I do a mysql.close ...

So normally the connection to mysql should be close after the page is executed, And I am not using the perssistant conenction of php.

here are the setting of mysql in my php.ini file :
[MySQL]
; Allow or prevent persistent links.
mysql.allow_persistent = Off

; Maximum number of persistent links. -1 means no limit.
mysql.max_persistent = -1

; Maximum number of links (persistent + non-persistent). -1 means no limi
t.
mysql.max_links = -1

; Default port number for mysql_connect(). If unset, mysql_connect() will
use
; the $MYSQL_TCP_PORT or the mysql-tcp entry in /etc/services or the
; compile-time value defined MYSQL_PORT (in that order). Win32 will only
look
; at MYSQL_PORT.
mysql.default_port =

; Default socket name for local MySQL connects. If empty, uses the built-
in
; MySQL defaults.
mysql.default_socket =

; Default host for mysql_connect() (doesn't apply in safe mode).
mysql.default_host =

; Default user for mysql_connect() (doesn't apply in safe mode).
mysql.default_user =

; Default password for mysql_connect() (doesn't apply in safe mode).
; Note that this is generally a *bad* idea to store passwords in this file
.
; *Any* user with PHP access can run 'echo get_cfg_var("mysql.default_pass
word")
; and reveal this password! And of course, any users with read access to
this
; file will be able to reveal the password as well.
mysql.default_password =

; Maximum time (in seconds) for connect timeout. -1 means no limit
mysql.connect_timeout = 20

; Trace mode. When trace_mode is active (=On), warnings for table/index sc
ans and
; SQL-Errors will be displayed.
mysql.trace_mode = Off



Is this what you wanted to know ?

[AndyGreen]

I'm thinking more about

mysql_free_result()

...do you do this explicitly? Just a thought.

[ajji]

Yes I do it as well ...

So it might come form something else ... Just for information how many mysql process should I expect when doing show processlist; if I run a stress testing for 50 user... curently I was able to see up to 150 process almost all of them were sleeping but this seems to be a lot ...

here is the small test page I use :

<html>
<body>

TEST >> <br>

<?

$myhote="localhost";
$myuser="";
$mypass="";
$mydb="";

$base_id = mysql_connect($myhote,$myuser,$mypass ) or die(error_log("mysqlError -> " . mysql_errno() . " - " . mysql_error()));
mysql_select_db($mydb) or die(error_log("mysqlError -> " . mysql_errno() . " - " . mysql_error()));
if ($base_id){
$cpt = 0;
$query = "SELECT objet_nom, objet_param1, objet_param2, objet_id,objet_description FROM objet where objet_nom like '%' or objet_nom like '%' or objet_nom like '%' ORDER BY objet_nom;";
$result = mysql_query($query)
or die (mysql_error(). "no locales found");
while($row = mysql_fetch_array($result))
{
//echo 'TEST Result : objet_nom = '. $row['objet_nom'] . 'objet_param1 = '. $row['objet_param1'] . 'objet_param2 = '. $row['objet_param2'] . 'objet_id = ' . $row['objet_id'] . 'objet_description = ' . $row['objet_description'] . "<br>";
$cpt++;
}
echo "num of results = " . $cpt;
mysql_free_result($result);
mysql_close($base_id);
}
?>

</body>
</html>

[ajji]

Hi, no news from anyone on this issue ?

I asked on the french forum of fedora and one member running FC3 on a powerfull box was able to have the same error message than me by doing a stress testing, so the issue concerns all the setups ...

So far no clear explanation o nthat but by using perssistant conenction there is no issue, but I can't use them it requires too much memories and consume too much connection, and by using innodb it is nort really recommended because of the table lock.

Does anw of you have anw idea on what is the reason of this problem? I am almost sure now tht this is due to a box or OS limitation when trying to access too many time simultaneously the socket file.

Thanks for your help !

[ajji]

Hello ,

Some news about my issue, it seems that using the TCP socket instead of the Unix socket solve the issue, I did that by using in my test page . mysql_connect("127.0.0.1", ....
instead of mysql_connect("localhost", ....

Still this doesn't explain what the problem is when using the Unix socket and I belive there is some performance wins when using Unix socket instead of TCP, so I would like to solve the issue.

Any ideas ?

[AndyGreen]

Since Xwindows goes through the loopback device for all graphical operations, I suspect the benefit of Unix Sockets over loopback device is very small if anything at all. But it was interesting to find such a bug.

[ajji]

thank s for the quick reply!

I read on the mysql documentation that the Unix socket were more performant if it was possible to use them. I don't see the link with xwindows ?

Otherwise do you think this error I have with mysql.sock is a bug ? because it is happening to a lot of other website when they have an heavy load... it is not only on one version of mysql (it is the same on 3.23 and on 4.0.24 , and other distribution seems to have the same problem , like suse... )
this is the reason why I beleave that there is a config option somewhere in Fedora that could fix this issue ... What do you think ?

[AndyGreen]

Hum never mind about X, it seems it too uses Unix Sockets for local connect

unix 2 [ ACC ] STREAM LISTENING 13784 6444/X /tmp/.X11-unix/X0

I guess that does suggest there is some performance advantage.

/proc/sys/net/unix/max_dgram_qlen is set to 10 by default... maybe echo something bigger into that.

[ajji]

I tried to increase this /proc/sys/net/unix/max_dgram_qlen to some bigger value , 20 then 50 and the 200 but it didn't change anything.
So it might be comming from something else....

what does this /proc/sys/net/unix/max_dgram_qlen is for ?

If you have any other idea don't hesitate i am ready to test them.

[AndyGreen]

That seems to have exhausted the settings for unix sockets :-) I looked at ulimit but nothing really made sense. I wonder if the error is still present on older kernels.

[ajji]

Uhm , for the ulimit I already tried and with everything set to unlimited it still occurs...

what is really strange is that this is not related directly to the memory the cpu usage or the number of processes running....

I did also some monitoring using vmstat while stress testing the application I noticed that there were some change whn the issue was appering it might be of some help for you :
(this was on a very heavy load to reproduc the issue very quicly still the output is the same... )

0 0 628 94728 44768 222800 0 0 0 0 1149 75 3 0 97 0
// START THE STRESS TESTING
3 0 628 92736 44776 222820 0 0 0 232 1125 3344 49 15 36 0
1 0 628 91960 44776 222844 0 0 0 0 1128 5746 77 23 0 0
1 0 628 89992 44776 222872 0 0 0 12 1103 5836 79 21 0 0
5 0 628 86504 44776 222896 0 0 0 0 1128 5817 79 21 0 0
3 0 628 83320 44776 222924 0 0 0 0 1092 6495 78 22 0 0
2 0 628 83824 44780 222948 0 0 0 180 1132 6829 80 20 0 0
3 0 628 83728 44780 222976 0 0 0 0 1095 6483 79 21 0 0
// First issues start to appears in the log
40 0 628 82240 44780 223032 0 0 0 180 1239 3305 81 19 0 0
53 0 628 80856 44780 223068 0 0 0 0 1173 4219 77 23 0 0
35 0 628 79664 44780 223132 0 0 0 0 1252 1851 75 25 0 0
35 0 628 78192 44784 223228 0 0 0 296 1253 1028 77 23 0 0
25 0 628 76680 44784 223304 0 0 0 64 1168 2277 71 29 0 0
62 0 628 72992 44784 223412 0 0 0 0 1479 1269 82 18 0 0
71 0 628 66088 44784 223528 0 0 0 0 1484 1245 81 19 0 0
45 0 628 59952 44788 223648 0 0 0 416 1524 1327 83 17 0 0
80 0 628 58872 44788 223748 0 0 0 0 1346 1119 81 19 0 0
82 0 628 59096 44788 223876 0 0 0 0 1690 1193 79 21 0 0
86 0 628 56728 44788 223980 0 0 0 0 1395 856 80 20 0 0
87 0 628 54616 44796 224092 0 0 0 560 1522 936 75 25 0 0
36 0 628 51192 44796 224188 0 0 0 0 1267 1146 81 19 0 0
78 0 628 51528 44796 224348 0 0 0 0 2067 1863 81 19 0 0
91 0 628 50208 44804 224496 0 0 0 564 1877 1745 83 17 0 0
33 0 628 49224 44804 224600 0 0 0 0 1299 1237 83 17 0 0
96 0 628 48248 44804 224736 0 0 0 0 1813 1593 82 18 0 0
96 0 628 48568 44808 224856 0 0 0 0 1551 976 75 25 0 0
100 0 628 46408 44816 224964 0 0 0 572 1522 953 75 25 0 0
51 0 628 44832 44816 225076 0 0 0 0 1390 1194 79 21 0 0
55 0 628 43616 44816 225184 0 0 0 0 1388 1270 82 18 0 0
104 0 628 42688 44816 225316 0 0 0 0 1748 1485 82 18 0 0


You can notice that system-Cs goes down very quicly and that the IO-bo is going up at the same time , this might be the eason of the problem or it might be due to the problem...

Thank s a lot for your effort I still hope we can fix this issue or at least understand it, what I really don't like is the fact that it failed without giving any details in the log and without any reasons...