- users-list - monetdb.org

Announcement: New Feb2013 Feature release of MonetDB suite
by Sjoerd Mullender 22 Feb '13

22 Feb '13

The MonetDB team at CWI/MonetDB BV is pleased to announce the Feb2013 feature release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <http://www.monetdb.org/>. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>. Feb 2013 feature release Testing Environment * enabled "top-level" Mtest.py So far, while Mtest.py could be called in any subdirectory of the MonetDB source tree (and could then run all tests in the entire sub-tree), it was not possible to call Mtest.py in the top-level MonetDB source directory to run all tests. Instead, to run all tests, Mtest.py had to be called at least 4 times, once in each of these directories: "clients", "monetdb5", "sql", "geom". Now, it is possible to call Mtest.py once in the top-level MonetDB source directory to run all tests in one go. The behaviour of calling Mtest.py in any subdirectory, including the four mentioned above, did not changed, other than that now obsolete command line options "-p / --package <package>" and "-5 / --monetdb5" have been removed. Java Module * merocontrol was changed to return server URIs, and lastStop time. Connections and dbpath were removed. * Mapi protocol v8 support was removed from MapiSocket. Protocol v8 has not been used by the servers any more since Apr2012 release Client Package * Mapi protocol v8 support was removed from all client drivers. Protocol v8 has not been used by the servers any more since Apr2012 release * The tool mnc was removed from installations * msqldump: Implmented an option (--table/-t) to dump a single table. * Changed msqdump's trace option to be in line with mclient. In both cases, the long option is --Xdebug and the short option is -X. MonetDB5 Server * mserver5: The --dbname and --dbfarm options have been replaced by the single --dbpath option. * The scheduler of mserver5 was changed to use a fixed set of workers to perform the work for all connected clients. Previously, each client connection had its own set of workers, easily causing resource problems upon multiple connections to the server. Merovingian * Upgrade support for dbfarms from Mar2011 and Aug2011 was dropped * monetdb status now uses a more condensed output, to cater for the uris being shown, and prints how long a database is stopped, or how long ago it crashed * monetdb status now prints the connection uri for each database, when available. The connections and database path properties have been dropped. * monetdb status now prints last crash date only if the database has not been started since. Bug Fixes * 2291: small doubles end up as NULL after arithmetic * 3215: Calculation Date function using interval and year * 3033: stethoscope needs better documentation * 3084: Timestamp arithmetic very slow (especially on Windows) * 3125: Python tests fail after recent Python API changes * 3172: assertion fails on table function with subselects as parameters * 3178: one scan is enough to implement ALGstdev_@1 in monetdb5/modules/kernel/algebra.mx * 3179: LIKE: batstr.like+algebra.uselect called instead of pcre.like_filter * 3193: Expressions not supported in the GROUP BY or ORDER BY clause. * 3216: "unknown property" error setting format and width in .monetdb file * 3217: gdk_posix fails to compile under NetBSD * 3221: can no execute large statements * 3227: MT_set_lock() call on an non-initialized lock

5 11

MonetDB documentation
by Brandon Jackson 15 Feb '13

15 Feb '13

Just curious, I follow the mailing lists closely very recently. Is there a way, or does it make sense for someone like me to have access to update the MonetDb documentation based on responses from the maintainers here? I would be happy to collect some of this information and send it in for review before posting as documentation. Brandon Sent from my iPhone

2 2

Windows Networked Drive Issue with COPY INTO
by Anthony Damico 15 Feb '13

15 Feb '13

I am running MonetDB5 Oct 2012 SP3 on my work Windows 7 desktop computer. My office has what I imagine is a pretty typical mix of networked machines, with a few more powerful servers and larger shared drives that everyone can connect to. (1) When I attempt to import a large CSV file into a MonetDB server on the _local_ disk (C:\My Directory\MonetDB\dbfarm), the importation command runs as expected. (2) When I attempt to import the same CSV file to a MonetDB server stored on a networked drive, the import command fails. (3) Finally, when I *remote desktop* into the server that the networked drive is actually attached to, the importation succeeds again! Here's the error seen in scenario (2) -- GDKerror:!ERROR: GDKunlink(bat\13\1327.tail) !OS: The process cannot access the file because it is being used by another process. GDKunlink(bat\13\1327.theap) !OS: The process cannot access the file because it is being used by another process. So it seems pretty clear that something about windows networked drives is the culprit. This error does not occur on every file (larger files seem more prone to the error), but if an import command does not work on a file, it _consistently_ does not work on that file. As an example, importing this large file works with methods (1) and (3) but not (2) -- http://downloads.cms.gov/BSAPUF/2008_BSA_Carrier_Line_Items_PUF_1.zip I tried disabling the windows firewall and using different server ports, but I still hit the above error. I spoke with Hannes about it briefly, he said: "The issue is probably related to the Windows mmap() implementation when talking to a SMB filesystem. It is far from certain that there is something we can do about this, but in either case, we'd like to know about it." Should I submit an official bug report to http://bugs.monetdb.org/ ? Perhaps we are overlooking something obvious about our windows network's configuration.. Thanks!!! Here is my MonetDB server info, followed by the COPY INTO commands run in -X mode for each of the three scenarios: # MonetDB 5 server v11.13.9 "Oct2012-SP3" # Serving database 'bsa', using 2 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 15.873 GiB available main-memory. # 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 # Listening for connection requests on mapi:monetdb://127.0.0.1:50001/ # MonetDB/JAQL module loaded # MonetDB/SQL module loaded ************************** (1) - local disk - works ----- fetch next block: start at:196 got next block: length:0 text: got complete block: text:☺ read_line:☺ Database: MonetDB v11.13.9 (Oct2012-SP3), 'bsa' closing result set Type \q to quit, \? for a list of available commands auto commit mode: on mapi_query:46:SET TIME ZONE INTERVAL '-05:00' HOUR TO MINUTE fetch next block: start at:198 got next block: length:3 text:&3 got complete block: text:&3 read_line:&3 allocating new result set fetch next block: start at:201 got next block: length:0 text: got complete block: text:☺ read_line:☺ closing result set sql>create table mytable ( mapi_query_part:23:create table mytable ( fetch next block: start at:203 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:206 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_id VARCHAR(255), mapi_query_part:27: car_line_id VARCHAR(255), fetch next block: start at:208 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:211 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> bene_sex_ident_cd INTEGER, mapi_query_part:28: bene_sex_ident_cd INTEGER, fetch next block: start at:213 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:216 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> bene_age_cat_cd INTEGER, mapi_query_part:26: bene_age_cat_cd INTEGER, fetch next block: start at:218 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:221 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_icd9_dgns_cd VARCHAR(255), mapi_query_part:37: car_line_icd9_dgns_cd VARCHAR(255), fetch next block: start at:223 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:226 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_hcpcs_cd VARCHAR(255), mapi_query_part:33: car_line_hcpcs_cd VARCHAR(255), fetch next block: start at:228 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:231 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_betos_cd VARCHAR(255), mapi_query_part:33: car_line_betos_cd VARCHAR(255), fetch next block: start at:233 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:236 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_srvc_cnt INTEGER, mapi_query_part:28: car_line_srvc_cnt INTEGER, fetch next block: start at:238 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:241 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_prvdr_type_cd INTEGER, mapi_query_part:33: car_line_prvdr_type_cd INTEGER, fetch next block: start at:243 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:246 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_cms_type_srvc_cd VARCHAR(255), mapi_query_part:41: car_line_cms_type_srvc_cd VARCHAR(255), fetch next block: start at:248 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:251 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_place_of_srvc_cd INTEGER, mapi_query_part:36: car_line_place_of_srvc_cd INTEGER, fetch next block: start at:253 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:256 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_hcpcs_pmt_amt INTEGER mapi_query_part:27: car_hcpcs_pmt_amt INTEGER fetch next block: start at:258 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:261 got next block: length:0 text: got complete block: text:☺ read_line:☺ more>) ; mapi_query_part:4:) ; fetch next block: start at:263 got next block: length:3 text:&3 got complete block: text:&3 read_line:&3 allocating new result set fetch next block: start at:266 got next block: length:0 text: got complete block: text:☺ read_line:☺ operation successful (118.294ms) closing result set sql> fetch next block: start at:268 got next block: length:0 text: got complete block: text:☺ read_line:☺ sql>COPY 9676440 offset 2 records INTO mytable FROM 'C:\My Directory\BSAPUF\2008 \2008_BSA_Carrier_Line_Items_PUF_1.csv' USING DELIMITERS ',','\n','\"' NULL AS ' ' ; mapi_query_part:160:COPY 9676440 offset 2 records INTO mytable FROM 'C:\My Direc tory\BSAPUF\2008\2008_BSA_Carrier_Line_Items_PUF_1.csv' USING DELIMITERS ',','\n ','\"' NULL AS '' ; fetch next block: start at:270 got next block: length:14 text:&2 9676439 -1 got complete block: text:&2 9676439 -1 read_line:&2 9676439 -1 allocating new result set fetch next block: start at:284 got next block: length:0 text: got complete block: text:☺ read_line:☺ 9676439 affected rows (3m 26s) closing result set sql> ************************** (2) - networked disk - fails ----- read_line:☺ more> car_line_id VARCHAR(255), mapi_query_part:27: car_line_id VARCHAR(255), fetch next block: start at:208 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:211 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> bene_sex_ident_cd INTEGER, mapi_query_part:28: bene_sex_ident_cd INTEGER, fetch next block: start at:213 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:216 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> bene_age_cat_cd INTEGER, mapi_query_part:26: bene_age_cat_cd INTEGER, fetch next block: start at:218 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:221 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_icd9_dgns_cd VARCHAR(255), mapi_query_part:37: car_line_icd9_dgns_cd VARCHAR(255), fetch next block: start at:223 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:226 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_hcpcs_cd VARCHAR(255), mapi_query_part:33: car_line_hcpcs_cd VARCHAR(255), fetch next block: start at:228 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:231 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_betos_cd VARCHAR(255), mapi_query_part:33: car_line_betos_cd VARCHAR(255), fetch next block: start at:233 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:236 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_srvc_cnt INTEGER, mapi_query_part:28: car_line_srvc_cnt INTEGER, fetch next block: start at:238 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:241 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_prvdr_type_cd INTEGER, mapi_query_part:33: car_line_prvdr_type_cd INTEGER, fetch next block: start at:243 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:246 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_cms_type_srvc_cd VARCHAR(255), mapi_query_part:41: car_line_cms_type_srvc_cd VARCHAR(255), fetch next block: start at:248 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:251 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_place_of_srvc_cd INTEGER, mapi_query_part:36: car_line_place_of_srvc_cd INTEGER, fetch next block: start at:253 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:256 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_hcpcs_pmt_amt INTEGER mapi_query_part:27: car_hcpcs_pmt_amt INTEGER fetch next block: start at:258 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:261 got next block: length:0 text: got complete block: text:☺ read_line:☺ more>) ; mapi_query_part:4:) ; fetch next block: start at:263 got next block: length:3 text:&3 got complete block: text:&3 read_line:&3 allocating new result set fetch next block: start at:266 got next block: length:0 text: got complete block: text:☺ read_line:☺ operation successful (289.145ms) closing result set sql> fetch next block: start at:268 got next block: length:0 text: got complete block: text:☺ read_line:☺ sql>COPY 9676440 offset 2 records INTO mytable FROM 'C:\My Directory\BSAPUF\2008 \2008_BSA_Carrier_Line_Items_PUF_1.csv' USING DELIMITERS ',','\n','\"' NULL AS ' ' ; mapi_query_part:160:COPY 9676440 offset 2 records INTO mytable FROM 'C:\My Direc tory\BSAPUF\2008\2008_BSA_Carrier_Line_Items_PUF_1.csv' USING DELIMITERS ',','\n ','\"' NULL AS '' ; fetch next block: start at:270 got next block: length:252 text:!GDKerror:!ERROR: GDKunlink(bat\13\1327.tail) !!OS: The process cannot access the file because it is being used by another pro cess. !GDKunlink(bat\13\1327.theap) !!OS: The process cannot access the file because it is being used by another pro cess. ! got complete block: text:!GDKerror:!ERROR: GDKunlink(bat\13\1327.tail) !!OS: The process cannot access the file because it is being used by another pro cess. !GDKunlink(bat\13\1327.theap) !!OS: The process cannot access the file because it is being used by another pro cess. ! read_line:!GDKerror:!ERROR: GDKunlink(bat\13\1327.tail) allocating new result set got complete block: text:!!OS: The process cannot access the file because it is being used by anothe r process. !GDKunlink(bat\13\1327.theap) !!OS: The process cannot access the file because it is being used by another pro cess. ! read_line:!!OS: The process cannot access the file because it is being used by a nother process. got complete block: text:!GDKunlink(bat\13\1327.theap) !!OS: The process cannot access the file because it is being used by another pro cess. ! read_line:!GDKunlink(bat\13\1327.theap) got complete block: text:!!OS: The process cannot access the file because it is being used by anothe r process. ! read_line:!!OS: The process cannot access the file because it is being used by a nother process. got complete block: text:! read_line:! fetch next block: start at:522 got next block: length:0 text: got complete block: text:☺ read_line:☺ GDKerror:!ERROR: GDKunlink(bat\13\1327.tail) !OS: The process cannot access the file because it is being used by another proc ess. GDKunlink(bat\13\1327.theap) !OS: The process cannot access the file because it is being used by another proc ess. closing result set sql> ************************** (3) - remote desktop into server so networked disk becomes local disk - works ----- read_line:[ "monet_release", "Oct2012-SP3" ] fetch next block: start at:196 got next block: length:0 text: got complete block: text:☺ read_line:☺ Database: MonetDB v11.13.9 (Oct2012-SP3), 'bsa' closing result set Type \q to quit, \? for a list of available commands auto commit mode: on mapi_query:46:SET TIME ZONE INTERVAL '-05:00' HOUR TO MINUTE fetch next block: start at:198 got next block: length:3 text:&3 got complete block: text:&3 read_line:&3 allocating new result set fetch next block: start at:201 got next block: length:0 text: got complete block: text:☺ read_line:☺ closing result set sql>create table mytable3 ( mapi_query_part:24:create table mytable3 ( fetch next block: start at:203 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:206 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_id VARCHAR(255), mapi_query_part:27: car_line_id VARCHAR(255), fetch next block: start at:208 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:211 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> bene_sex_ident_cd INTEGER, mapi_query_part:28: bene_sex_ident_cd INTEGER, fetch next block: start at:213 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:216 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> bene_age_cat_cd INTEGER, mapi_query_part:26: bene_age_cat_cd INTEGER, fetch next block: start at:218 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:221 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_icd9_dgns_cd VARCHAR(255), mapi_query_part:37: car_line_icd9_dgns_cd VARCHAR(255), fetch next block: start at:223 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:226 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_hcpcs_cd VARCHAR(255), mapi_query_part:33: car_line_hcpcs_cd VARCHAR(255), fetch next block: start at:228 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:231 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_betos_cd VARCHAR(255), mapi_query_part:33: car_line_betos_cd VARCHAR(255), fetch next block: start at:233 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:236 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_srvc_cnt INTEGER, mapi_query_part:28: car_line_srvc_cnt INTEGER, fetch next block: start at:238 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:241 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_prvdr_type_cd INTEGER, mapi_query_part:33: car_line_prvdr_type_cd INTEGER, fetch next block: start at:243 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:246 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_cms_type_srvc_cd VARCHAR(255), mapi_query_part:41: car_line_cms_type_srvc_cd VARCHAR(255), fetch next block: start at:248 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:251 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_line_place_of_srvc_cd INTEGER, mapi_query_part:36: car_line_place_of_srvc_cd INTEGER, fetch next block: start at:253 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:256 got next block: length:0 text: got complete block: text:☺ read_line:☺ more> car_hcpcs_pmt_amt INTEGER mapi_query_part:27: car_hcpcs_pmt_amt INTEGER fetch next block: start at:258 got next block: length:3 text:☺☻ got complete block: text:☺☻ read_line:☺☻ fetch next block: start at:261 got next block: length:0 text: got complete block: text:☺ read_line:☺ more>) ; mapi_query_part:4:) ; fetch next block: start at:263 got next block: length:3 text:&3 got complete block: text:&3 read_line:&3 allocating new result set fetch next block: start at:266 got next block: length:0 text: got complete block: text:☺ read_line:☺ operation successful (276.500ms) closing result set sql> fetch next block: start at:268 got next block: length:0 text: got complete block: text:☺ read_line:☺ sql>COPY 9676440 offset 2 records INTO mytable3 FROM 'v:\temp\2008_BSA_Carrier_L ine_Items_PUF_1.csv' USING DELIMITERS ',','\n','\"' NULL AS '' ; mapi_query_part:141:COPY 9676440 offset 2 records INTO mytable3 FROM 'v:\temp\20 08_BSA_Carrier_Line_Items_PUF_1.csv' USING DELIMITERS ',','\n','\"' NULL AS '' ; fetch next block: start at:270 got next block: length:14 text:&2 9676439 -1 got complete block: text:&2 9676439 -1 read_line:&2 9676439 -1 allocating new result set fetch next block: start at:284 got next block: length:0 text: got complete block: text:☺ read_line:☺ 9676439 affected rows (2m 14s) closing result set sql>

2 2

TPCH Throughput/latency for MonetDB
by Hassan, Ahmad 12 Feb '13

12 Feb '13

Hi, I intend to run TPCH benchmark on MonetDB. I have executed one iteration of benchmark through run.all script provided under sql/benchmarks/tpch. Please may I know if there is a script or framework available to run tpch benchmark for 'X' duration and get throughput and latency numbers for each query? Second, I want to generated data with big scale factor. I searched monetdb mailing list and found out that DBGEN should be used for that. As there is no specific DBGEN provided by monetdb so I have downloaded from http://www.tpc.org/tpch/ . But please let me know which of the following database shall I select while building DBGEN. Official DBGEN supports the following database options: INFORMIX, DB2, TDAT (Teradata), SQLSERVER, SYBASE, ORACLE, VECTORWISE Thanks for the help Kind Regards, Ahmad

1 0

monetdb status health
by Tapomay Dey 12 Feb '13

12 Feb '13

My questions are simple: what causes crashes? what is health? how do we stop health from degrading? Following is the status of my db- msearch_stats_db: database name: msearch_stats_db state: running locked: no scenarios: mal sql msql start count: 140 stop count: 1 crash count: 138 current uptime: 1m 49s average uptime: 15m 33s maximum uptime: 15m 33s minimum uptime: 15m 33s last start with crash: 2013-02-10 17:32:36 last start: 2013-02-10 17:32:47 last stop: 2013-02-06 11:27:15 average of crashes in the last start attempt: 0 average of crashes in the last 10 start attempts: 0.90 average of crashes in the last 30 start attempts: 0.97 Regards, Tapomay.

6 14

Suggested correction for the Java SQLcopyinto example
by Percy Wegmann 09 Feb '13

09 Feb '13

Dear Monet team, I've been getting quite a bit of use out of your example code here: http://dev.monetdb.org/hg/MonetDB/file/tip/java/example/SQLcopyinto.java Recently, though, I ran into a problem. If the "COPY INTO" statement that I issue is wrong, for example the referenced table doesn't exist, Monet comes back with an error. The example isn't checking for this error condition and proceeds to send data anyway. With a really large batch of data, this becomes a problem because the SQL parser ends up trying to parse all that data as if it were SQL and basically chokes on it. The solution is to add something like the following after the out.newLine() on line 86: error = in.waitForPrompt(); if (error != null) { throw new Exception(error); } Cheers, -- Percy Wegmann +1 512 637 8500 ext 148

3 5

Requirement gathering and questions about binary bulk loading.
by Brandon Jackson 08 Feb '13

08 Feb '13

Hello fellow MonetDB'ers, The Pentaho MonetDB Bulk Loader transformation step is now working pretty well in our tests. http://ci.pentaho.com/view/Data%20Integration/job/Kettle contains a continuous integration build of the software if anyone wanted to play with it. New Transformation -> Steps -> Bulk Loading (category)-> MonetDB Bulk Loader Thank you for the previous information about enabling log output from the MonetDB JDBC driver. After killing a stalled "monetdb start ~/sg-farm" thread and restarting it, MonetDB decided to work really well. It was suggested early on that I take a look at the binary bulk loading approach to get the fastest results. Now that I have a streaming ascii version of a bulk loader done for Pentaho Data Integration, I wanted to come back to the binary approach and see what that might look like and what to be aware of. I read http://www.monetdb.org/Documentation/Cookbooks/SQLrecipies/BinaryBulkLoad It looks like the requirements are: 1. Write a physical file per column to the disk the monetdb farm exists on. 2. Tables with DATES, TIMESTAMPS or UTF-8 characters ie: Simplified Chinese are verboten and must be handled with COPY INTO .... USING DELIMITERS Assuming the above is a correct understanding, given the command below: copy into Tmp from ('path_to_file_i', 'path_to_file_f', 'path_to_file_s'); 1. If run from a computer separate from the machine running the monetdb server, would the 'path_to_file_i' be a pathname relative to the monetdb farm root directory on the remote server machine? 2. This process should work for appending new data on to an existing table, right? 3. Am I out of luck if my table has any of the verboten data types mentioned above mixed in with supported binary bulkloadable types; aka C-array style data types? 4. If not out of luck for a mixed type table, what is the procedure to load the table? In practice I imagine I will not need to fret too much about DATES needing a COPY INTO .... USING DELIMITERS ascii streaming approach because it is likely part of a dimension table with integer keys matching integers on a fact table, which is far more likely to have the supported types MonetDB Binary Bulk-loading likes. Is it true that MonetDB does not handle joins well, but instead prefers single, denormalized tables with many columns? >From what you know of the internals of MonetDb, is the effort to make some kind of streaming binary bulk loader worth it in terms of execution time on the database converting to native binary types? The documentation used the words, 'slightly faster', but I do not have a feel for when 'slightly' makes a big difference. Thanks again for considering these questions. Brandon

2 1

High memory usage while loading data with many columns
by Andrew Duffey 08 Feb '13

08 Feb '13

Hi, I'm loading data into a table with >20,000 columns and several hundred thousand rows using "COPY INTO". It works, but I can't seem to make it take less than about 30GB of memory. I've tried specifying the number of records as well. Is there any way to reduce memory usage when loading? Is this much memory usage expected? It's a shame because our queries take very little memory. We have to size our machine simply to handle the load. It's impressive (and very convenient) that I can actually have a table with so many columns, but are there any other limitations I might come across? I've just been trying to keep the column count in the queries low. A "select *" seems to crash MonetDB. Thanks, Andrew

2 8

Data storage capacity in monetdb
by KHALED DEHDOUH 06 Feb '13

06 Feb '13

I installed the version of windows for monetdb http://www.monetdb.org/Downloads. I installed a database column, but when I went over 30 GB of stokage given. the query response time has become very slow. Please, I have two questions: - Are this version is limited or no storage ? - I have noticed that the name of the database is by default "demo". Can i create another one with this version with 1 TB?

4 3

Create stored procedure to add a list of ID's to a table
by Richard Siebeling 05 Feb '13

05 Feb '13

Hello, is it possible to create a stored procedure in MonetDB that will have one parameter containing a string with several thousands of comma-separated ID's and insert these comma-separated ID's as separate rows into a table? I am receiving thousands of ID's from a separate webservice and want to join these ID's to a table in monetDB, aggregate the results and return them back to the client for further processing in the GUI. I tried inserting the ID's into a temporary table using insert into instructions from PHP, but that didn't gave me the performance I wanted (about 6 seconds for 10.000 ID's). So the next approach would be to create a stored procedure receiving all the ID's in one parameter, splitting them and inserting them one-by-one into the temporary table... Or is there a better approach for this problem? thanks in advance, Richard

2 1