October 2007 - users-list

[MonetDB-users] Bulk-Load Problem
by algatt 27 Oct '07

27 Oct '07

I am using MonetDB 5.0.0 and 1GB Ram on Windows XP. I am trying to use the TPC-H benchmark. With 1GB of test data everything worked fine, but if I try to load a table with more than 1GB of data the server crashes. Does this happen due to the 1GB of RAM? Also, when I use the number of records with the COPY command it copies more records. Any pointers? Thanks -- View this message in context: http://www.nabble.com/Bulk-Load-Problem-tf4702911.html#a13442730 Sent from the monetdb-users mailing list archive at Nabble.com.

3 2

[MonetDB-users] Very slow group by sql query
by Arjen van der Meijden 22 Oct '07

22 Oct '07

Hi list, I was just doing some basic queries on MonetDB5/SQL to see if it is suitable for my application, I'm doing lots of aggregates on some logfile-abstractions. Basically they all boil down to 'how many unique visitors and total pageviews where there in period X-Y in section Z'. I have this table: pageviews ( timestamp timestamp not null, clientip varchar(15) not null, sectionid smallint not null, itemid integer not null, channelid smallint default 0 ) Currently it only contains data for last september, with about 2M records/day, and 5.6M in total. There are no additional indexes in this case. When doing a query like this, monetdb very fast. Once the data is in the memory cache, it returns (according to trace) in about half a second. select count(*) from pageviews where timestamp between '2007-09-21 00:00:00' and '2007-09-22 00:00:00'; The result is 1916813 This one is also pretty fast, taking about 1.7 second select count(distinct clientip) from pageviews where timestamp between '2007-09-21 00:00:00' and '2007-09-22 00:00:00'; The result is 165700 And the third which is pretty fast: select channelid, count(*) from pageviews where timestamp between '2007-09-21 00:00:00' and '2007-09-22 00:00:00' group by channelid; Here's the distribution, and its returned in about 0.6 second [ 0, 538187 ] [ 1, 1108478 ] [ 4, 42867 ] [ 3, 145565 ] [ 2, 81716 ] But when I combine those last two queries, the result isn't returned in a reasonable amount of time, I waited for more than half an hour and it still hadn't returned the results. select channelid, count(distinct clientip), count(*) from pageviews where timestamp between '2007-09-21 00:00:00' and '2007-09-22 00:00:00' group by channelid; I'm not very good at reading your explain output yet, so I've attached the resulting explain for that query. Is there a way to speed up this type of query? It seems a bit odd that it's taking more than half an hour (postgresql does it in about 20 seconds) while the other queries return much faster (postgresql does them in about 14 seconds). Best regards, Arjen function user.s5_1():void; _1:bat[:oid,:timestamp]{rows=55914459:lng,bid=1968} := sql.bind("sys","pageviews","timestamp",0); _10:bat[:oid,:timestamp]{rows=0:lng,bid=2323} := sql.bind("sys","pageviews","timestamp",1); constraints.emptySet(_10); _10:bat[:oid,:timestamp]{rows=0:lng,bid=2323} := nil; _12:bat[:oid,:timestamp]{rows=0:lng,bid=2324} := sql.bind("sys","pageviews","timestamp",2); constraints.emptySet(_12); _12:bat[:oid,:timestamp]{rows=0:lng,bid=2324} := nil; _17:bat[:oid,:sht]{notnil=true,rows=55914459:lng,bid=1969} := sql.bind("sys","pageviews","channelid",0); _24:bat[:oid,:str]{rows=55914459:lng,bid=1961} := sql.bind("sys","pageviews","clientip",0); _8{rows=27957230:lng} := algebra.uselect(_1,2007-09-21 00:00:00.000,2007-09-22 00:00:00.000,true,true); _1:bat[:oid,:timestamp]{rows=55914459:lng,bid=1968} := nil; _15{rows=27957230:lng} := algebra.markT(_8,0@0); _8{rows=27957230:lng} := nil; _16{rows=27957230:lng} := bat.reverse(_15); _15{rows=27957230:lng} := nil; _19{rows=27957230:lng} := algebra.join(_16,_17); (ext40,grp38):= group.new(_19); _19{rows=27957230:lng} := nil; _22 := bat.mirror(ext40); ext40 := nil; _23 := algebra.joinPath(_22,_16,_17); _17:bat[:oid,:sht]{notnil=true,rows=55914459:lng,bid=1969} := nil; _26{rows=27957230:lng} := algebra.join(_16,_24); _16{rows=27957230:lng} := nil; _24:bat[:oid,:str]{rows=55914459:lng,bid=1961} := nil; (_27,grp55):= group.new(grp38); (_29,grp57):= group.derive(_27,grp55,_26); _27 := nil; grp55 := nil; _31 := bat.mirror(_29); _29 := nil; _32 := algebra.semijoin(_26,_31); _26{rows=27957230:lng} := nil; _31 := nil; _33:bat[:oid,:int] := aggr.count_no_nil(_32,grp38,_22); _32 := nil; _34:bat[:oid,:int] := aggr.count(grp38,grp38,_22); grp38 := nil; _22 := nil; _35 := sql.resultSet(3,1,_23); sql.rsColumn(_35,"sys.pageviews","channelid","smallint",16,0,_23); _23 := nil; sql.rsColumn(_35,"sys.pageviews","count_no_nil_clientip","int",15,0,_33); _33:bat[:oid,:int] := nil; sql.rsColumn(_35,"sys.pageviews","count_channelid","int",32,0,_34); _34:bat[:oid,:int] := nil; sql.exportResult(_35,""); end s5_1;

4 6

[MonetDB-users] Server Crashes when Client is Opened
by algatt 21 Oct '07

21 Oct '07

Hello, I have installed Monet and created the TPC-H schema and populated it with data. Once all data was copied, I restarted the computer to try the queries. However, now the server starts, but when I open the client everything closes immediately. I uninstalled it, and re-installed it and the same happens. Any help? Thank You. Regards, Alan -- View this message in context: http://www.nabble.com/Server-Crashes-when-Client-is-Opened-tf4657643.html#a… Sent from the monetdb-users mailing list archive at Nabble.com.

3 6

[MonetDB-users] delete query with where clause
by pawan123 10 Oct '07

10 Oct '07

When I use without where clause if ((hdl = mapi_query(dbh,"select * from logException )) == NULL || mapi_error(dbh) != MOK) die(dbh, hdl); It is working fine. But when I use with where condition if ((hdl = mapi_query(dbh,"delete * from logException where logid=1 )) == NULL || mapi_error(dbh) != MOK) die(dbh, hdl); It is deleting the record only from memory not from database. -- View this message in context: http://www.nabble.com/delete-query-with-where-clause-tf4598673.html#a131295… Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

[MonetDB-users] configure warning
by Ying Zhang 09 Oct '07

09 Oct '07

Hello, During 'configure' of the MonetDB4 module (Stable branch, MonetDB_4-20), I got the warning: configure: WARNING: fcntl.h: present but cannot be compiled configure: WARNING: fcntl.h: check for missing prerequisite headers? configure: WARNING: fcntl.h: see the Autoconf documentation configure: WARNING: fcntl.h: section "Present But Cannot Be Compiled" configure: WARNING: fcntl.h: proceeding with the preprocessor's result configure: WARNING: fcntl.h: in the future, the compiler will take precedence The same warning was given about several other header files, e.g. limits.h, pwd.h, time.h, sys/time.h, utime.h, ... The compilation was finished without further problems, but I would like to know why I got these warnings, since this is something new. Thanks! Jennie

2 1

[MonetDB-users] how to measure a query execution time
by xxiao 08 Oct '07

08 Oct '07

Hi, I need to measure the sql query execution time. So I used "-t" option with mclient command. For example, I use "mclient -lsql -umonetdb -Pmonetdb -t < 15.sql" to execute query 15 in TPC-H benchmark. I have the following outputs: Timer 935.765 msec Timer 717.294 msec Timer 0.163 msec % sys.supplier, sys.supplier, sys.supplier, sys.supplier, sys.revenue0 # table_name % s_suppkey, s_name, s_address, s_phone, total_revenue # name % int, varchar, varchar, varchar, decimal # type % 4, 18, 17, 15, 21 # length [ 8449, "Supplier#000008449", "Wp34zim9qYFbVctdW", "20-469-856-8873", 1772627.2087 ] Timer 3559.856 msec Timer 0.079 msec Timer 63.676 msec Timer 0.890 msec % .stats, .stats # table_name % rewrite, count # name % clob, int # type % 7, 1 # length [ "joinidx", 0 ] Timer 1.043 msec There are multiple lines started with "Timer" and ended by a time measurement in milli-sec. So is the execution time of query 15 in TPC-H equal to the sum of all these time measurements. In this case, 935.765+717.294+0.163+3559.856+0.079+63.676+0.890+1.043=5278.766 msec. I appreciate your response. Xiang -- View this message in context: http://www.nabble.com/how-to-measure-a-query-execution-time-tf4588760.html#… Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] convert string to timestamp in MonetDB
by pawan123 04 Oct '07

04 Oct '07

is there any function in MonetDB that can convert string (i.e.Thu Oct 04 14:36:17 2007) in to timestamp; Thanks Pawan -- View this message in context: http://www.nabble.com/convert-string-to-timestamp-in-MonetDB-tf4567699.html… Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

[MonetDB-users] What is optimizer_stats()
by Pranav Vaidya 03 Oct '07

03 Oct '07

Hi, I am new to MonetDB and I am trying to run the TPC-H benchmarks and I was able to successfully load and execute the benchmarks in MonetDB version 4. I also verified whether the tables were populated using Dbvis. However, what seems to fail is the lack of a function called optimizer_stats() in the run.all script. Is this a procedure or a function defined additionally which is needed. And why is it not present ? Do I need to run some additional scripts to have this run.all script go through successfully without any errors. Thanks and Regards, Pranav. -- View this message in context: http://www.nabble.com/What-is-optimizer_stats%28%29-tf4564307.html#a13027620 Sent from the monetdb-users mailing list archive at Nabble.com.

3 4

[MonetDB-users] (no subject)
by Matt Magoffin 02 Oct '07

02 Oct '07

Hello, I am interested in evaluating MonetDB/XQuery. I have some XML collections for a project I work on that are of 1-2 million individual XML documents for which I use XQuery to access for OLAP style reporting operations. This is a Java-based project, and while working on this project I developed a thin XQuery-centric API so that I could evaluate/use many different XQuery implementations, which my company released as an open source project called Xaj (http://sourceforge.net/projects/xaj) Currently I am using a Berkeley DB XML implementation, but am interested in exploring other options. I'd like to implement a MonetDB version of this API now, but I am having some difficulty finding out how to effectively access MonetDB through Java. So far I was able to use the MonetDB JDBC driver to construct XQuery statements and get XML results, and also I was able to construct MonetDB pf:add-doc() statements to add documents, but I am wondering if this is the best approach (and if so, how to add documents to a remote MonetDB server using the pf:add-doc() mechanism). The API I am trying to implement you can view here, to give you an idea what I'm trying to do: http://xaj.svn.sourceforge.net/viewvc/xaj/xaj/src/net/sf/xaj/XmlDb.java?rev… It was somewhat modeled after the defunct XML:DB API, but focused just on add/store/XQuery operations. Any help/advise would be greatly appreciated! -- m@

1 0