- users-list - monetdb.org

MonetDB - Transaction aborted issue
by Sharma, Sreejith 10 Apr '18

10 Apr '18

All, We are getting below error while creating a table. In the current session no other bulk load has happened. CREATE TABLE IF NOT EXISTS "SCHEMA_NAME"."TABLE_NAME" AS SELECT * FROM " SCHEMA_NAME "."TABLE_NAME" WHERE 1=0; INSERT INTO "SCHEMA_NAME"."TABLE_NAME" SELECT * FROM "SCHEMA_NAME"."TABLE_NAME2"; ERROR = !COMMIT: transaction is aborted because of concurrency conflicts, will ROLLBACK instead ~ Also, is there a way to update multiple tables at the same time without above error? Is this due to concurrent insert? Any help here is much appreciated! Regards, Sreejith

3 3

ordered index Error in optimizer garbageCollector
by Anton Kravchenko 06 Apr '18

06 Apr '18

Here is a reproducible example. create table t1(v0 int, v1 char(1)); insert into t1 values (1,'a'),(2,'b'),(3,'c'); create ordered index index_t1_v1 on t1(v1); create table t2(v1 char(1)); insert into t2 values ('a'); 1) gives "Error in optimizer garbageCollector" create temp table t3 as (select t1.v0 from t1,t2 where trim(t2.v1)=t1.v1) on commit preserve rows; 2) works as expected create temp table t3 as (select t1.v0 from t1,t2 where t2.v1=t1.v1) on commit preserve rows; 3) works as expected select t1.v0 from t1,t2 where trim(t2.v1)=t1.v1; 4) works as expected drop index index_t1_v1; create temp table t3 as (select t1.v0 from t1,t2 where trim(t2.v1)=t1.v1) on commit preserve rows; I am using the latest MonetDB version: MonetDB 5 server v11.29.3 "Mar2018" (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 31.3GiB available memory, 4 available cpu cores Libraries: libpcre: 8.32 2012-11-30 (compiled with 8.32) openssl: OpenSSL 1.0.2k 26 Jan 2017 (compiled with ) libxml2: 2.9.1 (compiled with 2.9.1) Compiled by: mockbuild@ (x86_64-redhat-linux-gnu) Compilation: gcc -std=gnu99 -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic Linking : /usr/bin/ld -m elf_x86_64 -Wl,-z,relro -Wl,-Bsymbolic-functions Thanks, Anton

2 1

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

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

26 Mar '18

I'm not sure that you can get the accessory(script). Here I put the text. expe.sh: #!/bin/bash BASEDIR=$(cd `dirname $0`;pwd -P) DAT_DIR=$BASEDIR/tpc-ds_test_data10G #drop tables if they are existed for i in $(ls $DAT_DIR/*.dat) do filename=$(echo $i | awk -F '/' '{print $NF}' | awk -F '.' '{print $1}') echo "start dropping table $filename" #./drop_tables.sh $filename mclient -s "drop table $filename;" echo "end dropping table $filename" done #create tables mclient /home/monetdb/v2.6.0/tools/tpcds.sql #load data for i in $(ls $DAT_DIR/*.dat) do filename=$(echo $i | awk -F '/' '{print $NF}' | awk -F '.' '{print $1}') #./autoinsert.sh $filename echo "start loading data to table $filename" mclient -s "copy into $filename from '$DAT_DIR/$filename.dat' using delimiters '|' null as '';" echo "end loading data to table $filename" done the query is showed in previous email.

1 0

Less verbose logs?
by Roberto Cornacchia 22 Mar '18

22 Mar '18

merovingian.log files on our production servers are dominated by these two lines: 2018-03-21 16:55:38 MSG merovingian[23237]: proxying client localhost.localdomain:51502 for database 'test' to mapi:monetdb://path/test/.mapi.sock?database=test 2018-03-21 16:55:38 MSG merovingian[23237]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying They account for 90% of the log. Is there a way to reduce verbosity?

1 1

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

22 Mar '18

The free disk space is 800G. And the query also fail when I run query right after a restart of the MonetDB server. And I'm not very sure what the mserver5 size means. When I run the query, the log file provide some information below, is that your mentioned? #GDKmremap(2465464320) fails, try to free up space [memory in use=149750256,virtual memory in use=4497801712] Also, I'm sorry that the information(query statement and result) I put in is incomplete by my negligence. I copy the query statement and result and error message again as followed: result and error message: GDK reported error. MT_mremap: mremap(0x7ffc80000000,2054553600,2465464320) failed !OS: Cannot allocate memory HEAPextend: failed to extend to 2465464320 for 55/5577.tail: GDKmremap() failed +-------------+--------------+-------+ | c_last_name | c_first_name | sales | +=============+==============+=======+ +-------------+--------------+-------+ 0 tuples query statement: with frequent_ss_items as ( select substr(i_item_desc,1,30) itemdesc ,i_item_sk item_sk ,d_date solddate ,count(*) cnt from store_sales ,date_dim ,item where ss_sold_date_sk = d_date_sk and ss_item_sk = i_item_sk and d_year in (1999,1999+1,1999+2,1999+3) group by itemdesc ,i_item_sk ,d_date having count(*) >4 ), max_store_sales as ( select max(csales) tpcds_cmax from ( select c_customer_sk ,sum(ss_quantity*ss_sales_price) csales from store_sales ,customer ,date_dim where ss_customer_sk = c_customer_sk and ss_sold_date_sk = d_date_sk and d_year in (1999,1999+1,1999+2,1999+3) group by c_customer_sk ) x ), best_ss_customer as ( select c_customer_sk ,sum(ss_quantity*ss_sales_price) ssales from store_sales ,customer where ss_customer_sk = c_customer_sk group by c_customer_sk having sum(ss_quantity*ss_sales_price) > (95/100.0) * ( select * from max_store_sales ) ) select sum(sales) from ( select cs_quantity*cs_list_price sales from catalog_sales ,date_dim where d_year = 1999 and d_moy = 1 and cs_sold_date_sk = d_date_sk and cs_item_sk in (select item_sk from frequent_ss_items) and cs_bill_customer_sk in (select c_customer_sk from best_ss_customer) union all select ws_quantity*ws_list_price sales from web_sales ,date_dim where d_year = 1999 and d_moy = 1 and ws_sold_date_sk = d_date_sk and ws_item_sk in (select item_sk from frequent_ss_items) and ws_bill_customer_sk in (select c_customer_sk from best_ss_customer)) y limit 100; with frequent_ss_items as (select substr(i_item_desc,1,30) itemdesc,i_item_sk item_sk,d_date solddate,count(*) cnt from store_sales ,date_dim ,item where ss_sold_date_sk = d_date_sk and ss_item_sk = i_item_sk and d_year in (1999,1999 + 1,1999 + 2,1999 + 3) group by itemdesc,i_item_sk,d_date having count(*) >4), max_store_sales as (select max(csales) tpcds_cmax from (select c_customer_sk,sum(ss_quantity*ss_sales_price) csales from store_sales ,customer ,date_dim where ss_customer_sk = c_customer_sk and ss_sold_date_sk = d_date_sk and d_year in (1999,1999+1,1999+2,1999+3) group by c_customer_sk) x), best_ss_customer as (select c_customer_sk,sum(ss_quantity*ss_sales_price) ssales from store_sales ,customer where ss_customer_sk = c_customer_sk group by c_customer_sk having sum(ss_quantity*ss_sales_price) > (95/100.0) * (select * from max_store_sales)) select c_last_name,c_first_name,sales from (select c_last_name,c_first_name,sum(cs_quantity*cs_list_price) sales from catalog_sales ,customer ,date_dim where d_year = 1999 and d_moy = 1 and cs_sold_date_sk = d_date_sk and cs_item_sk in (select item_sk from frequent_ss_items) and cs_bill_customer_sk in (select c_customer_sk from best_ss_customer) and cs_bill_customer_sk = c_customer_sk group by c_last_name,c_first_name union all select c_last_name,c_first_name,sum(ws_quantity*ws_list_price) sales from web_sales ,customer ,date_dim where d_year = 1999 and d_moy = 1 and ws_sold_date_sk = d_date_sk and ws_item_sk in (select item_sk from frequent_ss_items) and ws_bill_customer_sk in (select c_customer_sk from best_ss_customer) and ws_bill_customer_sk = c_customer_sk group by c_last_name,c_first_name) x order by c_last_name,c_first_name,sales limit 100; Thanks! Regards, Rancho

2 1