- users-list - monetdb.org

Server - location of tmp database objects
by Radovan Bičiště 27 Aug '13

27 Aug '13

Hello, I was wondering if there is a way to specify for database where to put tmp files? For example when we use temporary tables etc. it is filling our data disk. I have another disk on the machine that I would like to use for temporary tables etc. Thank you for any hints. Best regards, Radovan -- __________________________ Radovan Bičiště ceos data s.r.o. třída SNP 402/48 500 03 Hradec Králové Czech Republic mobil CZ: +420 601 563 014 skype: rbiciste

4 3

Technical Problem connecting with database
by Hancel Gonzalez 27 Aug '13

27 Aug '13

Hi, i have a problem: i installed *monetdb 11.9.7 *from source code and created the database farm and the database with commands *monetdbd *and *monetdb, *respectively. The problem is when i try connect to the database (*mclient*) and dont work, see the log file but i dont how to solve. Please, anyone can help me to solve? This is the message in log file: *2013-08-26 21:48:41 MSG merovingian[7622]: starting database 'Berlin10MDB', up min/avg/max: 0s/0s/0s, crash average: 0.00 0.00 0.00 (0-0=0)* * * *2013-08-26 21:48:41 MSG Berlin10MDB[7668]: arguments: /usr/local/bin/mserver5 --set gdk_dbfarm=/home/gdesimone/monet_data --dbname=Berlin10MDB --set merovingian_uri=mapi:monetdb://Athena:50000/Berlin10MDB --set mapi_open=false --set mapi_port=0 --set mapi_usock=/home/gdesimone/monet_data/Berlin10MDB/.mapi.sock --set monet_vault_key=/home/gdesimone/monet_data/Berlin10MDB/.vaultkey --set gdk_nr_threads=4 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes* * * *2013-08-26 21:48:41 MSG merovingian[7622]: database 'Berlin10MDB' (7668) has exited with exit status 127* * * *2013-08-26 21:48:41 ERR merovingian[7622]: client error: database 'Berlin10MDB' appears to shut itself down after starting, check monetdbd's logfile for possible hints* * * Many thanks! Regards, Hancel.

3 5

Database Problem
by Thomas Johann 23 Aug '13

23 Aug '13

Dear all, I’m new to monetdb database programming and have a problem setting up my first database which I can not explain by myself: I’m basically running the following Python code: tempdir = 'E:\\xetra\\tempdir\\monetdb' connection = monetdb.sql.connect(username="monetdb", password="monetdb", hostname="localhost", database="database") # expected table layout connection.execute("CREATE TABLE XXX(Name_1 TEXT, Name_2 TEXT, Name_3 TEXT, Name_4 TEXT, Num_1 INT, Num_2 REAL,Num_3 INT,Name_5 CHARACTER(1))") def convert_table(filename,path): print("Reading CSV") try: start = time.clock() csv_to_sql = path + "\\" + filename connection.execute("COPY 100000000 OFFSET 2 RECORDS INTO XXX FROM '" + csv_to_sql + "' USING DELIMITERS ';','\n' ;") elapsed = (time.clock() - start) connection.commit() print("Time to write to database: " + str(elapsed) + " seconds") except: #get wrong transaction out of DB with rollback connection.rollback() try: #DB conversion start = time.clock() infile = open(csv_to_sql, 'rb') csv_to_sql_converted=path+ '\\converted.csv' outfile = open(csv_to_sql_converted, 'wb') BLOCKSIZE = 100000000 # experiment with size while True: block = infile.read(BLOCKSIZE) if not block: break outfile.write(block.decode('cp1252','strict').encode('utf-8','strict')) infile.close() outfile.close() elapsed = (time.clock() - start) print("Time to convert file to UTF-8: " + str(elapsed) + " seconds") #write to DB start = time.clock() connection.execute("COPY 100000000 OFFSET 2 RECORDS INTO XXX FROM '" + csv_to_sql_converted + "' USING DELIMITERS ';','\n' ;") connection.commit() os.remove(csv_to_sql_converted) elapsed = (time.clock() - start) print("Time to write to database: " + str(elapsed) + " seconds") except Exception as inst: #get wrong transaction out of DB with rollback connection.rollback() print type(inst) # the exception instance print inst.args # arguments stored in .args print inst # __str__ allows args to printed directly print("Could not write file "+csv_to_sql+"to the database XXX") filename_list = [“XYZ.csv”, “ABC.csv”,…] path = “C:\\” for filename in filename_list: convert_table(filename, path) I had to alter the code as the actual csvs contain very sensitive data, which I am not allowed to publish. But the structure is the same. I do not get any error message and the program runs through properly. Usually it goes through the “try:” part of the function. When I look into the resulting database the number of lines is as expected (so the observations must be somewhere in the database) but there are some gaps in the output, meaning that some values from the .csvs are just not copied and I am unable to compare those gap-lines to the original data as the identifier is usually among the missing variables. Some variables from the original data are just missing. This happens (at least to what I can say) in an unsystematic way. I have the impression that MonetDB might just be overwhelmed by the amount of data. Is this possible? The .csvs have each a size of 1.3GB and I’m reading more than 1000 of them into the database which has a final size of about 2 TB. I’m running this under Windows Server 2008 R2, 32GB RAM, AMD Opteron 4234 6-Core 3.10 GHz, using Anaconda. When I manually read in one single file, that made problems beforehand, I will find it in the database without error. So, I’m pretty puzzled what is going on here. Might it happen that the connection.execute-command runs through without triggering an error message but still not reading the whole dataset? I would be very glad about any help from the community. Thanks, Thomas

2 2

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

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

COPY INTO with ordered column
by Sebastian Dorok 09 Aug '13

09 Aug '13

Hi all, I have some slow queries that I want to accelerate by utilizing information about sorting. My table definition: create table genomics.grch36 (site bigint, base char(1)); I use COPY INTO to populate the table. The source file size is 40GB and contains more than 3 billion rows. I can guarantee that the data in file is ordered by site. I query the data like this: sql>select base from genomics.grch36 where site between 10000 and 10010; +------+ | base | +======+ | N | | T | | A | | A | | C | | C | | C | | T | | A | | A | | C | +------+ 11 tuples (5m 23s) I think 5 minutes seem too much for this query. Primary key or an index on 'site' don't work or at least aren't recognized in query execution. I think MonetDB would benefit when knowing that the data is ordered by site. Wouldn't it? Because I know that the data is ordered by 'site', how can I tell MonetDB about this fact to speed up the query? Any suggestions? Regards, Sebastian

2 6

Monetdb performance improvement with more RAM
by Anshuman Kumar 08 Aug '13

08 Aug '13

Hello All, We are evaluating monetdb for use as a tick database. We are storing all the data into a single table. Our table has a column for timestamp and a few other fields. We wish to run queries against it, the most useful of which is to return all entries within a given timestamp range. This query takes a long time to execute for even a 15 minute interval (about 40 to 60 seconds). We have noted that the system RAM usage goes up to the maximum (almost 8GB) while we run such queries on monetdb. We wanted to understand whether installing more RAM (say, we upgrade to 16 GB) will result in a significant performance improvement for such queries on monetdb. Also, if this is the case, we would like to know, how much RAM is monetdb optimised for? The following is the specification of our server: Intel Xeon X3430 2.4Ghz 64Bit 8Gb Ram 500Gb HDD - Raid0 Running on Windows Server 2008 R2. We are using the 20130212 build of monetdb. Thank you, Anshuman

2 1

Local, searchable mail archive
by Brandon Jackson 08 Aug '13

08 Aug '13

Would anyone be able to recommend an approach or software on either Windows or Linux that would enable me to replicate the MonetDB mail archives to my local machine to make it easer to search for answers and study topics before posting questions asked before. Even if I could get an export of the whole thing in some format that be consumed by a mail client. I did read that mailman is capable of such archives, but it seems to require a real administrative user and password to create the archive. I wouldn't expect a frequent completely up to date copy of the archive, just a backup that is closer to now and stays reasonably up to date (few months) Thanks for any suggestions. Brandon Sent from my iPhone

1 0

Re:monetdb lost column info when queryring
by integrity 08 Aug '13

08 Aug '13

sql>select * from kbms_logserver_msg t where command = 'CmdWxInfo' and logdate = '2013-08-06' and functionret <> '0' and servertime > '1375757230' limit 10; +------------+-----------+--------------+-------------+----------+------------+---------------+------------+----------------+------------+------------+ | logdate | command | commandtype | functionret | costtime | servertime | serverip | serverport | clientip | clientport | clienttype | +============+===========+==============+=============+==========+============+===============+============+================+============+============+ | 2013-08-06 | CmdWxInfo | getwxinfoall | -535 | 3032 | 1375757231 | 10.187.143.80 | 7000 | 10.153.130.228 | | | | 2013-08-06 | CmdWxInfo | getwxinfoall | -535 | 3032 | 1375757244 | 10.187.143.80 | 7000 | 10.153.130.228 | | | | 2013-08-06 | CmdWxInfo | getwxinfoall | -535 | 3030 | 1375757257 | 10.187.143.80 | 7000 | 10.153.130.229 | | | | 2013-08-06 | CmdWxInfo | getwxinfoall | -535 | 3032 | 1375757962 | 10.187.143.80 | 7000 | 10.153.130.229 | | | | 2013-08-06 | CmdWxInfo | getwxinfoall | -535 | 3030 | 1375757970 | 10.187.143.80 | 7000 | 10.153.130.228 | | | | 2013-08-06 | CmdWxInfo | getwxinfoall | -535 | 60 | 1375760693 | 10.187.143.80 | 7000 | 10.153.130.229 | | | | 2013-08-06 | CmdWxInfo | getwxinfoall | -535 | 60 | 1375760693 | 10.187.143.80 | 7000 | 10.153.130.228 | | | | 2013-08-06 | CmdWxInfo | getwxinfoall | -535 | 59 | 1375760693 | 10.187.143.80 | 7000 | 10.153.130.229 | | | | 2013-08-06 | CmdWxInfo | getwxinfoall | -535 | 63 | 1375760872 | 10.187.143.80 | 7000 | 10.153.130.229 | | | | 2013-08-06 | CmdWxInfo | getwxinfoall | -535 | 54 | 1375760872 | 10.187.143.80 | 7000 | 10.153.130.229 | | | +------------+-----------+--------------+-------------+----------+------------+---------------+------------+----------------+------------+------------+ 10 tuples (55.709ms) sql>select * from kbms_logserver_msg t where command = 'CmdWxInfo' and logdate = '2013-08-06' and functionret <> '0' and servertime = '1375757231' limit 10; +------------+-----------+--------------+-------------+----------+------------+---------------+------------+----------------+------------+------------+ | logdate | command | commandtype | functionret | costtime | servertime | serverip | serverport | clientip | clientport | clienttype | +============+===========+==============+=============+==========+============+===============+============+================+============+============+ | 2013-08-06 | CmdWxInfo | getwxinfoall | -535 | 3032 | 1375757231 | 10.187.143.80 | 7000 | 10.153.130.228 | 44328 | | +------------+-----------+--------------+-------------+----------+------------+---------------+------------+----------------+------------+------------+ 1 tuple (52.761ms) in the first query,when servertime > '1375757230' limit 10;clientport are all NULL. while in the second query, servertime = '1375757231' ,clientport appeard again. it proves that the clientport exist but just didn't appear in the query. Thanks, Meng

4 5

Determining Storage Consumption
by Sebastian Dorok 08 Aug '13

08 Aug '13

Hi all, I'm trying to figure out how much storage MonetDB (Feb2013-SP2) uses when populated with my data on a fresh database. Here is my table definition: create table genomics.grch36 (site bigint, base char(1)); I'm populating the table via COPY INTO. The source file size is 40GB and contains more than 3 billion rows. Following the suggestions on http://www.monetdb.org/Documentation/Userguide/diskspace, I get following result for the SQL query: sql>select * from storage() where "table" = 'grch36'; +----------+--------+--------+--------+----------+------------+-----------+-------------+----------+---------+--------+ | schema | table | column | type | location | count | typewidth | columnsize | heapsize | indices | sorted | +==========+========+========+========+==========+============+===========+=============+==========+=========+========+ | genomics | grch36 | site | bigint | 12/1252 | 3137459420 | 8 | 25099675360 | 0 | 0 | false | | genomics | grch36 | base | char | 12/1247 | 3137459420 | 1 | 6274918840 | 131072 | 0 | false | +----------+--------+--------+--------+----------+------------+-----------+-------------+----------+---------+--------+ I assume that 'columnsize' refers to the storage demand. I'm I right? Based on the numbers given, I assume a storage need of round about 31 GB (without any compression). Calling 'du -h' on my database directory confirms this assumption. 4,0K ./bat/14 44K ./bat/10 108K ./bat/07 476K ./bat/01 30G ./bat/12 256K ./bat/02 4,0K ./bat/03 4,0K ./bat/HC 4,0K ./bat/16 4,0K ./bat/17 12K ./bat/13 4,0K ./bat/LEFTOVERS 28K ./bat/BACKUP 4,0K ./bat/15 4,0K ./bat/11 12K ./bat/04 30G ./bat 8,0K ./sql_logs/sql 12K ./sql_logs 4,0K ./box 30G . But why is the 'columnsize' of the 'base' column something about 6GB whereas 3 billion times 1 Byte (for a 1 char) is 3GB? For the 'site' column this calculation works! Another question that comes into my mind: The number of distinct values in the 'base' column is 5. Thus, assuming a ideal dictionary encoding 3 Bit would be sufficient to encode all values. Since MonetDB uses dictionary compression, can it utilize this fact? Regards, Sebastian

1 0

SQL - syntax error in DELETE ALL FROM statement
by Radovan Bičiště 08 Aug '13

08 Aug '13

Hi, It is me again. :( I'm trying to "truncate" according to http://www.monetdb.org/Documentation/SQLreference/Updates using DELETE ALL FROM statement. But what I get is syntax error. :( ceos@lux-analytics:~$ mclient -h localhost -d zkdev1 -u drmaxtst -p 50000 password: Welcome to mclient, the MonetDB/SQL interactive terminal (Feb2013-SP3) Database: MonetDB v11.15.11 (Feb2013-SP3), 'mapi:monetdb://lux-analytics:50000/zkdev1' Type \q to quit, \? for a list of available commands auto commit mode: on sql>delete all from crmkarta; syntax error, unexpected ALL, expecting FROM in: "delete all" sql> Thank you for any hints. Best regards, Radovan -- __________________________ Radovan Bičiště

3 5

rpm for monetdb
by Sanjoy Bose 07 Aug '13

07 Aug '13

Hi, I am new to monetdb and considering using it for our Analytics need. Is there any rpm available for Centos 5 / Centos 6 RedHat 5 / Redhat 6 for its latest release or do I have to build from source ? Thanks, Sanjoy

4 6

SQL - temporary table data inside stored procedure
by Radovan Bičiště 07 Aug '13

07 Aug '13

Hello, I must be doing something horribly wrong. I have a stored procedure where I have temporary table declared, something like: DECLARE TABLE t1tmp (id bigint); Then in the same procedure I do INSERT INTO t1tmp SELECT .....; When I do Count(*) from temporary table in the procedure it returns 0. Fun starts after procedure called. When call is done temporary table is full of data as expected. But it seems the data is nowhere to be found when I look for them inside the procedure. Am I missing something again? Thanks for any hints, Radovan TEST CASE: CREATE PROCEDURE test1() BEGIN DECLARE TABLE t1tmp (bigint); INSERT INTO t1tmp SELECT id FROM t1; DECLARE v_cont BIGINT; -- returns 0 SELECT COUNT(*) INTO v_count FROM t1tmp; END; CALL test1; -- actually returns the data SELECT * FROM t1tmp; -- __________________________ Radovan Bičiště ceos data s.r.o. třída SNP 402/48 500 03 Hradec Králové Czech Republic

2 2