May 2011 - users-list

[MonetDB-users] Does a primary key based on a single column + numeric improve performance in MonetDB?
by Philippe Hanrigou 20 May '11

20 May '11

Hi, I am designing the schema of our brand new MonetDB database, and I am not sure whether my previous data modeling instincts are still relevant ;-). In particular, I am wondering: A) Does the definition of primary keys / foreign keys improve MonetDB performance? B) I need to do joins with a table whose natural key which is the combination of (bigint, varchar(51)). Is it best to: 1) just add these 2 columns to the dependent tables, and join on these 2 columns. 2) create a … [View More]

1 0

[MonetDB-users] MonetDB support for Upserts / On duplicate key ...
by Philippe Hanrigou 20 May '11

20 May '11

Hi, I am trying to operationalize our MonetDB usage and need some advice. What is the best way to do upserts in MonetDB? (https://secure.wikimedia.org/wikipedia/en/wiki/Upsert) Our application stores its data in a reference database (not MonetDB) and need to export some of its (derived) data in MonetDB for our real-time analytics needs. This "population" of MonetDB can happen in a couple of ways: A) In "real-time", on a "one row at a time" basis, when the application detects that it … [View More]

1 0

Re: [MonetDB-users] MonetDB-11.3.1 on OpenBSD 4.9 i386 fails during make
by Michael Sioutis 20 May '11

20 May '11

It seems that in OpenBSD sys/sysctl.h requires sys/param.h (http://lists.gnu.org/archive/html/bug-autoconf/2005-08/msg00015.html) I've found this workaround, but I won't test it until tomorrow probably: http://lists.gnu.org/archive/html/bug-autoconf/2005-08/msg00015.html I also don't know exactly how to apply the above workaround (or any other) in the MonetDB Makefile(s ?). On Wed, May 4, 2011 at 6:27 PM, Michael Sioutis <sioutis(a)di.uoa.gr> wrote: > It seems that in OpenBSD sys/… [View More]sysctl.h requires sys/param.h > (http://lists.gnu.org/archive/html/bug-autoconf/2005-08/msg00015.html) > I've found this workaround, but I won't test it until tomorrow > probably: http://lists.gnu.org/archive/html/bug-autoconf/2005-08/msg00015.html > > I also don't know exactly how to apply the above workaround (or any > other) in the MonetDB Makefile(s ?). > > On Wed, May 4, 2011 at 6:20 PM, Michael Sioutis <papito.dit(a)gmail.com> wrote: >> I can't find any of HAVE_SYS_SYSCTL_H and KERN_PROC_PATHNAME defined >> in my config.log. >> Also, I have a warning about sysctl.h, which is in the >> configuration.log I posted earlier (my first post). >> >> The important part is this: >> configure: WARNING: sys/sysctl.h: present but cannot be compiled >> configure: WARNING: sys/sysctl.h: check for missing prerequisite headers? >> configure: WARNING: sys/sysctl.h: see the Autoconf documentation >> configure: WARNING: sys/sysctl.h: section "Present But Cannot Be Compiled" >> configure: WARNING: sys/sysctl.h: proceeding with the compiler's result >> configure: WARNING: ## ------------------------------- ## >> configure: WARNING: ## Report this to info(a)monetdb.org ## >> configure: WARNING: ## ------------------------------- ## >> >> On Wed, May 4, 2011 at 6:06 PM, Fabian Groffen <Fabian.Groffen(a)cwi.nl> wrote: >>> Thanks for the summarisation! >>> >>> On 04-05-2011 17:56:54 +0300, Michael Sioutis wrote: >>>> To sum it up.. >>>> >>>> add >>>> #include <pthread.h> >>>> #include <pthread_np.h> >>> >>> hmm, do you really need pthread_np.h? That seems like a non-standard >>> header to me. >>> >>>> and... >>>> 2011-05-04 17:44:29 MSG mydb[19795]: !MALException:malInclude:could >>>> not open file: mal_init >>> >>> This indicates some bigger problem... >>> >>>> 2011-05-04 17:44:29 MSG mydb[19795]: #warning: unable to determine binary location, please use --set monet_mod_path=/path/to/... to allow finding modules >>> >>> ... and this causes it. >>> >>> Can you tell me if HAVE_SYS_SYSCTL_H and KERN_PROC_PATHNAME are defined >>> for you? >>> common/utils/mutils.c is apparently unable to find the executable path >>> on OpenBSD. >>> Perhaps KERN_PROC_PATHNAME is not in sys/sysctl.h on OpenBSD? >>> >>> ------------------------------------------------------------------------------ >>> WhatsUp Gold - Download Free Network Management Software >>> The most intuitive, comprehensive, and cost-effective network >>> management toolset available today. Delivers lowest initial >>> acquisition cost and overall TCO of any competing solution. >>> http://p.sf.net/sfu/whatsupgold-sd >>> _______________________________________________ >>> MonetDB-users mailing list >>> MonetDB-users(a)lists.sourceforge.net >>> https://lists.sourceforge.net/lists/listinfo/monetdb-users >>> >> > [View Less]

5 45

[MonetDB-users] Where [on file] does MonetDB persists the dB?
by Me Anonimous 19 May '11

19 May '11

I uploaded a 1M records, 67M file but can't find anything in the directory indicating dB storage... Thanks!

2 1

[MonetDB-users] feature request (srid support )
by George Garmpis 19 May '11

19 May '11

Hi, I try to use geometries in different reference systems but i don't get the expected results. I create a table with geometries and insert 2 points with different SRID. sql>create table geom(id int, g geometry); operation successful sql>insert into geom values(1, GeomFromText('POINT(100 100)', 4326)); operation successful sql>insert into geom values(2, GeomFromText('POINT(100 100)', 27700)); operation successful When i ask for these SRID i get the same (0) for every point. sql… [View More]

1 0

[MonetDB-users] 'mkey.bulk_rotate_xor_hash' exception
by Steve Elder 18 May '11

18 May '11

With a very simple test, I get the 'mkey.bulk_rotate_xor_hash' exception. After this error, the only way I have found to recover is to drop the table, re-create and repopulate it. * System parameters: o MonetDB-11.3.1 o x86-64 ( note: build was missing a shared library that was subsequently ) o Solaris 10. o 96 GB memory * I create a simple table. create table fluid ( fluid bigint not null, userID int not null default 0, relativeUser bigint not null, typeID … [View More]tinyint default 127, updateTime timestamp(0) default now(), constraint fluid_pk primary key( fluid, userID ), constraint user_fk1 foreign key ( userID ) references fuser ( userID ) ); create index rel_user_idx1 on fluid ( relativeUser ); create index userID_idx2 on fluid ( userID ); create index rel_user_user_idx1 on fluid ( relativeUser, userID ); create index update_idx1 on fluid ( updateTime ); * Populate it with records ( Note: the number of records does not seem to matter ). * Delete records. It does not appear to matter how many records I delete. Deleting 1 record or deleting all records seems to have the same effect. * Drop the primary key * Do "set optimizer='nov2009_pipe';" * Add the primary key. I always get the mkey.bulk_rotate_xor_hash exception. Here is a snapshot including a trace of a successful 'alter table drop constraint ...' command after NO records have been deleted. When I delete 1 ( or more records ), I get the exception with no trace output. sql>alter table fluid drop constraint fluid_pk; operation successful sql>set optimizer='nov2009_pipe'; sql>trace alter table fluid add constraint fluid_pk primary key( fluid, userID ); operation successful +------+--------------------------------------------------------------------------------------------------------------+ | tick | stmt | : s : : +======+==============================================================================================================+ | 7 | _2 := sql.mvc(); | | 451 | sql.catalog(29,"FLDB",42302696,0); | | 32 | _16:bat[:oid,:lng] <tmp_4741501>[52] := sql.bind(_2=48362568,"FLDB","fluid","fluid",0); | | 22 | _13:bat[:oid,:int] <tmp_4741500>[52] := sql.bind(_2=48362568,"FLDB","fluid","userID",0); | | 15 | _10:bat[:oid,:oid] <tmp_4741364>[0] := sql.bind_dbat(_2=48362568,"FLDB","fluid",1); | | 10 | _12<tmpr_4741364>[0] := bat.reverse(_10=nil:bat[:oid,:oid]); | | 20 | _15<tmp_4741350>[52] := algebra.kdifference(_13=nil:bat[:oid,:int],_12=<tmpr_4741364>[0]); | | 22 | _17<tmp_4741012>[52] := algebra.kdifference(_16=nil:bat[:oid,:lng],_12=nil); | | 11 | _19:bat[:oid,:wrd] <tmp_4741265>[52] := batcalc.hash(_17=<tmp_4741012>[52]); | | 29 | _22<tmp_4741636>[52] := bat.mirror(_17=<tmp_4741012>[52]); | | 16 | _21:bat[:oid,:wrd] <tmp_4741417>[52] := mkey.bulk_rotate_xor_hash(_19=nil:bat[:oid,:wrd],22,_15=<tmp_4741350 | : : >[52]); : | 8 | _23<tmpr_4741636>[52] := bat.reverse(_22=nil); | | 52 | _24<tmp_4741265>[52] := algebra.join(_23=nil,_21=nil:bat[:oid,:wrd]); | | 15 | (ext60<tmp_4741417>[52],grp58<tmp_4741636>[52]) := group.new(_24=<tmp_4741265>[52]); | | 19 | (ext63<tmp_4741417>[52],grp61<tmp_4741636>[52]) := group.derive(ext60=nil,grp58=nil,_17=nil); | | 10 | (ext66<tmp_4741417>[52],grp64<tmp_4741636>[52]) := group.done(ext63=nil,grp61=nil,_15=nil); | | 9 | _31<tmp_4740027>[52] := bat.mirror(ext66=nil); | | 10 | _32:bat[:oid,:wrd] <tmp_4741417>[52] := aggr.count(grp64=nil,grp64=nil,_31=nil); | | 17 | return sum := aggr.sum(b=<tmp_4741417>[52],true); | | 44 | _33:wrd := aggr.sum(_32=<tmp_4741417>:bat[:oid,:wrd][52]); | | 5 | _34 := calc.isnil(_33=52:wrd); | | 7 | _37 := aggr.count(_32=nil:bat[:oid,:wrd]); | | 6 | _36 := calc.ifthenelse(_34=false,0:wrd,_33=52:wrd); | | 5 | _38 := calc.!=(_37=52:wrd,_36=52:wrd); | | 2827 | barrier _53 := language.dataflow(); | | 6 | sql.assert(_38=false,"UPDATE: PRIMARY KEY constraint 'fluid.fluid_pk' violated"); | | 27 | sql.update(_2=48362568,"FLDB","fluid","fluid_pk",_24=nil); | | 5 | sql.exportOperation(""); | | 3724 | user.s1_1(); | +------+--------------------------------------------------------------------------------------------------------------+ 29 tuples (50.262ms) sql>set optimizer='default_pipe'; sql>alter table fluid drop constraint fluid_pk; operation successful sql>delete from fluid where relativeUser = 11111; 1 affected row (1.557ms) sql>trace alter table fluid add constraint fluid_pk primary key( fluid, userID ); MALException:mkey.bulk_rotate_xor_hash:operation failed Operands not synced on head I need to periodically do bulk loads ( 'copy into' ), but also need to be able to periodically delete records from the same table. Is there a resolution to this problem? Thanks Steve [View Less]

2 4

[MonetDB-users] mclient credentials after install?
by Philippe Hanrigou 17 May '11

17 May '11

Hi, I am trying to get started with MonetDB in order to assess its response time for an in-house dataset. I managed to configure/make/install it on my MacBook Pro (I have joined the custom Portfile I used for this purpose at the end of this email in case it matters). Nevertheless, when I go through the "Getting started" instructions, mclient is asking for credentials that I never had a chance to explicitly set: ph7:~$ monetdbd start ph7:~$ monetdbd get all property value … [View More]hostname Solaris.local dbfarm /opt/local/var/monetdb5/dbfarm mserver /opt/local/bin/mserver5 logfile merovingian.log pidfile merovingian.pid sockdir /tmp port 50000 controlport 50001 discoveryport 50000 exittimeout 60 forward proxy discoveryttl 600 passphrase <unknown> mapisock /tmp/.s.monetdb.50000 controlsock /tmp/.s.merovingian.50001 ph7:~$ monetdb create demo created database in maintenance mode: demo ph7:~$ monetdb status name state uptime health last crash demo locked ph7:~$ monetdb release demo taken database out of maintenance mode: demo ph7:~$ monetdb status name state uptime health last crash demo stopped ph7:~$ mclient -d demo user(ph7): password: InvalidCredentialsException:checkCredentials:invalid credentials for user 'ph7' Which credentials I am supposed to use to launch mclient? I am willing to try any workaround as, at this point, I am not concerned with security at all. Thanks in advance, - Philippe --------------------------------------------------------- # ph7's custom Portfile # $Id PortSystem 1.0 name MonetDB version 11.3.1 categories databases platforms darwin maintainers nomaintainer description MonetDB is an open-source database system for high-performance applications in data mining, OLAP, GIS, XML Query, text and multimedia retrieval. MonetDB often achieves a significant speed improvement for SQL and XQuery over other open-source systems. homepage http://www.monetdb.org master_sites http://dev.monetdb.org/downloads/sources/Latest/ distfiles MonetDB-${version}.tar.bz2 use_bzip2 yes checksums md5 f48869c87a56604d1c06f7d8243554f9 \ sha1 03a29a5e00a90d5294443ffb849167a2c108302e \ rmd160 c9bcdff333a60aad42dc8504c458335494e749f1 configure.args --enable-debug=no --enable-assert=no --enable-optimize=yes [View Less]

1 0

[MonetDB-users] Problem with optimizer ?
by Georgios Garmpis 15 May '11

15 May '11

Hi, Please find attached a query (and corresponding schema) that results in mserver5 using more than 1GB of memory and terminating after 6 mins with this output: MAPI = (monetdb) /tmp/.s.monetdb.50000 ACTION= read_line QUERY = PLAN SELECT DISTINCT h3.obj, CASE WHEN u_ENDPOINT.value IS NOT NULL THEN u_ENDPOINT.value WHEN l_ENDPOINT.value IS NOT NULL THEN l_ENDPOINT.value WHEN ll_ENDPOINT.value IS NOT NULL THEN ll_ENDPOINT.value WHEN lu_ENDPOINT.… [View More]

4 10

[MonetDB-users] Repeated mmap/munmap calls
by Steve Elder 13 May '11

13 May '11

We are running the April '11 version of MonetDB on multi-core x86-64 hardware running Solaris 10 OS. We are running on a machine with 96GB of memory. The ulimit for memory shows 'unlimited'. We have an application that we believe should be appropriate for MonetDB fast response-time characteristics. We have a very simple schema that includes the following table: create table tuid ( TUID bigint not null, clientID int not null default 0, mafID bigint not null, addrMatch tinyint default 127, … [View More]lastUsed timestamp(0) default now(), constraint tuid_pk primary key( TUID, clientID ), constraint client_fk1 foreign key ( clientID ) references client ( clientID ) ); The above table currently contains 13.65 million records ( mostly bulk loaded via 'copy into' ). The primary key was dropped and re-added via appropriate 'alter table' statements. There are other 5 other tables in the schema - all with less than 100 records. All 5 tables have fewer than 5 columns. The total size of the database directory in the dbfarm directory is 1.3 GB. Ultimately, the application could have between 500 million and 1 billion records in the above table. Except for one of the other tables, the number of records will be small ( less than 1000 ). We are running a Java application that uses the JDBC interface to query and update the table. Files of 1-3 million records are processed by the application in a given run. Less than 5% of the records require an update to above table ( no other tables are currently updated or inserted to ). When the application is processing these records, we run 'prstat' against 'mserver5' and see via the 'prstat' command the processing load appears to be distributed fairly evenly across many threads. However, occasionally at some point, one thread appears to be using most of the CPU. During these intervals, 'truss' shows 'mserver5' making repeated calls to mmap and munmap. (The calls to mmap have the same value for the mmap size parameter.) These intervals appear to last for many minutes. During these intervals, query response times slow to a crawl. As a result, the overall throughput of the application is disappointing. Given that we are actually accessing a fairly small number of columns all of which should be in memory ( we believe ), it would appear that MonetDB is not functioning properly. It is our belief that MonetDB should be a good fit for this particular application. I should note that we have 3 different MonetDB databases running on the machine ( thus requiring 3 instances of mserver5 ). However, two of the databases are idle when we run the above application. Given the amount of memory available the size of the database, the number of columns being queries ( and/or updated ), and the amount of memory on the machine, the behavior we are observing does not appear to be consistent with descriptions of MonetDB's expected behavior. We have 3 questions: * Is what we are observing concerning the frequency of mmap and munmap calls typical where the mmap size parameter has the same value? * Why would we be mapping columns into memory so frequently when we are not adding records? * Is it typical to have periods where one thread appears to be handling the entire load? * What should we look for concerning the tuning of the x86-64 platform or MonetDB to address these issues? Thanks Steve [View Less]

1 0

[MonetDB-users] simple load?
by Zoltan Tomory 11 May '11

11 May '11

Sorry to be a bother, but I wonder how hard it is to load a schema or table from ascii text or an .mdb "access" file. I am using the convenient simple console client that is included. Upon searching the list archives, I found some leads which lead me to panic and reflexively consider using Java's Robot class to perform row by row insert operations from the text file. I'm hopeful that there is another way which is both elegant and understandable to a newbie. Thanks in advance. I have … [View More]

2 2