- users-list - monetdb.org

MonetDB and Initial Memory requirements of database startup
by Ashish Kumar Singh 20 Aug '13

20 Aug '13

Hi All, Is there any check in monetdb at startup time which will ascertain that for a dataset of size x mb in hard disk how much minimum free memory should be available in system so that monetdb can run successfully. If so please point me to relevant section. Thanks! Regards, Ashish

2 2

change variables within select statement
by Christian Kleinke 19 Aug '13

19 Aug '13

Dear all, is there a possibility to change declared variables in monetdb within a select statement. For example in MySQL the variables can be declared with the @-symbol, and such variables easily could be changed in a case-when condition. Here a little example of what I want to do (in MySQL syntax): SET @id = ''; SET @pos = 1; drop table if exists test_table; create table test_table as select *, if (@id<> id, @pos:=1, @pos:=@pos+1) as position_number, @id:=id, from table_with_ids; Here the rows with identical ids should be counted, and if the id changes the position_number should start again as zero. If somebody has a hint for me, to execute such a query in monetdb, I would be very glad. Greetings, Christian

3 3

Fwd: MonetDB pip python3
by Martin Kersten 18 Aug '13

18 Aug '13

-------- Original Message -------- Subject: MonetDB pip python3 Date: Sat, 17 Aug 2013 20:21:51 +0200 From: Christian Tismer <tismer(a)stackless.com> To: Martin Kersten <Martin.Kersten(a)cwi.nl> Hi Martin, by chance I came along MonetDB today, and I think this can be the solution to my database problems. (used zodb and durus, but there are way too many columns and rows to handle with these). I am trying to use MonetDB with python 3.3. Unfortunately, the pip installer sees only the python 2 version. I had a look into the repository - the python 3 version is there. I saw you active right now, so I'm asking: Should I clone the repo and install the py3.3 version from there, or is it planned to add this to pip, soon? (I can work on this if it makes sense) cheers - Chris (that Stackless guy...) -- Christian Tismer :^) <mailto:tismer@stackless.com> Software Consulting : Have a break! Take a ride on Python's Karl-Liebknecht-Str. 121 : *Starship* http://starship.python.net/ 14482 Potsdam : PGP key -> http://pgp.uni-mainz.de phone +49 173 24 18 776 fax +49 (30) 700143-0023 PGP 0x57F3BF04 9064 F4E1 D754 C2FF 1619 305B C09C 5A3B 57F3 BF04 whom do you want to sponsor today? http://www.stackless.com/

2 1

b and g must be aligned
by Miguel Ping 16 Aug '13

16 Aug '13

Hi, I'm resurrecting this since I've been out of town and only today I got a chance to investigate further. I've recompiled with -O0 to prevent optimizations from "hiding" the values, and in the debugger I got this: b->U->count BUN 764314 g->U->count BUN 17 b->H->seq oid 0 g->H->seq oid 0 b->H->dense unsigned int 1 g->H->dense unsigned int 1 The stack call is as follows: BAThllaggr() at udf.c AGGRsubhllaggcand() at udf.c AGGRsubhllagg() at udf.c malCommandCall() at mal_interpreter.c runMALsequence() at mal_interpreter.c DFLOWworker() at mal_dataflow.c start_thread() at pthread_create.c clone() at clone.S 0x0 -------- Original Message -------- Subject: Re: b and g must be aligned Date: Fri, 26 Jul 2013 11:34:53 +0100 From: Sjoerd Mullender <sjoerd(a)acm.org> Reply-To: Communication channel for MonetDB users <users-list(a)monetdb.org> To: Communication channel for MonetDB users <users-list(a)monetdb.org> -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 2013-07-26 12:23, Miguel Ping wrote: > On 07/25/2013 01:51 PM, Sjoerd Mullender wrote: On 2013-07-25 > 14:04, Miguel Ping wrote: >>>> Hi all, >>>> >>>> We're hitting this error "b and g must be aligned". I tracked >>>> the src to a commit about some alignment code thing in >>>> gdk_calc: >>>>http://www.mail-archive.com/checkin-list@monetdb.org/msg09731.html >>>> >>>> (Fix alignment conversion in compatibility code for grouped >>>> aggregates.) >>>> >>>> Can you guys please explain what's the reason behind this >>>> error? I can't understand by just looking to the src of >>>> gdk_calc.c >>>> >>>> Thanks! > When using grouped aggregates, the grouping bat must be aligned > with the value bat. The value bat is b and contains the values you > want to aggregate. The group bat is g and contains for each value > in b the group (an oid) it belongs to. Equal group ids means the > same group. These bats must be aligned, because we need to know for > each value in b to which group it belongs. Aligned means: same > length, and same head column values. The head columns must be > dense (a sequence of numbers starting at some value, and each next > value exactly one larger than the previous). Dense sequences are > usually not stored explicitly in MonetDB. We only store the first > value in the hseqbase field. So the hseqbase fields of b and g > must be equal. The one exception to this is when the bats are both > empty. This last exception is the change to gdk_calc.c in that > changeset. > > -- Sjoerd Mullender >> _______________________________________________ users-list >> mailing listusers-list(a)monetdb.org >>http://mail.monetdb.org/mailman/listinfo/users-list >> >> > Thanks for your explanation. I still don't understand how can > there be a misalignment. I would expect MonetDB to feed my hll > aggregate functions with the correct values. Can it be that I may > have some NULL values and the validation is failing because of > that? If this happens when running a SQL query, it's a bug. I don't think NULLs have anything to do with it. NULL values are stored in-line. You might want to look at b->U->count, g->U->count, b->H->seq, g->H->seq, b->H->dense, g->H->dense when the misalignment happens (either in the debugger or by using printf--but realize that count and seq are not int, so %d is not going to work). Also things like the MAL plan (prepend SQL query with EXPLAIN) and the stack trace might be useful. - -- Sjoerd Mullender -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.13 (GNU/Linux) Comment: Using GnuPG with Thunderbird -http://www.enigmail.net/ iQCVAwUBUfJQyT7g04AjvIQpAQK/JAP9HCp/aFaYWv0jodfPUnSRVgFSsdjTn/VL ttSsmAF+yomGMDIne2311f/D51F3/nte7Utx+01lgvArapWErhjGN1hzPSr5LQbs PZ6dUfNcH8Rt2AtT3uSxfkFZy9VRDCNNXPei43IgMS2HxVZ48pnAVkNcpBbW3Gms GwdvU7bSZtM= =2zSg -----END PGP SIGNATURE----- _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

5 20

monetdb lost column info when queryring
by integrity 16 Aug '13

16 Aug '13

some of the column "clientport" is NULL, but if change to another query method, it appeared again. 2. another bug: sql>select * from t_ivr_result limit 1; could not allocate space for HEAPextend: failed to extend to 15096348672 for 02/04/20413.theap another time: ERROR: HEAPextend: failed to extend to 23460839424 for 02/21/22122.theap it seems monetdb applied a heap of 15G ,the RAM is 64G, root@10.187.22.175:~# free -g total used free shared buffers cached Mem: 62 45 16 0 0 43 -/+ buffers/cache: 2 60 Swap: 2 0 2 so the left memory is 16G, but failed to applied 15G heap. and the table t_ivr_result is only 3G, there is no need to apply such a large heap. sql>select count(*) from t_ivr_result; +----------+ | L1 | +==========+ | 40299239 | +----------+ 1 tuple (137.023ms) sometimes the heap is 24G: ERROR: MALException:mat.pack:could not allocate space for ERROR: HEAPextend: failed to extend to 23460839424 for 02/21/22122.theap Thanks, Meng

2 2

Timestamp truncation in MonetDB
by Ravikumar Balasubramaniam 13 Aug '13

13 Aug '13

Hi, I'm trying to truncate timestamp value to hour and/or minute precision. I combined cast and extract functions as: Problem: Truncate '2013-08-12 23:59:59' to '2013-08-12 23:00:00' select cast(timestamp '2013-08-12 23:59:59' as date)||' '||extract(hour from timestamp '2013-08-12 23:59:59')||':00:00'; Output: 2013-08-12 23:00:00 But this is expensive because of repeated function calls. Is there any faster and built-in function? Regards, Ravi

2 1

Count number of records returned by a subquery in monetdb
by Ravikumar Balasubramaniam 13 Aug '13

13 Aug '13

Hi, I try to execute a query like following: When I execute, select table_name from system order by table_name; no syntax error. But when I execute, select count(*) from (select table_name from system order by table_name) as count; I get syntax error, unexpected ORDER, expecting INTERSECT or EXCEPT or UNION or ')' in: "select count(*) from (select table_name from system order" What is the correct syntax? Regards, Ravi

3 2

Integrating a C++ module in MonetDB
by Adnan Agbaria 13 Aug '13

13 Aug '13

Hi All, I've implemented a module implemented in C++ for MonetDB. You can think that this module has a user-defined function. What changes should be made in Makefile.ag (of this module) to compile and build it with C++ compiler. Regards!

2 1

potential data contention in _list_find_name() during BAT binding?
by Mengmeng Chen 13 Aug '13

13 Aug '13

Hi, I'm reading the source code of MonetDB and I have a question about multi-threaded BAT binding and would appreciate it if someone could help to answer. Here is my question: When a sql_bind() MAL instruction is called, I see we have a callstack something like: mvc_bind_wrap -> mvc_bind-> mvc_bind_column -> find_sql_column -> _cs_find_name->_list_find_name In _list_find_name, we have something like: if (l && !l->ht && list_length(l) > HASH_MIN_SIZE && l->sa) { l->ht = hash_new(l->sa, list_length(l), (fkeyvalue)&base_key); for (n = l->h; n; n = n->next ) { sql_base *b = n->data; int key = base_key(b); hash_add(l->ht, key, b); } } if (l && l->ht) { int key = hash_key(name); sql_hash_e *he = l->ht->buckets[key&(l->ht->size-1)]; for (; he; he = he->chain) { sql_base *b = he->value; if (b->name && strcmp(b->name, name) == 0) return b; } return NULL; } if (l) for (n = l->h; n; n = n->next) { sql_base *b = n->data; /* check if names match */ if (name[0] == b->name[0] && strcmp(name, b->name) == 0) { return b; } } Looks like the function is trying to create a hash table for the list if called the first time. However, there is no protection against data contention. Now suppose the mitosis optimizer is turned on and we could have something like: X_157:bat[:oid,:oid] := sql.tid(X_4,"sys","lineitem",0,8); X_159:bat[:oid,:oid] := sql.tid(X_4,"sys","lineitem",1,8); X_161:bat[:oid,:oid] := sql.tid(X_4,"sys","lineitem",2,8); X_163:bat[:oid,:oid] := sql.tid(X_4,"sys","lineitem",3,8); X_165:bat[:oid,:oid] := sql.tid(X_4,"sys","lineitem",4,8); X_167:bat[:oid,:oid] := sql.tid(X_4,"sys","lineitem",5,8); X_169:bat[:oid,:oid] := sql.tid(X_4,"sys","lineitem",6,8); X_171:bat[:oid,:oid] := sql.tid(X_4,"sys","lineitem",7,8); If my understanding is correct, since these MAL instructions has no dependency in each other, they could potentially be executed in different threads simultaneously. How would MonetDB prevent data contention in _list_find_name()? Particularly the case that if thread A has created a hash table and has not yet inserted the elements while thread B tries to search through the list and thinks the hash table is ready and starts to use it. It will fail to return the correct column since the hash table is empty, right? Thanks, Mengmeng

2 3

MonetDB and Tableau
by Dimitrios Gyalistras 12 Aug '13

12 Aug '13

Hello I am new to MonetDB and I want to use it together with the "Tableau Desktop" (Version 8) software running on a Mac, later possibly also together with a Tableau Server. However, the first attempts are discouraging: when attempting to connect to a MonetDB database, the Tableau Desktop software seems to get stuck in an endless loop. Some details: I run "MonetDB-11.15.11-i686-Darwin-9" on a Mac with OS X 10.6.8. I was able to create my first MonetDB database under OS X and define a simple 10-rows test table. The Tableau software runs under Windows 7 within Parallels Desktop 8. I installed the "2013-06-19 18:20 2.4M MonetDB ODBC driver, 32 bit" under Windows, and used the Administrative Tool "Data Sources (ODBC)" provided by Windows to configure the MonetDB DSN. In Tableau I finally selected Connect to Data/Other data bases (ODBC) and I was able to select a Table. Then I pressed OK – and Tableau got stuck in the process. I also tried using a custom SQL-query (SELECT * FROM "TestTable"). Here Tableau was able to establish a live connection to the MonetDB, but when working with the table, i.e. when dragging dimensions and measures into the rows and columns shelfs, Tableau announced connection errors. Does it make sense to proceed with this project? Any infos on the following configurations would be highly appreciated: MonetDB on a Mac <--> Tableau Desktop on a Mac MonetDB on a Linux server <--> Tableau Desktop on a Mac MonetDB on a Linux server <--> Tableau Server (Windows) Thanks Dimitrios –––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––– Dimitrios Gyalistras Synergy BTC AG Buristrasse 49 CH–3006 Bern Switzerland Tel: +41 78 602 54 09 Mail: gyalistras(a)synergy.ch Web: www.synergy.ch ––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––

1 0