[MonetDB-users] MonetDB vs MySQL naive benchmark.

15 Sep 2007

      Hi,

I just downloaded and started experimenting with MonetDB today.  We have a 
large PHP/mysql app that runs millions of small/simple queries to generate 
various reports.  (It needs to be refactored, but that is hard).

Anyway, for starters, I wrote a very small/stupid benchmark script for both 
monetdb and mysql.  I expected monetdb to be slower at populating the table, 
and faster at selecting from it, but it was slower at both.  So below I am 
presenting my results, and my php scripts, and I would be very interested to 
know if there is a problem in my methodology, or if I should be running 
certain types of queries that monetdb will excel at.

Mysql Results:
--------------

$ php testmysql.php
MySQL Test
Dropping previous table (if present)
Dropped in 0.000669 seconds

creating table
Created in 0.007732 seconds

populating table
Populated in 1.163141 seconds

selecting from table
queried 10000 records in 1.446646 seconds

MonetDB Results:
----------------

$ php testmonet.php
MonetDB Test
Dropping table
Dropped in 0.039597 seconds

creating table
Created in 0.001858 seconds

populating table
Populated in 7.513073 seconds

selecting from table
Queried 10000 records in 2.680834 seconds

One interesting thing I noticed is that if I did not include the primary key 
index on the where-clause column, then mysql took ~80 seconds, and Monet 
still took about ~2.5 seconds. I guess this is because Monet just uses 
user-specified indexes as hints, so it had really created one anyway, yes?

Anyway, here are the PHP scripts I used:

testmonet.php
----------------

<?php

    echo "MonetDB Test\n";

    $db = @monetdb_connect('sql', 'localhost', 50000, 'monetdb', 'monetdb')
            or die('Failed to connect to MonetDB<br>');

	echo "Dropping table\n";
	$start_time = microtime(true);		
	$sql = "drop table big_table;";
    $res = monetdb_query($sql);
	echo sprintf( "Dropped in %f seconds\n\n", microtime(true) - $start_time);

	echo "creating table\n";
	$start_time = microtime(true);		
	$sql = "create table big_table( col1 int primary key, col2 varchar(20), col3 
int, col4 varchar(20) );";
    $res = monetdb_query($sql) or die ("failed to create table" );
	echo sprintf( "Created in %f seconds\n\n", microtime(true) - $start_time);

	echo "populating table\n";
	$start_time = microtime(true);		
	for( $i = 0; $i < 10000; $i ++ ) {
		$sql = "insert into big_table values ($i, 'col2 $i', $i, 'col4 $i' )";
		$res = monetdb_query( $sql ) or die ("failed to insert at row $i " );
	}
	echo sprintf( "Populated in %f seconds\n\n", microtime(true) - $start_time);

	echo "selecting from table\n";
	$start_time = microtime(true);		
	for( $i = 0; $i < 10000; $i ++ ) {
		$sql = "select col1 from big_table where col1 = $i";
	        $res = monetdb_query($sql);
	        while ( $row = monetdb_fetch_assoc($res) )
        	{
//        	    print_r($row);
	        }
	}
	echo sprintf( "Queried 10000 records in %f seconds\n\n", microtime(true) - 
$start_time);

?>

testmysql.php
----------------

<?php

    echo "MySQL Test\n";

    $db = @mysql_connect('localhost', 'root', '', 'monetdb')
            or die('Failed to connect to MySQL<br>');

    mysql_select_db( "perftest" ) or die ("Failed to select DB" );

    echo "Dropping previous table (if present)\n";
	$start_time = microtime(true);		
	$sql = "drop table big_table";
    $res = mysql_query($sql);
	echo sprintf( "Dropped in %f seconds\n\n", microtime(true) - $start_time);

	echo "creating table\n";
	$start_time = microtime(true);		
	$sql = "create table big_table( col1 int primary key, col2 varchar(20), col3 
int, col4 varchar(20) );";
    $res = mysql_query($sql) or die ("failed to create table" );
	echo sprintf( "Created in %f seconds\n\n", microtime(true) - $start_time);

	echo "populating table\n";
	$start_time = microtime(true);		
	for( $i = 0; $i < 10000; $i ++ ) {
		$sql = "insert into big_table values ($i, 'col2 $i', $i, 'col4 $i' )";
		$res = mysql_query( $sql ) or die ("failed to insert at row $i " );
	}
	echo sprintf( "Populated in %f seconds\n\n", microtime(true) - $start_time);

	echo "selecting from table\n";
	$start_time = microtime(true);		
	for( $i = 0; $i < 10000; $i ++ ) {
		$sql = "select col1 from big_table where col1 = $i";
	        $res = mysql_query($sql);
	        while ( $row = mysql_fetch_assoc($res) )
        	{
//        	    print_r($row);
	        }
	}
	echo sprintf( "queried 10000 records in %f seconds\n\n", microtime(true) - 
$start_time);
?>
</body>
</html>

[MonetDB-users] MonetDB vs MySQL naive benchmark.

Dan Libby