Solved

Slow load times converting PHP from MySQL to MySQLi

Posted on 2013-05-11
7
973 Views
Last Modified: 2013-05-13
We are upgrading a PHP project from MySQL to MySQLi.  There are two servers, one web and one database.  The database runs MySQL 5, the web server runs PHP 5.4.  We have a development and production environment on the web server.  The development environment was updated to use MySQLi and everything functions but the load times are very high.  The same access in the production environment (same server/PHP version/MySQL version) is near instantaneous, about <1 second, while the development environment can take in excess of 30 seconds.  It's the same query and eventually the data loads in the development environment, it's just very slow.  I tried changing the hostname in the connect object from a hostname to IP, this did not make a difference.  We tried persistent connections and that also made no difference.

All things being equal, it has to be something different between MySQL and MySQLi (or maybe the fact that both are installed on the same server?).  Does anyone have a suggestion as to what we can look at to resolve this problem?
0
Comment
Question by:dageyra
  • 4
  • 3
7 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39159410
Without seeing and timing all of the queries it would be difficult to isolate the issue.  This is a little timer class that can be used to wrap a virtual stopwatch around a block of code.  You can use it to help isolate the parts of a script that are running slowly.  If you can do that and show us the slow queries we may be able to help speed them up.  The sample use case for the class starts at line 160 where the example times the response from some external web sites.  

<?php // RAY_class_Stopwatch.php
error_reporting(E_ALL);


// DEMONSTRATE A SCRIPT TIMER FOR ALL OR PART OF A SCRIPT PHP 5+
// MAN PAGE http://php.net/manual/en/function.microtime.php


class StopWatch
{
    protected $a; // START TIME
    protected $s; // STATUS - IF RUNNING
    protected $z; // STOP TIME

    public function __construct()
    {
        $this->a = array();
        $this->s = array();
        $this->z = array();
    }

    public function __destruct()
    {
        $ret = $this->readout();
        if (!$ret) return FALSE;
        echo
          __CLASS__
        . '::'
        . __FUNCTION__
        . '() '
        ;
        echo "<b>$ret</b>";
        echo PHP_EOL;
    }

    // A METHOD TO REMOVE A TIMER
    public function reset($name='TIMER')
    {
        // RESET ALL TIMERS
        if ($name == 'TIMER')
        {
            $this->__construct();
        }
        else
        {
            unset($this->a[$name]);
            unset($this->s[$name]);
            unset($this->z[$name]);
        }
    }

    // A METHOD TO CAPTURE THE START TIME
    public function start($name='TIMER')
    {
        $this->a[$name] = microtime(TRUE);
        $this->z[$name] = $this->a[$name];
        $this->s[$name] = 'RUNNING';
    }

    // A METHOD TO CAPTURE THE END TIME
    public function stop($name='TIMER')
    {
        $ret = NULL;

        // STOP ALL THE TIMERS
        if ($name == 'TIMER')
        {
            foreach ($this->a as $name => $start_time)
            {
                // IF THIS TIMER IS STILL RUNNING, STOP IT
                if ($this->s[$name])
                {
                    $this->s[$name] = FALSE;
                    $this->z[$name] = microtime(TRUE);
                }
            }
        }

        // STOP ONLY ONE OF THE TIMERS
        else
        {
            if ($this->s[$name])
            {
                $this->s[$name] = FALSE;
                $this->z[$name] = microtime(TRUE);
            }
            else
            {
                $ret .= "ERROR: CALL TO STOP() METHOD FOR '$name' IS NOT RUNNING";
            }
        }

        // RETURN AN ERROR MESSAGE, IF ANY
        return $ret;
    }

    // A METHOD TO READ OUT THE TIMER(S)
    public function readout($name='TIMER', $dec=3, $m=1000, $eol=PHP_EOL)
    {
        $str = NULL;

        // GET READOUTS FOR ALL THE TIMERS
        if ($name == 'TIMER')
        {
            foreach ($this->a as $name => $start_time)
            {
                $str .= $name;

                // IF THIS TIMER IS STILL RUNNING UPDATE THE END TIME
                if ($this->s[$name])
                {
                    $this->z[$name] = microtime(TRUE);
                    $str .= " RUNNING ";
                }
                else
                {
                    $str .= " STOPPED ";
                }

                // RETURN A DISPLAY STRING
                $lapse_time = $this->z[$name] - $start_time;
                $lapse_msec = $lapse_time * $m;
                $lapse_echo = number_format($lapse_msec, $dec);
                $str .= " $lapse_echo";
                $str .= $eol;
            }
            return $str;
        }

        // GET A READOUT FOR ONLY ONE TIMER
        else
        {
            $str .= $name;

            // IF THIS TIME IS STILL RUNNING, UPDATE THE END TIME
            if ($this->s[$name])
            {
                $this->z[$name] = microtime(TRUE);
                $str .= " RUNNING ";
            }
            else
            {
                $str .= " STOPPED ";
            }


            // RETURN A DISPLAY STRING
            $lapse_time = $this->z[$name] - $this->a[$name];
            $lapse_msec = $lapse_time * $m;
            $lapse_echo = number_format($lapse_msec, $dec);
            $str .= " $lapse_echo";
            $str .= $eol;
            return $str;
        }
    }
}



// DEMONSTRATE THE USE -- INSTANTIATE THE STOPWATCH OBJECT
$sw  = new Stopwatch;

// SET A STOPWATCH NAME THAT REFLECTS THE PARTS OF THE SCRIPT WE WANT TO TIME
$g_timer = 'GOOGLE TIMER';

// START A TIMER TO GET ELAPSED TIME FOR A CALL TO GOOGLE
$sw->start($g_timer);

// PERFORM SOME ACTIVITY THAT YOU WANT TO TIME (READS GOOGLE WEB PAGE)
$page = 'http://google.com';
$html = file_get_contents($page);

// GET A READOUT OF THE TIMER WHILE IT IS STILL RUNNING
echo nl2br($sw->readout($g_timer));
echo "<br/>" . PHP_EOL;

// PERFORM SOME OTHER ACTIVITY (READS GOOGLE WEB PAGE AGAIN)
$page = 'http://google.com';
$html = file_get_contents($page);

// STOP THE TIMER AND GET A READOUT WITH SHORT DECIMALS
$x = $sw->stop($g_timer);
echo nl2br($sw->readout($g_timer, 1));
echo "<br/>" . PHP_EOL;




// START A SECOND TIMER
$y_timer = 'YAHOO TIMER';
$sw->start($y_timer);

// PERFORM SOME OTHER ACTIVITY THAT YOU WANT TO TIME
$page = 'http://yahoo.com/';
$html = file_get_contents($page);

// REPORT THE STOPWATCHES CONTENT (ONE IS STOPPED AND ONE IS STILL RUNNING)
echo nl2br($sw->readout());
echo "<br/>" . PHP_EOL;

// SHOW THE OBJECT
echo "<pre>";
var_dump($sw);
echo "</pre>";

// STOP ALL OF THE STOPWATCHES
$sw->stop();

// REPORT THE STOPWATCHES CONTENT AGAIN
echo nl2br($sw->readout());
echo "<br/>" . PHP_EOL;

// SHOW THE OBJECT
echo "<pre>";
var_dump($sw);
echo "</pre>";



// TRY TO STOP A TIMER THAT IS NOT RUNNING
$x = $sw->stop($g_timer);
var_dump($x);
echo "<br/>" . PHP_EOL;
echo "<br/>" . PHP_EOL;



// START THIS TIMER OVER AGAIN
$sw->start($y_timer);

// PERFORM SOME OTHER ACTIVITY THAT YOU WANT TO TIME
$page = 'http://weather.yahoo.com/';
$html = file_get_contents($page);

// REPORT THE STOPWATCHES CONTENT
echo nl2br($sw->readout());
echo "<br/>" . PHP_EOL;

// SHOW THE OBJECT
echo "<pre>";
var_dump($sw);
echo "</pre>";



// REMOVE ONE OF THE STOPWATCHES
$sw->reset($g_timer);

// REPORT THE STOPWATCHES CONTENT
echo nl2br($sw->readout());
echo "<br/>" . PHP_EOL;

// SHOW THE OBJECT
echo "<pre>";
var_dump($sw);
echo "</pre>";



// REMOVE ALL OF THE STOPWATCHES
$sw->reset();
echo "ALL STOPWATCHES HAVE BEEN REMOVED";

// REPORT THE STOPWATCHES CONTENT (SHOWS NOTHING)
echo nl2br($sw->readout());

// SHOW THE OBJECT
echo "<pre>";
var_dump($sw);
echo "</pre>";

// SHOW THE DESTRUCTOR IN ACTION
$sw->start('Foo');
echo "<pre>";
unset($sw);
echo "</pre>";

Open in new window

Please let us know what you find, thanks. ~Ray
0
 
LVL 1

Author Comment

by:dageyra
ID: 39160230
Thanks for the reply, but we are not talking about a script or two.  It's an application with over 50K lines of code.  All of the DB work is funneled through a wrapper library with a primary function for executing the queries of the application.  It is this function in the development code I migrated from MySQL to MySQLi for testing and the only reference to MySQL (for expansion/compatibility purposes) in the project.  

The thing is the query would be the same in the production or development.  Even the simple task of logging in to the application takes far longer in development and that only accesses a minimal amount of data/lookups.  I could export some of the queries through our logging mechanism, but they will be simple reads and writes, nothing fancy for the most part.  Everything is significantly slower under MySQLi.

I am going to try to build a small script outside the application and use MySQLi for it to see what happens, but I was hoping there might be some implementation aspect of MySQLi that I am missing and could be causing the delay in execution (for instance I read a post about switching from hostname to IP usage for MySQL connections, but that did not help).

I was also thinking about using PDO instead, though I think this will require a larger time investment than switching to MySQLi, which I found very straightforward compared to the MySQL code.
0
 
LVL 1

Author Comment

by:dageyra
ID: 39160264
Something else came up in my testing just now, there was an error related to this snippet:

if(($queryResult->free()) == false) {
        if($verbose) {
	    error("Could not free data from query", NULL);
	   }
}

Open in new window


Here  is the (old) production code equivalent:

if((mysql_free_result($queryResult)) == false) {
        if($verbose) {
	    error("Could not free data from query", NULL);
	   }
}

Open in new window


In this case $queryResult is the resulting object (previously it was the return value of a mysql_query call) from a query call on the MySQLi object.  We free the result after the data is pulled into an associative array.  However now that we are using MySQLi, this error keeps being called.  Maybe I am not understanding how to the free the result, or if it's unnecessary?  When this code is commented out the delay goes away and the load times are instantaneous as in production.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 1

Author Comment

by:dageyra
ID: 39160275
I reviewed the ::free() method and see now that it no longer returns a return value, now void instead of bool.  I changed the code to not compare the return value, just execute the method.  The application still runs quickly, I just want to be sure the memory is being cleared, I don't want to drag down the database.  Is it normal to not have any error checking on the ::free() method?
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 39161394
OK couple of things in play here...  Is this the function you're talking about?  I do not see any free() method on the MySQLi class or the MySQLi_stmt class.
http://www.php.net/manual/en/mysqli-stmt.free-result.php

This test should be omitted or at least changed to use a triple equal sign, not a double equal sign.  The double equal sign is a "loose" comparison that will interpret NULL or zero as being equal to FALSE.
if(($queryResult->free()) == false)

Open in new window

Depending on what the error() function is doing you may have found the culprit.  If it's logging errors or otherwise engaging in some I/O operation that might explain the performance degradation.

But to look a the larger picture, a method or function that does not return any value cannot be tested for success.  Its work must be accepted as an article of faith.
0
 
LVL 1

Author Comment

by:dageyra
ID: 39161716
Yeh ::free() is just a reference to the ::free_result()



I did realize after reviewing the documentation that it no longer returns a value, the MySQL equivalent of mysql_free_result() did return a bool and I had just updated the code with the appropriate function call.  That's what I was thinking about the function, thanks for the confirmation.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39161846
Great - glad you've got it pointed in the right direction ;-)

Thanks for the points, ~Ray
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

These days socially coordinated efforts have turned into a critical requirement for enterprises.
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
This tutorial demonstrates how to identify and create boundary or building outlines in Google Maps. In this example, I outline the boundaries of an enclosed skatepark within a community park.  Login to your Google Account, then  Google for "Google M…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now