- developers-list - monetdb.org

Slow query
by Luciano Sasso 24 Feb '16

24 Feb '16

Hi, I have MonetDB JUL2015-SP2, with table of 100M rows and 20 coluns, 4 of number type, others string type. A simple "select count(*) from table", run on 90 minutes. Hardware and SO is: Debian 8, 4 cores, 11G RAM and 20G free on disc. While monet run the query, command top, show 42G of virtual memory. Its normal ?, any idea ? thanks. -- Luciano Sasso Vieira Data Scientist & Solutions Architect luciano(a)gsgroup.com.br <http://www.gsgroup.com.br> | tel: 17 3353-0833 | cel: 17 99706-9335 www.gsgroup.com.br <http://www.gsgroup.com.br> --- Este email foi escaneado pelo Avast antivírus. https://www.avast.com/antivirus

1 0

Re: MonetDB: default - use same optimizer pipe for inlined queries.
by Stefan Manegold 03 Feb '16

03 Feb '16

cwi@achtbaan:~/manegold/source$ hg clone http://dev.monetdb.org/hg/MonetDB/ Het programma ‘hg’ is momenteel niet geïnstalleerd. Vraag uw beheerder om het pakket 'mercurial' te installeren om 'hg' uit te kunnen voeren nou, dan maar wget de tarball - werkt maar: cwi@achtbaan:~/manegold/build$ ../source/MonetDB-11.21.13/configure --enable-optimize --prefix=/home/cwi/manegold/prefix 2>&1 | tee config.out checking build system type... powerpc64le-unknown-linux-gnu checking host system type... powerpc64le-unknown-linux-gnu checking target system type... powerpc64le-unknown-linux-gnu checking for a BSD-compatible install... /usr/bin/install -c checking whether build environment is sane... yes checking for a thread-safe mkdir -p... /bin/mkdir -p checking for gawk... no checking for mawk... mawk checking whether make sets $(MAKE)... yes checking whether make supports nested variables... yes checking whether UID '1001' is supported by ustar format... yes checking whether GID '1001' is supported by ustar format... yes checking how to create a ustar tar archive... gnutar checking for pkg-config... no configure: error: pkg-config is required to configure MonetDB ... jammer ... Stefan ----- On Feb 3, 2016, at 10:45 AM, Niels Nes commits+niels=cwi.nl(a)monetdb.org wrote: > Changeset: 039a2b238d13 for MonetDB > URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=039a2b238d13 > Modified Files: > sql/backends/monet5/sql_optimizer.c > sql/backends/monet5/sql_optimizer.h > sql/backends/monet5/sql_scenario.c > Branch: default > Log Message: > > use same optimizer pipe for inlined queries. > > > diffs (107 lines): > > diff --git a/sql/backends/monet5/sql_optimizer.c > b/sql/backends/monet5/sql_optimizer.c > --- a/sql/backends/monet5/sql_optimizer.c > +++ b/sql/backends/monet5/sql_optimizer.c > @@ -215,8 +215,8 @@ addOptimizers(Client c, MalBlkPtr mb, ch > addtoMalBlkHistory(mb, "getStatistics"); > } > > -void > -addQueryToCache(Client c) > +str > +optimizeQuery(Client c) > { > MalBlkPtr mb; > backend *be; > @@ -226,13 +226,12 @@ addQueryToCache(Client c) > assert(be && be->mvc); /* SQL clients should always have their state set */ > pipe = getSQLoptimizer(be->mvc); > > - insertSymbol(c->nspace, c->curprg); > trimMalBlk(c->curprg->def); > c->blkmode = 0; > mb = c->curprg->def; > chkProgram(c->fdout, c->nspace, mb); > #ifdef _SQL_OPTIMIZER_DEBUG > - mnstr_printf(GDKout, "ADD QUERY TO CACHE\n"); > + mnstr_printf(GDKout, "Optimize query\n"); > printFunction(GDKout, mb, 0, LIST_MAL_ALL); > #endif > /* > @@ -250,23 +249,34 @@ addQueryToCache(Client c) > if (msg != MAL_SUCCEED) > GDKfree(msg); /* ignore error */ > } > - return; > + return NULL; > } > addOptimizers(c, mb, pipe); > msg = optimizeMALBlock(c, mb); > - if (msg != MAL_SUCCEED) { > - showScriptException(c->fdout, mb, 0, MAL, "%s", msg); > - GDKfree(msg); > - return; > - } > + if (msg) > + return msg; > > /* time to execute the optimizers */ > if (c->debug) > optimizerCheck(c, mb, "sql.baseline", -1, 0); > #ifdef _SQL_OPTIMIZER_DEBUG > - mnstr_printf(GDKout, "ADD optimized QUERY TO CACHE\n"); > + mnstr_printf(GDKout, "End Optimize Query\n"); > printFunction(GDKout, mb, 0, LIST_MAL_ALL); > #endif > + return NULL; > +} > + > +void > +addQueryToCache(Client c) > +{ > + str msg = NULL; > + > + insertSymbol(c->nspace, c->curprg); > + msg = optimizeQuery(c); > + if (msg != MAL_SUCCEED) { > + showScriptException(c->fdout, c->curprg->def, 0, MAL, "%s", msg); > + GDKfree(msg); > + } > } > > /* > diff --git a/sql/backends/monet5/sql_optimizer.h > b/sql/backends/monet5/sql_optimizer.h > --- a/sql/backends/monet5/sql_optimizer.h > +++ b/sql/backends/monet5/sql_optimizer.h > @@ -12,6 +12,7 @@ > > //#define _SQL_OPTIMIZER_DEBUG > > +sql5_export str optimizeQuery(Client c); > sql5_export void addQueryToCache(Client c); > sql5_export str SQLoptimizer(Client c); > sql5_export void SQLsetAccessMode(Client c); > diff --git a/sql/backends/monet5/sql_scenario.c > b/sql/backends/monet5/sql_scenario.c > --- a/sql/backends/monet5/sql_scenario.c > +++ b/sql/backends/monet5/sql_scenario.c > @@ -1263,17 +1263,20 @@ recompilequery: > > chkTypes(c->fdout, c->nspace, c->curprg->def, TRUE); /* resolve types */ > if (opt) { > + str msg = optimizeQuery(c); > + /* > MalBlkPtr mb = c->curprg->def; > > trimMalBlk(mb); > chkProgram(c->fdout, c->nspace, mb); > addOptimizers(c, mb, "default_pipe"); // TODO change to active pipe! > msg = optimizeMALBlock(c, mb); > + */ > if (msg != MAL_SUCCEED) { > sqlcleanup(m, err); > goto finalize; > } > - c->curprg->def = mb; > + //c->curprg->def = mb; > } > //printFunction(c->fdout, c->curprg->def, 0, LIST_MAL_ALL); > /* we know more in this case than chkProgram(c->fdout, c->nspace, > c->curprg->def); */ > _______________________________________________ > checkin-list mailing list > checkin-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/checkin-list -- | Stefan.Manegold(a)CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |

1 0

Drastic speed improvement for str.toLower() / str.toUpper
by Roberto Cornacchia 18 Jan '16

18 Jan '16

Hello, I had reported in https://www.monetdb.org/bugzilla/show_bug.cgi?id=3549 that string case conversion is very inefficient in MonetDB. I had a look at the code. For each UTF8 character it performs a hash lookup from the origin case bat and finds the corresponding character in the destination case bat. However, this is an overkill for ASCII characters: - for letters, [A-Z] + 32 = [a-z] - all other ASCII characters stay the same With the assumption that single-byte characters are very frequent in most texts, it makes sense to invest in a simple test and perform the hash lookup only for multi-byte characters. I tested this on 831MB (over 360K tuples) of standard English text: - original str.toLower/str.toUpper: 101 seconds (8 MB/s) - modified version: 3.6 seconds (230 MB/s) I guess that even when the text is highly multi-byte oriented the added test wouldn't hurt that much. A side-observation perhaps worth investigating is why that hash lookup is so expensive. Please find my patch in attachment. Roberto

2 3

Announcement: New Jul2015-SP2 Bugfix release of MonetDB suite
by Sjoerd Mullender 15 Jan '16

15 Jan '16

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jul2015-SP2 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <http://www.monetdb.org/>. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>. Jul 2015-SP2 bugfix release Bug Fixes * 2014: 'null' from copy into gets wrong * 3817: opt_pushselect stuck with multi-table UDF * 3835: windows does not release ram after operations * 3836: rand() only gets evaluated once when used as an expression * 3838: Update column with or without parenthesis produce different results * 3840: savepoints may crash the database * 3841: mclient fails with response "Challenge string is not valid" * 3842: SQL execution fails to finish and reports bogus error messages * 3845: Too many VALUES in INSERT freeze mserver5 * 3847: Wrong SQL results for a certain combination of GROUP BY / ORDER BY / LIMIT * 3848: mserver segfault during bulk loading/updating * 3849: HUGEINT incorrect value * 3850: DEL character not escaped * 3851: expression that should evaluate to FALSE evaluates to TRUE in SELECT query * 3852: CASE statement produces GDK error on multithreaded database: BATproject does not match always * 3854: Complex expression with comparison evaluates incorrectly in WHERE clause * 3855: Foreign key referencing table in a different schema - not allowed. * 3857: Large LIMIT in SELECT may abort the query * 3861: Using window functions cause a crash * 3864: Error in bulk import for chinese character * 3871: NOT x LIKE triggers "too many nested operators" * 3872: mserver crashes under specific combination of JOIN and WHERE conditions * 3873: mserver5: gdk_bat.c:1015: setcolprops: Assertion `x != ((void *)0) || col->type == 0' failed. * 3879: Database crashes when querying with several UNION ALLs. * 3887: Querying "sys"."tracelog" causes assertion violation and crash of mserver5 process * 3889: read only does not protect empty tables * 3895: read only does not protect this table

1 0

About JDBC Driver
by Luciano Sasso 07 Jan '16

07 Jan '16

Steps for error: 1- In testing with java client and JDBC 2:19, an error occurs when saving decimal field, for example the value 11:23 is recorded as 13. 2- When I run the same test with the 2.8 driver, saves normally and correct. Not only is the 2.19 driver, all above 2.8 is that same mistake. My Java client is Pentaho Data Integration, My MonetDB is JUL2015-SPI I go zip package test, and send fo tests. Att, -- Luciano Sasso Vieira Data Scientist & Solutions Architect luciano(a)gsgroup.com.br <http://www.gsgroup.com.br> | tel: 17 3353-0833 | cel: 17 99706-9335 www.gsgroup.com.br <http://www.gsgroup.com.br> --- Este email foi escaneado pelo Avast antivírus. https://www.avast.com/antivirus

1 0

Target Branch?
by Jörg Strebel 05 Jan '16

05 Jan '16

Hello! Thanks for the answer. What would be the branch to base my patches / developments on? I see two candidates in http://dev.monetdb.org/hg/MonetDB/branches: - default - Jul2015 Could you advise? Thanks! > The documentation for setting up the development environment and for > getting the source code is pretty good, but I am missing a process > description, how a volunteer can participate in the software > development process. E.g. how are bugs assigned, You could start by picking a bug, trying to fix it and then attach the patch to the bug description. We will have a a look at it for sure. > submitted, what needs to be done besides code production? Writing test cases for bugs without them is also a nice way of getting started. -- Jörg Strebel Aachener Straße 2 80804 München

2 1

How to contribute to MonetDB
by Jörg Strebel 04 Jan '16

04 Jan '16

Hello! I find MonetDB as a software product interesting and would be interested in contributing to its development (and learning about In-Memory databases in the process) . The documentation for setting up the development environment and for getting the source code is pretty good, but I am missing a process description, how a volunteer can participate in the software development process. E.g. how are bugs assigned, how are patches submitted, what needs to be done besides code production? Is there a description of sorts? Best regards Jörg -- Jörg Strebel Aachener Straße 2 80804 München

3 2

How is a query with 'string' columns in it, gets executed?
by bhuvan Mitra 04 Jan '16

04 Jan '16

Hello, I am looking into these column oriented databases for a while and wondered with monetdb's performance over others. I started studying about monet and found very interesting. Many concepts you use are really interesting. I want to know how monet executes a query which contain string columns in it. Does it maintain a dictionary or what kind of index it uses or is there any inverted index involved for strings etc., Any kind of material, source code portions or any other documentation regarding this, is most welcome. I would love to contribute back from my side once I get to know what is happening. Regards, Mit

2 1

MonetDB: reserved SQL keywords
by martin van dinther 14 Dec '15

14 Dec '15

Hi Hannes, FYI, we have a system table sys.keywords with all the reserved SQL keywords. The intention of this table is to have one place (in the server) with all the reserved SQL keywords, such that API implementations (ODBC, JDBC, PHP, Ruby, Python, R, etc) can query that table and construct the list or string. In the default branch I have implemented this already for jdbc/MonetDatabaseMetaData.java method getSQLKeywords(). This should reduce future maintenance of all the places where these reserved SQL keywords are hardcoded. The new keywords can simply be added/ in one place, the keywords table. It is filled in sql/scripts/51_sys_schema_extension.sql Regards, Martin van Dinther On 24-11-15 09:42, Hannes Muehleisen wrote: > Changeset: c0fb9a0d91e3 for MonetDB > URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=c0fb9a0d91e3 > Modified Files: > clients/R/MonetDB.R/R/dbi.R > Branch: embedded > Log Message: > > R Connector: More naughty words > > > diffs (65 lines): > > diff --git a/clients/R/MonetDB.R/R/dbi.R b/clients/R/MonetDB.R/R/dbi.R > --- a/clients/R/MonetDB.R/R/dbi.R > +++ b/clients/R/MonetDB.R/R/dbi.R > @@ -391,24 +391,43 @@ setMethod("dbSendQuery", signature(conn= > invisible(new("MonetDBEmbeddedResult", env=env)) > }) > > - > -reserved_monetdb_keywords <- c(.SQL92Keywords, > -"ADMIN", "AFTER", "AGGREGATE", "ALWAYS", "ASYMMETRIC", "ATOMIC", > -"AUTO_INCREMENT", "BEFORE", "BEST", "BIGINT", "BIGSERIAL", "BINARY", > -"BLOB", "CALL", "CHAIN", "CLOB", "COMMITTED", "COPY", "CROSS", > -"CURRENT_ROLE", "CURRENT_TIME", "CURRENT_USER", "DELIMITERS", > -"DO", "EACH", "EFFORT", "ELSEIF", "ENCRYPTED", "EXCLUDE", "FOLLOWING", > -"FUNCTION", "GENERATED", "HUGEINT", "IF", "ILIKE", "LIMIT", "LOCALTIME", > -"LOCALTIMESTAMP", "LOCKED", "MERGE", "NATURAL", "NEW", "NOCYCLE", > -"NOMAXVALUE", "NOMINVALUE", "OFFSET", "OLD", "ON", "OTHERS", > -"OVER", "PARTITION", "PRECEDING", "RANGE", "RECORDS", "REFERENCING", > -"REMOTE", "RENAME", "REPEATABLE", "REPLICA", "RESTART", "RETURN", > -"RETURNS", "SAMPLE", "SAVEPOINT", "SEQUENCE", "SERIAL", "SERIALIZABLE", > -"SESSION_USER", "SIMPLE", "SPLIT_PART", "STDIN", "STDOUT", "STREAM", > -"SYMMETRIC", "TIES", "TINYINT", "TRIGGER", "UNBOUNDED", "UNCOMMITTED", > -"UNENCRYPTED", "WHILE", "XMLAGG", "XMLATTRIBUTES", "XMLCOMMENT", > -"XMLCONCAT", "XMLDOCUMENT", "XMLELEMENT", "XMLFOREST", "XMLNAMESPACES", > -"XMLPARSE", "XMLPI", "XMLQUERY", "XMLSCHEMA", "XMLTEXT", "XMLVALIDATE") > +# found from sql_scan.c and trial/error > +reserved_monetdb_keywords <- sort(unique(toupper(c(.SQL92Keywords, > +"ADD", "ADMIN", "AFTER", "AGGREGATE", "ALL", "ALTER", "ALWAYS", > +"AND", "ANY", "ASC", "ASYMMETRIC", "ATOMIC", "AUTO_INCREMENT", > +"BEFORE", "BEGIN", "BEST", "BETWEEN", "BIGINT", "BIGSERIAL", > +"BINARY", "BLOB", "BY", "CALL", "CASCADE", "CASE", "CAST", "CHAIN", > +"CHAR", "CHARACTER", "CHECK", "CLOB", "COALESCE", "COMMIT", "COMMITTED", > +"CONSTRAINT", "CONVERT", "COPY", "CORRESPONDING", "CREATE", "CROSS", > +"CURRENT", "CURRENT_DATE", "CURRENT_ROLE", "CURRENT_TIME", "CURRENT_TIMESTAMP", > +"CURRENT_USER", "DAY", "DEC", "DECIMAL", "DECLARE", "DEFAULT", > +"DELETE", "DELIMITERS", "DESC", "DO", "DOUBLE", "DROP", "EACH", > +"EFFORT", "ELSE", "ELSEIF", "ENCRYPTED", "END", "ESCAPE", "EVERY", > +"EXCEPT", "EXCLUDE", "EXISTS", "EXTERNAL", "EXTRACT", "FALSE", > +"FLOAT", "FOLLOWING", "FOR", "FOREIGN", "FROM", "FULL", "FUNCTION", > +"GENERATED", "GLOBAL", "GRANT", "GROUP", "HAVING", "HOUR", "HUGEINT", > +"IDENTITY", "IF", "ILIKE", "IN", "INDEX", "INNER", "INSERT", > +"INT", "INTEGER", "INTERSECT", "INTO", "IS", "ISOLATION", "JOIN", > +"LEFT", "LIKE", "LIMIT", "LOCAL", "LOCALTIME", "LOCALTIMESTAMP", > +"LOCKED", "MEDIUMINT", "MERGE", "MINUTE", "MONTH", "NATURAL", > +"NEW", "NEXT", "NOCYCLE", "NOMAXVALUE", "NOMINVALUE", "NOT", > +"NOW", "NULL", "NULLIF", "NUMERIC", "OF", "OFFSET", "OLD", "ON", > +"ONLY", "OPTION", "OR", "ORDER", "OTHERS", "OUTER", "OVER", "PARTIAL", > +"PARTITION", "POSITION", "PRECEDING", "PRESERVE", "PRIMARY", > +"PRIVILEGES", "PROCEDURE", "PUBLIC", "RANGE", "READ", "REAL", > +"RECORDS", "REFERENCES", "REFERENCING", "REMOTE", "RENAME", "REPEATABLE", > +"REPLICA", "RESTART", "RESTRICT", "RETURN", "RETURNS", "REVOKE", > +"RIGHT", "ROLLBACK", "ROWS", "SAMPLE", "SAVEPOINT", "SECOND", > +"SELECT", "SEQUENCE", "SERIAL", "SERIALIZABLE", "SESSION_USER", > +"SET", "SIMPLE", "SMALLINT", "SOME", "SPLIT_PART", "STDIN", "STDOUT", > +"STORAGE", "STREAM", "STRING", "SUBSTRING", "SYMMETRIC", "THEN", > +"TIES", "TINYINT", "TO", "TRANSACTION", "TRIGGER", "TRUE", "UNBOUNDED", > +"UNCOMMITTED", "UNENCRYPTED", "UNION", "UNIQUE", "UPDATE", "USER", > +"USING", "VALUES", "VARCHAR", "VARYING", "VIEW", "WHEN", "WHERE", > +"WHILE", "WITH", "WORK", "WRITE", "XMLAGG", "XMLATTRIBUTES", > +"XMLCOMMENT", "XMLCONCAT", "XMLDOCUMENT", "XMLELEMENT", "XMLFOREST", > +"XMLNAMESPACES", "XMLPARSE", "XMLPI", "XMLQUERY", "XMLSCHEMA", > +"XMLTEXT", "XMLVALIDATE", "YEAR")))) > > # quoting > quoteIfNeeded <- function(conn, x, warn=T, ...) { > _______________________________________________ > checkin-list mailing list > checkin-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/checkin-list

2 1

code for primitives and operators
by Chenxi Li 14 Dec '15

14 Dec '15

Hi, Could you tell me which files have the code for primitives and operators? Thanks a lot. Best Regards, Chenxi Li

2 2