August 2013 - users-list

Installation and configuration on Windows 8
by m- dev 22 Aug '13

22 Aug '13

I'm not familiar with database administration, but I guess I'm having similar problems to this Windows user: http://mail.monetdb.org/pipermail/users-list/2013-June/006078.html I installed DB5-SQL, Geom and ODBC 32 and 64bit, default options for all. After that it's not clear to me which steps are for Linux or Windows, or both. Or how to set up the configuration file. In Windows 8 I start 'MonetDB SQL Server'. I can also start 'MonetDB SQL Client'. i) I added PATH in Environment variable to the installation folder (that defaults to 32-bit Program files): ;C:\Program Files\MonetDB\MonetDB5 But I couldn't set up a configuration file, either from within the Server: > cat ~/.monetdb SyntaxException:parseError:cat ~/.monetdb SyntaxException:parseError: ^,';' expected Or the Client: sql>cat ~/.monetdb more>name=#### more>password=######### more>language=SQL more> Creating that as a text file renamed config.monetdb in the installation folder doesn't work either. ii) I've extracted the voc_dump.sql file, but the Guide doesn't specify the location of mclient.exe and when I found it in C:\Program Files\MonetDB\MonetDB5\bin but it won't open because: "libmapi.dll is missing...Try reinstalling the program to fix this problem" Clearly I haven't set it up MonetDB correctly, I can only guess, but what are the correct steps? Is there any other guide or tutorial on installation and configuration on Windows 8? Thanks, Mark

2 2

SELECT DISTINCT on datatype inet causes high load even after cancelling
by Ralph Holz 22 Aug '13

22 Aug '13

Good day, We're experiencing a phenomenon that I can't quite explain. We have a table of maybe 110M entries mapping IP addresses to some values. The data type of field ip_addr is net. No indices or constraints set. A SELECT COUNT(ip) is zippy - result in some ms. Any SELECT, in fact, that does not use WHERE is fast. E.g.: SELECT ip, result FROM iptable LIMIT 10000; +------------------ 10000 tuples (4.6s) However: A SELECT COUNT(DISTINCT ip) does not return even after waiting an hour or, just to try it out once, 24 hrs. Load remained at 27 during that time. I restarted the server after that, as I had done ctrl-c in mclient, and that seems to let the current query continue? What would be the right way to cancel a query? I then tried SELECT ip FROM iptable WHERE ip = inet '1.0.0.1' That IP is the second in the column. The query did not return even after 10 minutes. That seems a bit excessive. Does anyone have a clue why this is so? Should I avoid inet? Thanks, Ralph Disclaimer: I come from a psql background and these are my first steps with vertical-col data stores. Server: MonetDB 5 server v11.15.11 "Feb2013-SP3" (64-bit, 64-bit oids) Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2013 MonetDB B.V., all rights reserved Visit http://www.monetdb.org/ for further information Found 142.0GiB available memory, 24 available cpu cores Libraries: libpcre: 8.30 2012-02-04 (compiled with 8.02) openssl: OpenSSL 0.9.8o 01 Jun 2010 (compiled with OpenSSL 0.9.8o 01 Jun 2010) libxml2: 2.7.8 (compiled with 2.7.8) Compiled by: root(a)dev.monetdb.org (x86_64-pc-linux-gnu) Compilation: gcc -O3 -fomit-frame-pointer -pipe -Wp,-D_FORTIFY_SOURCE=2 Linking : /usr/bin/ld -m elf_x86_64 -- Ralph Holz I8 - Network Architectures and Services Technische Universität München http://www.net.in.tum.de/de/mitarbeiter/holz/ Phone +49.89.289.18043 PGP: A805 D19C E23E 6BBB E0C4 86DC 520E 0C83 69B0 03EF

1 0

MonetDB OOM crash in CentOS
by Ravikumar Balasubramaniam 20 Aug '13

20 Aug '13

Hi, MonetDB is crashing because of OOM. I have tried following config update but in vain. sysctl -w vm.overcommit_memory=2 sysctl -w vm.overcommit_ratio=75 Is there any configuration to limit mserver memory usage? Database contains 300 million records and the select query expects 120,620 records as result. This is the log message: Out of memory: Kill process 30650 (mserver5) score 981 or sacrifice child Killed process 30650, UID 222, (mserver5) total-vm:35263192kB, anon-rss:14911384kB, file-rss:208kB *mserver5 version*: MonetDB 5 server v11.15.11 "Feb2013-SP3" (64-bit, 64-bit oids) Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2013 MonetDB B.V., all rights reserved Visit http://www.monetdb.org/ for further information Found 14.5GiB available memory, 4 available cpu cores Libraries: libpcre: 7.8 2008-09-05 (compiled with 7.8) openssl: OpenSSL 1.0.0 29 Mar 2010 (compiled with OpenSSL 1.0.0-fips 29 Mar 2010) libxml2: 2.7.6 (compiled with 2.7.6) Compiled by: ec2-user@ip-10-252-152-159 (x86_64-unknown-linux-gnu) Compilation: gcc -g -O2 Linking : /usr/bin/ld -m elf_x86_64 *Filesystem Size Used Avail Use% Mounted on* /dev/xvde1 5.0G 2.4G 2.4G 50% / tmpfs 7.3G 0 7.3G 0% /dev/shm /dev/xvdf 414G 209G 184G 54% /media/ephemeral0 (dbfarm) Regards, Ravi

1 0

MonetDB cluster management in EC2
by Ravikumar Balasubramaniam 20 Aug '13

20 Aug '13

I have followed http://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/ClusterManagement… to setup 3 node(in amazon EC2) cluster. I have considered the server from where I control remote databases as leader and others as remote servers. The 'monetdb discover' in the leader is not showing any other database(except self dbs). I have enabled discovery and control properties in the remote servers. What other configurations are needed for successful discovery in EC2 environment? MonetDB cluster enables mclient to combine all the (select)query results from remote servers and itself. The application should take care of shared write into nodes. Is this fair understanding? Regards, Ravi

2 2

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