May 2010 - users-list

[MonetDB-users] updates in a transaction
by Min Lu 19 May '10

19 May '10

Hi, I tried to update a column of a record more than once within a transaction. But it seemed that only the first update took effect. For example, for a table "CREATE TABLE people (id TINYINT PRIMARY KEY, name VARCHAR(128) NOT NULL)", sql>select * from people; +------+-----------------------------+ | id | name | +======+=============+ | 0 | Phil Ivey | | 1 | Michael Jordan | | 2 | Lionel Messi | +------+-----------------------------+ sql>start transaction; auto commit mode: off sql>update people set id = -1 where name='Phil Ivey'; 1 affected row sql>select * from people; +------+-----------------------------+ | id | name | +======+=============+ | 1 | Michael Jordan | | 2 | Lionel Messi | | -1 | Phil Ivey | +------+-----------------------------+ sql>update people set id = -2 where name='Phil Ivey'; 1 affected row sql>select * from people; +------+-----------------------------+ | id | name | +======+=============+ | 1 | Michael Jordan | | 2 | Lionel Messi | | -1 | Phil Ivey | +------+-----------------------------+ sql>commit; auto commit mode: on sql>select * from people; +------+-----------------------------+ | id | name | +======+=============+ | -1 | Phil Ivey | | 1 | Michael Jordan | | 2 | Lionel Messi | +------+-----------------------------+ It is not what we would expect, isn't it? My MonetDB version is v5.18.3 Min

3 5

[MonetDB-users] crash when doing count(distinct col)
by Sam Mason 18 May '10

18 May '10

Hi, I've just pulled a largish dataset into monetdb (419851473 rows, 8 columns) and it's crashing when I try to do the following: select count(distinct a) from calldata; "a" is an integer column and monetdb gets the right answer of 886693 when I run either: select count(a) from (select a from calldata group by a) x; or select count(a) from (select distinct a from calldata) x; I'm running the latest 64bit .deb packages under ubuntu 10.4. The following is what I get back from mclient: sql>select count(distinct a) from calldata; MALException:!ERROR: HEAPalloc: Insufficient space for HEAP of 3358812160 bytes. ERROR: HEAPalloc: Insufficient space for HEAP of 3358812160 bytes. ERROR: HEAPalloc: Insufficient space for HEAP of 3358812160 bytes. Timer 93948.763 msec 1 rows I've not got much swap space, does it just happen to need it for the first version but not the other versions? -- Sam http://samason.me.uk/

2 2

[MonetDB-users] MonetDB performance
by dariuszs 18 May '10

18 May '10

Hi, So what happens (performance wise) when you have 50GB of memory, 300GB of data and on average 100 concurrent users using all the tables and columns in your database? Thanks. Dariusz.

7 13

[MonetDB-users] MonetDB/SQL: bulk-loading large data sets & adding key constraints
by Stefan Manegold 15 May '10

15 May '10

Dear all, derived from recent experiments and intensive testing, here are some recommendations for bulk-loading large data sets (in particular those larger than your available main memory) in MonetDB/SQL and adding constraints, i.e., primary and foreign keys. First of all, bulk loading should be done from (possibly gzip or bzip2 compressed) CSV files using the SQL `COPY INTO` command; cf., http://monetdb.cwi.nl/SQL/Documentation/Data-Manipulation.html In case your tables come with primary or foreign key constraints, it is recommended to omit these constrains when initially creating the tables. Then bulk-load (`COPY INTO`) your data. Once the data is loaded add the key constraints one by one using the SQL `ALTER TABLE ... ADD CONSTRAINT ...` command. Note, though, that the current Feb2010 release of MonetDB/SQL might experience some performance problems when adding constraints on data sets larger than main memory on multi-core machines. The reason it a non-optimal query translation and plan parallelization. For the time being, i.e., until we manage to fix this problem properly, it is recommended to resort to the optimizer pipeline of the Nov2009 release (for insiders, this effectively means disabling the "mitosis" optimizer), (only) while adding the key constraints. To do so, simply activate the Nov2009 optimizer pipeline via SET optimizer='nov2009_pipe'; just before adding the key constraints via `ALTER TABLE ... ADD CONSTRAINTS` and then restore the default optimizer pipeline via SET optimizer='default_pipe'; once you're done with adding constraints. For TPCH scale factor 100 (100 GB) on a 8 GB 4-core machine, this reduces the total time to add all key constraints from ~1.5h to ~1h. For SSB scale factor 100 (100 GB) on a 8 GB 4-core machine, this reduces the total time to add all key constraints from ~4h to ~1h. Hope this helps. Please don't hesitate to comment or ask questions. We'll let you know, one we managed to fix this problem properly. Kind regards, Stefan -- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4199 |

1 0

[MonetDB-users] Help,how to create hash index on table
by 冯亮 15 May '10

15 May '10

Hello, Current I am using MonetDB server v5.18.3 , and as i see in Wiki , MonetDB support hash index ,I intended to create a hash index on a table. But how to do it , i have referred to sqlmanual downloaded from monetdb.cwi.nl, but it simply wrote following format: CREATE [UNIQUE] INDEX <index_name> ON <table_name> ( <column_name>, ...) ; Is that Btree index or hash index or others? Does MonetDB support multiple column hash index? I am looking forward to your reply. Thanks a lot. Andrew. Feng

4 3

[MonetDB-users] Help,how to create hash index on table
by fll 09 May '10

09 May '10

Hello, Current I am using MonetDB server v5.18.3 , and as i see in Wiki , MonetDB support hash index ,I intended to create a hash index on a table. But how to do it , i have referred to sqlmanual downloaded from monetdb.cwi.nl, but it simply wrote following format: CREATE [UNIQUE] INDEX <index_name> ON <table_name> ( <column_name>, ...) ; Is that Btree index or hash index or others? Does MonetDB support multiple column hash index? I am looking forward to your reply. Thanks a lot. Andrew. Feng

2 1

[MonetDB-users] Move of SourceForge bugtracker to MonetDB Bugzilla
by Fabian Groffen 07 May '10

07 May '10

This message is important for all that used to report bugs on our SourceForge tracker, or are planning to report bugs in the future. With the move of our CVS repository to a new Mercurial repository, we have also migrated the bugtracker from SourceForge to a Bugzilla instance. The old SourceForge trackers are no longer in use. Please DO NOT file any bugs there, as they will get lost. We have removed their presence as far as possible to avoid any confusion here. The new location to search, read and file new bugs is here: http://dev.monetdb.org/bugzilla/ (Please bear with us while we try to get the interface/skin well suited for our project.) All bugs that were previously on the SourceForge trackers have been migrated to the new Bugzilla instance. The bug numbers could not be retained, hence all have been renumbered. See [1] for dealing with this in Bugzilla. All users that have ever reported a bug, commented on a bug, or been an assignee for a bug on SourceForge have been imported in Bugzilla. This means that for all those users a <sf-nickname>@users.sourceforge.net account has been created. Exception to this are users who work at CWI, whose addresses have been mapped to their CWI address. If you want to use your migrated account, simply click the "Forgot Password" link on the Bugzilla main page. It will send an email to your (SourceForge) email address with a token to allow you to set a password. If you are a new user, you can simply sign up on our Bugzilla using the "Open a New Account" button. In both cases, make sure you look through the preferences, in particular the "Email Preferences". For migrated accounts these preferences are set to exclude sending any bug email, which may or may not be what you prefer. Feel free to ask for any help if problems arise. [1] Searching an old bug using its bugnumber: If you have an old SourceForge bug number and need to find tha associated bug in Bugzilla, open up an advanced search, and enter the old bug number in the URL field. Make sure that you select the appropriate bug resolutions (e.g. include RESOLVED) and search. Each migrated bug has an URL set to the original bug on SourceForge, hence is easily found this way.

3 2

[MonetDB-users] Parallel Processing
by Nafees Ur Rehman 04 May '10

04 May '10

Hi, Does monetdb has support for parallel processing? If some can guide me on that. Regards Nafees Ur Rehman

4 8

[MonetDB-users] trigger ambiguous issue
by Huabing 03 May '10

03 May '10

I have following trigger statement: CREATE TRIGGER delete_pos_cp AFTER DELETE ON cp referencing old row as o FOR EACH ROW WHEN o.new_id_ IS NOT NULL UPDATE cp SET old_id_ = o.old_id_ WHERE rec_id_ = o.rec_id_; Following error is issued: SELECT: identifier 'rec_id_' ambiguous The last "o.rec_id_" gives rise to this ambigious. Any suggestion on this.

2 1