Thread-safe shutdown while other threads might try to reopen

[Trejkaz]

Hi all.

We have an application where there are multiple different contexts and
each is isolated from the others (like a webapp). Individual users
close and open databases at will. They're opening their own database
instance rather than sharing an instance directly, in an attempt to
improve the isolation.

When all users have disconnected from the database, the admin expects
to be able to move the database files aside, so we have to shut the
database down when nobody is connected to it.

To try and achieve that, we keep our own static reference count. This
is done somewhat like this (only the real version uses ConcurrentMap,
so there is a lot more boilerplate):

public class Database {
private static final Map connectionCountMap =
new HashMap();
private static final Object connectionCountLock = new Object();

private final String path;
private final Connection conn;

public Database(String path) throws SQLException {
this.path = path;
conn = DriverManager.getConnection("jdbc:derby:" + path);
}

public void close() throws SQLException {
conn.close();
decConnectionCount(path);
}

public void incConnectionCount(String path) {
Integer count = connectionCountMap.get(path);
if (count == null) {
count = 1;
}
connectionCountMap.put(path, count + 1);
}

public void decConnectionCount(String path) {
Integer count = connectionCountMap.get(path);
int countAfterDec = count - 1;
if (countAfterDec > 0) {
connectionCountMap.put(path, countAfterDec);
} else {
connectionCountMap.remove(path);
try {
DriverManager.getConnection("jdbc:derby:" + path +
 ";shutdown=true");
} catch (SQLException e) { /* expected */ }
}
}
}

What we are finding is that if multiple threads are accessing the same
database like this, sometimes one of them will be in the middle of
opening a connection while another thread is shutting down the
database. I ended up debugging this into Derby and found that the
thread opening the database would get a null database reference. Derby
would then throw an exception saying the database not found.

So the end result is that despite the database files existing on disk,
Derby would report that the database was not found, which is wrong in
itself...

Anyway, setting the incorrect exception message aside, it seems like
Derby is thread-hostile in this regard. The docs do say only to
shutdown when you know the JVM won't open the database again. The
problem with this is that you never know that, so you can never shut
it down--so the files never get unlocked.

I know I can just slap a synchronized block around these two methods
to make it bulletproof, but there are two problems with this:
  (1) synchronized is slow and Derby's shutdown is not fast at all...
  (2) I don't know what other apps might be open in the same JVM at the same.

So I wonder if anyone with experience in this sort of thing has any
good tips on how to get around the problem.

Also, we're on Derby 10.8, so if anything has been improved in this
regard in the later releases, that would be good to know - it might
just be enough impetus to switch.

TX

Re: Thread-safe shutdown while other threads might try to reopen

[Bryan Pendleton]

I know I can just slap a synchronized block around these two methods
to make it bulletproof, but there are two problems with this:
   (1) synchronized is slow and Derby's shutdown is not fast at all...
   (2) I don't know what other apps might be open in the same JVM at the same.


I'm not sure you can avoid the performance cost, given the
needs of your application.

I'm always more comfortable getting the correct behavior first,
then dealing with the implications once it's reliably running.

I think you may have to do slightly more than just make two
methods synchronized; it isn't exactly clear to me which two
methods you mean. I might try:

 - make the Database class have two public methods "Open" and "Close".
 - in Open, call DriverManager.getConnection and increment your
   in-use count
 - in Close, close the connection, decrement your in-use count,
   and shutdown the database when it goes to zero
 - make Open and Close synchronized

Regarding other apps in the same JVM, is that truly a concern?

I'm not sure how your app is packaged, but it seems unlikely to me
that some other arbitrary body of Java code is going to try to
open one of your own databases. How would that code even know what
connection URL to use?

thanks,

bryan

Re: Thread-safe shutdown while other threads might try to reopen

[Dag H. Wanvik]

Hi,

I don't believe this situation has changed since 10.8. I agree this 
situation could be improved, so I suggest filing an improvement request 
in JIRA. It would also help if you could supply a self contained repro 
(not strictly necessary, as I believe the description is clear enough, 
but it might just increase the chance of some developer looking at it...)


Thanks,
Dag

On 01. okt. 2013 11:57, Trejkaz wrote:

Hi all.

We have an application where there are multiple different contexts and
each is isolated from the others (like a webapp). Individual users
close and open databases at will. They're opening their own database
instance rather than sharing an instance directly, in an attempt to
improve the isolation.

When all users have disconnected from the database, the admin expects
to be able to move the database files aside, so we have to shut the
database down when nobody is connected to it.

To try and achieve that, we keep our own static reference count. This
is done somewhat like this (only the real version uses ConcurrentMap,
so there is a lot more boilerplate):

 public class Database {
 private static final Map connectionCountMap =
 new HashMap();
 private static final Object connectionCountLock = new Object();

 private final String path;
 private final Connection conn;

 public Database(String path) throws SQLException {
 this.path = path;
 conn = DriverManager.getConnection("jdbc:derby:" + path);
 }

 public void close() throws SQLException {
 conn.close();
 decConnectionCount(path);
 }

 public void incConnectionCount(String path) {
 Integer count = connectionCountMap.get(path);
 if (count == null) {
 count = 1;
 }
 connectionCountMap.put(path, count + 1);
 }

 public void decConnectionCount(String path) {
 Integer count = connectionCountMap.get(path);
 int countAfterDec = count - 1;
 if (countAfterDec > 0) {
 connectionCountMap.put(path, countAfterDec);
 } else {
 connectionCountMap.remove(path);
 try {
 DriverManager.getConnection("jdbc:derby:" + path +
  ";shutdown=true");
 } catch (SQLException e) { /* expected */ }
 }
 }
 }

What we are finding is that if multiple threads are accessing the same
database like this, sometimes one of them will be in the middle of
opening a connection while another thread is shutting down the
database. I ended up debugging this into Derby and found that the
thread opening the database would get a null database reference. Derby
would then throw an exception saying the database not found.

So the end result is that despite the database files existing on disk,
Derby would report that the database was not found, which is wrong in
itself...

Anyway, setting the incorrect exception message aside, it seems like
Derby is thread-hostile in this regard. The docs do say only to
shutdown when you know the JVM won't open the database again. The
problem with this is that you never know that, so you can never shut
it down--so the files never get unlocked.

I know I can just slap a synchronized block around these two methods
to make it bulletproof, but there are two problems with this:
   (1) synchronized is slow and Derby's shutdown is not fast at all...
   (2) I don't know what other apps might be open in the same JVM at the same.

So I wonder if anyone with experience in this sort of thing has any
good tips on how to get around the problem.

Also, we're on Derby 10.8, so if anything has been improved in this
regard in the later releases, that would be good to know - it might
just be enough impetus to switch.

TX

Re: Thread-safe shutdown while other threads might try to reopen

[Knut Anders Hatlen]

"Dag H. Wanvik"  writes:

> Hi,
>
> I don't believe this situation has changed since 10.8. I agree this
> situation could be improved, so I suggest filing an improvement
> request in JIRA. It would also help if you could supply a self
> contained repro (not strictly necessary, as I believe the description
> is clear enough, but it might just increase the chance of some
> developer looking at it...)

There's already a JIRA issue tracking such an improvement:
https://issues.apache.org/jira/browse/DERBY-4447

Work on the issue seems to have stalled, though.

>
> Thanks,
> Dag
>
> On 01. okt. 2013 11:57, Trejkaz wrote:
>> Hi all.
>>
>> We have an application where there are multiple different contexts and
>> each is isolated from the others (like a webapp). Individual users
>> close and open databases at will. They're opening their own database
>> instance rather than sharing an instance directly, in an attempt to
>> improve the isolation.
>>
>> When all users have disconnected from the database, the admin expects
>> to be able to move the database files aside, so we have to shut the
>> database down when nobody is connected to it.
>>
>> To try and achieve that, we keep our own static reference count. This
>> is done somewhat like this (only the real version uses ConcurrentMap,
>> so there is a lot more boilerplate):
>>
>>  public class Database {
>>  private static final Map connectionCountMap =
>>  new HashMap();
>>  private static final Object connectionCountLock = new Object();
>>
>>  private final String path;
>>  private final Connection conn;
>>
>>  public Database(String path) throws SQLException {
>>  this.path = path;
>>  conn = DriverManager.getConnection("jdbc:derby:" + path);
>>  }
>>
>>  public void close() throws SQLException {
>>  conn.close();
>>  decConnectionCount(path);
>>  }
>>
>>  public void incConnectionCount(String path) {
>>  Integer count = connectionCountMap.get(path);
>>  if (count == null) {
>>  count = 1;
>>  }
>>  connectionCountMap.put(path, count + 1);
>>  }
>>
>>  public void decConnectionCount(String path) {
>>  Integer count = connectionCountMap.get(path);
>>  int countAfterDec = count - 1;
>>  if (countAfterDec > 0) {
>>  connectionCountMap.put(path, countAfterDec);
>>  } else {
>>  connectionCountMap.remove(path);
>>  try {
>>  DriverManager.getConnection("jdbc:derby:" + path +
>>   ";shutdown=true");
>>  } catch (SQLException e) { /* expected */ }
>>  }
>>  }
>>  }
>>
>> What we are finding is that if multiple threads are accessing the same
>> database like this, sometimes one of them will be in the middle of
>> opening a connection while another thread is shutting down the
>> database. I ended up debugging this into Derby and found that the
>> thread opening the database would get a null database reference. Derby
>> would then throw an exception saying the database not found.
>>
>> So the end result is that despite the database files existing on disk,
>> Derby would report that the database was not found, which is wrong in
>> itself...
>>
>> Anyway, setting the incorrect exception message aside, it seems like
>> Derby is thread-hostile in this regard. The docs do say only to
>> shutdown when you know the JVM won't open the database again. The
>> problem with this is that you never know that, so you can never shut
>> it down--so the files never get unlocked.
>>
>> I know I can just slap a synchronized block around these two methods
>> to make it bulletproof, but there are two problems with this:
>>(1) synchronized is slow and Derby's shutdown is not fast at all...
>>(2) I don't know what other apps might be open in the same JVM at the 
>> same.
>>
>> So I wonder if anyone with experience in this sort of thing has any
>> good tips on how to get around the problem.
>>
>> Also, we're on Derby 10.8, so if anything has been improved in this
>> regard in the later releases, that would be good to know - it might
>> just be enough impetus to switch.
>>
>> TX

Re: Serious issue with 10.10.1.1 and tomcat-jdbc

[Rick Hillegas]

Hi Frank,

Can you include the stack trace for this error? The ALTER TABLE 
statement does not mention a ROOT schema, so it is hard to see how this 
error would come from Derby.


Thanks,
-Rick

On 10/1/13 8:00 AM, Frank Rivera wrote:

Using *Derby 10.10.1.1 Embedded*

*Tomcat 6*

using *tomcat-jdbc connection pool*

The following simple statement fails with the following error " 
*Schema 'ROOT' does not exist* " although the schema is clearly stated 
*esq.licreq*


*ALTER TABLE esq.licreq ADD COLUMN u_domain GENERATED ALWAYS AS 
(UPPER(domain)); *

*
*
Reads and writes are fine. All Alter statements fail.

Without tomcat-jdbc.jar stored in the tomcat/lib folder Alter 
statements work. A pooled connection problem with tomcat-jdbc doesn't 
seem like the cause since the underlying driver is in fact Derby.

*
*
*
*
/
Sincerely,/
/
/
*Frank Rivera* CEO
-
LogicBit Software - Research Triangle Park, North Carolina
919.238.7024408.213.8138*888.366.2280*

***HoudiniEsq ™Legal Practice Management Suite**http://HoudiniEsq.com* 



Feature Rich web-based Legal Practice Management for today's cutting 
edge Law Firms

Any Browser, Any OS, Anytime, Anywhere, Any size firm, SaaS or On-premise

Simple & Simplicity are not the same thing. *"Simple", as it turns 
out, simply isn't good enough.*
-- 

*NOTICE*: The sender does not accept liability for any errors 
or omissions in the contents of this message which arise as
a result of e-mail transmission.  This e-mail and any files 
transmitted with it are confidential and are intended solely for
the use of the individual or entity to which they are addressed. It is 
not an offer or acceptance, and it is not intended to
be all or part of an agreement.  This communication may 
contain material protected by the attorney-client privilege.
Original material created by the author may be protected by US 
Copyright law, 17 U.S.C. § 101 et seq. This communication
constitutes an electronic communication within the meaning of the 
Electronic Communications Privacy Act, 18 U.S.C.
§ 2510, and its disclosure is strictly limited to the recipient 
intended by the sender of this message.   If you are not the
intended recipient or the person responsible for delivering the e-mail 
to the intended recipient, be advised that you have
received this e-mail in error and that any review, use, dissemination, 
forwarding, printing, or copying of this e-mail is strictly
prohibited. If you have received this e-mail in error, please notify 
the sender immediately by return e-mail and delete this

Re: Proper configuration for a very busy DB?

[Jerry Lampi]

Peter:
Each client has one connection.  It is used for the entire session (which can 
be days).
The Derby log file are configured to have one log file per day.  Format names 
like: productName-stderr.2013-10-01.log and productName-
stdout.2013-10-01.log.

Brett:
- A flurry of data has been as great as 4000 records per second.  That is the 
number cached by the client(s) and each record is dumped to the DB one at a 
time.  Not all 30 clients see 4000 per second, likely only 2 or three of them.  
The DB has over 10 million records in it at any given time and it is purged 
daily of older records.
- We use prepared statements (PS).
- Each client has one dedicated connection.

All:
I appreciate your responses.  I will benchmark using JMeter and then follow the 
tuning tips for derby 10.8 ( 
http://db.apache.org/derby/docs/10.8/tuning/index.html ).  I will start by 
tweaking the derby.statementCache.size up from the 100 default.

Any other advice greatly appreciated.

Thanks,

Jerry

On 9/30/2013 2:55 PM, Peter wrote:
Do you open new connection every time or do you have a pool? How often does 
Derby checkpoint/switch log file?


Peter


On 9/30/2013 2:47 PM, Bergquist, Brett wrote:

Jerry, can you provide a bit more background which might be helpful:

- what is your definition of a flurry of data?   What sort of transaction rate 
do you estimate this is?
- are you using prepared statements for your inserts, updates, etc? If not, 
then do so and also change the derby.statementCache.size to something quite a 
bit larger.  This will allow the statements to be compiled once and cached 
instead of being prepared each time you execute them.
- are you using a connection pool or are you opening/closing connections 
frequently?

I have a system with a busy database and it took some tuning to get to this 
point.  Right now it is doing about 100 inserts/second continuous 24x7 and it 
has peaked up to 200 inserts/second.  Granted my application is different than 
what you are doing but it is possible to get derby to run when busy.


-Original Message-
From: Jerry Lampi [mailto:j...@sdsusa.com]
Sent: Monday, September 30, 2013 3:29 PM
To: Derby User Group
Subject: Proper configuration for a very busy DB?

We have about 30 clients that connect to our version 10.8.2.2 Derby DB.

The clients are programs that gather data from the operating system of their 
host and then store that data in the DB, including FTP activity.
Sometimes, the clients get huge flurries of data all at once and Derby is 
unable to handle the influx of requests; inserts, updates, etc.  In addition, 
the clients are written so that if they are unable to talk to the DB, they 
queue up as much data as possible and then write it to the DB when the DB 
becomes available.

This client queuing is a poor design, and places greater stress on the DB, as 
when the 30 clients finally do talk to the DB, they all dump data at once.  The 
clients do not know about one another and therefore do not attempt any 
throttling or cooperation when dumping on the DB.

The net effect of all this is that the DB is too slow to keep up with the 
clients.  As clients try to feed data to the DB, it cannot accept it as fast as 
desired and this results in the clients queueing more data, exacerbating the 
issue.

So the DB is very busy.  The only significant thing we have done thus far is 
change the derby.storage.pageCacheSize=5000 and increase Java heap to 1536m.

Is there a configuration considered optimal for a VERY busy Derby DB?

Thanks,

Jerry


---
avast! Antivirus: Outbound message clean.
Virus Database (VPS): 130930-0, 09/30/2013 Tested on: 9/30/2013 2:28:40 PM 
avast! - copyright (c) 1988-2013 AVAST Software.
http://www.avast.com









---
avast! Antivirus: Outbound message clean.
Virus Database (VPS): 131001-0, 10/01/2013
Tested on: 10/1/2013 10:53:12 AM
avast! - copyright (c) 1988-2013 AVAST Software.
http://www.avast.com

Re: Serious issue with 10.10.1.1 and tomcat-jdbc

[Rick Hillegas]

On 10/1/13 9:02 AM, Frank Rivera wrote:
ALTER TABLE esq.licreq ADD COLUMN u_domain GENERATED ALWAYS AS 
(UPPER(domain))

Hi Frank,

I have been able to reproduce this problem when the connected user (ROOT 
in this case) has not created any schema objects of his own. I have 
logged https://issues.apache.org/jira/browse/DERBY-6361 to track this 
problem.


Derby only implicitly creates a schema for the default APP user. Derby 
is supposed to implicitly create a schema for the connected user when 
the user performs certain operations. But this ALTER TABLE statement 
isn't one of those operations. The workaround would be to make sure that 
a schema has been created for the connected user. This can be done 
explicitly like this...


CREATE SCHEMA root;

Here's a script which shows the problem:

connect 'jdbc:derby:memory:db;create=true;user=esq';

create table licreq( domain varchar( 10 ) );

connect 'jdbc:derby:memory:db;user=root';

-- fails
ALTER TABLE esq.licreq ADD COLUMN u_domain GENERATED ALWAYS AS 
(UPPER(domain));


create schema root;

-- succeeds
ALTER TABLE esq.licreq ADD COLUMN u_domain GENERATED ALWAYS AS 
(UPPER(domain));


Hope this helps,
-Rick

RE: Proper configuration for a very busy DB?

[Bergquist, Brett]

Jerry, I have a similar database size, about +10,000,000 records a day being 
stored and needing purging.  I found that purging the database had a 
significant impact on the insertion rate.  I originally had one table in the 
database for these (in my case performance measurements) records.   I 
ultimately went to a "poor man's partitioning".   I created separate tables for 
each week of the year (53 tables), inserted records into the correct week, used 
a database view to join these tables back into one logical table (a union 
across the tables) and then purging was done by week.  This was nearly 
instantaneous using a TRUNCATE TABLE.

Just something to be aware of.

-Original Message-
From: Jerry Lampi [mailto:j...@sdsusa.com] 
Sent: Tuesday, October 01, 2013 11:53 AM
To: Derby Discussion
Subject: Re: Proper configuration for a very busy DB?

Peter:
Each client has one connection.  It is used for the entire session (which can 
be days).
The Derby log file are configured to have one log file per day.  Format names 
like: productName-stderr.2013-10-01.log and productName- stdout.2013-10-01.log.

Brett:
- A flurry of data has been as great as 4000 records per second.  That is the 
number cached by the client(s) and each record is dumped to the DB one at a 
time.  Not all 30 clients see 4000 per second, likely only 2 or three of them.  
The DB has over 10 million records in it at any given time and it is purged 
daily of older records.
- We use prepared statements (PS).
- Each client has one dedicated connection.

All:
I appreciate your responses.  I will benchmark using JMeter and then follow the 
tuning tips for derby 10.8 ( 
http://db.apache.org/derby/docs/10.8/tuning/index.html ).  I will start by 
tweaking the derby.statementCache.size up from the 100 default.

Any other advice greatly appreciated.

Thanks,

Jerry

On 9/30/2013 2:55 PM, Peter wrote:
Do you open new connection every time or do you have a pool? How often does 
Derby checkpoint/switch log file?


Peter


On 9/30/2013 2:47 PM, Bergquist, Brett wrote:
> Jerry, can you provide a bit more background which might be helpful:
>
> - what is your definition of a flurry of data?   What sort of transaction 
> rate do you estimate this is?
> - are you using prepared statements for your inserts, updates, etc? If not, 
> then do so and also change the derby.statementCache.size to something quite a 
> bit larger.  This will allow the statements to be compiled once and cached 
> instead of being prepared each time you execute them.
> - are you using a connection pool or are you opening/closing connections 
> frequently?
>
> I have a system with a busy database and it took some tuning to get to this 
> point.  Right now it is doing about 100 inserts/second continuous 24x7 and it 
> has peaked up to 200 inserts/second.  Granted my application is different 
> than what you are doing but it is possible to get derby to run when busy.
>
>
> -Original Message-
> From: Jerry Lampi [mailto:j...@sdsusa.com]
> Sent: Monday, September 30, 2013 3:29 PM
> To: Derby User Group
> Subject: Proper configuration for a very busy DB?
>
> We have about 30 clients that connect to our version 10.8.2.2 Derby DB.
>
> The clients are programs that gather data from the operating system of their 
> host and then store that data in the DB, including FTP activity.
> Sometimes, the clients get huge flurries of data all at once and Derby is 
> unable to handle the influx of requests; inserts, updates, etc.  In addition, 
> the clients are written so that if they are unable to talk to the DB, they 
> queue up as much data as possible and then write it to the DB when the DB 
> becomes available.
>
> This client queuing is a poor design, and places greater stress on the DB, as 
> when the 30 clients finally do talk to the DB, they all dump data at once.  
> The clients do not know about one another and therefore do not attempt any 
> throttling or cooperation when dumping on the DB.
>
> The net effect of all this is that the DB is too slow to keep up with the 
> clients.  As clients try to feed data to the DB, it cannot accept it as fast 
> as desired and this results in the clients queueing more data, exacerbating 
> the issue.
>
> So the DB is very busy.  The only significant thing we have done thus far is 
> change the derby.storage.pageCacheSize=5000 and increase Java heap to 1536m.
>
> Is there a configuration considered optimal for a VERY busy Derby DB?
>
> Thanks,
>
> Jerry
>
>
> ---
> avast! Antivirus: Outbound message clean.
> Virus Database (VPS): 130930-0, 09/30/2013 Tested on: 9/30/2013 2:28:40 PM 
> avast! - copyright (c) 1988-2013 AVAST Software.
> http://www.avast.com
>
>
>
>
>



---
avast! Antivirus: Outbound message clean.
Virus Database (VPS): 131001-0, 10/01/2013 Tested on: 10/1/2013 10:53:12 AM 
avast! - copyright (c) 1988-2013 AVAST Software.
http://www.avast.com