users-list April 2012

users-list@monetdb.org

10 participants
8 discussions

[MonetDB-users] Different results with same query

by Matthieu Guamis

Hello, Running the same query on the same data leeds to different results between postgresql and monetdb. I have not been able to get a simple example to reproduce the problem. So here is what I observe: the query: select temps_mois.rfoperdmo as c1, sum((case when dwhinv.dwhinv___rfodomide = 'RH' and dwhinv.dwhinv___rfoindide = 'tech_rh_effectif_rem_reel' then dwhinv.dwhinvqte else 0 end)) as m0 from rfoper_temps_mois as temps_mois, dwhinv as dwhinv, rfovsn as rfovsn_0, rrhamv as rrhamv_1, rrhcov as rrhcov_2, rfoadv as rfoadv_3 where temps_mois.rfoper___rforefide = 'HPLUS' and dwhinv.dwhinv___rforefide = 'HPLUS' and dwhinv.dwhinv___rfodomide = 'RH' and dwhinv.dwhinv___rfoindide = 'tech_rh_effectif_rem_reel' and dwhinv.dwhinvdtd = temps_mois.rfoperdtd and temps_mois.rfoperyea = '2011' and rfovsn___rforefide = 'HPLUS' and dwhinv.dwhinv___rfovsnide = rfovsn_0.rfovsnide and rfovsn_0.rfovsnide = '201111_reel' and rrhamv_1.rrhamv___rrhvemide='GRACOR' AND rrhamv_1.rrhamvrvs=1 AND rrhamv_1.rrhamv___rforefide= 'HPLUS' and dwhinv.dwhinv___rrhempide = rrhamv_1.rrhamvinf and rrhamv_1.rrhamvsup = 'CEMP' and rrhcov_2.rrhcov___rrhvcoide='CONTRATS' AND rrhcov_2.rrhcovrvs=1 AND rrhcov_2.rrhcov___rforefide= 'HPLUS' and dwhinv.dwhinv___rrhcntide = rrhcov_2.rrhcovinf and rrhcov_2.rrhcovsup = 'CONTRATS' and rfoadv_3.rfoadv___rfovdeide='STRC' AND rfoadv_3.rfoadvrvs=1 AND rfoadv_3.rfoadv___rforefide= 'HPLUS' and dwhinv.dwhinv_p2rfodstide = rfoadv_3.rfoadvinf and rfoadv_3.rfoadvsup = 'HPLUS' group by c1 order by c1 Postgresql result: c1 | m0 ----+----------------- 01 | 7111.5376967750 02 | 7100.9108821426 03 | 7150.2597967742 04 | 7151.4283666667 05 | 7109.1641451610 06 | 6976.2108421239 07 | 6329.7404193564 08 | 6302.9823032247 09 | 6426.9459633351 10 | 6519.6889580648 11 | 6549.5235033402 12 | 6492.5477161292 (12 lignes) Monetdb result: +------+----------------------+ | c1 | m0 | +======+======================+ | 12 | 1.000000 | | 11 | 1.000000 | | 10 | 2.000000 | | 09 | 0.366667 | | 08 | 1.000000 | | 07 | 1.000000 | | 06 | 1.000000 | | 05 | 3.000000 | | 04 | 4.000000 | | 03 | 15.000000 | | 02 | 9.964286 | | 01 | 1.000000 | +------+----------------------+ 12 tuples (212.346ms) As you see, all "m0" values are very different. I have no idea of what is causing this but if I modify the query in order to simplify the CASE...WHEN...ELSE part of the select, it produce the expected result: replacing sum((case when dwhinv.dwhinv___rfodomide = 'RH' and dwhinv.dwhinv___rfoindide = 'tech_rh_effectif_rem_reel' then dwhinv.dwhinvqte else 0 end)) as m0 by sum((case when dwhinv.dwhinv___rfoindide = 'tech_rh_effectif_rem_reel' then dwhinv.dwhinvqte else 0 end)) as m0 Its not exactly the same query but it must leeds to the same result due to my test datas (its the case in postgres) I join to this email two files with the TRACES, respectively for the request with bad/correct result (ko.txt/ok.txt) Config: Ubuntu Server 11.04 x64 MonetDB v11.7.9 (Dec2011-SP2), MonetDB Database Server v1.6 (Dec2011-SP2), MonetDB Database Server Toolkit v1.0 (Dec2011-SP2) Thank you very much for your help!! -- *Matthieu Guamis* *Logo Axège <http://www.axege.com/>* /Axège// 23,rue Saint-Simon 63000 Clermont-Ferrand/ Tél: +33 (0)4 63 05 95 40 Fax: +33 (0)4.73.70.65.29 Email: matthieu.guamis(a)axege.com <mailto:matthieu.guamis@axege.com>

9 years, 8 months

[MonetDB-users] Bad SELECT performance after deletion of 1 record

by suncpp

Hi everyone, I had added about 30,000,000 records in one MonetDB table. The performance of executing SELECT-queries was really great. But after the deletion of only 1 record from the table, the performance had slowed down considerably. I have seen the topics here with alike problem but I have not found the solution. Could anyone help me to understand what the problem is? -- View this message in context: http://old.nabble.com/Bad-SELECT-performance-after-deletion-of-1-record-tp3… Sent from the monetdb-users mailing list archive at Nabble.com.

10 years, 2 months

[MonetDB-users] Announcement: New Apr2012 Feature release of MonetDB suite

by Sjoerd Mullender

The MonetDB team at CWI/MonetDB BV is pleased to announce the Apr2012 feature 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 feature release Build Environment * Fixed some of the package names for Debian/Ubuntu. Packages for libraries should contain the major number of the library version number. This was not always the case. * On Debian and Ubuntu, install Python modules in dist-packages instead of site-packages. This fixed bug 2997. SQL * Fixed a crash that happened at the end of a database upgrade to the Dec2011 database scheme. The crash happened during cleanup after the database was upgraded, so it was merely inconvenient. * Stripped off implementation-specific parts from error messages before they get presented to the user. Java Module * Password reading by JdbcClient no longer results in strange artifacts * JdbcClient now returns exit code 1 in case of failures * Implemented missing Number types support in PreparedStatement.setObject() * Fixed bug in DatabaseMetaData.getSchemas() method that caused an SQL error when called with catalog and schema argument. * Resolved a bug where JDBC and Control connections could terminate abruptly with 'Connection closed' messages Client Package * ODBC: Implemented the SQL_ATTR_CONNECTION_TIMEOUT attribute. * mclient now has a -a (--autocommit) option to turn off autocommit mode. MonetDB5 Server * The type "chr" has been removed. chr has long been superseded by bte for 1 byte arithmetic plus it is pretty useless to hold single characters since we use Unicode and thus only a tiny subset of the supported character set would fit. * Fixed potential crash by dealing better with non-standard types. * A couple of memory leaks have been plugged. Merovingian * The logfile and pidfile monetdbd properties are now displayed with dbfarm path when relative * Fixed a bug in the multiplex-funnel where certain clients would abort on responses for update queries. * Fixed problem where version and mserver properties for monetdbd were not always successfully retrieved. Bug #2982. * Fixed problem where shutdown of monetdbd would lead to shutting down database 'control' which does not exist. Bug #2983. * Fixed issue causing (harmless) 'error reading from control channel' messages. Bug #2984. * Resolved problem where remote start/stop/etc. commands with monetdb would report error 'OK'. Bug #2984. MonetDB Common * The type "chr" has been removed. chr has long been superseded by bte for 1 byte arithmetic plus it is pretty useless to hold single characters since we use Unicode and thus only a tiny subset of the supported character set would fit. Bug Fixes

10 years, 3 months

Re: [MonetDB-users] Strange things...

by Alexey Mints

Hi Stefan! condition1 is like this: exists (select 1 from temp_assoc_counts tc where tc.xtrsrc_id = temp_associations.xtrsrc_id and tc.assoc_count = 1) condition 2 is like: kind is null I have also bumped in the same situation, when condition2 was like "kind = 2". But when "2" was replaced to a column name (with all values in this column were 2) - it worked fine. I.e. there is something wrong when constant appears in the condition. Traces can be seen in attachment.

10 years, 3 months

[MonetDB-users] Strange things...

by Alexey Mints

Hello! I have a strange behavior of my MonetDB database. After some actions I run into situation, when queries select * from table1 where {condition1}; and select * from table1 where {condition2}; return the same results (same rows), but select * from table1 where {condition1} and {condition2}; is empty, which is definitely wrong. I cannot see any problems from SQL-side. I am still struggling to get a minimal code producing this bug. Even worse - it is not always occurring under the same conditions... How can I get more information on what's happened? Alexey

10 years, 3 months

[MonetDB-users] Performance Degredation

by Joseph Brower

We discussed this a little bit before, but I was called away. Now that I have some time, I'm hoping to discuss this again and hopefully find resolution. Normally our MonetDB instance is running happily and doesn't have any problems. We've limited the number of queries being performed concurrently to the number of cores in the machine, and we do all of our inserts using COPY INTO statements. The problem is that, for no reason that we can see, suddenly MonetDB starts to perform incredibly slowly. This causes queries to stop returning, or, what used to return after less than a second starts to take over 30 seconds, and causes a timeout on our system that produces the graphs based on this information. We restart MonetDB and the problem goes away for a while. Sometimes it drops again after a short time (as little as a few hours) other times it will run fine for a few days. Does anyone have any ideas? What other information can I provide to get a closer look at why it is behaving this way? Thanks, Joseph Brower

10 years, 3 months

[MonetDB-users] MonetDB driver for node.js?

by Robert Kosara

Hello, I am looking for a better database for a project where I am dealing with large numbers of data points, and MonetDB seems like a really interesting choice for that. This is for a web application that is written in JavaScript/node.js, though, so I'm wondering if anybody might be working on a MonetDB driver for node. I know that your focus is on scientific data, and JavaScript might not be a big factor there, but node is gaining a lot of momentum for web applications. Also, is there a way to search the mailing list archive? I can't seem to find a way to do that on the sourceforge website. Best, Robert

10 years, 3 months

[MonetDB-users] Funnel

by Joseph Brower

We're looking at setting up the funnel. We've got 2 database servers that we're planning on using, and we simply want to balance the requests between the two. We don't want any of the multiplexing to take effect. I'm not seeing much as far as guides or how to set this up in the man pages. Does anyone have any good pointers on how to set things up this way? Thanks, Joseph Brower

10 years, 3 months

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

2008

2007

2006

2005

2004

users-list April 2012