- users-list - monetdb.org

High amount of VIRT and high amount of writing
by amihay gonen 29 Jul '14

29 Jul '14

Hi all , we are trying to compare oracle vs monetdb. the query we are try to run is : select count( client_code_new ) as s from ( SELECT t.client_code_new, t.bank, t.branch_new, t.ACCOUNT, t.instrument_ident, MAX (t.system_code) FROM transactions t left outer join ( SELECT c.linked_open_transaction_new as linked_open_transaction_new FROM calculated_taxs c GROUP BY c.linked_open_transaction_new) c_temp on t.transaction_no = c_temp.linked_open_transaction_new join clients_list cl on cl.client_code = t.client_code_new AND cl.branch = t.branch_new WHERE t.opg_cls = 'O' AND (t.close_trans_disregard_balance_new) =0 and new_system_code = 1 GROUP BY t.client_code_new, t.bank, t.branch_new, t.ACCOUNT, t.instrument_ident) a ; tables sizes are: transactions = 103M rows calculated_taxs= 79M rows The query is taking 1min to run. we've notice that during the query run the VIRT column (from top utility) is become very high 10G , and during the we see extensive write to disk . why does monet is do so much writing ? thanks, amihay bellow is output of minimal_pipe optimizer +------------------------------------------------------------------------------------------------------------------------------------------------------------+ | mal | +============================================================================================================================================================+ | function user.s4_3{autoCommit=true}(A0:str,A1:int,A2:int):void; | | X_5 := sql.mvc(); | | X_6:bat[:oid,:oid] := sql.tid(X_5,"sys","clients_list"); | | X_9 := sql.bind(X_5,"sys","clients_list","client_code",0); | | (X_12,r1_12) := sql.bind(X_5,"sys","clients_list","client_code",2); | | X_15 := sql.bind(X_5,"sys","clients_list","client_code",1); | | X_17 := sql.delta(X_9,X_12,r1_12,X_15); | | X_18 := algebra.leftfetchjoin(X_6,X_17); | | X_19 := batcalc.int(X_18); | | X_20:bat[:oid,:wrd] := batcalc.hash(X_19); | | X_23 := calc.int(22); | | X_25 := sql.bind(X_5,"sys","clients_list","branch",0); | | (X_27,r1_27) := sql.bind(X_5,"sys","clients_list","branch",2); | | X_29 := sql.bind(X_5,"sys","clients_list","branch",1); | | X_30 := sql.delta(X_25,X_27,r1_27,X_29); | | X_31 := algebra.leftfetchjoin(X_6,X_30); | | X_32:bat[:oid,:wrd] := mkey.bulk_rotate_xor_hash(X_20,X_23,X_31); | | X_33:bat[:oid,:int] := bat.new(nil:oid,nil:int); | | X_36:bat[:oid,:oid] := sql.tid(X_5,"sys","transactions"); | | X_38 := sql.bind(X_5,"sys","transactions","close_trans_disregard_balance_new",0); | | (X_40,r1_40) := sql.bind(X_5,"sys","transactions","close_trans_disregard_balance_new",2); | | X_43 := sql.bind(X_5,"sys","transactions","close_trans_disregard_balance_new",1); | | X_44 := sql.delta(X_38,X_40,r1_40,X_43); | | X_45 := algebra.leftfetchjoin(X_36,X_44); | | X_46 := A1; | | X_47 := sql.bind(X_5,"sys","transactions","new_system_code",0); | | (X_50,r1_50) := sql.bind(X_5,"sys","transactions","new_system_code",2); | | X_53 := sql.bind(X_5,"sys","transactions","new_system_code",1); | | X_55 := sql.delta(X_47,X_50,r1_50,X_53); | | X_56 := algebra.leftfetchjoin(X_36,X_55); | | X_57 := A2; | | X_58 := sql.bind(X_5,"sys","transactions","opg_cls",0); | | (X_60,r1_60) := sql.bind(X_5,"sys","transactions","opg_cls",2); | | X_62 := sql.bind(X_5,"sys","transactions","opg_cls",1); | | X_63 := sql.delta(X_58,X_60,r1_60,X_62); | | X_64 := algebra.leftfetchjoin(X_36,X_63); | | X_65 := A0; | | X_66 := algebra.subselect(X_64,X_65,X_65,true,true,false); | | X_69 := algebra.subselect(X_56,X_66,X_57,X_57,true,true,false); | | X_70 := algebra.subselect(X_45,X_69,X_46,X_46,true,true,false); | | X_71 := sql.bind(X_5,"sys","transactions","transaction_no",0); | | (X_74,r1_74) := sql.bind(X_5,"sys","transactions","transaction_no",2); | | X_77 := sql.bind(X_5,"sys","transactions","transaction_no",1); | | X_78 := sql.delta(X_71,X_74,r1_74,X_77); | | X_79 := algebra.leftfetchjoin(X_36,X_78); | | X_80 := algebra.leftfetchjoin(X_70,X_79); | | X_81:bat[:oid,:oid] := sql.tid(X_5,"sys","calculated_taxs"); | | X_83 := sql.bind(X_5,"sys","calculated_taxs","linked_open_transaction_new",0); | | (X_86,r1_86) := sql.bind(X_5,"sys","calculated_taxs","linked_open_transaction_new",2); | | X_89 := sql.bind(X_5,"sys","calculated_taxs","linked_open_transaction_new",1); | | X_91 := sql.delta(X_83,X_86,r1_86,X_89); | | X_92 := algebra.leftfetchjoin(X_81,X_91); | | (X_93,r1_93,r2_93) := group.subgroupdone(X_92); | | X_96 := algebra.leftfetchjoin(r1_93,X_92); | | X_97 := batcalc.lng(X_96); | | (X_98,r1_98) := algebra.join(X_80,X_97); | | X_100 := sql.bind(X_5,"sys","transactions","client_code_new",0); | | (X_102,r1_102) := sql.bind(X_5,"sys","transactions","client_code_new",2); | | X_104 := sql.bind(X_5,"sys","transactions","client_code_new",1); | | X_106 := sql.delta(X_100,X_102,r1_102,X_104); | | X_107 := algebra.leftfetchjoin(X_36,X_106); | | X_108 := algebra.leftfetchjoin(X_70,X_107); | | X_109 := algebra.leftfetchjoin(X_98,X_108); | | X_110 := bat.append(X_33,X_109,true); | | X_112 := bat.mirror(X_80); | | X_113 := algebra.tdiff(X_112,X_98); | | X_114 := algebra.leftfetchjoin(X_113,X_108); | | X_115 := bat.append(X_110,X_114,true); | | X_116:bat[:oid,:wrd] := batcalc.hash(X_115); | | X_119 := calc.int(22); | | X_121:bat[:oid,:int] := bat.new(nil:oid,nil:int); | | X_124 := sql.bind(X_5,"sys","transactions","branch_new",0); | | (X_128,r1_128) := sql.bind(X_5,"sys","transactions","branch_new",2); | | X_131 := sql.bind(X_5,"sys","transactions","branch_new",1); | | X_133 := sql.delta(X_124,X_128,r1_128,X_131); | | X_134 := algebra.leftfetchjoin(X_36,X_133); | | X_135 := algebra.leftfetchjoin(X_70,X_134); | | X_136 := algebra.leftfetchjoin(X_98,X_135); | | X_137 := bat.append(X_121,X_136,true); | | X_138 := algebra.leftfetchjoin(X_113,X_135); | | X_139 := bat.append(X_137,X_138,true); | | X_140:bat[:oid,:wrd] := mkey.bulk_rotate_xor_hash(X_116,X_119,X_139); | | (X_141,r1_141) := algebra.join(X_32,X_140); | | X_143 := algebra.leftfetchjoin(X_141,X_19); | | X_144 := algebra.leftfetchjoin(r1_141,X_115); | | X_145:bat[:oid,:bit] := batcalc.==(X_143,X_144); | | X_147 := calc.bit(true); | | X_149 := algebra.subselect(X_145,X_147,X_147,true,true,false); | | X_151 := algebra.leftfetchjoin(X_149,X_141); | | X_152 := algebra.leftfetchjoin(X_151,X_31); | | X_153 := algebra.leftfetchjoin(X_149,r1_141); | | X_154 := algebra.leftfetchjoin(X_153,X_139); | | X_155:bat[:oid,:bit] := batcalc.==(X_152,X_154); | | X_157 := calc.bit(true); | | X_158 := algebra.subselect(X_155,X_157,X_157,true,true,false); | | X_159 := algebra.leftfetchjoin(X_158,X_153); | | X_160:bat[:oid,:dbl] := bat.new(nil:oid,nil:dbl); | | X_163 := sql.bind(X_5,"sys","transactions","bank",0); | | (X_168,r1_168) := sql.bind(X_5,"sys","transactions","bank",2); | | X_171 := sql.bind(X_5,"sys","transactions","bank",1); | | X_173 := sql.delta(X_163,X_168,r1_168,X_171); | | X_174 := algebra.leftfetchjoin(X_36,X_173); | | X_175 := algebra.leftfetchjoin(X_70,X_174); | | X_176 := algebra.leftfetchjoin(X_98,X_175); | | X_177 := bat.append(X_160,X_176,true); | | X_178 := algebra.leftfetchjoin(X_113,X_175); | | X_179 := bat.append(X_177,X_178,true); | | X_180 := algebra.leftfetchjoin(X_159,X_179); | | X_181:bat[:oid,:str] := bat.new(nil:oid,nil:str); | | X_183 := sql.bind(X_5,"sys","transactions","account",0); | | (X_185,r1_185) := sql.bind(X_5,"sys","transactions","account",2); | | X_187 := sql.bind(X_5,"sys","transactions","account",1); | | X_188 := sql.delta(X_183,X_185,r1_185,X_187); | | X_189 := algebra.leftfetchjoin(X_36,X_188); | | X_190 := algebra.leftfetchjoin(X_70,X_189); | | X_191 := algebra.leftfetchjoin(X_98,X_190); | | X_192 := bat.append(X_181,X_191,true); | | X_194 := algebra.leftfetchjoin(X_113,X_190); | | X_195 := bat.append(X_192,X_194,true); | | X_196 := algebra.leftfetchjoin(X_159,X_195); | | X_197:bat[:oid,:str] := bat.new(nil:oid,nil:str); | | X_199 := sql.bind(X_5,"sys","transactions","instrument_ident",0); | | (X_204,r1_204) := sql.bind(X_5,"sys","transactions","instrument_ident",2); | | X_207 := sql.bind(X_5,"sys","transactions","instrument_ident",1); | | X_209 := sql.delta(X_199,X_204,r1_204,X_207); | | X_210 := algebra.leftfetchjoin(X_36,X_209); | | X_211 := algebra.leftfetchjoin(X_70,X_210); | | X_212 := algebra.leftfetchjoin(X_98,X_211); | | X_213 := bat.append(X_197,X_212,true); | | X_214 := algebra.leftfetchjoin(X_113,X_211); | | X_215 := bat.append(X_213,X_214,true); | | X_216 := algebra.leftfetchjoin(X_159,X_215); | | X_217 := algebra.leftfetchjoin(X_159,X_115); | | X_218 := algebra.leftfetchjoin(X_159,X_139); | | (X_219,r1_219,r2_219) := group.subgroup(X_218); | | (X_222,r1_222,r2_222) := group.subgroup(X_217,X_219); | | (X_225,r1_225,r2_225) := group.subgroup(X_216,X_222); | | (X_228,r1_228,r2_228) := group.subgroup(X_196,X_225); | | (X_231,r1_231,r2_231) := group.subgroupdone(X_180,X_228); | | X_234 := algebra.leftfetchjoin(r1_231,X_217); | | X_235 := algebra.selectNotNil(X_234); | | X_236 := aggr.count(X_235); | | sql.exportValue(1,".L2","s","wrd",64,0,6,X_236,""); | | end s4_3; | | # querylog.define("explain \nselect count( client_code_new ) as s from \n(\n select t.client_code_new, t.bank, t.branch_new, t.account, t. | : instrument_ident, max (t.system_code)\n from transactions t left outer join \n ( select c.linked_open_transaction_new as linked_open_transa : : ction_new from calculated_taxs c group by c.linked_open_transaction_new) c_temp\n on t.transaction_no = c_temp.linked_open_transaction_new \njoin cli : : ents_list cl on cl.client_code = t.client_code_new and cl.branch = t.branch_new\n where t.opg_cls = \\'O\\' and (t.close_trans_disregard_balance_new) = : : 0 and new_system_code = 1 \n group by t.client_code_new, t.bank, t.branch_new, t.account, t.instrument_ident) a : : ;","minimal_pipe") : +------------------------------------------------------------------------------------------------------------------------------------------------------------+

2 1

monetdb is slow to backup
by Pierre-Adrien Coustillas 29 Jul '14

29 Jul '14

Hello, I'm used to save monetdb using msqldump : msqldump --database=base --table=table > backup.sql This command takes 30 minutes to backup 6 GB , but 4 minutes to restore. How can I increase backup speed ? 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

3 5

GDKerror when using COPY INTO sql commands
by Mahmut Uludag 25 Jul '14

25 Jul '14

Hi, We update our MonetDB databases using COPY INTO commands, we recently have started to get errors similar to the following error. GDKerror:!ERROR: GDKload: cannot read: name=13/52/135274, ext=theap, 56932 bytes missing. !OS: No such file or directory GDKload: cannot read: name=13/56/135610, ext=theap, 906 bytes missing. !OS: No such file or directory Deleting and recreating the tables and the schema haven't helped. If we create a new table with a table name not used in the past then we are able to use COPY INTO commands to update the new table. It is also possible to update the broken tables using 'INSERT INTO' commands. We see the problem only when we use COPY INTO commands. We use MonetDB Jan2014-SP1 release. We have 3 database nodes that 2 of them doesn't show this error. Any help much appreciated. We also don't know yet why we have started getting this error, any notes on how one can start getting this error would also be helpful. Mahmut Uludag

2 3

Developing a C-based UDF
by Dennis Pallett 18 Jul '14

18 Jul '14

Hi all, I'm developing a C-based user defined function at the moment (actually porting an existing Postgres extension) and I was wondering what kinds of tools I should use for this and how I should actually do the development. At the moment I'm using a simple text editor to edit the relevant files (i.e. the c and mal files) and then completely re-compile MonetDB to test my changes. I'm not entirely happy about this workflow though, so is there an easier way? Perhaps with Visual Studio (I'm on Windows7) ? How do the core developers work on MonetDB? Also, if I run into any problems or have any questions, is this the right mailing list or should I use the developers mailing list? Best regards, Dennis

3 3

python3-monetdb cursor.scroll questions
by Chris Richards 17 Jul '14

17 Jul '14

Howdy. I've recently started using monetdb and was testing out the python3-monetdb module with the 'quick start' voc dataset. I'm not grokking the purpose of cursor.scroll() if it's working as intended. I am getting weird (to me) results, which I'm including below. Running 11.17.17-20140514 of the server, client, and python module under Ubuntu 14.04 LTS. My example starts at this point in the quick start guide (but in python): sql>SELECT type, COUNT(*) AS total I loaded this snippet into the python3 interpreter: from monetdb import sql def p(c): print('.rowcnt={}, .__offset={}, .rownumber={}'.format ( c.rowcount, c._Cursor__offset, c.rownumber)) print ('.__rows={}'.format (c._Cursor__rows)) db = sql.connect(database='voc', user='voc', password='voc') cursor = db.cursor() # Set the arraysize small so it traverses result sets, # though it doesn't matter for the problem I'm seeing. cursor.arraysize = 2 cursor.execute("SELECT type, COUNT(*) AS total FROM onboard_people GROUP BY type ORDER BY type;") Then proceeded with this interactive session. Consecutive fetchone()'s seem to work properly; it traverses to the second result set. Where I get confused is when I .scroll(): >>> p(cursor) .rowcnt=6, .__offset=0, .rownumber=0 .__rows=[('craftsmen', 2349), ('impotenten', 938)] >>> cursor.fetchone() ('craftsmen', 2349) >>> p(cursor) .rowcnt=6, .__offset=0, .rownumber=1 .__rows=[('craftsmen', 2349), ('impotenten', 938)] >>> cursor.fetchone() ('impotenten', 938) >>> p(cursor) .rowcnt=6, .__offset=0, .rownumber=2 .__rows=[('craftsmen', 2349), ('impotenten', 938)] >>> cursor.fetchone() ('passengers', 2813) >>> p(cursor) .rowcnt=6, .__offset=2, .rownumber=3 .__rows=[('passengers', 2813), ('seafarers', 4468)] # Here's where I scroll back to the beginning. The # offset changes, but the rownumber doesn't. >>> cursor.scroll (0, mode='absolute') >>> p(cursor) .rowcnt=6, .__offset=0, .rownumber=3 .__rows=[('craftsmen', 2349), ('impotenten', 938), ('passengers', 2813), ('seafarers', 4468), ('soldiers', 4177)] # My expectation was that this would be ('craftsmen', 2349), # but it continues as if I didn't scroll. >>> cursor.fetchone() ('seafarers', 4468) # I try to back to the last item with no luck, it instead # marches forward to the next item as if I didn't scroll. >>> cursor.scroll (-1, mode='relative') >>> p(cursor) .rowcnt=6, .__offset=3, .rownumber=4 .__rows=[('seafarers', 4468), ('soldiers', 4177), ('total', 2467)] >>> cursor.fetchone() ('soldiers', 4177) Am I using the .scroll() wrong? Is this a bug? Thanks, Chris

1 0

Large query taking a long time
by Dennis Pallett 16 Jul '14

16 Jul '14

Hi all, I'm executing a very large query with MonetDB and it takes very long before the result is calculated (e.g. several minutes) whereas PostgreSQL does it in less than 100 ms. The query itself is a very simple select + sum query but the WHERE condition has more than a thousand equality tests on the primary key (ckey) of the table. It basically looks like this: SELECT sum(countAggr) FROM sys.london_hav_neogeo_pa WHERE ckey=16947611436 OR ckey=16947611437 OR ckey=16947611438 OR ckey=16947611439 OR ckey=16947611440 OR ckey=16947617908 OR ckey=16947617909 OR ckey=16947617910 OR ckey=16947617911 OR ckey=16947617912 [.. several hundreds of lines removed ..] OR ckey=289411309830 OR ckey=289411309831 OR ckey=289411309832 OR ckey=289411309833 OR ckey=289411309834 OR ckey=289411309835 OR ckey=289411309836 OR ckey=289411309837; Does anyone know why it's taking so long with MonetDB and if there is a way to significantly improve performance? Best regards, Dennis

4 7

TMP space deallocation
by Radovan Bičiště 08 Jul '14

08 Jul '14

Hi, My database started consuming disk space. Here is how: 1. run procedure. 2. procedure fails with server crash due to insufficient disk space. It eats up all the space and crash. 3. restart the database but the disk space remain allocated. All changes by the procedure are rolled back. There are inserts but not that many to eat up 10 GB of space. When I check sys.storage, it does not show any large table. My guess is that it allocates temporary space and it does not deallocate it after database start. It deallocates a bit but not everything. Is there any way how to shrink it manually? It already ate around 40 GB space. The whole database is around 130 GB so the quick dump and restore is not possible. Thank you for any tips, Radovan Biciste

1 0

MonetDB and cpu resources management
by Dimitris Karampinas 03 Jul '14

03 Jul '14

Hi, I'm trying to set the maximum number of cores using gdk_nr_threads parameter but it doesn't seem to work for me. I've tried "variants" of this command: monetdbd set gdk_nr_threads=4 start /my/dbfarm but it seems like gdk_nr_threads is not a supported property. Additionally to this, I would like to ask how does MonetDB handle concurrent user sessions and the available cpu resources ? The manual says there is support for "inter- and intra- parallelism". Can someone roughly explain me what to expect or give me a pointer to the code that is responsible for resource management ? Cheers! Dimitris

6 12

Index usage
by Danila B. Savchuk 27 Jun '14

27 Jun '14

I started using Monetdb two weeks ago. Since this is not RDBMS , what is the general strategy of using and building indexes on tables ?

2 1

Problem at start dbfarm
by Dimitris Karampinas 18 Jun '14

18 Jun '14

Hi, I've built from source MonetDB and I'm following the steps at the tutorial: https://www.monetdb.org/Documentation/UserGuide/Tutorial I've successfully created the dbfarm but I can't proceed beyond the second step. Monetdb returns after ~30 seconds with the following message: $ dbms/bin/monetdbd start data/ another monetdbd is already running I 'm sure no other monetdb instance is running in the system. For the record, get all looks as follows: $ dbms/bin/monetdbd get all data/ property value hostname sgs-r815-02 dbfarm data status a monetdbd is serving this dbfarm, but a pidfile was not found/is corrupt mserver unknown (failed to connect to monetdbd) logfile data/merovingian.log pidfile data/merovingian.pid sockdir /tmp port 50000 exittimeout 60 forward proxy discovery true discoveryttl 600 control no passphrase <unknown> mapisock /tmp/.s.monetdb.50000 controlsock /tmp/.s.merovingian.50000 I had no problems with the whole process when I installed the system on my desktop a few days ago. Any hints ? Dimitris

4 6

Unresponsive mserver
by Pete Hollobon 18 Jun '14

18 Jun '14

Hi, We have recently seen MonetDB become unresponsive several times. Upon restarting, after mserver has been sent the TERM signal, merovingian.log contains lots of "unknown state: 4" entries, repeated many times without linefeeds. We're running Jan2014-SP2 (official binaries) on Ubuntu server 14.04. We had the same database running on Jan2014-SP1 on Ubuntu 12.10 for several months without this issue. I have submitted as a bug ( https://www.monetdb.org/bugzilla/show_bug.cgi?id=3499) Thanks, Pete.

1 0

odbc driver compliance: NULL strings or "" in SQLTables and SQLColumns API calls
by László Sándor 18 Jun '14

18 Jun '14

Stata fixed how they will talk to the MonetDB driver in the future, but they say the MonetDB driver is less standard, so perhaps we should be aware of this: We were passing a NULL string to the ODBC > api call SQLTables and SQLColumns where the MonetDB driver wants an empty > string > (""). Some other drivers need a NULL string passed in to work (Oracle > being > one) so I believe this is a driver bug, but I have patched the problem in > Stata. > I have added code in -odbc- to check for the MonetDB driver and pass an > empty > string in that case.

2 1

How do I monitor or speed up insert from aggregation query?
by Joe Wass 17 Jun '14

17 Jun '14

I'm running the following query over a database of 1,000,000,000 items. insert into date_aggregate (count, a, date) select count(date) as count, a, date, from input group by date, a order by date; I expect it to take a certain length of time, but I have no way of knowing how long. The system queue table has a 'null' estimate. All I know from that table is that the query is running. Is there a way to get any more information about its progress? I would be happy to just wait, but I don't know if I should be waiting for hours or days or weeks. The monetdbd process is only using 14% CPU (on a 6 core machine) and 14% RAM (the machine has 7GB and lots of SSD swap). My machine is basically idling when I want it to run as fast as possible. Running Jan2014-SP2, 64-bit on Ubuntu 14.04. Any hints about measuring progress and improving resource utilisation? Joe

2 1

MonetDB just reads and reads when restoring a dump
by Bryan Senseman 16 Jun '14

16 Jun '14

All, I know I have asked this before, but I am still plagued by this error. I am restoring a 50GB database from Linux to Windows, the dump file created via mclient is ~53GB. I am reading that file from an SSD and loading the database onto another SSD. This has been running for 2 DAYS! Using almost all available memory 16GB. Using Windows Resoure Monitor, the mserver5.exe process is just reading tail files for hours. The pattern is that the disk usage will grow by some amount say 5gb (I’m assuming the size of the table actually being loaded) and then it will spend 6-12 hours reading what it just wrote. Why? Can this be eliminated? I removed all indexes on the fact tables so it isn’t indexing (although that runs almost instantly anyway). Only about 10% cpu utilization. Thanx, Bryan -- ------------------------------ This transmission is confidential and intended solely for the use of the recipient named above. It may contain confidential, proprietary, or legally privileged information. If you are not the intended recipient, you are hereby notified that any unauthorized review, use, disclosure or distribution is strictly prohibited. If you have received this transmission in error, please contact the sender by reply e-mail and delete the original transmission and all copies from your system.

2 1

"LIMIT + ORDER BY" TypeException
by George Ramonov 15 Jun '14

15 Jun '14

Hi, I am still learning about monetdb internals, so I hope someone can enlighten me here. I was playing around with monetdb, and run into the following problem: Suppose we have a table CREATE TABLE test.simple_table("id" INTEGER); INSERT INTO test.simple_table VALUES (1),(2),(3),(4),(5); Let's say I want to run a simple query: SELECT COUNT(*) AS val FROM test.simple_table ORDER BY val DESC; +----+ | v | +==+ | 5 | +----+ Runs fine. However, if add a *LIMIT* clause: SELECT COUNT(*) AS val FROM test.simple_table ORDER BY val DESC LIMIT 5; I get the following TypeException:user.s37_7[17]:'algebra.markT' undefined in: _30:any := algebra.markT(_27:oid, _29:oid) program contains errors Now things let's add a primitive *HAVING* clause SELECT COUNT(*) AS val FROM test.simple_table HAVING 1 > 0 ORDER BY val DESC LIMIT 5; +----+ | v | +==+ | 5 | +----+ And things are magically fine again. Can someone explain what's going on? Thanks, George Ramonov UC Berkeley B.S. Electrical Engineering & Computer Science '13 <http://www.linkedin.com/in/georgeramonov>

2 2

odbc driver 11.17.17 (64-bit) memory allocation error on Windows Server 2012
by László Sándor 10 Jun '14

10 Jun '14

Upon some further reflection, the Stata error message still suggest the error happened within MonetDB's ODBC driver. Anybody else using MonetDB with ODBC on Windows? What shall I check? Thanks! Laszlo

1 2

Re: Getting "BATproject: does not match always"
by Joe Wass 10 Jun '14

10 Jun '14

Update to my previous message. Looks like something went wrong with an mmap. 2014-06-09 17:10:58 ERR my_database[32949]: = gdk_posix.c:560: MT_mremap(bat/11/1150.theap,c0000000,538312704,1076625408): p == MAP_FAILED Looks like the server ran out of memory but just kept going with a corrupted table? Should this have been allowed to happen or should the process have crashed? It looks like this bug is related, although it has a different cause: https://www.monetdb.org/bugzilla/show_bug.cgi?id=3450 Joe

1 0

Getting "BATproject: does not match always"
by Joe Wass 10 Jun '14

10 Jun '14

My schema is: create table my_table (col_a date, col_b varchar(1024), col_c varchar(128), col_d varchar(128), col_e varchar(128)); I have approx 5,000,000 entries. The following queries not work: select count(col_b) from my_table; select distinct(col_b) from my_table; select 1 from my_table group by col_b; Giving the error: GDK reported error. BATproject: does not match always They work fine on all other fields. I don't know precisely what triggers this. I've tried to create some small sample values but I can only reproduce this on my large data set. I'm actually trying to do more interesting aggregation, but this is a minimal way to trigger the error. I'm using Jan2014-SP2 on Mac OS X 10.9.2 . Thanks Joe

1 0

Re: MonetDB problem with mserver.exe
by Hannes Mühleisen 10 Jun '14

10 Jun '14

On 06/10/2014 09:04 AM, Kaspar Burger wrote: > You are right, Anthony, when I run the file independent of R, > mserver.exe shuts down immediately. Could you please share your test.bat file? > > *De :*users-list > [mailto:users-list-bounces+kaspar.burger=iukb.ch@monetdb.org] *De la > part de* Anthony Damico > *Envoyé :* mardi 10 juin 2014 08:51 > *À :* Communication channel for MonetDB users > *Objet :* Re: MonetDB problem with mserver.exe > > kaspar has something changed? when you and i screenshared to try to > resolve this issue, mserver.exe was shutting down immediately before R > ever came into the picture? > > what happens when you run this file independent of R? > > > C:\Users\Kaspar Burger\MonetDB\test.bat > > > > On Tue, Jun 10, 2014 at 2:42 AM, Hannes Mühleisen > <Hannes.Muehleisen(a)cwi.nl <mailto:Hannes.Muehleisen@cwi.nl>> wrote: > > This seems to fall into my backyard... > > > On 10.06.2014, at 08:32, Kaspar Burger <kaspar.burger(a)iukb.ch > <mailto:kaspar.burger@iukb.ch>> wrote: > > I am working in R, configuring a MonetDB test database and trying to > > access MonetDB with the following code: > > > > batfile <- "C:/Users/Kaspar Burger/MonetDB/test.bat" > > pid <- monetdb.server.start( batfile ) > > dbname <- "test" > > dbport <- 50000 > > monet.url <- paste0( "monetdb://localhost:" , dbport , "/" , dbname ) > > db <- dbConnect( MonetDB.R() , monet.url , wait = TRUE ) > > > > When I run the “pid <- monetdb.server.start( batfile )” line, the > > mserver5.exe opens briefly but shuts down immediately afterwards (for a > > more extensive description of the R code, see: > > > https://raw.githubusercontent.com/ajdamico/usgsd/master/MonetDB/monetdb%20d…) > > By contrast, running the batch file from C:\Program > > Files\MonetDB\MonetDB5\M5server.bat or from the Start menu works fine. I > > tried when both drivers were installed as well as when only one > driver was > > installed. Could that problem be related to R or to my code? > > Would not be surprised if the space in the batfile path causes the > problem. Try setting up the DB into another directory without a space in > it and try again. > > Best, > > Hannes > > > > > > > > >> > >> > >> On 2014-06-10 00:06, Stefan Manegold wrote: > >>>> On 09/06/14 16:28, Kaspar Burger wrote: > >>>>> > >>>>> Hello, > >>>>> > >>>>> I seem to have difficulties with mserver.exe. I would like to use > >>>>> MonetDB > >>>>> on a Windows 64 bit computer, so... > >>>>> > >>>>> 1) ...I went to > >>>>> https://www.monetdb.org/downloads/Windows/Jan2014-SP2/ > >>>>> > >>>> Most likely, you need only 1 ODBC driver. > >>>> Otherwise causing a clash. > >>> > >>> For the records, as by our own documentation, on a 64-bit Windows > >>> system, > >>> both the 32-bit and the 64-bit ODBC driver *must* be installed. > >>> (As far as I understand, this is required to support both 32-bit and > >>> 64-bit > >>> applications, or simply because Windows requires it.) > >> > >> You need both in order to support both 32 and 64 bit applications. > >> That's the only reason to install both. > >> > >>> Stefan > >>> > >>>>> 2) ...and first installed the MonetDB ODBC driver, 32 bit (to > >>>>> C:\Program > >>>>> Files (x86)\MonetDB\MonetDB ODBC Driver\) > >>>>> > >>>>> 3) ...then I installed the MonetDB ODBC driver, 64 bit (to C:\Program > >>>>> Files\MonetDB\MonetDB ODBC Driver\) > >>>>> > >>>>> 4) ...and finally I installed the MonetDB SQL server and client, 64 > >>>>> bit > >>>>> (to C:\Program Files\MonetDB\MonetDB5\). > >>>>> > >>>>> When I open up mserver.exe, it shuts down immediately. Does anyone > >>>>> know > >>>>> what could have gone wrong? > >> > >> Did you start the server using the start menu? That actually runs a cmd > >> script to set a bunch of environment variables before starting the > >> actual server mserver5.exe. > >> > >>>>> I would appreciate any hint. Thanks in advance. > >>>>> > >>>>> Kaspar Burger > >>>>> > >>>>> > >>>>> _______________________________________________ > >>>>> users-list mailing list > >>>>> users-list(a)monetdb.org <mailto:users-list@monetdb.org> > >>>>> https://www.monetdb.org/mailman/listinfo/users-list > >>>>> > >>>> > >>>> _______________________________________________ > >>>> users-list mailing list > >>>> users-list(a)monetdb.org <mailto:users-list@monetdb.org> > >>>> https://www.monetdb.org/mailman/listinfo/users-list > >>>> > >>> > >> > >> -- > >> Sjoerd Mullender > >> > >> _______________________________________________ > >> users-list mailing list > >> users-list(a)monetdb.org <mailto:users-list@monetdb.org> > >> https://www.monetdb.org/mailman/listinfo/users-list > >> > > > > > > _______________________________________________ > > users-list mailing list > > users-list(a)monetdb.org <mailto:users-list@monetdb.org> > > https://www.monetdb.org/mailman/listinfo/users-list > > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org <mailto:users-list@monetdb.org> > https://www.monetdb.org/mailman/listinfo/users-list > > > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list >

3 5

MonetDB problem with mserver.exe
by Kaspar Burger 10 Jun '14

10 Jun '14

Hello, I seem to have difficulties with mserver.exe. I would like to use MonetDB on a Windows 64 bit computer, so... 1) ...I went to https://www.monetdb.org/downloads/Windows/Jan2014-SP2/ 2) ...and first installed the MonetDB ODBC driver, 32 bit (to C:\Program Files (x86)\MonetDB\MonetDB ODBC Driver\) 3) ...then I installed the MonetDB ODBC driver, 64 bit (to C:\Program Files\MonetDB\MonetDB ODBC Driver\) 4) ...and finally I installed the MonetDB SQL server and client, 64 bit (to C:\Program Files\MonetDB\MonetDB5\). When I open up mserver.exe, it shuts down immediately. Does anyone know what could have gone wrong? I would appreciate any hint. Thanks in advance. Kaspar Burger

7 8

Re: Could monetdb have a capacity to surpport our case
by Hannes Mühleisen 09 Jun '14

09 Jun '14

Hello, On 06.06.2014, at 12:06, liuguodong <liuguodong(a)jd.com> wrote: > Our case as following: > > 1. There are several big tables(more than billion rows per table, more than 1 TB) ; > 2. Many dimentions in every table( more than 150 dimentions per table), they random do join operation; > 3. Low latency reponse, it is expected to reponse time being less than 1 second. > > Could monetdb have a capacity to surpport our case or any suggestion? This depends first on your queries and second on your hardware. In general, the columnar architecture of MonetDB would be a good match for what you describe (lots of dimensions, random join combinations). I would suggest to simply try your queries with a subset of data on a scaled-down machine (e.g. 100 GB of your data on a 16GB RAM box) and see how it goes. Best, Hannes

1 0

Re: Re: Re: tomograph does not work
by Cheng Xuntao 07 Jun '14

07 Jun '14

Thanks Martin. Xuntao

1 0

MAL scheduling and stethoscope metrics
by Cheng Xuntao 07 Jun '14

07 Jun '14

Hi all, I am new to monetdb. I want to know how Monetdb schedule its MAL statements to threads, or to which CPU socket in a multi-cpu environment. I didn't find any document talking bout this directly. Shall I start from reading the source code? Which part of the source code shall I start with? Any suggestion will do me a great favour! I also have some confusions about the metrics of stethoscope. So I asked these 2 question in stackoverflow. http://stackoverflow.com/questions/24077439/what-does-the-metrics-c-m-r-w-a… http://stackoverflow.com/questions/24076688/how-does-monetdb-schedule-its-m… Any suggestion? Thanks very much! Xuntao

1 0

odbc (64-bit) problems in Windows?
by László Sándor 06 Jun '14

06 Jun '14

Hi, I reinstalled MonetDB as well as the ODBC driver after last week's update to SP2. How could I troubleshoot my system if I get a Memory allocation error SQLSTATE=HY001 if Stata would query the database? Otherwise I do see the data source listed, both in Stata (with -odbc query-) and Microsoft ODBC Data Source Administrator. (The latter lists the database under System DSN with the right name, platform and driver.) Thanks, Laszlo

1 1

Text type vs JDBC driver
by Joe Wass 06 Jun '14

06 Jun '14

I just started looking at MonetDB today, so please excuse me if I've missed something. I'm using the JDBC driver in Clojure. I have a table which has one column of type TEXT. The docs mention TEXT [0] and I have no problem with it from the database CLI. When I'm inserting with JDBC, I get an error message : "Conversion not allowed", pointing to MonetPreparedStatement.java line 1775. Looking at the switch statement it doesn't look like the driver has a mapping of String to TEXT (and possibly others). I'm aware that using Clojure might introduce issues, but I've checked and the type of objects I'm passing in are definitely java.lang.Strings. I don't want to raise a ticket because I'm not confident that is is a bug. But it does look like a mismatch between the MonetDB types and the JDBC driver. A quick look at the bug tracker doesn't mention anything about 'text'. (FWIW I've got my code working by using VARCHAR.) Joe [0] https://www.monetdb.org/Documentation/Manuals/SQLreference/BuiltinTypes [1] http://dev.monetdb.org/hg/MonetDB/file/tip/java/src/nl/cwi/monetdb/jdbc/Mon…

1 0