December 2017 - users-list

Excessive slowness in a query
by Alfio Bettini 14 Dec '17

14 Dec '17

Hi all, we're testing MonetDB performances in order to understand if it could be possible to use it as data mart in our BI software. We're considering a set of queries executed in our test environment (CentOS 7, 32 GB RAM, 4 cpu). All the executed queries have quite good performances (between 10 and 45 seconds) except the following one that is taking several minutes in order to be executed: SELECT id, count(distinct value) FROM table GROUP BY id; The table contains 100 millions of data. It has about 130 columns (varchar, int, bigint, smallint, tinyint, timestamp, datetime). We have no BLOB/CLOB/Text types. The fields used in the query have these data types: id: int value: bigint Can you suggest us any optimizations in order to have this query run faster? Do you know some possible reasons why it's so slow? Kind Regards, Alfio Bettini The information contained in this message, together with any attachments, may be privileged and confidential and is intended for the use of the addressee(s) only. Any review, copying, use or distribution of this email by others (including any of its attachments) is strictly prohibited. If you are not the intended recipient, please destroy this email and its attachments and contact the sender immediately. This email was sent by EidosMedia SpA<http://www.eidosmedia.com>

1 0

Re: algebra.join heapextend crash
by Stefan Manegold 13 Dec '17

13 Dec '17

Allocating "only" about 2.8 GB does not ring alarms that the plan night be degenerated - assuming your data is not tiny. Could you share:OS, full query, data sizes, free disk space at time of failure, mserver5 process size at time of failure? Thanks,Stefan -------- Original message --------From: Anthony Damico <ajdamico(a)gmail.com> Date: 11/22/17 12:13 (GMT+01:00) To: Communication channel for MonetDB users <users-list(a)monetdb.org> Subject: algebra.join heapextend crash hi, i'm using monetdblite-r 0.5.0 on a confidential dataset. my syntax worked on monetdblite 0.3.1 on the same machine ftruncate: Invalid argument Error in .local(conn, statement, ...) : Unable to execute statement 'CREATE TABLE ccaed123_b_svcmon_xwalk AS ( SELECT a.enrolid , a.yr , a.dataty...'. Server says 'MALException:algebra.join:GDK reported error. !ERROR: GDKextendf: c !ERROR: HEAPextend: failed to extend to 2890530816 for 02\57\25766.tail: GDKmre map() failed '. it's a local drive, the hard disk isn't close to full and the machine has 700gb of ram, but the table it's trying to create might be bigger than that. dunno if related to https://www.monetdb.org/bugzilla/show_bug.cgi?id=3791 thanks

2 4

MonetDB Blob Inserts
by Brian Hood 07 Dec '17

07 Dec '17

Hi Guys, I don't seem to be able to INSERT into a blob field is there any information how to achieve this. Regards, Brian Hood

3 6

What is better JSON or TEXT field in terms of performance ???
by Ariel Abadi 07 Dec '17

07 Dec '17

Hi All! Does anybody has worked with json fields? We are planning to use json fields with very large text on it (the hierarchy of our json would be plain (only one level), we are doing some tests over 7MM records, it took like 1m30s to retrieve the information. In this case to get the information we used the function json.filter(name_of_field, '$..xxxx') We have also tested using a TEXT field (and using some string functions) we parsed the information inside the field and we found that sometimes it was quicker it took from 1m30s to 35s. I dont know exactly why the difference on that delta. We need to implement the processes, and need to decide how to move forward.... Does anybody can help me on the decision ??? We know and is a fact, that Monet can beat any DB in terms of performance, and that is why we decided to use it, but now Im worried about this type of queries (against json/text fields) and how long this queries would take if we increase the amount of records.... What would you suggest ?? Thanks in advance! Rgds Ariel

3 3

mserver5 crash on startup in Ubuntu Xenial
by Manuel Pagliai 06 Dec '17

06 Dec '17

Hello, I get this error when I try to start monetdb on Ubuntu Xenial 16.04.03 mserver5 visokio # MonetDB 5 server v11.27.9 "Jul2017-SP2" # Serving database 'demo', using 12 threads # Compiled for x86_64-pc-linux-gnu/64bit with 128bit integers # Found 7.702 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2017 MonetDB B.V., all rights reserved # Visit https://www.monetdb.org/ for further information !SyntaxException:parseError:include sqlcatalog; !SyntaxException:parseError:^LoaderException:loadLibrary:Loading error failed to open library sql (from within file '/usr/lib/monetdb5/lib_sql.so'): /usr/lib/monetdb5/lib_sql.so: undefined symbol: mnstr_writeFlt !SyntaxException:parseError:include sqlcatalog; !SyntaxException:parseError: ^';' expected Segmentation fault (core dumped) any ideas ? Thanks, Best regards, *Manuel Pagliai* Visokio Ltd www.visokio.com +44 20 7351 4646

1 0

Issue with SUM(DISTINCT) in 11.27.9
by Sharma, Sreejith 05 Dec '17

05 Dec '17

All, We have noticed when SQL pattern has SUM(DISTINCT... ) there is a degradation of SQL timings when compared to 11.27.5 build. Anyone is aware of this? Regards, Sreejith

1 0

Re: MAL query "sql.resultSet"
by dennis.fassl 01 Dec '17

01 Dec '17

Hi all, can you help me with the below query? Is there an other way to return query results using the jdbc interface (without using sql.resultSet)? Thanks in advance! Best regards,Dennis -------- Ursprüngliche Nachricht --------Von: Dennis Fassl <dennis.fassl(a)uni-konstanz.de> Datum: 25.11.17 16:55 (GMT+01:00) An: users-list(a)monetdb.org Betreff: MAL query "sql.resultSet" Hi all, I am trying to execute the MAL query below. But the command "sql.resultSet" is terminating mclient. When using the JDBC interface I get "SQLException: Connection to server lost! (mserver still alive?)". Do you have an idea why this is happening? Thanks and regards, Dennis sql.init(); table_names := bat.new(nil:str); bat.append(table_names, "t0"); bat.append(table_names, "t1"); table_attrs := bat.new(nil:str); bat.append(table_attrs, "int_col"); bat.append(table_attrs, "str_col"); table_types := bat.new(nil:str); bat.append(table_types, "int"); bat.append(table_types, "str"); table_lens := bat.new(nil:int); bat.append(table_lens, 1); bat.append(table_lens, 1); table_scales := bat.new(nil:int); bat.append(table_scales, 0); bat.append(table_scales, 0); ints := bat.new(nil:int); bat.append(ints, 42); bat.append(ints, 23); strs := bat.new(nil:str); bat.append(strs, "foo"); bat.append(strs, "bar"); sql.resultSet(table_names, table_attrs, table_types, table_lens, table_scales, ints, strs); _______________________________________________ users-list mailing list users-list(a)monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

4 6