Re: Is there some way to shut down a Derby database faster?
Trejkaz trej...@trypticon.org writes: On Tue, Jul 2, 2013 at 10:18 PM, Rick Hillegas rick.hille...@oracle.com 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
st.executeQuery() got stuck
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: st.executeQuery() got stuck
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
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 dyre.tjeldv...@oracle.comwrote: 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
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) SELECTG_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 FROMINTERNAL.G_CUST_OPPORTUNITY, INTERNAL.G_XREF_EVNT_OPPR, INTERNAL.G_CORE_PARTYA, INTERNAL.G_CORE_PARTYB, INTERNAL.G_XREF_EVNT_PARTY, INTERNAL.G_EVNT_MASTER LEFTOUTERJOININTERNAL.G_CORE_DOCUMENT ONG_EVNT_MASTER_ID=G_CORE_DOCUMENT_MEETING_ID WHEREG_EVNT_MASTER_ID=G_XREF_EVNT_OPPR_EVENT_ID ANDG_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 ANDG_XREF_EVNT_PARTY_EVENT_ID=G_EVNT_MASTER_ID ANDG_EVNT_MASTER_TYPE='Contact Event' ANDG_XREF_EVNT_PARTY_TYPE='Primary Contact' ANDG_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 dyre.tjeldv...@oracle.com 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
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 dyre.tjeldv...@oracle.comwrote: 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) SELECTG_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 FROMINTERNAL.G_CUST_**OPPORTUNITY, INTERNAL.G_XREF_EVNT_OPPR, INTERNAL.G_CORE_PARTYA, INTERNAL.G_CORE_PARTYB, INTERNAL.G_XREF_EVNT_PARTY**, INTERNAL.G_EVNT_MASTER LEFTOUTERJOININTERNAL.G_**CORE_DOCUMENT ONG_EVNT_MASTER_ID=G_CORE_**DOCUMENT_MEETING_ID WHEREG_EVNT_MASTER_ID=G_**XREF_EVNT_OPPR_EVENT_ID ANDG_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 ANDG_XREF_EVNT_PARTY_EVENT_**ID=G_EVNT_MASTER_ID ANDG_EVNT_MASTER_TYPE='**Contact Event' ANDG_XREF_EVNT_PARTY_TYPE='**Primary Contact' ANDG_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.htmlhttp://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,
Re: Is there some way to shut down a Derby database faster?
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: Is there some way to shut down a Derby database faster?
On Fri, Jul 5, 2013 at 12:44 AM, Bryan Pendleton bpendleton.de...@gmail.com 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?
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 trej...@trypticon.org wrote: On Fri, Jul 5, 2013 at 12:44 AM, Bryan Pendleton bpendleton.de...@gmail.com 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
