Hi, Today, we encountered this serious memory issue with MonetDB. *Data : *10 tables - each with ~600 columns - all tables are EMPTY (No rows). *Query : *One query that involves with 25 joins across all the 10 tables. *MonetDB version :* July2015-SP3 The above query is ran on the same MonetDB version with the same table structure with all tables empty on two different machines as follows. *Scenario 1 - Query ran on Macbook Pro * - RAM - 8 GB (with other processes like Chrome/Eclipse running in parallel) - Free disk space - ~100 GB - MonetDB July2015-SP3 downloaded as installable The query is ran. *Expected result* - return an empty set in a few seconds. *Actual result* - returns an empty set in ~5 minutes RAM - 8 GB utilised completely. Swap space used from disk - 10 GB This swap space is cleared after sometime automatically (around 5 mins) *Scenario 2 - With a CentOS virtual machine* - RAM - 128 GB - Free disk space - ~650 GB - MonetDB July2015-SP3 compiled from source code The query is ran. *Expected result* - return an empty set in a few seconds. *Actual result* - the query rebooted this high end CentOS machine. RAM - 128 GB utilised completely. After reboot, the swap memory was freed. I am puzzled with the following questions. 1. Why should a query that runs on 10 EMPTY tables require memory in GBs? 2. How did this query manage to run on a Mac with just 8 GB RAM, but crashed a machine with 128 GB RAM? Is this a known memory leak issue? Any help much appreciated. Thanks in advance. Regards, Vijay.
From my own experience, I would suspect the time is taken for creating / optimizing the MAL plan, not to execute it.
Try running the same query with EXPLAIN in front. This only produces the
MAL plan.
I expect this will give the same problems as running the query
Then try to replace EXPLAIN with PLAN. This only generates the algebra plan
and no MAL plan.
If this works as expected, then the problem is either in the MAL generation
itself, or in its optimization (more likely)
To investigate further the last case, you can try an EXPLAIN, but with this
first:
sql> set optimizer='minimal_pipe';
If this works well, then the problem is in one of the optimizers that is in
default_pipe but not in minimal_pipe.
On 29 March 2016 at 13:20, Vijay Krishna
Hi,
Today, we encountered this serious memory issue with MonetDB.
*Data : *10 tables - each with ~600 columns - all tables are EMPTY (No rows).
*Query : *One query that involves with 25 joins across all the 10 tables.
*MonetDB version :* July2015-SP3
The above query is ran on the same MonetDB version with the same table structure with all tables empty on two different machines as follows.
*Scenario 1 - Query ran on Macbook Pro *
- RAM - 8 GB (with other processes like Chrome/Eclipse running in parallel) - Free disk space - ~100 GB - MonetDB July2015-SP3 downloaded as installable
The query is ran. *Expected result* - return an empty set in a few seconds. *Actual result* - returns an empty set in ~5 minutes RAM - 8 GB utilised completely. Swap space used from disk - 10 GB This swap space is cleared after sometime automatically (around 5 mins)
*Scenario 2 - With a CentOS virtual machine*
- RAM - 128 GB - Free disk space - ~650 GB - MonetDB July2015-SP3 compiled from source code
The query is ran. *Expected result* - return an empty set in a few seconds. *Actual result* - the query rebooted this high end CentOS machine. RAM - 128 GB utilised completely. After reboot, the swap memory was freed.
I am puzzled with the following questions.
1. Why should a query that runs on 10 EMPTY tables require memory in GBs? 2. How did this query manage to run on a Mac with just 8 GB RAM, but crashed a machine with 128 GB RAM?
Is this a known memory leak issue?
Any help much appreciated. Thanks in advance.
Regards, Vijay.
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
HI, given that the problem appears to be worse on the bigger machines --- assuming it has not only more memory, but in particular more cores --- the problem might be related to generating and/or optimizing multi-threaded plans. In addition to Roberto's suggestions, you might want to test whether single-threaded execution works, either by starting the MonetDB server single-threaded (see the manpage(s) of monetdb or mserver5 for details), or by issuing one (or both; one at a time) of the following via mclient: sql> set optimizer='sequential_pipe'; sql> set optimizer='no_mitosis_pipe'; Further, for us to reproduce, debug, and potentially fix the problem, we'd need your database schema (SQL DDL) and query --- preferably via bug report at http://bugs.monetdb.org/ Best, Stefan ----- On Mar 29, 2016, at 1:31 PM, Roberto Cornacchia roberto.cornacchia@gmail.com wrote:
From my own experience, I would suspect the time is taken for creating / optimizing the MAL plan, not to execute it.
Try running the same query with EXPLAIN in front. This only produces the MAL plan. I expect this will give the same problems as running the query
Then try to replace EXPLAIN with PLAN. This only generates the algebra plan and no MAL plan. If this works as expected, then the problem is either in the MAL generation itself, or in its optimization (more likely)
To investigate further the last case, you can try an EXPLAIN, but with this first:
sql> set optimizer='minimal_pipe';
If this works well, then the problem is in one of the optimizers that is in default_pipe but not in minimal_pipe.
On 29 March 2016 at 13:20, Vijay Krishna < vijayakrishna55@gmail.com > wrote:
Hi,
Today, we encountered this serious memory issue with MonetDB.
Data : 10 tables - each with ~600 columns - all tables are EMPTY (No rows). Query : One query that involves with 25 joins across all the 10 tables.
MonetDB version : July2015-SP3
The above query is ran on the same MonetDB version with the same table structure with all tables empty on two different machines as follows.
Scenario 1 - Query ran on Macbook Pro
* RAM - 8 GB (with other processes like Chrome/Eclipse running in parallel) * Free disk space - ~100 GB * MonetDB July2015-SP3 downloaded as installable
The query is ran. Expected result - return an empty set in a few seconds. Actual result - returns an empty set in ~5 minutes RAM - 8 GB utilised completely. Swap space used from disk - 10 GB This swap space is cleared after sometime automatically (around 5 mins)
Scenario 2 - With a CentOS virtual machine
* RAM - 128 GB * Free disk space - ~650 GB * MonetDB July2015-SP3 compiled from source code
The query is ran. Expected result - return an empty set in a few seconds. Actual result - the query rebooted this high end CentOS machine. RAM - 128 GB utilised completely. After reboot, the swap memory was freed.
I am puzzled with the following questions.
1. Why should a query that runs on 10 EMPTY tables require memory in GBs? 2. How did this query manage to run on a Mac with just 8 GB RAM, but crashed a machine with 128 GB RAM? Is this a known memory leak issue?
Any help much appreciated. Thanks in advance.
Regards, Vijay.
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
Hello Stefan,
Thanks for the inputs.
I tried with all optpipes, but the query crashes the machine on all
instances, even with minimal_pipe.
I will also file a bug.
Thanks & Regards,
Vijayakrishna.P.
Mobile : (+91) 9500402305.
On Tue, Mar 29, 2016 at 8:16 PM, Stefan Manegold
HI,
given that the problem appears to be worse on the bigger machines --- assuming it has not only more memory, but in particular more cores --- the problem might be related to generating and/or optimizing multi-threaded plans.
In addition to Roberto's suggestions, you might want to test whether single-threaded execution works, either by starting the MonetDB server single-threaded (see the manpage(s) of monetdb or mserver5 for details), or by issuing one (or both; one at a time) of the following via mclient:
sql> set optimizer='sequential_pipe';
sql> set optimizer='no_mitosis_pipe';
Further, for us to reproduce, debug, and potentially fix the problem, we'd need your database schema (SQL DDL) and query --- preferably via bug report at http://bugs.monetdb.org/
Best, Stefan
----- On Mar 29, 2016, at 1:31 PM, Roberto Cornacchia roberto.cornacchia@gmail.com wrote:
From my own experience, I would suspect the time is taken for creating / optimizing the MAL plan, not to execute it.
Try running the same query with EXPLAIN in front. This only produces the MAL plan. I expect this will give the same problems as running the query
Then try to replace EXPLAIN with PLAN. This only generates the algebra plan and no MAL plan. If this works as expected, then the problem is either in the MAL generation itself, or in its optimization (more likely)
To investigate further the last case, you can try an EXPLAIN, but with this first:
sql> set optimizer='minimal_pipe';
If this works well, then the problem is in one of the optimizers that is in default_pipe but not in minimal_pipe.
On 29 March 2016 at 13:20, Vijay Krishna < vijayakrishna55@gmail.com > wrote:
Hi,
Today, we encountered this serious memory issue with MonetDB.
Data : 10 tables - each with ~600 columns - all tables are EMPTY (No rows). Query : One query that involves with 25 joins across all the 10 tables.
MonetDB version : July2015-SP3
The above query is ran on the same MonetDB version with the same table structure with all tables empty on two different machines as follows.
Scenario 1 - Query ran on Macbook Pro
* RAM - 8 GB (with other processes like Chrome/Eclipse running in parallel) * Free disk space - ~100 GB * MonetDB July2015-SP3 downloaded as installable
The query is ran. Expected result - return an empty set in a few seconds. Actual result - returns an empty set in ~5 minutes RAM - 8 GB utilised completely. Swap space used from disk - 10 GB This swap space is cleared after sometime automatically (around 5 mins)
Scenario 2 - With a CentOS virtual machine
* RAM - 128 GB * Free disk space - ~650 GB * MonetDB July2015-SP3 compiled from source code
The query is ran. Expected result - return an empty set in a few seconds. Actual result - the query rebooted this high end CentOS machine. RAM - 128 GB utilised completely. After reboot, the swap memory was freed.
I am puzzled with the following questions.
1. Why should a query that runs on 10 EMPTY tables require memory in GBs? 2. How did this query manage to run on a Mac with just 8 GB RAM, but crashed a machine with 128 GB RAM? Is this a known memory leak issue?
Any help much appreciated. Thanks in advance.
Regards, Vijay.
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
participants (3)
-
Roberto Cornacchia -
Stefan Manegold -
Vijay Krishna