[MonetDB-users] Odd Single Thread Only Processing in Dec 2011 Release
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 that we overlooked? Can you please help us troubleshoot? Thank you.
HS
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
On Sun, Feb 12, 2012 at 10:39:36PM -0800, Henry Addington wrote:
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 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
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 Stefan.Manegold@cwi.nlwrote:
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
On Sun, Feb 12, 2012 at 10:39:36PM -0800, Henry Addington wrote:
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
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
Stefan:
Recompiling the December release did not help. And, we are sure that sending 'SELECT 1;' to the December release definitely causes MonetDB to a crawl. We have a work-around for this issue in our system. But, I just wanted to let you know that we were able to duplicate the problem very easily with SELECT 1, SELECT '1', SELECT 2 - 1, and even SELECT 2...... Thank you.
Best regards,
Henry
On Mon, Feb 13, 2012 at 3:22 PM, Henry Addington hs.addington@gmail.comwrote:
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 Stefan.Manegold@cwi.nlwrote:
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
On Sun, Feb 12, 2012 at 10:39:36PM -0800, Henry Addington wrote:
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
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
participants (2)
-
Henry Addington -
Stefan Manegold