Hi Stefan:
Thank you for your response.
We are using the JDBC driver that was released with the December 2011
release in both our BI tool and DBVisualizer.
Before I send you the information you requested, I can provide some other
information that may be useful. My staff looked at the Merovingian log to
see if there were any differences in the SQLs that are being received by
the database. And, we found that our BI tool is sending a simple SQL,
'SELECT 1', before other queries. I didn't see this in the BI log which
shows the SQL being sent. But, we are seeing it in the Merovingian log. So,
my suspicion is that our BI tool is sending the simple query to check for
the connection, but not recording it in its log.
My next question is whether 'SELECT 1;' has any significance for the
December release. We took sent 'SELECT 1;' then one of our BI generated SQL
statements in DBVisualizer to test if 'SELECT 1' has an impact. And, it
looks like that puts the MonetDB in a single thread mode or something
because we were able to duplicate the condition that results in only 1 cpu
working. 'SELECT 1' SQL statement does not seem to affect 2010 October SP1
release. Is it possible that 'SELECT 1;' has triggers something special in
the December release?
We also ran 'explain select 1' in the October 2010 SP1 and in the December
2011 environments. As seen below, MAL in December 2011 release does not
show sql.mvc()...... Could this possibly be a bug?
October 2010 SP1 Environment:
sql>explain select 1;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mal
|
+================================================================================================================================================================+
| function user.s1_7{autoCommit=true}():void;
|
| sql.mvc();
|
| sql.exportValue(1,".","single_value","tinyint",8,0,6,1:bte,"");
|
| end s1_7;
|
+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 tuples (20.292ms)
December 2011 Environment:
sql>explain select 1;
+-------------------------------------------------------------------------+
| mal |
+=========================================================================+
| function user.s2_3{autoCommit=true}():void; |
| sql.exportValue(1,".","single_value":str,"tinyint",8,0,6,1:bte,""); |
| end s2_3; |
+-------------------------------------------------------------------------+
3 tuples (10.632ms)
I was also told that the code optimize flag was not set as default and we
are recompiling the source now to see if that would make any difference.
Thank you for your assistance.
Best regards,
Henry
On Mon, Feb 13, 2012 at 1:47 AM, Stefan Manegold
Hi Henry,
sorry for the inconvenience. We are not aware of any changes that might trigger the phenomenon you describe. In fact, we did do quite some performance testing before releasing Dec2011, and found and fixed some performance problems that had sneaked in.
Having said that, do I understand correctly, that your SQL queries run fine (fast) both directly from your BI tool and via DBVisualizer when using MonetDB Oct2010-SP1, and also when run via DBVisualizer in MonetDB Dec2011, but run single-threaded and much slower when your BI tool sends the queries directly to MonetDB Dec2011?
How does your BI tool connect to MonetDB? ODBC, JDBC, plain MAPI, ?
To further analyze this issue, it would be great, if you could provide us with the following information:
- your database schema (e.g., as SQL DDL statements) - a representative sample query (in SQL) - the output of running (on your database) your query prefixed with PLAN, EXPLAIN & TRACE (one at a time), if possible both on MonetDB Oct2010-SP1 & MonetDB Dec2011.
Thanks!
Stefan
Hi:
We are testing Dec 2011 Release to upgrade from Oct 2010 SP1 release on CentOS 5.2 64-bit with 100+ GB memory. We have a BI tool that users use to generate various reports. We have access to the SQL statements that get generated by the BI tool and sent to the MonetDB. So, we know exactly the SQL statements that are being sent to MonetDB.
One very odd thing that we are seeing the December release is that the reports from the BI tool are very, very slow (x10 - x15 from Oct releasee). It is obvious why looking at the server resource use. It looks like only 1 cpu is being used for all the queries! However, if I take the SQL statements generated by the BI tool and run them in DBVisualizer, all 20 cpu cores are used and the results return quickly. I am at a loss as to why we are seeing such different performance from identical SQL statements...
Is there a configuration setting or something in the December release
On Sun, Feb 12, 2012 at 10:39:36PM -0800, Henry Addington wrote: that
we overlooked? Can you please help us troubleshoot? Thank you.
HS
------------------------------------------------------------------------------
Try before you buy = See our experts in action! The most comprehensive online learning library for Microsoft developers is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3, Metro Style Apps, more. Free future releases when you subscribe now! http://p.sf.net/sfu/learndevnow-dev2
_______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- | Stefan.Manegold @ CWI.nl | DB Architectures (INS1) | | http://CWI.nl/~manegold/ | Science Park 123 (L321) | | Tel.: +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
------------------------------------------------------------------------------ Try before you buy = See our experts in action! The most comprehensive online learning library for Microsoft developers is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3, Metro Style Apps, more. Free future releases when you subscribe now! http://p.sf.net/sfu/learndevnow-dev2 _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users