August 2015 - users-list

NULL support in MonetDB?
by Stephen P. Morgan 14 Aug '15

14 Aug '15

Hi, I wonder if anyone can tell me whether MonetDB supports NULL values? Thanks, Steve

2 1

THEAP corruption after DB restart
by Dennis Lorson 14 Aug '15

14 Aug '15

Hi, My DB contains around 1000 tables consisting of 100K-15M records each. They all have the same schema, but I’m not using a single table because locality is required for the individual sets. After restarting MonetDB, I’m experiencing data corruption in some of the tables (around 10-20): - when selecting a single row from those tables (LIMIT 1), the mserver process crashes - when calling the storage() function, the mserver process crashes - the storage() function can only be used again after dropping the tables in question (by trial and error) More information: - The restart was performed as part of an EC2 instance stop/start, This should send a SIGTERM signal to monetdbd. - The DB farm is stored on EBS. Snapshots were made well before the restart, but the same tables are affected. - The corrupted tables were used before successfully and the content has been unchanged since. - No client connections were active at restart time. mserver info: > 2015-08-13 15:57:42 MSG DB[10633]: arguments: /usr/bin/mserver5 --dbpath=/opt/db-farm/DB --set merovingian_uri=mapi:monetdb://ip-HOST:50000/DB --set mapi_open=false --set mapi_port=0 --set mapi_usock=/opt/db-farm/DB/.mapi.sock --set monet_vault_key=/opt/db-farm/DB/.vaultkey --set gdk_nr_threads=4 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes > 2015-08-13 15:57:42 MSG DB[10633]: # MonetDB 5 server v11.19.15 "Oct2014-SP4" > 2015-08-13 15:57:42 MSG DB[10633]: # Serving database ‘DB', using 4 threads > 2015-08-13 15:57:42 MSG DB[10633]: # Compiled for x86_64-pc-linux-gnu/64bit with 64bit OIDs dynamically linked > 2015-08-13 15:57:42 MSG DB[10633]: # Found 15.672 GiB available main-memory. This error occurs a couple of times in the server logs, but only when first starting monetdbd: > 2015-08-13 14:33:15 MSG: !ERROR: GDKload: cannot read: name=15/31/153162, ext=theap, 57144 bytes missing. > 2015-08-13 14:33:15 MSG: !OS: No such file or directory > 2015-08-13 14:33:15 MSG: !ERROR: GDKload: cannot read: name=15/25/152564, ext=theap, 58536 bytes missing. > 2015-08-13 14:33:15 MSG: !OS: No such file or directory And then each time an affected table is accessed: > 2015-08-13 16:02:19 MSG merovingian[1041]: database ‘DB' (10640) was killed by signal SIGSEGV I’m trying to reproduce the issue, but it would be helpful if anyone has any pointers. My hunch would be that the table catalog and the theap files are not persisted in the same way, so when snapshotting or restarting, one of the two is out of sync. Could it be that some dynamic column index is built, but then only persisted in mapped memory? The comments at the top of the gdk/gdk_storage.c file seem to imply that this is not the case, but I’m not sure. If so, how would one ensure that all changes are properly flushed to disk? Would fsfreeze help? What if an unexpected loss of power occurs? Thanks, - Dennis

1 1

Remote table joining and where conditions
by Wu XIANG 13 Aug '15

13 Aug '15

Hi guys, I've two questions on July2015 release of monetdb. 1) If I have two VMs (vm1, vm2) with monetdb installed: on vm1, there is a table named t1, and it's also accessible on vm2 via remote table alias rt1, and there's another table on vm2 named d2. If I do a table join on rt1 and d2, what happens here ? a. Is it true that data in t1 are fully loaded into vm2, and table joining happens on vm2 ? b. If I add a where condition on d2, what happens ? Is it true that data of t1 are pre-filtered by this newly added condition before sent to vm2 ? c. If there's another table on vm1 with the same name of 'd2', with where condition on d2 added, what happens then ? p.s. all queries are requested on CLI of vm2. 2) is there anything like partition key of prestodb in a partition table, such that some partitions can be fully skipped when querying with a where condition on these partition key provided ? thanks wu

1 0

Request Buffer pools
by Vijay Krishna 13 Aug '15

13 Aug '15

Hi, Does MonetDB have any built in buffer pools to handle parallel requests? I see the default number of parallel connections accepted and processed at a time by MonetDB is 64.. While I see that there are ways to increase it, is there anyway built in to pool incoming requests until the live 64 requests are processed? For example, our application would receive 100 requests at a time, while we wish to limit the number of queries handled at a time by MonetDB to 50. Is there any way to automatically handle the remaining 50 (something like innodb buffer pool) ?? Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

4 8

Slow DB performance after data insertion
by Zimmermann, Alexander 10 Aug '15

10 Aug '15

Hi folks, in the following scenario I see very slow DB performance after data insertion. W/ slow performance I mean that „mclient -u XXX -d XXX" blocks for roughly 10min bevore it let me access the DB. Scenario: I parse a pcap file (40MB, 695 connection) in python w/ dpkt. For every new connection found in the pcap file I create a new entry in my connection table and two new empty packet tables (for each direction one table). The actual packet data is not directly inserted into the packet tables, but written into files to push the data later on w/ COPY BINARY INTO into the packet tables. Autocommit is off. I commit after the I read the pcap completely and before I start the COPY BINARY INTO. After the COPY BINARY INTO I’ve another commit. That’s it. The number of transactions is 3*695 (2*695 table creations for the packet tables and 695 entries into the connection table). The script itself terminates in roughly 30secs, but the DB is blocked +- 10mins. Why? What is going on internally in the DB? Can I speedup the performance? Cheers Alex

3 3

possible monetdb catalogue corruption and how to recover from it?
by Ashish Kumar Singh 10 Aug '15

10 Aug '15

Hi List, We are seeing a situation with monetdb v11.17.17 "Jan2014-SP2” release where at the time of dropping table , data base got crashed. After that we are not able to drop this table. Any command to drop this table is returning with success but we can not create same table again as it complains about table exists/ in use. But when we try to select * from this table or do a count on this it does complains saying No Such Table exist. My hunch is somehow database catalogue got corrupted, what is recommended way to bring back database to good health after any such condition occurs? We have not tried restarting monetdb instance yet. Regards, Ashish Kumar Singh Mobile: +91-9972045095 Skype: toashishk(a)gmail.com

5 8

Help with lag()
by Eggert, Lars 06 Aug '15

06 Aug '15

Hi, there doesn't seem to be much documentation about how to use lag() in monetdb, and I'm running into a cryptic error. Here is a snippet of a table: sql>select time, tcp_seq from packet_1_s order by time limit 10; +--------------------------+------------+ | time | tcp_seq | +==========================+============+ | 1436723208.3099999 | 2056936800 | | 1436723208.3099999 | 2056936800 | | 1436723208.3099999 | 2056936800 | | 1436723208.3099999 | 2056936800 | | 1436723208.3099999 | 2056936848 | | 1436723208.3099999 | 2056936848 | | 1436723208.3099999 | 2056936848 | | 1436723208.3099999 | 2056936848 | | 1436723208.3099999 | 2056936896 | | 1436723208.3099999 | 2056936896 | +--------------------------+------------+ 10 tuples (9.701ms) sql>select time, tcp_seq, lag(tcp_seq, 1) over (order by time) from packet_1_s order by time limit 10; TypeException:user.s22_25[502]:'calc.lag_grp' undefined in: _1678:any := calc.lag_grp(_1672:oid, _1674:oid, _1676:dbl) program contains errors Any ideas what causes this error? Thanks, Lars

3 5

Trouble with perl dbd modules for Monetdb
by Bryan Ellerbrock 03 Aug '15

03 Aug '15

Hi, I'm new to database work, and completely new to monetdb. I am attempting to load data into a new monetdb setup for testing, to see if it may make sense to migrate some genotyping datasets from postgres. I'm working in a Virtualbox Debian 64bit VM with MonetDB v11.19.15 (Oct2014-SP4). I've set up everything according to the MonetDB tutorial, and have had no trouble creating test tables and inserting data with simple sql statements. Beyond that though I need to use perl, and I'm having trouble with DBD::monetdb and it's dependencies. The download from cpan either times out or fails when working on dependencies MonetDB::CLI and MonetDB::CLI::MapiPP. If I use the verbose flag it reports: Test Summary Report ------------------- t/02cxn.t (Wstat: 28416 Tests: 15 Failed: 14) Failed tests: 2-15 Non-zero exit status: 111 Parse errors: Bad plan. You planned 18 tests but ran 15. t/75mil.t (Wstat: 28416 Tests: 15 Failed: 14) Failed tests: 2-15 Non-zero exit status: 111 Parse errors: Bad plan. You planned 18 tests but ran 15. When I skip these tests with a force install, and try to use the DBD in a loading script, I encounter a similar problem. The perl debugger shows the script getting stuck at my line of code attempting to connect to the database. my $dbh = DBI->connect("dbi:monetdb:database=genotypes;host=localhost",$username,$password, { PrintError => 0, RaiseError => 1 } # turn on exception handling ); If I step into the subroutine calls I can't pinpoint a failure but it is at some point after a call to MonetDB::CLI::MapiPP Am I missing something obvious? Is there something I can do to further address the problem? Any help would be greatly appreciated! Best regards, Bryan Ellerbrock

1 0

Problem using tomograph
by Martin Schwitalla 03 Aug '15

03 Aug '15

Hi, I`m trying to use tomograph for a query analysis. But unlike stethoscope it doesn`t work. I start the tomogroph with tomograph -u monetdb -d exomate, just like in the tutorial. After that I start a mclient with mclient -u monetdb -d exomate and execute the query. But tomograph triggers no output until I quit it with CTRL+C and when I try to gnuplot the file file, I only get errors because there is no valuable data. Could you maybe tell me what I am doing wrong? I`m using MonetDB 11.19.15. Regards, Martin

3 4