Apache, PHP & MySQL Performance Tweaks.

nainil
nainil used Ask the Experts™
on
Hi,

Do you have any performance enhancement tips for Apache, PHP & MySQL?

For me Performance = Something that will result into faster website delivery and help utilize the server resources optimally.

I use WAMP most of the times for my setup. I know it's not recommended to use it in production sites.

Server has 500GB HDD, 4GB RAM.

I usually have a lot of read-write on the disk/database. DB has indexes.

I am not using any caching. Any good suggestions?


Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012
Commented:
Most Valuable Expert 2011
Top Expert 2016
Commented:
This will turn up some interesting links.
http://lmgtfy.com/?q=Should+I+Normalize+my+Database

In questions of computer performance, there are three elements that matter:  The system under test, the workload, and the response time.  It's like Ohm's law.  Fix any two and the third is determined.  If you have performance problems, the first place to look is always the I/O subsystem.  With modern web applications that is always the data base.  Here are a few guidelines that will serve you well.

Never use SELECT * -- instead name the columns you need.
Always use LIMIT on any query that does not require a complete table scan.
Add indexes on every column used in WHERE, ORDER, GROUP or JOIN clauses.
Avoid "blob" data like images or large texts --  put these in the server file system.
Use EXPLAIN SELECT on any query that accesses more than one table or contains any conditional clauses.
Time your scripts to see where the time is being spent.  Something like the code snippet can be useful.

HTH, ~Ray
<?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();
    }

    // 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>";

Open in new window

Commented:
1. Definitely tune MySQL and pay special attention to using query caching and the slow query log:
http://www.experts-exchange.com/Database/MySQL/A_1250-3-Ways-to-Speed-Up-MySQL.html

Query caching is safe to use (no worries about old data) and will eliminate a lot of disk activity.

2. If you can make hardware changes, then I would suggest throwing a small SSD drive on there and move your databases onto it (make regular, consistent backups that you're checking). If you have enough room on it (ensuring room to grow later), you can store other Apache server resources. If cost is a factor AND if HDD space is an issue, then I would go with a RAID 10 setup if you can. That should give you the most reliable disk performance and fault-tolerance.

3. If you have some really-frequently-accessed static files (e.g. your main website's images and Javascript), consider using a memory cache on the web server (or even a small RAM drive like the free one from DataRAM) so that they are being served from ultra-fast memory and freeing up the hard drive for other tasks.

All the other / previous comments are also good ones. The reason we pay attention to the database so much is because it is often the biggest culprit of performance problems (slow queries in particular)
Most Valuable Expert 2011
Top Expert 2016

Commented:
To Jonathan's excellent point about the "biggest culprit" here is the math.

Disk access is typically measured in milliseconds.  Memory access is measured in nanoseconds.  So an improvement made in the database has the potential to be a million times more valuable when compared to an improvement made in the program logic.  In other words, don't spend your time wondering whether explode() is faster than a regular expression -- optimize something you can measure with a hand-held stopwatch!
Commented:
Also bear in mind that web server performance issues have a compounding factor because the problems are repeated with each visitor. If you have a web page that takes 5 seconds to render (just the page, not the full thing with images and Javascript, etc), then it will take 5 seconds for EVERY visitor. This means that your server resources might be being completely taken up for 5 seconds every single time someone hits the page.

If you typically have more than one visitor in a 5-second period, person #2 may be waiting for several seconds before their request even gets started, which means they might be waiting for 8 seconds (3 seconds of waiting for the first one to finish, and then another 5 seconds of page load time). In those 8 seconds, you may have another visitor, and so on and so on.

Your goal as a server admin is to be able to ensure that your server is delivering pages and resources faster than they are being requested. So if you get a visitor to a specific page every 5 seconds, then you need to make sure that page is not taking longer than 5 seconds to load, or else you're probably going to run out of memory, resources, and then crash.

On the bright side, every bit of performance gained is also multiplied. So if you have a thousand visitors a day and you are able to shave off a second of loading time, you've just vastly improved the number of visitors you can handle, and made the existing visitors happier.

This also works in terms of bandwidth. If you erase a single space in an HTML file that gets one million hits in a month, then you've just saved about one megabyte of bandwidth by deleting that one space. If you have the server resources / CPU to enable mod_gzip on your Apache server, then that COULD reduce your bandwidth down to 1/10th of what it was originally. The downside is that pages have to completely render before they are sent to the browser, so if you have any pages that depend on progressive rendering (showing parts of a web page as it loads), this could cause problems with them unless you disable the gzip function for that specific page.

A good tool to use is YSlow - it should help you optimize various parts of your final web page and performance times (and FireBug's "Net" console should also help you visualize how your pages load), but you always need to be aware of bottlenecks. You can optimize Apache all you want, but if you have bad PHP code or a slow query, no amount of optimization is going to fix it. It's like trying to use makeup to cover up a bullet wound. Optimization should be the step you take after your code is ensured to be working efficiently.
Most Valuable Expert 2011
Top Expert 2016

Commented:
@gr8gonzo: Very well said!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial