- users-list - monetdb.org

How to update one table from another table or from aggregation in subqueries?
by Ning Yan 04 Mar '14

04 Mar '14

Hi, Firstly, I'm using MonetDB branch from MonetDB Database Server Toolkit v1.1 (Feb2013-SP1) I have two tables, and want to complete the following two functions without creating a temporary table: 1) update one table from another table; and 2) update one table from aggregation of another table. I figured out how to do them in MySQL and Postgresql but similar queries failed in MonetDB. For 1), Suppose I have the following two tables: drop table t1; create table t1(id int, data int); drop table t2; create table t2(id int, data int); delete from t1; insert into t1 values (0, 0), (1,1), (2,2); select*from t1; delete from t2; insert into t2 values (1,2), (2, 3), (3,4); select*from t2; We need to set t1.data to t1.data+t2.data when their ids match, t1.id=t2.id . In MySQL, we can use: update t1 inner join t2 on t1.id=t2.id set t1.data=t1.data+t2.data; In Postgresql, we can use: with tmp as (select t1.id, t1.data+t2.data as data from t1,t2 where t1.id= t2.id) update t1 set data = tmp.data from tmp where t1.id=tmp.id;select*from t1; Or for both, we can use: update t1 set data=(select t1.data+t2.data from t2 where t2.id=t1.id) where exists (select * from t2 where t1.id=t2.id); But none of them work in MonetDB. This question is similar to question asked before ( https://www.monetdb.org/pipermail/users-list/2011-August/005072.html) but the answered suggested sees to be incorrect. There the two tables having the same value for tuple id=2. update t1 set data=(select data+t2.data from t2 where t2.id=t1.id) where exists (select * from t2 where t1.id=t2.id); Thus in this query, the data field in 1st subquery of select actually comes from t2 not t1. However, if we changed to t1.data, the parser will not recognize name t1. It is somehow wired that it can recognize t1.id in condition clause. Not that the following query is also not correct: update t1 set data=(select t1.data + t2.data from t1, t2 where t2.id = t1.id) where exists (select t2.id from t2 where t2.id = t1.id); As the subquery becomes independent from external query, and might result in multiple values which violates cardinality of assignment. Also changing field names in t1 and t2 will not be helpful either. For 2), Suppose we have the following two tables: delete from t1; insert into t1 values (0, 0), (1,1), (2,2); select * from t1; delete from t2; insert into t2 values (1,2), (2, 3), (3,4), (2,5); select * from t2; We want to aggregate values in t2 and assign results to t1. In MySQL, we can use: update t1 inner join t2 on t1.id=t2.id set t1.data=(select sum(t2.data) from t2 where t2.id=t1.id group by t2.id); Or in Postgresql, we can use: with tmp as (select t2.id, sum(t2.data) as data from t2 group by t2.id) update t1 set data=tmp.data from tmp where t1.id=tmp.id; In MonetDB, how to correctly write such subquery in update statement? It looks like that MonetDB supports with clause in select, but not in update. with tmp as (select t2.id, sum(t2.data) as data from t2 group by t2.id) select * from tmp; I'm also interested to know the performance difference between using subqueries and creating temporary tables. Thanks! Regards, -- Ning Yan

2 2

Re: what is monetdb doing?
by Luis Neves 25 Feb '14

25 Feb '14

On 02/25/2014 05:56 PM, Mrunal Gawade wrote: > Also try to tune your virtual memory settings by playing around with the following parameters. > This changes the flushing behavior of memory pages. > > Do not use the parameters below directly as it is. > > Look at what are your existing parameters and tune to delay flushing. The parameters below, will > delay flushing for an extreme time, which might not be good in your case as file system might get > out of sync. But as a test you can try these parameters to see if they solve the problem, and then > use correct parameters after playing around with them. > > sysctl -w vm.swappiness=0 > sysctl -w vm.dirty_expire_centisecs=6000000 > sysctl -w vm.dirty_background_ratio=90 > sysctl -w vm.dirty_writeback_centisecs=6000000 > sysctl -w vm.dirty_ratio=90 The current value for these parameters are: vm.swappiness = 60 vm.dirty_expire_centisecs = 3000 vm.dirty_background_ratio = 10 vm.dirty_writeback_centisecs = 500 vm.dirty_ratio = 20 I will read more about the effects of these settings and try to tune the system. Also... about the hugepages, these are Debian 6.0.4 servers and the only hugepages parameters I can find are these: /proc/sys/vm/nr_hugepages /proc/sys/vm/hugetlb_shm_group /proc/sys/vm/hugepages_treat_as_movable /proc/sys/vm/nr_overcommit_hugepages and all of them are set to "0". Thanks for your help. -- /** * Luis Neves * @e-mail: luis.neves(a)co.sapo.pt * @xmpp: lfs_neves(a)sapo.pt * @web: <http://about.me/luisneves> * @tlm: +351 962 057 656 * -- * As opiniões expressas por mim não são * necessariamente opiniões em que eu acredito * -- */

1 0

what is monetdb doing?
by Luis Neves 25 Feb '14

25 Feb '14

Hi all, I've run into a behaviour that I would like to understand and possibly disable. Two times per day the mserver daeomon starts trashing the disk furiously, I see that the size on disk of data directory goes up to 115 GB from 85 GB and then comes back again to the same initial size, during this process disk utilization goes trough the roof. Can anyone tell me what is monetdb doing? There are no import/export process running at this times, it seems that monetdb is doing some kind of data compaction... is there anyway to disable this? $ mserver5 --version MonetDB 5 server v11.15.7 "Feb2013-SP2" (64-bit, 64-bit oids) Thanks! -- Luis Neves

2 3

Re: slow performance with 10 joins and above
by Ying Zhang 24 Feb '14

24 Feb '14

Hai Imad, On Feb 20, 2014, at 10:16 , imad hajj chahine <imad.hajj.chahine(a)gmail.com> wrote: > Hi Ying, > > Thank you for your update, i will try to have an instance of monetdb > on linux machine and check the difference. Can you please specify the > optimal machine configuration for deploy (since we are using key value > store model, target number of row per table > 1 billion): CPU,RAM,SSD. This largely depends on the size of your hot data set, the type of queries, and the query load. It's important to have sufficient RAM to hold the how hot dataset. SSDs are always nice to have, but probably doesn't qualify the costs if you can have sufficient RAM. > Also do running monetdb in cluster will help to optimize queries > response or its just for fail-over purpose? does monetdb support > physical table data-partitioning? any reference on how monetdb > operates in cluster mode, like data partitioning, query plans, merging > results,... For this, you probably want to have a look at the possibilities to query "REMOTE DATABASES" and creating "MULTIPLEX-FUNNELS" here:http://www.monetdb.org/Documentation/monetdbd-man-page With kind regards, Jennie > > Thank you > >>>> >>>> On 17/02/14 11:44, imad hajj chahine wrote: >>>> >>>> Hi, >>>> >>>> I have the following query that do a pivot for the data stored in different tables, the time of the query is acceptable as long as the number of table to join is up to 10, 10 to 15 the query take more than 90 seconds to return, above 15 it >>>> does not >>>> return. >>>> Is there something i am missing, or i have to slice my query in smaller joins and the join back the results? >>>> >>>> PS: the max number of records in each table is < 200k >>>> >>>> select tval1.value as countall,tval2.value as ana1,tval3.value as ana2,tval4.value as count5,tval5.value as min5,tval6.value as max5,tval7.value as sum5,tval8.value as avg5,tval9.value as count6,tval10.value as min6,tval11.value as >>>> max6,tval12.value as >>>> sum6,tval13.value as avg6,tval14.value as count7,tval15.value as min7,tval16.value as max7,tval17.value as sum7,tval18.value as avg7 >>>> from "RPME".t_entity_cache tec join >>>> "RPME".t_value_int_cache tval1 on tec.id_schema=tval1.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval1.id_____entity and tval1.id_attribute=1 join >>>> "RPME".t_value_date_cache tval2 on tec.id_schema=tval2.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval2.id_____entity and tval2.id_attribute=2 join >>>> "RPME".t_value_string_cache tval3 on tec.id_schema=tval3.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval3.id_____entity and tval3.id_attribute=3 join >>>> "RPME".t_value_int_cache tval4 on tec.id_schema=tval4.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval4.id_____entity and tval4.id_attribute=4 join >>>> "RPME".t_value_numeric_cache tval5 on tec.id_schema=tval5.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval5.id_____entity and tval5.id_attribute=5 join >>>> "RPME".t_value_numeric_cache tval6 on tec.id_schema=tval6.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval6.id_____entity and tval6.id_attribute=6 join >>>> "RPME".t_value_numeric_cache tval7 on tec.id_schema=tval7.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval7.id_____entity and tval7.id_attribute=7 join >>>> "RPME".t_value_numeric_cache tval8 on tec.id_schema=tval8.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval8.id_____entity and tval8.id_attribute=8 join >>>> "RPME".t_value_int_cache tval9 on tec.id_schema=tval9.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval9.id_____entity and tval9.id_attribute=9 join >>>> "RPME".t_value_numeric_cache tval10 on tec.id_schema=tval10.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval10.id_____entity and tval10.id_attribute=10 join >>>> "RPME".t_value_numeric_cache tval11 on tec.id_schema=tval11.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval11.id_____entity and tval11.id_attribute=11 join >>>> "RPME".t_value_numeric_cache tval12 on tec.id_schema=tval12.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval12.id_____entity and tval12.id_attribute=12 join >>>> "RPME".t_value_numeric_cache tval13 on tec.id_schema=tval13.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval13.id_____entity and tval13.id_attribute=13 join >>>> "RPME".t_value_int_cache tval14 on tec.id_schema=tval14.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval14.id_____entity and tval14.id_attribute=14 join >>>> "RPME".t_value_numeric_cache tval15 on tec.id_schema=tval15.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval15.id_____entity and tval15.id_attribute=15 join >>>> "RPME".t_value_numeric_cache tval16 on tec.id_schema=tval16.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval16.id_____entity and tval16.id_attribute=16 join >>>> "RPME".t_value_numeric_cache tval17 on tec.id_schema=tval17.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval17.id_____entity and tval17.id_attribute=17 join >>>> "RPME".t_value_numeric_cache tval18 on tec.id_schema=tval18.id_schema and tec.id <http://tec.id> <http://tec.id> <http://tec.id>=tval18.id_____entity and tval18.id_attribute=18 >>>> where tec.id_schema=3 >>>> >>>> >>>> ___________________________________________________ >>>> users-list mailing list >>>> users-list(a)monetdb.org <mailto:users-list@monetdb.org> <mailto:users-list@monetdb.org <mailto:users-list@monetdb.org>__> >>>> https://www.monetdb.org/____mailman/listinfo/users-list <https://www.monetdb.org/__mailman/listinfo/users-list> <https://www.monetdb.org/__mailman/listinfo/users-list <https://www.monetdb.org/mailman/listinfo/users-list>> >>>> >>>> >>>> >>>> >>>> >>> >> >>

1 0

Load Bulk Data With COPY INTO
by Summer Xia 21 Feb '14

21 Feb '14

Hi, I followed the description in load bulk data to load a 200MB csv file into monetdb. It worked on my desktop (64-bit windows 7 system, 32GB memory), but not on my laptop (64-bit windows 8 system, 2GB memory). I also tried with a windows 7 8GB memory computer, it didn't work either. I followed the same procedure on all computers. There is no other error except "failed to import data". I'm wondering what's the cause of loading error. BTW, data fields in this data include timestamp, char, text, char... Thanks ahead. Best regards, Summer

1 0

Indices on monetdb
by kanumula raviteja 21 Feb '14

21 Feb '14

Hello, I’m doing a comparision on a couple of databases including monetdb . But to maintain uniformity , I wanted to either turn off the indexing feature in monetdb (or) to know what all indices are being maintained. Thanks in advance .

1 0

Announcement: New Jan2014 Feature release of MonetDB suite
by Sjoerd Mullender 20 Feb '14

20 Feb '14

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jan2014 feature release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <http://www.monetdb.org/>. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>. Jan 2014 feature release Build Environment * Created development packages for RPM based systems and Debian/Ubuntu containing the files needed to create extensions to the SQL front end. * Removed Mx, the literate programming tool. All code for the server is now pure C. * Created packages for RPM based systems and Debian/Ubunty containing the MonetDB interface to the GNU Scientific Library (gsl). * We no longer install the .la files in our Fedora/Debian/Ubuntu packages. Client Package * ODBC: Implemented {fn scalar()} and {interval ...} escapes. python2 * Changed defaults for connecting (defaults to unix socket now). * Unix sockets partially working for control protocol. * Add support for unix socket. python3 * Changed defaults for connecting (defaults to unix socket now). * Unix sockets partially working for control protocol. * Add support for unix socket. R * The R connector is now distributed in the source code packages. MonetDB Common * The join code has been completely rewritten. It now optionally uses candidate lists, like the select code that was introduced in the previous release. * A new indexing structure for range selections on unsorted data has been added. * The vmtrim thread is no longer started by default on 64 bit architectures. The vmtrim thread monitors memory usage and drops BATs from memory when memory gets tight. However, in the age of large address spaces and virtual memory, the kernel does a good enough job. And in addition to dropping BATs, the thread also destroyed indexing structures which would have to be recreated the next time they were needed. * Cleaned up some of the parameters dealing with memory usage. * If available on the system, we now use atomic instructions for latching. * Removed some unused fields in the atomDesc structure. This change requires a complete recompilation of the whole suite. * Replaced the mutex implementation for both GNU C and Visual Studio with a home-grown implementation that uses atomic instructions (__sync_*() in gcc, _Interlocked*() in VS). SQL * Added support for quantiles (generalization of median). Usage: SELECT quantile(column_name,0.25) FROM table_name; The value should be in the range 0..1. Bug Fixes * 3040: Wrong NULL behavior in EXCEPT and INTERSECT * 3092: ODBC client doesn't support scalar function escape * 3198: SIGSEGV insert_string_bat (b=0x7fffe419d0a0, n=0x7fffc4006010, append=0) at gdk_batop.c:196 * 3210: Unexpected concurrency conflict when inserting to 2 tables simultaneously and querying one of them * 3273: Add support to Python DBAPI package for timetz, inet and url types * 3285: no such table 'queryHistory' * 3298: GDKmmap messages and monetdb start db takes very long * 3354: Introduce query time-out * 3371: (i)like generates batloop instead of algebra.likesubselect * 3372: Large group by queries never complete - server at 100% cpu(all cores) until MonetDB stopped * 3383: Bad performance with DISTINCT GROUP BY * 3391: Bad performance with GROUP BY and FK with out aggregate function * 3393: "COPY .. INTO ..." - escape of string quotes * 3399: server crashed on simple (malformed) query * 3401: inconsistent/strange handling of invalid dates (e.g. 2013-02-29) in where clause * 3403: NOT NULL constraint can't be applied after deleting rows with null values * 3404: Assertion `h->storage == STORE_MMAP' failed. * 3408: nested concat query crashed server * 3411: (disguised) BETWEEN clause not recognised. Hence no rangejoin. * 3412: Boolean expressions in WHERE clause, result in incorrect resulsts * 3417: Nested Common Table Expressions Crash * 3418: Segmentation fault on a query from table expression * 3419: Database does not start after upgrade * 3420: Database does not start after upgrade * 3423: Group by alias with distinct count doesn't work * 3425: Temporal extraction glitches * 3427: Consistent use of current_timestamp and now() * 3428: Aggregation over two columns is broken * 3429: SAMPLE on JOIN result crashes server * 3430: Wrong temporary handling * 3431: SQLGetInfo returns incorrect value for SQL_FN_NUM_TRUNCATE * 3432: MonetDB SQL syntax incompatible with SQL-92 <delimited identifier> syntax * 3435: INDEX prevents JOIN from discovering matches * 3436: COPY INTO from file containing leading Byte Order Mark (BOM) causes corruption

1 0

slow performance with 10 joins and above
by imad hajj chahine 20 Feb '14

20 Feb '14

Hi, I have the following query that do a pivot for the data stored in different tables, the time of the query is acceptable as long as the number of table to join is up to 10, 10 to 15 the query take more than 90 seconds to return, above 15 it does not return. Is there something i am missing, or i have to slice my query in smaller joins and the join back the results? PS: the max number of records in each table is < 200k select tval1.value as countall,tval2.value as ana1,tval3.value as ana2,tval4.value as count5,tval5.value as min5,tval6.value as max5,tval7.value as sum5,tval8.value as avg5,tval9.value as count6,tval10.value as min6,tval11.value as max6,tval12.value as sum6,tval13.value as avg6,tval14.value as count7,tval15.value as min7,tval16.value as max7,tval17.value as sum7,tval18.value as avg7 from "RPME".t_entity_cache tec join "RPME".t_value_int_cache tval1 on tec.id_schema=tval1.id_schema and tec.id=tval1.id_entity and tval1.id_attribute=1 join "RPME".t_value_date_cache tval2 on tec.id_schema=tval2.id_schema and tec.id=tval2.id_entity and tval2.id_attribute=2 join "RPME".t_value_string_cache tval3 on tec.id_schema=tval3.id_schema and tec.id=tval3.id_entity and tval3.id_attribute=3 join "RPME".t_value_int_cache tval4 on tec.id_schema=tval4.id_schema and tec.id=tval4.id_entity and tval4.id_attribute=4 join "RPME".t_value_numeric_cache tval5 on tec.id_schema=tval5.id_schema and tec.id=tval5.id_entity and tval5.id_attribute=5 join "RPME".t_value_numeric_cache tval6 on tec.id_schema=tval6.id_schema and tec.id=tval6.id_entity and tval6.id_attribute=6 join "RPME".t_value_numeric_cache tval7 on tec.id_schema=tval7.id_schema and tec.id=tval7.id_entity and tval7.id_attribute=7 join "RPME".t_value_numeric_cache tval8 on tec.id_schema=tval8.id_schema and tec.id=tval8.id_entity and tval8.id_attribute=8 join "RPME".t_value_int_cache tval9 on tec.id_schema=tval9.id_schema and tec.id=tval9.id_entity and tval9.id_attribute=9 join "RPME".t_value_numeric_cache tval10 on tec.id_schema=tval10.id_schema and tec.id=tval10.id_entity and tval10.id_attribute=10 join "RPME".t_value_numeric_cache tval11 on tec.id_schema=tval11.id_schema and tec.id=tval11.id_entity and tval11.id_attribute=11 join "RPME".t_value_numeric_cache tval12 on tec.id_schema=tval12.id_schema and tec.id=tval12.id_entity and tval12.id_attribute=12 join "RPME".t_value_numeric_cache tval13 on tec.id_schema=tval13.id_schema and tec.id=tval13.id_entity and tval13.id_attribute=13 join "RPME".t_value_int_cache tval14 on tec.id_schema=tval14.id_schema and tec.id=tval14.id_entity and tval14.id_attribute=14 join "RPME".t_value_numeric_cache tval15 on tec.id_schema=tval15.id_schema and tec.id=tval15.id_entity and tval15.id_attribute=15 join "RPME".t_value_numeric_cache tval16 on tec.id_schema=tval16.id_schema and tec.id=tval16.id_entity and tval16.id_attribute=16 join "RPME".t_value_numeric_cache tval17 on tec.id_schema=tval17.id_schema and tec.id=tval17.id_entity and tval17.id_attribute=17 join "RPME".t_value_numeric_cache tval18 on tec.id_schema=tval18.id_schema and tec.id=tval18.id_entity and tval18.id_attribute=18 where tec.id_schema=3

3 3

what is the character limit for monetdb queries?
by Anthony Damico 17 Feb '14

17 Feb '14

i'm definitely hitting one around 1.7 million characters, but i'm wondering how much lower the actual limit is? thanks!

3 4

mapi_get_last_id in JDBC
by imad hajj chahine 16 Feb '14

16 Feb '14

Hi, How to get the last inserted id with JDBC, i see its supported in mapi library but i am unable to get it with JDBC interface using st.getGeneratedKeys(). Thanks

2 1

Choices of operating system
by Radovan Bičiště 12 Feb '14

12 Feb '14

Hello, We have monetdb deployed for our customers and due to decent experience we would like to solidify and standardize our enviroment. So far we've been using UBUNTU 12.04. I would like to ask for recommendation over operating system with good mix performance/support period. Internally we use Oracle Linux, but lack of prebuild packages pushed us to consider different distribution. We are looking at these options ordered by our priority: 1. Fedora, I have doubts about long term support. Is this what is used for Monet development? 2. FreeBSD, AFAIK it has different memory management and should scale better. Thank you, Radovan

2 1

database crash
by Radovan Bičiště 11 Feb '14

11 Feb '14

Hello, I hope anyone can help with database crash. In .gdk_lock is following: USR=1001 PID=6751 TIME=Mon Feb 10 16:04:17 2014 @ LOGON USR=1001 PID=6751 TIME=Mon Feb 10 16:04:18 2014 @ !FATAL: logger_new: there is a logger catalog, but no log file. Are you sure you are using the correct combination of database (--dbpath) and log directory (--set sql_logdir)? If you have done a recent update of the server, it may be that your logs are in an old location. You should then either use --set sql_logdir=<path to old log directory> or move the old log directory to the new location (/u01/ceosdata/database/monetdb/drmax/crm/zkdev1/sql_logs/sql/). USR=1001 PID=6751 TIME=Mon Feb 10 16:04:18 2014 @ LOGOFF Anyway to start the database without logs ? Thanks, Radovan

3 6

Announcement: (Hopefully) Final Release Candidate for Jan2014 Release
by Sjoerd Mullender 10 Feb '14

10 Feb '14

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 The MonetDB team at CWI/MonetDB BV is pleased to announce a new release candidate for the upcoming Jan2014 feature release. Many things have changed in this release compared to the previous (Feb2013) release (that's why it took so long). For a smooth upgrade I would urge you to try out this release candidate and to provide feedback on our bug tracker (http://bugs.monetdb.org) A number of bugs in the previous release candidate have been fixed. We hope that this release candidate is good enough to be the new release, but we will give it a few more days. The release candidate can be found at http://dev.monetdb.org/downloads/testing. - -- Sjoerd Mullender -----BEGIN PGP SIGNATURE----- Version: GnuPG v1 Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQCVAwUBUviUtz7g04AjvIQpAQJy5wQArulRMgV7YWX1NoOJMoSSKINjEQLeneZC vj4NqMJ0igxYMRxqBLd7EcSfLKvgDR3ElmP7HhDkXbmCDk6POD4PjxPRmh66AEwY uYBhT3iIEe01PFzqjMhWm2HnDnfDOTzvn8Ai4n1/qCIVmW0p14kIYOkTRXWiNX5o tcGfy384cx8= =2un1 -----END PGP SIGNATURE-----

1 0

Some questions regarding MonetDB
by Alejandro Villamarin 07 Feb '14

07 Feb '14

Hi everyone, I've got some questions regarding MonetDB and scientifc data...more precisely, I'd like to know if MonetDB is suitable for ETL on datasets coming from NOAA in the following formats: GRIB2, NetCDF and HDF5, which all are multdimensional value grid files (to put it simple). I've already installed MonetDB and played with the tutorial, but so far I looks like a traditional RDBMS to me (yet)...any insights in this direction? Should I deep dive into the reference Manual? Another question would be...could I directly insert the data or do I have to do any parsing before? Does MonetDB provide analytical tools to do, for instance, correlation on that type of datasets? Thanks in advance, Alejandro

2 2

Fwd: Error when running Monet as cron jobs
by Nitin Gautam 07 Feb '14

07 Feb '14

Hi I am parsing my logs hourly as part of cron jobs and then uploading the data to Monet. However sometimes I do see some error messages in the logs *unsupported hash algorithms: gorithms: gorit* *monetdbd: internal error while starting mserver, please refer to the logs* Not sure what these error messages are, are they because of a bug or limitation of the Monet DB. Regards Nitin

4 9

mserver5 parameters from monetdbd
by Javier Picorel 06 Feb '14

06 Feb '14

Hello, I want to profile the data that is being read or created (e.g., tables, intermediate results) during the execution of a query. I would like to have more info than the tomograph or stethoscope show. I'm thinking about using the gdk_debug parameter (ALLOCMASK and HEAPMASK). However, I cannot specify the parameters of mserver5 when I run monetdbd. Running mserver5 manually doesn't seem to work. How can I specify the mserver5 parameter when starting the farm through monetdbd? Thanks! Regards, Javier

3 2

MonetDB Next Release Feature Wishlist
by Martin Kersten 04 Feb '14

04 Feb '14

LS, A major MonetDB feature release is pending, called Jan2014. To plan our (always scarce) resources, we would like to learn from the community in what areas the functionality or performance should be further improved. Please drop us an email on info(a)monetdb.org to share your thoughts. And, please, enlighten us on how you use the product, or modify/deploy/expand it to better suite your environment. regards, Martin Kersten

1 0

Re: problem with Jan2014 build
by Ying Zhang 29 Jan '14

29 Jan '14

Hai Bas, We hebben vandaag een probleem met substring gefixed die waarschijnlijk met jou probleem te maken heeft. Zou je misschien willen kijken of die fix iets voor je doet? We kunnen dat helaas zelf niet testen. Mvg, Jennie On Jan 27, 2014, at 15:02, Ying Zhang <ying.zhang(a)monetdb.com> wrote: > Hai Bas, > > Thanks again for the information. I was hoping that with some luck your problems might be gone in RC2 due to various fixes, unfortunately they are not. I'd like to look more deeply into the problems you have reported. Problems like not effective updates are rather show stoppers. > > Would you please provide me the ASC files with which the problems can be reproduced? You can send the files directly to me if necessary. Thanks! > > Regards, > > Jennie > > On Jan 27, 2014, at 13:05, Bas Kaptijn <b.kaptijn(a)chs.nl> wrote: > >> Tested with Jan 2014 RC2. All mentioned problems still occur with this release. >> >> Also, with the no_mitosis_pipe my program has problems with DELETE’s and possibly other queries >> not taking place effectively somehow. For me the investigation of the Jan2014 build stops here >> until some of the problems mentioned here are fixed >> >> Regards, Bas >> >> Van: Bas Kaptijn >> Verzonden: dinsdag 21 januari 2014 12:55 >> Aan: 'users-list(a)monetdb.org' >> Onderwerp: RE: problem with Jan2014 build >> >> Some last info: the problem disappears when using the no_mitosis_pipe optimizer pipeline. >> >> sql>set optimizer='no_mitosis_pipe'; >> operation successful (0.139ms) >> sql>SELECT * FROM source1 WHERE SUBSTRING(SOURCE,1,2)='04' LIMIT 1 OFFSET 400000; >> +-----------------------------------------------------------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------+ >> | file | line | source | >> +=============================================================================+========+====================================================================================================================================================+ >> | /home/bkaptijn/DDDB/.tmpinputdir/9772_9999_20130501_20130531_Schade_516.ASC | 430664 | 04999999999999999999999999999999999999999 >> : >> +-----------------------------------------------------------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------+ >> 1 tuple (1.1s) >> sql>set optimizer='default_pipe'; >> operation successful (0.305ms) >> sql>SELECT * FROM source1 WHERE SUBSTRING(SOURCE,1,2)='04' LIMIT 1 OFFSET 400000; >> +------+------+--------+ >> | file | line | source | >> +======+======+========+ >> +------+------+--------+ >> 0 tuples (798.870ms) >> >> Regards, Bas >> _______________________________________________ >> users-list mailing list >> users-list(a)monetdb.org >> https://www.monetdb.org/mailman/listinfo/users-list >

1 0

Dynamic queries
by imad hajj chahine 28 Jan '14

28 Jan '14

Hi, What is the recommended way to work with dynamic queries, any optimization techniques, is there a way to avoid them using functions written in MAL.

1 0

Bug when using BAT UDFs with grouped aggregates and mitosis (related to 3396)
by Alastair McKinley 27 Jan '14

27 Jan '14

Dear all, I have been trying to integrate a BAT UDF to test the performance of MonetDB for a particular query. I am currently testing on Ubuntu 13.04 with MonetDB 11.15.19. My BAT UDF works in some cases and in others fails with the error message "b and g must be aligned" in gdk_aggr.c:89. Using gdb I can see that the BATcount() function returns different values for the length of b and g. For a minimal test case, I created a very simple BAT UDF that just multiples two doubles together in the input bats. I believe this is related to bug 3396 as passing --forcemito to mserver triggers the bug. If only the scalar version of the UDF is defined the query works correctly in all cases. Below I have included my test sql and the query plans of the working and non-working usages. I hope this enough to help track this down. Best regards, Alastair // SQL create table tab1 (group_by_col int, index_col int, f float); create table tab2 (index_col int, f float); insert into tab1 values (1,1,0.1),(1,2,0.2),(2,1,0.3),(2,2,0.4),(3,1,0.5),(3,2,0.6); insert into tab2 values (1,0.1),(2,0.2); select tab1.group_by_col,SUM(udf_multiply_dbl(tab1.f,tab2.f)) from tab2 inner join tab1 on tab1.index_col = tab2.index_col group by tab1.group_by_col; drop table tab1; drop table tab2; // Working MAL +-------------------------------------------------------------------------+ | mal | +=========================================================================+ | function user.s2_1{autoCommit=true}():void; | | X_36 := nil:bat[:oid,:int]; | | X_57:bat[:oid,:dbl] := nil:bat[:oid,:dbl]; | | barrier X_86 := language.dataflow(); | | X_2 := sql.mvc(); | | X_3:bat[:oid,:oid] := sql.tid(X_2,"sys","tab1"); | | X_6 := sql.bind(X_2,"sys","tab1","index_col",0); | | (X_9,r1_9) := sql.bind(X_2,"sys","tab1","index_col",2); | | X_12 := sql.bind(X_2,"sys","tab1","index_col",1); | | X_14 := sql.delta(X_6,X_9,r1_9,X_12); | | X_15 := algebra.leftfetchjoin(X_3,X_14); | | X_16:bat[:oid,:oid] := sql.tid(X_2,"sys","tab2"); | | X_18 := sql.bind(X_2,"sys","tab2","index_col",0); | | (X_19,r1_19) := sql.bind(X_2,"sys","tab2","index_col",2); | | X_21 := sql.bind(X_2,"sys","tab2","index_col",1); | | X_22 := sql.delta(X_18,X_19,r1_19,X_21); | | X_23 := algebra.leftfetchjoin(X_16,X_22); | | (X_24,r1_24) := algebra.join(X_15,X_23); | | X_85 := algebra.leftfetchjoin(X_24,X_3); | | X_26 := sql.bind(X_2,"sys","tab1","group_by_col",0); | | (X_28,r1_28) := sql.bind(X_2,"sys","tab1","group_by_col",2); | | X_30 := sql.bind(X_2,"sys","tab1","group_by_col",1); | | X_31 := sql.delta(X_26,X_28,r1_28,X_30); | | X_32:bat[:oid,:int] := algebra.leftfetchjoin(X_85,X_31); | | (X_33,r1_34,r2_34) := group.subgroupdone(X_32); | | X_36 := algebra.leftfetchjoin(r1_34,X_32); | | X_37 := sql.bind(X_2,"sys","tab1","f",0); | | (X_42,r1_43) := sql.bind(X_2,"sys","tab1","f",2); | | X_45 := sql.bind(X_2,"sys","tab1","f",1); | | X_47 := sql.delta(X_37,X_42,r1_43,X_45); | | X_48:bat[:oid,:dbl] := algebra.leftfetchjoin(X_85,X_47); | | X_49 := sql.bind(X_2,"sys","tab2","f",0); | | (X_51,r1_53) := sql.bind(X_2,"sys","tab2","f",2); | | X_53 := sql.bind(X_2,"sys","tab2","f",1); | | X_54 := sql.delta(X_49,X_51,r1_53,X_53); | | X_55:bat[:oid,:dbl] := algebra.leftfetchjoinPath(r1_24,X_16,X_54); | | X_56:bat[:oid,:dbl] := batudf.udf_multiply_dbl(X_48,X_55); | | X_57:bat[:oid,:dbl] := aggr.subsum(X_56,X_33,r1_34,true,true); | | language.pass(X_3); | | language.pass(X_32); | | language.pass(X_85); | | language.pass(X_16); | | language.pass(r1_34); | | exit X_86; | | X_59 := sql.resultSet(2,1,X_36); | | sql.rsColumn(X_59,"sys.tab1","group_by_col","int",32,0,X_36); | | sql.rsColumn(X_59,"sys.L1","L1","double",53,0,X_57); | | X_71 := io.stdout(); | | sql.exportResult(X_71,X_59); | | end s2_1; | +-------------------------------------------------------------------------+ // Non working MAL with --forcemito +-------------------------------------------------------------------------------------------------------+ | mal | +=======================================================================================================+ | function user.s2_1{autoCommit=true}():void; | | X_23 := nil:bat[:oid,:int]; | | X_41:bat[:oid,:dbl] := nil:bat[:oid,:dbl]; | | barrier X_196 := language.dataflow(); | | X_2 := sql.mvc(); | | X_70:bat[:oid,:oid] := sql.tid(X_2,"sys","tab1",0,4); | | X_78:bat[:oid,:int] := sql.bind(X_2,"sys","tab1","index_col",0,0,4); | | (X_82:bat[:oid,:oid] ,X_83:bat[:oid,:int] ) := sql.bind(X_2,"sys","tab1","index_col",2,0,4); | | X_119 := sql.delta(X_78,X_82,X_83); | | X_123 := algebra.leftfetchjoin(X_70,X_119); | | X_10:bat[:oid,:oid] := sql.tid(X_2,"sys","tab2"); | | X_12 := sql.bind(X_2,"sys","tab2","index_col",0); | | (X_13,r1_19) := sql.bind(X_2,"sys","tab2","index_col",2); | | X_15 := sql.bind(X_2,"sys","tab2","index_col",1); | | X_16 := sql.delta(X_12,X_13,r1_19,X_15); | | X_17 := algebra.leftfetchjoin(X_10,X_16); | | (X_127,X_128) := algebra.join(X_123,X_17); | | X_180 := algebra.leftfetchjoin(X_127,X_70); | | X_90:bat[:oid,:int] := sql.bind(X_2,"sys","tab1","group_by_col",0,0,4); | | (X_94:bat[:oid,:oid] ,X_95:bat[:oid,:int] ) := sql.bind(X_2,"sys","tab1","group_by_col",2,0,4); | | X_136 := sql.delta(X_90,X_94,X_95); | | X_143:bat[:oid,:int] := algebra.leftfetchjoin(X_180,X_136); | | (X_147,X_148,X_149) := group.subgroupdone(X_143); | | X_150 := algebra.leftfetchjoin(X_148,X_143); | | X_72:bat[:oid,:oid] := sql.tid(X_2,"sys","tab1",1,4); | | X_79:bat[:oid,:int] := sql.bind(X_2,"sys","tab1","index_col",0,1,4); | | (X_84:bat[:oid,:oid] ,X_85:bat[:oid,:int] ) := sql.bind(X_2,"sys","tab1","index_col",2,1,4); | | X_120 := sql.delta(X_79,X_84,X_85); | | X_124 := algebra.leftfetchjoin(X_72,X_120); | | (X_129,X_130) := algebra.join(X_124,X_17); | | X_181 := algebra.leftfetchjoin(X_129,X_72); | | X_91:bat[:oid,:int] := sql.bind(X_2,"sys","tab1","group_by_col",0,1,4); | | (X_96:bat[:oid,:oid] ,X_97:bat[:oid,:int] ) := sql.bind(X_2,"sys","tab1","group_by_col",2,1,4); | | X_138 := sql.delta(X_91,X_96,X_97); | | X_144:bat[:oid,:int] := algebra.leftfetchjoin(X_181,X_138); | | (X_151,X_152,X_153) := group.subgroupdone(X_144); | | X_154 := algebra.leftfetchjoin(X_152,X_144); | | X_74:bat[:oid,:oid] := sql.tid(X_2,"sys","tab1",2,4); | | X_80:bat[:oid,:int] := sql.bind(X_2,"sys","tab1","index_col",0,2,4); | | (X_86:bat[:oid,:oid] ,X_87:bat[:oid,:int] ) := sql.bind(X_2,"sys","tab1","index_col",2,2,4); | | X_121 := sql.delta(X_80,X_86,X_87); | | X_125 := algebra.leftfetchjoin(X_74,X_121); | | (X_131,X_132) := algebra.join(X_125,X_17); | | X_182 := algebra.leftfetchjoin(X_131,X_74); | | X_92:bat[:oid,:int] := sql.bind(X_2,"sys","tab1","group_by_col",0,2,4); | | (X_98:bat[:oid,:oid] ,X_99:bat[:oid,:int] ) := sql.bind(X_2,"sys","tab1","group_by_col",2,2,4); | | X_140 := sql.delta(X_92,X_98,X_99); | | X_145:bat[:oid,:int] := algebra.leftfetchjoin(X_182,X_140); | | (X_155,X_156,X_157) := group.subgroupdone(X_145); | | X_158 := algebra.leftfetchjoin(X_156,X_145); | | X_76:bat[:oid,:oid] := sql.tid(X_2,"sys","tab1",3,4); | | X_81:bat[:oid,:int] := sql.bind(X_2,"sys","tab1","index_col",0,3,4); | | (X_88:bat[:oid,:oid] ,X_89:bat[:oid,:int] ) := sql.bind(X_2,"sys","tab1","index_col",2,3,4); | | X_8 := sql.bind(X_2,"sys","tab1","index_col",1); | | X_122 := sql.delta(X_81,X_88,X_89,X_8); | | X_126 := algebra.leftfetchjoin(X_76,X_122); | | (X_133,X_134) := algebra.join(X_126,X_17); | | X_183 := algebra.leftfetchjoin(X_133,X_76); | | X_93:bat[:oid,:int] := sql.bind(X_2,"sys","tab1","group_by_col",0,3,4); | | (X_100:bat[:oid,:oid] ,X_101:bat[:oid,:int] ) := sql.bind(X_2,"sys","tab1","group_by_col",2,3,4); | | X_19 := sql.bind(X_2,"sys","tab1","group_by_col",1); | | X_142 := sql.delta(X_93,X_100,X_101,X_19); | | X_146:bat[:oid,:int] := algebra.leftfetchjoin(X_183,X_142); | | (X_159,X_160,X_161) := group.subgroupdone(X_146); | | X_162 := algebra.leftfetchjoin(X_160,X_146); | | X_184 := mat.packIncrement(X_150,4); | | X_185 := mat.packIncrement(X_184,X_154); | | X_186 := mat.packIncrement(X_185,X_158); | | X_20 := mat.packIncrement(X_186,X_162); | | (X_21,r1_34,X_163) := group.subgroupdone(X_20); | | X_23 := algebra.leftfetchjoin(r1_34,X_20); | | X_103:bat[:oid,:dbl] := sql.bind(X_2,"sys","tab1","f",0,0,4); | | (X_111:bat[:oid,:oid] ,X_112:bat[:oid,:dbl] ) := sql.bind(X_2,"sys","tab1","f",2,0,4); | | X_168 := sql.delta(X_103,X_111,X_112); | | X_172:bat[:oid,:dbl] := algebra.leftfetchjoin(X_180,X_168); | | X_105:bat[:oid,:dbl] := sql.bind(X_2,"sys","tab1","f",0,1,4); | | (X_113:bat[:oid,:oid] ,X_114:bat[:oid,:dbl] ) := sql.bind(X_2,"sys","tab1","f",2,1,4); | | X_169 := sql.delta(X_105,X_113,X_114); | | X_173:bat[:oid,:dbl] := algebra.leftfetchjoin(X_181,X_169); | | X_107:bat[:oid,:dbl] := sql.bind(X_2,"sys","tab1","f",0,2,4); | | (X_115:bat[:oid,:oid] ,X_116:bat[:oid,:dbl] ) := sql.bind(X_2,"sys","tab1","f",2,2,4); | | X_170 := sql.delta(X_107,X_115,X_116); | | X_174:bat[:oid,:dbl] := algebra.leftfetchjoin(X_182,X_170); | | X_109:bat[:oid,:dbl] := sql.bind(X_2,"sys","tab1","f",0,3,4); | | (X_117:bat[:oid,:oid] ,X_118:bat[:oid,:dbl] ) := sql.bind(X_2,"sys","tab1","f",2,3,4); | | X_29 := sql.bind(X_2,"sys","tab1","f",1); | | X_171 := sql.delta(X_109,X_117,X_118,X_29); | | X_175:bat[:oid,:dbl] := algebra.leftfetchjoin(X_183,X_171); | | X_188 := mat.packIncrement(X_172,4); | | X_189 := mat.packIncrement(X_188,X_173); | | X_190 := mat.packIncrement(X_189,X_174); | | X_31 := mat.packIncrement(X_190,X_175); | | X_32 := sql.bind(X_2,"sys","tab2","f",0); | | (X_34,r1_53) := sql.bind(X_2,"sys","tab2","f",2); | | X_36 := sql.bind(X_2,"sys","tab2","f",1); | | X_37 := sql.delta(X_32,X_34,r1_53,X_36); | | X_38 := algebra.leftfetchjoin(X_10,X_37); | | X_176 := algebra.leftfetchjoin(X_128,X_38); | | X_177 := algebra.leftfetchjoin(X_130,X_38); | | X_178 := algebra.leftfetchjoin(X_132,X_38); | | X_179 := algebra.leftfetchjoin(X_134,X_38); | | X_192 := mat.packIncrement(X_176,4); | | X_193 := mat.packIncrement(X_192,X_177); | | X_194 := mat.packIncrement(X_193,X_178); | | X_39 := mat.packIncrement(X_194,X_179); | | X_40:bat[:oid,:dbl] := batudf.udf_multiply_dbl(X_31,X_39); | | X_41:bat[:oid,:dbl] := aggr.subsum(X_40,X_21,r1_34,true,true); | | language.pass(X_70); | | language.pass(X_143); | | language.pass(X_72); | | language.pass(X_144); | | language.pass(X_74); | | language.pass(X_145); | | language.pass(X_17); | | language.pass(X_76); | | language.pass(X_146); | | language.pass(X_20); | | language.pass(X_180); | | language.pass(X_181); | | language.pass(X_182); | | language.pass(X_183); | | language.pass(X_10); | | language.pass(X_38); | | language.pass(r1_34); | | exit X_196; | | X_187:bat[:oid,:int] := nil:bat[:oid,:int]; | | X_191:bat[:oid,:dbl] := nil:bat[:oid,:dbl]; | | X_195:bat[:oid,:dbl] := nil:bat[:oid,:dbl]; | | X_43 := sql.resultSet(2,1,X_23); | | sql.rsColumn(X_43,"sys.tab1","group_by_col","int",32,0,X_23); | | sql.rsColumn(X_43,"sys.L1","L1","double",53,0,X_41); | | X_55 := io.stdout(); | | sql.exportResult(X_55,X_43); | | end s2_1; | +-------------------------------------------------------------------------------------------------------+

1 0

RE: problem with Jan2014 build
by Bas Kaptijn 27 Jan '14

27 Jan '14

Tested with Jan 2014 RC2. All mentioned problems still occur with this release. Also, with the no_mitosis_pipe my program has problems with DELETE's and possibly other queries not taking place effectively somehow. For me the investigation of the Jan2014 build stops here until some of the problems mentioned here are fixed Regards, Bas Van: Bas Kaptijn Verzonden: dinsdag 21 januari 2014 12:55 Aan: 'users-list(a)monetdb.org' Onderwerp: RE: problem with Jan2014 build Some last info: the problem disappears when using the no_mitosis_pipe optimizer pipeline. sql>set optimizer='no_mitosis_pipe'; operation successful (0.139ms) sql>SELECT * FROM source1 WHERE SUBSTRING(SOURCE,1,2)='04' LIMIT 1 OFFSET 400000; +-----------------------------------------------------------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------+ | file | line | source | +=============================================================================+========+====================================================================================================================================================+ | /home/bkaptijn/DDDB/.tmpinputdir/9772_9999_20130501_20130531_Schade_516.ASC | 430664 | 04999999999999999999999999999999999999999 : +-----------------------------------------------------------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------+ 1 tuple (1.1s) sql>set optimizer='default_pipe'; operation successful (0.305ms) sql>SELECT * FROM source1 WHERE SUBSTRING(SOURCE,1,2)='04' LIMIT 1 OFFSET 400000; +------+------+--------+ | file | line | source | +======+======+========+ +------+------+--------+ 0 tuples (798.870ms) Regards, Bas

2 1

Announcement: Second Release Candidate for Jan2014 Release
by Sjoerd Mullender 25 Jan '14

25 Jan '14

The MonetDB team at CWI/MonetDB BV is pleased to announce a second release candidate for the upcoming Jan2014 feature release. Many things have changed in this release compared to the previous (Feb2013) release (that's why it took so long). For a smooth upgrade I would urge you to try out this release candidate and to provide feedback on our bug tracker (http://bugs.monetdb.org) A number of bugs in the previous release candidate have been fixed. Among others, packaging errors on Debian and Ubuntu have been fixed, and a problem with slow startup has been fixed. The release candidate can be found at http://dev.monetdb.org/downloads/testing. -- Sjoerd Mullender

1 0

Re: Contents of users-list digest What differences between monetdb and mongodb for analytical data?
by Edgar Mejia 24 Jan '14

24 Jan '14

Hello Jennie, I can to do clustering, to use (map-reduce, handoop) as monetdb for large amounts of information? any have any documentation for this? What amount of information are you talking about? I have two applications. In 1 i have over 100.000.000 millions of records. And i have other application as more of 400.000.000 millions of records and i need migrate this of oracle. Thanks, Edgar M

1 0

Query speed on MonetDB with 4-5 users
by Charles Bajomo 23 Jan '14

23 Jan '14

Hello, We have just started using MonetDB in production and can I start by saying its a pretty impressive database. We are having a problem with query when we have 4 -5 users running reports and I wanted to know if anyone could recommend some optimisation tips. On particular one is multiplex-funnels. I can't find any documentation on how to create it. I read some where that it can be used to replicate data between two servers. It would be great to get some guidance on how to do this. Kind Regards

1 0

Possible mapi bug in Windows version 20131120?
by Alastair McKinley 23 Jan '14

23 Jan '14

Dear all, I am using MonetDB for 64 bit Windows from the package MonetDB5-SQL-Installer-x86_64-20131120.msi. If I compile the example mapi program from here http://www.monetdb.org/Documentation/Manuals/SQLreference/Programming/MAPI and cause an error in a query (e.g. incorrect port number in mapi_connect params) then the application will crash with the following message: 0xC0000005: Access violation writing location 0x0000000000000024 This appears to happen if any of the following functions are called: mapi_explain(dbh, stderr); mapi_explain_query(hdl, stderr); mapi_explain_result(hdl, stderr); The same code works correctly on Ubuntu 13.04 with MonetDB-11.15.17?. Can anyone reproduce this error on Windows? Best regards and thanks, Alastair

1 0