jump to navigation

A Simple PHP-script to Benchmark Read/Write Cycles of Mysql , SQLite and Regular-Textfiles July 30, 2012

Posted by Tournas Dimitrios in PHP.
trackback

Nowadays , all websites have some kind of persistent data storage mechanism . Although Mysql is the most used data storage tool , alternative tools are also available (SQLite , MongoDB and regular text-files ) . A developer should know how each of these tools behave , in the context of speed , and adopt the proper tool for building his web application . This article will demonstrate how I had implemented a basic benchmarking script to evaluate the speed of the aforementioned persistent storage tools . MongoDB was excluded from the comparison list , as it’s usually only available on VPS-hosted sites .
The consept is simple : a custom “Timer” Class will handle all timing functionality (counting time) .To keep thinks simple , each storage tool will be evaluated separately (in it’s own script)  .  Each script will follow these steps :

  • Connecting to the database . In case of the “file-storage” evaluation script , just the proper file will be opened .
  • A loop (1000 times) will write some dummy text into the persistent data tool .
  • Reading the content that was written in previous step .
  • Writing the content into an array .

On each of the aforementioned steps , the “Timer” will display the time (in seconds) from the beginning of the script up to the current execution stage .

Keep in mind that a benchmark is always biased by the chosen methodology , platform  , the method choosen to store data into a file / database could have an huge impact on the performances . My golden rule : Don’t jump too fast on the conclusions , especially when comparing / benchmarking unrelated  tools .  It would be WRONG to say that SQLite3 is faster than Mysqli . The results provided on this article should only be taken into account on small-sized websites (up-to 10000 visitors/day) .

The PHP-timer script : 

<!--?<span class="hiddenSpellError" pre=""-->php
class Timer
{
    private  $start ;
    private  $pause_time ;

    /*  start the timer  */
	  public function  __construct($start = 0)
    {
        if($start) { $this->start(); }
    }

    /*  start the timer  */
    public function start()
    {
        $this->start = $this->get_time();
        $this->pause_time = 0;
    }

    /*  pause the timer  */
    public function pause()
    {
        $this->pause_time = $this->get_time();
    }

    /*  unpause the timer  */
    public function unpause()
    {
        $this->start += ($this->get_time() - $this->pause_time);
        $this->pause_time = 0;
    }

    /*  get the current timer value  */
    public function get($decimals = 8  )
    {
        return round(($this->get_time() - $this->start) , $decimals);
    }

    /*  format the time in seconds  */
    public function get_time()
    {
        list($usec,$sec) = explode(' ', microtime());
        return ((float)$usec + (float)$sec);
    }
}

The Mysqli benchmark script : 

 <!--?php-->
require_once('Timer.Class.php') ;
$timer = new Timer(1);
$mysqli = mysqli_connect("localhost","root","", "test") or die ("could not connect to mysql");
echo  "Db connection established  at  : " . $timer->get() . "<br \> " ;
  // Create an sql command structure for creating a table
    $tableCreate = "CREATE TABLE IF NOT EXISTS test_tbl (
                                          id int(11) NOT NULL auto_increment ,
										  RandomTxt TEXT ,
                                          PRIMARY KEY (id)
                                          ) ";
    // This line uses the mysqli_query() function to create the table now
    $queryResult = mysqli_query($mysqli , $tableCreate);
echo  "Table created at  : " . $timer->get() . "<br \> " ;
    // Create a conditional to see if the query executed successfully or not
    if ($queryResult === TRUE) {
		for ($i = 1; $i <= 1000; $i++) {
    mysqli_query($mysqli ,
	"INSERT INTO Test_tbl (RandomTxt) VALUES ('abcdefghklmnopqrsst')" )  ;
			   }
    } else {
    print "<br /><br />No TABLE created. Check";
    }
echo  "Data inserted into the table at  : " . $timer->get() . "<br \> " ;
   $result = mysqli_query($mysqli , 'SELECT * FROM Test_tbl') ;
   $arrayResults = array() ;
   while ($row = $result->fetch_assoc()) {
 array_push($arrayResults , $row['RandomTxt']);     }
echo "Data is read from table and inserted into an array at  : ". $timer->get() . "
 " ;
//print_r($arrayResults) ;

The SQLite3 benchmark script :

<?php

require_once('Timer.Class.php') ;
try
  {

  $timer = new Timer(1);
    //open the database or create a new if not already exists

	$db = new PDO('sqlite:benchmark.sqlite') ;
	echo  "Db connection established  at  : " . $timer->get() . "<br \> " ;
    //create the database
$db->exec("CREATE TABLE Test_tbl (Id INTEGER PRIMARY KEY, RandomTxt TEXT)");
	echo  "Table created at  : " . $timer->get() . "<br \> " ;
    //insert some data...
	for ($i = 1; $i <= 1000; $i++) {
    $db->exec("INSERT INTO Test_tbl (RandomTxt) VALUES ('abcdefghklmnopqrsst')");

			   }

echo  "All data is set into the table  at : " . $timer->get() . "
 " ;
    $result = $db->query('SELECT * FROM Test_tbl') ;
	$arrayResults = array() ;
    foreach($result as $row)
    {
	  array_push($arrayResults , $row['RandomTxt']);
    }
echo "Data is read from table and inserted into an array at  : ". $timer->get() . "
 " ;

//print_r($arrayResults) ;

    // close the database connection
    $db = NULL;
  }
  catch(PDOException $e)
  {
    print 'Exception : '.$e->getMessage();
  }

The simple-file benchmark script : 

<?php
require_once('Timer.Class.php') ;
$timer = new Timer(1) ;
$file = 'benchmark.txt';
$content = "abcdefghklmnopqrsst\t";
// Write (append)  the content into the file
echo  "Opening file for inserting the data  : " . $timer->get() . "<br \> " ;
for ($i = 1; $i <= 1000; $i++) {
file_put_contents($file, $content , FILE_APPEND );
}
echo  "All data is inserted into the file   : " . $timer->get() . "
 " ;
$fileContent = file_get_contents($file);
echo  "End of file read at  : " . $timer->get() . "<br \> " ;
$arrayResults = array() ;
array_push($arrayResults , explode("\t", $fileContent) );
echo  "Data inserted into array at  : " . $timer->get() . "<br \> " ;
print_r($arrayResults) ;
//print_r($fileContent)  ; 

Here are the results : 

The time elapsed from the beginning of the script up to this step (in seconds)
Different steps Mysqli SQLite3 Regular-file
Step 1 : Db connection established 0,00173783 0,00039816 ——
Step 2 : Table Create 0,00225496 0,00075912 —–
Step 3 : Write all data into the table (via a loop) 92,43907094 131,71816301 0,19595092
Step 4 : Read the data and write it into an array 92,46624496 131,72408795 0,19660997

Some notes : 

  • Mysqli database connections are made via the network  (tcp socket , port 3306 ) . In the above table , it is clearly indicated that Mysql demands  170ms (whereas SQLite demands only 0.39ms) . SQLite is writen on server’s file-system (no network connection is needed) .
  • Mysqli has a good authentication mechanism (username-password) . Whereas SQLite and regular file have no build-in authentication mechanism . The developer should take all necessary steps to secure these files .
  • Mysqli compared with SQLite3 is much faster when writing content into tables  .
  • SQLite3 compared with Mysqli  is much faster when reading content .
  • Regular-file storage is much faster than Mysqli and SQLite when reading/writing content . Regular-files can only store “scalar” values , whereas Mysqli and SQLite are relational databases .

Which tool is most useful ? It all depends on the application , my personal preference is to use a combination of the three aforementioned tools .

Comments»

1. lina - August 23, 2012

good post keep posting…


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s