- users-list - monetdb.org

Re: Re: tomograph does not work
by Cheng Xuntao 06 Jun '14

06 Jun '14

Hi Martin, My Monetdb version is MonetDB Database Server v1.7 (Jan2014-SP2). I'm using Ubuntu 12.04 LTS Precise. My Stethoscope works perfectly displaying valuable information. However, I have some confusion about the metrics. Why some MAL statement has non-zero 'blk.reads' and 'blk.writes' but their rbytes and wbytes are zero? What exactly does these memory related metrics mean? Thanks! Xuntao On 06/06/2014 06:00 PM, users-list-request(a)monetdb.org wrote: > Send users-list mailing list submissions to > users-list(a)monetdb.org > > To subscribe or unsubscribe via the World Wide Web, visit > https://www.monetdb.org/mailman/listinfo/users-list > or, via email, send a message with subject or body 'help' to > users-list-request(a)monetdb.org > > You can reach the person managing the list at > users-list-owner(a)monetdb.org > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of users-list digest..." > > > Today's Topics: > > 1. tomograph does not work (Cheng Xuntao) > 2. Re: tomograph does not work (Martin Kersten) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Fri, 06 Jun 2014 11:18:29 +0800 > From: Cheng Xuntao <chengxuntao(a)gmail.com> > To: users-list(a)monetdb.org > Subject: tomograph does not work > Message-ID: <53913305.5090803(a)gmail.com> > Content-Type: text/plain; charset=ISO-8859-1; format=flowed > > Hi all, > > I need to have a visual view of the execution detail of each thread in > monetdb so I wish to use Tomograph. I followed this > https://www.monetdb.org/Documentation/Manuals/MonetDB/Profiler/tomograph > and have installed all required pacakges. But Tomograph does not respond > when I finished my queries. All its outputs are empty. However, my > Stethoscope works perfectly. > > Is there anything not listed in the Tomograph documentation that needs > to be done to get it work? > > Thanks! > > Xuntao > > > ------------------------------ > > Message: 2 > Date: Fri, 06 Jun 2014 08:26:00 +0200 > From: Martin Kersten <Martin.Kersten(a)cwi.nl> > To: users-list(a)monetdb.org > Subject: Re: tomograph does not work > Message-ID: <53915EF8.5030202(a)cwi.nl> > Content-Type: text/plain; charset=ISO-8859-1; format=flowed > > Hi > > Could you indicate which platform and MonetDB version you are using. > > regards, Martin > > > On 06/06/14 05:18, Cheng Xuntao wrote: >> Hi all, >> >> I need to have a visual view of the execution detail of each thread in >> monetdb so I wish to use Tomograph. I followed this >> https://www.monetdb.org/Documentation/Manuals/MonetDB/Profiler/tomograph >> and have installed all required pacakges. But Tomograph does not respond >> when I finished my queries. All its outputs are empty. However, my >> Stethoscope works perfectly. >> >> Is there anything not listed in the Tomograph documentation that needs >> to be done to get it work? >> >> Thanks! >> >> Xuntao >> _______________________________________________ >> users-list mailing list >> users-list(a)monetdb.org >> https://www.monetdb.org/mailman/listinfo/users-list > > ------------------------------ > > Subject: Digest Footer > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list > > > ------------------------------ > > End of users-list Digest, Vol 22, Issue 5 > *****************************************

2 1

tomograph does not work
by Cheng Xuntao 06 Jun '14

06 Jun '14

Hi all, I need to have a visual view of the execution detail of each thread in monetdb so I wish to use Tomograph. I followed this https://www.monetdb.org/Documentation/Manuals/MonetDB/Profiler/tomograph and have installed all required pacakges. But Tomograph does not respond when I finished my queries. All its outputs are empty. However, my Stethoscope works perfectly. Is there anything not listed in the Tomograph documentation that needs to be done to get it work? Thanks! Xuntao

2 1

Announcement: New Jan2014-SP2 Bugfix release of MonetDB suite
by Sjoerd Mullender 02 Jun '14

02 Jun '14

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jan2014-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/>. Jan 2014-SP2 bugfix release Build Environment * Lots of minor fixes were made for potential defects found by Coverity Scan. * On Windows we now build the geom module against version 3.4.2 of the geos library. Client Package * ODBC: Implemented {call procedure-name(...)} escape. The version {?=call ...} is not implemented. Bug Fixes * 3424: numeric values at the front of strings determines whether CAST works successfully * 3439: Python driver drops milliseconds from timestamps * 3446: SET READ ONLY forgets previous changes * 3455: String columns unusable from 64-bit .NET via ODBC * 3456: Insert fails * 3457: When kernel of remote client crashes, the connection remains established on server side * 3458: mserver5 crash on SQL: SELECT COUNT(*) FROM SYS.TABLES HAVING COUNT(*) > 0 * 3461: mserver5 crash on SQL: SELECT * FROM SYS.ARGS WHERE FUNC_ID NOT IN (SELECT ID FROM SYS.FUNCTIONS) OR FUNC_ID NOT IN (SELECT * FROM SYS.FUNCTIONS) * 3462: Invalid SQL (IN with subquery which returns multiple columns) is accepted * 3463: Crash on SELECT with SERIAL aggregation and GROUP BY column alias's * 3468: Local temporary table persists across sessions * 3469: Absolute network paths considered invalid for COPY INTO ... FROM statement. * 3473: Various memory leaks in SQL compilation * 3477: ODBC driver raises "unexpected end of input" for prepared string parameter from .NET application * 3481: Cannot run multiple COPY INTO statements in one 's'-command * 3482: Crossproduct error

3 9

how to count the number of query results in monetdb
by Bing Zhang 29 May '14

29 May '14

Hi: Is there a fast way to get the number of results, instead of looping on the ResultSet.next()? Thanks.

1 0

Processing web logs in monetdb
by Christopher Nelson 27 May '14

27 May '14

Hello, I've been looking at monetdb for a while, and I've been very impressed with the project. I would like to use monetdb to analyze web log data from a very active site. I have no doubt that it can handle the quantity of data, but I'm not sure how to perform some of the queries that I have used in other systems. For example, I would like in particular to be able to perform click-stream analysis of the data. On other systems I've used proprietary extensions which allowed me to create a window by pattern matching a certain column. Do you have any suggestions about where I should look to perform similar kinds of queries? Thanks! -={C}=-

2 1

question: efficient use of partitioned tables
by László Sándor 26 May '14

26 May '14

Hi, This might be less of MonetDB-specific question about features than about RDBMS (or just SQL) in general, but I would still appreciate your advice in these matters. I have data with a few "cross-sectional" tables (e.g. the parents of an individual) and many longitudinal/panel tables in annual chunks (e.g. tax records for each year separately). I wonder if it is a good idea to merge/union the annual tables into long panels. The benefits seems to be a much cleaner structure for the consistent columns, correspondingly easier code to write, fewer loops, tables names, lines of code. Also, fast joins on individual IDs and years across the tables without intermediary steps. However, the downsides are that not all columns are consistent across years, and there are simply some that appear only in certain years. Storing missing values for other years for these variables is a waste of disk space and memory. The merged tables could be too big anyway — if it matters in MonetDB (think of tables ~70 GB in some cases, while my server has 128 GB RAM), while many use cases would focus on a subset of years anyway. Do you have any thoughts about this? Maybe more concrete questions would be: — How cumbersome would it be (for a novice!) to code joining data from the annual tables separately, for multiple series of tables? (E.g. union all tax returns for 1997-2008, then join all residential data for these 12 years year-by-year, then employment records year-by-year etc.) — How much slower would this *run* than simply selecting years from the longitudinal tables? — Shall I be worried about the longitudinal tables having the size on the same order of magnitude as my physical memory, or MonetDB handles it equally efficiently (it would need to access many of the small annual tables anyway, that also takes memory). Thanks, Laszlo

1 0

Strange query performance
by N.H. Schuiling 24 May '14

24 May '14

Hi all, Can someone explain me why this simple query takes so long to execute on MonetDB 5 server v11.17.13 "Jan2014-SP1"? sql>select * from fp_tree limit 1; +-----------------------------+ | path | +=============================+ | ;8;60;379;519;1241;174;692; | +-----------------------------+ 1 tuple (1.2s) Table fp_tree has one column path with data type string. Some useful statistics of table fp_tree: sql>select count(*) from fp_tree; +--------+ | L1 | +========+ | 416499 | +--------+ 1 tuple (2.801ms) sql>select max(length(path)) from fp_tree; +------+ | L1 | +======+ | 1275 | +------+ 1 tuple (254.742ms) sql>select min(length(path)) from fp_tree; +------+ | L1 | +======+ | 3 | +------+ 1 tuple (272.186ms) sql>select avg(length(path)) from fp_tree; +--------------------------+ | L1 | +==========================+ | 202.73579288305606 | +--------------------------+ 1 tuple (331.306ms) Query trace: +---------+-------------------------------------------------------------------+ | ticks | stmt | +=========+===================================================================+ | 3 | X_2 := sql.mvc(); | | 29 | X_35:bat[:oid,:oid] =<tmp_440>[104124] := sql.tid(X_2=0,"sys","fp | : : _tree",0,4); : | 6 | X_8=<tmp_3501>[0] := sql.bind(X_2=0,"sys","fp_tree","path",1); | | 40 | (X_52:bat[:oid,:oid] =<tmp_2110>[0],X_53:bat[:oid,:str] =<tmp_350 | : : 1>[0]) := sql.bind(X_2=0,"sys","fp_tree","path",2,3,4); : | 8 | X_45:bat[:oid,:str] =<tmp_1357>[104127] := sql.bind(X_2=0,"sys"," | : : fp_tree","path",0,3,4); : | 3 | X_57=<tmp_1357>[104127] := sql.delta(X_45=<tmp_1357>:bat[:oid,:st | : : r][104127],X_52=<tmp_2110>:bat[:oid,:oid][0],X_53=<tmp_3501>:bat[ : : : :oid,:str][0],X_8=<tmp_3501>[0]); : | 4 | X_41:bat[:oid,:oid] =<tmp_1256>[104127] := sql.tid(X_2=0,"sys","f | : : p_tree",3,4); : | 8 | X_61=<tmp_302>[104127] := algebra.leftfetchjoin(X_41=<tmp_1256>:b | : : at[:oid,:oid][104127],X_57=<tmp_1357>[104127]); : | 10 | X_65=<tmpr_1256>[1] := algebra.subslice(X_61=<tmp_302>[104127],0: | : : wrd,0:wrd); : | 6 | X_71=<tmp_1357>[1] := algebra.leftfetchjoin(X_65=<tmpr_1256>[1],X | : : _61=<tmp_302>[104127]); : | 1 | language.pass(X_61=<tmp_302>[104127]); | | 24 | (X_50:bat[:oid,:oid] =<tmp_2110>[0],X_51:bat[:oid,:str] =<tmp_350 | : : 1>[0]) := sql.bind(X_2=0,"sys","fp_tree","path",2,2,4); : | 5 | X_44:bat[:oid,:str] =<tmp_1560>[104124] := sql.bind(X_2=0,"sys"," | : : fp_tree","path",0,2,4); : | 3 | X_56=<tmp_1560>[104124] := sql.delta(X_44=<tmp_1560>:bat[:oid,:st | : : r][104124],X_50=<tmp_2110>:bat[:oid,:oid][0],X_51=<tmp_3501>:bat[ : : : :oid,:str][0]); : | 3 | X_39:bat[:oid,:oid] =<tmp_1760>[104124] := sql.tid(X_2=0,"sys","f | : : p_tree",2,4); : | 5 | X_60=<tmp_302>[104124] := algebra.leftfetchjoin(X_39=<tmp_1760>:b | : : at[:oid,:oid][104124],X_56=<tmp_1560>[104124]); : | 8 | X_64=<tmpr_1760>[1] := algebra.subslice(X_60=<tmp_302>[104124],0: | : : wrd,0:wrd); : | 4 | X_70=<tmp_1560>[1] := algebra.leftfetchjoin(X_64=<tmpr_1760>[1],X | : : _60=<tmp_302>[104124]); : | 1 | language.pass(X_60=<tmp_302>[104124]); | | 21 | (X_48:bat[:oid,:oid] =<tmp_2110>[0],X_49:bat[:oid,:str] =<tmp_350 | : : 1>[0]) := sql.bind(X_2=0,"sys","fp_tree","path",2,1,4); : | 5 | X_43:bat[:oid,:str] =<tmp_1660>[104124] := sql.bind(X_2=0,"sys"," | : : fp_tree","path",0,1,4); : | 1 | X_55=<tmp_1660>[104124] := sql.delta(X_43=<tmp_1660>:bat[:oid,:st | : : r][104124],X_48=<tmp_2110>:bat[:oid,:oid][0],X_49=<tmp_3501>:bat[ : : : :oid,:str][0]); : | 3 | X_37:bat[:oid,:oid] =<tmp_1054>[104124] := sql.tid(X_2=0,"sys","f | : : p_tree",1,4); : | 5 | X_59=<tmp_302>[104124] := algebra.leftfetchjoin(X_37=<tmp_1054>:b | : : at[:oid,:oid][104124],X_55=<tmp_1660>[104124]); : | 8 | X_63=<tmpr_1054>[1] := algebra.subslice(X_59=<tmp_302>[104124],0: | : : wrd,0:wrd); : | 5 | X_69=<tmp_1660>[1] := algebra.leftfetchjoin(X_63=<tmpr_1054>[1],X | : : _59=<tmp_302>[104124]); : | 1 | language.pass(X_59=<tmp_302>[104124]); | | 23 | (X_46:bat[:oid,:oid] =<tmp_2110>[0],X_47:bat[:oid,:str] =<tmp_350 | : : 1>[0]) := sql.bind(X_2=0,"sys","fp_tree","path",2,0,4); : | 9 | X_42:bat[:oid,:str] =<tmp_462>[104124] := sql.bind(X_2=0,"sys","f | : : p_tree","path",0,0,4); : | 2 | X_54=<tmp_462>[104124] := sql.delta(X_42=<tmp_462>:bat[:oid,:str] | : : [104124],X_46=<tmp_2110>:bat[:oid,:oid][0],X_47=<tmp_3501>:bat[:o : : : id,:str][0]); : | 6 | X_58=<tmp_764>[104124] := algebra.leftfetchjoin(X_35=<tmp_440>:ba | : : t[:oid,:oid][104124],X_54=<tmp_462>[104124]); : | 8 | X_62=<tmpr_440>[1] := algebra.subslice(X_58=<tmp_764>[104124],0:w | : : rd,0:wrd); : | 10 | X_72=<tmp_462>[1] := mat.packIncrement(X_62=<tmpr_440>[1],4); | | 3 | X_74=<tmp_462>[2] := mat.packIncrement(X_72=<tmp_462>[2],X_63=<tm | : : pr_1054>[1]); : | 1 | language.pass(X_63=<tmpr_1054>[1]); | | 2 | X_75=<tmp_462>[3] := mat.packIncrement(X_74=<tmp_462>[3],X_64=<tm | : : pr_1760>[1]); : | 1 | language.pass(X_64=<tmpr_1760>[1]); | | 3 | X_66=<tmp_462>[4] := mat.packIncrement(X_75=<tmp_462>[4],X_65=<tm | : : pr_1256>[1]); : | 1 | language.pass(X_65=<tmpr_1256>[1]); | | 9 | X_11=<tmpr_1206>[1] := algebra.subslice(X_66=<tmp_462>[4],0:wrd,0 | : : :wrd); : | 5 | X_68=<tmp_1760>[1] := algebra.leftfetchjoin(X_62=<tmpr_440>[1],X_ | : : 58=<tmp_764>[104124]); : | 1 | language.pass(X_62=<tmpr_440>[1]); | | 6 | language.pass(X_58=<tmp_764>[104124]); | | 1019824 | X_77=<tmp_1256>[1] := mat.packIncrement(X_68=<tmp_1760>[1],4); | | 8 | X_78=<tmp_1256>[2] := mat.packIncrement(X_77=<tmp_1256>[2],X_69=< | : : tmp_1660>[1]); : | 7 | X_79=<tmp_1256>[3] := mat.packIncrement(X_78=<tmp_1256>[3],X_70=< | : : tmp_1560>[1]); : | 18 | X_67=<tmp_1256>[4] := mat.packIncrement(X_79=<tmp_1256>[4],X_71=< | : : tmp_1357>[1]); : | 18 | X_12=<tmp_1357>[1] := algebra.leftfetchjoin(X_11=<tmpr_1206>[1],X | : : _67=<tmp_1256>[4]); : | 1095479 | barrier X_89 := language.dataflow(); | | 13 | X_13 := sql.resultSet(1,1,X_12=<tmp_1357>[1]); | | 7 | sql.rsColumn(X_13=7,"sys.fp_tree","path","clob",0,0,X_12=<tmp_135 | : : 7>[1]); : | 2 | X_17 := io.stdout(); | | 33 | sql.exportResult(X_17=="104d2":streams,X_13=7); | | 0 | end s2_2; | | 1095725 | function user.s2_2(); | | 1095772 | X_5:void := user.s2_2(); | +---------+-------------------------------------------------------------------+ 56 tuples (1.1s) Why does mat.packIncrement takes so much time? Thanks in advance, Nik

1 0

How to use SciQL on MonetDB
by Yingwei Yu 23 May '14

23 May '14

Hello, I am new to MonetDB, and I am very interested in the SciQL functionality. I downloaded and installed the windows build of MonetDB. When I tried some SciQL on the client, it seems that it does not work. I followed the commands in paper Kersten et. al (2011) as follows: sql>start transaction; auto commit mode: off sql>CREATE ARRAY A1 ( more> x INTEGER DIMENSION[4], more> v FLOAT DEFAULT 0.0); syntax error, unexpected IDENT, expecting INDEX in: "create array" Do I miss something important? Please advise. Thanks, yingwei

2 1

ODBC driver cannot be removed (or repaired, upgraded), Jan2014-SP2 .msi files
by László Sándor 22 May '14

22 May '14

Hi, With today's Jan2014-SP2 update, I wanted to uninstall and reinstall using the .msi files in Windows Server 2012. (I also wanted to change the install locations, as I have no access to the default location on C to change the bat files and edit where my dbfarm should be.) Maybe because I ran MonetDB5-SQL-Installer-x86_64-20140514.msi before MonetDB-ODBC-Installer-x86_64-20140514.msi, but now the ODBC remove fails, repairing also. A desperate deletion of the entire C:/Program Files/MonetDB, did not make things better, maybe it made worse. Could you let me know how I could proceed with uninstalling the ODBC driver? I do want to have a clean, stable reinstall (under the new location) which I can trust. Thanks! Laszlo

1 2

ascii encoding error in block input (COPY INTO)
by László Sándor 21 May '14

21 May '14

Hi, I found previous notes<https://www.monetdb.org/pipermail/users-list/2010-November/004529.html>here that the server would expect UTF-8 input from the client or COPY INTO, so I need to work on my my Western ISO Latin 1 files. However, the error message is confusing about, COPY INTO complains about "'ascii' codec can't decode byte 0xc4 in position 8: ordinal not in range(128)." Shall I convert the data to UTF-8 then, or is that futile? What has the ascii codec to do with UTF-8? Thanks, Laszlo

1 0

COPY INTO on Windows
by László Sándor 21 May '14

21 May '14

Hi, I investigated further, and actually I cannot get any CSV file working with monetDB's COPY INTO on Windows. Hannes filed a bug report today: https://www.monetdb.org/bugzilla/show_bug.cgi?id=3484 However, I tried other things since his/our attempts, e.g. removing the final end-of-line (EOL, still CRLF on Windows) or switching EOL to LF only for the temporary file (and changing the COPY INTO call's delimiters too, of course). Neither helped. Still the same error: Incomplete record at end of file. (Which I am not sure whether it literally means the end of the source textfile or simply the last line before breaking.) Basically, the question is, is there a way to get this working on Windows in Jan14-SP1? Or could someone please verify it's worth downgrading to a previous because it definitely can do COPY INTO? Thanks! Laszlo

2 3

vacuum safe to use
by Bryan Senseman 20 May '14

20 May '14

All, Is the sys.vacuum call safe, anybody using it? I notice it is marked experimental. We have times when an entire data set is reprocessed and reloaded forcing a delete to occur, we have added this call after the delete and it seems to be working, however we still get a frequent number of database crashes. Thanx, Bryan -- ------------------------------ This transmission is confidential and intended solely for the use of the recipient named above. It may contain confidential, proprietary, or legally privileged information. If you are not the intended recipient, you are hereby notified that any unauthorized review, use, disclosure or distribution is strictly prohibited. If you have received this transmission in error, please contact the sender by reply e-mail and delete the original transmission and all copies from your system.

1 0

COPY INTO robust to empty row at end of CSV
by László Sándor 19 May '14

19 May '14

Hi all, Hannes Mühleisen (cc'd) generously shared his homepages.cwi.nl/~hannes/importer.py Python script with me last week, which automates the creation of tables from text files: Python's csv-reader helps recognize the dialect of the text file as well as grab the header for column names and identify the types for the columns, and then a single COPY %i OFFSET %i RECORDS INTO %s FROM \'%s\' USING DELIMITERS … is issued to MonetDB (see line 404 of the script). I had some issues with this over the last three days, but now I see the source of the error I get: There is nothing wrong with Hannes's Python code but when COPY INTO turns back to the text file, MonetDB still complains about the last row containing an "incomplete record" (it is empty) even if Python was smart enough not to count that row into the number of records beforehand. Is there a way to call COPY INTO that is robust to the text file ending in an extra empty row? It is common with some generated CSV or TSV files. By the way, I also have a follow-up about how to merge some of the tables together. I am happy to work on this, of course, I don't expect anyone to script this for me, but I am confused about how much I can use SQL variables for this (and how) versus how much I need to use another language to script the repeated calls to MonetDB about each year for each series of tables (I have dozens of dozens). I hope this is a reasonably well-posed question: http://dba.stackexchange.com/questions/65329/dynamic-sql-merge-partitioned-… Thanks for any help, Laszlo

2 7

type recognition, *creating* tables from CSVs
by László Sándor 15 May '14

15 May '14

Hi, I asked a question on DBA of StackExchange about MonetDB, but as there is not even a monetdb tag in use, maybe it wasn't the right forum for it. Please have a look over there, or I can repeat it below: http://dba.stackexchange.com/q/65126/38399 I am confused whether you can copy records into a *new* table in MonetDB. The official documentation seems[1] to talk only about copying into existing tables. I have quite a large universe of billions of records in dozens of tables, and I would much prefer a solution that inferred the type from the CSV itself, and the column names from the first rows. A StackOverflow answer[2] seems to work for SQL Server 2000 with `OpenRowset`, though even they are not concerned about data-type inferencing. Another answer[3] here on DBA recommends `BCP` before calling `BULK INSERT` in Server 2000, neither of which is discussed in the MonetDB documentation. Other answers[4] also suggest that this is impossible in MySQL without external scripts. The background of my issues are that I realized that managing my raw data (which came in text files from government agencies, with scarce documentation) should be separate from the front-end of my analysis still kept in Stata. This is similar to what has been achieved with MonetDB.R (e.g. hannes.muehleisen.org/SSDBM2013-databases-and-statistics.pdf) though probably limited to an odbc link (so missing some of the real benefits coming from merging and calculations done in MonetDB). That said, this is the only sign of a Stata and MonetDB link I could find: http://www.stata.com/statalist/archive/2012-08/msg01363.html Disclaimer: I am an SQL and MonetDB newbie, but thanks for not letting me miss the obvious. [1]: https://www.monetdb.org/Documentation/Manuals/SQLreference/CopyInto [2]: http://stackoverflow.com/a/10421034/938408 [3]: http://dba.stackexchange.com/a/22763/38399 [4]: http://dba.stackexchange.com/a/61969/38399

3 5

SELECT failure: "insufficient space for HEAP ..."
by Clarkson, Edward C. 14 May '14

14 May '14

Hi, We have an application (http://tmt.gtri.gatech.edu/lytic.html) based on a relatively modest, single (~1-10M row, ~20 column) MonetDB table (Win7, 64 bit, Jan14-SP1 server release, quad-core, 8GB RAM) and have been encountering sporadic SELECT query failures with the error: "Insufficient space for HEAP of 1017856 bytes". Since I've been trying in earnest to diagnose this, that byte value has been consistent, FWIW. The failures are frustratingly intermittent; if I rerun a failing query (by hand, using mclient) it sometimes fails again, and sometimes works correctly. I've been unable to figure out any sort of pattern. Unfortunately, I can't post the exact table and query, but the general form of the queries (which have ~5-10 case clauses) are: SELECT CASE WHEN Foo = 'ValueA' and Bar LIKE 'ValueB%' THEN 100*(Baz-1234)/1234 WHEN Foo = 'ValueC' and Bar LIKE 'ValueD%' THEN 100*(Baz-3456)/3456 ... END FROM table WHERE <same case statement as above> IS NOT NULL LIMIT 1 Any ideas what I might do to narrow this down or other information I might provide? I captured an explain of a failing query--it's many hundreds (1000s?) of lines, so I didn't want to include it here in full. All suggestions appreciated. Thanks, Ed P.S. MonetDB has been a fantastic complement to our application, thanks to everyone involved!

2 3

thank you for monetdb
by Anthony Damico 02 May '14

02 May '14

i love it this much: http://2.bp.blogspot.com/-ogFP6EHJ6h8/U1E225vANvI/AAAAAAAAww8/9wOEp95iekk/s…

2 1

why the same procedure (in shell or in sql) cost so different time?
by integrity 30 Apr '14

30 Apr '14

Hi All, I want to execute a batch of small sql stored procedures, for convenience, i put them totally in another big stored procedure: gwac_uniquecatalog, it cost 58 minutes: #!/bin/bash time mclient -d mydb -s "CALL gwac_uniquecatalog(99);" real 58m3.518s The time is so long that I tried to execute the small stored procedures one by one in SHELL, it only cost 6.5 minutes /usr/bin/time ./gwac_uniquecatalog.sh 99 2.82user 3.25system 6:30.39elapsed 1%CPU (0avgtext+0avgdata 10880maxresident)k 0inputs+0outputs (0major+980011minor)pagefaults 0swaps my question is why did this dramatic difference happen and how to reduce the time of big stored procedure? =========================================================== the content of the big stored procedure: gwac_uniquecatalog is CREATE PROCEDURE gwac_uniquecatalog(imgid int) BEGIN DECLARE imgid_t int; SET imgid_t =1; CALL insert_1_to_1_assoc(imgid_t); --对第一幅图 only insert into assoc is enough SET imgid_t = imgid_t +1; WHILE (imgid_t<=imgid) DO CALL insert_tempuniquecatalog(imgid_t,10.0); --10 is match radius 对第二幅图及以后的图 CALL find_n_to_m(); CALL insert_1_to_n_unique(); CALL insert_new_1_to_n_assoc(); CALL insert_1_to_n_assoc(); CALL delete_1_to_n_inactive_assoc(); CALL flag_1_to_n_inactive_uniq(); CALL flag_1_to_n_inactive_tempuniq(); CALL insert_1_to_1_assoc(imgid_t); CALL update_1_to_1_uniq(); CALL insert_new_uniq(imgid_t); CALL insert_new_assoc(imgid_t); SET imgid_t = imgid_t +1; END WHILE; END; =========================================== the SHELL code is: #!/bin/bash imgid_t=1 /usr/bin/time -f %e mclient -d mydb -s "CALL insert_1_to_1_assoc(imgid_t -le $1 ] do /usr/bin/time -f %e mclient -d mydb -s " CALL insert_tempuniquecatalog($imgid_t,10.0);" mclient -d mydb -s " CALL find_n_to_m();" mclient -d mydb -s " CALL insert_1_to_n_unique(); " mclient -d mydb -s " CALL insert_new_1_to_n_assoc();" mclient -d mydb -s " CALL insert_1_to_n_assoc();" mclient -d mydb -s " CALL delete_1_to_n_inactive_assoc();" mclient -d mydb -s " CALL flag_1_to_n_inactive_uniq();" mclient -d mydb -s " CALL flag_1_to_n_inactive_tempuniq();" mclient -d mydb -s " CALL insert_1_to_1_assoc($imgid_t);" mclient -d mydb -s " CALL update_1_to_1_uniq();" mclient -d mydb -s " CALL insert_new_uniq($imgid_t);" mclient -d mydb -s " CALL insert_new_assoc(imgid_t ((++imgid_t)) done Thanks very much! Best regards, Meng

4 8

procedure to follow when changing the sql-optimizer settings
by Mahmut Uludag 25 Apr '14

25 Apr '14

Hi, We were getting database crashes when we were using our database with read/write mode and using the default sql_optimizer settings. We decided to run our database in read-only mode and also has changed the sql_optimizer setting to sequential_pipe. With new settings we don't see any crashes but have started to see error messages in the log files similar to the following lines. 2014-04-25 08:21:52 ERR db1[14554]: #mvc_bind_schema schema1 2014-04-25 08:21:52 ERR db1[14554]: #mvc_bind_table schema1.table1 2014-04-25 08:21:52 ERR db1[14554]: #mvc_bind_column table1.col1 2014-04-25 08:21:52 ERR db1[14554]: #mvc_rollback 2014-04-25 08:21:52 ERR db1[14554]: #mvc_rollback done 2014-04-25 08:21:52 ERR db1[14554]: #mvc_commit 2014-04-25 08:21:52 ERR db1[14554]: #mvc_commit done My question is there a procedure to follow when we change sql-optimizer settings? I assume MonetDB has some history of the past and because of the change of the sql_optimizer settings the bits in the history causing these error messages but i would not be surprised if this is completely wrong. Mahmut Uludag

3 4

concurrency and use of temporary tables
by Niall Rooney 23 Apr '14

23 Apr '14

Hello, I am running a java program which connects to a MonetDB database and runs a thread where each thread creates a number of local temporary tables using the and populates these tables in certain data processing steps. On termination the temporary tables are deleted. These temporary tables are utilised in a number of select statements in combination with the tables in the database. Tables are created using a table name where a UUID is generated for each thread so the use of local tables is unique to the thread e.g. create local temporary table tempquerywords0e5a0e08c1d24cf6b9529426463b6191(word_word varchar(255)) ON COMMIT PRESERVE ROWS. All needed temporary tables are created by the thread as an initial process. The connection to the database is set with autoCommit set to true. If each thread is run sequentially , there is no problem. However if I run the process concurrently with a maximum of 2 concurrent threads, some threads terminate early with an Exception message e.g. nested exception is java.sql.SQLException: SELECT: no such table 'tempweightedclustersa7f7f47e7c184175ac8ab5ee2dc76a03'. This message is confusing as the table is populated ok before the select. Although other participants have posted messages concerning problems based on concurrent updates and temporary tables , is there an issue concerning concurrent reads and temporary tables? Best Regards Niall

1 0

TPC-H in MALs
by Qing Guo 23 Apr '14

23 Apr '14

Hi, I was trying to run “mserver” (standalone) in a simulator to do some performance study. As mserver only reads MALs, are there any ready-to-use MAL scripts for TPC-H? I tried to use “explain” in “mclient”, but the translated MALs seem to be too detailed and cannot simply copy and run. Any idea on this? Thank you! -Kevin

2 2

Build for Ubuntu 14.04 "Trusty Tahr"?
by Robert Kosara 19 Apr '14

19 Apr '14

Hi, I see Ubuntu 14.04 (which was just officially released a few days ago) listed on the Ubuntu/Debian packages list: http://dev.monetdb.org/downloads/deb/ However, the “trusty” directory doesn’t exist, and I can’t install MonetDB through apt-get. Will that build be enabled in the near future? Thanks, Robert

2 1

Overflow calculation in tpch Q1
by Tsai Li Ming 17 Apr '14

17 Apr '14

Hi, I’m getting an overflow calculation when SF=100 for Q1. The rest of the queries are fine. There’s an old thread on this from Sep 2013: https://www.monetdb.org/pipermail/users-list/2013-September/006817.html I’m using 11.17.13. Will this be fixed soon? Thanks!

1 0

Blob and Clob not implement in JDBC v 2.10?
by Phat Loc 11 Apr '14

11 Apr '14

Hello, I am not able to get Blob and Clob to work in JDBC v 2.10? I get a SQLFeatureNotSupportedException? Is this the correct behavior? Thanks, Phat

2 5

fastest way of having a number of rows of a table in monetdb ?
by Leon-Constantin Fopa 11 Apr '14

11 Apr '14

Helo everybody, To have the exact number of rows of a table t I actually run the following query SELECT COUNT(*) AS n FROM t; Is there a faster way to do this ? Best regards, Fopa

1 0

How to have the list of all tables names of a monetdb database ?
by Leon-Constantin Fopa 11 Apr '14

11 Apr '14

Hi everyone In postgresql one can have all tables names by running the following query SELECT table_name FROM information_schema.tables WHERE table_schema='public'; Is there something similar in monetdb to have the list of tables ? Best regards, Fopa

3 3