- developers-list - monetdb.org

Internal type conversion
by Robert Koch 17 Oct '14

17 Oct '14

SELECT count(*) FROM sapsr3.dfkkop WHERE vkont >= '000802037649' and vkont < '000802038649' ; +-------+ | L1 | +=======+ | 81196 | +-------+ 1 tuple (1.8s) SELECT count(*) FROM sapsr3.dfkkop WHERE vkont >= 000802037649 and vkont < '000802038649' ; +-------+ | L1 | +=======+ | 81196 | +-------+ 1 tuple (1.5s) SELECT count(*) FROM sapsr3.dfkkop WHERE vkont >= 000802037649 and vkont < 000802038649 ; conversion of string 'VI0802037717' to type int failed. Column "vkont" is defined as varchar(12), values are going from '000218123761' to 'ZZ0876213465'. 2nd statement is obviously doing some internal type conversion. 3rd statement fails. If there is done any kind of internal type conversion, I would expect that the outcome of this opereration must not depend on column's values.

1 0

Problems with GLOBAL TEMPORARY TABLES (GTT)
by Robert Koch 17 Oct '14

17 Oct '14

GLOBAL TEMPORARY TABLES do not work consistently. I am not sure, if all or some of the following problems described are intended. (is it bug or feature?) Out of 2 different sessions I am able to create GTTs with same name. Out of a third session I am able to see both tables (with same name), but not beeing able to access one of these tables. I am able to delete both tables, but I am not sure, which one is deleted first. Restarting MonetDB results in a situation where temp tables are still existent, but it is not possible to drop these tables out of any session. Is it a matter of privileges?

1 0

cost-based execution plan - incorporating cardinality?
by Robert Koch 17 Oct '14

17 Oct '14

SELECT opbel FROM sapsr3.dfkkop WHERE --mandt='200' and vkont='000802037649' ; runs in 5 ms. SELECT opbel FROM sapsr3.dfkkop WHERE mandt='200' and vkont='000802037649' ; runs in 15 s. (Factor 3.000) Column "mandt" has a cardinality of 2/1.000.000.000 (2 distinct values at 1 billion rows). Column vkont has a cardinality of 12.960.000/1.000.000.000. So I would expect to do the lookup on column vkont. Output of EXPLAIN: EXPLAIN SELECT opbel FROM sapsr3.dfkkop WHERE --mandt='200' and vkont='000802037649' ; mal ----------------------------------------------------------------------------------------------------------------------------------------------- function user.s11_3{autoCommit=true}(A0:str):void; X_3 := sql.mvc(); X_7 := sql.bind(X_3,"sapsr3","dfkkop","vkont",0); X_4:bat[:oid,:oid] := sql.tid(X_3,"sapsr3","dfkkop"); X_45 := algebra.subselect(X_7,X_4,A0,A0,true,true,false); (X_10,r1_10) := sql.bind(X_3,"sapsr3","dfkkop","vkont",2); X_46 := algebra.subselect(r1_10,A0,A0,true,true,false); X_13 := sql.bind(X_3,"sapsr3","dfkkop","vkont",1); X_47 := algebra.subselect(X_13,X_4,A0,A0,true,true,false); X_15 := sql.subdelta(X_45,X_4,X_10,X_46,X_47); X_17 := sql.bind(X_3,"sapsr3","dfkkop","opbel",0); (X_19,r1_22) := sql.bind(X_3,"sapsr3","dfkkop","opbel",2); X_21 := sql.bind(X_3,"sapsr3","dfkkop","opbel",1); X_22 := sql.projectdelta(X_15,X_17,X_19,r1_22,X_21); X_23 := sql.resultSet(1,1,X_22); sql.rsColumn(X_23,"sapsr3.dfkkop","opbel","varchar",12,0,X_22); X_28 := io.stdout(); sql.exportResult(X_28,X_23); end s11_3; # querylog.define("select\n opbel\nfrom\n sapsr3.dfkkop\nwhere\n --mandt=\\'200\\' and\n vkont=\\'000802037649\\';","default_pipe") # optimizer.mitosis() # optimizer.dataflow() EXPLAIN SELECT opbel FROM sapsr3.dfkkop WHERE mandt='200' and vkont='000802037649' ; mal ------------------------------------------------------------------------------------------------------------------------------------------------------ function user.s12_3{autoCommit=true}(A0:str,A1:str):void; X_4 := sql.mvc(); X_8 := sql.bind(X_4,"sapsr3","dfkkop","vkont",0); X_16 := sql.bind(X_4,"sapsr3","dfkkop","mandt",0); X_5:bat[:oid,:oid] := sql.tid(X_4,"sapsr3","dfkkop"); X_54 := algebra.subselect(X_16,X_5,A0,A0,true,true,false); (X_18,r1_21) := sql.bind(X_4,"sapsr3","dfkkop","mandt",2); X_55 := algebra.subselect(r1_21,A0,A0,true,true,false); X_20 := sql.bind(X_4,"sapsr3","dfkkop","mandt",1); X_56 := algebra.subselect(X_20,X_5,A0,A0,true,true,false); X_21 := sql.subdelta(X_54,X_5,X_18,X_55,X_56); X_57 := algebra.subselect(X_8,X_21,A1,A1,true,true,false); (X_11,r1_11) := sql.bind(X_4,"sapsr3","dfkkop","vkont",2); X_58 := algebra.subselect(r1_11,A1,A1,true,true,false); X_14 := sql.bind(X_4,"sapsr3","dfkkop","vkont",1); X_59 := algebra.subselect(X_14,X_21,A1,A1,true,true,false); X_23 := sql.subdelta(X_57,X_21,X_11,X_58,X_59); X_24 := sql.bind(X_4,"sapsr3","dfkkop","opbel",0); (X_26,r1_32) := sql.bind(X_4,"sapsr3","dfkkop","opbel",2); X_28 := sql.bind(X_4,"sapsr3","dfkkop","opbel",1); X_29 := sql.projectdelta(X_23,X_24,X_26,r1_32,X_28); X_30 := sql.resultSet(1,1,X_29); sql.rsColumn(X_30,"sapsr3.dfkkop","opbel","varchar",12,0,X_29); X_36 := io.stdout(); sql.exportResult(X_36,X_30); end s12_3; # querylog.define("explain\nselect\n opbel\nfrom\n sapsr3.dfkkop\nwhere\n mandt=\\'200\\' and\n vkont=\\'000802037649\\';","default_pipe") # optimizer.mitosis() # optimizer.dataflow()

1 0

bad performance of LIMIT/OFFSET
by Robert Koch 17 Oct '14

17 Oct '14

SELECT col1 FROM tbl WHERE col1 = 1 ; is executed in 5 ms. Table has 1 billion rows, resultset is about 350 rows. Same statement, but with LIMIT/OFFSET SELECT col1 FROM tbl WHERE col1 = 1 LIMIT 1 OFFSET 0 ; is executed in 1,03 s. Can't believe that subsetting from 350 to 1 takes 1 second.

2 1

Introducing testing scenario on big data
by Robert Koch 17 Oct '14

17 Oct '14

Hello list (and people behind), you are doing a great job on MonetDB and I have great respect to your work. During the last time I made some testing with MonetDB. I like the concept of memory mapped files, since files are "in-memory", even if MonetDB is restarted. Please understand my following mails to this list as contribution to your project. I am not very familiar to the BAT algebra or MAL language, moreover looking for an open-source alternative to commercial in-memory databases, able to run on commodity hardware, not beeing bounded to a specific operating system. For my use cases it is important to have a rich implementation of SQL and - even more important - to have a good SQL optimizer, since I have complex SQL statements with some joins over huge tables. From point of my observations, an improved optimizer would be the key for a wider spread of MonetDBs user community. On simple statements I see a performance comparable to commercial vedors/products. If my scenarios are interesting for you (developers), please feel free to contact me to discuss internals or to look inside the servers behavior yourself. I am working on 6 tables, each up to 1 billion rows and between 4 an 12 columns. Server with 300 GB RAM, fast SSDs on RAID 10 and 2 CPUs with 6 cores each. Testing was done both in DBVisualizer and in mclient (to avoid JDBC issues and fetch times). Running MonetDB v11.17.21 (Jan2014-SP3) on a fresh and clean Debian Wheezy. I will go on to describe my dev-related observations in separate mails in order to enable a separate discussion an each issue. Robert

1 0

msqldump - option for exporting all tables of a given schema
by gms_xy＠gmx.de 09 Oct '14

09 Oct '14

Is there an option for exporting all tables of a given schema in msqldump? If not -> request for feature!

1 0

mserver5 fails in running MAL plan
by Qing Guo 09 Oct '14

09 Oct '14

Hi, I run mserver5 in standalone mode. The SQL input works fine but the corresponding MAL input fails. See below for the details. - MonetDB version 11.17.21 - Command line mserver5 --dbpath=${DBPATH} --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} - Database TPC-H (scale factor: 1) - The SQL input: sql.start(); select count(*) from supplier, nation, region where r_name = 'EUROPE' and r_regionkey = n_regionkey and s_nationkey = n_nationkey; - The MAL input: (I use "explain" to get the MAL plan) sql.init(); function user.s0_0{autoCommit=true}(A0:str):void; X_3 := sql.mvc(); X_4:bat[:oid,:oid] := sql.tid(X_3,"sys","nation"); X_7 := sql.bind_idxbat(X_3,"sys","nation","nation_n_regionkey_fkey",0); (X_10,r1_10) := sql.bind_idxbat(X_3,"sys","nation","nation_n_regionkey_fkey",2); X_13 := sql.bind_idxbat(X_3,"sys","nation","nation_n_regionkey_fkey",1); X_15 := sql.projectdelta(X_4,X_7,X_10,r1_10,X_13); X_18 := sql.bind(X_3,"sys","region","r_name",0); X_16:bat[:oid,:oid] := sql.tid(X_3,"sys","region"); X_72 := algebra.subselect(X_18,X_16,A0,A0,true,true,false); (X_20,r1_21) := sql.bind(X_3,"sys","region","r_name",2); X_73 := algebra.subselect(r1_21,A0,A0,true,true,false); X_22 := sql.bind(X_3,"sys","region","r_name",1); X_74 := algebra.subselect(X_22,X_16,A0,A0,true,true,false); X_23 := sql.subdelta(X_72,X_16,X_20,X_73,X_74); X_25 := X_23; (X_26,r1_30) := algebra.join(X_15,X_25); X_28 := algebra.leftfetchjoin(X_26,X_4); X_29:bat[:oid,:oid] := sql.tid(X_3,"sys","supplier"); X_31 := sql.bind_idxbat(X_3,"sys","supplier","supplier_s_nationkey_fkey",0); (X_33,r1_37) := sql.bind_idxbat(X_3,"sys","supplier","supplier_s_nationkey_fkey",2); X_36 := sql.bind_idxbat(X_3,"sys","supplier","supplier_s_nationkey_fkey",1); X_37 := sql.projectdelta(X_29,X_31,X_33,r1_37,X_36); (X_38,r1_43) := algebra.join(X_28,X_37); X_40 := algebra.leftfetchjoin(X_38,X_28); X_41 := aggr.count(X_40); sql.exportValue(1,".L1","L1","wrd",64,0,6,X_41,""); end s0_0; user.s0_0("EUROPE"); mserver5 reports the following error message when running the MAL input: !TypeException:user.s0_0[23]:'algebra.join' undefined in: (X_38:any, r1_43:any) := algebra.join(X_28:bat[:oid,:oid], X_37:bat[:oid,:any]) It seems to me that X_28 is not in the right format. But I have no idea how it could happen and how to make it correct. Any thoughts on this will be much appreciated. Thanks! -Qing

1 0

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.

1 0

Re: MonetDB: default - Disable -pthread for Clang 6.*
by Sjoerd Mullender 19 Sep '14

19 Sep '14

Shouldn't this go to the Oct2014 branch? On 19/09/14 11:45, Dimitar Nedev wrote: > Changeset: 10ae6a3308bf for MonetDB > URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=10ae6a3308bf > Modified Files: > configure.ag > Branch: default > Log Message: > > Disable -pthread for Clang 6.* > This is in line OS X Yosemite and more specifically Xcode 6.0.1 shipping with Clang verion 6 > > > diffs (19 lines): > > diff --git a/configure.ag b/configure.ag > --- a/configure.ag > +++ b/configure.ag > @@ -1885,11 +1885,11 @@ if test "x$have_pthread" != xno; then > save_LIBS="$LIBS" > save_CPPFLAGS="$CPPFLAGS" > case $GCC-$have_pthread-$CC_ver in > - yes-auto-clang-5.*|yes-yes-clang-5.*) > - # clang 5.* (at least on Mac OSX Mavericks) does not > + yes-auto-clang-5.*|yes-yes-clang-5.*|yes-auto-clang-6.*|yes-yes-clang-6.*) > + # clang 5.*/6.* (Xcode 6.0) does not > # seem to have / require -pthread as compiler > - # option; on Mac OSX Mavericks, "Apple LLVM version > - # 5.0 (clang-500.2.79) (based on LLVM 3.3svn)" > + # option; on Mac OS X Yosamite, "Apple LLVM > + # version 6.0 (clang-600.0.51) (based on LLVM 3.5svn)" > # complains about "error: argument unused during > # compilation: '-pthread'". > # However, disabling -pthread completely might be > _______________________________________________ > checkin-list mailing list > checkin-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/checkin-list > -- Sjoerd Mullender

2 2

How varchar/text column works ?
by Mike 16 Sep '14

16 Sep '14

Hi, i have been working on monetdb for quite some time and am trying to understand how string column works so as i can (try to ) make it import string columns as fast as possible.The tool is external and generates monetdb binary compatible columns( and may be used to optimize tables in offline mode). As far as reading code and understanding it. here is my part String columns is kept in 2 parts, x.tail and x.theap ( where x is number ) x.tail contains pointers into x.theap x.theap has 2 regions/parts first part contains a 8KB hash table, to detect collisions and second part contains 8 zeroes,8 Byte hash, variable length String which is null terminated and aligned on 8 byte boundary (i am talking about x64 bit arch) and next string follows and so on However, the problem i am facing is , the tools works and generates columns till x.tail contains pointers addressing x.theap positions less than 255. The moment pointers are 2 byte tail entry, monetdb is confused. The reason is tail pointers are varsized for efficiency, and if there are few small string, pointers will fit in small memory and not waste 8 bytes per entry. However, i am unable to understand how monetdb figures out, how big entry is currently being used ? in case of small strings, it's 1 byte (tested) in case of some bigger string, it's 2 bytes (tested) and even in large string tables, it 4 bytes (tested) and large (>4 GB) sting tables, it can be 8 bytes ??? ( this case has not been tested) i have understood, it has something to with bat->T->shift , bat->T->width but still it doesn't work.So, i am definitely missing something. So, my questions are 1) is it an efficient method ( not talking about space efficiency) to have var-sized tail pointers. because it might involve lots of casting and so on during calculations.. 2) how to disable var-sized tail pointers (if it's possible) so as all pointers are 4byte or 8 byte long ( any compile time option or runtime option ) 3) if 2) not possible , then how does monetdb calculate tail pointer width. for example, i am using following code, to initialize new bat with my tail and theap pointers cap=num_of_tail_entries; bs = BATcreatedesc(TYPE_void, TYPE_str, 1); bn = &bs->B; BATsetdims(bn); BATkey(bn, TRUE); BATsetcapacity(bn, cap); BATsetcount(bn, cap); bn->tsorted = 0; bn->trevsorted = 0; bn->tdense = 0; bn->tkey = 0; bn->tvarsized=1; // try to set shift to 1 and thus width to 2 bytes //bn->T->shift=1; // this property seems to be used to calculate tail ptr size //bn->T->width=2; // this property seems to be used to calculate tail ptr size bn->T->varsized=1; then i load both heap and theap. The above code works and i am able to query the table as long as tail pointers are 1 byte long. However, as soon as pointers size is 2 bytes,it fails, so i must set something in BAT init to tell tail ptr size. Any clues are welcome... ( if more information is required, please ask.) Thanks for any help . Rgds mike

1 1