December 2014 - users-list

RAM usage of MonetDB
by Vijay Krishna 13 Dec '14

13 Dec '14

Hi, I have been testing MonetDB for quite a while and I am a bit quizzed by its RAM utilisation. I tested MonetDB with some complex data and queries as per my requirement in 2 machines - *one with 16 GB RAM and the other with 128 GB RAM *with no memory restrictions for applications. There are no applications other than MySQL and MonetDB running in those machines. The puzzling part was *all the queries took the same time with both the machines*. I have read quite a few documentations wherein I found that the MonetDB performance heavily depends upon RAM memory available. What am I missing? Any help much appreciated. Thanks & Regards, Vijayakrishna.P. Mobile : (+91) 9500402305.

2 1

Inserting arrays with SciQL
by Dimitar Misev 11 Dec '14

11 Dec '14

Hi, I've been experimenting with loading arrays into SciQL, but hit a wall at approx. 200MB TIFF size. Not sure if I'm doing something wrong or if there is a better way to import arrays? sql>CALL rs.attach('grey2.tif'); sql>CALL rs.import(2); Connection terminated Here's some metadata of the file: $ gdalinfo grey2.tif Driver: GTiff/GeoTIFF Files: grey2.tif Size is 21600, 10800 Coordinate System is: GEOGCS["WGS 84", DATUM["WGS_1984", SPHEROID["WGS 84",6378137,298.257223563, AUTHORITY["EPSG","7030"]], AUTHORITY["EPSG","6326"]], PRIMEM["Greenwich",0], UNIT["degree",0.0174532925199433], AUTHORITY["EPSG","4326"]] GeoTransform = 291.065568377342, 3.938632460246877e-06, 8.68784597348722e-07 -22.27513940185346, 8.250100004406028e-07, -3.669324727326387e-06 Metadata: AREA_OR_POINT=Point Image Structure Metadata: INTERLEAVE=BAND Corner Coordinates: Upper Left ( 291.066, -22.275) (291d 3'56.05"E, 22d16'30.50"S) Lower Left ( 291.075, -22.315) (291d 4'29.82"E, 22d18'53.17"S) Upper Right ( 291.151, -22.257) (291d 9' 2.31"E, 22d15'26.35"S) Lower Right ( 291.160, -22.297) (291d 9'36.09"E, 22d17'49.01"S) Center ( 291.113, -22.286) (291d 6'46.07"E, 22d17' 9.76"S) Band 1 Block=21600x1 Type=Byte, ColorInterp=Gray I managed to import a similarly sized file previously, but copying it to another file and importing again fails with "Connection terminated" as well. $ gdalinfo grey.tif Driver: GTiff/GeoTIFF Files: grey.tif Size is 21600, 10800 Coordinate System is `' Origin = (-180.016666666666652,90.016666666666666) Pixel Size = (0.016666666666665,-0.016666666666665) Metadata: TIFFTAG_DATETIME=2008:06:22 09:01:55 TIFFTAG_RESOLUTIONUNIT=2 (pixels/inch) TIFFTAG_SOFTWARE=Adobe Photoshop CS3 Macintosh TIFFTAG_XRESOLUTION=72 TIFFTAG_YRESOLUTION=72 Image Structure Metadata: INTERLEAVE=BAND Corner Coordinates: Upper Left (-180.0166667, 90.0166667) Lower Left (-180.0166667, -89.9833333) Upper Right ( 179.9833333, 90.0166667) Lower Right ( 179.9833333, -89.9833333) Center ( -0.0166667, 0.0166667) Band 1 Block=21600x1 Type=Byte, ColorInterp=Gray Thanks, Dimitar

1 1

Re: inner join very slow
by Stefan Manegold 08 Dec '14

08 Dec '14

Of what type(s) are the join attributes? How big is the expected join result? If you don't know, select count(*), sum(cnt) from (select count(*) as cnt from tmp__pierre2 group by siteniveau1, chapitreniveau1) as x; select count(*), sum(cnt) from (select count(*) as cnt from ref__at_lm_chapitres group by site, idchapitre1) as x; might help to give some estimation / idea ... Best, Stefan On December 1, 2014 6:16:11 PM CET, Pierre-Adrien Coustillas <pcoustillas(a)1g6.biz> wrote: >Hello, > > >With this query : >SELECT count(*) FROM tmp__pierre2 SRC INNER JOIN ref__at_lm_chapitres >SUB ON SRC.siteniveau1=SUB.site AND >SRC.chapitreniveau1=SUB.idchapitre1; > > >SELECT count(*) FROM tmp__pierre2; >+------------+ >| L1 | >+============+ >| 1 061 964 254 | > >SELECT count(*) FROM ref__at_lm_chapitres; >+---------+ >| L1 | >+=========+ >| 1 331 867 | > > >after several hours of running the query is still not finished and I >stopped > > >how to find out where the problem? > > >thanks > > >Pierre > > >-- >1G6 >52 route de bischwiller >67300 Schiltigheim >Société de Services et de Formations en Logiciels Libres >http://1g6.biz >Tél : 06 64 63 70 35 > > > >------------------------------------------------------------------------ > >_______________________________________________ >users-list mailing list >users-list(a)monetdb.org >https://www.monetdb.org/mailman/listinfo/users-list

2 3

COMMIT: transaction is aborted because of concurency conflicts, will ROLLBACK instead
by Pierre-Adrien Coustillas 08 Dec '14

08 Dec '14

Hello, is this a bug or a feature I do not understand ? I have a script bash : ------------------------------------ pcoustillas@monetdb:~$ cat bug_commit.sh #!/bin/bash i=1 while [ $i -le 3 ] do create="CREATE TABLE tmp_schampy_odc__lemondeclient_$i AS ( SELECT datamart__lemonde.id_unique, ( departement IN ( '08','10','67','42','10','15' ) ) AS top_departement FROM datamart__lemonde ) WITH DATA " echo $create mclient lemonde --statement="$create" & i=$(($i+1)) done ------------------------------------ I monetdb restarts before : service monetdb5-sql stop ; sleep 10 ; service monetdb5-sql start And i run script : pcoustillas@monetdb:~$ ./bug_commit.sh CREATE TABLE tmp_schampy_odc__lemondeclient_1 AS ( SELECT datamart__lemonde.id_unique, ( departement IN ( '08','10','67','42','10','15' ) ) AS top_departement FROM datamart__lemonde ) WITH DATA CREATE TABLE tmp_schampy_odc__lemondeclient_2 AS ( SELECT datamart__lemonde.id_unique, ( departement IN ( '08','10','67','42','10','15' ) ) AS top_departement FROM datamart__lemonde ) WITH DATA CREATE TABLE tmp_schampy_odc__lemondeclient_3 AS ( SELECT datamart__lemonde.id_unique, ( departement IN ( '08','10','67','42','10','15' ) ) AS top_departement FROM datamart__lemonde ) WITH DATA pcoustillas@monetdb:~$ operation successful operation successful COMMIT: transaction is aborted because of concurency conflicts, will ROLLBACK instead operation successful why is there a conflict while they are different tables that are created? I use the last version Database: MonetDB v11.19.7 (Oct2014-SP1) I have the same bug on : MonetDB v11.17.21 (Jan2014-SP3) Thank Pierre -- 1G6 52 route de bischwiller 67300 Schiltigheim Société de Services et de Formations en Logiciels Libres http://1g6.biz Tél : 06 64 63 70 35

3 4

Grant select on all tables
by Dennis Pallett 08 Dec '14

08 Dec '14

Hi all, I'm playing around with the authorisation/privileges functionality in MonetDB and I'm wondering if it's possible to grant a user the SELECT privilege on ALL tables (present and future tables) in a schema? At the moment I'm only able to grant this privilege on specific tables and I'd ideally like to create a user with full SELECT privileges and nothing more or less. Best regards, Dennis

1 1

SciQL-2 branch
by Dimitar Misev 04 Dec '14

04 Dec '14

Hi, I wanted to try SciQL so I got the SciQL-2 branch, but unfortunately it doesn't seem to be working. Following the tutorial at https://www.monetdb.org/Documentation/UserGuide/Tutorial I got stuck at $ mclient -u monetdb -d voc password: 3F000!CREATE PROCEDURE: no such schema 'rs' ParseException:SQLparser:3F000!CREATE PROCEDURE: no such schema 'rs' The default branch works ok. Any ideas on this problem, is there maybe a specific commit known to work? Thanks, Dimitar

4 9

Release Disk Space
by baraa Mohamad 02 Dec '14

02 Dec '14

Hello, I'm using MonetDB (on ubuntu) for doing some performance tests. I had added and deleted a lot of tables and right now I have no more free disk space since it seems that MonetDB does not clean up disk space after dropping tables. I would like to know the best way to clean up (as much as possible) disk space on MonetDB. Thank you for your help,Baraa

2 2

Error in mserver5 standalone
by Qing Guo 02 Dec '14

02 Dec '14

Hello, I was doing a performance profiling on MonetDB server so I run mserver5 in standalone. The command line I used is: mserver5 --dbpath=[path_to_your_db] --set mapi_open=false --set mapi_port=0 --set single-user=yes --set gdk_nr_threads=1 --set max_clients=1 --set sql_optimizer=default_pipe --set monet_daemon=no < [input] It worked for previous releases but not for the Oct2014. Instead, it gives the following message: "error initSQLreferences" The problem appears that "algebraRef" is not assigned a value before calling "initSQLreferences(void)". Any ideas to solve this problem? Much appreciate, thanks! Kevin

2 2

GROUP BY with JOIN produces unexpected results
by Jonas Goerlich 02 Dec '14

02 Dec '14

Hello, I am using MonetDB in a university project dealing with analysis of twitter data. "stuff" contains some pre-selected user ids. "tweets1207" contain tweet data for one day (userid, latitude, longitude, time, ....) What I want in the end is to find all (rounded) unique geo-coordinates of tweets by users in "stuff". I am using the following query: SELECT userid, ROUND(latitude,0) AS lat, ROUND(longitude,0) AS long FROM tweets1207 INNER JOIN stuff USING (userid) WHERE latitude <> 0 AND longitude <> 0 GROUP BY userid, lat, long ORDER BY userid, lat, long; The result set however still contains rows with duplicate values for "lat" and "long", e.g. userid lat long ----------------------------- 19703 41 -74 19703 41 -74 If I remove the INNER JOIN part from the query, the grouping works as expected. To check whether I am doing something fundamentally wrong (don't have much experience with SQL), I tried running the same query in a sqlite version of the data, where it produced the result I expected (although much, much slower...) Can anybody point me in the right direction? Am I doing something wrong here? Best regards and thanks in advance, Jonas

2 2

inner join very slow
by Pierre-Adrien Coustillas 01 Dec '14

01 Dec '14

Hello, With this query : SELECT count(*) FROM tmp__pierre2 SRC INNER JOIN ref__at_lm_chapitres SUB ON SRC.siteniveau1=SUB.site AND SRC.chapitreniveau1=SUB.idchapitre1; SELECT count(*) FROM tmp__pierre2; +------------+ | L1 | +============+ | 1 061 964 254 | SELECT count(*) FROM ref__at_lm_chapitres; +---------+ | L1 | +=========+ | 1 331 867 | after several hours of running the query is still not finished and I stopped how to find out where the problem? thanks Pierre -- 1G6 52 route de bischwiller 67300 Schiltigheim Société de Services et de Formations en Logiciels Libres http://1g6.biz Tél : 06 64 63 70 35

1 0