CODEWITHSUNDEEP
phpmysqliis-7.5performance
Josh M.
Josh M.

Reputation: 27791

PHP, MySQL, IIS7.5 is taking far too long for simple queries

I've created a simple PHP test page which connects to a MySQL database, selects one int value, and then frees the result. The page takes 5 seconds or more to load. I've seen a lot of posts about this or similar issues but not of the resolutions have worked. Here is the output I'm getting:

mysql_connect took 4.8948628902435 seconds
mysql_select_db 0.00073790550231934 seconds
mysql_query took 0.0013959407806396 seconds
mysql_free_result took 2.0980834960938E-5 seconds

As you can see, the connection takes far too long and everything else is fast.

What I've tried

The Facts

FYI - I don't do PHP so it's okay to treat me like a baby when suggesting fixes.

The Test Script

<?php
    $mtime = microtime();
    $mtime = explode(' ', $mtime);
    $mtime = $mtime[1] + $mtime[0];
    $starttime = $mtime;

    mysql_connect("the_ip||the_hostname", "the_username", "the_password");

    $mtime = microtime();
    $mtime = explode(" ", $mtime);
    $mtime = $mtime[1] + $mtime[0];
    $endtime = $mtime;
    $totaltime = ($endtime - $starttime);
    echo '<h2> mysql_connect took ' .$totaltime. ' seconds</h2>';
?>

Trace route is instantaneous: By the way, the application in question is MantisBT as well as Wordpress.

  1     2 ms    <1 ms    <1 ms  1.2.3.4
  2    <1 ms    <1 ms    <1 ms  MYSQL5 [5.6.7.8]

Upvotes: 4

Views: 6184

Answers (3)

Paul Grimes
Paul Grimes

Reputation: 31

I had a similar problem, PHP was accessing MySQL with a simple select statement but taking for ages to display.

With help from another forum, the guy suggested that I make a new website and give it a different port than 80, so I did, In IIS manager, under sites, add new site and point it to the virtual DIR, entered the IP address of the server 192.168.2.2 and gave it port 82.

Opened port 82 in firewall and from a networked XP machine entered into the browser 192.168.2.2:82 and it was lightening. Worked first time.

Also, in the IIS manager for the new website, In Output Cacheing, Uncheck Use-mode cache, and it lets the pages run and enter data using the php pages.

A bit late with the reply, but thought I would share it any ways.

Upvotes: 3

Richard Dev
Richard Dev

Reputation: 1170

Another thing to look at is "mysql_connect" which might show you that PHP is having a hard time resolving (finding) the mysql host. Are you using a IP address, localhost, or hostname? (when connecting to the database in php) If it's a hostname try and add the IP to c:\windows\system32\drivers\etc\host

if that doesn't work. If all your "The Facts" are correct then you might have a issue with your php script. Can you post it so I can see what you are running? Also post the mysql table info like "Indexes" etc.

:)

Upvotes: 7

Charles
Charles

Reputation: 51411

Can you please try these two test scripts as well? (Note the microtime change -- the thing you're doing is an ancient PHP4 hack... you aren't using PHP4, are you?)

This one will test the MySQLi extension, if it's available:

<h1>MySQLi Test</h1>
<?php
    $starttime = microtime(true);

    $db = new mysqli("the_ip||the_hostname", "the_username", "the_password");

    $endtime = microtime(true);
    $totaltime = ($endtime - $starttime);
    echo '<h2> mysqli_connect took ' .$totaltime. ' seconds</h2>';

And this one tests the PDO extension, if it's available:

<h1>PDO Test</h1>
<?php
    $starttime = microtime(true);

    $db = new PDO("mysql:host=host_or_ip", "the_username", "the_password");

    $endtime = microtime(true);
    $totaltime = ($endtime - $starttime);
    echo '<h2>PDO took ' .$totaltime. ' seconds</h2>';

If both of these experience the same delay, then we know it's not just something in the ancient "mysql" extension code.

Upvotes: 2

Related Questions