- users-list - monetdb.org

[MonetDB-users] nanosleep?
by Mag Gam 10 May '09

10 May '09

I am running a query, similar to this: SELECT distinct extract(day from time), extract (month from time) from T; The query takes a long time because the table T has over 280 Million records. However I was curious to run an strace on the mserver5 process and I notced "nanosleep". Is the process sleeping or waiting? strace -p 25984 Process 25984 attached - interrupt to quit restart_syscall(<... resuming interrupted call ...>) = 0 nanosleep({5, 0}, {5, 0}) = 0 nanosleep({5, 0}, {5, 0}) = 0 nanosleep({5, 0}, {5, 0}) = 0

4 4

[MonetDB-users] Memory consumption
by Mag Gam 10 May '09

10 May '09

I have limited memory on our system 16GB of memory and our tables have more than 2 billion records. The underlying storage is extremely fast (300MB/sec read and write and rewrite), therefore I would like to depend mainly on the storage versus placing everything in memory. Is there an option with MonetDB which will let me only allocate 25% of the database into memory and rest seek disk? TIA

3 3

[MonetDB-users] Extra Tables and performance
by Mag Gam 10 May '09

10 May '09

I was wondering if I added referential integrity with a foreign keys will slow down a query. I know the loading will take longer but would MonetDB be much slower if I was doing a query? TIA

2 2

[MonetDB-users] Redundant data
by Mag Gam 09 May '09

09 May '09

Hello All, I am very new to MonetDB and I am very pleased with its performance at our university. Basically, I have data which is stored on our Unix filesystem. /research_data/life/YYYY/MM/DD/species (there are over 100 species files) I then created a very large csv file which is traverses thru this data. The file size is about 2GB. The table I created looks like this t1 ( t as timestamp species as varchar(10) weight as float color as varchar(10) ) The file species.csv looks like this weigh, color 3,red 7,green 4,blue I have a script that basically traverses thru the filesystem and creates a big csv file for example: 2008-04-01, cat,3,red 2008-04-01, cat,7,green 2008-04-01, cat,4,blue This works fine, but if I run the copy() operation again it will put redundant data. Is there anyway to avoid this?

2 2

[MonetDB-users] porting a financial time series database from mysql (how to represent datetime fields)
by Tom H 08 May '09

08 May '09

Hi monetDb collective, I am porting a test database over from a MySQL schema, and it currently looks like this; CREATE TABLE `MSFT` ( `date` datetime default NULL, `o` decimal(10,0) default NULL, `h` decimal(10,0) default NULL, `l` decimal(10,0) default NULL, `c` decimal(10,0) default NULL, `v` decimal(10,0) default NULL, `a` decimal(10,0) default NULL, `period` varchar(12) default 'daily' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1) Whats the equivalent monetDB method for representing the datetime of the start of the OHLC period? 2) As lots of queries are going to pull all the data from a particular symbol table to do things like calculate moving average, should I even be using datetime as the time index, should I use an integer value instead? Many thanks, Tom

1 1

[MonetDB-users] Trigger oddities
by Bram Van Dam 08 May '09

08 May '09

Hi, I'm experiencing some strange trigger behaviour. Not sure if I'm simply missing something, or if I've run into a bug. create table t1 (id serial, toggle boolean); create table t2 (id serial, ref bigint); insert into t1 (toggle) values (false); insert into t1 (toggle) values (false); create trigger updateMe after update on t1 referencing new row new_row for each row when new_row.toggle = true insert into t2 (ref) values (new_row.id); update t1 set toggle = true where id = 1; After this update statement, I would expect to see *1* row in t2. Instead, I see two rows: +----+-----+ | id | ref | +====+=====+ | 1 | 1 | | 2 | 2 | +----+-----+ The "when" clause seems to be ignored entirely -- I can change to "when 1 = 0" and it'll still work. If I change the trigger to create trigger updateMe after update on t1 referencing new row new_row for each row insert into t2 (ref) values (new_row.id); something equally odd happens. Instead of doing an insert for each UPDATED row, it inserts a row for each row present in t1. This seems a bit odd to me, but again, I could just be misunderstand the way this is supposed to work. Documentation on triggers is a bit sparse I'm afraid... Any help on this issue would be more than welcome! - Bram

2 2

[MonetDB-users] clarity on creating users/reseting passwords
by Tom H 08 May '09

08 May '09

Hi, I need to reset some passwords on a monetDB5 installation, and I am not clear on how to do it. Currently I can't login at all. Is there a way to enter single-user mode, or something so I can reset the monetDB user password? Also, I really don't understand the difference between running monet as mserver5 and Merovingian. Basically I want to run the server from an init.d script, which should I use? At the moment I am running the server like so; [root@localhost ~]# mserver5 --dbinit="include sql;" # MonetDB server v5.10.4, based on kernel v1.28.4 # Serving database 'demo', using 2 threads # Compiled for x86_64-redhat-linux-gnu/64bit with 64bit OIDs dynamically linked # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2009 MonetDB B.V., all rights reserved # Visit http://monetdb.cwi.nl/ for further information #warning: please don't forget to set your vault key! #(see /etc/monetdb5.conf) # Listening for connection requests on mapi:monetdb://127.0.0.1:50000/ # MonetDB/SQL module v2.28.4 loaded > Can I reset the password using MAL commands from the prompt? Thanks, Tom

2 1

[MonetDB-users] Primary-Foreign keys
by Alex Bo. 07 May '09

07 May '09

Hi, I wanted to ask what I should expect in terms of performance out of primary-foreign keys. Do join queries that have primary keys defined on their tables return faster? Are such joins materialized in the database? If not - is there any other way to control materialization of joins? Thanks. -- View this message in context: http://www.nabble.com/Primary-Foreign-keys-tp23428537p23428537.html Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] copy into from a file hangs
by Venu Alatzeth 06 May '09

06 May '09

Hi, I am evaluating MonetDB server v5.8.4, based on kernel v1.26.4 on FreeBSD 6.2 Loading data into a table from a file seem to hang, with mserver using high CPU. The file has only 10 records. If I use stdin instead of a file name the data can be loaded. Here is the debug output. openRead(nil) does not seem right. shouldn't it be openRead('/tmp/head.txt') ? sql>debug copy 10 records into "log_0" from '/tmp/head.txt' using delimiters '|'; mdb># mdb.start(); mdb>n mdb># user.s0_2(); mdb>n mdb># _2 := streams.openRead(nil); mdb>n What am I doing wrong? Thanks for your help, -VG

2 1

[MonetDB-users] Cannot install MonetDB with monetdb-install.sh
by Stefan Klinger 06 May '09

06 May '09

Hello, I'm having problems installing a current (--nightly=current) version of MonetDB: Running monetdb-install.sh provied at [1] displays a lot of HTTP 404 error messages. Then it successfully downloads a 0Byte MonetDB4-XQuery.tar.gz which it cannot unpack. The logfile is attached. Tested on openSUSE 10.2 and Debian GNU/Linux squeeze/sid. Just ask if you need more details. Thank you very much, Stefan ____________________ [1] http://monetdb.cwi.nl/projects/monetdb/Assets/monetdb-install.sh -- Stefan Klinger o/klettern /\/ bis zum send plaintext only - max size 32kB - no spam \ Abfallen http://stefan-klinger.de

2 1