- users-list - monetdb.org

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 03 Dec '14

03 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

espace disk after crash
by Pierre-Adrien Coustillas 28 Nov '14

28 Nov '14

Hello, I try to understand the use of disk space after disk full. I look at the files in the folder base /bat/ root@groupelemonde:/var/lib/monetdb/lemonde# ls -l bat/06/676.* -rw------- 1 monetdb monetdb 1579876352 oct. 15 17:42 bat/06/676.tail -rw------- 1 monetdb monetdb 3606183936 nov. 28 15:03 bat/06/676.theap And i look sys.storage SELECT location,columnsize,heapsize FROM sys.storage WHERE location LIKE '06/676'; +----------+------------+------------+ | location | columnsize | heapsize | +==========+============+============+ | 06/676 | 1579850648 | 3606183936 | 676.tail is columnsize column 676.theap is heapsize column but I have 2 large file (> 100 Go) root@groupelemonde:/var/lib/monetdb/lemonde# ls -l bat/06/670.* -rw------- 1 monetdb monetdb 120641335296 nov. 28 14:28 bat/06/670.tail root@groupelemonde:/var/lib/monetdb/lemonde# ls -l bat/06/636.* -rw------- 1 monetdb monetdb 144702111744 nov. 28 12:32 bat/06/636.tail sql>select location,columnsize,heapsize from sys.storage WHERE location IN ('06/636','06/670'); 0 tuples (34.050ms) the modification time of two files is identical to the full disk on my server. Is what I can delete these 2 files ? 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

Big Slowdown (15-20X ) on import functions in Monetdb October 2014 Release
by Ashish Kumar Singh 28 Nov '14

28 Nov '14

All, With October,2014 release we are stucked in to very strange performance issue while importing data on virtualised environments. On Virtual machines in our local VMware servers and amazon machine we are seeing import performance has downgraded to close to 15 to 20 while importing ~ 2000 files as compared to 1 hour time being taken with old release. This we are seeing consistently with CentOS, SLES vm and configuration is 4 Core 33 GB machine. We tried on quite a few amazon vm just to make sure its not an issue with a particular instance setup. On a physical machine of 4 core 24 GB we are able to match speed of import with old monetdb. We are issuing copy into command through JDBC connection. Can you guys help me with pointers on this issue, I will be happy to share any debug info. Regards, Ashish Kumar Singh Mobile: +91-9972045095 Skype: toashishk(a)gmail.com

5 17