6

actually its little complicated:

summary: the connection to DB is very slow.

the page rendering takes around 10 seconds but the last statement on the page is an echo and i can see its output while the page is loading in firefox (IE is same). in google chrome the output becomes visible only when the loading finishes. loading time is approximately the same across browsers.

on debugging i found out that its the DB connectivity that is creating problem. the DB was on another machine. to debug further. i deployed the DB on my local machine .. so now the DB connection is at 127.0.0.1 but the connectivity still takes long time.

this means that the issue is with APACHE/PHP and not with mysql. but then i deployed my code on another machine which connects to DB remotely.and everything seems fine.

basically the application uses couple of mod_rewrite.. but i removed all the .htaccess files and the slow connectivity issue remains..

i installed another APACHE on my machine and used default settings. the connection was still very slow.

i added following statements to measure the execution time

    $stime = microtime();  
    $stime = explode(" ",$stime);  
    $stime = $stime[1] + $stime[0];  

// my code -- it involves connection to DB

    $mtime = microtime();  
    $mtime = explode(" ",$mtime);  
    $mtime = $mtime[1] + $mtime[0]; 

    $totaltime = ($mtime - $stime);
    echo $totaltime;

the output is 0.0631899833679

but firebug Net panel shows total loading time of 10-11 seconds. same is the case with google chrome

i tried to turn off windows firewall.. connectivity is still slow

and i just can't quite find the reason.. i've tried multiple DB servers.. multiple apaches.. nothing seems to be working.. any idea of what might be the problem?

[edit]

please go through the comments section for further details. actually i think im getting near to get the solution. basically im working on developing a framework of my own which includes url rewriting through .htaccess files. i added few css and js files and i noticed that multiple requests were being sent for these files for no good reasons (in firefox). i think the problem is somewhat related to CONTENT-LENGTH header as firefox doesnt receive this header so it keeps waiting for the contents (and may be then there is a timeout).. does it have anything to do with Transfer-Encoding:chunked?

Ahmad
  • 474
  • 1
  • 4
  • 19
  • can you provide a link to the page? Are you sure its not the images\css\js that's at issue, can you time the whole page load with the above code, just start it at very begging, and finish at very end. –  Jan 07 '11 at 04:27
  • 1
    Just a note; `microtime(true)` returns the number as a float with µsecs, so you can avoid the addition. – Dan Lugg Jan 07 '11 at 04:41
  • yeah its not images or css or js.. i removed everything from the page.. it just has 2 echo statements.. one to echo the execution time the other one to echo the selected record from db – Ahmad Jan 07 '11 at 04:47
  • the page is on my machine. so i cant give any direct link – Ahmad Jan 07 '11 at 04:49
  • this may be a bad way to test the time of a query due to caching. you might find running this same code more than once in a row will cause varied results. – dqhendricks Jan 07 '11 at 18:41
  • actually im pretty sure that the problem is not with the SQL query. the problem is with SQL connection or with apache settings – Ahmad Jan 10 '11 at 03:31
  • Have you tried doing a manual reverse DNS lookup up on your IP? MySQL has to do that for any host-based permissions GRANT (all it sees when you connect is an IP). If your DNS server is slow, that would explain the delay. – Marc B Jan 11 '11 at 12:08

2 Answers2

32

I'm late to the party here but I have a solution to this issue for any future visitors to this page.

Simply change:

 $link = mysqli_connect('localhost','username','password','db');

to:

 $link = mysqli_connect('127.0.0.1','username','password','db');

This will improve speeds by as much as 1000% on the Local Host.

Jamie Turner
  • 569
  • 1
  • 6
  • 14
  • 2
    I just hit this today. Why is this the case? – Joshua Nov 07 '13 at 02:30
  • #SoftwareEngineering –  Dec 03 '16 at 10:08
  • oddly enough, this change (to 127.0.0.1) fixed our production server problem as well. We had localhost set correctly in /etc/hosts. You can see the sign of this particular problem in your mysql slow-query log (if you have it turned on), where it includes USE TABLENAME; as part of the slow running query. We always saw it on small, properly indexed tables, where there is no reason for the simple sql to run slowly. – Beracah Feb 20 '17 at 21:24
-2

fast-cgi fixed the problem.. thanks to srisa.. just changed apache to run php files through fast-cgi

Ahmad
  • 474
  • 1
  • 4
  • 19