dbfuncs.php : is the file which contains classes and functions for firing queries on mysql and pgsql
mysqlinsert.php : creates and fires inserts on mysql
mysqlselect.php : creates and fires selects on mysql
pgsqlinsert.php : creates and fires inserts on pgsql
pgsqlselect.php : creates and fires selects on pgsql
benchmark.php : script used to control concurrency and number of queries per script
Please refer to http://jayant7k.blogspot.com/2010/09/database-speed-tests-mysql-and_29.html and http://jayant7k.blogspot.com/2010/09/database-speed-tests-mysql-and.html for benchmarks of selects and inserts respectively.
And the code....
dbfuncs.php
abstract class dbfuncs
{
abstract function insertqry($qry);
abstract function selectqry($qry);
abstract function connectdb();
public function log($str)
{
$file = "error.log";
$fp = fopen($file, "a");
fwrite($fp, "$str\n");
fclose($fp);
}
}
class mysqldb extends dbfuncs
{
private $user = "root";
private $pass = "jayant";
private $host = "localhost";
//private $port = 3307;
private $socket = "/tmp/mysql.sock";
private $database = "benchmark";
public $db;
function __construct()
{
$this->connectdb();
}
public function connectdb()
{
$this->db = mysql_connect($this->host.':'.$this->socket, $this->user, $this->pass) or die(mysql_error())
mysql_select_db($this->database, $this->db);
}
public function insertqry($qry)
{
if(!mysql_ping($this->db))
$this->connectdb();
mysql_query($qry, $this->db) or $this->log(mysql_error());
}
public function selectqry($qry)
{
if(!mysql_ping($this->db))
$this->connectdb();
$rs = mysql_query($qry, $this->db) or $this->log(mysql_error());
return $rs;
}
}
class pgsqldb extends dbfuncs
{
private $dns = "host=localhost port=5432 user=jayant password=12qwaszx dbname=benchmark";
public $db;
function __construct()
{
$this->connectdb();
}
public function connectdb()
{
$this->db = pg_connect($this->dns) or die(pg_last_error());
}
public function insertqry($qry)
{
if(!pg_ping($this->db))
connectdb();
pg_query($this->db, $qry) or $this->log(pg_last_error($this->db));
}
public function selectqry($qry)
{
if(!pg_ping($this->db))
$this->connectdb();
$rs = pg_query($this->db, $qry) or $this->log(pg_last_error($this->db));
return $rs;
}
}
function logtime($str)
{
$file = "benchmark.log";
$fp = fopen($file, "a");
fputs($fp, $str);
fclose($fp);
}
mysqlinsert.php
include "dbfuncs.php";
$scriptno = $argv[1]+1;
$count = $argv[2];
$mysql = new mysqldb();
$start = microtime(true);
for($x=0; $x<$count; $x++)
{
$xx = $x*$scriptno;
$qry = "insert into data (val, txt) values ('$xx','$x in $scriptno')";
$mysql->insertqry($qry);
}
$end = microtime(true);
$log = "\nMysql innodb Time to insert $count in run $scriptno = ".($end-$start);
logtime($log);
pgsqlinsert.php
include "dbfuncs.php";
$scriptno = $argv[1]+1;
$count = $argv[2];
$mysql = new pgsqldb();
$start = microtime(true);
for($x=0; $x<$count; $x++)
{
$xx = $x*$scriptno;
$qry = "insert into data (val, txt) values ('$xx','$x in $scriptno')";
$mysql->insertqry($qry);
}
$end = microtime(true);
$log = "\nAvg Pgsql Time to insert $count in run $scriptno = ".($end-$start);
logtime($log);
mysqlselect.php
include "dbfuncs.php";
$scriptno = $argv[1]+1;
$count = $argv[2];
$mysql = new mysqldb();
$start = microtime(true);
for($x=0; $x<$count; $x++)
{
$xx = $x*$scriptno;
$qry = "select * from `data` where val ='$xx'";
$mysql->selectqry($qry);
}
$end = microtime(true);
$log = "\nMysql innodb Time to select $count in run $scriptno = ".($end-$start);
logtime($log);
pgsqlselect.php
include "dbfuncs.php";
$scriptno = $argv[1]+1;
$count = $argv[2];
$mysql = new pgsqldb();
$start = microtime(true);
for($x=0; $x<$count; $x++)
{
$xx = $x*$scriptno;
$qry = "select * from data where val ='$xx'";
$mysql->selectqry($qry);
}
$end = microtime(true);
$log = "\nPgsql Time to select $count in run $scriptno = ".($end-$start);
logtime($log);
benchmark.php
$count = 100000;
$concurrency = 40;
for($i=0; $i<$concurrency; $i++)
{
exec("php -q mysqlselect.php $i $count > /dev/null &");
// exec("php -q pgsqlselect.php $i $count > /dev/null &");
// exec("php -q mysqlinsert.php $i $count > /dev/null &");
// exec("php -q pgsqlinsert.php $i $count > /dev/null &");
}
All test runs were individual - meaning that only one script was run at a time with different count and concurrency. For inserts $count was set to 10,000 and for selects $count was set to 100,000 while $concurrency kept on varying.
I am using ubuntu 10.04 with 32 bit kernel 2.6.32-24 and ext4 file system. And my system has around 3GB of RAM and Intel Core 2 DUO T5870 @ 2.0 GHz.
