- users-list - monetdb.org

[MonetDB-users] Does Monetdb supports Date functions?
by Dongli 16 Feb '12

16 Feb '12

Hi guys, Does Monetdb supports "datediff" function? I didn't found this function while I run "select name from functions". Besides, Does Monetdb supports other Date relevant functions? Any advices will be highly appreciated. Thanks in advance, Tony

4 3

[MonetDB-users] Does Monetdb supports UTF-8 characters when I use 'as' clause?
by Dongli 15 Feb '12

15 Feb '12

Hi folks, Please take a look at following issue that I got today: The following queries I executed aim to test if Monetdb supports GBK characters(Chinese), since I have to use Chinese characters in my system to display the reports. I’m not sure if I missed some setting such as ‘set characters=’utf-8’? The following alias after the ‘as’ is a Chinese character ‘I’. sql>select source as '我' from session; 42000!syntax error, unexpected STRING in: "select source as '我'" sql>select source as 我 from session; unexpected character (U+6211) sql>select source as "我" from session; Invalid identifier '我' Thanks in advance, Tony

3 3

[MonetDB-users] database 'dw' has crashed after starting, manual intervention needed, check monetdbd's logfile for details
by Dongli 15 Feb '12

15 Feb '12

Hi folks, How can I recover from a crashed database? I got this error when I restart a running database 'dw', Do you have any ideas on this? 2012-02-15 13:59:28 MSG control[27007]: (local): served status list 2012-02-15 13:59:28 MSG merovingian[27007]: startup of database under maintenance 'dw' forced 2012-02-15 13:59:28 MSG merovingian[27007]: database 'dw' has crashed after start on 2012-02-15 13:58:47, attempting restart, up min/avg/max: 1d/1d/1d, crash average: 1.00 1.00 0.97 (45-1=44) 2012-02-15 13:59:28 MSG dw[27831]: arguments: /usr/lib/monetdb/bin/mserver5 --set gdk_dbfarm=/data/warehouse --dbname=dw --set merovingian_uri=mapi:monetdb://monet00:50000/dw --set mapi_open=false --set mapi_port=0 --set mapi_usock=/data/warehouse/dw/.mapi.sock --set monet_vault_key=/data/warehouse/dw/.vaultkey --set gdk_nr_threads=24 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2012-02-15 13:59:28 MSG dw[27831]: # MonetDB 5 server v11.7.5 "Dec2011" 2012-02-15 13:59:28 MSG dw[27831]: # Serving database 'dw', using 24 threads 2012-02-15 13:59:28 MSG dw[27831]: # Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked 2012-02-15 13:59:28 MSG dw[27831]: # Found 23.533 GiB available main-memory. 2012-02-15 13:59:28 MSG dw[27831]: # Copyright (c) 1993-July 2008 CWI. 2012-02-15 13:59:28 MSG dw[27831]: # Copyright (c) August 2008-2012 MonetDB B.V., all rights reserved 2012-02-15 13:59:28 MSG dw[27831]: # Visit http://www.monetdb.org/ for further information 2012-02-15 13:59:28 MSG dw[27831]: # Listening for UNIX domain connection requests on mapi:monetdb:///data/warehouse/dw/.mapi.sock 2012-02-15 13:59:28 MSG dw[27831]: # MonetDB/SQL module loaded 2012-02-15 13:59:28 MSG merovingian[27007]: database 'dw' (27831) was killed by signal SIGSEGV 2012-02-15 13:59:28 ERR control[27007]: (local): failed to fork mserver: database 'dw' has crashed after starting, manual intervention needed, check monetdbd's logfile for details >>The following is my platform information: MonetDB 5 server v11.7.5 "Dec2011" (64-bit, 64-bit oids) Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2012 MonetDB B.V., all rights reserved Visit http://www.monetdb.org/ for further information Found 23.5GiB available memory, 24 available cpu cores Libraries: libpcre: 6.6 06-Feb-2006 (compiled with 6.6) openssl: OpenSSL 0.9.8e-rhel5 01 Jul 2008 (compiled with OpenSSL 0.9.8e-fips-rhel5 01 Jul 2008) libxml2: 2.6.26 (compiled with 2.6.26) Compiled by: root@monet00 (x86_64-unknown-linux-gnu) Compilation: gcc -g -O2 Linking : /usr/bin/ld -m elf_x86_64 Thanks in advance, Tony

1 0

[MonetDB-users] failed to fork mserver: database 'dw' has crashed after starting, manual intervention needed, check monetdbd's logfile for details
by Tony 15 Feb '12

15 Feb '12

Hi folks, How can I recover from a crashed database? I got this error when I restart a running database 'dw', Do you have any ideas on this? 2012-02-15 13:59:28 MSG control[27007]: (local): served status list 2012-02-15 13:59:28 MSG merovingian[27007]: startup of database under maintenance 'dw' forced 2012-02-15 13:59:28 MSG merovingian[27007]: database 'dw' has crashed after start on 2012-02-15 13:58:47, attempting restart, up min/avg/max: 1d/1d/1d, crash average: 1.00 1.00 0.97 (45-1=44) 2012-02-15 13:59:28 MSG dw[27831]: arguments: /usr/lib/monetdb/bin/mserver5 --set gdk_dbfarm=/data/warehouse --dbname=dw --set merovingian_uri=mapi:monetdb://monet00:50000/dw --set mapi_open=false --set mapi_port=0 --set mapi_usock=/data/warehouse/dw/.mapi.sock --set monet_vault_key=/data/warehouse/dw/.vaultkey --set gdk_nr_threads=24 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2012-02-15 13:59:28 MSG dw[27831]: # MonetDB 5 server v11.7.5 "Dec2011" 2012-02-15 13:59:28 MSG dw[27831]: # Serving database 'dw', using 24 threads 2012-02-15 13:59:28 MSG dw[27831]: # Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked 2012-02-15 13:59:28 MSG dw[27831]: # Found 23.533 GiB available main-memory. 2012-02-15 13:59:28 MSG dw[27831]: # Copyright (c) 1993-July 2008 CWI. 2012-02-15 13:59:28 MSG dw[27831]: # Copyright (c) August 2008-2012 MonetDB B.V., all rights reserved 2012-02-15 13:59:28 MSG dw[27831]: # Visit http://www.monetdb.org/ for further information 2012-02-15 13:59:28 MSG dw[27831]: # Listening for UNIX domain connection requests on mapi:monetdb:///data/warehouse/dw/.mapi.sock 2012-02-15 13:59:28 MSG dw[27831]: # MonetDB/SQL module loaded 2012-02-15 13:59:28 MSG merovingian[27007]: database 'dw' (27831) was killed by signal SIGSEGV 2012-02-15 13:59:28 ERR control[27007]: (local): failed to fork mserver: database 'dw' has crashed after starting, manual intervention needed, check monetdbd's logfile for details >>The following is my platform information: MonetDB 5 server v11.7.5 "Dec2011" (64-bit, 64-bit oids) Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2012 MonetDB B.V., all rights reserved Visit http://www.monetdb.org/ for further information Found 23.5GiB available memory, 24 available cpu cores Libraries: libpcre: 6.6 06-Feb-2006 (compiled with 6.6) openssl: OpenSSL 0.9.8e-rhel5 01 Jul 2008 (compiled with OpenSSL 0.9.8e-fips-rhel5 01 Jul 2008) libxml2: 2.6.26 (compiled with 2.6.26) Compiled by: root@monet00 (x86_64-unknown-linux-gnu) Compilation: gcc -g -O2 Linking : /usr/bin/ld -m elf_x86_64 Thanks in advance, Tony

2 1

[MonetDB-users] Odd Single Thread Only Processing in Dec 2011 Release
by Henry Addington 15 Feb '12

15 Feb '12

Hi: We are testing Dec 2011 Release to upgrade from Oct 2010 SP1 release on CentOS 5.2 64-bit with 100+ GB memory. We have a BI tool that users use to generate various reports. We have access to the SQL statements that get generated by the BI tool and sent to the MonetDB. So, we know exactly the SQL statements that are being sent to MonetDB. One very odd thing that we are seeing the December release is that the reports from the BI tool are very, very slow (x10 - x15 from Oct releasee). It is obvious why looking at the server resource use. It looks like only 1 cpu is being used for all the queries! However, if I take the SQL statements generated by the BI tool and run them in DBVisualizer, all 20 cpu cores are used and the results return quickly. I am at a loss as to why we are seeing such different performance from identical SQL statements... Is there a configuration setting or something in the December release that we overlooked? Can you please help us troubleshoot? Thank you. HS

2 3

[MonetDB-users] select distinct ... where algorithm
by zack_pcd 13 Feb '12

13 Feb '12

I am seeing the following: select count(*) from f_sorted; +-----------+ | L7 | +===========+ | 102612097 | +-----------+ 1 tuple (0.988ms) select count(*) from f_sorted where yyyymm='201110'; +----------+ | L10 | +==========+ | 23505343 | +----------+ 1 tuple (1.0s) select count(distinct uuid) from f_sorted where yyyymm='201110'; +---------+ | L6 | +=========+ | 5941581 | +---------+ 1 tuple (23.8s) Very fast thus far! However: copy (select distinct(uuid) from f_sorted where yyyymm='201110') into '/home/user/filename' USING DELIMITERS ',','\n','"' NULL as 'null'; This has been running for hours and has yet to start writing to the file. I get this when I explain the query: +----------------------------------------------------------------------------------------+ | mal | +========================================================================================+ | function user.s5_3{autoCommit=true}():void; | | X_2 := sql.mvc(); | | X_22:bat[:oid,:str] := sql.bind(X_2,"sys","f_sorted","yyyymm",0); | | X_23 := algebra.uselect(X_22,"201110":str); | | X_20:bat[:oid,:str] := sql.bind(X_2,"sys","f_sorted","yyyymm",2); | | X_24 := algebra.kdifference(X_23,X_20); | | X_21 := algebra.uselect(X_20,"201110":str); | | X_25 := algebra.kunion(X_24,X_21); | | X_18:bat[:oid,:str] := sql.bind(X_2,"sys","f_sorted","yyyymm",1); | | X_19 := algebra.uselect(X_18,"201110":str); | | X_26 := algebra.kunion(X_25,X_19); | | X_16:bat[:oid,:oid] := sql.bind_dbat(X_2,"sys","f_sorted",1); | | X_17 := bat.reverse(X_16); | | X_27 := algebra.kdifference(X_26,X_17); | | X_28 := algebra.markT(X_27,0@0:oid); | | X_29 := bat.reverse(X_28); | | X_10:bat[:oid,:str] := sql.bind(X_2,"sys","f_sorted","uuid",0); | | X_8:bat[:oid,:str] := sql.bind(X_2,"sys","f_sorted","uuid",2); | | X_12 := algebra.kdifference(X_10,X_8); | | X_13 := algebra.kunion(X_12,X_8); | | X_5:bat[:oid,:str] := sql.bind(X_2,"sys","f_sorted","uuid",1); | | X_14 := algebra.kunion(X_13,X_5); | | X_30 := algebra.leftjoin(X_29,X_14); | | (ext44,grp42) := group.done(X_30); | | X_33 := bat.mirror(ext44); | | X_34 := algebra.leftjoin(X_33,X_30); | | X_35 := sql.resultSet(1,1,X_34); | | sql.rsColumn(X_35,"sys.f_sorted","uuid":str,"varchar",2048,0,X_34); | | X_43 := io.stdout(); | | sql.exportResult(X_43,X_35); | | end s5_3; | +----------------------------------------------------------------------------------------+ 31 tuples (1.350ms) Is it doing something wrong? I don't expect the query to take so long (hours) to produce the output, especially since the distinct count is also very fast. The copy-into *seems* to be irrelevant to the problem, but I decided to include it just in case, as that's how I am using the query. -- View this message in context: http://old.nabble.com/select-distinct-...-where-algorithm-tp33297578p332975… Sent from the monetdb-users mailing list archive at Nabble.com.

4 6

[MonetDB-users] False dependency
by Stathis Gkotsis 10 Feb '12

10 Feb '12

Hi all, trying to install: libmonetdb-client-dev_11.7.5-20120120_amd64.deb on Ubuntu 10.04, I see that there is a wrong dependency on libmonetdb-client1. Is there a way around this? Thank you.

1 0

[MonetDB-users] re cycler_pipe and cracker_pipe
by zack_pcd 08 Feb '12

08 Feb '12

Is it possible to have a mix of these pipe's optimizers so you get the benefit of both? Or are these two pipes incompatible? (recycler_pipe and cracker_pipe) -- View this message in context: http://old.nabble.com/recycler_pipe-and-cracker_pipe-tp33282420p33282420.ht… Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] LIMIT clause not recognized in nested query in FROM clause
by Viktor Rosenfeld 07 Feb '12

07 Feb '12

Hi, it appears that clauses like LIMIT or OFFSET are not allowed if the query is used inside a FROM clause. The following query throws an error: SELECT * FROM ( SELECT id FROM corpus LIMIT 10 ) AS inner_table; The error message is: syntax error, unexpected LIMIT, expecting INTERSECT or EXCEPT or UNION or ')' in: "select * Cheers, Viktor

1 0

[MonetDB-users] How can I execute SQL via mclient directly?
by bai dongli 02 Feb '12

02 Feb '12

Hi guys, I'm pretty new for Monetdb, As per the introduce of the online document, I have to type the password on the command line window when I want to execute SQL via the mclient program. It there a way like this which can be used in Mysql: "mysql --user --password databasename -e SQL" in monetdb? I didn't found the argument that can used for the "password" in mclient. Thanks, Tony

2 4

[MonetDB-users] Can't drop "global temporary table" in SCHEMA tmp
by bai dongli 02 Feb '12

02 Feb '12

Hi guys, I got a problem when I trying to drop a global temporary table in my database, I got following errors: sql>drop table tmp.test; 42S02!DROP TABLE: no such table 'test' I got the same error as above when I trying to execute a "select" query on that table, It's so weird, cause the table "test" was been there: sql>\d TABLE tmp.test The even more weird things happened when I execute following command: sql>create global temporary table tmp.test(id int); operation successful (1.888ms) sql>\d TABLE tmp.test sql>\d test table tmp.test is not unique, corrupt catalog? sql>\d TABLE tmp.test sql>drop table tmp.test more>; operation successful (1.470ms) sql>\d TABLE tmp.test Please pay attention on this line "table tmp.test is not unique, corrupt catalog?". Do anybody can help me out of here? Thanks, Tony

1 0

[MonetDB-users] Batch sql inserts in monetdb
by Federico Dayan 01 Feb '12

01 Feb '12

Hello all, I'm working in a project and we are planning to use monetdb. Our Data: We have around 15 millon users and 6,000 columns. Our Problem We need to be able to query specific log data. We use Hadoop to massage the data and create a sql file with sql inserts (around 15 millon insert with 3000 columns) Out Question Is it efficent to run a command like. By efficent I refe:. betweent 1 to 6 hours efficent, after that, a big problem. monetdb -u ....... < my_sql_insert.sql with 15 millon inserts and between 1000 to 3000 columns? (I'm not always going to create insert for the 6000 columns) could monetdb handle multiple of those command if a split the file. monetdb -u ....... < my_sql_insert_1.sql monetdb -u ....... < my_sql_insert_2.sql monetdb -u ....... < my_sql_insert_3.sql ... Is SQL COPY more efficient than a sql scrip file with inserts? BAT files is it significant faster ? I'm trying to avoid this approach since it seems a little bit obscue. any other suggestion? I'm not able to run all this test and get the info myself becuase I will not have a monetdb ready soon. Thank you very much and I would be glad to send back an email with the results I got and contribute in that way. Federico

4 3

[MonetDB-users] size of table
by Pavel Stehule 30 Jan '12

30 Jan '12

Hello I can't to search in doc a way to get size of tables. Is there some function or statement how to do it? Thank you Pavel Stehule

3 3

[MonetDB-users] Best backup strategy
by Sékine Coulibaly 27 Jan '12

27 Jan '12

Hi there, Thanks again for your last replies regarding the insertion into base. Using COPY INTO worked like a charm. A production deployment means : - Backup - High availability So my questions here, not addressed in the online docs are : - Regarding backup, is there a way to make a consistent backup (I mean putting the DB in a "backuping state", and save/archive the files) - To allow for high availability, can I use clusterization ? Can horizontal scalability be achieved through clusterization ? Thank you !

1 0

[MonetDB-users] Announcement: New Dec2011 Feature release of MonetDB suite
by Sjoerd Mullender 26 Jan '12

26 Jan '12

The MonetDB team at CWI/MonetDB BV is pleased to announce the Dec2011 feature 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/>. Dec 2011 feature release SQL * mclient: The csv output format can now also be of the form csv=c and csv+c where c is the column separator. The form with csv+ produces a single header line with column names. * Changed a bug where the sign() function returned the same type as its argument instead of always an INTEGER. * Added a fix for bug #2834, which caused weird (failing) behaviour with PreparedStatements. * Fixed a bug when using default values for interval columns. Bug 2877. * A bug was fixed where deleted rows weren't properly accounted for in all operations. This was bug 2882. * A bug was fixed which caused an update to an internal table to happen too soon. The bug could be observed on a multicore system with a query INSERT INTO t (SELECT * FROM t) when the table t is "large enough". This was bug 2883. Java Module * Implemented getMetaData() method of PreparedStatement. * Fixed an AssertionError for special results from e.g. EXPLAIN queries. * Fixed overflow error when batching large statements, bug #2952 * Resolved a concurrency problem where ResultSet's date-related getters could cause odd stack traces when used by multiple threads at the same time, bug #2950. * JDBC now implements JDBCv4.1 which makes it possible to be built with Java 7 and up. JDBCv4.1 is a maintenance release of JDBCv4, and hence can be compiled with Java 6, although the added methods obviously are not part of the java.sql interfaces. * SQLExceptions thrown now carry a SQLSTATE. Until the server starts sending correct SQLSTATEs for all errors, server originated errors without SQLSTATE are considered generic data exceptions (22000). * Fixed a bug where closing ResultSets and PreparedStatements could lead to errors on concurrent running queries using the same Connection due to a race condition. * Changed version scheme of JDBC driver and MCL jar to be more standard, from monetdb-X.Y-<thing>.jar to monetdb-<thing>-X.Y.jar, bug #2943 * The embedded monet instance capability of MonetConnection was removed. * Bump JDBC version to 2.0 (Liberica). JDBC now implements JDBCv4 which makes it possible to be built with Java 6 and up. Java 5 and before are no longer supported, and can use the 1.x releases of the driver. * INTERVAL columns are now treated as decimals, since they can have sub-second precision. Client Package * Fix making connections with stethoscope to hosts without monetdbd. Bug #2944. * Fixed some bugs in the PHP interface affecting the mapi_ping(), monetdb_insert_id() and auto_commit() functions. Bugs #2936, #2937, #2938. * ODBC: Fixed SQLNumResultCols and SQLDescribeCol to return useful information after a call to SQLPrepare and before any SQLExecute. * mclient: The exact interpretation of the -i (--interactive) option and the - filename argument have changed. The - filename argument means read from standard input and no longer implies that no \ interpretation is done on the input. Instead, \ interpretation is done if either standard input is a terminal, or if the -i option is given. The -i option no longer causes mclient to read from standard input. It only means to do \ interpretation when reading from standard input. Use the - filename argument to read from standard input. Note that if no -s option is specified and no filename arguments are present, mclient still reads from standard input. * ODBC: Improved internal query for SQLSpecialColumns. Before, the query returned all columns taking part in a PRIMARY KEY *and* all columns taking part in a UNIQUE constraint. Now it returns only one or the other set. * ODBC: The database name is now used as the catalog name throughout. Functions that return a catalog name return the database name, and functions that match on catalog name match it with the database name. * ODBC: Implemented an easier way to create a log file of interactions with the ODBC driver. You can now add a connection attribute "LOGFILE=filename" to the connection string parameter of SQLBrowseConnect and SQLDriverConnect, and to the relevant part of the Windows registry or odbc.ini file. This value is only used if there is no environment variable ODBCDEBUG. * Quoting of object names for mclient's \d command is now more flexible and consistent with standard SQL quoting rules, bug #2846. * ODBC: Implemented SQLColumnPrivileges function. * Install new program, mnc, which provides netcat functionality, but based on MonetDB's communication libraries * Report full URI to database instead of just database when available in mclient. * The time format of the timer output can now be controlled with an optional argument to the -i option. ms, s and m force the time to be formatted as milliseconds, seconds or minutes + seconds respectively. * ODBC: Implemented a workaround in SQLTables for bug 2908. * Small improvement to mclient's table rendering for tables without any rows. Previously, the column names in the header could be squeezed to very small widths, degrading readability. * Python DB API connect() function now supports PEP 249-style arguments user and host, bug #2901 * mclient now checks the result of encoding conversions using the iconv library. * Fixed a source of crashes in mclient when a query on the command line using the -s option is combined with input on standard input (e.g. in the construct mclient -s 'COPY INTO t FROM STDIN ...' < file.csv). * Fixed bug 2897 where slow (network) reads could cause blocks to not be fully read in one go, causing errors in the subsequent use of those blocks. With thanks to Rémy Chibois. * Fixed a bug in mclient where processing queries from files could result in ghost empty results to be reported in the output * Fixed Perl DBD rowcount for larger results, bug #2889 * Perl: We now distinguish properly between TABLE and GLOBAL TEMPORARY (the latter are recognized by being in the "tmp" schema). * Perl: fixed a bunch of syntax errors. This fixes bug 2884. With thanks to Rémy Chibois. * Perl: Fixed DBD::monetdb table_info and tabletype_info. This fixes bug 2885. With thanks to Rémy Chibois. * mclient: fix display of varchar columns with only NULL values. * Fixed a bug in mclient/msqldump where an internal error occurred during dump when there are GLOBAL TEMPORARY tables. MonetDB5 Server * A couple of memory leaks have been plugged. * Introduced new variable max_clients that allows to define how many concurrent connections are allowed to be made against the database. * mal_interpreter.mx Protect against concurrent exceptions If multiple parallel blocks access the exception variables and perform GDKfree on old messages, then we may enter a case that one thread attempts a second free. Simple lock-based protection is the first line of defense. * bpm and partitions have been moved to the attic. It is replaced by the partition optimizer, still under construction. * The MAL debugger next/step operations semantics have been fixed. The profiler also now clearly shows entrance/exit of a MAL function. * The dataflow optimizer uses a less strict side-effect test for BAT new operations and better recognizes trivial plans. * Fixed a problem where MAL variables weren't properly cleared before reuse of the data strucutre. This problem could cause the data flow scheduler to generate dependencies between instructions that didn't actually exist, which in turn could cause circular dependencies among instructions with deadlock as a result. Bugs 2865 and 2888. Merovingian * Fixed crash in monetdb when an invalid property was retrieved using the get command, bug #2953. * monetdb get output is now grouped by database instead of by property * Unlike in previous releases, 'monetdbd get mserver' now returns the path to the mserver5 binary only for dbfarms that have a running monetdbd, instead of only for those that are not served by a monetdbd. * Add nclients property that controls the maximum number of concurrent clients allowed to access the database * Starting monetdbd without any arguments or without dbfarm is no longer supported. A dbfarm now must be provided for each command. * The control passphrase has been turned into a hash of the password, for a more stronger authorisation model as used by mclients. On upgrade, any existing passphrase is converted to the hashed version of the passphrase. * The monetdbd controlport option has been removed in favour of a boolean control option. On upgrade, when controlport was set to a non-zero value and a passphrase was set, control is set to true, or false otherwise. * The monetdbd discoveryport option has been removed in favour of a boolean discovery option. On upgrade, when discoveryport was set to a non-zero value, discovery is set to true, or false otherwise. * Fixed a bug where monetdbd's socket files from /tmp were removed when a second monetdbd was attempted to be started using the same port. * Resolved problem where monetdbd would terminate abnormally when databases named 'control', 'discovery' or 'merovingian' were stopped. * monetdbd get status now also reports the version of the running monetdbd * Improved response time of 'monetdb start' when the database fails to start. * Fixed a bug in monetdbd where starting a failing database could incorrectly be reported as a 'running but dead' database. * To avoid confusion, all occurrences of merovingian were changed into monetdbd for error messages sent to a client. MonetDB Common * Fixed memory detection on 32-bits Solaris systems with more memory than can be addressed in 32-bits mode, bug #2935 Bug Fixes

1 0

[MonetDB-users] measure performance time
by jfergar 26 Jan '12

26 Jan '12

Hi, I need to measure the sql query execution time of several queries. The mclient "-t" option does not exist in the newer version, and the "\< file_with_queries" SQL command does not seem to show the time when importing from a file. Any ideas?? -- View this message in context: http://old.nabble.com/measure-performance-time-tp33201681p33201681.html Sent from the monetdb-users mailing list archive at Nabble.com.

2 3

[MonetDB-users] Strange disk I/O behavior
by Viktor Rosenfeld 26 Jan '12

26 Jan '12

Hi, I'm observing disk I/O when evaluating some queries which I am unable to explain. I have two datasets, one relatively small with the BATs taking up 1.6 MB on disk and one fairly large with 283 MB on disk. According to iostat, when I evaluate the attached query on the small dataset, 100 MB are written to disk, but none are read. This is on a Linux machine with 4 GB RAM running the Aug2011-SP3 release. I don't understand why data is written on disk, that (a) is never read again and (b) would easily fit into main memory. I am unable to evaluate the queries on the large dataset because MonetDB writes so much data that the disk fills up after a few minutes (more than 22GB). Please let me know if I can provide more information. Cheers, Viktor

3 12

[MonetDB-users] MonetDB to X100 relations
by Pavel Stehule 24 Jan '12

24 Jan '12

Hello I am interested about current state of MonetDB/X100 project? It is freely available? It is part of current MonetDB 5 project? Can we expect some progress and possible availability under some open source licence? Thank you for information Best regards Pavel Stehule

2 2

[MonetDB-users] ODBC Error
by Luiz Gustavo de Souza 18 Jan '12

18 Jan '12

Greetings MonetDB developers! I'm preparing MonetDB for an application, and I got the current error from the ODBC: [MonetDB][ODBC Driver 11.5.9]Cannot return the number of output columns. Query must be executed first. I'm using Windows 7 Ultimate 64bits with both ODBC x86 and x64, but the system and also the Microsoft Excel only find the x86 driver. How can I overcome this? Thank you very much.

2 1

[MonetDB-users] Slow import from a SQL (20 tps)
by Sékine Coulibaly 18 Jan '12

18 Jan '12

I'm evaluating MonetDB and importing an SQL file containing INSERT statements, so that I can populate the following table : CREATE TABLE session ( cmd character varying(25), date bigint, session_id character varying(64), score integer, src_ip character varying(25), dst_ip character varying(25), tx_bytes integer, rx_bytes integer, retx_bytes integer, scrambling character varying(16), url character varying(64), country character varying(32), orig character varying(3), status integer ) No index was created. >From what I can see, I'm somewhere between 20 and 40 insertions per second (Core i5, 4GB RAM, Windows7, MonetDb MonetDB5-SQL-Installer-i386-20111021.msi). The Windows task manager shows a disk write throughput of 300 kB/s which looks like very small, processor usage around 5%. I imported the file with mclient -u monetdb < myfile.sql I expected to import at 200 rows/second minimum. Am I doing something wrong ? What can be done to improve the loading speed ? Thank you SC

2 2

[MonetDB-users] How to find implementation function of a MAL function
by Charalampos Nikolaou 18 Jan '12

18 Jan '12

Hello, I would like to know whether there is any way to find out the C function implementing a MAL function through mclient (maybe with the ?pattern command). Let's say I search for batcalc.and(:bat, :bat) function. If there isn't any, then what is the C function implementing the MAL function I mentioned previously? Thanks, Babis

2 1

[MonetDB-users] C++ Driver (postgresql proxy)
by Sékine Coulibaly 18 Jan '12

18 Jan '12

Hi, I plan to use MonetDb in a C++ project and have the following questions/concerns : - Using an third party ODBC library (such as SOCI) to add ODBC support to my project looks like risky. Is MonetDB fully compliant with ODBC ? Does anyone know if it works well with SOCI (or any other open source ODBC library) ? - I really would prefer using an integrated library (Postgresql for example ships with a client library for embedding SQL in C/C++ code, and a libpq library). I guess this is not available for MonetDb. Confirmed ? - Previously, I came through a driver that allowed to proxy MonetDb to a virtual Postgresql. I mean that the proxy attacks MonetDb in ODBC (and hopefully it does it well), and then exposes the MonetDb database through the standard Postgesql interface. Hence you can use PgAdminIII, pgadmin... on port 5432 which is the Postgesql port. I can't find this proxy driver anymore. Does anyone ? Thank you !

1 0

[MonetDB-users] Stored Procedure code example.
by Miguel Chang 18 Jan '12

18 Jan '12

Hello there, before I start with my question I would like to thank all those developers who put so much effort on this great Open Source Database. My name is Miguel, I´m currently using the MonetDB database for a university project and so far it´s been great. By following the documentation on the webpage I´ve build a fully functional system but I´ve encoutered one problem: So far I´ve been unable to create an stored procedure, even after following all the instructions on the documentation page: http://www.monetdb.org/Documentation/Manuals/SQLreference/Procedures Here´s an example I´ve tried but that does not works: CREATE PROCEDURE SP_GET_ALL_EVENTS () BEGIN SELECT * FROM DEMO.EVENTS; END; >From the many variation I tried with the last query I always get these errors: "Unexpected From, Expected In" or "unexpected END" I´ve been trying many variations (INSERT, UPDATES, DELETES) and I get the same responses... So can someone please point me what´s the error in my code? and also show me some examples codes for stored procedure creation? Much Appreciated.

2 2

[MonetDB-users] MonetDB unexpected count/group by slowdown
by zack_pcd 17 Jan '12

17 Jan '12

I have a table f_trigger with 171,452,378 records. It has fields script_id and machine_uuid (and other fields not used here). I want to run select script_id,count(distinct machine_uuid) from f_trigger group by script_id; First, though, let's get the number of expected records only: select count(*) from (select script_id,count(distinct machine_uuid) from f_trigger group by script_id) as subq; +------+ | L7 | +======+ | 208 | +------+ 1 tuple (359.124ms) Very nice. Let's explain the query before I run it: explain select script_id,count(distinct machine_uuid) from f_trigger group by script_id; +-------------------------------------------------------------------------------+ | mal | +===============================================================================+ | function user.s0_1{autoCommit=true}():void; | | X_21 := nil:bat[:oid,:str]; | | X_39:bat[:oid,:wrd] := nil:bat[:oid,:wrd]; | | barrier X_596 := language.dataflow(); | | X_2 := sql.mvc(); | | X_12:bat[:oid,:str] := sql.bind(X_2,"sys","f_trigger","script_id",0); | | X_10:bat[:oid,:str] := sql.bind(X_2,"sys","f_trigger","script_id",2); | | X_14 := algebra.kdifference(X_12,X_10); | | X_15 := algebra.kunion(X_14,X_10); | | X_9:bat[:oid,:str] := sql.bind(X_2,"sys","f_trigger","script_id",1); | | X_16 := algebra.kunion(X_15,X_9); | | X_5:bat[:oid,:oid] := sql.bind_dbat(X_2,"sys","f_trigger",1); | | X_8 := bat.reverse(X_5); | | X_17 := algebra.kdifference(X_16,X_8); | | (ext28,grp26) := group.done(X_17); | | X_20 := bat.mirror(ext28); | | X_21 := algebra.join(X_20,X_17); | | X_29:bat[:oid,:str] := sql.bind(X_2,"sys","f_trigger","machine_uuid",0); | | X_30 := algebra.selectNotNil(X_29); | | X_26:bat[:oid,:str] := sql.bind(X_2,"sys","f_trigger","machine_uuid",2); | | X_31 := algebra.kdifference(X_30,X_26); | | X_28 := algebra.selectNotNil(X_26); | | X_32 := algebra.kunion(X_31,X_28); | | X_24:bat[:oid,:str] := sql.bind(X_2,"sys","f_trigger","machine_uuid",1); | | X_25 := algebra.selectNotNil(X_24); | | X_33 := algebra.kunion(X_32,X_25); | | X_34 := algebra.kdifference(X_33,X_8); | | (X_35,grp55) := group.derive(ext28,grp26,X_34); | | X_37 := bat.mirror(X_35); | | X_38 := algebra.join(X_37,X_34); | | X_39:bat[:oid,:wrd] := aggr.count(X_38,grp26,X_20); | | exit X_596; | | X_40 := sql.resultSet(2,1,X_21); | | sql.rsColumn(X_40,"sys.f_trigger","script_id","varchar",32768,0,X_21); | | sql.rsColumn(X_40,"sys.f_trigger","L10","wrd",64,0,X_39); | | X_53 := io.stdout(); | | sql.exportResult(X_53,X_40); | | end s0_1; | +-------------------------------------------------------------------------------+ 38 tuples (3.136ms) Running the query takes a very long time. While the query is running, I notice VERY heavy disk writes, and almost no reads and almost no CPU activity. Based on other information on the mailing list, I suspect the query generates intermediate data that gets writted to disk (instead of kept in memory). We are running monetdb on a 32 core linux machine with 256 GB of RAM and a fast raid array. Memory usage is under 20%, so I would not expect monetdb to be writing to disk so much (50-500MB/s writes)... Database settings: shared default yes nthreads local 32 optpipe local recycler_pipe master default no slave default <unknown> readonly local yes Stethoscope output: [1] # event, time, thread, usec, stmt, # name [1] [ 256, "14:02:38.525477", 2, 0, "calc.str(\"script_id\");" ] [1] [ 257, "14:02:38.525477", 2, 134, "calc.str(\"script_id\");" ] [1] [ 258, "14:02:38.525634", 2, 0, "calc.str(\"f_trigger\");" ] [1] [ 259, "14:02:38.525634", 2, 18, "calc.str(\"f_trigger\");" ] [1] [ 260, "14:02:38.525670", 2, 0, "calc.str(\"script_id\");" ] [1] [ 261, "14:02:38.525670", 2, 16, "calc.str(\"script_id\");" ] [1] [ 262, "14:02:38.525704", 2, 0, "calc.str(\"f_trigger\");" ] [1] [ 263, "14:02:38.525704", 2, 17, "calc.str(\"f_trigger\");" ] [1] [ 264, "14:02:38.525740", 2, 0, "calc.str(\"script_id\");" ] [1] [ 265, "14:02:38.525740", 2, 17, "calc.str(\"script_id\");" ] [1] [ 266, "14:02:38.525777", 2, 0, "calc.str(\"f_trigger\");" ] [1] [ 267, "14:02:38.525777", 2, 19, "calc.str(\"f_trigger\");" ] [1] [ 268, "14:02:38.525812", 2, 0, "calc.str(\"script_id\");" ] [1] [ 269, "14:02:38.525812", 2, 19, "calc.str(\"script_id\");" ] [1] [ 270, "14:02:38.525848", 2, 0, "calc.str(\"f_trigger\");" ] [1] [ 271, "14:02:38.525848", 2, 19, "calc.str(\"f_trigger\");" ] [1] [ 272, "14:02:38.525884", 2, 0, "calc.str(\"L11\");" ] [1] [ 273, "14:02:38.525884", 2, 19, "calc.str(\"L11\");" ] [1] [ 274, "14:02:38.525925", 2, 0, "calc.str(\"f_trigger\");" ] [1] [ 275, "14:02:38.525925", 2, 20, "calc.str(\"f_trigger\");" ] [1] [ 276, "14:02:38.525962", 2, 0, "calc.str(\"L11\");" ] [1] [ 277, "14:02:38.525962", 2, 18, "calc.str(\"L11\");" ] [1] [ 278, "14:02:38.526000", 2, 0, "calc.str(\"f_trigger\");" ] [1] [ 279, "14:02:38.526000", 2, 18, "calc.str(\"f_trigger\");" ] [1] [ 280, "14:02:38.526039", 2, 0, "calc.str(\"machine_uuid\");" ] [1] [ 281, "14:02:38.526039", 2, 19, "calc.str(\"machine_uuid\");" ] [1] [ 282, "14:02:38.526077", 2, 0, "calc.str(\"f_trigger\");" ] [1] [ 283, "14:02:38.526077", 2, 20, "calc.str(\"f_trigger\");" ] [1] [ 284, "14:02:38.526116", 2, 0, "calc.str(nil:str);" ] [1] [ 285, "14:02:38.526116", 2, 22, "calc.str(nil:str);" ] [1] [ 286, "14:02:38.528707", 2, 0, "X_21 := nil:bat[:oid,:str];" ] [1] [ 287, "14:02:38.528707", 2, 64, "X_21 := nil:bat[:oid,:str];" ] [1] [ 288, "14:02:38.528790", 2, 0, "X_39:bat[:oid,:wrd] := nil:bat[:oid,:wrd];" ] [1] [ 289, "14:02:38.528790", 2, 27, "X_39:bat[:oid,:wrd] := nil:bat[:oid,:wrd];" ] [1] [ 290, "14:02:38.528835", 2, 0, "barrier X_596 := language.dataflow();" ] [1] [ 291, "14:02:38.528928", 40, 0, "X_2 := sql.mvc();" ] [1] [ 292, "14:02:38.528928", 40, 141, "X_2 := sql.mvc();" ] [1] [ 293, "14:02:38.529100", 40, 0, "X_12:bat[:oid,:str] := sql.bind(X_2=0,\"sys\",\"f_trigger\",\"script_id\",0);" ] [1] [ 294, "14:02:38.529100", 40, 78, "X_12:bat[:oid,:str] =<tmp_7153>[171452378] := sql.bind(X_2=0,\"sys\",\"f_trigger\",\"script_id\",0);" ] [1] [ 295, "14:02:38.529255", 52, 0, "X_10:bat[:oid,:str] := sql.bind(X_2=0,\"sys\",\"f_trigger\",\"script_id\",2);" ] [1] [ 296, "14:02:38.529255", 52, 229, "X_10:bat[:oid,:str] =<tmp_11631>[0] := sql.bind(X_2=0,\"sys\",\"f_trigger\",\"script_id\",2);" ] [1] [ 297, "14:02:38.529491", 45, 0, "X_5:bat[:oid,:oid] := sql.bind_dbat(X_2=0,\"sys\",\"f_trigger\",1);" ] [1] [ 298, "14:02:38.529491", 45, 201, "X_5:bat[:oid,:oid] =<tmp_11616>[0] := sql.bind_dbat(X_2=0,\"sys\",\"f_trigger\",1);" ] [1] [ 299, "14:02:38.529699", 50, 0, "X_9:bat[:oid,:str] := sql.bind(X_2=0,\"sys\",\"f_trigger\",\"script_id\",1);" ] [1] [ 300, "14:02:38.529699", 50, 227, "X_9:bat[:oid,:str] =<tmp_11632>[0] := sql.bind(X_2=0,\"sys\",\"f_trigger\",\"script_id\",1);" ] [1] [ 301, "14:02:38.529951", 56, 0, "X_24:bat[:oid,:str] := sql.bind(X_2=0,\"sys\",\"f_trigger\",\"machine_uuid\",1);" ] [1] [ 302, "14:02:38.530203", 52, 0, "X_14 := algebra.kdifference(X_12=<tmp_7153>:bat[:oid,:str][171452378],X_10=<tmp_11631>:bat[:oid,:str][0]);" ] [1] [ 303, "14:02:38.529951", 56, 259, "X_24:bat[:oid,:str] =<tmp_11632>[0] := sql.bind(X_2=0,\"sys\",\"f_trigger\",\"machine_uuid\",1);" ] [1] [ 304, "14:02:38.530203", 52, 112, "X_14=<tmp_12321>[171452378] := algebra.kdifference(X_12=nil:bat[:oid,:str],X_10=<tmp_11631>:bat[:oid,:str][0]);" ] [1] [ 305, "14:02:38.530308", 45, 0, "X_8 := bat.reverse(X_5=<tmp_11616>:bat[:oid,:oid][0]);" ] [1] [ 306, "14:02:38.530308", 45, 143, "X_8=<tmpr_11616>[0] := bat.reverse(X_5=nil:bat[:oid,:oid]);" ] [1] [ 307, "14:02:38.530465", 57, 0, "X_29:bat[:oid,:str] := sql.bind(X_2=0,\"sys\",\"f_trigger\",\"machine_uuid\",0);" ] [1] [ 308, "14:02:38.530668", 56, 0, "X_25 := algebra.selectNotNil(X_24=<tmp_11632>:bat[:oid,:str][0]);" ] [1] [ 309, "14:02:38.530465", 57, 210, "X_29:bat[:oid,:str] =<tmp_10750>[171452378] := sql.bind(X_2=0,\"sys\",\"f_trigger\",\"machine_uuid\",0);" ] [1] [ 310, "14:02:38.530668", 38, 0, "X_26:bat[:oid,:str] := sql.bind(X_2=0,\"sys\",\"f_trigger\",\"machine_uuid\",2);" ] [1] [ 311, "14:02:38.530668", 56, 44, "X_25=<tmp_11632>[0] := algebra.selectNotNil(X_24=nil:bat[:oid,:str]);" ] [1] [ 312, "14:02:38.530668", 38, 1743, "X_26:bat[:oid,:str] =<tmp_11631>[0] := sql.bind(X_2=0,\"sys\",\"f_trigger\",\"machine_uuid\",2);" ] [1] [ 313, "14:02:38.530946", 61, 0, "X_15 := algebra.kunion(X_14=<tmp_12321>[171452378],X_10=<tmp_11631>:bat[:oid,:str][0]);" ] [1] [ 314, "14:02:38.531115", 57, 0, "X_30 := algebra.selectNotNil(X_29=<tmp_10750>:bat[:oid,:str][171452378]);" ] [1] [ 315, "14:02:38.531115", 57, 39, "X_30=<tmp_10750>[171452378] := algebra.selectNotNil(X_29=nil:bat[:oid,:str]);" ] [1] [ 316, "14:02:38.530946", 61, 210, "X_15=<tmp_12211>[171452378] := algebra.kunion(X_14=nil,X_10=nil:bat[:oid,:str]);" ] [1] [ 317, "14:02:38.531190", 57, 0, "X_31 := algebra.kdifference(X_30=<tmp_10750>[171452378],X_26=<tmp_11631>:bat[:oid,:str][0]);" ] [1] [ 318, "14:02:38.531271", 61, 0, "X_16 := algebra.kunion(X_15=<tmp_12211>[171452378],X_9=<tmp_11632>:bat[:oid,:str][0]);" ] [1] [ 319, "14:02:38.531190", 57, 99, "X_31=<tmp_12321>[171452378] := algebra.kdifference(X_30=nil,X_26=<tmp_11631>:bat[:oid,:str][0]);" ] [1] [ 320, "14:02:38.531271", 61, 42, "X_16=<tmp_12216>[171452378] := algebra.kunion(X_15=nil,X_9=nil:bat[:oid,:str]);" ] [1] [ 321, "14:02:38.531346", 57, 0, "X_28 := algebra.selectNotNil(X_26=<tmp_11631>:bat[:oid,:str][0]);" ] [1] [ 322, "14:02:38.531346", 57, 55, "X_28=<tmp_11631>[0] := algebra.selectNotNil(X_26=nil:bat[:oid,:str]);" ] [1] [ 323, "14:02:38.531408", 61, 0, "X_17 := algebra.kdifference(X_16=<tmp_12216>[171452378],X_8=<tmpr_11616>[0]);" ] [1] [ 324, "14:02:38.531466", 57, 0, "X_32 := algebra.kunion(X_31=<tmp_12321>[171452378],X_28=<tmp_11631>[0]);" ] [1] [ 325, "14:02:38.531408", 61, 59, "X_17=<tmp_12211>[171452378] := algebra.kdifference(X_16=nil,X_8=<tmpr_11616>[0]);" ] [1] [ 326, "14:02:38.531466", 57, 49, "X_32=<tmp_12216>[171452378] := algebra.kunion(X_31=nil,X_28=nil);" ] [1] [ 327, "14:02:38.531537", 61, 0, "(ext28,grp26) := group.done(X_17=<tmp_12211>[171452378]);" ] [1] [ 328, "14:02:38.531596", 57, 0, "X_33 := algebra.kunion(X_32=<tmp_12216>[171452378],X_25=<tmp_11632>[0]);" ] [1] [ 329, "14:02:38.531596", 57, 34, "X_33=<tmp_12261>[171452378] := algebra.kunion(X_32=nil,X_25=nil);" ] [1] [ 330, "14:02:38.531660", 57, 0, "X_34 := algebra.kdifference(X_33=<tmp_12261>[171452378],X_8=<tmpr_11616>[0]);" ] [1] [ 331, "14:02:38.531660", 57, 28, "X_34=<tmp_12216>[171452378] := algebra.kdifference(X_33=nil,X_8=nil);" ] [1] [ 332, "14:02:38.531537", 61, 4488161, "(ext28=<tmp_12207>[208],grp26=<tmp_12321>[171452378]) := group.done(X_17=<tmp_12211>[171452378]);" ] [1] [ 333, "14:02:43.019887", 61, 0, "X_20 := bat.mirror(ext28=<tmp_12207>[208]);" ] [1] [ 334, "14:02:43.019887", 61, 39, "X_20=<tmp_12261>[208] := bat.mirror(ext28=<tmp_12207>[208]);" ] [1] [ 335, "14:02:43.019962", 61, 0, "(X_35,grp55) := group.derive(ext28=<tmp_12207>[208],grp26=<tmp_12321>[171452378],X_34=<tmp_12216>[171452378]);" ] [1] [ 336, "14:02:43.020045", 60, 0, "X_21 := algebra.join(X_20=<tmp_12261>[208],X_17=<tmp_12211>[171452378]);" ] [1] [ 337, "14:02:43.020045", 60, 505, "X_21=<tmp_12332>[208] := algebra.join(X_20=<tmp_12261>[208],X_17=nil);" ] [1] [ 338, "13:50:05.243130", 21, 2365556004, "(X_41=<tmp_12243>[90737912],grp74) := group.derive(ext30=nil,grp28=<tmp_12111>[171452378],X_40=<tmp_12110>[171452378]);" ] [1] [ 339, "14:29:30.799335", 21, 0, "X_43 := bat.mirror(X_41=<tmp_12243>[90737912]);" ] [1] [ 340, "14:29:30.799335", 21, 44, "X_43=<tmp_12113>[90737912] := bat.mirror(X_41=nil);" ] [1] [ 341, "14:29:30.799408", 21, 0, "X_44 := algebra.join(X_43=<tmp_12113>[90737912],X_40=<tmp_12110>[171452378]);" ] [1] [ 342, "14:29:30.799408", 21, 1931164, "X_44=<tmp_12117>[90737912] := algebra.join(X_43=nil,X_40=nil);" ] [1] [ 343, "14:29:32.730825", 21, 0, "X_45:bat[:oid,:wrd] := aggr.count(X_44=<tmp_12117>[90737912],grp28=<tmp_12111>[171452378],X_20=<tmp_12107>[208]);" ] [1] [ 344, "14:29:32.730825", 21, 2686923, "X_45:bat[:oid,:wrd] =<tmp_12110>[208] := aggr.count(X_44=nil,grp28=nil,X_20=nil);" ] [1] [ 345, "13:49:55.404991", 35, 2380012994, "barrier X_602 := language.dataflow();" ] [1] [ 346, "14:29:35.418135", 35, 0, "X_47 := sql.resultSet(2,1,X_25=<tmp_12112>[10]);" ] [1] [ 347, "14:29:35.418135", 35, 53, "X_47 := sql.resultSet(2,1,X_25=<tmp_12112>[10]);" ] [1] [ 348, "14:29:35.418208", 35, 0, "X_46 := algebra.leftjoin(X_24=<tmp_12115>[10],X_45=<tmp_12110>:bat[:oid,:wrd][208]);" ] [1] [ 349, "14:29:35.418208", 35, 130, "X_46=<tmp_12172>[10] := algebra.leftjoin(X_24=nil,X_45=nil:bat[:oid,:wrd]);" ] [1] [ 350, "14:29:35.418370", 35, 0, "sql.rsColumn(X_47=1,\"sys.f_trigger\",\"script_id\",\"varchar\",32768,0,X_25=<tmp_12112>[10]);" ] [1] [ 351, "14:29:35.418370", 35, 36, "sql.rsColumn(X_47=1,\"sys.f_trigger\",\"script_id\",\"varchar\",32768,0,X_25=nil);" ] [1] [ 352, "14:29:35.418429", 35, 0, "sql.rsColumn(X_47=1,\"sys.f_trigger\",\"L6\",\"wrd\",64,0,X_46=<tmp_12172>[10]);" ] [1] [ 353, "14:29:35.418429", 35, 34, "sql.rsColumn(X_47=1,\"sys.f_trigger\",\"L6\",\"wrd\",64,0,X_46=nil);" ] [1] [ 354, "14:29:35.418483", 35, 0, "X_57 := io.stdout();" ] [1] [ 355, "14:29:35.418483", 35, 18, "X_57 := io.stdout();" ] [1] [ 356, "14:29:35.418515", 35, 0, "sql.exportResult(X_57==\"42083632\":streams,X_47=1);" ] [1] [ 357, "14:29:35.418515", 35, 73, "sql.exportResult(X_57==\"42083632\":streams,X_47=1);" ] [1] [ 358, "14:29:35.418611", 35, 0, "end s3_2;" ] [1] [ 359, "14:02:43.019962", 61, 2633497166, "(X_35=<tmp_12112>[90737912],grp55) := group.derive(ext28=nil,grp26=<tmp_12321>[171452378],X_34=<tmp_12216>[171452378]);" ] [1] [ 360, "14:46:36.517329", 61, 0, "X_37 := bat.mirror(X_35=<tmp_12112>[90737912]);" ] [1] [ 361, "14:46:36.517329", 61, 43, "X_37=<tmp_12206>[90737912] := bat.mirror(X_35=nil);" ] [1] [ 362, "14:46:36.517404", 61, 0, "X_38 := algebra.join(X_37=<tmp_12206>[90737912],X_34=<tmp_12216>[171452378]);" ] [1] [ 363, "14:46:36.517404", 61, 2374517, "X_38=<tmp_12211>[90737912] := algebra.join(X_37=nil,X_34=nil);" ] [1] [ 364, "14:46:38.892126", 61, 0, "X_39:bat[:oid,:wrd] := aggr.count(X_38=<tmp_12211>[90737912],grp26=<tmp_12321>[171452378],X_20=<tmp_12261>[208]);" ] [1] [ 365, "14:46:38.892126", 61, 2690276, "X_39:bat[:oid,:wrd] =<tmp_12216>[208] := aggr.count(X_38=nil,grp26=nil,X_20=nil);" ] [1] [ 366, "14:02:38.528835", 2, 2643053835, "barrier X_596 := language.dataflow();" ] [1] [ 367, "14:46:41.583009", 2, 0, "X_40 := sql.resultSet(2,1,X_21=<tmp_12332>[208]);" ] [1] [ 368, "14:46:41.583009", 2, 48, "X_40 := sql.resultSet(2,1,X_21=<tmp_12332>[208]);" ] [1] [ 369, "14:46:41.583076", 2, 0, "sql.rsColumn(X_40=1,\"sys.f_trigger\",\"script_id\",\"varchar\",32768,0,X_21=<tmp_12332>[208]);" ] [1] [ 370, "14:46:41.583076", 2, 40, "sql.rsColumn(X_40=1,\"sys.f_trigger\",\"script_id\",\"varchar\",32768,0,X_21=nil);" ] [1] [ 371, "14:46:41.583140", 2, 0, "sql.rsColumn(X_40=1,\"sys.f_trigger\",\"L11\",\"wrd\",64,0,X_39=<tmp_12216>:bat[:oid,:wrd][208]);" ] [1] [ 372, "14:46:41.583140", 2, 35, "sql.rsColumn(X_40=1,\"sys.f_trigger\",\"L11\",\"wrd\",64,0,X_39=nil:bat[:oid,:wrd]);" ] [1] [ 373, "14:46:41.583198", 2, 0, "X_53 := io.stdout();" ] [1] [ 374, "14:46:41.583198", 2, 18, "X_53 := io.stdout();" ] [1] [ 375, "14:46:41.583230", 2, 0, "sql.exportResult(X_53==\"35761888\":streams,X_40=1);" ] [1] [ 376, "14:46:41.583230", 2, 403, "sql.exportResult(X_53==\"35761888\":streams,X_40=1);" ] [1] [ 377, "14:46:41.583661", 2, 0, "end s1_1;" ] The query took 44 minutes to complete. Is this a bug (as I suspect)? Or is the group by/count distinct operation just slow? If it is a bug, is this fixed in dec-2011? If that is the case, I would like to verify this was fixed for us in dec-2011 - however the last "latest" build I tried was crashing for me. I am not sure which version of dec-2011 I should try for testing this. Thanks -- View this message in context: http://old.nabble.com/MonetDB-unexpected-count-group-by-slowdown-tp33137428… Sent from the monetdb-users mailing list archive at Nabble.com.

3 4

[MonetDB-users] column sortedness
by David Pennell 13 Jan '12

13 Jan '12

I'm running some experiments with a 1B row fact table (10 columns). - How can I tell if a column is sorted? - If I generate files in sorted order and use binary bulk loading, will monetdb consider the column sorted? If not, can I force it? I've been reading code, but understanding all the assignments to bn->tsorted is going to take a while. I get the general theme of setting tsorted during bat operations, but I've been unsuccessful in finding where it is initialized when a column is first accessed. TIA, -david

2 2