March 2014 - users-list

possible to improve paging performance on the windows version of monetdb?
by Anthony Damico 17 Mar '14

17 Mar '14

hi monetdb team, could disk paging performance be improved on windows? a query run without versus with any disk paging is kinda like the hulk versus plain old bruce banner. ;) ==background== i'm working on a longer tutorial for http://asdfree.com/ about how to work with census microdata from 74 different countries (yes, 74!) with R and monetdb. i'm planning to release a blog post specifically for the brazilian microdata and then expand that code to work on the other 73 countries <https://international.ipums.org/international-action/countries>. both brazilian census bureau employees and outside users have taken a serious interest in this work, with official links from one<http://www.ibge.gov.br/home/estatistica/populacao/trabalhoerendimento/pnad2…>, two<http://www.ibge.gov.br/home/estatistica/indicadores/trabalhoerendimento/pme…>, three <http://www.ibge.gov.br/home/xml/pof_2008_2009.shtm> of their microdata pages. r and monetdb are obviously great choices for analysts who don't have the resources for expensive computers and/or software. a former employee at insee (the french stat. bureau) who currently consults on international demography projects outlined this problem for me pretty clearly: lower-resource statistical agencies in the developing world need a good, free option like (R + monetdb) for analysis of larger data sets on the (generally cheaper) hardware that they already have. international census bureau employees and other demographers are much more likely to have statistical but not computer programming skills, so as i work on these instructional documents, i'll aim to make the computing side of things as easy as possible. and, most central to this e-mail, much of this new potential audience uses windows. (on march 10th, 2014<http://cran-logs.rstudio.com/2014/2014-03-10.csv.gz>, 67% of MonetDB.R package downloads were windows users) ==problem== certain sql commands - it's hard for me to pin down which ones - tend to hang. i will run a command, look at my "process manager" and see RAM start getting gobbled up (not by mserver.exe but by some invisible process--i think disk paging?), and then wait. mserver.exe will use between 0% and 1% of the total CPU for a few minutes, then something will "wake up" and suddenly the CPU starts lighting up.. with 70%-90% usage across multiple cores. this 1% CPU usage to 75% CPU usage can jump back and forth a few times until the full command finally finishes. all of the time spent with 0% or 1% CPU usage - waiting for commands to execute - seems like an opportunity for improvement of the windows build? here's an example that took 11 minutes for a query run on 20,000,000 records.. > system.time( z <- svymean( ~v0601 , pes.design , byvar = ~v0001 , se = TRUE ) ) user system elapsed 18.47 1.13 *710.86 * ..totally awesome! but can it be improved? ==comparison between two machines== the attached .sql code has been run on two windows machines: on the windows server with 64 gb of ram-- # Serving database 'test', using 4 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 64.000 GiB available main-memory. on my personal windows laptop-- # Serving database 'test', using 8 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 7.860 GiB available main-memory. ==processing time comparison== processing time on the 64gb server: 27 tuples *(5.2s)* processing time on my 8gb laptop: 27 tuples *(7m 46s)* *so on a windows machine, if disk paging can be avoided, the processing time is 90x faster for this attached sql code?* ==how to load this reproducible example== ** data file with four columns ** https://drive.google.com/file/d/0B3LuuP081KDYNERWQWx5MEpZU2M/edit?usp=shari… ** commands to load the data ** CREATE TABLE "sys"."d10" ( "idkey" INTEGER, "v0601" VARCHAR(255), "pes_wgt" DOUBLE, "v0001" VARCHAR(255) ); COPY 20635472 RECORDS INTO d10 FROM 's:/temp/example.txt'; [[see .sql attachment for post-import commands]] ==general diagnostics== # MonetDB 5 server v11.17.11 "Jan2014-SP1" # Serving database 'test', using 4 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 64.000 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2014 MonetDB B.V., all rights reserved # Visit http://www.monetdb.org/ for further information # Listening for connection requests on mapi:monetdb://127.0.0.1:50000/ # MonetDB/JAQL module loaded # MonetDB/SQL module loaded > ==conclusion question== the windows version of monetdb is intensely slow at rather unexpected times. can and will anything be done about this in the near future? thanks for monetdb!

1 1

MonetDB Slow Query with Index
by Summer Xia 15 Mar '14

15 Mar '14

Hi all, I created a table with 10GB (60M records), added unique index (hidden_id) manually after data insertion. I had the simplest query but it took one minutes to complete. select hidden_id from netflow where hidden_id = 350000; And also the query took tens of minutes "select * from netflow order by hidden_id limit 12500 offset 212500;". It really confuses me. I post the analysis of the first below. Any clue why it's so slow? trace select hidden_id from netflow where hidden_id = 350000; +----------+------------------------------------------------------------------+ | ticks | stmt | +==========+==================================================================+ | 3 | X_3 := sql.mvc(); | | 15 | X_7=<tmp_2510>[69396995] := sql.bind(X_3=0,"sys","netflow","hidd | : : en_id",0); : | 227 | X_4:bat[:oid,:oid] =<tmp_13332>[69396995] := sql.tid(X_3=0,"sys" | : : ,"netflow"); : | 72978741 | X_36=<tmp_4053>[1] := algebra.subselect(X_7=<tmp_2510>[69396995] | : : ,X_4=<tmp_13332>:bat[:oid,:oid][69396995],A0=350000:lng,A0=35000 : : : 0:lng,true,true,false); : | 17 | (X_10=<tmp_2175>[0],r1_10=<tmp_3416>[0]) := sql.bind(X_3=0,"sys" | : : ,"netflow","hidden_id",2); : | 14 | X_37=<tmp_13337>[0] := algebra.subselect(r1_10=<tmp_3416>[0],A0= | : : 350000:lng,A0=350000:lng,true,true,false); : | 6 | X_13=<tmp_3416>[0] := sql.bind(X_3=0,"sys","netflow","hidden_id" | : : ,1); : | 15 | X_38=<tmp_11053>[0] := algebra.subselect(X_13=<tmp_3416>[0],X_4= | : : <tmp_13332>:bat[:oid,:oid][69396995],A0=350000:lng,A0=350000:lng : : : ,true,true,false); : | 4 | X_15=<tmp_4053>[1] := sql.subdelta(X_36=<tmp_4053>[1],X_4=<tmp_1 | : : 3332>:bat[:oid,:oid][69396995],X_10=<tmp_2175>[0],X_37=<tmp_1333 : : : 7>[0],X_38=<tmp_11053>[0]); : | 20 | X_17=<tmp_11053>[1] := sql.projectdelta(X_15=<tmp_4053>[1],X_7=< | : : tmp_2510>[69396995],X_10=<tmp_2175>[0],r1_10=<tmp_3416>[0],X_13= : : : <tmp_3416>[0]); : | 6 | X_18 := sql.resultSet(1,1,X_17=<tmp_11053>[1]); | | 7 | sql.rsColumn(X_18=1,"sys.netflow","hidden_id","bigint",64,0,X_17 | : : =<tmp_11053>[1]); : | 2 | X_23 := io.stdout(); | | 25 | sql.exportResult(X_23=="104d2":streams,X_18=1); | | 1 | end s1_3; | | 73011629 | X_5:void := user.s1_3(350000:lng); | +----------+------------------------------------------------------------------+ This is the table being created. CREATE TABLE "netflow" ( "time_seconds" double DEFAULT NULL, "parsed_date" timestamp DEFAULT NULL, "date_time_str" varchar(45) DEFAULT NULL, "ip_layer_protocol" bigint DEFAULT NULL, "ip_layer_protocol_code" varchar(45) DEFAULT NULL, "first_seen_src_ip" varchar(45) DEFAULT NULL, "first_seen_dest_ip" varchar(45) DEFAULT NULL, "first_seen_src_port" bigint DEFAULT NULL, "first_seen_dest_port" bigint DEFAULT NULL, "more_fragments" varchar(45) DEFAULT NULL, "cont_fragments" varchar(45) DEFAULT NULL, "duration_seconds" bigint DEFAULT NULL, "first_seen_src_payload_bytes" bigint DEFAULT NULL, "first_seen_dest_payload_bytes" bigint DEFAULT NULL, "first_seen_src_total_bytes" bigint DEFAULT NULL, "first_seen_dest_total_bytes" bigint DEFAULT NULL, "first_seen_src_packet_count" bigint DEFAULT NULL, "first_seen_dest_packet_count" bigint DEFAULT NULL, "record_force_out" varchar(45) DEFAULT NULL ); Best regards, Summer

2 1

python 3.2 support
by Simon 13 Mar '14

13 Mar '14

Hi thanks for the great project. I just wonder when there would be python 3.2 support. Is there any way that we could use monetdb in python 3.2 before the official package? Thanks again. Simon

4 5

Announcement: New Jan2014-SP1 Bugfix release of MonetDB suite
by Sjoerd Mullender 10 Mar '14

10 Mar '14

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jan2014-SP1 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-SP1 bugfix release Build Environment * Configure now enables the SQL front end by default. Merovingian * monetdb destroy -f now also works on running databases Bug Fixes * 3442: COPY INTO ... LOCKED reports incorrect count * 3443: DROP INDEX crashes server with BATsubselect: invalid argument: b must have a dense head * 3444: AND after ON () of LEFT OUTER JOIN with certain expressions will cause crash * 3452: ODBC driver build fails on Mac OS X due to a conflicting types for the SQLColAttribute with the unixODBC library

1 0

Fwd: [asdfree by anthony damico] New comment on analyze the program for international student asse....
by Anthony Damico 04 Mar '14

04 Mar '14

if there's any chance you might release SP1 soon, i'd appreciate it :) this user is hitting (resolved) bug 3443<http://bugs.monetdb.org/show_bug.cgi?id=3443>. thanks! ---------- Forwarded message ---------- From: lightest <noreply-comment(a)blogger.com> Date: Sun, Feb 23, 2014 at 7:53 PM Subject: [asdfree by anthony damico] New comment on analyze the program for international student asse.... To: ajdamico(a)gmail.com lightest <http://www.blogger.com/profile/12197082513968468249> has left a new comment on your post "analyze the program for international student asse...<http://www.asdfree.com/2013/12/analyze-program-for-international.html>": Hi Anthony, I've tried to run to codes, and when I run lines such as those of MICombine() functions, sometimes there is an error like the following: In R: Error in .mapiRequest(conn, paste0("s", statement, ";"), async = async) : error writing to socket (10054) Error in .mapiRequest(conn, paste0("s", statement, ";"), async = async) : error writing to socket (10054) Error in .mapiRequest(conn, paste0("s", statement, ";"), async = async) : error writing to socket (10054) > ======================================== In the console: # MonetDB 5 server v11.17.9 "Jan2014" # Serving database 'pisa', using 4 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 7.913 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2014 MonetDB B.V., all rights reserved # Visit http://www.monetdb.org/ for further information # Listening for connection requests on mapi:monetdb://127.0.0.1:50007/ # MonetDB/JAQL module loaded # MonetDB/SQL module loaded >!FATAL: 40000!COMMIT: transation commit failed (perhaps your disk is full?) exi ting (kernel error: !ERROR: BATsubselect: invalid argument: b must have a dense head. ) Press any key to continue . . . ======================================= Publish<http://www.blogger.com/comment-moderate-confirm.do?blogID=69830612920483473…> Delete<http://www.blogger.com/comment-moderate-confirm.do?blogID=69830612920483473…> Mark as spam<http://www.blogger.com/comment-moderate-confirm.do?blogID=69830612920483473…> Moderate<http://www.blogger.com/blogger.g?blogID=6983061292048347330#pendingcomments>comments for this blog. Posted by lightest to asdfree by anthony damico <http://www.asdfree.com/>at February 23, 2014 at 7:53 PM

2 3

How to update one table from another table or from aggregation in subqueries?
by Ning Yan 04 Mar '14

04 Mar '14

Hi, Firstly, I'm using MonetDB branch from MonetDB Database Server Toolkit v1.1 (Feb2013-SP1) I have two tables, and want to complete the following two functions without creating a temporary table: 1) update one table from another table; and 2) update one table from aggregation of another table. I figured out how to do them in MySQL and Postgresql but similar queries failed in MonetDB. For 1), Suppose I have the following two tables: drop table t1; create table t1(id int, data int); drop table t2; create table t2(id int, data int); delete from t1; insert into t1 values (0, 0), (1,1), (2,2); select*from t1; delete from t2; insert into t2 values (1,2), (2, 3), (3,4); select*from t2; We need to set t1.data to t1.data+t2.data when their ids match, t1.id=t2.id . In MySQL, we can use: update t1 inner join t2 on t1.id=t2.id set t1.data=t1.data+t2.data; In Postgresql, we can use: with tmp as (select t1.id, t1.data+t2.data as data from t1,t2 where t1.id= t2.id) update t1 set data = tmp.data from tmp where t1.id=tmp.id;select*from t1; Or for both, we can use: update t1 set data=(select t1.data+t2.data from t2 where t2.id=t1.id) where exists (select * from t2 where t1.id=t2.id); But none of them work in MonetDB. This question is similar to question asked before ( https://www.monetdb.org/pipermail/users-list/2011-August/005072.html), but the answered suggested sees to be incorrect. There the two tables having the same value for tuple id=2. update t1 set data=(select data+t2.data from t2 where t2.id=t1.id) where exists (select * from t2 where t1.id=t2.id); Thus in this query, the data field in 1st subquery of select actually comes from t2 not t1. However, if we changed to t1.data, the parser will not recognize name t1. It is somehow wired that it can recognize t1.id in condition clause. Not that the following query is also not correct: update t1 set data=(select t1.data + t2.data from t1, t2 where t2.id = t1.id) where exists (select t2.id from t2 where t2.id = t1.id); As the subquery becomes independent from external query, and might result in multiple values which violates cardinality of assignment. Also changing field names in t1 and t2 will not be helpful either. For 2), Suppose we have the following two tables: delete from t1; insert into t1 values (0, 0), (1,1), (2,2); select * from t1; delete from t2; insert into t2 values (1,2), (2, 3), (3,4), (2,5); select * from t2; We want to aggregate values in t2 and assign results to t1. In MySQL, we can use: update t1 inner join t2 on t1.id=t2.id set t1.data=(select sum(t2.data) from t2 where t2.id=t1.id group by t2.id); Or in Postgresql, we can use: with tmp as (select t2.id, sum(t2.data) as data from t2 group by t2.id) update t1 set data=tmp.data from tmp where t1.id=tmp.id; In MonetDB, how to correctly write such subquery in update statement? It looks like that MonetDB supports with clause in select, but not in update. with tmp as (select t2.id, sum(t2.data) as data from t2 group by t2.id) select * from tmp; I'm also interested to know the performance difference between using subqueries and creating temporary tables. Thanks! Regards, -- Ning Yan

2 2