October 2014 - developers-list

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