- developers-list - monetdb.org

Abort multiline input in mclient
by Robert Koch 17 Oct '14

17 Oct '14

Is there any way to abort multiline input in mclient without leaving mclient? If I press Ctrl-C, mclient exits.

2 1

CASE/WHEN syntax in UPDATE/SET clauses
by Robert Koch 17 Oct '14

17 Oct '14

CASE/WHEN syntax would be useful for UPDATE/SET clauses. Example: UPDATE tbl1 SET col1 = CASE WHEN ... THEN ... ELSE ... END ; I am not sure if this is conform to ANSI SQL. I would find it useful. Today I have to write multiple UPDATE queries with different WHERE conditions.

1 0

Large intermediate results dureing columns update
by Robert Koch 17 Oct '14

17 Oct '14

Given a column defined as bigint (int64) with 1 billion rows. Storage size of this column is 8 GB. Values range in space of int (int32). UPDATE sapsr3.dfkkop SET vkont_int32 = vkont_int64 ; This query takes very long (minutes). During execution the data directory grows by 40 GB (!). I would expect the data cells do be converted an written into the new column directly. Even if MonetDB would materialize the whole source column as "intermediate result", I would expect not to exceed 8 GB for "intermediates" and 4 GB for the target column.

1 0

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