Hey Hannes, I won't be able to share the code I am working against but I will try to create a new stand-alone application which can replicate the issue. I'll try to share it as soon as possible. Also, in your earlier comments you mentioned that disk space does fill up for a small period of time. Is that a desired behavior? What if the connection pool stays active for long period and that causes disk space to fill up? I think this could be the scenario in my case Anu -----Original Message----- From: developers-list [mailto:developers-list-bounces+anu.srivastava=rms.com@monetdb.org] On Behalf Of Hannes Mühleisen Sent: Monday, July 20, 2015 11:19 PM To: Communication channel for developers of the MonetDB suite. Subject: Re: MonetDB causing disk space to fill up Hey Anu, we need more than a snippet of Java code to properly debug this. Schema, sample data, queries, runnable stand-alone Java source at the least. Hannes

On 20.07.2015, at 23:28, Anu Raj Srivastava wrote:

I am sorry didn¹t realize mailing list is not their in my reply.

On 7/20/15, 14:24, "Ying Zhang" wrote:

...

Hello Anu,

Would you please post this to the MonetDB users mailing list? Thanks!

Regards,

Jennie

...

On Jul 20, 2015, at 19:44, Anu Raj Srivastava wrote:

Hi Hannes,

I am using apache dbcp2 connection Pool. I am running queries in a constant loop with 8 concurrent invocations. And each 8 threads are running 9 queries each again and again.

Java code is quite straight forward:-

private ConnectionPoolPG() { try { final Properties properties = new Properties();

properties.load(ConnectionPoolPG.class.getClassLoader().getResourceA sStre am("monetDB.properties")); dataSource = BasicDataSourceFactory.createDataSource(properties); schemaName = properties.getProperty("schemaname"); } catch (Exception e) { throw new ExposureStoreException("Unable to instantiate connection pool: " + e.getMessage(), EAPAException.ERR_STORE_CONNECTION_CONFIG_ERROR); } }

public void AllQueryPerformance() throws Exception { while (true) { if (queries != null) { for (String query : queries) { try (Connection connection = ConnectionPoolPG.getInstance().getConnection()) { try (Statement statement = connection.createStatement()) { statement.execute(query); } } } } } }

-----Original Message----- From: Hannes Mühleisen [mailto:Hannes.Muehleisen@cwi.nl] Sent: Sunday, July 19, 2015 4:47 AM To: Communication channel for developers of the MonetDB suite. Cc: Ying Zhang; Anu Raj Srivastava Subject: Re: MonetDB causing disk space to fill up

...

On 19 Jul 2015, at 13:23, Hannes Mühleisen wrote:

Hi,

...

On 16 Jul 2015, at 23:11, Ying Zhang wrote:

On Jul 13, 2015, at 22:32, Anu Raj Srivastava wrote:

...

Hey Jennie,

I did upgrade to latest version but I am facing the same issue. Further debugging into the issue it seems like it is ConnectionPool issue. When I use new connection for each query MonetDB seems to be able to clean up the resources

Ok, MonetDB is doing what it¹s supposed to do.

...

but if I use ConnectionPool for some reason DiskSpace keep filling up.

If a connection is kept open, I¹d expect MonetDB to be less eager to clean up resources. However, we generally recommend using a ConnectionPool, to avoid too much overhead caused by making a new connection for every query. So my suggestion for now would be to keep using the ConnectionPool, but try to find the reason why your disk is filled up.

...

Do you have any idea why that could happen?

Hmmm, no. We¹ve fixed quite some leaks in Jul2015... Some more thinking: - After how many iterations of repeatedly executing the queries is your disk filled up? - Can you please try to run your queries with mclient to observe the behaviour of MonetDB (and disk usage)?

@Hannes: sorry to disturb you during holiday. But if possible, would you please have a look at this thread. Does anything ring a bell? Thanks!

I tested this with ac3p0 connection pool (limited to 4 concurrent connections) and repeating queries from 10 threads (SELECT MIN(something) FROM Š). I was not able to observe any (!) increase in storage footprint. So without a minimal reproducible example of Java code + DB schema + sample data, it is hard to tell what is going on. Is this running in auto commit? Using temporary tables?

Small follow-up: When forcing the allocation of a large intermediate (SELECT MIN(something+1) FROM Š), additional storage space is indeed claimed. The attached plot shows dbfarm size over time while 10 threads are running 1000 queries each using a 5-connection pool. You can see how the amount of additional storage drops back to exactly where it was once the pool shuts down at around 350s, and that there is no accumulation of additional storage over the course of the pool being active.

Hannes

_______________________________________________ developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list

_______________________________________________ developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list

Hi Hannes, I have uploaded a sample project on github https://github.com/anu-srivastava/MonetDB_Test. You should be able to replicate the problem. Anu On 7/22/15, 0:36, "Hannes Mühleisen" wrote:

Hi,

...

On 21.07.2015, at 20:27, Anu Raj Srivastava wrote: I won't be able to share the code I am working against but I will try to create a new stand-alone application which can replicate the issue. I'll try to share it as soon as possible. OK.

...

Also, in your earlier comments you mentioned that disk space does fill up for a small period of time. Is that a desired behavior? What if the connection pool stays active for long period and that causes disk space to fill up? I think this could be the scenario in my case This is expected behavior. MonetDB puts intermediate results into memory-mapped files. For these, some space is required. These files are removed once the query is finished (or should be). This has nothing to do with connections.

Hannes

...

-----Original Message----- From: developers-list [mailto:developers-list-bounces+anu.srivastava=rms.com@monetdb.org] On Behalf Of Hannes Mühleisen Sent: Monday, July 20, 2015 11:19 PM To: Communication channel for developers of the MonetDB suite. Subject: Re: MonetDB causing disk space to fill up

Hey Anu,

we need more than a snippet of Java code to properly debug this. Schema, sample data, queries, runnable stand-alone Java source at the least.

Hannes

...

On 20.07.2015, at 23:28, Anu Raj Srivastava wrote:

I am sorry didn¹t realize mailing list is not their in my reply.

On 7/20/15, 14:24, "Ying Zhang" wrote:

...

Hello Anu,

Would you please post this to the MonetDB users mailing list? Thanks!

Regards,

Jennie

...

On Jul 20, 2015, at 19:44, Anu Raj Srivastava wrote:

Hi Hannes,

I am using apache dbcp2 connection Pool. I am running queries in a constant loop with 8 concurrent invocations. And each 8 threads are running 9 queries each again and again.

Java code is quite straight forward:-

private ConnectionPoolPG() { try { final Properties properties = new Properties();

properties.load(ConnectionPoolPG.class.getClassLoader().getResourceA sStre am("monetDB.properties")); dataSource = BasicDataSourceFactory.createDataSource(properties); schemaName = properties.getProperty("schemaname"); } catch (Exception e) { throw new ExposureStoreException("Unable to instantiate connection pool: " + e.getMessage(), EAPAException.ERR_STORE_CONNECTION_CONFIG_ERROR); } }

public void AllQueryPerformance() throws Exception { while (true) { if (queries != null) { for (String query : queries) { try (Connection connection = ConnectionPoolPG.getInstance().getConnection()) { try (Statement statement = connection.createStatement()) { statement.execute(query); } } } } } }

-----Original Message----- From: Hannes Mühleisen [mailto:Hannes.Muehleisen@cwi.nl] Sent: Sunday, July 19, 2015 4:47 AM To: Communication channel for developers of the MonetDB suite. Cc: Ying Zhang; Anu Raj Srivastava Subject: Re: MonetDB causing disk space to fill up

...

On 19 Jul 2015, at 13:23, Hannes Mühleisen wrote:

Hi,

> On 16 Jul 2015, at 23:11, Ying Zhang wrote: > > On Jul 13, 2015, at 22:32, Anu Raj Srivastava > wrote: >> >> Hey Jennie, >> >> I did upgrade to latest version but I am facing the same issue. >> Further debugging into the issue it seems like it is >> ConnectionPool issue. When I use new connection for each query >> MonetDB seems to be able to clean up the resources > > Ok, MonetDB is doing what it¹s supposed to do. > >> but if I use ConnectionPool for some reason DiskSpace keep >> filling up. > > If a connection is kept open, I¹d expect MonetDB to be less eager > to clean up resources. > However, we generally recommend using a ConnectionPool, to avoid > too much overhead caused by making a new connection for every > query. So my suggestion for now would be to keep using the > ConnectionPool, but try to find the reason why your disk is filled >up. > >> Do you have any idea why that could happen? > > Hmmm, no. We¹ve fixed quite some leaks in Jul2015... > Some more thinking: > - After how many iterations of repeatedly executing the queries is > your disk filled up? > - Can you please try to run your queries with mclient to observe > the behaviour of MonetDB (and disk usage)? > > @Hannes: sorry to disturb you during holiday. But if possible, > would you please have a look at this thread. Does anything ring a >bell? > Thanks!

I tested this with ac3p0 connection pool (limited to 4 concurrent connections) and repeating queries from 10 threads (SELECT MIN(something) FROM Š). I was not able to observe any (!) increase in storage footprint. So without a minimal reproducible example of Java code + DB schema + sample data, it is hard to tell what is going on. Is this running in auto commit? Using temporary tables?

Small follow-up: When forcing the allocation of a large intermediate (SELECT MIN(something+1) FROM Š), additional storage space is indeed claimed. The attached plot shows dbfarm size over time while 10 threads are running 1000 queries each using a 5-connection pool. You can see how the amount of additional storage drops back to exactly where it was once the pool shuts down at around 350s, and that there is no accumulation of additional storage over the course of the pool being active.

Hannes

_______________________________________________ developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list

_______________________________________________ developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list _______________________________________________ developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list

Not yet, sorry.

On 10 Aug 2015, at 22:33, Anu Raj Srivastava wrote:

Hi Hannes,

Just wanted to check if you got a chance to look into the issue?

Anu

On 7/31/15, 17:33, "developers-list on behalf of Anu Raj Srivastava" wrote:

...

Hi Hannes,

I have uploaded a sample project on github https://github.com/anu-srivastava/MonetDB_Test.

You should be able to replicate the problem.

Anu

On 7/22/15, 0:36, "Hannes Mühleisen" wrote:

...

Hi,

...

On 21.07.2015, at 20:27, Anu Raj Srivastava wrote: I won't be able to share the code I am working against but I will try to create a new stand-alone application which can replicate the issue. I'll try to share it as soon as possible. OK.

...

Also, in your earlier comments you mentioned that disk space does fill up for a small period of time. Is that a desired behavior? What if the connection pool stays active for long period and that causes disk space to fill up? I think this could be the scenario in my case This is expected behavior. MonetDB puts intermediate results into memory-mapped files. For these, some space is required. These files are removed once the query is finished (or should be). This has nothing to do with connections.

Hannes

...

-----Original Message----- From: developers-list [mailto:developers-list-bounces+anu.srivastava=rms.com@monetdb.org] On Behalf Of Hannes Mühleisen Sent: Monday, July 20, 2015 11:19 PM To: Communication channel for developers of the MonetDB suite. Subject: Re: MonetDB causing disk space to fill up

Hey Anu,

we need more than a snippet of Java code to properly debug this. Schema, sample data, queries, runnable stand-alone Java source at the least.

Hannes

...

On 20.07.2015, at 23:28, Anu Raj Srivastava wrote:

I am sorry didn¹t realize mailing list is not their in my reply.

On 7/20/15, 14:24, "Ying Zhang" wrote:

...

Hello Anu,

Would you please post this to the MonetDB users mailing list? Thanks!

Regards,

Jennie

> On Jul 20, 2015, at 19:44, Anu Raj Srivastava > > wrote: > > Hi Hannes, > > I am using apache dbcp2 connection Pool. I am running queries in a > constant loop with 8 concurrent invocations. And each 8 threads are > running 9 queries each again and again. > > Java code is quite straight forward:- > > private ConnectionPoolPG() { > try { > final Properties properties = new Properties(); > > properties.load(ConnectionPoolPG.class.getClassLoader().getResourceA > sStre > am("monetDB.properties")); > dataSource = > BasicDataSourceFactory.createDataSource(properties); > schemaName = properties.getProperty("schemaname"); > } catch (Exception e) { > throw new ExposureStoreException("Unable to instantiate > connection pool: " + e.getMessage(), > EAPAException.ERR_STORE_CONNECTION_CONFIG_ERROR); > } > } > > public void AllQueryPerformance() throws Exception { > while (true) { > if (queries != null) { > for (String query : queries) { > try (Connection connection = > ConnectionPoolPG.getInstance().getConnection()) { > try (Statement statement = > connection.createStatement()) { > statement.execute(query); > } > } > } > } > } > } > > -----Original Message----- > From: Hannes Mühleisen [mailto:Hannes.Muehleisen@cwi.nl] > Sent: Sunday, July 19, 2015 4:47 AM > To: Communication channel for developers of the MonetDB suite. > Cc: Ying Zhang; Anu Raj Srivastava > Subject: Re: MonetDB causing disk space to fill up > > >> On 19 Jul 2015, at 13:23, Hannes Mühleisen >> >> wrote: >> >> Hi, >> >>> On 16 Jul 2015, at 23:11, Ying Zhang wrote: >>> >>> On Jul 13, 2015, at 22:32, Anu Raj Srivastava >>> wrote: >>>> >>>> Hey Jennie, >>>> >>>> I did upgrade to latest version but I am facing the same issue. >>>> Further debugging into the issue it seems like it is >>>> ConnectionPool issue. When I use new connection for each query >>>> MonetDB seems to be able to clean up the resources >>> >>> Ok, MonetDB is doing what it¹s supposed to do. >>> >>>> but if I use ConnectionPool for some reason DiskSpace keep >>>> filling up. >>> >>> If a connection is kept open, I¹d expect MonetDB to be less eager >>> to clean up resources. >>> However, we generally recommend using a ConnectionPool, to avoid >>> too much overhead caused by making a new connection for every >>> query. So my suggestion for now would be to keep using the >>> ConnectionPool, but try to find the reason why your disk is filled >>> up. >>> >>>> Do you have any idea why that could happen? >>> >>> Hmmm, no. We¹ve fixed quite some leaks in Jul2015... >>> Some more thinking: >>> - After how many iterations of repeatedly executing the queries is >>> your disk filled up? >>> - Can you please try to run your queries with mclient to observe >>> the behaviour of MonetDB (and disk usage)? >>> >>> @Hannes: sorry to disturb you during holiday. But if possible, >>> would you please have a look at this thread. Does anything ring a >>> bell? >>> Thanks! >> >> I tested this with ac3p0 connection pool (limited to 4 concurrent >> connections) and repeating queries from 10 threads (SELECT >> MIN(something) FROM Š). I was not able to observe any (!) increase >> in storage footprint. So without a minimal reproducible example of >> Java code + DB schema + sample data, it is hard to tell what is >> going on. Is this running in auto commit? Using temporary tables? > > Small follow-up: When forcing the allocation of a large intermediate > (SELECT MIN(something+1) FROM Š), additional storage space is indeed > claimed. The attached plot shows dbfarm size over time while 10 > threads are running 1000 queries each using a 5-connection pool. You > can see how the amount of additional storage drops back to exactly > where it was once the pool shuts down at around 350s, and that there > is no accumulation of additional storage over the course of the pool > being active. > > Hannes >

_______________________________________________ developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list

_______________________________________________ developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list _______________________________________________ developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list

_______________________________________________ developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list

https://www.monetdb.org/bugzilla/show_bug.cgi?id=3784

On 11 Aug 2015, at 12:02, Hannes Mühleisen wrote:

I checked on this, your test program triggers an assertion in mserver5. Will investigate.

...

On 11 Aug 2015, at 10:49, Hannes Mühleisen wrote:

Not yet, sorry.

...

On 10 Aug 2015, at 22:33, Anu Raj Srivastava wrote:

Hi Hannes,

Just wanted to check if you got a chance to look into the issue?

Anu

On 7/31/15, 17:33, "developers-list on behalf of Anu Raj Srivastava" wrote:

...

Hi Hannes,

I have uploaded a sample project on github https://github.com/anu-srivastava/MonetDB_Test.

You should be able to replicate the problem.

Anu

On 7/22/15, 0:36, "Hannes Mühleisen" wrote:

...

Hi,

...

On 21.07.2015, at 20:27, Anu Raj Srivastava wrote: I won't be able to share the code I am working against but I will try to create a new stand-alone application which can replicate the issue. I'll try to share it as soon as possible. OK.

...

Also, in your earlier comments you mentioned that disk space does fill up for a small period of time. Is that a desired behavior? What if the connection pool stays active for long period and that causes disk space to fill up? I think this could be the scenario in my case This is expected behavior. MonetDB puts intermediate results into memory-mapped files. For these, some space is required. These files are removed once the query is finished (or should be). This has nothing to do with connections.

Hannes

...

-----Original Message----- From: developers-list [mailto:developers-list-bounces+anu.srivastava=rms.com@monetdb.org] On Behalf Of Hannes Mühleisen Sent: Monday, July 20, 2015 11:19 PM To: Communication channel for developers of the MonetDB suite. Subject: Re: MonetDB causing disk space to fill up

Hey Anu,

we need more than a snippet of Java code to properly debug this. Schema, sample data, queries, runnable stand-alone Java source at the least.

Hannes

> On 20.07.2015, at 23:28, Anu Raj Srivastava > wrote: > > I am sorry didn¹t realize mailing list is not their in my reply. > > On 7/20/15, 14:24, "Ying Zhang" wrote: > >> Hello Anu, >> >> Would you please post this to the MonetDB users mailing list? >> Thanks! >> >> Regards, >> >> Jennie >> >>> On Jul 20, 2015, at 19:44, Anu Raj Srivastava >>> >>> wrote: >>> >>> Hi Hannes, >>> >>> I am using apache dbcp2 connection Pool. I am running queries in a >>> constant loop with 8 concurrent invocations. And each 8 threads are >>> running 9 queries each again and again. >>> >>> Java code is quite straight forward:- >>> >>> private ConnectionPoolPG() { >>> try { >>> final Properties properties = new Properties(); >>> >>> properties.load(ConnectionPoolPG.class.getClassLoader().getResourceA >>> sStre >>> am("monetDB.properties")); >>> dataSource = >>> BasicDataSourceFactory.createDataSource(properties); >>> schemaName = properties.getProperty("schemaname"); >>> } catch (Exception e) { >>> throw new ExposureStoreException("Unable to instantiate >>> connection pool: " + e.getMessage(), >>> EAPAException.ERR_STORE_CONNECTION_CONFIG_ERROR); >>> } >>> } >>> >>> public void AllQueryPerformance() throws Exception { >>> while (true) { >>> if (queries != null) { >>> for (String query : queries) { >>> try (Connection connection = >>> ConnectionPoolPG.getInstance().getConnection()) { >>> try (Statement statement = >>> connection.createStatement()) { >>> statement.execute(query); >>> } >>> } >>> } >>> } >>> } >>> } >>> >>> -----Original Message----- >>> From: Hannes Mühleisen [mailto:Hannes.Muehleisen@cwi.nl] >>> Sent: Sunday, July 19, 2015 4:47 AM >>> To: Communication channel for developers of the MonetDB suite. >>> Cc: Ying Zhang; Anu Raj Srivastava >>> Subject: Re: MonetDB causing disk space to fill up >>> >>> >>>> On 19 Jul 2015, at 13:23, Hannes Mühleisen >>>> >>>> wrote: >>>> >>>> Hi, >>>> >>>>> On 16 Jul 2015, at 23:11, Ying Zhang wrote: >>>>> >>>>> On Jul 13, 2015, at 22:32, Anu Raj Srivastava >>>>> wrote: >>>>>> >>>>>> Hey Jennie, >>>>>> >>>>>> I did upgrade to latest version but I am facing the same issue. >>>>>> Further debugging into the issue it seems like it is >>>>>> ConnectionPool issue. When I use new connection for each query >>>>>> MonetDB seems to be able to clean up the resources >>>>> >>>>> Ok, MonetDB is doing what it¹s supposed to do. >>>>> >>>>>> but if I use ConnectionPool for some reason DiskSpace keep >>>>>> filling up. >>>>> >>>>> If a connection is kept open, I¹d expect MonetDB to be less eager >>>>> to clean up resources. >>>>> However, we generally recommend using a ConnectionPool, to avoid >>>>> too much overhead caused by making a new connection for every >>>>> query. So my suggestion for now would be to keep using the >>>>> ConnectionPool, but try to find the reason why your disk is filled >>>>> up. >>>>> >>>>>> Do you have any idea why that could happen? >>>>> >>>>> Hmmm, no. We¹ve fixed quite some leaks in Jul2015... >>>>> Some more thinking: >>>>> - After how many iterations of repeatedly executing the queries is >>>>> your disk filled up? >>>>> - Can you please try to run your queries with mclient to observe >>>>> the behaviour of MonetDB (and disk usage)? >>>>> >>>>> @Hannes: sorry to disturb you during holiday. But if possible, >>>>> would you please have a look at this thread. Does anything ring a >>>>> bell? >>>>> Thanks! >>>> >>>> I tested this with ac3p0 connection pool (limited to 4 concurrent >>>> connections) and repeating queries from 10 threads (SELECT >>>> MIN(something) FROM Š). I was not able to observe any (!) increase >>>> in storage footprint. So without a minimal reproducible example of >>>> Java code + DB schema + sample data, it is hard to tell what is >>>> going on. Is this running in auto commit? Using temporary tables? >>> >>> Small follow-up: When forcing the allocation of a large intermediate >>> (SELECT MIN(something+1) FROM Š), additional storage space is indeed >>> claimed. The attached plot shows dbfarm size over time while 10 >>> threads are running 1000 queries each using a 5-connection pool. You >>> can see how the amount of additional storage drops back to exactly >>> where it was once the pool shuts down at around 350s, and that there >>> is no accumulation of additional storage over the course of the pool >>> being active. >>> >>> Hannes >>> >> > > _______________________________________________ > developers-list mailing list > developers-list@monetdb.org > https://www.monetdb.org/mailman/listinfo/developers-list

_______________________________________________ developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list _______________________________________________ developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list

_______________________________________________ developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list

_______________________________________________ developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list

_______________________________________________ developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list

Hi Hannes, Thanks for looking into it. I did try a different connection pool so I am not sure whether it is connection pool issue or not. Regards, Anu -----Original Message----- From: Hannes Mühleisen [mailto:hannes.muehleisen@cwi.nl] Sent: Tuesday, August 11, 2015 8:07 AM To: Anu Raj Srivastava Cc: Communication channel for developers of the MonetDB suite. Subject: Re: MonetDB causing disk space to fill up Confirmed, you test program does make the disk fill up. This should not happen, it could be the connection pool not properly releasing resources (such as Statement or ResultSet instances) or a bug in MonetDB itself. Will investigate further. Hannes

On 11 Aug 2015, at 12:19, Hannes Mühleisen wrote:

https://www.monetdb.org/bugzilla/show_bug.cgi?id=3784

...

On 11 Aug 2015, at 12:02, Hannes Mühleisen wrote:

I checked on this, your test program triggers an assertion in mserver5. Will investigate.

...

On 11 Aug 2015, at 10:49, Hannes Mühleisen wrote:

Not yet, sorry.

...

On 10 Aug 2015, at 22:33, Anu Raj Srivastava wrote:

Hi Hannes,

Just wanted to check if you got a chance to look into the issue?

Anu

On 7/31/15, 17:33, "developers-list on behalf of Anu Raj Srivastava" wrote:

...

Hi Hannes,

I have uploaded a sample project on github https://github.com/anu-srivastava/MonetDB_Test.

You should be able to replicate the problem.

Anu

On 7/22/15, 0:36, "Hannes Mühleisen" wrote:

...

Hi,

> On 21.07.2015, at 20:27, Anu Raj Srivastava > wrote: > I won't be able to share the code I am working against but I will try > to create a new stand-alone application which can replicate the issue. > I'll try to share it as soon as possible. OK.

> Also, in your earlier comments you mentioned that disk space does fill > up for a small period of time. Is that a desired behavior? What if the > connection pool stays active for long period and that causes disk space > to fill up? I think this could be the scenario in my case This is expected behavior. MonetDB puts intermediate results into memory-mapped files. For these, some space is required. These files are removed once the query is finished (or should be). This has nothing to do with connections.

Hannes

> > -----Original Message----- > From: developers-list > [mailto:developers-list-bounces+anu.srivastava=rms.com@monetdb.org] On > Behalf Of Hannes Mühleisen > Sent: Monday, July 20, 2015 11:19 PM > To: Communication channel for developers of the MonetDB suite. > Subject: Re: MonetDB causing disk space to fill up > > Hey Anu, > > we need more than a snippet of Java code to properly debug this. > Schema, sample data, queries, runnable stand-alone Java source at the > least. > > Hannes > >> On 20.07.2015, at 23:28, Anu Raj Srivastava >> wrote: >> >> I am sorry didn¹t realize mailing list is not their in my reply. >> >> On 7/20/15, 14:24, "Ying Zhang" wrote: >> >>> Hello Anu, >>> >>> Would you please post this to the MonetDB users mailing list? >>> Thanks! >>> >>> Regards, >>> >>> Jennie >>> >>>> On Jul 20, 2015, at 19:44, Anu Raj Srivastava >>>> >>>> wrote: >>>> >>>> Hi Hannes, >>>> >>>> I am using apache dbcp2 connection Pool. I am running queries in a >>>> constant loop with 8 concurrent invocations. And each 8 threads are >>>> running 9 queries each again and again. >>>> >>>> Java code is quite straight forward:- >>>> >>>> private ConnectionPoolPG() { >>>> try { >>>> final Properties properties = new Properties(); >>>> >>>> properties.load(ConnectionPoolPG.class.getClassLoader().getResourceA >>>> sStre >>>> am("monetDB.properties")); >>>> dataSource = >>>> BasicDataSourceFactory.createDataSource(properties); >>>> schemaName = properties.getProperty("schemaname"); >>>> } catch (Exception e) { >>>> throw new ExposureStoreException("Unable to instantiate >>>> connection pool: " + e.getMessage(), >>>> EAPAException.ERR_STORE_CONNECTION_CONFIG_ERROR); >>>> } >>>> } >>>> >>>> public void AllQueryPerformance() throws Exception { >>>> while (true) { >>>> if (queries != null) { >>>> for (String query : queries) { >>>> try (Connection connection = >>>> ConnectionPoolPG.getInstance().getConnection()) { >>>> try (Statement statement = >>>> connection.createStatement()) { >>>> statement.execute(query); >>>> } >>>> } >>>> } >>>> } >>>> } >>>> } >>>> >>>> -----Original Message----- >>>> From: Hannes Mühleisen [mailto:Hannes.Muehleisen@cwi.nl] >>>> Sent: Sunday, July 19, 2015 4:47 AM >>>> To: Communication channel for developers of the MonetDB suite. >>>> Cc: Ying Zhang; Anu Raj Srivastava >>>> Subject: Re: MonetDB causing disk space to fill up >>>> >>>> >>>>> On 19 Jul 2015, at 13:23, Hannes Mühleisen >>>>> >>>>> wrote: >>>>> >>>>> Hi, >>>>> >>>>>> On 16 Jul 2015, at 23:11, Ying Zhang wrote: >>>>>> >>>>>> On Jul 13, 2015, at 22:32, Anu Raj Srivastava >>>>>> wrote: >>>>>>> >>>>>>> Hey Jennie, >>>>>>> >>>>>>> I did upgrade to latest version but I am facing the same issue. >>>>>>> Further debugging into the issue it seems like it is >>>>>>> ConnectionPool issue. When I use new connection for each query >>>>>>> MonetDB seems to be able to clean up the resources >>>>>> >>>>>> Ok, MonetDB is doing what it¹s supposed to do. >>>>>> >>>>>>> but if I use ConnectionPool for some reason DiskSpace keep >>>>>>> filling up. >>>>>> >>>>>> If a connection is kept open, I¹d expect MonetDB to be less eager >>>>>> to clean up resources. >>>>>> However, we generally recommend using a ConnectionPool, to avoid >>>>>> too much overhead caused by making a new connection for every >>>>>> query. So my suggestion for now would be to keep using the >>>>>> ConnectionPool, but try to find the reason why your disk is filled >>>>>> up. >>>>>> >>>>>>> Do you have any idea why that could happen? >>>>>> >>>>>> Hmmm, no. We¹ve fixed quite some leaks in Jul2015... >>>>>> Some more thinking: >>>>>> - After how many iterations of repeatedly executing the queries is >>>>>> your disk filled up? >>>>>> - Can you please try to run your queries with mclient to observe >>>>>> the behaviour of MonetDB (and disk usage)? >>>>>> >>>>>> @Hannes: sorry to disturb you during holiday. But if possible, >>>>>> would you please have a look at this thread. Does anything ring a >>>>>> bell? >>>>>> Thanks! >>>>> >>>>> I tested this with ac3p0 connection pool (limited to 4 concurrent >>>>> connections) and repeating queries from 10 threads (SELECT >>>>> MIN(something) FROM Š). I was not able to observe any (!) increase >>>>> in storage footprint. So without a minimal reproducible example of >>>>> Java code + DB schema + sample data, it is hard to tell what is >>>>> going on. Is this running in auto commit? Using temporary tables? >>>> >>>> Small follow-up: When forcing the allocation of a large intermediate >>>> (SELECT MIN(something+1) FROM Š), additional storage space is indeed >>>> claimed. The attached plot shows dbfarm size over time while 10 >>>> threads are running 1000 queries each using a 5-connection pool. You >>>> can see how the amount of additional storage drops back to exactly >>>> where it was once the pool shuts down at around 350s, and that there >>>> is no accumulation of additional storage over the course of the pool >>>> being active. >>>> >>>> Hannes >>>> >>> >> >> _______________________________________________ >> developers-list mailing list >> developers-list@monetdb.org >> https://www.monetdb.org/mailman/listinfo/developers-list > > _______________________________________________ > developers-list mailing list > developers-list@monetdb.org > https://www.monetdb.org/mailman/listinfo/developers-list > _______________________________________________ > developers-list mailing list > developers-list@monetdb.org > https://www.monetdb.org/mailman/listinfo/developers-list

_______________________________________________ developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list

_______________________________________________ developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list

_______________________________________________ developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list

_______________________________________________ developers-list mailing list developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list

Hey Hannes, Thanks for looking into it. Any idea when will the new version be released? Anu From: developers-list [mailto:developers-list-bounces+anu.srivastava=rms.com@monetdb.org] On Behalf Of Hannes Mühleisen Sent: Wednesday, August 12, 2015 5:04 AM To: Communication channel for developers of the MonetDB suite. Subject: Re: MonetDB causing disk space to fill up Hi Anu, I just fixed a bug in the JDBC driver that lead to result sets piling up in the server. See https://www.monetdb.org/bugzilla/show_bug.cgi?id=3786 So the next version of the driver should no longer have this issue. Thanks for taking the time to create your test program! Best, Hannes On 11 Aug 2015, at 18:05, Anu Raj Srivastava mailto:Anu.Srivastava@rms.com> wrote: Hi Hannes, Thanks for looking into it. I did try a different connection pool so I am not sure whether it is connection pool issue or not. Regards, Anu -----Original Message----- From: Hannes Mühleisen [mailto:hannes.muehleisen@cwi.nl] Sent: Tuesday, August 11, 2015 8:07 AM To: Anu Raj Srivastava mailto:Anu.Srivastava@rms.com> Cc: Communication channel for developers of the MonetDB suite. mailto:developers-list@monetdb.org> Subject: Re: MonetDB causing disk space to fill up Confirmed, you test program does make the disk fill up. This should not happen, it could be the connection pool not properly releasing resources (such as Statement or ResultSet instances) or a bug in MonetDB itself. Will investigate further. Hannes On 11 Aug 2015, at 12:19, Hannes Mühleisen mailto:Hannes.Muehleisen@cwi.nl> wrote: https://www.monetdb.org/bugzilla/show_bug.cgi?id=3784 On 11 Aug 2015, at 12:02, Hannes Mühleisen mailto:hannes.muehleisen@cwi.nl> wrote: I checked on this, your test program triggers an assertion in mserver5. Will investigate. On 11 Aug 2015, at 10:49, Hannes Mühleisen mailto:hannes.muehleisen@cwi.nl> wrote: Not yet, sorry. On 10 Aug 2015, at 22:33, Anu Raj Srivastava mailto:Anu.Srivastava@rms.com> wrote: Hi Hannes, Just wanted to check if you got a chance to look into the issue? Anu On 7/31/15, 17:33, "developers-list on behalf of Anu Raj Srivastava" mailto:developers-list-bounces+anu.srivastava=rms.com@monetdb.org on behalf of Anu.Srivastava@rms.commailto:Anu.Srivastava@rms.com> wrote: Hi Hannes, I have uploaded a sample project on github https://github.com/anu-srivastava/MonetDB_Test. You should be able to replicate the problem. Anu On 7/22/15, 0:36, "Hannes Mühleisen" mailto:Hannes.Muehleisen@cwi.nl> wrote: Hi, On 21.07.2015, at 20:27, Anu Raj Srivastava mailto:Anu.Srivastava@rms.com> wrote: I won't be able to share the code I am working against but I will try to create a new stand-alone application which can replicate the issue. I'll try to share it as soon as possible. OK. Also, in your earlier comments you mentioned that disk space does fill up for a small period of time. Is that a desired behavior? What if the connection pool stays active for long period and that causes disk space to fill up? I think this could be the scenario in my case This is expected behavior. MonetDB puts intermediate results into memory-mapped files. For these, some space is required. These files are removed once the query is finished (or should be). This has nothing to do with connections. Hannes -----Original Message----- From: developers-list [mailto:developers-list-bounces+anu.srivastava=rms.com@monetdb.org] On Behalf Of Hannes Mühleisen Sent: Monday, July 20, 2015 11:19 PM To: Communication channel for developers of the MonetDB suite. Subject: Re: MonetDB causing disk space to fill up Hey Anu, we need more than a snippet of Java code to properly debug this. Schema, sample data, queries, runnable stand-alone Java source at the least. Hannes On 20.07.2015, at 23:28, Anu Raj Srivastava mailto:Anu.Srivastava@rms.com> wrote: I am sorry didn¹t realize mailing list is not their in my reply. On 7/20/15, 14:24, "Ying Zhang" mailto:Y.Zhang@cwi.nl> wrote: Hello Anu, Would you please post this to the MonetDB users mailing list? Thanks! Regards, Jennie On Jul 20, 2015, at 19:44, Anu Raj Srivastava mailto:Anu.Srivastava@rms.com> wrote: Hi Hannes, I am using apache dbcp2 connection Pool. I am running queries in a constant loop with 8 concurrent invocations. And each 8 threads are running 9 queries each again and again. Java code is quite straight forward:- private ConnectionPoolPG() { try { final Properties properties = new Properties(); properties.load(ConnectionPoolPG.class.getClassLoader().getResourceA sStre am("monetDB.properties")); dataSource = BasicDataSourceFactory.createDataSource(properties); schemaName = properties.getProperty("schemaname"); } catch (Exception e) { throw new ExposureStoreException("Unable to instantiate connection pool: " + e.getMessage(), EAPAException.ERR_STORE_CONNECTION_CONFIG_ERROR); } } public void AllQueryPerformance() throws Exception { while (true) { if (queries != null) { for (String query : queries) { try (Connection connection = ConnectionPoolPG.getInstance().getConnection()) { try (Statement statement = connection.createStatement()) { statement.execute(query); } } } } } } -----Original Message----- From: Hannes Mühleisen [mailto:Hannes.Muehleisen@cwi.nl] Sent: Sunday, July 19, 2015 4:47 AM To: Communication channel for developers of the MonetDB suite. Cc: Ying Zhang; Anu Raj Srivastava Subject: Re: MonetDB causing disk space to fill up On 19 Jul 2015, at 13:23, Hannes Mühleisen mailto:Hannes.Muehleisen@cwi.nl> wrote: Hi, On 16 Jul 2015, at 23:11, Ying Zhang mailto:Y.Zhang@cwi.nl> wrote: On Jul 13, 2015, at 22:32, Anu Raj Srivastava mailto:Anu.Srivastava@rms.com> wrote: Hey Jennie, I did upgrade to latest version but I am facing the same issue. Further debugging into the issue it seems like it is ConnectionPool issue. When I use new connection for each query MonetDB seems to be able to clean up the resources Ok, MonetDB is doing what it¹s supposed to do. but if I use ConnectionPool for some reason DiskSpace keep filling up. If a connection is kept open, I¹d expect MonetDB to be less eager to clean up resources. However, we generally recommend using a ConnectionPool, to avoid too much overhead caused by making a new connection for every query. So my suggestion for now would be to keep using the ConnectionPool, but try to find the reason why your disk is filled up. Do you have any idea why that could happen? Hmmm, no. We¹ve fixed quite some leaks in Jul2015... Some more thinking: - After how many iterations of repeatedly executing the queries is your disk filled up? - Can you please try to run your queries with mclient to observe the behaviour of MonetDB (and disk usage)? @Hannes: sorry to disturb you during holiday. But if possible, would you please have a look at this thread. Does anything ring a bell? Thanks! I tested this with ac3p0 connection pool (limited to 4 concurrent connections) and repeating queries from 10 threads (SELECT MIN(something) FROM Š). I was not able to observe any (!) increase in storage footprint. So without a minimal reproducible example of Java code + DB schema + sample data, it is hard to tell what is going on. Is this running in auto commit? Using temporary tables? Small follow-up: When forcing the allocation of a large intermediate (SELECT MIN(something+1) FROM Š), additional storage space is indeed claimed. The attached plot shows dbfarm size over time while 10 threads are running 1000 queries each using a 5-connection pool. You can see how the amount of additional storage drops back to exactly where it was once the pool shuts down at around 350s, and that there is no accumulation of additional storage over the course of the pool being active. Hannes _______________________________________________ developers-list mailing list developers-list@monetdb.orgmailto:developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list _______________________________________________ developers-list mailing list developers-list@monetdb.orgmailto:developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list _______________________________________________ developers-list mailing list developers-list@monetdb.orgmailto:developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list _______________________________________________ developers-list mailing list developers-list@monetdb.orgmailto:developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list _______________________________________________ developers-list mailing list developers-list@monetdb.orgmailto:developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list _______________________________________________ developers-list mailing list developers-list@monetdb.orgmailto:developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list _______________________________________________ developers-list mailing list developers-list@monetdb.orgmailto:developers-list@monetdb.org https://www.monetdb.org/mailman/listinfo/developers-list