- users-list - monetdb.org

[MonetDB-users] strange decrease in performance after some deletes
by Majid Azimi 25 Jun '12

25 Jun '12

Hi guys, I have loaded 80 million records in database. a very complex aggregate query(multiple COUNT, SUM, AVG, GROUP BY combined) runs deadly fast in about 30 seconds. But after deleting 700 records performance has been decreased alot. the same query runs in 8 minutes now. Is this usual? Thank you

1 0

[MonetDB-users] Changing SysV parameteres
by Majid Azimi 19 Jun '12

19 Jun '12

Hi guys, Does changing SysV parameters such as SHMMAX, SHMMIN, SHMSEG , ... affects MonetDB performance? How they affect? I mean the vanilla installation of MonetDB on a very powerful hardware needs massive optimization like what we see on other relational DBMS products?

1 0

[MonetDB-users] Announcement: New Apr2012-SP2 Bugfix release of MonetDB suite
by Sjoerd Mullender 19 Jun '12

19 Jun '12

The MonetDB team at CWI/MonetDB BV is pleased to announce the Apr2012-SP2 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <http://www.monetdb.org/>. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>. Apr 2012-SP2 bugfix release Client Package * Resolved a cosmetical error where tab-characters would cause misalignments in tabular result views. For the time being, tabs are now represented as a single space in tabular view. MonetDB Common * Limit number of detected CPU cores to 16 on 32-bits systems to avoid running quickly out of addressable resources followed by a kill from the OS. Bug Fixes * 2594: NULL cannot automatically be casted to every other type * 3085: assertion fails upon execution of a valid SQL query * 3086: Modulo function does not work in select from table * 3087: query causes "Not yet implemented" message * 3089: mserver5 crashed if join condition has two column and incorrect order * 3090: crashed if using single identifier for where condition * 3096: Type of median error

1 0

Re: [MonetDB-users] python-monetdb and pip
by Alexey Mints 18 Jun '12

18 Jun '12

Hallo! As far as Python question has been raised. There is a tiny thing that is (probably) missing in the client. Connection class (and other classes as well) are not inherited from the "object" class, therefore further inheritance and method overriding is problematic. I think, this might be corrected in future. Regards, Alexey Mints

3 2

[MonetDB-users] Locality Sensitive Hashing method for Image Retrieval
by BOOM Bas 18 Jun '12

18 Jun '12

Hi, Sorry, I am new to this mailinglist and not sure this question fits, but have I question how to optimize my queries I am experimenting with monetdb for image retrieval, having an image database of 300000 image. At the moment, I am using the Locality Sensitive Hashing method and because I understood from the monetdb documenation that monetdb supports indexing/hash tables, which makes the implementation quiet easy in monetdb. I have for each image 30 buckets with 24 bit strings (int) (should be indexed, assume automatically done by monetdb) I have for each image a feature vector of 1000 real values. I have for each image a unique id together with some info like file name. At the moment, my table is as follows image_id, filename, bucket_1, bucket_2, bucket_3 ... bucket_30, feature_value_1, feature_value_2, ..., feature_value_1000 For Locality Sensitive Hashing, I am performing the following sql query: select * from table where bucket_1 = bitstring or bucket_2 = bitstring or bucket_3 = bitstring or ... bucket_30 = bitstring (limit 1000) These bitstrings are quiet unique, giving me around the 500 images from the entire database. I would also like to obtain the feature vector (so all feature values). At the moment, query and data retrieval times depends but often take around 0.5-1 minute, what are ways to improve this. Does using 2 tables help or storing the feature vector in one column? Ideas are welcome. I am using the MAPI client to retrieve the data. Happy to provide more information if necessary Cheers, Bas Boom University of Edinburgh part of Fish4Knowledge consortium -- The University of Edinburgh is a charitable body, registered in Scotland, with registration number SC005336.

1 0

[MonetDB-users] charset problem with the odbc driver for windows
by liangmeng 18 Jun '12

18 Jun '12

hello, all, i installed monetdb in windows server 2008(64bit) , and installed the odbc for windows( 32bit intalled firstly, and 64bit the next), after insert some Chinese characters, i use oracle biee to show the table data, but biee can't show all the characters, just like below: 1��monetdb data showed in biee i also install mysql to test this, the database character set is utf8 too, and biee can show the data correctly: 2��mysql data showed in biee the data showed in command line tool are the same from monetdb and mysql monetdb command line tool return: sql>select * from test2; +------+ | nam | +======+ | 广州 | | 梅州 | +------+ 2 tuples (2.189ms) mysql commad line tool return: mysql> select * from test; +--------+ | nam | +--------+ | 广州 | | 梅州 | +--------+ 2 rows in set (0.00 sec) Does this mean that: the odbc driver of monetdb has some problems?

2 1

[MonetDB-users] monetdb--query is very slow on table about 1 billion rows
by liangmeng 16 Jun '12

16 Jun '12

hi, guys, i create a simple table , and the table is up to 1 billion rows, its structure as follow: create table test(Month_ID int not null, C_NAME varchar(10) not null, C_ID int not null, MSISDN varchar(15) not null, C_FLUX double not null); and the data looks like: “201201，name1, 123213, 13723213213, 12.12321" when i execute the query "select c_name,c_flux from test where c_id=**** order by c_flux desc", it takes more than 3 minute to get the result(more than 100 thousands rows returned, if limit 100 is used, the execute time is about 40s), is there any ways to optimize? Hardwares: intel xeon 2.12*8, 32G memory, 4*1T SATA(raid 0)

2 3

[MonetDB-users] How to see what query is running? kill long running queries?
by Majid Azimi 15 Jun '12

15 Jun '12

Hi guys, Is it possible to see what queries are running on the database? How to kill a query? I have a issued a SELECT * query and then looked at sys.querylog and sys.queryhistory, But they are empty. Is there any solution?

4 7

[MonetDB-users] RDF3x to MonetDB
by Hancel Gonzalez 15 Jun '12

15 Jun '12

Hi people, i have installed rdf-3x <http://www.mpi-inf.mpg.de/~neumann/rdf3x/>. This software have *buildmonetdb *that is for translate rdf queries to monetdb. The problem is that i dont know which arguments i have send in command line. I appreciate your help! Greettings, Hancel Gonzalez.

3 2

[MonetDB-users] monetdb do not release the memory
by liangmeng 15 Jun '12

15 Jun '12

hi, everyone i'am new here, i install monetdb of windows 64bit version, after copy into table from several very large files(about 10G each), i found that monetdb occupied too much momery, almost 100% of total 32G , and it keeps occupying, after 24hours, it still don't release the memory, what's the matter? Also, i found the same problem after query several times with large amount of records returned;

4 6

[MonetDB-users] how to find the foreign key relationships
by Molenaar, Gijs 14 Jun '12

14 Jun '12

Hi monetdb people, How do I find all foreign key relationships with MonetDB using SQL? I now have: "select idxs.name, tables.name from sys.tables, sys.idxs where tables.id = idxs.table_id and idxs.type=1;" But that doesn't give me the related table. Any suggestion? I can't find documentation of the sys tables on the website, does it exist? Thanks, -- Gijs Molenaar http://www.astro.uva.nl/people/gijs-molenaar/

3 7

[MonetDB-users] python-monetdb and pip
by Molenaar, Gijs 14 Jun '12

14 Jun '12

Hi Monetdb people, python-monetdb is now also in the pypi repo, which means you can easily install it with pip: $ pip install python-monetdb more info: http://pypi.python.org/pypi/python-monetdb/11.9.5 -- Gijs Molenaar http://www.astro.uva.nl/people/gijs-molenaar/

1 0

[MonetDB-users] python-monetdb and pip
by Molenaar, Gijs 13 Jun '12

13 Jun '12

Hi Monetdb people, python-monetdb is now also in the pypi repo, which means you can easily install it with pip: $ pip install python-monetdb -- Gijs Molenaar http://www.astro.uva.nl/people/gijs-molenaar/

1 1

Re: [MonetDB-users] hold cursors over commit in JDBC
by Majid Azimi 12 Jun '12

12 Jun '12

Let me ask it this way: How can we configure driver so it doesn't fetch all records at one time. for example 100 records per each fetch, So the application server doesn't get OutOfMemoryException. Thank you. >> hi guys, >> >> I have a very large table, which I should scan it and generate reports. My code is like this: >> >> Class.forName("nl.cwi.monetdb.jdbc.MonetDriver"); >> Connection conn = DriverManager.getConnection("jdbc:monetdb://localhost/online", "monetdb", "monetdb"); >> >> conn.setAutoCommit(false); >> conn.setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT); >> >> Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); >> stmt.setFetchSize(10); >> >> ResultSet rs = stmt.executeQuery("SELECT * FROM denorm"); >> >> while (rs.next()) { >> /* process line */ >> } >> >> conn.commit(); >> >> I do not use much ram inside while loop. I get OutOfMemeryException from JVM. >> So I think the driver ignores HOLD_CURSORS_OVER_COMMIT and fetches all records. Is there any solution? > HOLD_CURSORS_OVER_COMMIT means it keeps the cursor open, right? So what > else can the driver do but keep them open (including the memory > associated to that)? What would you have expected to happen with this > setting?

2 1

[MonetDB-users] hold cursors over commit in JDBC
by Majid Azimi 12 Jun '12

12 Jun '12

hi guys, I have a very large table, which I should scan it and generate reports. My code is like this: Class.forName("nl.cwi.monetdb.jdbc.MonetDriver"); Connection conn = DriverManager.getConnection("jdbc:monetdb://localhost/online", "monetdb", "monetdb"); conn.setAutoCommit(false); conn.setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT); Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(10); ResultSet rs = stmt.executeQuery("SELECT * FROM denorm"); while (rs.next()) { /* process line */ } conn.commit(); I do not use much ram inside while loop. I get OutOfMemeryException from JVM. So I think the driver ignores HOLD_CURSORS_OVER_COMMIT and fetches all records. Is there any solution?

2 1

[MonetDB-users] Setting up replication
by Majid Azimi 12 Jun '12

12 Jun '12

Hi guys, I', trying to configure replication on single node(different ports). What I did is: 1. I have created 2 dbfarms 2. set port=50000 for master node and set port=50001 for slave node 3. set control=yes for both 4. set passphrase=master for master node and set passphrase=slave for second node My master node has already data in it, I just changed the configuration. then: moentdb -p 50000 set master=yes maindb --> for master node monetdb -p 50001 set slave=mapi:monetdb://127.0.0.1:50000/maindb slavedb --> on slave node(slavedb is empty database) Now there is no data in slave node. Can anyone help?

2 1

[MonetDB-users] Server socket stays 'open' after crash
by Stefan de Konink 11 Jun '12

11 Jun '12

Instead of using monetdbd I am using a very simple while [ 1 ]; do monetdb; sleep 2; done command to restart the database after an assertion failed or segmentation fault. For some reason even after a crash MonetDB complains at the start of the application that the port is already in use. Would could -unix wise- be the reason for this, that even after a crash and the program has been terminated resources are still claimed for the terminated program? Stefan

2 3

[MonetDB-users] MonetDB Clustering
by Majid Azimi 11 Jun '12

11 Jun '12

Hi guys, What types of clustering does monetdb support? Sharding? Master/Slave? if it is Master/Slave does it support multi master? or all writes must go to master node? In clustering documentation (http://www.monetdb.org/Documentation/Cookbooks/Recipes/Cluster) there is paragraph: willem% monetdb -h cruquius -P mypassphraseset shared=pump/2/harvey pump willem% monetdb -h lijnden -P mypassphraseset shared=pump/2/fox pump willem% monetdb -h leeghwater -P mypassphraseset shared=pump/1/harvey pump What are those 1, 2, fox, harvey?

2 1

[MonetDB-users] monetdb clustering
by Majid Azimi 10 Jun '12

10 Jun '12

Hi guys, What types of clustering does monetdb support? Sharding? Master/Slave? if it is Master/Slave does it support multi master? or all writes must go to master node? In clustering documentation (http://www.monetdb.org/Documentation/Cookbooks/Recipes/Cluster) there is paragraph: willem% monetdb -h cruquius -P mypassphraseset shared=pump/2/harvey pump willem% monetdb -h lijnden -P mypassphraseset shared=pump/2/fox pump willem% monetdb -h leeghwater -P mypassphraseset shared=pump/1/harvey pump What are those 1, 2, fox, harvey?

1 0

[MonetDB-users] Install MonetDB in CentOS 5
by Hancel Gonzalez 08 Jun '12

08 Jun '12

Hi people, i'm new here and i hope your help. I wanna install MonetDB in a computer with CentOS 5: download rpm packages from http://code.google.com/p/monetdb-rhel-5/downloads/list but i not work. Then, i tried compile the sourcecode http://dev.monetdb.org/downloads/sources/Latest/MonetDB-11.9.5.zip, command ./configure work but when i 'make' i have error of compilation. I dont know what to do! Please help me! Greetings, Hancel Gonzalez.

2 1

[MonetDB-users] Djonet
by Gijs Molenaar 08 Jun '12

08 Jun '12

Hi, I've updated the MonetDB Django backend created by Mark Bucciarelli and renamed it to Djonet. https://github.com/gijzelaerr/djonet basic stuff is working, but many Django tests are still failing, which i'm working on. Still it is already quite usable. If people want to help make it better, let me know. -- Gijs Molenaar http://www.astro.uva.nl/people/gijs-molenaar/

5 5

[MonetDB-users] Any plan to support Ubuntu Server 12.04
by Majid Azimi 28 May '12

28 May '12

hi guys, Is there any plan to build binaries for Ubuntu server 12.04(Precise Pangolin)? I didn't find debs for this release from repository?

2 1

[MonetDB-users] Changing time zone after getting GMT time from database
by Majid Azimi 27 May '12

27 May '12

Hi guys, I have MonetDB database with table containing user table: CREATE TABLE user ( birth_date TIMESTAMP NOT NULL ); birth_date is saved in GMT without DST. (This is the default behavior of MonetDB). So I should change the TimeZone in my application. Here is my code: Class.forName("nl.cwi.monetdb.jdbc.MonetDriver"); Connectioncon =DriverManager.getConnection("jdbc:monetdb://localhost/online","monetdb","monetdb"); Statementst =con.createStatement(); ResultSetrs; Calendar c = Calendar.getInstance(TimeZone.getTimeZone("UTC")); rs =st.executeQuery("SELECT * FROM user"); while(rs.next()){ c.setTime(rs.getTimestamp("birth_date")); c.setTimeZone(TimeZone.getTimeZone("Asia/Tehran")); System.out.println(c.get(Calendar.YEAR)+"-"+c.get(Calendar.MONTH)+"-"+c.get(Calendar.DAY_OF_MONTH)+" "+c.get(Calendar.HOUR_OF_DAY)+":"+c.get(Calendar.MINUTE)+":"+c.get(Calendar.SECOND)); } But there is no difference between timestamps in database and timestamps after converting time zone in application. Could anyone help? Is this the wrong way to convert TimeZone? I'm using MonetDB version 11.9.5-20120516 on Debian 6 with openjdk 6. Here is the monetdbd getall /home/dbfarm: dbfarm /home/dbfarm/ status monetdbd[4187]1.6(Apr2012-SP1)is serving thisdbfarm mserver /usr/bin/mserver5 logfile /home/dbfarm//merovingian.log pidfile /home/dbfarm//merovingian.pid sockdir /tmp port 50000 exittimeout 60 forward proxy discovery yes discoveryttl 600 control yes passphrase {SHA512}ba3253876aed6bc22d4a6ff53d8406c6ad864195ed144ab5c87621b6c233b548baeae6956df346ec8c17f5ea10f35ee3cbc514797ed7ddd3145464e2a0bab413 mapisock /tmp/.s.monetdb.50000 controlsock /tmp/.s.merovingian.50000

2 1

[MonetDB-users] Tuning MonetDB
by Majid Azimi 27 May '12

27 May '12

Hi guys, As MonetDB does rely on OS features to be tuned, Which settings should we optimize on OS specially on Linux? 1. Tuning network a. using jumbo b. socket buffer space c. TCP setting 2. Tuning VM a. huge page b. swappiness 3. Tuning File system a. which file system? ext3? ext4? XFS? b. using large blocks if database is almost read only does using ext2 make any benefit?

2 1

[MonetDB-users] Announcement: New Apr2012-SP1 Bugfix release of MonetDB suite
by Fabian Groffen 25 May '12

25 May '12

The MonetDB team at CWI/MonetDB BV is pleased to announce the Apr2012-SP1 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at http://www.monetdb.org/. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>. Apr 2012-SP1 bugfix release Build Environment * Windows: we now use OpenSSL 1.0.1b. SQL * Databases that were upgraded from the Aug2011 release have an error in the catalog for SQL procedures. This is now fixed. Merovingian * Fixed a bug where connecting to a stopped multiplex-funnel would result in a 'there are no available connections' error. MonetDB Common * Implemented MT_getrss for Mac OS X systems, this allows the server to know about how much memory is currently in use. Bug Fixes * 2454: SQL: add various statistical functions * 2916: non admin user cannot create temporary tables * 2949: SQL exception during execution of a correct user defined function that just returns a relation table constructed by a simple SQL select query * 2958: Grant does't work with schema syntax * 2960: mclient terminates connection during insertion of a large number of records from a file * 2972: SQL URL functionality contains errors * 2987: Broken type inference when using sql round * 2992: SQL module truncates DECIMALS when passing integers to MAL module * 3000: Inconsistency between read-only and read-write modes after dropping a schema * 3028: Segmentation fault in seqscan_eq_bte_bte_tloc_void_void * 3046: mserver5 terminates with segmentation fault * 3063: datacell functions no longer work after restart of database * 3072: Database upgrade from Dec2011 to Apr2012 broken for storage() * 3073: VERY slow foreign key check (due to missing batcalc.isnil() ?) * 3074: Name resolution in procedures * 3077: No errors on UDF returning table with too many columns * 3079: Converting an Oracle schema DDL for MonetDB fails to create a column named "no".

1 0