May 2009 - users-list

[MonetDB-users] Freeing memory through Mapi
by Alex Bo. 18 May '09

18 May '09

Hi, Sometimes when memory-heavy operations on MonetDB finish (I run them through Mapi), memory that is used by the MonetDB process isn't freed. Is there a Mapi command I can use too make Monet free up memory? What I do now is restart the process, but I am hoping to be able to avoid that. Thanks. -- View this message in context: http://www.nabble.com/Freeing-memory-through-Mapi-tp23595876p23595876.html Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] (Jdbc?) concurrency issues
by Bram Van Dam 18 May '09

18 May '09

Hi, I'm implementing a bit of a consumer-producer application in Java using the JDBC driver. When I use transactions in the producer & consumer, I'll frequently get the "ERROR: COMMIT: transaction is aborted because of concurency conflicts, will ROLLBACK instead" error. Not quite sure why this keeps happening. It's not like the same row is modified concurrently. The same *table* is (insert in one thread, vs delete in another). Could this be the cause of the problem? And more importantly, how do I get around this? thanks a bunch, - Bram

7 12

[MonetDB-users] MonetDB vs MySQL command list
by Tom H 15 May '09

15 May '09

Hi monetDB collective, I thought I would post to the list my current monetDB vs MySQL command list, as I had a bit of trouble working out how to do some routine admin stuff. I have to go and do some real work now, so I will update this later and post a more complete version. I would be grateful for any comments. Cheers, T Mysql command:CREATE DATABASE test1; The equivilent monetDB command is accessed using the monetDB tool from the command line; [root@localhost ~]$ monetdb create test1 warning: merovingian is not running successfully created database 'test1' Mysql command:SHOW DATABASES; The equivilent monetDB command is accessed using the monetDB tool from the command line; [root@localhost ~]# monetdb status warning: merovingian is not running name state uptime health last crash demo running 8m 38s 50%, 0s 2009-05-08 09:44:05 test1 stopped Mysql command:USE DATABASE test1; I don't think that you can change the database context using SQL within the client, so it would seem the way to access another database is to login to another client session with the alternative database name; shell> mclient -lsql --database=test1 sql> Mysql command:SHOW TABLES; shell> mclient -lsql --database=test1 sql>sql>select * from tables where system = false; I've not yet noted what the direct equivilents of these commands are; DESCRIBE TABLE; SHOW CREATE TABLE; GRANT ALL PRIVILEGES ON DATABASENAME.* to SOMEONE@localhost IDENTIFIED BY "PASSWORD"; mysqldump --opt -u username -p database > database_backup.sql mysql -u username -p database < database_backup.sql And I'm not entirely clear how a schema works, I'm guessing similar to the oracle model.

2 1

[MonetDB-users] rank over, sum over
by Alex Bo. 14 May '09

14 May '09

Hi, I am interested in calculating running sums, and therefor I looked for something like 'SUM OVER'. Since that doesn't exist, I looked into 'RANK OVER', and am trying to implement 'SUM OVER' according to that. I am looking at sql_rank_grp() in sql.c and rel_rankop() in rel_select.c, and am thinking how to modify them to support running sum. However, I can't figure out how to add another column to the parameters that sql_rank_grp gets (the column I want to sum over). I can't figure out how these two functions work together. Where are the incoming parameters for sql_rank_grp() created? I can see that rel_rankop() fills an sql_exp struct with the 'group by' and 'order by' columns, but I cannot find where to go from there. Any kind of guidance in the right direction would be appreciated. Thanks. -- View this message in context: http://www.nabble.com/rank-over%2C-sum-over-tp23543557p23543557.html Sent from the monetdb-users mailing list archive at Nabble.com.

1 0

[MonetDB-users] doc-available unuseable?
by Floris Ouwendijk (DT) 14 May '09

14 May '09

Hi, Given the following code, I'd assume to get an empty b element (for a database not containing the given document). if (doc-available('asdasda')) then doc('asdasda') else element b{} The result happens to be QUERY = if (doc-available('asdasda')) ERROR = !ERROR: [shred_url]: 1 times inserted nil due to errors at tuples 0@0. !ERROR: [shred_url]: first error was: !ERROR: shred: cannot stat `asdasda': No such file or directory !ERROR: CMDshred_url: operation failed. !ERROR: interpret_params: leftfetchjoin(param 2): evaluation error. If I do if (doc-available('asdasda')) then element a{} else element b{} Then i get b. What is the proper way to return some default value when a document is not available in the database? Or is this the proper way and should this be reported as a bug? Kind regards, Floris

4 4

[MonetDB-users] Load times expected
by Karanbir Singh 13 May '09

13 May '09

Is this reasonable : $ time ( echo "copy 587321890 records into sample from STDIN using delimiters '\t','\n','';" ; zcat logs20090511.txt.gz ) | recode iso8859-1..utf-8 | mclient -lsql -ukb -Pkb [ 587321890 ] real 618m29.030s 618m is a bit excessive, and well over what I had expected! Background on the machine : its a dual cpu opteron 252, 6 GB ram, running centos5.3/x86_64 stock - storage is from a 2 disk raid0, local to the machine ( sata 7200 disks, md-raid ). size of this logs file, unzipped is : 56854417129 There is almost nothing else running on the machine ( load avg from pre-kickoff was ~ 0 0 0 ). An aggregate querry that I kicked off last night ( about 14 hrs back ) is still running... Are these levels of performance in line with what most people would expect ? Is there a generic tuning guide or a best-practises doc that I should be looking at about now ? As something to compare against, pgsql-8.3 on the same machine, was able to do its bulk load in under 58 min, and then took just over 10 hours to run the aggregate query ( no index's on data ) : - KB

2 1

[MonetDB-users] copy from stdin oddness
by Karanbir Singh 13 May '09

13 May '09

Hi, This is on CentOS-5/x86_64 running : [kbsingh@koala data]$ rpm -qa | grep -i Monet MonetDB-1.22.0-1.el5.kb.oid64 MonetDB5-server-5.4.0-1.el5.kb.oid64 MonetDB-SQL-2.22.0-1.el5.kb.oid64 MonetDB-client-1.22.0-1.el5.kb.oid64 MonetDB-SQL-server5-2.22.0-1.el5.kb.oid64 server started with : [root@koala ~]# mserver5 --dbinit="include sql;" on the client : [kbsingh@koala data]$ echo "\d sample" | mclient -lsql -ukb -Pkb CREATE TABLE "kb"."sample" ( "id" char(33), "sdate" char(20), "key" varchar(255), "country" char(2), "region" char(2), "city" varchar(50), "pid" int ); [kbsingh@koala data]$ echo "select 'hello';" | mclient -lsql -ukb -Pkb % . # table_name % single_value # name % char # type % 5 # length [ "hello" ] [kbsingh@koala data]$ echo "copy 1000 records into sample from '/home/kbsingh/data/data/1000.utf8' using delimiters '\t','\n','';" | mclient -lsql -ukb -Pkb [ 1000 ] also: [kbsingh@koala data]$ mclient -lsql -ukb -Pkb -s "copy 1000 records into sample from '/home/kbsingh/data/data/1000.utf8' using delimiters '\t','\n','';" [ 1000 ] so, works so far. However: [kbsingh@koala data]$ cat 1000.utf8 | mclient -lsql -ukb -Pkb -s "copy 1000 records into sample from STDIN using delimiters '\t','\n','';" MAPI = kb@localhost:50000 QUERY = copy 1000 records into sample from STDIN using delimiters '\t','\n',''; ERROR = !SQLException:sql:missing sep line 0 field 0 !SQLException:importTable:failed to import table given that mclient is on the right side of the pipe, the data is surely being made available on the stdin, but why does mclient fail like this ? also, adding an OFFSET makes mclient just die quietly like this: [kbsingh@koala data]$ cat 1000.utf8 | mclient -lsql -ukb -Pkb -s "copy 5 offset 1000 records into sample from STDIN using delimiters '\t','\n','';" [kbsingh@koala data]$ so, what am I getting so wrong here ? the load chain I need to setup will look like this : zcat andy1.gz | iconv -f latin1 -t utf8 | mclient ...... that compressed file is 62 GB, the changes of me being able to expand that run the load from a specific filename is zero. -- Karanbir Singh : http://www.karan.org/ : 2522219@icq

2 4

[MonetDB-users] Timestamp performance
by Alex Bo. 13 May '09

13 May '09

Hi, How fast are join operations on timestamp fields compared to joins on int/bigint fields? I thought about using timestamp.unix_epoch to store numbers instead of timestamps to speed up joins, but if timestamp joins are just as fast then that wouldn't be necessary. Thanks. -- View this message in context: http://www.nabble.com/Timestamp-performance-tp23520512p23520512.html Sent from the monetdb-users mailing list archive at Nabble.com.

3 2

Re: [MonetDB-users] Version 5 crashing.
by Jens Oehlschlägel 13 May '09

13 May '09

> Yes, have to be on a network filesystem. Its pretty fast, I can get > close to 500MB/sec consistently. Also, it has over 50TB of stable > available :-) > > This issue is getting very frustrating. :-( Could it be that specific OS configuration is needed for memory-mapping network-shares? MonetDB makes heavy use of memory mapping. Just an idea Jens

1 0

[MonetDB-users] deleting sql_logs and disappearing tables
by Alex Bo. 13 May '09

13 May '09

Hi, Newly created tables disappear if I follow this sequence of actions: 1. Create a new table and insert data into it. 2. Shut down MonetDB. 3. Manually remove the sql_logs folder. 4. Start MonetDB 5. Now the newly created table is gone. What exactly is the purpose if the sql_logs folder, and when is it safe to delete it? The reason I deleted the sql_logs folder in the first place, is that a while ago I got into a corrupt DB state, and the mserver prompted me to remove the log folder. Doing so fixed the DB and it was usable again, so I assumed deleting the sql_logs folder would always be ok before starting Monet. -- View this message in context: http://www.nabble.com/deleting-sql_logs-and-disappearing-tables-tp23518145p… Sent from the monetdb-users mailing list archive at Nabble.com.

2 1