- users-list - monetdb.org

[MonetDB-users] MonetDB vs MySQL naive benchmark.
by Dan Libby 16 Sep '07

16 Sep '07

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>

3 4

[MonetDB-users] MonetDB vs MySQL naive benchmark.
by Dan Libby 16 Sep '07

16 Sep '07

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>

2 3

[MonetDB-users] [Newbie] sizeof(ssize_t) error on Solaris 10
by Pranav Vaidya 13 Sep '07

13 Sep '07

Hello, I am trying to install MonetDB on Solaris 10/UltraSparc III and Fedora6/AMD. I tried running monetdb-install.sh as suggested in the documentation. The Fedora installation went on fine without any hitches. However, the solaris 10 installation failed. The script is failing due to some error on the test which determines sizeof(ssize_t). To see if there was some problem in my sys/types.h I compiled and executed a code which I think is used by the configure script. This test program ran without any issues writing a value of 4 to the conftest.val file. My guess is ssize_t is typedefd as an int on my machine. The test program is as follows : #include <stddef.h> #include <sys/types.h> long longval () { return (long) (sizeof (ssize_t)); } unsigned long ulongval () { return (long) (sizeof (ssize_t)); } #include <stdio.h> #include <stdlib.h> int main () { FILE *f = fopen ("conftest.val", "w"); if (! f) exit (1); if (((long) (sizeof (ssize_t))) < 0) { long i = longval (); if (i != ((long) (sizeof (ssize_t)))) exit (1); fprintf (f, "%ld\n", i); } else { unsigned long i = ulongval (); if (i != ((long) (sizeof (ssize_t)))) exit (1); fprintf (f, "%lu\n", i); } exit (ferror (f) || fclose (f) != 0); ; return 0; } I am attaching the relevant portion of the config.log that is produced. I would appreciate any inputs on how to resolve this issue and why it arises. ------------------------------------------------------------------------------------------------------------------------------------------------------ #include <sys/types.h> | | long longval () { return (long) (sizeof (ssize_t)); } | unsigned long ulongval () { return (long) (sizeof (ssize_t)); } | #include <stdio.h> | #include <stdlib.h> | int | main () | { | | FILE *f = fopen ("conftest.val", "w"); | if (! f) | exit (1); | if (((long) (sizeof (ssize_t))) < 0) | { | long i = longval (); | if (i != ((long) (sizeof (ssize_t)))) | exit (1); | fprintf (f, "%ld\n", i); | } | else | { | unsigned long i = ulongval (); | if (i != ((long) (sizeof (ssize_t)))) | exit (1); | fprintf (f, "%lu\n", i); | } | exit (ferror (f) || fclose (f) != 0); | | ; | return 0; | } configure:26151: error: cannot compute sizeof (ssize_t), 77 See `config.log' for more details. The following is the configure output - MonetDB>./configure checking build system type... sparc-sun-solaris2.10 checking host system type... sparc-sun-solaris2.10 checking target system type... sparc-sun-solaris2.10 checking for a BSD-compatible install... /usr/local/bin/ginstall -c checking whether build environment is sane... yes checking for gawk... gawk checking whether make sets $(MAKE)... yes checking for style of include used by make... GNU checking for gcc... gcc checking for C compiler default output file name... a.out checking whether the C compiler works... yes checking whether we are cross compiling... no checking for suffix of executables... checking for suffix of object files... o checking whether we are using the GNU C compiler... yes checking whether gcc accepts -g... yes checking for gcc option to accept ANSI C... none needed checking dependency style of gcc... none checking for egrep... egrep checking for ANSI C header files... no checking for sys/types.h... yes checking for sys/stat.h... yes checking for stdlib.h... yes checking for string.h... yes checking for memory.h... yes checking for strings.h... yes checking for inttypes.h... yes checking for stdint.h... yes checking for unistd.h... yes checking how to run the C preprocessor... gcc -E checking for gcc... (cached) gcc checking whether we are using the GNU C compiler... (cached) yes checking whether gcc accepts -g... (cached) yes checking for gcc option to accept ANSI C... (cached) none needed checking dependency style of gcc... (cached) none checking for long... yes checking size of long... 4 checking whether byte ordering is bigendian... yes checking if your compiler is C99 compliant... yes checking for ant... no checking for java... /bin/java checking for Java >= 1.4, but < 1.6... yes -> 1.5.0_12 found checking for javac... /bin/javac checking for jar... /bin/jar checking for javadoc... /bin/javadoc checking for a sed that does not truncate output... /usr/local/bin/gsed checking for option to reload object files... -r checking for BSD-compatible nm... /usr/local/bin/nm -B checking whether ln -s works... yes checking how to recognise dependent libraries... pass_all checking dlfcn.h usability... no checking dlfcn.h presence... yes configure: WARNING: dlfcn.h: present but cannot be compiled configure: WARNING: dlfcn.h: check for missing prerequisite headers? configure: WARNING: dlfcn.h: see the Autoconf documentation configure: WARNING: dlfcn.h: section "Present But Cannot Be Compiled" configure: WARNING: dlfcn.h: proceeding with the preprocessor's result configure: WARNING: dlfcn.h: in the future, the compiler will take precedence configure: WARNING: ## --------------------------- ## configure: WARNING: ## Report this to monet(a)cwi.nl ## configure: WARNING: ## --------------------------- ## checking for dlfcn.h... yes checking for g++... g++ checking whether we are using the GNU C++ compiler... yes checking whether g++ accepts -g... yes checking dependency style of g++... none checking how to run the C++ preprocessor... g++ -E checking for g77... g77 checking whether we are using the GNU Fortran 77 compiler... yes checking whether g77 accepts -g... yes checking for flex... flex checking lex output file root... lex.yy checking whether yytext is a pointer... no checking for a BSD-compatible install... /usr/local/bin/ginstall -c checking for ld used by gcc... /usr/ccs/bin/ld checking if the linker (/usr/ccs/bin/ld) is GNU ld... no checking the maximum length of command line arguments... 262144 checking command to parse /usr/local/bin/nm -B output from gcc object... ok checking for objdir... .libs checking for ar... ar checking for ranlib... ranlib checking for strip... strip checking if gcc supports -fno-rtti -fno-exceptions... no checking for gcc option to produce PIC... -fPIC checking if gcc PIC flag -fPIC works... yes checking if gcc static flag -static works... no checking if gcc supports -c -o file.o... yes checking whether the gcc linker (/usr/ccs/bin/ld) supports shared libraries... yes checking whether -lc should be explicitly linked in... yes checking dynamic linker characteristics... solaris2.10 ld.so checking how to hardcode library paths into programs... immediate checking whether stripping libraries is possible... yes checking for shl_load... no checking for shl_load in -ldld... no checking for dlopen... yes checking whether a program can dlopen itself... yes checking whether a statically linked program can dlopen itself... yes checking if libtool supports shared libraries... yes checking whether to build shared libraries... yes checking whether to build static libraries... no configure: creating libtool appending configuration tag "CXX" to libtool checking for ld used by g++... /usr/ccs/bin/ld checking if the linker (/usr/ccs/bin/ld) is GNU ld... no checking whether the g++ linker (/usr/ccs/bin/ld) supports shared libraries... yes checking for g++ option to produce PIC... -fPIC checking if g++ PIC flag -fPIC works... yes checking if g++ static flag -static works... no checking if g++ supports -c -o file.o... yes checking whether the g++ linker (/usr/ccs/bin/ld) supports shared libraries... yes checking dynamic linker characteristics... solaris2.10 ld.so checking how to hardcode library paths into programs... immediate appending configuration tag "F77" to libtool checking if libtool supports shared libraries... yes checking whether to build shared libraries... yes checking whether to build static libraries... no checking for g77 option to produce PIC... -fPIC checking if g77 PIC flag -fPIC works... yes checking if g77 static flag -static works... no checking if g77 supports -c -o file.o... yes checking whether the g77 linker (/usr/ccs/bin/ld) supports shared libraries... yes checking dynamic linker characteristics... solaris2.10 ld.so checking how to hardcode library paths into programs... immediate checking for ANSI C header files... (cached) no checking whether time.h and sys/time.h may both be included... yes checking for dirent.h that defines DIR... yes checking for library containing opendir... none required checking alloca.h usability... no checking alloca.h presence... yes configure: WARNING: alloca.h: present but cannot be compiled configure: WARNING: alloca.h: check for missing prerequisite headers? configure: WARNING: alloca.h: see the Autoconf documentation configure: WARNING: alloca.h: section "Present But Cannot Be Compiled" configure: WARNING: alloca.h: proceeding with the preprocessor's result configure: WARNING: alloca.h: in the future, the compiler will take precedence configure: WARNING: ## --------------------------- ## configure: WARNING: ## Report this to monet(a)cwi.nl ## configure: WARNING: ## --------------------------- ## checking for alloca.h... yes checking getopt.h usability... no checking getopt.h presence... yes configure: WARNING: getopt.h: present but cannot be compiled configure: WARNING: getopt.h: check for missing prerequisite headers? configure: WARNING: getopt.h: see the Autoconf documentation configure: WARNING: getopt.h: section "Present But Cannot Be Compiled" configure: WARNING: getopt.h: proceeding with the preprocessor's result configure: WARNING: getopt.h: in the future, the compiler will take precedence configure: WARNING: ## --------------------------- ## configure: WARNING: ## Report this to monet(a)cwi.nl ## configure: WARNING: ## --------------------------- ## checking for getopt.h... yes checking netdb.h usability... no checking netdb.h presence... yes configure: WARNING: netdb.h: present but cannot be compiled configure: WARNING: netdb.h: check for missing prerequisite headers? configure: WARNING: netdb.h: see the Autoconf documentation configure: WARNING: netdb.h: section "Present But Cannot Be Compiled" configure: WARNING: netdb.h: proceeding with the preprocessor's result configure: WARNING: netdb.h: in the future, the compiler will take precedence configure: WARNING: ## --------------------------- ## configure: WARNING: ## Report this to monet(a)cwi.nl ## configure: WARNING: ## --------------------------- ## checking for netdb.h... yes checking for sys/types.h... (cached) yes checking for special C compiler options needed for large files... no checking for _FILE_OFFSET_BITS value needed for large files... no checking for _LARGE_FILES value needed for large files... no checking for an ANSI C-conforming const... yes checking for size_t... no checking return type of signal handlers... int checking for ptrdiff_t... yes checking for ssize_t... yes checking for __int64... no checking for long long... no checking for char... no checking size of char... 0 checking for short... no checking size of short... 0 checking for int... no checking size of int... 0 checking for long... (cached) yes checking size of long... (cached) 4 checking for void *... no checking size of void *... 0 checking for size_t... (cached) no checking size of size_t... 0 checking for ssize_t... (cached) yes checking size of ssize_t... configure: error: cannot compute sizeof (ssize_t), However, I think the problem is somewhere above this line as I was getting sizeof(char), sizeof (long long) as zeros. Kindly guide me as I am a relative newbie to MonetDB. Thanks in advance, Pranav. -- View this message in context: http://www.nabble.com/-Newbie--sizeof%28ssize_t%29-error-on-Solaris-10-tf44… Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] [newbie] Loading a directory of XML files into a collection
by Philip Webster 11 Sep '07

11 Sep '07

Hi, I have a directory containing XML files, all sharing a common structure. I want to load them all into a collection so I can run queries against them using the XQuery interface in MonetDB4. The tutorials give useful examples that show how to load a single file into a collection, but I need an automated process to load all of the XML files I have. There are over 60,000 files, currently spread over 16 directories to make them more managable. I've been looking around on the Web, but I haven't found any XQuery examples that do what I want. Is it possible? If so, can anyone give an example to show me how to load my data? Thanks, Phil -- View this message in context: http://www.nabble.com/-newbie--Loading-a-directory-of-XML-files-into-a-coll… Sent from the monetdb-users mailing list archive at Nabble.com.

2 3

[MonetDB-users] Not responding after sql server restart
by Franco Guidoli 11 Sep '07

11 Sep '07

Hi all: I'm trying to measure the response time of MonetDB against a commercial RDBMS, so I've made a dump of the data from my current database in order to load into MonetDB. I have approximately 2.6 million rows, divided into 26 flat files of 100k rows each one, which I load using then SQL command "copy into....". Everything works ok with the data load (performing a commit after each 100k file), and I can make use of that data in the same session from which I've loaded the data, but when I restart MonetDB Server, I can't login via SQL (I can start MonetDB Server, but SQL Client doesn't respond). Apparently, from what I can realize, after the shutdown of MonetDB Server, there is a big log file in Monet's sql_logs directory, and when I get a response from SQL Client, the log file is apparently empty (I've realized this loading only 200k rows). Is this the expected behaviour for the MonetDB SQL Server? Did I miss anything in order to avoid this? After the second start of the server, this doesn't happen anymore until I load new data. I'm working with MonetDB Server v5.0.0_beta1_2 (downloaded msi from sourceforge) over Windows XP, and the SQL Client is via JDBC. Thanks in advance for any help you can give me. Franco -- View this message in context: http://www.nabble.com/Not-responding-after-sql-server-restart-tf3762198.htm… Sent from the monetdb-users mailing list archive at Nabble.com.

5 4

[MonetDB-users] Super Source Ball release update
by Martin Kersten 07 Sep '07

07 Sep '07

A glitch in maintaining the monetdb website led to an outdated link for the super source ball on the download page. This tarball is a convenience package containing all source tars and a script (for Linux) to build components. The link has been updated to the latest version: http://downloads.sourceforge.net/monetdb/MonetDB-Mars-SuperBall-SR3.tar.bz2 The MonetDB Development team

1 0

[MonetDB-users] Announcement: various new packages released
by Sjoerd Mullender 06 Sep '07

06 Sep '07

Today the MonetDB group at CWI have a released bug-fix release for a number of packages. The release contains source tar balls, source RPMs, and i686 and x86_64 (AMD64) binaries for Fedora 7 (RPMs) and Windows XP (.msi files). All files are available on the project download page at sourceforge. The packages and changes are: MonetDB Common 1.18.2. On Windows the indexing service would occasionally interfere with the workings of the server. This was because the indexing service would temporarily prevent files and directories from being removed. This problem has been circumvented. In addition, some minor fixes were made. MonetDB Clients 1.18.2. A memory leak was repaired. MonetDB4 Server 4.18.2. A number of small bugs were fixed. The most important one probably the handling of aggregate. MonetDB XQuery 0.18.4. A bug was fixed in the MIL code generation phase for larger queries. [ 1771532 ] MIL-optimizer fails sometimes for big queries [ 1760168 ] PF: wrong use of leftfetchjoin causes segfault in bat_cast A bug was fixed where BATs were not destroyed that should have been. [ 1763495 ] XQ: illegal offsets in swizzle (2) [ 1766414 ] XQ: third pf-add into collection fails [ 1763575 ] XQ: insert attribute gives ERROR in merged_union [ 1760811 ] XQ: illegal offsets in swizzle. [ 1726954 ] XQuery: inserting (large) doc into other doc A bug was fixed when serializing comments. [ 1743433 ] XQ: problem with ampersand in attribute value [ 1786908 ] SQL+PF: loading both "sql_server" and "pathfinder" fails -- Sjoerd Mullender

1 0

[MonetDB-users] How to select random resords from MonetDB
by lonefs 31 Aug '07

31 Aug '07

How to select random resords from MonetDB? Thanks! lonefs 2007-08-30

2 2

[MonetDB-users] Announcement: MonetDB-SQL 2.18.2 Released
by Sjoerd Mullender 29 Aug '07

29 Aug '07

Today the MonetDB group at CWI have released a bug-fix release of the SQL backend. This release is version 2.18.2, and is now available at monetdb.sourceforge.net. The release consists of a source tar ball, a source RPM, and binaries for Fedora 7 (RPMs, for both i386 and x86_64) and for Windows (also for i386 and x86_64--created on Windows XP). -- Sjoerd Mullender

1 0

[MonetDB-users] monetdb5 and pathfinder?
by Oliver Oli 21 Aug '07

21 Aug '07

will pathfinder work on monetdb5, too?

2 1