Apache/PHP processes hanging while interacting with MySQL

Question

Hi all,

We're seeing some strange behaviour and we're not sure if it's a problem with apache, php, mysql or the OS, so over to the big brains of stackoverflow!

We have Apache and mod_php talking to a mysql5 server. Sometimes, a process will choose to hang, trying to read from a file descriptor.

Firing up strace one on of them (all hanging processes showed the same results) gave this :

[root@prweb133v ~]# strace -p 8450
Process 8450 attached - interrupt to quit
read(57,  <unfinished ...>

So what was it trying to read?

[root@prweb133v ~]# lsof -p 8450
...
...
httpd   8450 apache   57u  IPv4    5546599             TCP
prweb133v.local:36615->hadat.local:mysql (ESTABLISHED)

That's our mysql server! Ok, so maybe it was trying to read the results of a query, I thought. Checking the processlist on the mysql server, the connection was established but in a state of SLEEP.

Hmmmm.

So then I checked netstat to see who was trying to send/receive what.

On the webserver :

[root@prweb133v ~]# netstat -t -n -a | grep 36615
tcp        0      5 172.23.179.6:36615          172.23.179.67:3306         
ESTABLISHED 

and on the mysql server there was an established connection but 0 in the send or receive queues.

Any idea what these mysterious 5 bytes could be, or why they randomly don't get the the mysql server?

Cheers!

Mike

Answer 1

What mysql-engine are you using (myisam, innodb, ...)? Do you use mysql or mysqli interface on the php side?

I would give the "log" and "log_slow_queries" in the mysql config file a try (possibly to a ramdisk) along with wading trough the output for "SHOW GLOBAL STATUS;" in the mysql shell (every server variable that ends in "*_waits" or is connection related).

Do you have altered any part in the "Fine tuning" section of the mysql config file? Changed some buffers?

In the php.ini, do you have a default value (60) for mysql.connect_timeout? Setting "mysql.trace_mode" to "on" won't hurt for a while.

You also might want to stress-test, if possible, different parts/URLs of your app with a tool like "ab" in order to narrow it down.

Or: When using apache with prefork module, locally start only one server ("Startservers 1", "MaxSpareServers 0", something like that) and stress test until it hangs. Then the logs could be of more value.