date:20130704

Re: Is there some way to shut down a Derby database faster?

2013-07-04 Thread Peter Ondruška

So if users need data after working with database give them consistent copy 
using backup 
http://db.apache.org/derby/docs/10.0/manuals/admin/hubprnt43.html#HDRSII-BUBBKUP-63476

Peter

On 4. 7. 2013, at 23:17, Trejkaz  wrote:

> On Fri, Jul 5, 2013 at 12:44 AM, Bryan Pendleton
>  wrote:
>> Have you considered using a connection pool in between your
>> application layer and the database, so that the connections
>> are retained and re-used, rather than being fully reclaimed
>> and fully reopened?
> 
> From the original post:
> 
> | and when the user closes the database they expect to be able to move
> | the files immediately after.
> 
> TX

Re: Is there some way to shut down a Derby database faster?

2013-07-04 Thread Trejkaz

On Fri, Jul 5, 2013 at 12:44 AM, Bryan Pendleton
 wrote:
> Have you considered using a connection pool in between your
> application layer and the database, so that the connections
> are retained and re-used, rather than being fully reclaimed
> and fully reopened?

>From the original post:

| and when the user closes the database they expect to be able to move
| the files immediately after.

TX

Re: Is there some way to shut down a Derby database faster?

2013-07-04 Thread Bryan Pendleton


Exiting the VM isn't really an option for me anyway, this is just when
someone is closing the database, potentially planning to open another
one.

Another question along the same lines then - if I kick off the
shutdown in another thread, what happens if the same database is then
reopened while the shutdown is still occurring?


It sounds like users are routinely arriving, doing work,
and disconnecting, causing the database to be opened and closed.

Have you considered using a connection pool in between your
application layer and the database, so that the connections
are retained and re-used, rather than being fully reclaimed
and fully reopened?

thanks,

bryan

Re: st.executeQuery() got stuck

2013-07-04 Thread Amit

Also, I did jstack on derby process and got a thread in BLOCKED state, what
is the reason for this?

"DRDAConnThread_5" prio=6 tid=0x27500c00 nid=0x178c in Object.wait()
[0x2801f000]
   java.lang.Thread.State: BLOCKED (on object monitor)
at java.lang.Object.wait(Native Method)
 at java.lang.Object.wait(Object.java:503)
at org.apache.derby.impl.store.raw.data.BasePage.setExclusive(Unknown
Source)
 - locked <0x18f464e8> (a org.apache.derby.impl.store.raw.data.StoredPage)
 at org.apache.derby.impl.store.raw.data.BaseContainer.latchPage(Unknown
Source)
at org.apache.derby.impl.store.raw.data.FileContainer.latchPage(Unknown
Source)
 at org.apache.derby.impl.store.raw.data.FileContainer.getUserPage(Unknown
Source)
 at
org.apache.derby.impl.store.raw.data.FileContainer.getNextHeadPage(Unknown
Source)
at org.apache.derby.impl.store.raw.data.BaseContainer.getNextPage(Unknown
Source)
 at
org.apache.derby.impl.store.raw.data.BaseContainerHandle.getNextPage(Unknown
Source)
 at
org.apache.derby.impl.store.access.conglomerate.GenericScanController.positionAtNextPage(Unknown
Source)
 at
org.apache.derby.impl.store.access.conglomerate.GenericScanController.fetchRows(Unknown
Source)
 at org.apache.derby.impl.store.access.heap.HeapScan.fetchNextGroup(Unknown
Source)
at
org.apache.derby.impl.sql.execute.BulkTableScanResultSet.reloadArray(Unknown
Source)
 at
org.apache.derby.impl.sql.execute.BulkTableScanResultSet.getNextRowCore(Unknown
Source)
 at
org.apache.derby.impl.sql.execute.NestedLoopJoinResultSet.getNextRowCore(Unknown
Source)
at
org.apache.derby.impl.sql.execute.NestedLoopJoinResultSet.getNextRowCore(Unknown
Source)
 at
org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(Unknown
Source)
 at
org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(Unknown Source)
 - locked <0x1403f008> (a org.apache.derby.impl.jdbc.EmbedConnection40)
 at org.apache.derby.impl.jdbc.EmbedResultSet.next(Unknown Source)
at org.apache.derby.impl.drda.DRDAConnThread.writeFDODTA(Unknown Source)
 at org.apache.derby.impl.drda.DRDAConnThread.writeQRYDTA(Unknown Source)
 at org.apache.derby.impl.drda.DRDAConnThread.processCommands(Unknown
Source)
at org.apache.derby.impl.drda.DRDAConnThread.run(Unknown Source)


On Thu, Jul 4, 2013 at 5:34 PM, Dyre Tjeldvoll wrote:

> On 07/ 4/13 01:30 PM, Amit wrote:
>
>> Thanks Dyre for your reply.
>>
>> Yes, I mean it hangs in executeQuery().
>> The derby running as client/server mode.
>> Other threads/connections accessing the same table simultaneously.
>> The tables are not that large. Max records among the tables listed in
>> the query is 5,000
>>
>> This is the query - (The columns ends with "_ID" are indexed)
>> SELECT"G_EVNT_MASTER_NAME",
>> B."G_CORE_PARTY_NAME",
>> A."G_CORE_PARTY_NAME",
>> "G_EVNT_MASTER_START_DT",
>> "G_EVNT_MASTER_ID",
>> "G_EVNT_MASTER_OFFERING",
>> "G_CORE_DOCUMENT_NAME",
>> "G_CORE_DOCUMENT_URL"
>> FROM"INTERNAL"."G_CUST_**OPPORTUNITY",
>> "INTERNAL"."G_XREF_EVNT_OPPR",
>> "INTERNAL"."G_CORE_PARTY"A,
>> "INTERNAL"."G_CORE_PARTY"B,
>> "INTERNAL"."G_XREF_EVNT_PARTY"**,
>> "INTERNAL"."G_EVNT_MASTER"
>> LEFTOUTERJOIN"INTERNAL"."G_**CORE_DOCUMENT"
>> ONG_EVNT_MASTER_ID=G_CORE_**DOCUMENT_MEETING_ID
>> WHERE"G_EVNT_MASTER_ID"="G_**XREF_EVNT_OPPR_EVENT_ID"
>> AND"G_XREF_EVNT_OPPR_**OPORTUNITY_ID"="G_CUST_**OPPORTUNITY_ID"
>> ANDA."G_CORE_PARTY_ID"="G_**EVNT_MASTER_ORGANIZER_ID"
>> ANDB."G_CORE_PARTY_ID"="G_**XREF_EVNT_PARTY_PARTY_ID"
>>
>> AND"G_XREF_EVNT_PARTY_EVENT_**ID"="G_EVNT_MASTER_ID"
>> AND"G_EVNT_MASTER_TYPE"='**Contact Event'
>> AND"G_XREF_EVNT_PARTY_TYPE"='**Primary Contact'
>> AND"G_CUST_OPPORTUNITY_ID"='**99223977'
>>
>>
>>
>> This is the derby.log -
>> Booting Derby version The Apache Software Foundation - Apache Derby -
>> 10.9.1.0 - (1344872): instance a816c00e-013f-a339-24f0-**697cf4d7
>> on database directory E:\database1 with class loader
>> sun.misc.Launcher$**AppClassLoader@f08d0f
>> Loaded from file:/E:/lib/drivers/derby.jar
>> java.vendor=Oracle Corporation
>> java.runtime.version=1.7.0_13-**b20
>> user.dir=E:\
>> derby.system.home=E:\
>> Database Class Loader started - derby.database.classpath=''
>>
>
> Right. Please have a look at the tuning guide:
>
> http://db.apache.org/derby/**docs/10.10/tuning/**ttundepth33391.html
>
> But you should probably start by finding out if your server is buzy or
> idle. E.g. by using jstack or some other java monitoring tool on the jvm
> running the Derby server to see what it appears to be doing when the query
> hangs. These are some possibilities:
>
> - Derby thread is starved - other threads take all the resources. Can
> happen if Derby is sharing the jvm with another application like an
> appserver.
>
> - Derby thread blocked. It is waiting for db lock or java monitor.
>
> - Derby thread busy. Optimizer may have chosen a bad plan. Maybe
> statistics

Re: st.executeQuery() got stuck

2013-07-04 Thread Dyre Tjeldvoll


On 07/ 4/13 01:30 PM, Amit wrote:

Thanks Dyre for your reply.

Yes, I mean it hangs in executeQuery().
The derby running as client/server mode.
Other threads/connections accessing the same table simultaneously.
The tables are not that large. Max records among the tables listed in
the query is 5,000

This is the query - (The columns ends with "_ID" are indexed)
SELECT"G_EVNT_MASTER_NAME",
B."G_CORE_PARTY_NAME",
A."G_CORE_PARTY_NAME",
"G_EVNT_MASTER_START_DT",
"G_EVNT_MASTER_ID",
"G_EVNT_MASTER_OFFERING",
"G_CORE_DOCUMENT_NAME",
"G_CORE_DOCUMENT_URL"
FROM"INTERNAL"."G_CUST_OPPORTUNITY",
"INTERNAL"."G_XREF_EVNT_OPPR",
"INTERNAL"."G_CORE_PARTY"A,
"INTERNAL"."G_CORE_PARTY"B,
"INTERNAL"."G_XREF_EVNT_PARTY",
"INTERNAL"."G_EVNT_MASTER"
LEFTOUTERJOIN"INTERNAL"."G_CORE_DOCUMENT"
ONG_EVNT_MASTER_ID=G_CORE_DOCUMENT_MEETING_ID
WHERE"G_EVNT_MASTER_ID"="G_XREF_EVNT_OPPR_EVENT_ID"
AND"G_XREF_EVNT_OPPR_OPORTUNITY_ID"="G_CUST_OPPORTUNITY_ID"
ANDA."G_CORE_PARTY_ID"="G_EVNT_MASTER_ORGANIZER_ID"
ANDB."G_CORE_PARTY_ID"="G_XREF_EVNT_PARTY_PARTY_ID"
AND"G_XREF_EVNT_PARTY_EVENT_ID"="G_EVNT_MASTER_ID"
AND"G_EVNT_MASTER_TYPE"='Contact Event'
AND"G_XREF_EVNT_PARTY_TYPE"='Primary Contact'
AND"G_CUST_OPPORTUNITY_ID"='99223977'



This is the derby.log -
Booting Derby version The Apache Software Foundation - Apache Derby -
10.9.1.0 - (1344872): instance a816c00e-013f-a339-24f0-697cf4d7
on database directory E:\database1 with class loader
sun.misc.Launcher$AppClassLoader@f08d0f
Loaded from file:/E:/lib/drivers/derby.jar
java.vendor=Oracle Corporation
java.runtime.version=1.7.0_13-b20
user.dir=E:\
derby.system.home=E:\
Database Class Loader started - derby.database.classpath=''


Right. Please have a look at the tuning guide:

http://db.apache.org/derby/docs/10.10/tuning/ttundepth33391.html

But you should probably start by finding out if your server is buzy or 
idle. E.g. by using jstack or some other java monitoring tool on the jvm 
running the Derby server to see what it appears to be doing when the 
query hangs. These are some possibilities:


- Derby thread is starved - other threads take all the resources. Can 
happen if Derby is sharing the jvm with another application like an 
appserver.


- Derby thread blocked. It is waiting for db lock or java monitor.

- Derby thread busy. Optimizer may have chosen a bad plan. Maybe 
statistics need to be updated.


- Derby thread is idle. Possibly network problem between client and server.


HTH,

Dyre




--
Thanks,
Amit



On Thu, Jul 4, 2013 at 4:06 PM, Dyre Tjeldvoll
mailto:dyre.tjeldv...@oracle.com>> wrote:

On 07/ 4/13 10:36 AM, Amit wrote:

This is the code I am using to execute query.

Statement st = con.createStatement(ResultSet.__TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
st.setFetchSize(100);
ResultSet queryRs = st.executeQuery(sql);
System.out.println("Success");

Usually the query takes 1-2 sec to execute. But very rare
occasion the
code got stuck at line 4.


That would be println... I assume you mean that it hangs in
executeQuery()?


I have waited for 1 hr and then killed the
thread. It's a production issue. I am using connection pool and
my derby
version is - 10.9.1.0

Can you help me narrow down the problem.


Really hard without more information. If you can post the SQL
involved it would be helpful. Is this client/server or embedded?
Is the table large? Do you access it through an index? Are other
threads/connections accessing the same table simultaneously?
Anything in derby.log at the time of the hang?


Regards,

Dyre

Re: st.executeQuery() got stuck

2013-07-04 Thread Amit

Thanks Dyre for your reply.

Yes, I mean it hangs in executeQuery().
The derby running as client/server mode.
Other threads/connections accessing the same table simultaneously.
The tables are not that large. Max records among the tables listed in the
query is 5,000

This is the query - (The columns ends with "_ID" are indexed)
SELECT "G_EVNT_MASTER_NAME",
   B."G_CORE_PARTY_NAME",
   A."G_CORE_PARTY_NAME",
   "G_EVNT_MASTER_START_DT",
   "G_EVNT_MASTER_ID",
   "G_EVNT_MASTER_OFFERING",
   "G_CORE_DOCUMENT_NAME",
   "G_CORE_DOCUMENT_URL"
FROM   "INTERNAL"."G_CUST_OPPORTUNITY",
   "INTERNAL"."G_XREF_EVNT_OPPR",
   "INTERNAL"."G_CORE_PARTY" A,
   "INTERNAL"."G_CORE_PARTY" B,
   "INTERNAL"."G_XREF_EVNT_PARTY",
   "INTERNAL"."G_EVNT_MASTER"
   LEFT OUTER JOIN "INTERNAL"."G_CORE_DOCUMENT"
ON G_EVNT_MASTER_ID = G_CORE_DOCUMENT_MEETING_ID
WHERE  "G_EVNT_MASTER_ID" = "G_XREF_EVNT_OPPR_EVENT_ID"
   AND "G_XREF_EVNT_OPPR_OPORTUNITY_ID" = "G_CUST_OPPORTUNITY_ID"
   AND A."G_CORE_PARTY_ID" = "G_EVNT_MASTER_ORGANIZER_ID"
   AND B."G_CORE_PARTY_ID" = "G_XREF_EVNT_PARTY_PARTY_ID"
   AND "G_XREF_EVNT_PARTY_EVENT_ID" = "G_EVNT_MASTER_ID"
   AND "G_EVNT_MASTER_TYPE" = 'Contact Event'
   AND "G_XREF_EVNT_PARTY_TYPE" = 'Primary Contact'
   AND "G_CUST_OPPORTUNITY_ID" = '99223977'



This is the derby.log -

Booting Derby version The Apache Software Foundation - Apache Derby -
10.9.1.0 - (1344872): instance a816c00e-013f-a339-24f0-697cf4d7
on database directory E:\database1 with class loader
sun.misc.Launcher$AppClassLoader@f08d0f
Loaded from file:/E:/lib/drivers/derby.jar
java.vendor=Oracle Corporation
java.runtime.version=1.7.0_13-b20
user.dir=E:\
derby.system.home=E:\
Database Class Loader started - derby.database.classpath=''

-- 
Thanks,
Amit



On Thu, Jul 4, 2013 at 4:06 PM, Dyre Tjeldvoll wrote:

> On 07/ 4/13 10:36 AM, Amit wrote:
>
>> This is the code I am using to execute query.
>>
>> Statement st = con.createStatement(ResultSet.**TYPE_FORWARD_ONLY,
>> ResultSet.CONCUR_READ_ONLY);
>> st.setFetchSize(100);
>> ResultSet queryRs = st.executeQuery(sql);
>> System.out.println("Success");
>>
>> Usually the query takes 1-2 sec to execute. But very rare occasion the
>> code got stuck at line 4.
>>
>
> That would be println... I assume you mean that it hangs in executeQuery()?
>
>
>  I have waited for 1 hr and then killed the
>> thread. It's a production issue. I am using connection pool and my derby
>> version is - 10.9.1.0
>>
>> Can you help me narrow down the problem.
>>
>
> Really hard without more information. If you can post the SQL involved it
> would be helpful. Is this client/server or embedded?
> Is the table large? Do you access it through an index? Are other
> threads/connections accessing the same table simultaneously?
> Anything in derby.log at the time of the hang?
>
>
> Regards,
>
> Dyre
>
>
>

Re: st.executeQuery() got stuck

2013-07-04 Thread Dyre Tjeldvoll


On 07/ 4/13 10:36 AM, Amit wrote:

This is the code I am using to execute query.

Statement st = con.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
st.setFetchSize(100);
ResultSet queryRs = st.executeQuery(sql);
System.out.println("Success");

Usually the query takes 1-2 sec to execute. But very rare occasion the
code got stuck at line 4.


That would be println... I assume you mean that it hangs in executeQuery()?


I have waited for 1 hr and then killed the
thread. It's a production issue. I am using connection pool and my derby
version is - 10.9.1.0

Can you help me narrow down the problem.


Really hard without more information. If you can post the SQL involved 
it would be helpful. Is this client/server or embedded?
Is the table large? Do you access it through an index? Are other 
threads/connections accessing the same table simultaneously?

Anything in derby.log at the time of the hang?


Regards,

Dyre

st.executeQuery() got stuck

2013-07-04 Thread Amit

This is the code I am using to execute query.

Statement st = con.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
st.setFetchSize(100);
ResultSet queryRs = st.executeQuery(sql);
System.out.println("Success");

Usually the query takes 1-2 sec to execute. But very rare occasion the code
got stuck at line 4. I have waited for 1 hr and then killed the thread.
It's a production issue. I am using connection pool and my derby version is
- 10.9.1.0

Can you help me narrow down the problem.


-- 
Thanks,
Am
it

Re: Is there some way to shut down a Derby database faster?

2013-07-04 Thread Knut Anders Hatlen

Trejkaz  writes:

> On Tue, Jul 2, 2013 at 10:18 PM, Rick Hillegas  
> wrote:
>> If you simply exit the VM without requesting an orderly shutdown, then the
>> checkpoint won't be performed. However, the cleanup performed by that
>> checkpoint still has to be performed. It will be performed the next time you
>> boot the database. My experience is that if you always skip the orderly
>> shutdown, then the time required to boot the database grows progressively
>> longer until it is intolerable. Full disclosure: I haven't tried this
>> experiment for a long time and there may have been improvements in this
>> area. Nevertheless, orderly shutdown is the recommended practice.
>
> Exiting the VM isn't really an option for me anyway, this is just when
> someone is closing the database, potentially planning to open another
> one.
>
> Another question along the same lines then - if I kick off the
> shutdown in another thread, what happens if the same database is then
> reopened while the shutdown is still occurring?

Hi Trejkaz,

I think the shutdown will continue unaffected of the other thread trying
to reopen the database. The thread that tries to reopen it, on the other
hand, might see all kinds of errors, like NullPointerExceptions or
conglomerate not found errors.

There once was an effort to make Derby more well-behaved in this use
case (see https://issues.apache.org/jira/browse/DERBY-4447), but the
work on that issue stalled. So for now you'll probably have to
synchronize between the thread that shuts down the database and those
that try to connect to the same database, so that they wait with
connecting until the database has finished shutting down.

-- 
Knut Anders

Re: Is there some way to shut down a Derby database faster?

Re: Is there some way to shut down a Derby database faster?

Re: Is there some way to shut down a Derby database faster?

Re: st.executeQuery() got stuck

Re: st.executeQuery() got stuck

Re: st.executeQuery() got stuck

Re: st.executeQuery() got stuck

st.executeQuery() got stuck

Re: Is there some way to shut down a Derby database faster?

9 matches

Site Navigation

Mail list logo

Footer information