- users-list - monetdb.org

[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

Re: [MonetDB-users] sql query size
by dariuszs 27 Apr '10

27 Apr '10

Hi, This still does not work in the latest version - Feb2010+SP2 - on Windows XP 32bit, Windows Server 2003 64bit,Ubuntu 9.10 64 bit,Fedora 12 64bit. Dariusz. Martin Kersten wrote: > Thanks for the example, we will look into it. > > dariuszs wrote: >> Martin, >> 1) Table schema >> sql>\d zip_codes >> CREATE TABLE "sys"."zip_codes" ( >> "zip_code" varchar(5) >> ); >> sql> >> >> 2) From mclient >> sql>\< /tmp/test.sql >> MAPI = monetdb@localhost:50000 >> ACTION= read_line >> QUERY = select count(*) from zip_codes WHERE zip_code IN >> ('00501','00544','01001','01002','01003','01004','01005','01007','01008','01009','01010','01011','01012','01013','01014','01020','01021','01022','01026','01027','01028','01029','01030','01031','01032','01033','01034','01035','01036','01037','01038','01039','01040','01041','01050','01053','01054','01056','01057','01059','01060','01061','01062','01063','01066','01068','01069','01070','01071','01072','01073','01074','01075','01077','01079','01080','01081','01082 >> >> ERROR = Connection terminated >> sql> >> 3) From shell >> root@monetdb:/tmp# mclient -lsql < test.sql >> MAPI = monetdb@localhost:50000 >> ACTION= read_line >> QUERY = select count(*) from zip_codes WHERE zip_code IN >> ('00501','00544','01001','01002','01003','01004','01005','01007','01008','01009','01010','01011','01012','01013','01014','01020','01021','01022','01026','01027','01028','01029','01030','01031','01032','01033','01034','01035','01036','01037','01038','01039','01040','01041','01050','01053','01054','01056','01057','01059','01060','01061','01062','01063','01066','01068','01069','01070','01071','01072','01073','01074','01075','01077','01079','01080','01081','01082 >> >> ERROR = Connection terminated >> root@monetdb:/tmp# >> 4) SQL >> >> >> Dariusz >> >> Martin Kersten wrote: >>> Hi Dariusz, >>> >>> A bug or a system limitation. It should at least never crash. >>> >>> How did you submit the query? To mclient? or mserver? or through >>> a mapi libary call? >>> >>> I would have to look into the SQL parser. The underlying stream would >>> expand upon need. It is, however, an interesting query. What kind of >>> application generates it? >>> >>> regards, Martin >>> >>> ps. we should keep a profile of users to avoid asking platform >>> details. >>> >>> dariuszs wrote: >>>> Hi Martin, >>>> Then I guess it's a bug. Please try running a query over 44k, >>>> you'll get >>>> MAPI = monetdb@localhost:50000 >>>> ACTION= read_line. >>>> QUERY = select count(*) from plk100 WHERE c15 IN >>>> ('00501','00544','01001','01002','01003'.......... >>>> ERROR = Connection terminated >>>> Any query under 44k runs fine, so I'm assuming the limit is 64k >>>> (query + other packet data) >>>> >>>> Thanks. Dariusz. >>>> >>>> >>>> >>>> Martin Kersten wrote: >>>>> dariuszs wrote: >>>>> >>>>>> Hi, >>>>>> Is there any limit on how big the sql query can be (in bytes)? >>>>>> Thanks. Dariusz. >>>>>> >>>>>> >>>>> As far as i am aware no. >>>>> >>>>>> >>>>>> ------------------------------------------------------------------------------ >>>>>> >>>>>> Download Intel® Parallel Studio Eval >>>>>> Try the new software tools for yourself. Speed compiling, find bugs >>>>>> proactively, and fine-tune applications for parallel performance. >>>>>> See why Intel Parallel Studio got high marks during beta. >>>>>> http://p.sf.net/sfu/intel-sw-dev >>>>>> _______________________________________________ >>>>>> MonetDB-users mailing list >>>>>> MonetDB-users(a)lists.sourceforge.net >>>>>> https://lists.sourceforge.net/lists/listinfo/monetdb-users >>>>>> >>>>> >>>>> >>>>> ------------------------------------------------------------------------------ >>>>> >>>>> Download Intel® Parallel Studio Eval >>>>> Try the new software tools for yourself. Speed compiling, find bugs >>>>> proactively, and fine-tune applications for parallel performance. >>>>> See why Intel Parallel Studio got high marks during beta. >>>>> http://p.sf.net/sfu/intel-sw-dev >>>>> _______________________________________________ >>>>> MonetDB-users mailing list >>>>> MonetDB-users(a)lists.sourceforge.net >>>>> https://lists.sourceforge.net/lists/listinfo/monetdb-users >>>>> >>>>> >>>>> >>>>> >>>> >>>> >>>> >>> >>> >>> >>> >> > > > >

1 0

[MonetDB-users] ODBC connection problem.
by Nozhup 27 Apr '10

27 Apr '10

I get the following error when trying to connect a MonetDB database with 'OpenOffice.org Database' through the ODBC driver. [unixODBC][MonetDB][ODBC Driver 1.0]Client unable to establish connection .ini-files used: odbc.ini: [MonetDB_User] Description = Default MonetDB Data Source Driver = MonetDB Host = localhost Port = 50000 Database = TestDB User = monetdb Password = monetdb Debug = odbcinst.ini [MonetDB] Description = MonetDB Driver Driver = Driver64 = /home/breplu/MonetDB/lib/libMonetODBC.so Setup = Setup64 = /home/breplu/MonetDB/lib/libMonetODBCs.so UsageCount = 1 CPTimeout = CPReuse = MonetDB version: Feb2010 release A ODBC connection between MySql and OpenOffice works correctly, so I don't think the problem lies with OpenOffice. Am I doing something wrong or is this a know issue with MonetDB? Thanks in advance. -- View this message in context: http://old.nabble.com/ODBC-connection-problem.-tp28364397p28364397.html Sent from the monetdb-users mailing list archive at Nabble.com.

1 1

[MonetDB-users] SMELLIEST data types???
by Steve Schmechel 27 Apr '10

27 Apr '10

I'm guessing that this was meant to be "SMALLINT". From: http://monetdb.cwi.nl/SQL/Documentation/SQL-Features.html Feature ID Feature name S/N B011-B017 Embedded Language support. Core SQL:1999 says that at least one of Embedded Ada, Embedded C, Embedded Cobol, Embedded Fortran, Embedded MUMPS, Embedded Pascal or Embedded PL/I 1 should be supported. N E011 Numeric data types (FULL support) S E011-01 INTEGER and SMELLIEST data types ^^^^^^^^^ I know I read that page before, funny I never noticed that. (I have to admit, I did check the standard just to make sure it wasn't in there and I had just missed it all these years.) I have seen a few database tables that had some really stinky data types, but I'm glad I never came across one that contained the smelliest. :-) Steve

2 1