November 2012 - users-list

How do I interpret testweb listing for the latest patches the next release?
by Brandon Jackson 17 Nov '12

17 Nov '12

45868:949c8b8db28d Was suggested to me for fixing the recent error with aggregates and scaling. Which area is the best one to go to for receiving the patches going into the next release? Thanks for the guidance. Brandon _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

2 1

Client PHP too slow with many results
by Eduardo Oliveira 16 Nov '12

16 Nov '12

Hello, I'm finding php client too slow returning big result sets. I'm using php client i i downloaded from http://dev.monetdb.org/downloads/sources/Latest/ MonetDB-11.13.3.tar.bz2 Some benchmark down, anyway of get the results faster? LIMIT 50K [root@monetdb examples]# php simple_query.php *9* [root@monetdb examples]# time mclient -u adctest -d adctest --statement='SELECT * FROM summary_sent LIMIT 50000' > res.txt password: real 0m4.566s <- already counting with some time to password input user 0m1.272s sys 0m0.132s ---- LIMIT 100K [root@monetdb examples]# time mclient -u adctest -d adctest --statement='SELECT * FROM summary_sent LIMIT 100000' > res.txt password: *real 0m7.184s* user 0m2.545s sys 0m0.266s [root@monetdb examples]# php simple_query.php *18* ---------- Code: [root@monetdb examples]# cat simple_query.php <?php require '../lib/php_monetdb.php'; $db = monetdb_connect("sql", '127.0.0.1', 50000, 'adctest', 'adctest', 'adctest') or trigger_error(monetdb_last_error()); $start_query_monetdb = time(); $res = monetdb_query($db, monetdb_escape_string('SELECT * FROM summary_sent LIMIT 100000')) or trigger_error(monetdb_last_error()); while ( $row = monetdb_fetch_object($res) ) { } $time = time() - $start_query_monetdb; echo $time . "\n"; /* Free the result set */ monetdb_free_result($res); /* Disconnect from the database */ if (monetdb_connected($db)) { monetdb_disconnect($db); } -- *Eduardo Oliveira * *IT* ***Email:* eduardo.oliveira(a)adclick.pt <nuno.morais(a)adclick.pt> *Web: * www.adclickint.com ** <http://www.adclickint.com/> * * <http://www.adclickint.com/> <http://www.adclickint.com/> _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

4 5

Ubuntu compilation problem
by Matthieu Guamis 14 Nov '12

14 Nov '12

Hello, I'm tryingto compile MonetDB on Ubuntu 12.04 x64but It fails with alot of "../mapilib/.libs/libmapi.so: undefined reference to `SHA256'".I though it was related with a library linking problem,but I use LDFLAGS=-lssl -lcrypt and CFLAGS=-Wl,-verbose which give me : attempt to open /usr/lib/gcc/x86_64-linux-gnu/4.6/../../../x86_64-linux-gnu/libssl.so succeeded -lssl (/usr/lib/gcc/x86_64-linux-gnu/4.6/../../../x86_64-linux-gnu/libssl.so) ... attempt to open /usr/lib/gcc/x86_64-linux-gnu/4.6/../../../x86_64-linux-gnu/libcrypt.so succeeded -lcrypt (/usr/lib/gcc/x86_64-linux-gnu/4.6/../../../x86_64-linux-gnu/libcrypt.so) I'm not very familiar with Make and C,so Iwould appreciate some help. PS: Could it be related to the packaging problem reported on the lastreleases notes "No Debian and Ubuntu packages were built for this release due to a packaging failure"? Thanks a lot -- *Matthieu Guamis* *Logo Axège <http://www.axege.com/>* /Axège// 22 bis, rue des Chazots 63170 Aubière/ Tél: +33 (0)4 63 05 95 40 Fax: +33 (0)4.73.70.65.29 Email: matthieu.guamis(a)axege.com <mailto:matthieu.guamis@axege.com> _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

5 5

growing size of database
by Medha Atre 14 Nov '12

14 Nov '12

Hi, I am running the latest MonetDB 11.13.3. I loaded a pretty tiny RDF dataset of 13 million triples and created some "vertical partitions" on predicate values. After having done dataset loading, I checked the size of the dataset folder and it showed to be _258 MB_. Then I ran about 6000+ bulk queries by periodically shutting down the server after about 1000 queries each time. The queries finished successfully and there was no crash. After the final query I stopped the database "monetdb stop dbname" and shutdown the main server too "monetdbd stop /path/to/dbfarm". After that I again checked the size of the database folder and then it showed it to be _5.8GB_ and the "HC" folder under "bat" was shown to have size of about 3 GB with some other files having sizes between 300-700 MBs. It seems mysterious to me why would the size of database go on increasing as much as 25 times the original size. I intend to load much larger data (> 1 billion triples), and want to avoid growing disk size of the dataset due to limited disk-space. Can someone please enlighten? Medha _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

3 6

Database Memory Usage
by Ashish Kumar Singh 12 Nov '12

12 Nov '12

HI All, While calculating monetdb size I am observing below : When I am inserting records to Monet DB using JDBC total memory and VM usage is going to 10 G + but when I see file system size of the database directory it is showing something like 1 GB . Is some compression coming to picture here while storing data in file system? Any pointers will be helpful. Regards, Ashish _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

1 3

Is there a way to define type aliases for internal types?
by Percy Wegmann 11 Nov '12

11 Nov '12

I'd like to define aliases for CLOB and would like to know if/how this is possible. I tried this: create type "array" external name "str"; create table atest(it array); insert into atest values('test'); Yielding the following error: TypeException:user.s2_27[3]:'calc.array' undefined in: _5:any := calc.array(_4:str) program contains errors So, that was probably not the right way to do it ... Thanks, -- Percy Wegmann +1 512 637 8500 ext 148 _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

2 2

Standard Deviation, Variance, and Quantile Functions
by Anthony Damico 08 Nov '12

08 Nov '12

Hi all, I'm trying to run a simple standard deviation function on a column using MonetDB -- select stdev( columnname ) from tablename -- is this possible with the current release? The only posts I can find about stdev() or variance() are quite old, such as a 2008 post requesting medians (which has been implemented) and quantiles (which I cannot find?) http://osdir.com/ml/db.monetdb.bugs/2008-06/msg00206.html a 2010 post offering to add these functions to the main release code. http://mail.monetdb.org/pipermail/users-list/2010-January/003836.html What would be the easiest way to get MonetDB to produce standard deviations, variances, and quantiles (let's say the 25th percentile)? >From the discussion list, it looks like people have succeeded in doing it, but they're probably much more advanced users than I am. ;) Thanks!!! _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

3 2

[MonetDB-users] Different results with same query
by Matthieu Guamis 08 Nov '12

08 Nov '12

Hello, Running the same query on the same data leeds to different results between postgresql and monetdb. I have not been able to get a simple example to reproduce the problem. So here is what I observe: the query: select temps_mois.rfoperdmo as c1, sum((case when dwhinv.dwhinv___rfodomide = 'RH' and dwhinv.dwhinv___rfoindide = 'tech_rh_effectif_rem_reel' then dwhinv.dwhinvqte else 0 end)) as m0 from rfoper_temps_mois as temps_mois, dwhinv as dwhinv, rfovsn as rfovsn_0, rrhamv as rrhamv_1, rrhcov as rrhcov_2, rfoadv as rfoadv_3 where temps_mois.rfoper___rforefide = 'HPLUS' and dwhinv.dwhinv___rforefide = 'HPLUS' and dwhinv.dwhinv___rfodomide = 'RH' and dwhinv.dwhinv___rfoindide = 'tech_rh_effectif_rem_reel' and dwhinv.dwhinvdtd = temps_mois.rfoperdtd and temps_mois.rfoperyea = '2011' and rfovsn___rforefide = 'HPLUS' and dwhinv.dwhinv___rfovsnide = rfovsn_0.rfovsnide and rfovsn_0.rfovsnide = '201111_reel' and rrhamv_1.rrhamv___rrhvemide='GRACOR' AND rrhamv_1.rrhamvrvs=1 AND rrhamv_1.rrhamv___rforefide= 'HPLUS' and dwhinv.dwhinv___rrhempide = rrhamv_1.rrhamvinf and rrhamv_1.rrhamvsup = 'CEMP' and rrhcov_2.rrhcov___rrhvcoide='CONTRATS' AND rrhcov_2.rrhcovrvs=1 AND rrhcov_2.rrhcov___rforefide= 'HPLUS' and dwhinv.dwhinv___rrhcntide = rrhcov_2.rrhcovinf and rrhcov_2.rrhcovsup = 'CONTRATS' and rfoadv_3.rfoadv___rfovdeide='STRC' AND rfoadv_3.rfoadvrvs=1 AND rfoadv_3.rfoadv___rforefide= 'HPLUS' and dwhinv.dwhinv_p2rfodstide = rfoadv_3.rfoadvinf and rfoadv_3.rfoadvsup = 'HPLUS' group by c1 order by c1 Postgresql result: c1 | m0 ----+----------------- 01 | 7111.5376967750 02 | 7100.9108821426 03 | 7150.2597967742 04 | 7151.4283666667 05 | 7109.1641451610 06 | 6976.2108421239 07 | 6329.7404193564 08 | 6302.9823032247 09 | 6426.9459633351 10 | 6519.6889580648 11 | 6549.5235033402 12 | 6492.5477161292 (12 lignes) Monetdb result: +------+----------------------+ | c1 | m0 | +======+======================+ | 12 | 1.000000 | | 11 | 1.000000 | | 10 | 2.000000 | | 09 | 0.366667 | | 08 | 1.000000 | | 07 | 1.000000 | | 06 | 1.000000 | | 05 | 3.000000 | | 04 | 4.000000 | | 03 | 15.000000 | | 02 | 9.964286 | | 01 | 1.000000 | +------+----------------------+ 12 tuples (212.346ms) As you see, all "m0" values are very different. I have no idea of what is causing this but if I modify the query in order to simplify the CASE...WHEN...ELSE part of the select, it produce the expected result: replacing sum((case when dwhinv.dwhinv___rfodomide = 'RH' and dwhinv.dwhinv___rfoindide = 'tech_rh_effectif_rem_reel' then dwhinv.dwhinvqte else 0 end)) as m0 by sum((case when dwhinv.dwhinv___rfoindide = 'tech_rh_effectif_rem_reel' then dwhinv.dwhinvqte else 0 end)) as m0 Its not exactly the same query but it must leeds to the same result due to my test datas (its the case in postgres) I join to this email two files with the TRACES, respectively for the request with bad/correct result (ko.txt/ok.txt) Config: Ubuntu Server 11.04 x64 MonetDB v11.7.9 (Dec2011-SP2), MonetDB Database Server v1.6 (Dec2011-SP2), MonetDB Database Server Toolkit v1.0 (Dec2011-SP2) Thank you very much for your help!! -- *Matthieu Guamis* *Logo Axège <http://www.axege.com/>* /Axège// 23,rue Saint-Simon 63000 Clermont-Ferrand/ Tél: +33 (0)4 63 05 95 40 Fax: +33 (0)4.73.70.65.29 Email: matthieu.guamis(a)axege.com <mailto:matthieu.guamis@axege.com>

3 4

JDBC and transaction serialization
by Tapomay Dey 08 Nov '12

08 Nov '12

Hi, I have written a multi-threaded app that consumes multiple logs simultaneously and generates DDL/DML statements for monetdb. It uses JDBC and the threads write to a single table. However I am getting "COMMIT: transaction is aborted because of concurency conflicts, will ROLLBACK instead" very frequently. Also when it retries the insert there is some inconsistency that I have not been able to try and debug yet. Eventually there are more rows than inserts. Weird thing is I also have a unique key constraint on the table. I am guessing this happen as monetdb doesn't use locking for serialization. I have tried connection.setAutoCommit(true) by itself and also with connection.setTransactionIsolation(NONE). But in vain. Please help. Thanks and Regards, Tapomay Dey. _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

2 2

Re: set sql_debug in monetdb configuration file
by Liwen -George- Zhang 06 Nov '12

06 Nov '12

Hi Fabian,Thanks for looking into this. The reason why i want this is that I can't figure out why my udf function does not work. I added a MAL function to the udf.c and related files, it works when i used it in mal client, but can't make it work in sql after creating a sql function. I checked the udf.reverse example, looks like it need only a SQL function for the scalar version of reverse, and the batudf.reverse will be used automatically if i "select reverse(colname) from table". Anyway, i tried different combination, my scalar version udf function works, but BAT version don't work, thinking that sql_debug might provide some info for me before i jump into gdb. As to setting this parameter, i know i can use mserver5 --config=filename, or just mserver5 --set sql_debug, but don't know how to let monetdbd to use a configuration file. thanks for your help!George > From: users-list-request(a)monetdb.org > Subject: users-list Digest, Vol 3, Issue 4 > To: users-list(a)monetdb.org > Date: Mon, 5 Nov 2012 12:00:01 +0100 > > Send users-list mailing list submissions to > users-list(a)monetdb.org > > To subscribe or unsubscribe via the World Wide Web, visit > http://mail.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. Re: set sql_debug in monetdb configuration file (Fabian Groffen) > 2. Re: Standard Deviation, Variance, and Quantile Functions > (Fabian Groffen) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Mon, 5 Nov 2012 08:35:51 +0100 > From: Fabian Groffen <Fabian.Groffen(a)cwi.nl> > To: Communication channel for MonetDB users <users-list(a)monetdb.org> > Subject: Re: set sql_debug in monetdb configuration file > Message-ID: <20121105073551.GF49556(a)cwi.nl> > Content-Type: text/plain; charset="utf-8" > > On 04-11-2012 23:10:22 -0800, Liwen -George- Zhang wrote: > > Hi, > > I want to set?sql_debug=16384 for mserver5, where can i set this value? I > > can't find any documentation on where's the monetdb configuration file. > > Why would you want that? > > If you're sure you want so, it should be clear to you how to do that as > well. This is absolutely not a supported mode of operation. > > Fabian > _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

2 1