March 2018 - users-list

[MonetDB-users] Books CDs
by CHARTS LICENSING 11 Aug '24

11 Aug '24

Everything you want to know this dynamic artist check. At blueberry hills duck, roomfull story gtgt getting. Intriguing, life in biography. Here music looking for lyrics maybe, youre find which! Download free wallpapers featuring business cmg worldwide is exclusive. Videos latest products been easier. Exclusive estate we consider request use name voice, image. List covered berries charts licensing message! Reelin and rockin about, has everything you? Many exciting, photos him stage click! Artist check, out some quotes read all intriguing. It alive he, did on occasion birthday wednesday? Has everything you want to know this? Quotes read all intriguing life, in biography or. Gtgt getting licks years reelin and. Click here, music looking for lyrics maybe, youre. News celebrates his th rock will never! Chuck berry news celebrates his th rock, will. Browse through many exciting, photos him stage click here. He did on occasion birthday wednesday night at blueberry. Wallpapers featuring business cmg worldwide. Worldwide is exclusive estate we consider, request use name. Want to know this dynamic artist check out. Awards fast, facts clips list covered. Music looking for lyrics maybe youre. Message board downloads tour dates fan, club inquiries. Other sites, download free wallpapers featuring, business cmg worldwide. Request use, name, voice.

2 1

Multi-column update based on joined table
by Lee Hachadoorian 11 Aug '24

11 Aug '24

I need to update multiple columns (~250) from one table to another based on a two-column key. In Postgres I would write this using the FROM clause, but since MonetDB doesn't support the FROM clause, I've successfully updated one column with: UPDATE census.acs2010_5yr SET b07401001 = ( SELECT CAST(b07401001 AS int) FROM acs2010_5yr.tmp_seq0001 WHERE acs2010_5yr.stusab = tmp_seq0001.stusab AND acs2010_5yr.logrecno = tmp_seq0001.logrecno ); My question is whether there is any benefit to writing a multiple column update into a single UPDATE statement: UPDATE target_table SET column1 = (SELECT column1 FROM source_table WHERE target_table.key = source_table.key), column2 = (SELECT column2 FROM source_table WHERE target_table.key = source_table.key), ..., columnN = (SELECT columnN FROM source_table WHERE target_table.key = source_table.key); On Postgres I believe the multi-column update using a table join would only have to do the sequential scan of each table once, but with the necessity of sub-selects in MonetDB, what is the backend doing? Is it already doing an index scan once for each row in the single column case, then doing it once for each row * column in the multi-column case? Best, --Lee -- Lee Hachadoorian Asst Professor of Geography, Dartmouth College http://freecity.commons.gc.cuny.edu/

3 3

[MonetDB-users] XQuery API and XLink/XUpdate support
by Alexandra Mart�nez 27 Jun '24

27 Jun '24

Hello all, I was wondering if there is any documentation about the API for XQuery (i.e., how to write xqueries from a Java program). Also, I am interested in using some features of XLink and XUpdate on top of my XML database. Is support for any of these in progress? Thanks in advance, Alexandra ______________________________________________________ Click here to donate to the Hurricane Katrina relief effort. http://store.yahoo.com/redcross-donate3/

4 3

[MonetDB-users] Proofreading your professional documents
by Richard Hounslow 24 Feb '24

24 Feb '24

Dear Professor/Lecturer Please excuse us for contacting you directly. We are offering an efficient service to help you ensure your academic and professional work is written in perfect English. We will check the grammar and style of your work and return it to you to meet your requirements and deadlines. Proof-Reading-Service.com can provide you with a professional proofreading service at a very reasonable rate. All our proofreaders are highly qualified native English speakers. Many work as leading academics in their fields and all have extensive experience of proofreading to the highest standards. If you are interested in our service, please take a look at our website: www.proof-reading-service.com All you have to do is send us your document as a word attachment with the deadline and we will guarantee delivery of a perfectly written document to give you complete confidence when you submit your work. The fee is worked out on a flat rate (£6.95 per thousand words or 0.695 pence per word), so you know exactly how much the proofreading will cost in advance. Once we have received your document(s) we will confirm the word count, the price and the deadline. If you have not heard from us within 2 hours during normal business hours after you have sent your work please resend it from a different email account or send the file to proofreadings(a)gmail.com. We look forward to hearing from you! Yours faithfully Proof-Reading-Service.com

2 2

GDK reported error. MT_mremap: mremap failed. !OS: Cannot allocate memory
by RanchoYuan 04 Jul '19

04 Jul '19

Hi Stefan, Thanks for your reply! We have run the query a few times with different size of data. There we used 16G RAM(actually 13.5G was used), and find the size of 10G's data is the critical point that can run the query. All of the data files' size are listed below, each file name is a table name(there are only a few tables are refered -- store_sales, date_dim, item, customer, catalog_sales, web_sales): 7.4K call_center.dat 1.6M catalog_page.dat 212M catalog_returns.dat 2.9G catalog_sales.dat 27M customer_address.dat 64M customer.dat 77M customer_demographics.dat 9.9M date_dim.dat 77B dbgen_version.dat 149K household_demographics.dat 328B income_band.dat 2.6G inventory.dat 28M item.dat 61K promotion.dat 1.7K reason.dat 1.1K ship_mode.dat 27K store.dat 323M store_returns.dat 3.8G store_sales.dat 4.9M time_dim.dat 1.2K warehouse.dat 19K web_page.dat 98M web_returns.dat 1.5G web_sales.dat 12K web_site.dat So we guess that the monetdb has no memory management? For the output of `mserver5 --version` is: MonetDB 5 server v11.27.13 "Jul2017-SP4" (64-bit, 128-bit integers) Copyright (c) 1993 - July 2008 CWI Copyright (c) August 2008 - 2018 MonetDB B.V., all rights reserved Visit https://www.monetdb.org/ for further information Found 17.0GiB available memory, 40 available cpu cores Libraries: libpcre: 8.38 2015-11-23 (compiled with 8.38) openssl: OpenSSL 1.0.2g 1 Mar 2016 (compiled with OpenSSL 1.0.2g 1 Mar 2016) libxml2: 2.9.3 (compiled with 2.9.3) Compiled by: monetdb(a)MonetDB-0.0 (x86_64-pc-linux-gnu) Compilation: gcc -g -O2 Linking : /usr/bin/ld -m elf_x86_64 And the size of processes is not limited. To let you reproduce the problem conveniently, I'll provide more details here: you can get tpc-ds from its website(we use version 2.6.0). Install the tpc-ds, access the directory v2.6.0/tools and run `./dsdgen -scale 10 -dir /home/monetdb/tpc-ds_test_data10G` to generate the data. When data has been generated, using the script /expe.sh/ to create tables and load the data. The query script is 123.tpcds.23.sql.(The syntaxs of other queries that tpc-ds generates is not suitable for monetdb all, we don't modify them all when the problem occurred). One more question, I can't get your reply email, so I don't know how to reply you, for this case, I could only send a new mail echo time. Thanks! Regards, Rancho

4 3

Announcement: New Mar2018 Feature release of MonetDB suite
by Sjoerd Mullender 29 Mar '18

29 Mar '18

The MonetDB team at CWI/MonetDB BV is pleased to announce the Mar2018 feature release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <https://www.monetdb.org/>. For details on this release, please see the release notes at <https://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <https://dev.monetdb.org/downloads/>. Some notable changes: - Code base requires a C99 compiler. - The internal representation of REAL and DOUBLE NULL values has changed. Before they were represented as the smallest C float/double finite value (-FLT_MAX and -DBL_MAX). Now they are represented by a NaN (not-a-number) value. This requires a database upgrade which is done automatically at startup. Be advised that it is recommended to make a backup before upgrading. - Various extensions to SQL have been implemented. For more details, see below. Mar 2018 feature release (11.29.3) MonetDB5 Server * Implemented function pcre.replace_first which is like pcre.replace, except it only replaces the first match. * The EXPLAIN command now shows all the MAL type resolutions, because in general users may not be aware of their signatures. It also simplifies programs to analyze such plans. * Implemented versions of group.(sub)group(done) that don't return a histogram. * Removed MAL functions streams.socketRead, streams.socketReadBytes, streams.socketWrite, and streams.socketWriteBytes. * Removed MAL functions streams.openRead(s:streams):streams and streams.openWrite(s:streams):streams. * Lots of changes to streamline the internal error handling. In principle, all errors should now include a SQLSTATE error code (see the SQL standard). Build Environment * Added the .pdb files needed for debug symbols to the Windows installer for MonetDB/SQL. . * mclient's execution time profiling options and output format have been changed. Instead of implicitly via the "--interactive"/"-i" option (or when using an interactive mclient console), execution time profiling is now controlled via an explicit "--timer=timermode"/"-t timermode" command-line option, or a "\t timermode" command in the interactive mclient console. The default (also in the interactive mclient console) is now timermode "none", i.e., no timing information is given. Timermode "clock" activates client-side wall-clock timing ("clk") in "human-friendly" format much like the interactive mode did before. Timermode "performance" also provides detailed server-side timings: "sql" is the time to parse the SQL query, optimize the logical relational plan and create the initial physical (MAL) plan; "opt" is the time to optimize the physical (MAL) plan; "run" is the time to execute the physical (MAL) plan. With timermode "performance" all server-side timings and the client-side wall-clock time are given in milliseconds (ms). Note that the client-measured wall-clock time "clk" is reported per query only when options "--interactive" or "--echo" are used, because only then does mclient send individual lines (statements) of the SQL script to the server. Otherwise, mclient sends the SQL script in large(r) batch(es) to the server, and, thus, only the total wall-clock time per batch is measured and reported. The server-measured detailed performance timings "sql", "opt", "run" are always measured and reported per query. Also, all timing information is now given on a separate line and sent to stderr rather than stdout. * Some types and constants were moved from configure (and hence monetdb_config.h) to gdk.h. In particular, the types "lng" and "ulng" have been moved and can therefore no longer be used by code that doesn't (ultimately) include gdk.h. Just use int64_t instead. A bunch of format defines have been removed: SZFMT, SSZFMT, PTRFMT, PDFMT. Just use the C standard codes for those (%zu, %zd, %p, %td). The define for printing a lng (LLFMT) was also moved. Use PRId64 for printing int64_t in code not using gdk.h. Removed all references to __int64 and long long (use int64_t instead). Merovingian * Add daemon commands for starting/stopping collection of profiler (stethoscope) logs. Client Package * ODBC: The driver function SQLProcedureColumns was implemented. * ODBC: Changed table types as used by SQLTables from "LOCAL TEMPORARY" and "GLOBAL TEMPORARY" to "LOCAL TEMPORARY TABLE" and "GLOBAL TEMPORARY TABLE" respectively. * ODBC: Initial support for the HUGEINT type in SQL was added. Any value with type HUGEINT can be retrieved in a C variable with type SQL_C_CHAR or SQL_C_WCHAR. A value of type HUGEINT can be retrieved in other C types as long as they fit, the largest C type supported being a 64 bit integer (equivalent to BIGINT). * The functions in the mapi library that require 64 bit integers now use the standard type int64_t instead of the non-standard mapi_int64. This requires a compilation environment that has the stdint.h include file (standardized in C99). Compilation of the library also requires the inttypes.h include file (also standardized in C99). * Add a new pretty printing option to stethoscope Running stethoscope with the flag -j will produce not pretty printed output (one json object per line). Running with the -y flag will produce pretty printed output. Running with neither will produce the legacy, line oriented format mapilib * The three mapi_explain* functions and mapi_trace don't return any useful information, so they now return void. * The functions mapi_error_str and mapi_result_error now return const char * instead of plain char * to indicate that the returned data belongs to the library and should not be changed or freed by the application. * New function const char *mapi_result_errorcode(MapiHdl) which returns the SQLSTATE code if available when an error has occurred. stream * The interface of mnstr_fgetpos and mnstr_fsetpos was changed to look more like the standard C functions fsetpos and fgetpos: they both have a second argument "pointer to fpos_t". * Removed function wbstream. * Removed functions udp_rastream and udp_wastream. * Removed functions socket_rstream and socket_wstream. * Removed functions append_wstream and append_wastream. * Removed functions mnstr_rstream and mnstr_wstream. utils * The function mcrypt_getHashAlgorithms now returns a static, constant string, so the result should not be modified or freed. MonetDB Common * Changed return type of function void_replace_bat from BUN to gdk_return: it now only returns whether the operation succeeded or not. * Changed the return type of BATroles from void to gdk_return: it can fail due to malloc failure. * Removed functions ALIGNsetH, ALIGNsetT, and CREATEview_ (mind the underscore). The first can easily be replace by using BAThseqbase (that's all it did), the second was only used once, and the third can be replace by VIEWcreate. * Removed unused functions BATmemsize and BATvmsize. * Removed the tnodense property: it was maintained but never actually used, not even stored. * The NIL representation of the internal flt and dbl types was changed from the smallest representable finite value to NaN (not-a-number). * Changed the interface of ATOMformat and VALformat: they now return a pointer to the allocated string. * The length "method" for atoms now returns a size_t, the "len" field of a ValRecord is now a size_t, the "size" field of the atomDesc structure is now unsigned short. * Removed the "align" field from the ATOM descriptor (atomDesc) structure. * The atomtostr and atomfromstr "methods" for atoms now return ssize_t and require a pointer to size_t for the size of the buffer. * The atom tostr and fromstr "methods" now always return -1 on error. A return value greater than 0 is normal, a return value of 0 is not normal, but technically not an error. Testing Environment * Added a --data_path option to Mtest.py that defines an external data repository. See the commit message of c484932c7fd8 for more info. SQL * Extended support to use CREATE ORDERED INDEX on columns of type: char, varchar, clob, blob, url, json, inet and uuid. * Added new system view: sys.ids which contains all database objects ids which can be used in sys.dependencies table. * Added new system view: sys.dependencies_vw which shows all data of sys.dependencies including names on objects, object types and dependency types. * Added 25 new system views for finding out dependencies between database objects. These new dependency views improve, extend and replace the 17 sys.dependencies_X_on_Y() functions as previously defined in 21_dependency_functions.sql. Those sys.dependencies_X_on_Y() functions are now marked as deprecated. * Added new system view: sys.roles which contains all defined roles. * Added new system view: sys.var_values which shows the values for system variables. * Added support for COMMENT ON statements using SQL syntax: COMMENT ON { SCHEMA | TABLE | VIEW | COLUMN | INDEX | SEQUENCE | FUNCTION | PROCEDURE | AGGREGATE | FILTER FUNCTION | LOADER } qname IS { 'my description text' | NULL | '' } ; For COLUMN the qname can be "table_name"."column_name" or fully qualified as in: "schema_name"."table_name"."column_name". For FUNCTION, PROCEDURE, AGGREGATE, FILTER FUNCTION and LOADER the qname may need to include the signature (argument types) to be able to differentiate between multiple overloaded functions which have the same name and schema. By specifying IS NULL or IS '' you remove the comment for the database object. If a database object is dropped, the associated comment is also removed. Note: it is not allowed or possible to add comments for temporary tables or objects in schema "tmp". The sql catalog has been extended with system table: sys.comments. The keyword 'COMMENT' has now become a reserved keyword. * Removed system function sys.environment(). It was a duplicate of system function sys.env(). * Implemented behavior for DROP SCHEMA my_schema RESTRICT command. Previously the RESTRICT keyword was accepted but not obeyed. It would always do a CASCADE operation. Also the default behavior of DROP SCHEMA my_schema command is now changed into RESTRICT behavior (was CASCADE). * The internal NULL representation of the REAL (FLOAT) and DOUBLE types was changed from the smallest representable finite value to NaN (not-a-number). * A column default value can be used in a UPDATE statement: UPDATE tname SET cname = DEFAULT, and INSERT statements: INSERT INTO tname VALUES (..., DEFAULT, ...) * Added support for TRUNCATE statements conforming to the SQL:2008 standard: TRUNCATE [ TABLE ] qname [ CONTINUE IDENTITY | RESTART IDENTITY ] [ RESTRICT | CASCADE ] In a TRUNCATE statement a 'CONTINUE IDENTITY' or 'RESTART IDENTITY' clause can be passed to restart or not, being the former the default one. The 'CASCADE' option instructs to truncate referencing table(s) also if the referencing table(s) have foreign key references to this table. The default behavior is 'RESTRICT'. Note: it is possible to use TRUNCATE statements in a transaction and thus to rollback the effects of a truncate. The keywords 'TRUNCATE' and 'CONTINUE' have now become reserved keywords. * Added possibility to GRANT or REVOKE a TRUNCATE privilege to a user or role. * Added possibility to define a TRIGGER on a TRUNCATE event. * Added possibility to specify 'OR REPLACE' in following CREATE commands: CREATE [ OR REPLACE ] VIEW qname ... CREATE [ OR REPLACE ] TRIGGER qname ... * Added possibility to specify 'IF EXIST' in following DROP commands: DROP AGGREGATE [ IF EXISTS ] qname ... DROP FUNCTION [ IF EXISTS ] qname ... DROP FILTER FUNCTION [ IF EXISTS ] qname ... DROP LOADER [ IF EXISTS ] qname ... DROP PROCEDURE [ IF EXISTS ] qname ... DROP TRIGGER [ IF EXISTS ] qname ... * Lots of changes to streamline the internal error handling. In principle, all errors should now include a SQLSTATE error code (see the SQL standard). * Added support for extracting the quarter (number between 1 and 4) of a date or a timestamp or a timestamp with timezone in SQL: EXTRACT ( QUARTER FROM my_date_expr ). Added support for extracting the week (number between 1 and 53) of a date or a timestamp or a timestamp with timezone in SQL: EXTRACT ( WEEK FROM my_date_expr ). Added support for scalar functions: quarter(date_expr), quarter(timestamp_expr) and quarter(timestamptz_expr). Bug Fixes * 3574: Add support for: create OR REPLACE view ... * 3824: Created table not visible from ODBC * 3831: Extend date part extraction and date formating functions to support more formats like "quarter", "iso year/week" * 6244: Add support for: TRUNCATE TABLE * 6249: DEFAULT in row-values missing (sqlsmith) * 6346: BATsort returns GDK_SUCCEED when **sorted bat is null * 6438: Implement functionality to enforce the restrict option in: DROP SCHEMA xyz RESTRICT; * 6507: Column Header coming with trailing spaces in compiled version of MonetDB 11.27.9/11 * 6513: Sqlitelogictest: Wrong MAL plan generation for column product * 6526: Crash using aggregate function inside a case statement in having clause * 6529: Sqlitelogictest crash in select query with IN operator and cast * 6530: Sqlitelogictest: select query with NOT IN giving wrong results * 6532: copy into ignore null as directive if first column doesn't come from file * 6534: [Mar2018]: mclient -f tab / --format=tab complains about "unsupported formatter" * 6535: [Mar2018]: mclient -t / --timer does not work as documented * 6536: [Mar2018]: timing output of mclient -t / --timer= should go to stderr rather than stdout * 6537: [Mar2018]: mclient's default timing mode should (again) be "none" rather than "clock" * 6541: [Mar2018]: mclient reports incorrect wall-clock time * 6542: assertion failure when querying: select count(*) from sys.commented_function_signatures; * 6543: Mar2018: truncate on SQL system tables should NOT be allowed * 6545: Sqlitelogictest crash in IN query * 6546: Sqlitelogictest crash in IN query with division * 6547: OS-dependent behaviour for ilike * 6548: Select from remote table leaves session open * 6549: Add log messages when listen fails * 6550: Sqlitelogictest crash on complex CASE statement * 6551: Sqlitelogictest wrong NULL value cast * 6552: Sqlitelogictest crash on complex case statement * 6553: Sqlitelogictest crash on aggregation with having statement * 6554: Sqlitelogictest crash on complex case statement * 6555: Sqlitelogictest wrong result set generated from complex case statement * 6556: Sqlitelogictest division by zero on COALESCE call * 6557: Sqlitelogictest crash on aggregation query with not in * 6559: rows in sys.statistics are not removed when a temporary table is dropped. * 6560: Sqlitelogictest crash on group by query with having in * 6561: Sqlitelogictest crash on group by query with having not in clause

1 0

RE: Adding sum() to a query appears to disable multithreading/parallel execution
by Anderson, David B 28 Mar '18

28 Mar '18

But couldn't the exact summation operate in parallel on each of the sub-groups coming from the group by statement? I guess I was thinking that part of the computation would be split up as well. Would it be difficult to add a psum() function that would drop bits (so understood to be an approximation), but still operate in parallel? Thanks, Dave -----Original Message----- From: users-list [mailto:users-list-bounces+david.b.anderson=citi.com@monetdb.org] On Behalf Of Sjoerd Mullender Sent: Tuesday, March 27, 2018 10:36 AM To: users-list(a)monetdb.org Subject: Re: Adding sum() to a query appears to disable multithreading/parallel execution The reason for the lack of parallelization in case of sum() is that the column is of type float. When summing floating point numbers we try to give the correct result, even if there are large differences between individual values (e.g. a column that has 1e37, 1e-37, and -1e37, the result should be 1e-37, not 0). For this reason, we use an algorithm that remembers all bits during summing, but we then cannot parallelize the sum since the bits that didn't fit in a float (or double) for the partial sums get lost. On 27/03/18 15:42, Anderson, David B wrote: > All, > > Not entirely sure how to submit a bug report for this, but it appears that adding a sum() term to a query completely disables parallel execution of the query. > > When I run this query on my server (11.27.13 built from source), linux RH6, 24 core, 190GB memory: > > select s.product, d.fctrdt, d.delinq, count(*), count(d.realized) from > dyn d, stt s where s.loanseqnum = d.loanseqnum group by s.product, > d.fctrdt, d.delinq order by s.product, d.fctrdt, d.delinq; > > It executes in ~20 seconds (the table stt contains ~20 million > records, and the table dyn contains ~550 million records), and when I > run top I can see the process using all 24 cores, > > but when I change the term count(d.realized) to sum(d.realized), the query now takes 5-6 minutes to run, and when I run top, the mserver process appears to use a single core for most of the query. > > select s.product, d.fctrdt, d.delinq, count(*), sum(d.realized) from > dyn d, stt s where s.loanseqnum = d.loanseqnum group by s.product, > d.fctrdt, d.delinq order by s.product, d.fctrdt, d.delinq; > > Is this expected behavior? Am I doing something wrong? Queries are both run on a hot server. > > Note: the explain from the query containing sum(d.realized) only contains 103 tuples, but the initial query with count(d.realized) contains 1052 tuples. > > Thanks, > Dave > > > Here are the definitions of the stt/dyn tables: > > create TABLE stt ( > product char(12) NULL, > prefix char(2) NULL, > coupon float NULL, > productionyear smallint NULL, > cusip char(9) NULL, > loanseqnum char(12) NULL, > loanpurpose char(1) NULL, > tpoflag char(1) NULL, > proptype char(2) NULL, > occstatus char(1) NULL, > numunits smallint NULL, > state char(2) NULL, > cs smallint NULL, > origterm smallint NULL, > origltv float NULL, > orignoterate float NULL, > origloanamt float NULL, > origwala smallint NULL, > origserv char(8) NULL, > govtagy char(1) NULL); > > create TABLE dyn ( > fctrdt DATE NULL, > loanseqnum char(12) NULL, > scheduled float NULL, > realized float NULL, > smm float NULL, > delinq int NULL, > paidahead int NULL); > > And here is the Merovingian log: > > $ more merovingian.log > 2018-03-26 09:17:16 MSG merovingian[26418]: Merovingian 1.7 > (Jul2017-SP4) starting > 2018-03-26 09:17:16 MSG merovingian[26418]: monitoring dbfarm > /opt/flash1/test/DB > 2018-03-26 09:17:16 MSG merovingian[26418]: Merovingian 1.7 stopped > 2018-03-26 09:17:16 ERR merovingian[26418]: binding to stream socket > port 50000 failed: Address already in use > 2018-03-26 09:17:16 ERR merovingian[26418]: fatal startup condition > encountered, aborting startup > 2018-03-26 09:18:15 MSG merovingian[28537]: Merovingian 1.7 > (Jul2017-SP4) starting > 2018-03-26 09:18:15 MSG merovingian[28537]: monitoring dbfarm > /opt/flash1/test/DB > 2018-03-26 09:18:15 MSG merovingian[28537]: accepting connections on > TCP socket localhost:50000 > 2018-03-26 09:18:15 MSG merovingian[28537]: accepting connections on > UNIX domain socket /tmp/.s.monetdb.50000 > 2018-03-26 09:18:15 MSG discovery[28537]: listening for UDP messages > on localhost:50000 > 2018-03-26 09:18:15 MSG control[28537]: accepting connections on UNIX > domain socket /tmp/.s.merovingian.50000 > 2018-03-26 09:18:15 MSG control[28537]: (local): created database 'loan' > 2018-03-26 09:18:15 MSG control[28537]: (local): served status list > 2018-03-26 09:18:15 MSG control[28537]: (local): released database 'loan' > 2018-03-26 09:18:15 MSG merovingian[28537]: starting database 'loan', > up min/avg/max: 0s/0s/0s, crash average: 0.00 0.00 0.00 (0-0=0) > 2018-03-26 09:18:15 MSG merovingian[28537]: proxying client (local) > for database 'loan' to > mapi:monetdb:///opt/flash1/test/DB/loan/.mapi.sock?database=loan > 2018-03-26 09:18:15 MSG merovingian[28537]: target connection is on > local UNIX domain socket, passing on filedescriptor instead of > proxying > 2018-03-26 09:18:15 MSG loan[28550]: arguments: > /opt/tools/root/MonetDB-11.27.13/bin/mserver5 > --dbpath=/opt/flash1/test/DB/loan --set > merovingian_uri=mapi:monetdb://malxcs1p:50000/loan --set > mapi_open=false --set mapi_port=0 - -set > mapi_usock=/opt/flash1/test/DB/loan/.mapi.sock --set > monet_vault_key=/opt/flash1/test/DB/loan/.vaultkey --set > gdk_nr_threads=24 --set max_clients=64 --set > sql_optimizer=default_pipe --set monet_daemon=yes > 2018-03-26 09:18:15 MSG loan[28550]: # MonetDB 5 server v11.27.13 "Jul2017-SP4" > 2018-03-26 09:18:15 MSG loan[28550]: # Serving database 'loan', using > 24 threads > 2018-03-26 09:18:15 MSG loan[28550]: # Compiled for > x86_64-pc-linux-gnu/64bit with 128bit integers > 2018-03-26 09:18:15 MSG loan[28550]: # Found 189.145 GiB available main-memory. > 2018-03-26 09:18:15 MSG loan[28550]: # Copyright (c) 1993 - July 2008 CWI. > 2018-03-26 09:18:15 MSG loan[28550]: # Copyright (c) August 2008 - > 2018 MonetDB B.V., all rights reserved > 2018-03-26 09:18:15 MSG loan[28550]: # Visit https://www.monetdb.org/ > for further information > 2018-03-26 09:18:15 MSG loan[28550]: # Listening for UNIX domain > connection requests on > mapi:monetdb:///opt/flash1/test/DB/loan/.mapi.sock > 2018-03-26 09:18:15 MSG loan[28550]: # MonetDB/SQL module loaded > 2018-03-26 09:18:16 MSG loan[28550]: # SQL catalog created, loading > sql scripts once > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 09_like.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 10_math.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 11_times.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 12_url.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 13_date.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 14_inet.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 15_querylog.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 16_tracelog.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 17_temporal.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 18_index.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 20_vacuum.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 21_dependency_functions.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 22_clients.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 23_skyserver.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 25_debug.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 26_sysmon.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 27_rejects.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 39_analytics.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 39_analytics_hge.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 40_json.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 40_json_hge.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 41_md5sum.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 45_uuid.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 46_profiler.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 51_sys_schema_extension.sql > 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: > 75_storagemodel.sql > > > > > > > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list > -- Sjoerd Mullender

3 3

DB Crashing Post Data Load
by Sharma, Sreejith 28 Mar '18

28 Mar '18

All, We have a scenario where we load the data via the shell scripts from .dat file using ControlM process. We notice the DB crashes intermittently post the load. Is there any limitation or settings on multiple session loads? Or what happens if load is parallel? 2018-03-27 00:21:54 MSG merovingian[45726]: database 'TEST_MDP' has crashed after start on 2018-03-13 05:04:41, attempting restart, up min/avg/max: 2m/4d/2w, crash average: 1.00 1.00 0.80 (35-9=26 MonetDB : 11.27.13 OS : CentOS 6 Regards, Sreejith

2 2

Adding sum() to a query appears to disable multithreading/parallel execution
by Anderson, David B 27 Mar '18

27 Mar '18

All, Not entirely sure how to submit a bug report for this, but it appears that adding a sum() term to a query completely disables parallel execution of the query. When I run this query on my server (11.27.13 built from source), linux RH6, 24 core, 190GB memory: select s.product, d.fctrdt, d.delinq, count(*), count(d.realized) from dyn d, stt s where s.loanseqnum = d.loanseqnum group by s.product, d.fctrdt, d.delinq order by s.product, d.fctrdt, d.delinq; It executes in ~20 seconds (the table stt contains ~20 million records, and the table dyn contains ~550 million records), and when I run top I can see the process using all 24 cores, but when I change the term count(d.realized) to sum(d.realized), the query now takes 5-6 minutes to run, and when I run top, the mserver process appears to use a single core for most of the query. select s.product, d.fctrdt, d.delinq, count(*), sum(d.realized) from dyn d, stt s where s.loanseqnum = d.loanseqnum group by s.product, d.fctrdt, d.delinq order by s.product, d.fctrdt, d.delinq; Is this expected behavior? Am I doing something wrong? Queries are both run on a hot server. Note: the explain from the query containing sum(d.realized) only contains 103 tuples, but the initial query with count(d.realized) contains 1052 tuples. Thanks, Dave Here are the definitions of the stt/dyn tables: create TABLE stt ( product char(12) NULL, prefix char(2) NULL, coupon float NULL, productionyear smallint NULL, cusip char(9) NULL, loanseqnum char(12) NULL, loanpurpose char(1) NULL, tpoflag char(1) NULL, proptype char(2) NULL, occstatus char(1) NULL, numunits smallint NULL, state char(2) NULL, cs smallint NULL, origterm smallint NULL, origltv float NULL, orignoterate float NULL, origloanamt float NULL, origwala smallint NULL, origserv char(8) NULL, govtagy char(1) NULL); create TABLE dyn ( fctrdt DATE NULL, loanseqnum char(12) NULL, scheduled float NULL, realized float NULL, smm float NULL, delinq int NULL, paidahead int NULL); And here is the Merovingian log: $ more merovingian.log 2018-03-26 09:17:16 MSG merovingian[26418]: Merovingian 1.7 (Jul2017-SP4) starting 2018-03-26 09:17:16 MSG merovingian[26418]: monitoring dbfarm /opt/flash1/test/DB 2018-03-26 09:17:16 MSG merovingian[26418]: Merovingian 1.7 stopped 2018-03-26 09:17:16 ERR merovingian[26418]: binding to stream socket port 50000 failed: Address already in use 2018-03-26 09:17:16 ERR merovingian[26418]: fatal startup condition encountered, aborting startup 2018-03-26 09:18:15 MSG merovingian[28537]: Merovingian 1.7 (Jul2017-SP4) starting 2018-03-26 09:18:15 MSG merovingian[28537]: monitoring dbfarm /opt/flash1/test/DB 2018-03-26 09:18:15 MSG merovingian[28537]: accepting connections on TCP socket localhost:50000 2018-03-26 09:18:15 MSG merovingian[28537]: accepting connections on UNIX domain socket /tmp/.s.monetdb.50000 2018-03-26 09:18:15 MSG discovery[28537]: listening for UDP messages on localhost:50000 2018-03-26 09:18:15 MSG control[28537]: accepting connections on UNIX domain socket /tmp/.s.merovingian.50000 2018-03-26 09:18:15 MSG control[28537]: (local): created database 'loan' 2018-03-26 09:18:15 MSG control[28537]: (local): served status list 2018-03-26 09:18:15 MSG control[28537]: (local): released database 'loan' 2018-03-26 09:18:15 MSG merovingian[28537]: starting database 'loan', up min/avg/max: 0s/0s/0s, crash average: 0.00 0.00 0.00 (0-0=0) 2018-03-26 09:18:15 MSG merovingian[28537]: proxying client (local) for database 'loan' to mapi:monetdb:///opt/flash1/test/DB/loan/.mapi.sock?database=loan 2018-03-26 09:18:15 MSG merovingian[28537]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2018-03-26 09:18:15 MSG loan[28550]: arguments: /opt/tools/root/MonetDB-11.27.13/bin/mserver5 --dbpath=/opt/flash1/test/DB/loan --set merovingian_uri=mapi:monetdb://malxcs1p:50000/loan --set mapi_open=false --set mapi_port=0 - -set mapi_usock=/opt/flash1/test/DB/loan/.mapi.sock --set monet_vault_key=/opt/flash1/test/DB/loan/.vaultkey --set gdk_nr_threads=24 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2018-03-26 09:18:15 MSG loan[28550]: # MonetDB 5 server v11.27.13 "Jul2017-SP4" 2018-03-26 09:18:15 MSG loan[28550]: # Serving database 'loan', using 24 threads 2018-03-26 09:18:15 MSG loan[28550]: # Compiled for x86_64-pc-linux-gnu/64bit with 128bit integers 2018-03-26 09:18:15 MSG loan[28550]: # Found 189.145 GiB available main-memory. 2018-03-26 09:18:15 MSG loan[28550]: # Copyright (c) 1993 - July 2008 CWI. 2018-03-26 09:18:15 MSG loan[28550]: # Copyright (c) August 2008 - 2018 MonetDB B.V., all rights reserved 2018-03-26 09:18:15 MSG loan[28550]: # Visit https://www.monetdb.org/ for further information 2018-03-26 09:18:15 MSG loan[28550]: # Listening for UNIX domain connection requests on mapi:monetdb:///opt/flash1/test/DB/loan/.mapi.sock 2018-03-26 09:18:15 MSG loan[28550]: # MonetDB/SQL module loaded 2018-03-26 09:18:16 MSG loan[28550]: # SQL catalog created, loading sql scripts once 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 09_like.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 10_math.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 11_times.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 12_url.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 13_date.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 14_inet.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 15_querylog.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 16_tracelog.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 17_temporal.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 18_index.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 20_vacuum.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 21_dependency_functions.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 22_clients.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 23_skyserver.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 25_debug.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 26_sysmon.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 27_rejects.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 39_analytics.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 39_analytics_hge.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 40_json.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 40_json_hge.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 41_md5sum.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 45_uuid.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 46_profiler.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 51_sys_schema_extension.sql 2018-03-26 09:18:16 MSG loan[28550]: # loading sql script: 75_storagemodel.sql

2 1

GDK reported error. MT_mremap: mremap failed. !OS: Cannot allocate memory
by RanchoYuan 26 Mar '18

26 Mar '18

total used free shared buff/cache available Mem: 17825792 59904 17765888 0 0 17765888 Swap: 33554432 0 33554432

1 0