Sharing benchmarks for speed improvements (PHP and MySQL)

Problems with the Windows version of XAMPP, questions, comments, and anything related.

Sharing benchmarks for speed improvements (PHP and MySQL)

Postby celestinoxp » 30. May 2018 10:34

Hi, in order to discuss the speed of Database connections and data speed, i would like to discuss a possible benchmarks to people share.

I have a page in php to work with lotteries, but unfortunately as it works with multiple tables in phpmyadmin and has a code that does thousands of tests with the values of the tables, the program takes about 24 hours to do all the calculations.

To run a bechmark i used this code:

Code: Select all
<?php

/**
 * 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'] = 'localhost';
$options['db.user'] = 'root';
$options['db.pw'] = '';
$options['db.name'] = 'test';
// -----------------------------------------------------------------------------
// 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);

    //Original $mathFunctions = array("abs", "acos", "asin", "atan", "bindec", "floor", "exp", "sin", "tan", "pi", "is_finite", "is_nan", "sqrt");
   $mathFunctions = array("abs", "acos", "acosh", "asin", "asinh", "atan", "atanh", "bindec", "ceil", "cos", "cosh", "decbin", "dechex", "decoct", "deg2rad", "floor", "hexdec", "exp", "expm1", "sin", "tan", "tanh", "pi", "round", "is_finite", "is_nan", "sqrt", "srand");
   
   
   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;
}


Results:
[sysinfo]
php_version 7.2.5
platform WINNT
mysql_version 10.1.32-MariaDB

math 3.982
string 10.925
loops 0.103
ifelse 0.178
connect 0.023
select_db 0.038
query_version 0.061
query_benchmark 32.735
total 32.735

Total final- 47.924

My computer have CPU Intel Atom Z3735F @1.33GHZ (Quad-core)
Ram: 2GB
Windows 10 - 32bits
celestinoxp
 
Posts: 4
Joined: 30. May 2018 10:05
XAMPP version: 3.2.2
Operating System: Windows 10

Re: Sharing benchmarks for speed improvements (PHP and MySQL

Postby celestinoxp » 30. May 2018 20:54

I founded a new code to benchmark, its a new version to simplify the results.

the code:

Code: Select all
<?php

/**
 * 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();

// Show or hide the server name and IP address
$showServerName = false;

// Optional: mysql performance test
$options['db.host'] = '127.0.0.1';
$options['db.user'] = 'root';
$options['db.pw'] = '';
$options['db.name'] = 'test';

// -----------------------------------------------------------------------------
// Main
// -----------------------------------------------------------------------------
// check performance
$benchmarkResult = test_benchmark($options);

// html output
echo "<!DOCTYPE html>\n<html><head>\n";
echo "<style>
       table a:link {
        color: #666;
        font-weight: bold;
        text-decoration:none;
    }
    table a:visited {
        color: #999999;
        font-weight:bold;
        text-decoration:none;
    }
    table a:active,
    table a:hover {
        color: #bd5a35;
        text-decoration:underline;
    }
    table {
        font-family:Arial, Helvetica, sans-serif;
        color:#666;
        font-size:12px;
        text-shadow: 1px 1px 0px #fff;
        background:#eaebec;
        margin:20px;
        border:#ccc 1px solid;
        -moz-border-radius:3px;
        -webkit-border-radius:3px;
        border-radius:3px;
        -moz-box-shadow: 0 1px 2px #d1d1d1;
        -webkit-box-shadow: 0 1px 2px #d1d1d1;
        box-shadow: 0 1px 2px #d1d1d1;
    }
    table th {
        padding:8px 15px 8px 8px;
        border-top:1px solid #fafafa;
        border-bottom:1px solid #e0e0e0;
        text-align: left;
        background: #ededed;
        background: -webkit-gradient(linear, left top, left bottom, from(#ededed), to(#ebebeb));
        background: -moz-linear-gradient(top,  #ededed,  #ebebeb);
    }
    table th:first-child {
        text-align: left;
        padding-left:10px;
    }
    table tr:first-child th:first-child {
        -moz-border-radius-topleft:3px;
        -webkit-border-top-left-radius:3px;
        border-top-left-radius:3px;
    }
    table tr:first-child th:last-child {
        -moz-border-radius-topright:3px;
        -webkit-border-top-right-radius:3px;
        border-top-right-radius:3px;
    }
    table tr {
        padding-left:10px;
    }
    table td:first-child {
        text-align: left;
        padding-left:10px;
        border-left: 0;
    }
    table td {
        padding:8px;
        border-top: 1px solid #ffffff;
        border-bottom:1px solid #e0e0e0;
        border-left: 1px solid #e0e0e0;
        background: #fafafa;
        background: -webkit-gradient(linear, left top, left bottom, from(#fbfbfb), to(#fafafa));
        background: -moz-linear-gradient(top,  #fbfbfb,  #fafafa);
    }
    table tr.even td {
        background: #f6f6f6;
        background: -webkit-gradient(linear, left top, left bottom, from(#f8f8f8), to(#f6f6f6));
        background: -moz-linear-gradient(top,  #f8f8f8,  #f6f6f6);
    }
    table tr:last-child td {
        border-bottom:0;
    }
    table tr:last-child td:first-child {
        -moz-border-radius-bottomleft:3px;
        -webkit-border-bottom-left-radius:3px;
        border-bottom-left-radius:3px;
    }
    table tr:last-child td:last-child {
        -moz-border-radius-bottomright:3px;
        -webkit-border-bottom-right-radius:3px;
        border-bottom-right-radius:3px;
    }
    table tr:hover td {
        background: #f2f2f2;
        background: -webkit-gradient(linear, left top, left bottom, from(#f2f2f2), to(#f0f0f0));
        background: -moz-linear-gradient(top,  #f2f2f2,  #f0f0f0);   
    }
    </style>
    </head>
    <body>";

echo print_benchmark_result($benchmarkResult, $showServerName);

echo "\n</body></html>";
exit;

// -----------------------------------------------------------------------------
// Benchmark functions
// -----------------------------------------------------------------------------

function test_benchmark($settings)
{
    $result = array();
    $result['version'] = '1.2';
    $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'];
    $result['sysinfo']['xdebug'] = in_array('xdebug', get_loaded_extensions());

    $timeStart = microtime(true);

    test_math($result);
    test_string($result);
    test_loops($result);
    test_ifelse($result);

    $result['benchmark']['calculation_total'] = timer_diff($timeStart) . ' sec.';

    if (isset($settings['db.host'])) {
        test_mysql($result, $settings);
    }

    $result['benchmark']['total'] = timer_diff($timeStart) . ' sec.';

    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) . ' sec.';
}

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) . ' sec.';
}

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) . ' sec.';
}

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) . ' sec.';
}

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) . ' sec.';

    mysqli_select_db($link, $settings['db.name']);
    $result['benchmark']['mysql_select_db'] = timer_diff($timeStart) . ' sec.';

    $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) . ' sec.';

    $query = "SELECT BENCHMARK(1000000,ENCODE('hello',RAND()));";
    mysqli_query($link, $query);
    $result['benchmark']['mysql_query_benchmark'] = timer_diff($timeStart) . ' sec.';

    mysqli_close($link);

    $result['benchmark']['mysql_total'] = timer_diff($timeStart) . ' sec.';

    return $result;
}

function timer_diff($timeStart)
{
    return number_format(microtime(true) - $timeStart, 3);
}

function print_benchmark_result($data, $showServerName = true)
{
    $result = '<table cellspacing="0">';
    $result .= '<thead><tr><th>System Info</th><th></th></tr></thead>';
    $result .= '<tbody>';
    $result .= '<tr class="even"><td>Version</td><td>' . h($data['version']) . '</td></tr>';
    $result .= '<tr class="even"><td>Time</td><td>' . h($data['sysinfo']['time']) . '</td></tr>';

    if (!empty($data['sysinfo']['xdebug'])) {
        // You are running the benchmark with xdebug enabled. This has a major impact on runtime performance.
        $result .= '<tr class="even"><td>Xdebug</td><td><span style="color: darkred">'
            . h('Warning: Xdebug is enabled!')
            . '</span></td></tr>';
    }

    $result .= '<tr class="even"><td>PHP Version</td><td>' . h($data['sysinfo']['php_version']) . '</td></tr>';
    $result .= '<tr class="even"><td>Platform</td><td>' . h($data['sysinfo']['platform']) . '</td></tr>';

    if ($showServerName == true) {
        $result .= '<tr class="even"><td>Server name</td><td>' . h($data['sysinfo']['server_name']) . '</td></tr>';
        $result .= '<tr class="even"><td>Server address</td><td>' . h($data['sysinfo']['server_addr']) . '</td></tr>';
    }

    $result .= '</tbody>';

    $result .= '<thead><tr><th>Benchmark</th><th></th></tr></thead>';
    $result .= '<tbody>';
    $result .= '<tr><td>String</td><td>' . h($data['benchmark']['string']) . '</td></tr>';
    $result .= '<tr><td>Loops</td><td>' . h($data['benchmark']['loops']) . '</td></tr>';
    $result .= '<tr><td>If Else</td><td>' . h($data['benchmark']['ifelse']) . '</td></tr>';
    $result .= '<tr class="even"><td>Calculation total</td><td>' . h($data['benchmark']['calculation_total']) . '</td></tr>';
    $result .= '</tbody>';

    if (isset($data['sysinfo']['mysql_version'])) {
        $result .= '<thead><tr><th>MySQL</th><th></th></tr></thead>';
        $result .= '<tbody>';
        $result .= '<tr><td>MySQL Version</td><td>' . h($data['sysinfo']['mysql_version']) . '</td></tr>';
        $result .= '<tr><td>MySQL Connect</td><td>' . h($data['benchmark']['mysql_connect']) . '</td></tr>';
        $result .= '<tr><td>MySQL Select DB</td><td>' . h($data['benchmark']['mysql_select_db']) . '</td></tr>';
        $result .= '<tr><td>MySQL Query Version</td><td>' . h($data['benchmark']['mysql_query_version']) . '</td></tr>';
        $result .= '<tr><td>MySQL Benchmark</td><td>' . h($data['benchmark']['mysql_query_benchmark']) . '</td></tr>';
        $result .= '<tr class="even"><td>MySQL Total</td><td>' . h($data['benchmark']['mysql_total']) . '</td></tr>';
        $result .= '</tbody>';
    }

    $result .= '<thead><tr><th>Total</th><th>' . h($data['benchmark']['total']) . '</th></tr></thead>';
    $result .= '</table>';

    return $result;
}

function h($v)
{
    return htmlentities($v);
}


After run code the results:

System Info
Version 1.2
Time 2018-05-30 21:51:20
PHP Version 7.2.5
Platform WINNT
Benchmark
String 11.472 sec.
Loops 0.123 sec.
If Else 0.205 sec.
Calculation total 13.039 sec.
MySQL
MySQL Version 10.1.32-MariaDB
MySQL Connect 0.002 sec.
MySQL Select DB 0.002 sec.
MySQL Query Version 0.003 sec.
MySQL Benchmark 37.239 sec.
MySQL Total 37.239 sec.
Total 50.278 sec.


Anyone know how to change the speed of "String" and "MySQL Benchmark"? Its necessary change any values in my.ini file?
celestinoxp
 
Posts: 4
Joined: 30. May 2018 10:05
XAMPP version: 3.2.2
Operating System: Windows 10

Re: Sharing benchmarks for speed improvements (PHP and MySQL

Postby TonyVier » 30. May 2018 21:26

I don't think you can speed up PHP by changing values in the ini, unless its removing some debugger. as the source says. Its the processor etc.

Fun to try though:

System Info
Version 1.2
Time 2018-05-30 22:24:29
PHP Version 7.2.3
Platform Linux
Benchmark
String 0.531 sec.
Loops 0.016 sec.
If Else 0.045 sec.
Calculation total 1.162 sec.
MySQL
MySQL Version 10.1.24-MariaDB-cll-lve
MySQL Connect 0.002 sec.
MySQL Select DB 0.002 sec.
MySQL Query Version 0.003 sec.
MySQL Benchmark 12.856 sec.
MySQL Total 12.856 sec.
Total 14.018 sec.


System Info
Version 1.2
Time 2018-05-30 20:17:41
PHP Version 7.2.6
Platform WINNT
Benchmark
String 1.175 sec.
Loops 0.012 sec.
If Else 0.013 sec.
Calculation total 1.343 sec.
MySQL
MySQL Version 10.3.7-MariaDB
MySQL Connect 0.002 sec.
MySQL Select DB 0.002 sec.
MySQL Query Version 0.003 sec.
MySQL Benchmark 4.086 sec.
MySQL Total 4.086 sec.
Total 5.429 sec.
TonyVier
 
Posts: 43
Joined: 24. May 2018 11:22
XAMPP version: 7.2.5-0
Operating System: Windows 10 pro

Re: Sharing benchmarks for speed improvements (PHP and MySQL

Postby TonyVier » 23. June 2018 22:45

Just for the fun of it...

I bought a Raspberry PI and ran the benchmark:

version 1.1
sysinfo
time 2018-06-23 21:43:31
php_version 7.0.27-0+deb9u1
platform Linux
server_name 172.20.20.5
server_addr 172.20.20.5
mysql_version 10.1.23-MariaDB-9+deb9u1
benchmark
math 4.399
string 2.232
loops 0.117
ifelse 0.177
mysql
connect 0.001
select_db 0.001
query_version 0.001
query_benchmark 31.691
total 31.691
total 38.637

Not bad for a 40 eur webserver :)
TonyVier
 
Posts: 43
Joined: 24. May 2018 11:22
XAMPP version: 7.2.5-0
Operating System: Windows 10 pro


Return to XAMPP for Windows

Who is online

Users browsing this forum: No registered users and 28 guests