24

I would like to know how to benchmark a php/mysql site.

We have a web app almost completed and ready to go live, we know how many people are going to be using it in a years time but have absolutely no idea how much bandwidth the average user takes, to how much time they burn up on the database etc. We need to determine the correct servers to purchase.

Is there something server side linux that can monitor these statistics per user? So that we can then take this data and extrapolate it?

If I am going about this completely wrong please let me know but I believe that this is a frequent activity for new web apps.

EDIT: I may have asked for the incorrect information. We can see how long the database queries take and how long it takes to load the page but have no idea what load is placed on the server. The question I am asking is can we handle 100 users at once on average...1000? What type of server requirements are needed to hit 1M users. Etc.

Thanks for your help.

Mihai
  • 26,325
  • 7
  • 66
  • 81
user103219
  • 3,209
  • 11
  • 39
  • 50

7 Answers7

16

A tool that I find fairly useful is jmeter which allows (at it's most basic) you to set your browser to use jmeter as a proxy then you wander all around your website and it will record everything you do.

Once you are happy that it's a decent test of most of your website you can then save the test in jmeter and tell it to run your test with a set number of threads and a number of loops per thread to simulate load on your website.

For example you can run 50 clients each running the testplan 10 times.

You can then ramp the numbers up and down to see the performance impact it has on the site, it graphs the response time for you.

This lets you tune different parameters, try different caching strategies and check the real world impact of those changes.

Matt Wheeler
  • 455
  • 2
  • 10
8

You can use ApacheBench tool (ab, usually a part of apache web-server package) for stress-testing(1k requests with 10 clients = ab -c 10 -n 1000 http://url) of script, that you suspect could be slow enough. It will show you the distribution of response times (in 90% cases request processed in less than 200msec).

Than you can also grab SQL-queries executed by that particular script and do "explain plan" for them to get a rough idea how will it degrade when there will be 10-100-10mln times more records in tables.

Regarding how many users can it serve - you can use your favourite browser and emulate a typical user visit, take access_log file and sum sent bytes (one of last numbers in log line). For example it was 5kb text/html+50kb png/jpg/etc.=55kb per user visit. Plus headers/etc let's say 60kb per visit*1m=60gb traffic per day. Is your bandwidth good enough for that? (60gb/86.4ksec=700kb/sec).

pavel
  • 377
  • 2
  • 11
6

Unless you are using a heavyweight framework or something like that, the DB queries are likely to be the slowest part of your app.

What I do for monitoring is measure execution time for every query in my DB abstraction object. Then, for every query that takes longer than X milliseconds (fill in your own X), I write a line to my query log file that identifies the PHP script file and line number on which the query appeared (use debug_backtrace() to find that information) together with other relevant context data (e.g. user identity, date-time etc.).

This log file can be statistically analyzed later for various info.

For example, you can find which of your queries are taking the greatest total time (relevant to server load). Or which are the slowest (relevant to user experience). Or which user is loading the system most (possibly abuse or robots).

I also plot Pareto charts to identify where best to spend my query optimization efforts.

  • Nice answer! I'm gonna implement that for some larger projects I'm working on! – Ben Fransen Dec 02 '09 at 16:35
  • Could you briefly explain how you use a Pareto chart to identify where time is best spent? – user103219 Dec 03 '09 at 05:20
  • http://en.wikipedia.org/wiki/Pareto_chart Make a list of the total and average query time for each of your queries and sort the list by one or the other metric. Plot the chart as described in the Wikipedia link to visualize how much of your time is spent in the slowest N queries and which queries they are. Start optimization work at the top of the list. –  Dec 12 '09 at 22:09
2

Most importanly, you need to define what you want the performance to be: You can always find areas to optimize. However, improving the response time from 750ms to 650ms may not be worth the time.

As fsb said, your bottlenecks will probably be your database queries. However, I would also stipulate that your bottlenecks are not always (or even likely) where you think they are. I would suggest reading this first, and do a global test of your site.

If it is your application, use xdebug to profile your PHP code. Then use WinCacheGrind or KCacheGrind to analyze the output. This may surprise you.

To address database issues, it is pretty database specific. For MySQL, I turn on the slow query log, log queries not using indices, enable query logging, and use toolkits like Maatkit to analyze the queries and find the bottlenecks.

Community
  • 1
  • 1
Mike Crowe
  • 2,203
  • 3
  • 22
  • 37
1

Try this:

<?php
/**
 * Created by PhpStorm.
 * User: NEO
 * Date: 9/18/2016
 * Time: 10:57 AM
 */

/**
 * PHP Script to benchmark PHP and MySQL-Server
 *
 * inspired by / thanks to:
 * - www.php-benchmark-script.com  (Alessandro Torrisi)
 * - www.webdesign-informatik.de
 *
 * @author odan
 * @license MIT
 */
// -----------------------------------------------------------------------------
// Setup
// -----------------------------------------------------------------------------
set_time_limit(120); // 2 minutes
$options = array();
// Optional: mysql performance test
$options['db.host'] = '127.0.0.1';
$options['db.user'] = 'root';
$options['db.pw'] = '';
$options['db.name'] = 'bache3';
// -----------------------------------------------------------------------------
// Main
// -----------------------------------------------------------------------------
// check performance
$benchmarkResult = test_benchmark($options);
// html output
echo "<!DOCTYPE html>\n<html><head>\n";
echo "<style>
    table {
        color: #333; /* Lighten up font color */
        font-family: Helvetica, Arial, sans-serif; /* Nicer font */
        width: 640px;
        border-collapse:
        collapse; border-spacing: 0;
    }
    td, th {
        border: 1px solid #CCC; height: 30px;
    } /* Make cells a bit taller */
    th {
        background: #F3F3F3; /* Light grey background */
        font-weight: bold; /* Make sure they're bold */
    }
    td {
        background: #FAFAFA; /* Lighter grey background */
    }
    </style>
    </head>
    <body>";
echo array_to_html($benchmarkResult);
echo "\n</body></html>";
exit;
// -----------------------------------------------------------------------------
// Benchmark functions
// -----------------------------------------------------------------------------
function test_benchmark($settings)
{
    $timeStart = microtime(true);
    $result = array();
    $result['version'] = '1.1';
    $result['sysinfo']['time'] = date("Y-m-d H:i:s");
    $result['sysinfo']['php_version'] = PHP_VERSION;
    $result['sysinfo']['platform'] = PHP_OS;
    $result['sysinfo']['server_name'] = $_SERVER['SERVER_NAME'];
    $result['sysinfo']['server_addr'] = $_SERVER['SERVER_ADDR'];
    test_math($result);
    test_string($result);
    test_loops($result);
    test_ifelse($result);
    if (isset($settings['db.host'])) {
        test_mysql($result, $settings);
    }
    $result['total'] = timer_diff($timeStart);
    return $result;
}
function test_math(&$result, $count = 99999)
{
    $timeStart = microtime(true);
    $mathFunctions = array("abs", "acos", "asin", "atan", "bindec", "floor", "exp", "sin", "tan", "pi", "is_finite", "is_nan", "sqrt");
    for ($i = 0; $i < $count; $i++) {
        foreach ($mathFunctions as $function) {
            call_user_func_array($function, array($i));
        }
    }
    $result['benchmark']['math'] = timer_diff($timeStart);
}
function test_string(&$result, $count = 99999)
{
    $timeStart = microtime(true);
    $stringFunctions = array("addslashes", "chunk_split", "metaphone", "strip_tags", "md5", "sha1", "strtoupper", "strtolower", "strrev", "strlen", "soundex", "ord");
    $string = 'the quick brown fox jumps over the lazy dog';
    for ($i = 0; $i < $count; $i++) {
        foreach ($stringFunctions as $function) {
            call_user_func_array($function, array($string));
        }
    }
    $result['benchmark']['string'] = timer_diff($timeStart);
}
function test_loops(&$result, $count = 999999)
{
    $timeStart = microtime(true);
    for ($i = 0; $i < $count; ++$i) {
    }
    $i = 0;
    while ($i < $count) {
        ++$i;
    }
    $result['benchmark']['loops'] = timer_diff($timeStart);
}
function test_ifelse(&$result, $count = 999999)
{
    $timeStart = microtime(true);
    for ($i = 0; $i < $count; $i++) {
        if ($i == -1) {
        } elseif ($i == -2) {
        } else if ($i == -3) {
        }
    }
    $result['benchmark']['ifelse'] = timer_diff($timeStart);
}
function test_mysql(&$result, $settings)
{
    $timeStart = microtime(true);
    $link = mysqli_connect($settings['db.host'], $settings['db.user'], $settings['db.pw']);
    $result['benchmark']['mysql']['connect'] = timer_diff($timeStart);
    //$arr_return['sysinfo']['mysql_version'] = '';
    mysqli_select_db($link, $settings['db.name']);
    $result['benchmark']['mysql']['select_db'] = timer_diff($timeStart);
    $dbResult = mysqli_query($link, 'SELECT VERSION() as version;');
    $arr_row = mysqli_fetch_array($dbResult);
    $result['sysinfo']['mysql_version'] = $arr_row['version'];
    $result['benchmark']['mysql']['query_version'] = timer_diff($timeStart);
    $query = "SELECT BENCHMARK(1000000,ENCODE('hello',RAND()));";
    $dbResult = mysqli_query($link, $query);
    $result['benchmark']['mysql']['query_benchmark'] = timer_diff($timeStart);
    mysqli_close($link);
    $result['benchmark']['mysql']['total'] = timer_diff($timeStart);
    return $result;
}
function timer_diff($timeStart)
{
    return number_format(microtime(true) - $timeStart, 3);
}
function array_to_html($array)
{
    $result = '';
    if (is_array($array)) {
        $result .= '<table>';
        foreach ($array as $k => $v) {
            $result .= "\n<tr><td>";
            $result .= '<strong>' . htmlentities($k) . "</strong></td><td>";
            $result .= array_to_html($v);
            $result .= "</td></tr>";
        }
        $result .= "\n</table>";
    } else {
        $result = htmlentities($array);
    }
    return $result;
}
Erich García
  • 1,648
  • 21
  • 30
1

I've recently developed a PHP component to easily test, and build a report, for your benchmark test. I've also published an article about the importance of benchmark testing, which I indicate the importance of testing due to the strain not testing can cause on your web server. Benchmark testing is extremely important, but I think that in this day and age people tend to forget that, when in reality it should be in the top of the checklist. Bug checking, security checking, then benchmark testing.

In that order.

http://www.binpress.com/app/benchmark-testing-framework/534?ad=1229 - The framework I've developed http://www.binpress.com/blog/2011/08/04/the-important-of-benchmark-testing/?ad=1229 - The article I wrote

Travis Weston
  • 903
  • 6
  • 24
0

I dont have any experience with benchmarking tools but in some cases I create a simple table with the fields id, ipaddress, parsetime, queries. Just insert a new row each time a page refreshes or gets called (in ajax situations). Then analyze the data collected in a week/month/quarter/year. Its not your preferred situation but a simple way to get some statistics on short notice.

Some results on PHP benchmarks: http://www.google.nl/search?hl=nl&source=hp&q=php+benchmark&meta=&aq=f&oq=

John Kugelman
  • 349,597
  • 67
  • 533
  • 578
Ben Fransen
  • 10,884
  • 18
  • 76
  • 129