August 2013 - users-list

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