September 2017 - users-list

Column rename issue
by Amit Ambekar 15 Sep '17

15 Sep '17

Hi, how to update already created column name and type in monetdb ?

1 0

large scale hardware configuration for MonetDB
by Anton Kravchenko 06 Sep '17

06 Sep '17

Hi there, >From https://www.monetdbsolutions.com/news/monetdb-numascale : "The system is fine-tuned to exploit the large main memories of modern computer systems effectively and has proven to be highly efficient in data warehouse query processing. NumaQ can easily scale to thousands of cores and terabytes of memory for data-intensive." I wonder if there is a public documentation on how to configure large scale hardware for optimal MonetDB performance. So one could try setup NumaQ-like machine by using what you already learned. Also from https://stackoverflow.com/questions/7435235/is-it-worth-trying-monetdb : "MonetDB is developed/tuned under two main assumptions: 1. Your workload is analytical, i.e., you have lots of (grouped) aggregations and the like. 2. Even more important: your hot dataset (the data that you actually work with) fits into the main memory of your system. MonetDB does not have it's own Buffer Manager but relies on the OS to handle disk I/O. Since the OS (especially windows but Linux too) is sometimes very dumb about disk swapping that may become a problem (especially for joins that run out of memory)." Are there any official benchmarks for Windows vs Linux MonetDB performance (especially for queries that require disk swapping) ? Thank you, Anton

2 1

default_pipe optimizer slowness during concurrency
by Sharma, Sreejith 06 Sep '17

06 Sep '17

Hi, I have a scenario where if I run the a SQL with 10 user concurrency in a recursive mode, the execution time is significantly slower. The SQL which completes in .8sec will end up in completing 5-10hrs during concurrency. This behavior is seen when the optimizer is set to "default_pipe" , however when set to "sequential_pipe" the timing has been drastically reduced and it end ups in seconds. How is "sequential_pipe" optimizer works here, is it to do anything with concurrency. NOTE - this happens when there is DISTINCT pattern in SQL. Regards, Sreejith

2 1

Autoload scripts from MonetDB/sql/scripts
by Lefteris 05 Sep '17

05 Sep '17

After playing around with some udfs and such, it seems to me that the autoload feature of the scripts in directory MonetDB/sql/scripts only run if I start monetdb with the following command mserver5 --dbpath=... and then when I connect with mclient all these messages about loading the scripts appear at the mserver5 console. However, if I create a dbfarm with monetdbd create path/ monetdbd start path/ and then a database monetdb create dbname monetdb release dbname and connect with mclient -lsql -d dbname all the goodies from the script dir do not appear to be there. Is there another trigger for this case that runs autoload? thanx

3 5

How to get Memory Dump. Is there any way to eliminate Memory leaks.
by Sharma, Sreejith 05 Sep '17

05 Sep '17

All, Is there any way to get the memory dump from MonetDB? The memory consumed by merver5 post concurrent query execution are not coming down. Need to investigate why memory leak is happening here. Regards, Sreejith

2 2

Monetdb on windows with embedded python 3.6
by imad hajj chahine 05 Sep '17

05 Sep '17

Hi, I have been successfully using monetdb (11.25.3) on windows with embedded python 2.7, recently I needed a library that is only supported on python 3.6, is it possible somehow to point monetdb to the python 3.6 folder? I know this is possible on linux, but my dev environment is on windows. Thank you.

3 6

Defining a table with a sorted column
by Cota, Luis 05 Sep '17

05 Sep '17

Is it possible to define a table such that the server maintains the table sorted with each new row? I'm dealing with observations on market data, and while market data for a specific stock symbol will be sorted, I will be adding multiple symbols to the same table in chunks, so that each symbol is contained within its own load file, and is sorted within that file. How can I enforce this constraint? - Luis ________________________________ IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses.

2 1

Function with table as argument that return a table
by imad hajj chahine 05 Sep '17

05 Sep '17

Hi, I want to create a Python function that accept a query as argument, do some processing and return another table, In addition to the table columns I want to have some additional scalar parameters and i am getting the following error: function requires a single sub query. CREATE function f1(col1 string, col2 int, col3 double, scalar1 int) RETURNS TABLE(col1 string, col2 double, col3 double, col4 double) LANGUAGE PYTHON Is this the normal behavior of Table function, as in aggregate function I can combine columns with scalar parameters, any workaround for this issue. Thank you.

2 3

COPY INTO from LZ4 file: bug or missing feature?
by Ying Zhang 04 Sep '17

04 Sep '17

Dear all, Is COPY INTO from an LZ4 file supported by MonetDB? When I try to load data from an LZ4 file, I always get the error: Failed to import table 'pt', record too long (EOS found) Is this an error or a missing feature? Additional info: - MonetDB version Jul2017 (Mercurial source) - Mac OS (Sierra) - I checked the config.log that my lz4 lib was found - The LZ4 file is valid (I decompressed it, then I could load the data). Thanks in advance! Jennie

1 1

Execute MAL queries
by Dennis Fassl 03 Sep '17

03 Sep '17

Dear all, I am trying to write and execute MAL queries. The overall goal is to translate Cypher to MAL and to use Cypher as an input language for MonetDB. I am using the "explain" statement to get some example MAL queries. This is an output of explain (without the optimizer stuff at the end): function user.s4_1():void; X_0:void := querylog.define("explain select source, target from graph where source = \\'A\\';","default_pipe",14:int); X_30 := bat.new(nil:str); X_36 := bat.new(nil:int); X_34 := bat.new(nil:int); X_33 := bat.new(nil:str); X_32 := bat.new(nil:str); X_3 := sql.mvc(); X_7:bat[:str] := sql.bind(X_3,"sys","graph","source",0:int); C_4:bat[:oid] := sql.tid(X_3,"sys","graph"); C_18 := algebra.thetasubselect(X_7,C_4,"A","=="); X_21:bat[:str] := sql.bind(X_3,"sys","graph","target",0:int); X_28 := algebra.projection(C_18,X_21); X_20 := algebra.projection(C_18,X_7); X_37 := bat.append(X_30,"sys.graph"); X_39 := bat.append(X_32,"source"); X_40 := bat.append(X_33,"varchar"); X_42 := bat.append(X_34,31:int); X_44 := bat.append(X_36,0:int); X_46 := bat.append(X_37,"sys.graph"); X_47 := bat.append(X_39,"target"); X_48 := bat.append(X_40,"varchar"); X_49 := bat.append(X_42,31:int); X_50 := bat.append(X_44,0:int); sql.resultSet(X_46,X_47,X_48,X_49,X_50,X_20,X_28); end user.s4_1; But when I try to execute this query I get errors like that: ERROR = !TypeException:user.main[8]:'sql.tid' undefined in: C_4:bat[:oid] := sql.tid("sys":str,"sys":str,"graph":str); I don't know why the "X_3" is replaced by "sys". I see changes like that in the queries regularly. Does someone know what is wrong or missing in this query? Thanks a lot in advance! Best regards, Dennis

4 5