2

I have not changed anything in this situation recently, but recently my mysqli_connect() to a remote server is very slow (about half the time it hits the 30 second max execution time). When it works, the response time is between 28 and 30 seconds.

I have restarted MySQL on both servers. The server holding the PHP script is a local WAMP server, the remote server is a LAMP stack. The LAMP server performs fine serving the same information that the WAMP server is requesting. The queries do not appear to be the issue, instead it looks like mysqli_connect() is the problem:

    $mtime = microtime(); 
    $mtime = explode(" ",$mtime); 
    $starttime = $mtime[1] + $mtime[0]; 
        $this->conn = mysqli_connect($this->host,$this->user,$this->password) or die(mysqli_error($this->conn));
        $etime = microtime(); 
    $etime = explode(" ",$etime); 
    $endtime = $etime[1] + $etime[0]; 
    $totaltime = ($endtime - $starttime);  
    echo('<!--mysqli_connect took: '.$totaltime.' seconds-->');

The page (when it does not give a 500 response for hitting max_execution_time) will show:

<!--mysqli_connect took: 28.975151777267 seconds-->

The same code on the remote LAMP server will show:

<!--mysqli_connect took: 0.036190032958984 seconds-->

I have not updated this code or made any configuration changes since this was working. I get no errors on either server except for the max_execution_time exceeded error when it goes past 30 seconds on the WAMP server. Does anyone have any suggestions?

Thanks for reading.

Sarah Kemp
  • 2,670
  • 3
  • 21
  • 29
  • Do you have enough available connections on the server to meet your demand for connections? Or is there possibly some long-running query which is causing queries/connections to stack up? – Mike Brant Jan 14 '14 at 18:59
  • 10 minutes after a restart: `max_connections` is set to 151, pma shows `max. concurrent connections` at 6 and `threads_connected` is 3 while `threads_created` is 23. There have been no slow queries logged. My mysqli_connect still takes >30 seconds even right after a restart. – Sarah Kemp Jan 14 '14 at 20:20
  • mysqltuner shows Highest usage of available connections: 5% (9/151) – Sarah Kemp Jan 14 '14 at 20:33

2 Answers2

4

Perhaps a DNS issue? Have you tried changing your host string to a straight IP address rather than say a domain? There was a similar question here, Connecting to mysql server(localhost) very slow , that sounded similar however it was local host and changing to 127.0.0.1 fixed the issue.

Community
  • 1
  • 1
Christian D.
  • 83
  • 6
  • I tried the IP per your suggestion and the time was unaffected. Changing the hostname on the LAMP server to use `localhost` rather than the domain name did lower that response time from 0.03 to 0.0002. I assume it is not a DNS issue since I was using the same host name on both servers (not using localhost or 127.0.0.1 for the LAMP server). Thank you for your suggestion though. – Sarah Kemp Jan 14 '14 at 18:14
  • Sarah - I'm really shooting at a dart board here, but here's something else that might possibly be causing the issue (although the time out your talking about is a LOT longer than this one): http://stackoverflow.com/questions/13439817/why-is-my-mysqli-connection-so-slow – Christian D. Jan 14 '14 at 18:49
  • Yeah, it looks like that one is for a slowdown for a local MySQL server (my localhost connection works fine). It also appears to be a problem for Windows 7 and later, and I am using Linux and an older version of Windows Server. – Sarah Kemp Jan 14 '14 at 18:54
  • thanks a lot it worked for me... (it downed from 1.08 sn to 0.004sn) +1 – BARIS KURT Jan 06 '16 at 00:41
0

Found the problem and it is DNS related. I still don't fully understand it though. It appears the daemon named was stopped on the LAMP server. This server is the authoritative NS for the domain name I was using to connect. I don't know why it was working sometimes, or why it didn't work when I used the IP address, but it is working fine now that I started the service again.

Thank you for all of your help.

Sarah Kemp
  • 2,670
  • 3
  • 21
  • 29
  • This is because MySQL server looks up DNS of clients. You could as well [disable](http://developer.sugarcrm.com/2012/01/10/howto-turn-off-mysql-reverse-dns-lookup-to-speed-up-response-times/) it at the server config. – spacediver Jan 14 '14 at 23:55
  • The WAMP server's IP is not controlled by the LAMP server, but by our ISP - so I don't think the problem was related to the LAMP server's inability to find information about the client's IP. I appreciate the link though. – Sarah Kemp Jan 15 '14 at 17:20
  • 1
    Well, for the sake of correctness ;) — MySQL server would lookup DNS names of connecting clients, wherever do those clients get their IP addresses. I believe that it is motivated by more verbose logging and connection display, perhaps. From your solution I see that once turning on named solved the delay, consequently MySQL server actively uses it. Moreover, if by any reason you would start getting resolution delays on named — those delays would propagate to MySQL as well. I cannot see any reason to have this lookup (and service dependency!) configured at all. :) – spacediver Jan 15 '14 at 17:36
  • 1
    I wasn't clear in my comment, I meant that the name server for the WAMP IP is the ISP - not that we got the IP from the ISP which should be obvious. I don't see how starting the name service on the LAMP machine would affect its ability to query DNS information on the WAMP machine, unless `named` is required for it to request DNS information... and from a quick test it looks like it might be. I can get non-authoritative answers back with it stopped but only get authoritative replies if it is running. Hopefully one day I will have time to try out `skip-name-resolve`... Thank you for your help. – Sarah Kemp Jan 15 '14 at 18:32