May 2009 - users-list

[MonetDB-users] selection queries
by Mayank Maheshwari 13 May '09

13 May '09

Hi, I was trying out selection queries with varying selectivity factors on a 1 million tuple relation. The selectivity factors for predicates were 0.1%, 1% and 10% of the entries in the column and the data type of the column is int. What I'm seeing is that the execution time increases with selectivity factors going from 0.1% to 1% to 10%. I'm not sure why this is happening though, considering its a column-store system. Should this be attributed to increase in CPU time or disk I/Os or any other factor? What my understanding of column-store systems is that the execution time should remain constant for varying selectivity factors for the same column. Any help would be appreciated. Thanks Mayank Mayank Maheshwari mayank(a)cs.wisc.edu

3 2

[MonetDB-users] Odd Create Table behavior
by JLee 13 May '09

13 May '09

Hi: I must be doing something really dumb. But, I can't seem to find my way around creating a simple table in the Feb SP1 release db. I have several tables that I created couple of weeks ago as "user_test" and they have been working just fine. Today, I tried to create another table in DBVisualizer and the new table gets created with SYSTEM TABLE table_type definition. I tried logging in as monetdb as well as user_test and tried to create the table. But, the table always gets created as a SYSTEM TABLE. I thought it may be an issue with DBVisualizer. So, I used the following command to start mclient: mclient -lsql -d <dbname> -u <username> -P <password> The command returns a request for root username and password. After entering monetdb/monetdb for username/password, I get an error that says: user_test: cannot open user_test: cannot open I tried the same mclient command as monetdb and I get the same error. What am I doing wrong? Thanks! JLee

1 0

[MonetDB-users] Table Views
by Mag Gam 11 May '09

11 May '09

I have 5 tables like named this: 2009.01 2009.02 2009.03 2009.04 2009.05 Each table has a columns like this: ( id integer, ts timestamp, value char(50) ); If I would like to search from 2009.01 to 2009.04 is it possible to build a query like that? I was thinking if there is a clever VIEW I can create that would include all 5 tables as 1 table. Any thoughts or ideas? TIA

3 3

[MonetDB-users] MonetDB papers
by Mayank Maheshwari 11 May '09

11 May '09

Hi, Can someone please point me to the papers which describe the internals of MonetDB? I'm looking to find answers to issues like what are the compression techniques MonetDB uses, does it horizontally partition the tables into segments identified by segment IDs and use join indexes for joins. I have come across these ideas in C-store and I was wondering if MonetDB uses the same ideas or does something different. Thanks. Mayank Mayank Maheshwari mayank(a)cs.wisc.edu

3 2

[MonetDB-users] Database Design Question/Advice
by Mag Gam 10 May '09

10 May '09

At my university we are working on a project for financial engineering. We obtained trade data from Newyork Stock Exchange. The data looks like this: YYYYMMDD/Symbol There are about 5000 YYYYMMDD directories and 9000 Symbols The symbol is a tsv or csv file and it has 20 fields and has about 30k lines (average) Clearly, I can't place all this into 1 table (or can I? ) called Symbol. This would entail about 5000*9000*30000 = 1350000000000 entries in a table :-) I think thats crazy :-) I was wondering if its possible to partition the tables into YYYY or MM. Or maybe someone here has better ideas on howto better normalize the data. Any thoughts or ideas? TIA

1 0

[MonetDB-users] merovingian crash
by Mag Gam 10 May '09

10 May '09

While loading a lot of data, I noticed my MonetDB crashed In the log files I get this error: MSG merovingian[17779]: database 'demo' already running since 2009-05-10 14:12:21, up min/avg/max: 66/21400/101789, crash average: 0.00 0.00 0.00 (9-8=0) MSG merovingian[17779]: proxying client 127.0.0.1:51882 for database 'demo' to mapi:monetdb://srv011:50001/ MSG merovingian[17779]: starting a proxy failed: failed to connect: Connection refused ERR merovingian[17779]: client error: failed to connect: Connection refused MSG merovingian[17779]: database 'demo' already running since 2009-05-10 14:12:21, up min/avg/max: 66/21400/101789, crash average: 0.00 0.00 0.00 (9-8=0) Any ideas? TIA

3 4

[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