PHP’s MySQL connection timeout

This blog has moved to http://brian.moonspot.net/.

You should be warned. Amazingly, in 10 years of PHP/MySQL development work I never hit this issue in the manner I did this week. There are several reasons that PHP could not be able to connect to MySQL. The MySQL daemon could be down. It could be an authentication problem. Or, perhas the entire server is offline. The last one there is the one I want to talk about.

In your php.ini you will find a value called mysql.connect_timeout. In the PHP ext/mysql it defaults to 60 seconds. Likewise, the php.ini has the same value. As far as I can tell, this timeout only comes in to play when the server is completely offline. If the server is up, but mysqld is not, the server refuses the connection immediately. I suppose if the server was under high load it could be used as well.

Well, IMO, 60 seconds is way to long to wait on a connection to the database for a web application. We had a server offline and expected the mysql_connect() call to simply fail due to the server not being up. However, it was waiting 60 seconds every time for the connection. This caused the PHP processes to hang and caused huge load on the servers.

So, what should it be? Well, the default MySQL connection timeout was 5 seconds up until October when it was changed to 10. I can’t find why it changed. But, anyhow, IMO, a timeout of 5 seconds should be plenty for a web application.

I am working on a patch that I hope will be accepted to the PHP ext/mysql to set this to something more sane. Just have to find time to do it right, on all the branches and get the email written.

About these ads

8 Responses to PHP’s MySQL connection timeout

  1. john allspaw says:

    We see this all the time, Brian, with not ideal solutions. Thanks for the post.

  2. Brian Moon’s Blog: PHP’s MySQL connection timeout

    In the process of debugging one of his scripts, Brian Moon …

  3. […] Moon came across a default setting (and problem) in the MySQL extension that didn’t seem to make much sense to him: There are several reasons that PHP could not be able to connect to MySQL. […] Or, perhaps […]

  4. Dr.Tool says:

    Check if related to resolver (DNS).

  5. doughboy says:

    Yes, Dr. Tool, I have seen that issue before too. But, this was not the case. We use IP addresses for this very reason. We also disable reverse lookups in MySQL.

  6. Gretta says:

    I think this is why the default changed from 5 to 10: http://bugs.mysql.com/bug.php?id=28359

  7. Dave says:

    Yeah dude. 60 seconds is way too long. It’s making debugging difficult right now…

  8. Rafaqat Ali says:

    HI
    i feel this is best , i seek well but i want to save time in database in oracle.plz help me.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: