Re: [MonetDB-users] MonetDB vs MySQL naive benchmark.

15 Sep 2007

      On Fri, Sep 14, 2007 at 09:55:21PM -0600, Dan Libby wrote:
...
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.
First thanks for trying out MonetDB.

It seems your benchmark is mostly measuring the communication overhead of both
database systems. Indeed thats where MySql excels. Add some more 
complexitity to the queries (joins and/or aggregates) and MonetDB should
behave just fine. Maybe even changing your query from

"select col1 from big_table where col1 = $i";

into

"select count(*) from big_table where col1 = $i";

may allready change the picture. For sure your big_table isn't that big.

Niels
...
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
----------------

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
----------------

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>
-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2005.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
_______________________________________________
MonetDB-users mailing list
MonetDB-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- 

Niels Nes, Centre for Mathematics and Computer Science (CWI)
Kruislaan 413, 1098 SJ Amsterdam, The Netherlands
room C0.02,  phone ++31 20 592-4098, fax ++31 20 592-4312
url: http://www.cwi.nl/~niels   e-mail: Niels.Nes@cwi.nl