Benchmarking our LAMP servers with this php script

Question

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

1. Disk I/O
2. Database I/O (mysql)
3. 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?

<?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<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->exec('create table temp(t text)');
        $db->exec("begin");
        $stmt = $db->prepare("insert into temp values(:id)");
        for($i=0;$i<$iterations;$i++) {
            $stmt->bindValue(':id', $payload, SQLITE3_TEXT);
            $stmt->execute();
            //$db->exec("delete from temp");
            };
        $db->exec("commit");

    }
    else if ($type=='mysql') //mysql test
    {
        $mysqli = new mysqli($mysqlserver, $mysqlusername, $mysqlpassword, $mysqldatabase);
        $mysqli->query('create table temp(t varchar(108000))');
        $mysqli->query('begin transaction');
        for($i=0;$i<$iterations;$i++) 
            $mysqli->query("insert into temp values('{$payload}')");
        $mysqli->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<$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<$iterations;$i++) {
        $result = $db->query("select t from temp limit 1");
        $result = $result->fetchArray()['t'];
        //$db->exec("delete from temp");
    };
        //var_dump($result);
}
else if ($type=='mysql'){
    $stmt = $mysqli->prepare('select t from temp limit 1');
    if ($stmt) 
    {
        for($i=0;$i<$iterations;$i++) {
            $stmt->execute();
            $stmt->bind_result($result);
            $stmt->fetch();
        };
    }
}
else
{
    for($i=0;$i<$iterations;$i++) $result = file_get_contents($fname);
}
$rtime=round((microtime(true) - $start)*1000);

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

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

echo json_encode($result);

Answer 1

To address the database code stuff. I don't know much about PHP, but I've heard from good sources "don't concatenate SQL queries"

That said, I feel that part of the reason your database calls are slower is because you are just passing ad-hoc scripts to the RDBMS so it has to figure out the execution plan each time since it is not stored. Best practice for performance is to let the RDBMS do as much of the DB work as possible, as that is what it's good at.

Let's suppose you ran this script just once in MySQL:

DROP TEMPORARY TABLE IF EXISTS tt_mysqli_benchmark;
CREATE TABLE tt_mysqli_benchmark 
    (
    t VARCHAR(108000)
    );

-- Ad hoc code to create stored procedure

CREATE PROCEDURE sp_mysqli_benchmark 

    (
    IN p_payload VARCHAR(108000)
    );
BEGIN
    DELIMITER //
    DELETE FROM tt_mysqli_benchmark ;
    INSERT INTO tt_mysqli_benchmark (t)
    VALUES (p_payload);
DELIMITER ;
END;

Then this section of your PHP script:

    else if ($type=='mysql') //mysql test
    {
        $mysqli = new mysqli($mysqlserver, $mysqlusername, $mysqlpassword, $mysqldatabase);
        $mysqli->query('create table temp(t varchar(108000))');
        $mysqli->query('begin transaction');
        for($i=0;$i<$iterations;$i++) 
            $mysqli->query("insert into temp values('{$payload}')");
        $mysqli->query('commit');
    }

Would become:

    else if ($type=='mysql') //mysql test
    {
        $mysqli = new mysqli($mysqlserver, $mysqlusername, $mysqlpassword, $mysqldatabase);
        $mysqli->query("CALL sp_mysqli_benchmark('{$payload}')");
    }

My PHP syntax may be slightly off. As you can see though, the PHP is cleaner, and only parameters are passed to the RDBMS, which makes it better able to optimize it, and lets it remember the execution plan for next time since the procedure is stored.

I don't know as much about SQLite but it likely is very similar, if not simpler.

Answer 2

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="";) 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 "c" would be better than an "n", but really, variables should have some sort of context in them. So "$charCode" 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 = "";
    for ($i = 0; $i < $length; $i++) {
        $returnString .= chr(rand(65, 122));
    }
    return $returnString;
}

Great! Now we can replace

for($i=0;$i<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) . ".txt";

---

Towards the end there, you have

$stmt = $mysqli->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!