- users-list - monetdb.org

[MonetDB-users] Announcement: New Apr2011-SP1 bugfix release
by Fabian Groffen 31 May '11

31 May '11

The MonetDB team at CWI/MonetDB BV is pleased to announce the Apr2011-SP1 bugfix release of the MonetDB suite of programs. At the time of this release, we also officially changed to our new website at http://www.monetdb.org/. This new website is our central source of information, and comes with our new logo and style. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>.

1 0

[MonetDB-users] Syntax for column tables
by Michael K 25 May '11

25 May '11

Hi, is there a "CREATE COLUMN TABLE xyz" like statement in MonetDB or will every "CREATE TABLE" create a column table? Can I create "normal" row store tables in MonetDB at all? I have read http://monetdb.cwi.nl/SQL/Documentation/Table-Definition.htmlbut did not found the information I was looking for. Kind regards. michael

3 2

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

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

3 3

[MonetDB-users] using MonetDB in embedded mode - mapi interface
by Ramak 21 May '11

21 May '11

Hi, I am trying to use monetDB in embedded mode. The sample at "http://monetdb.cwi.nl/SQL/Documentation/A-Simple-Example.html#A-Simple-Exam…" suggests that we can interface to the data base using "embedded_sql()" method in combination with "embeddedclient.h". I dont seem to find this method in the library "libmapi.so.3"; neither the include file in the code base. Is "embedded mode" still supported? is there an alternate way of connecting to embedded monetDB server? PS: I am using monedDB5 - April 2011 release. Thanks, Ram -- View this message in context: http://old.nabble.com/using-MonetDB-in-embedded-mode---mapi-interface-tp316… Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[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 single varchar column that would be the concatenation of the bigint + varchar(51) and join on this single varchar column 3) Introduce a numeric+sequential surrogate key (presumably generated from a sequence), and join on the surrogate key. I am especially interested in the performance / acidental complexity tradeoffs in this situation. Thanks in advance, - Philippe

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 would be valuable to update the analytics information. B) Nightly in a batch oriented basis, to make sure that no information was lost due to a glitch/downtime during the day. C) We also periodically "refresh" all historical data in a batch oriented mode when we find bugs in the way we are deriving the data or what to derive new analytics from the same reference data. And whatever triggered the update, the rule is that the last update "wins" (and contains the full data). I am explaining this to illustrate why upserts would be very convenient. They would allow me to use the same code in all situations (A, B, C) and in "row" mode as well as in "batch" mode. If, as I suspect there is no out-of-the-box support for upserts, what is the best way to achieve them efficiently in batch mode. I am open to any (performant) solution, it does not have to be SQL based. Thanks in advance, - Philippe PS: Thanks a lot for MonetDB, I am completely amazed by its performance for our analytics queries.

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

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>select id, srid(g) from geom; +------+--------+ | id | srid_g | +======+=+ | 2 | 0 | | 1 | 0 | +------+--------+ Also, if i ask whether these points overlap i don't get the right result. sql>select equals(g1.g, g2.g) from geom g1, geom g2 where g1.id=1 and g2.id =2; +-----------------+ | overlaps_g | +========+ | true | +-----------------+ 1 tuple (4.119ms) I have noticed that function wkbFromText (in file geom/monetdb5/geom.mx), which is used in SQL functions GeomFromText, PointFromText e.t.c., returns a wkb datatype (defined in geom/lib/libgeom.mx) which has not any field for srid. Is srid stored somewhere else (e.g. as metadata of a geometry collumn)? Generally, does monetdb supports different reference systems? If no, will this feature be added ? -- George Garbis

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

2 4