Benchmarking our LAMP servers with this php script I've written a small script to benchmark our LAMP hosted servers that assess the performance based on three factors:


Disk I/O
Database I/O (mysql)
Database I/O (sqlite)


The logic is as follows:


get the type of test performed using a querystring value.
generate a big random string of 100KB.
If (disk-i/o)
write this line to a random file 500 times.
else if (mysql i/o)
insert this as record in a mysql table 500 times.
else if (sqlite i/o)
insert this as record in a sqlite table 500 times.
Write back to response all variables such as the big string ($payload), time taken to write ($wtime), etc..


The program is working fine, but the database i/o is taking way more time than the file i/o. In one test instance, file i/o took only 1.5 seconds, whilst db i/o took 43 seconds! Can you help me with streamlining this code?

&lt;?php
//index.php
$mysqlserver = 'localhost';
$mysqlusername = 'test';
$mysqlpassword='test';
$mysqldatabase='test';

$iterations=500;
$payload=""; //generate a random string of 108KB and a random filename
$fname='';
$rtime=0; //in milliseconds
$wtime=0;
$gentime=0;
$type='';

$db = null;
$mysqli = null;

if (isset($_REQUEST['type'])) $type=$_REQUEST['type'];

//generate:
$start = microtime(true);
for($i=0;$i&lt;108000;$i++) //generate a big string
{
    $n=rand(0,57)+65;
    $payload = $payload.chr($n);
}
$gentime=round((microtime(true) - $start)*1000);


//write test:
$start = microtime(true);
    if ($type=='sqlite') //sqlite test
    {
        $db = new SQLite3("benchmark.db");
        $db-&gt;exec('create table temp(t text)');
        $db-&gt;exec("begin");
        $stmt = $db-&gt;prepare("insert into temp values(:id)");
        for($i=0;$i&lt;$iterations;$i++) {
            $stmt-&gt;bindValue(':id', $payload, SQLITE3_TEXT);
            $stmt-&gt;execute();
            //$db-&gt;exec("delete from temp");
            };
        $db-&gt;exec("commit");

    }
    else if ($type=='mysql') //mysql test
    {
        $mysqli = new mysqli($mysqlserver, $mysqlusername, $mysqlpassword, $mysqldatabase);
        $mysqli-&gt;query('create table temp(t varchar(108000))');
        $mysqli-&gt;query('begin transaction');
        for($i=0;$i&lt;$iterations;$i++) 
            $mysqli-&gt;query("insert into temp values('{$payload}')");
        $mysqli-&gt;query('commit');
    }
    else // Disk I/O
    {
        $fname = chr(rand(0,57)+65).chr(rand(0,57)+65).chr(rand(0,57)+65).chr(rand(0,57)+65).'.txt';
        for($i=0;$i&lt;$iterations;$i++) file_put_contents($fname,$payload);
    }
$wtime=round((microtime(true) - $start)*1000);

//read test:
$start = microtime(true);
$result = '';
if ($type=='sqlite'){
    for($i=0;$i&lt;$iterations;$i++) {
        $result = $db-&gt;query("select t from temp limit 1");
        $result = $result-&gt;fetchArray()['t'];
        //$db-&gt;exec("delete from temp");
    };
        //var_dump($result);
}
else if ($type=='mysql'){
    $stmt = $mysqli-&gt;prepare('select t from temp limit 1');
    if ($stmt) 
    {
        for($i=0;$i&lt;$iterations;$i++) {
            $stmt-&gt;execute();
            $stmt-&gt;bind_result($result);
            $stmt-&gt;fetch();
        };
    }
}
else
{
    for($i=0;$i&lt;$iterations;$i++) $result = file_get_contents($fname);
}
$rtime=round((microtime(true) - $start)*1000);

//cleanup:  
if ($type=='sqlite') 
{
        $db-&gt;exec("drop table temp");
        $db-&gt;close();
}
else if ($type=='mysql') 
{
    $mysqli-&gt;query('drop table temp');
}
else 
{
    unlink($fname);
}

//return:
$result =  array(
    'type'=&gt;($type==''?'disk':$type),
    'iterations'=&gt;$iterations,
    'generate_time'=&gt;$gentime,
    'write_time'=&gt;$wtime,
    'read_time'=&gt;$rtime,
    'server_software'=&gt;$_SERVER["SERVER_SOFTWARE"],
    'payload'=&gt;$result,
);

echo json_encode($result);
I'm not to good at optimizing, so I don't think I'm qualified to touch on that aspect.
So, formatting I guess,
One thing I can say though, is that your code is seriously lacking consistency. Some assignments ($mysqlserver = 'localhost';) you place spaces around operators and use single quotes, on some ($payload=&quot;&quot;;) it's the exact opposite! I suggest you choose a style, or a beautifier for that matter, and apply it to the whole script. It makes reading it, a lot easier. Also, please fix your indentation. Knowing which functions and clauses your closing brackets end is pretty important.
Now the code,
Why $_REQUEST? Are you expecting to receive input from the URL, other pages, and cookies? If so, you may skip this. If not, I suggest you choose between $_GET or $_POST to keep things specific.
How about this:
$n=rand(0,57)+65;
$payload = $payload.chr($n);

It really could be reduced to:
$c = rand(65, 122);
$payload .= chr($c);

I felt a &quot;c&quot; would be better than an &quot;n&quot;, but really, variables should have some sort of context in them. So &quot;$charCode&quot; would be excellent. Hold off on fixing this though!
Now I don't feel qualified to critique the actual I/O code, so I'll leave that up to someone with more knowledge. From what I do know though, I can't see any glaring problems with your database queries that would cause it to be so slow. Hopefully some one with an eye keener than mine can help.

Now this:
$fname = chr(rand(0,57)+65).chr(rand(0,57)+65).chr(rand(0,57)+65).chr(rand(0,57)+65).'.txt'

is very wet. What I mean by that is, we need to DRY it out a bit!
Head back up to the top, and let's make a nifty function:
function rand_char_str($length) {
    $returnString = &quot;&quot;;
    for ($i = 0; $i &lt; $length; $i++) {
        $returnString .= chr(rand(65, 122));
    }
    return $returnString;
}

Great! Now we can replace
for($i=0;$i&lt;108000;$i++) //generate a big string
{
    $n=rand(0,57)+65;
    $payload = $payload.chr($n);
}

with
$payload = rand_char_str(108000);

We can also replace that $fname assignment with
$fname = rand_char_str(4) . &quot;.txt&quot;;


Towards the end there, you have
$stmt = $mysqli-&gt;prepare('select t from temp limit 1');

and then a conditions checking the value of $stmt. You can just place the method call inside the conditional. This one's just nit-picking though!