October 2014 - users-list

heapsize, speed, and vacuum
by Pierre-Adrien Coustillas 23 Oct '14

23 Oct '14

Hello, (google translate) I have a query that takes a long time : select count(distinct idvisiteur) from src_inbox__at__lm__visites; After two hours of running, it is not over , I'll kill her . if I look at the heapsize column storage ( ) sql>select * from storage() WHERE "table"='src_inbox__at__lm__visites'; +--------+----------------------------+---------------------+---------+-------------+-----------+-----------+------------+-------------+---------+--------+ | schema | table | column | type | location | count | typewidth | columnsize | heapsize | indices | sorted | +========+============================+=====================+=========+=============+===========+===========+============+=============+=========+========+ | sys | src_inbox__at__lm__visites | idvisite | varchar | 01/00/10025 | 315819936 | 5 | 2526559488 | 27755937792 | 0 | false | | sys | src_inbox__at__lm__visites | idvisiteur | varchar | 76/7667 | 315819936 | 19 | 2526559488 | 80449110016 | 0 | false | | sys | src_inbox__at__lm__visites | idvisiteuridentifie | varchar | 62/6201 | 315819936 | 2 | 1263279744 | 2222391296 | 0 | false | | sys | src_inbox__at__lm__visites | siteniveau1 | varchar | 77/7704 | 315819936 | 5 | 315819936 | 65536 | 0 | false | | sys | src_inbox__at__lm__visites | debutvisite | varchar | 30/3064 | 315819936 | 19 | 2526559488 | 10741678080 | 0 | false | | sys | src_inbox__at__lm__visites | catvisiteur | varchar | 31/3171 | 315819936 | 0 | 631639872 | 65536 | 0 | false | | sys | src_inbox__at__lm__visites | region | varchar | 67/6706 | 315819936 | 12 | 1263279744 | 2077556736 | 0 | false | | sys | src_inbox__at__lm__visites | ville | varchar | 40/4071 | 315819936 | 10 | 2526559488 | 22635741184 | 0 | false | | sys | src_inbox__at__lm__visites | os | varchar | 01/26/12673 | 315819936 | 8 | 631639872 | 65536 | 0 | false | | sys | src_inbox__at__lm__visites | browser | varchar | 01/44/14410 | 315819936 | 17 | 631639872 | 65536 | 0 | false | | sys | src_inbox__at__lm__visites | sourcevisite | varchar | 42/4234 | 315819936 | 1 | 631639872 | 65536 | 0 | false | | sys | src_inbox__at__lm__visites | idcampagne | varchar | 01/41/14100 | 315819936 | 0 | 631639872 | 65536 | 0 | false | | sys | src_inbox__at__lm__visites | prefixe | varchar | 01/22/12277 | 315819936 | 0 | 315819936 | 65536 | 0 | false | | sys | src_inbox__at__lm__visites | libcampagne | varchar | 65/6573 | 315819936 | 0 | 631639872 | 65536 | 0 | false | | sys | src_inbox__at__lm__visites | emailing | varchar | 01/04/10434 | 315819936 | 0 | 631639872 | 65536 | 0 | false | | sys | src_inbox__at__lm__visites | dateenvois | varchar | 01/22/12264 | 315819936 | 0 | 631639872 | 65536 | 0 | false | | sys | src_inbox__at__lm__visites | nbpages | int | 56/5615 | 315819936 | 4 | 1263279744 | 0 | 0 | false | | sys | src_inbox__at__lm__visites | dureevisite | time | 01/02/10211 | 315819936 | 4 | 1263279744 | 0 | 0 | false | | sys | src_inbox__at__lm__visites | id_unique | int | 01/23/12370 | 315819936 | 4 | 1263279744 | 0 | 0 | false | +--------+----------------------------+---------------------+---------+-------------+-----------+-----------+------------+-------------+---------+--------+ Is the heapsize column represents the amount of data to be placed in RAM ? I have 64GB of RAM on the server , so the idvisiteur column can not be held in RAM. Is this true? ( 64 000 000 000 < 80 449 110 016 ) I execute a vacuum (experimental command ?) : https://www.monetdb.org/Documentation/UserGuide/AutoloadingScript (Table vacuum commands (experimental!)) call vacuum('sys','src_inbox__at__lm__visites'); sql>select * from storage() WHERE "table"='src_inbox__at__lm__visites'; +--------+----------------------------+---------------------+---------+-------------+-----------+-----------+------------+------------+---------+--------+ | schema | table | column | type | location | count | typewidth | columnsize | heapsize | indices | sorted | +========+============================+=====================+=========+=============+===========+===========+============+============+=========+========+ | sys | src_inbox__at__lm__visites | idvisite | varchar | 36 | 315819936 | 5 | 2526559488 | 4906811392 | 0 | false | | sys | src_inbox__at__lm__visites | idvisiteur | varchar | 64/6435 | 315819936 | 19 | 1263279744 | 3667394560 | 0 | false | | sys | src_inbox__at__lm__visites | idvisiteuridentifie | varchar | 66/6640 | 315819936 | 2 | 1263279744 | 40042496 | 0 | false | | sys | src_inbox__at__lm__visites | siteniveau1 | varchar | 67/6743 | 315819936 | 5 | 315819936 | 73728 | 0 | false | | sys | src_inbox__at__lm__visites | debutvisite | varchar | 70/7045 | 315819936 | 19 | 2526559488 | 7576551424 | 0 | false | | sys | src_inbox__at__lm__visites | catvisiteur | varchar | 71/7152 | 315819936 | 0 | 631639872 | 73728 | 0 | false | | sys | src_inbox__at__lm__visites | region | varchar | 72/7252 | 315819936 | 12 | 1263279744 | 106561536 | 0 | false | | sys | src_inbox__at__lm__visites | ville | varchar | 73/7353 | 315819936 | 10 | 1263279744 | 1362165760 | 0 | false | | sys | src_inbox__at__lm__visites | os | varchar | 74/7453 | 315819936 | 8 | 631639872 | 73728 | 0 | false | | sys | src_inbox__at__lm__visites | browser | varchar | 75/7554 | 315819936 | 17 | 631639872 | 73728 | 0 | false | | sys | src_inbox__at__lm__visites | sourcevisite | varchar | 76/7655 | 315819936 | 1 | 631639872 | 73728 | 0 | false | | sys | src_inbox__at__lm__visites | idcampagne | varchar | 77/7761 | 315819936 | 0 | 631639872 | 73728 | 0 | false | | sys | src_inbox__at__lm__visites | prefixe | varchar | 01/00/10064 | 315819936 | 0 | 315819936 | 73728 | 0 | false | | sys | src_inbox__at__lm__visites | libcampagne | varchar | 01/01/10165 | 315819936 | 0 | 631639872 | 73728 | 0 | false | | sys | src_inbox__at__lm__visites | emailing | varchar | 01/02/10270 | 315819936 | 0 | 631639872 | 73728 | 0 | false | | sys | src_inbox__at__lm__visites | dateenvois | varchar | 01/03/10372 | 315819936 | 0 | 631639872 | 73728 | 0 | false | | sys | src_inbox__at__lm__visites | nbpages | int | 01/04/10476 | 315819936 | 4 | 1263279744 | 0 | 0 | false | | sys | src_inbox__at__lm__visites | dureevisite | time | 01/06/10600 | 315819936 | 4 | 1263279744 | 0 | 0 | false | | sys | src_inbox__at__lm__visites | id_unique | int | 01/07/10703 | 315819936 | 4 | 1263279744 | 0 | 0 | false | +--------+----------------------------+---------------------+---------+-------------+-----------+-----------+------------+------------+---------+--------+ heapsize of idvisteur = 3 667 394 560 (3,6GB) instead of 80 449 110 016 (80 GB) my query is present MUCH faster sql>select count(distinct idvisiteur) from src_inbox__at__lm__visites; +----------+ | L1 | +==========+ | 75528081 | +----------+ 1 tuple (4m 22s) HOURA !!! but WHY WHY WHY ? I want to understand 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 3

table not unique
by Pierre-Adrien Coustillas 22 Oct '14

22 Oct '14

Hello, (google translate) We had a crash monetdb following a full disk. We had already found that the bug not uniq table ( corrupt catalog ) could be related to these crash . And indeed this command will return no rows before the crash , but 20 lines after the crash : echo "SELECT name FROM SYS.TABLES WHERE system=false;" | mclient base | grep " " | grep -v " name " | grep -v " tuple" | sed 's/[| ]//g' | sort | uniq -c | grep -v " 1 " 5 bdm__lm__agregats 4 bdm__lm__agregats_temp 5 bdm__tra__agregats 4 bdm__tra__agregats_temp 5 ci__data__agregats 6 ci__data__reporting .. ... ... Is the October 2014 release will fix this trouble ? We are Jan2014 - SP3 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

4 3

Drop table not existe
by Pierre-Adrien Coustillas 20 Oct '14

20 Oct '14

Hello, (sorry my english, google translate) When I drop tables that does not exist, the drop may remain in queue. sql>DROP TABLE ci__data__agregats ; DROP TABLE ci__data__reporting ;DROP TABLE ci__data__reporting_ci ; DROP TABLE ci__data__reporting_regle; DROP TABLE ci__data__resultats; DROP TABLE ci__data__resultats_datamart; DROP TABLE ci__data__traductions; DROP TABLE ci__ref__calcul_final; DROP TABLE ci__ref__traductions_agregats; DROP TABLE src__arv__achat; DROP TABLE src__bil__commandes; DROP TABLE src__eve__abo; DROP TABLE src__eve__qlf; DROP TABLE src__eve__vpc; DROP TABLE src__mia__inscrits; DROP TABLE src__sdv__inscrits; operation successful (28.442ms) DROP TABLE: no such table 'ci__data__reporting' DROP TABLE: no such table 'ci__data__reporting_ci' DROP TABLE: no such table 'ci__data__reporting_regle' operation successful (28.490ms) operation successful (28.497ms) operation successful (28.502ms) operation successful (28.507ms) operation successful (28.511ms) DROP TABLE: no such table 'src__arv__achat' DROP TABLE: no such table 'src__bil__commandes' DROP TABLE: no such table 'src__eve__abo' DROP TABLE: no such table 'src__eve__qlf' DROP TABLE: no such table 'src__eve__vpc' DROP TABLE: no such table 'src__mia__inscrits' DROP TABLE: no such table 'src__sdv__inscrits' sql>select * from sys.queue(); +------+---------+----------------------------+----------------------------+----------+---------+-------------------+---------------------------------+ | qtag | user | started | estimate | progress | status | tag | query | +======+=========+============================+============================+==========+=========+===================+=================================+ | 12 | monetdb | 2014-10-17 14:34:16.000000 | 2014-10-17 14:34:16.024000 | null | running | 51539607560@0 | drop table src__arv__achat; | | 13 | monetdb | 2014-10-17 14:34:16.000000 | 6453-11-03 00:20:26.928000 | null | running | 140082976780320@0 | drop table src__bil__commandes; | | 14 | monetdb | 2014-10-17 14:34:16.000000 | null | null | running | 4@0 | drop table src__eve__abo; | | 15 | monetdb | 2014-10-17 14:34:23.000000 | null | null | running | 11776651@0 | select * from sys.queue(); | +------+---------+----------------------------+----------------------------+----------+---------+-------------------+---------------------------------+ 4 tuples (1.504ms) We do a lot of drop followed by create table. We believe that this is related to a concern for duplication in the catalog table ( table xxx is not single , corrupt catalog ? ) 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 2

MAL script error
by Hassan, Ahmad 14 Oct '14

14 Oct '14

Hi, One executing the MAL script http://pastebin.com/VUPbhD8V , I get the following errors: !TypeException:user.s0_11[87]:'algebra.join' undefined in: (X_150:any, r1_280:any) := algebra.join(X_137:bat[:oid,:oid], X_149:bat[:oid,:any]) !TypeException:user.s0_11[88]:'algebra.leftfetchjoin' undefined in: X_152:any := algebra.leftfetchjoin(r1_280:any, X_138:bat[:oid,:oid]) !TypeException:user.s0_11[94]:'algebra.join' undefined in: (X_161:any, r1_304:any) := algebra.join(X_152:any, X_160:bat[:oid,:any]) !TypeException:user.s0_11[95]:'algebra.leftfetchjoin' undefined in: X_404:any := algebra.leftfetchjoin(X_150:any, X_135:bat[:oid,:oid]) !TypeException:user.s0_11[100]:'algebra.leftfetchjoinPath' undefined in: X_174:bat[:oid,:int] := algebra.leftfetchjoinPath(X_161:any, X_404:any, X_173:bat[:oid,:any]) !TypeException:user.s0_11[106]:'algebra.leftfetchjoin' undefined in: X_183:any := algebra.leftfetchjoin(r1_304:any, X_182:bat[:oid,:any]) !TypeException:user.s0_11[107]:'algebra.join' undefined in: (X_184:any, r1_343:any) := algebra.join(X_183:any, X_107:bat[:oid,:int]) !TypeException:user.s0_11[109]:'algebra.leftfetchjoin' undefined in: X_187:any := algebra.leftfetchjoin(X_186:bat[:oid,:oid], X_183:any) !TypeException:user.s0_11[110]:'group.subgroupdone' undefined in: (X_188:any, r1_349:any, r2_349:any) := group.subgroupdone(X_187:any) !TypeException:user.s0_11[111]:'algebra.leftfetchjoin' undefined in: X_191:any := algebra.leftfetchjoin(r1_349:any, X_187:any) !TypeException:user.s0_11[113]:'algebra.leftfetchjoinPath' undefined in: X_195:bat[:oid,:lng] := algebra.leftfetchjoinPath(X_186:bat[:oid,:oid], r1_304:any, X_194:bat[:oid,:any]) !TypeException:user.s0_11[114]:'aggr.submin' undefined in: X_196:bat[:oid,:lng] := aggr.submin(X_195:bat[:oid,:lng], X_188:any, r1_349:any, _201:bit) !TypeException:user.s0_11[119]:'algebra.leftfetchjoin' undefined in: X_202:any := algebra.leftfetchjoin(r1_389:bat[:oid,:oid], X_191:any) !TypeException:user.main[2]:'user.s0_11' undefined in: _4:any := user.s0_11() The equivalent SQL query works fine but I am interested in running MAL script via mserver5. How can I resolve the above exceptions please? Thanks.

3 8

Re: Left/Right joins performance - Need help!!
by Vijay Krishna 10 Oct '14

10 Oct '14

Hello Martin, Thanks for the useful tip. On cross checking the trace statements of both inner and left join queries, found out that most of the time taken by the left join query is spent at the following stage. This instance has happened for at least 20 times and each is around 40-60 seconds long while executing a single left/right join query. <<<-------- start quote - sample trace left join query --------->>> 45983604 | X_2138=<tmp_14607>[64851] := bat.append(X_2128=<tmp_14607>:bat[:oid,:str][64851],X_2137=<tmp_14544>:bat[:oid,:str][64851],true); 46443428 | X_3402=<tmp_6631>[64851] := bat.append(X_3390=<tmp_6631>:bat[:oid,:str][64851],X_3401=<tmp_20102>:bat[:oid,:str][64851],true); 53520133 | X_1820=<tmp_12102>[64851] := bat.append(X_1808=<tmp_12102>:bat[:oid,:str][64851],X_1819=<tmp_10437>:bat[:oid,:str][64851],true); 64750174 | X_1864=<tmp_12404>[64851] := bat.append(X_1854=<tmp_12404>:bat[:oid,:str][64851],X_1863=<tmp_21302>:bat[:oid,:str][64851],true); <<<-------- end quote - sample trace left join query --------->>> To my understanding, I think the discrepancy is because of the different approaches on deltas in executing both the queries. Not sure if I'm right. I would also like to know if this large left/inner join discrepancy is a default Monet behaviour or this is just because of some ignorant mistake/misconfigurations of mine (considering my heavy data load). Thanks & Regards, Vijayakrishna.P. Mobile : 9500402305.

1 1

Group by and select distinct
by Vijay Krishna 09 Oct '14

09 Oct '14

Hi, The output of "*select * from table group by col1*" and "*select distinct(col1) from table*" appear to be *same*. But in SQL, group by returns all columns and distinct retrieves only that column values over which the function is ran. Is there any way to retrieve non duplicate rows based on a single column (similar to group by in SQL) in MonetDB? Thanks in advance. With Regards, Vijayakrishna.P. Mobile : 9500402305.

2 1

Left/Right joins performance - Need help!!
by Vijay Krishna 08 Oct '14

08 Oct '14

Hi all, I have been comparing MySQL with MonetDB. Obviously, queries that took minutes in MySQL got executed in a matter of few seconds in Monet. However, I found a real blockade with joins. I have 2 tables - each with 150 columns. Among these (150+150) columns, around 60 are CHARACTER LARGE OBJECT type. Both the tables are populated with around 50,000 rows - with data in all the 150 columns. The average length of data in a CLOB type column is 9,000 (varying from 2 characters to 20,000 characters). The primary key of both the tables have same values and the join is always based on the primary key. The rows are by default inserted in ascending order on the primary key. When I ran *an inner join query on these two tables with about 5 criteria and with limit 1000, Monet processed this query in 5 seconds*, which is completely impressive compared to MySQL's (19 seconds). But when I ran *the same query with same criteria and limits using a left or right joins, Monet took around 5 minutes, which is clearly way behind MySQL's (just 22 seconds)*. Having read a lot about Monet's blinding speed compared to traditional relation row based DBs, I could feel that I am missing something, but couldn't figure out what. Can anyone please tell me why there is such a huge difference in such query execution time and how I can prevent it? Much grateful to have any help. Thanks a lot in advance. P.S.: I am running Monet on Macbook Pro - 2.3 GHz Core i7 - Quad core processor with 8 GB RAM. With Regards, Vijayakrishna.P. Mobile : +91-9500402305.

2 1

MonetDB and clout-backup
by Thomas Yokota 07 Oct '14

07 Oct '14

Hi, I am interested in using MonetDB in my workflow. Currently, I am using SQLite. One reason is that I can store the db one a separate file in my dropbox dir, which allows me to work from multiple computers rather than relying on a VMware-type solution. Can MonetDB give me the same flexibility? If so, is it simply saving the DB into a dir nested within Dropbox dir? Thank you, Thomas

1 0

Can not drop the view
by Radovan Bičiště 06 Oct '14

06 Oct '14

Hello, I'm wondering if there is some hard core way to drop a view. Somehow I have three views that I'm not able to drop. I can issue drop statement several times and it returns success. Here is log from mclient: ceos@lux-drmax-ana:/u01/ceosdata/database/monetdb/drmax/crm$ mclient -d zkdev1 -u drmaxtst password: Welcome to mclient, the MonetDB/SQL interactive terminal (Jan2014-SP2) Database: MonetDB v11.17.17 (Jan2014-SP2), 'mapi:monetdb://lux-drmax-ana:50000/zkdev1' Type \q to quit, \? for a list of available commands auto commit mode: on sql>\dv VIEW drmaxtst.all_constraints VIEW drmaxtst.all_tab_columns VIEW drmaxtst.mmtest_v VIEW drmaxtst.pohyby_prodeje_v VIEW drmaxtst.prijmy_2012_v_mr_tmp VIEW drmaxtst.prijmy_v VIEW drmaxtst.prodeje_2011_v VIEW drmaxtst.prodeje_2012_v VIEW drmaxtst.prodeje_2012_v_mr_tmp VIEW drmaxtst.prodeje_2013_v VIEW drmaxtst.prodeje_2014_v VIEW drmaxtst.prodeje_v VIEW drmaxtst.stav_skladu_historie sql>drop view drmaxtst.prodeje_v; operation successful (3.168ms) sql>drop view drmaxtst.prodeje_v; operation successful (3.004ms) sql>drop view drmaxtst.prodeje_v; operation successful (7.501ms) sql>drop view drmaxtst.prodeje_v; operation successful (4.154ms) sql>drop view drmaxtst.prodeje_v; operation successful (2.351ms) sql>drop view drmaxtst.prodeje_v; operation successful (2.410ms) sql>drop view drmaxtst.prodeje_v; operation successful (2.355ms) sql>drop view drmaxtst.prodeje_v; operation successful (2.685ms) sql>drop view drmaxtst.prodeje_v; operation successful (2.646ms) sql>drop view drmaxtst.prodeje_v; operation successful (2.695ms) sql>drop view drmaxtst.prodeje_v; operation successful (2.390ms) sql>drop view drmaxtst.prodeje_v; operation successful (2.399ms) sql>drop view drmaxtst.prodeje_v; DROP VIEW: unknown view 'prodeje_v' sql>drop view drmaxtst.prodeje_v; DROP VIEW: unknown view 'prodeje_v' sql>\dv VIEW drmaxtst.all_constraints VIEW drmaxtst.all_tab_columns VIEW drmaxtst.mmtest_v VIEW drmaxtst.pohyby_prodeje_v VIEW drmaxtst.prijmy_2012_v_mr_tmp VIEW drmaxtst.prijmy_v VIEW drmaxtst.prodeje_2011_v VIEW drmaxtst.prodeje_2012_v VIEW drmaxtst.prodeje_2012_v_mr_tmp VIEW drmaxtst.prodeje_2013_v VIEW drmaxtst.prodeje_2014_v VIEW drmaxtst.prodeje_v VIEW drmaxtst.stav_skladu_historie sql> I guess my only option is to dump a reload the database. :( Radovan

5 11

any tips to COPY INTO from a csv with whitespace around some DOUBLE fields?
by Anthony Damico 03 Oct '14

03 Oct '14

here's the problem-- Unable to execute statement 'COPY 2626675 OFFSET 2 RECORDS INTO OPPR_ALL_DTL_GNRL_09302014 FROM 'C:\Users\anthonyd\AppData\Local\...'. Server says '!value '1 ' from line 1 field 51 not inserted, expecting type double'. i could import it as VARCHAR and then CAST it back into a DOUBLE type, but i'm wondering if anyone has an easier/more clever way to import this? handling it manually would mean that i cannot use the monet.read.csv() function, so it's a bit more work. thanks!

2 2