db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-1482) Update triggers on tables with blob columns stream blobs into memory even when the blobs are not referenced/accessed.
Date Fri, 19 Feb 2010 06:45:27 GMT

     [ https://issues.apache.org/jira/browse/DERBY-1482?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Mamta A. Satoor updated DERBY-1482:
-----------------------------------

    Attachment: TriggerTests_ver1_stat.txt
                TriggerTests_ver1_diff.txt

The attached patch, TriggerTests_ver1_diff.txt, includes junit test which has few test fixtures
to test triggers on table with LOB columns. Each and every one of those tests run into OOM
probably because we are streaming the before and after values of LOB columns even when they
are not being referenced by the trigger action. More code investigation is needed to identify
different trigger cases where we should not stream the LOBs. 

I have added this junit test, TriggerTests.java, in org.apache.derbyTesting.functionTests.tests.memory
package. It has been added to the _Suite in that package but has been commented for now with
the following comments.
        //Disable following TriggerTests until DERBY-1482 has been fixed.
        //Without that fix, the test will run into OOM errors for all
        //the test fixtures. This test is written for triggers defined
        //on table with LOB columns. No matter whether the LoB columns
        //are touched in the trigger action, it appears that Derby is
        //streaming the before and after values of LOB columns. Once
        //the streaming problem has been resolved, we should be able
        //to uncomment the following test.
        //suite.addTest(TriggerTests.suite());
The org.apache.derbyTesting.functionTests.tests.memory._Suite gets run as part of the lowmem
suite. lowmem suite runs all the tests in it with 16M heap. I have tweaked the page cache
size for TriggerTests to 100 pages. The reason for this is that the test deals with pretty
big LOBs(320M each) and we will soon run into OOM because of no memory left for page cache
if we ran with the default page cache of 1000 against the 16M heap.

For now, this suite runs only in embedded mode. When I ran with network server mode, I got
following error. But for now, I just want to focus on the OOM failures. Once they are fixed,
we can probably look at enabling this test for network server mode.
java.sql.SQLNonTransientConnectionException: Network protocol exception: actual code point,
4,692, does not match expected code point, 9,224.  The connection has been terminated.


One interesting exception I have noticed for few of the test fixtures (in addition to OOM)
looks something like following.
java.io.FileNotFoundException: system\wombat\log\log211.dat (The system cannot find the file
specified.)
        at java.io.FileInputStream.open(Native Method)
        at java.io.FileInputStream.<init>(FileInputStream.java:112)
        at org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests.copySingleFile(PrivilegedFileOpsForTests.java:245)
        at org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests.recursiveCopy(PrivilegedFileOpsForTests.java:222)
        at org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests.access$000(PrivilegedFileOpsForTests.java:47)
        at org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests$6.run(PrivilegedFileOpsForTests.java:184)
        at java.security.AccessController.doPrivileged(AccessController.java:251)
        at org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests.copy(PrivilegedFileOpsForTests.java:182)
        at org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests.recursiveCopy(PrivilegedFileOpsForTests.java:220)
        at org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests.access$000(PrivilegedFileOpsForTests.java:47)
        at org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests$6.run(PrivilegedFileOpsForTests.java:184)
        at java.security.AccessController.doPrivileged(AccessController.java:251)
        at org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests.copy(PrivilegedFileOpsForTests.java:182)
        at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:133)
        at junit.framework.TestResult$1.protect(TestResult.java:106)
        at junit.framework.TestResult.runProtected(TestResult.java:124)
        at junit.framework.TestResult.run(TestResult.java:109)
        at junit.framework.TestCase.run(TestCase.java:118)
        at junit.framework.TestSuite.runTest(TestSuite.java:208)
        at junit.framework.TestSuite.run(TestSuite.java:203)
        at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22)
        at junit.extensions.TestSetup$1.protect(TestSetup.java:19)
        at junit.framework.TestResult.runProtected(TestResult.java:124)
        at junit.extensions.TestSetup.run(TestSetup.java:23)
        at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
        at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22)
        at junit.extensions.TestSetup$1.protect(TestSetup.java:19)
        at junit.framework.TestResult.runProtected(TestResult.java:124)
        at junit.extensions.TestSetup.run(TestSetup.java:23)
        at junit.framework.TestSuite.runTest(TestSuite.java:208)
        at junit.framework.TestSuite.run(TestSuite.java:203)
        at junit.textui.TestRunner.doRun(TestRunner.java:116)
        at junit.textui.TestRunner.start(TestRunner.java:172)
        at junit.textui.TestRunner.main(TestRunner.java:138)
I do not see this exception for first test fixtures. The reason behind these additional exceptions
may be that we are running into OOM and jvm can start behaving unpredictably after running
into OOMs. To verify that the problem is not with my tests, I changed the LOB size to 60K
from 320M and ran with default heap and default page cache size and didn't run into above
exceptions. Of course there were no OOMs either because we have enough heap memory to be able
to stream the LOBs. In the patch attached, I have reverted my junit test back to use 320M
LOB data, page cache size of 100 and limited heap of 16M enforced by the lowmem suite.


The typical OOM exception for the test looks as follows
7) test1InsertBeforeTrigger(org.apache.derbyTesting.functionTests.tests.memory.TriggerTests)java.sql.SQLException:
Java exception: ': java.lang.OutOfMemoryError'.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:95)
        at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:142)
        at org.apache.derby.impl.jdbc.Util.javaException(Util.java:299)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:403)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:346)
        at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2269)
        at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:81)
        at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1321)
        at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(EmbedPreparedStatement.java:1673)
        at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(EmbedPreparedStatement.java:303)
        at org.apache.derbyTesting.functionTests.tests.memory.TriggerTests.runInsertTriggerTest(TriggerTests.java:1479)
        at org.apache.derbyTesting.functionTests.tests.memory.TriggerTests.test1InsertBeforeTrigger(TriggerTests.java:541)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:48)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)
        at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:109)
        at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22)
        at junit.extensions.TestSetup$1.protect(TestSetup.java:19)
        at junit.extensions.TestSetup.run(TestSetup.java:23)
        at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
        at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22)
        at junit.extensions.TestSetup$1.protect(TestSetup.java:19)
        at junit.extensions.TestSetup.run(TestSetup.java:23)
Caused by: java.sql.SQLException: Java exception: ': java.lang.OutOfMemoryError'.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45)
        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory40.java:119)
        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:70)
        ... 38 more
Caused by: java.lang.OutOfMemoryError
        at org.apache.derby.iapi.types.SQLBinary.readExternal(SQLBinary.java:421)
        at org.apache.derby.iapi.types.SQLBinary.getValue(SQLBinary.java:244)
        at org.apache.derby.iapi.types.SQLBinary.loadStream(SQLBinary.java:686)
        at org.apache.derby.impl.sql.execute.DMLWriteResultSet.objectifyStreams(DMLWriteResultSet.java:156)
        at org.apache.derby.impl.sql.execute.DMLWriteResultSet.getNextRowCore(DMLWriteResultSet.java:135)
        at org.apache.derby.impl.sql.execute.InsertResultSet.open(InsertResultSet.java:494)
        at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:436)
        at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:317)
        at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1232)
        ... 31 more

In order to run the junit test to repro the problem, first you will need to uncomment the
test from  org.apache.derbyTesting.functionTests.tests.memory._Suite and compile the code.
Next, you can run the lowmem suite one of the following 2 ways
1)You can run it as an ant target
ant junit-lowmem
2)java -Xmx16M junit.textui.TestRunner org.apache.derbyTesting.functionTests.tests.memory._Suite
Either one of above runs should show you OOMs for each of the test fixtures in TriggerTests.java

Following documents the different trigger and LOB combinations that are being tested in TriggerTests.java.
It also shows the test fixture name for that trigger and LOB test combination
                                                 NO REFERENCE CLAUSE in trigger definition
AFTER INSERT trigger        test5InsertAfterTriggerNoReferencingClause 
BEFORE INSERT trigger    test5InsertBeforeTriggerNoReferencingClause 
AFTER DELETE trigger       test5DeleteAfterTriggerNoReferencingClause 
BEFORE DELETE trigger    test5DeleteBeforeTriggerNoReferencingClause 
AFTER UPDATE trigger       test5UpdateAfterTriggerNoReferencingClause 
BEFORE UPDATE trigger   test5UpdateBeforeTriggerNoReferencingClause 


                       tirgger created on non-LOB column and no LOB is referenced in trigger
action(*)   
AFTER INSERT trigger       test1InsertAfterTrigger 
BEFORE INSERT trigger    test1InsertBeforeTrigger 
AFTER DELETE trigger       test1DeleteAfterTrigger 
BEFORE DELETE trigger   test1DeleteBeforeTrigger 
AFTER UPDATE trigger      test1UpdateAfterTrigger 
BEFORE UPDATE trigger   test1UpdateBeforeTrigger 


                       tirgger created on non-LOB column and LOB is accessed in trigger action(+)

AFTER INSERT trigger        test2InsertAfterTriggerAccessLOB 
BEFORE INSERT trigger     test2InsertBeforeTriggerAccessLOB 
AFTER DELETE trigger       test2DeleteAfterTriggerAccessLOB 
BEFORE DELETE trigger    test2DeleteBeforeTriggerAccessLOB 
AFTER UPDATE trigger       test2UpdateAfterTriggerAccessLOB 
BEFORE UPDATE trigger   test2UpdateBeforeTriggerAccessLOB 



                  tirgger created on non-LOB column and LOB is getting UPDATEed in trigger
action(+) 
AFTER INSERT trigger        test2InsertAfterTriggerUpdatedLOB 
BEFORE INSERT trigger    No test - BEFORE triggers do not allow INSERT/UPDATE/DELETE in trigger
action 
AFTER DELETE trigger       No test - the row has been deleted and hence no way for trigger
action to modify the LOB of the deleted row 
BEFORE DELETE trigger    No test - BEFORE triggers do not allow INSERT/UPDATE/DELETE in trigger
action 
AFTER UPDATE trigger       test2UpdateAfterTriggerUpdatedLOB 
BEFORE UPDATE trigger   No test - BEFORE triggers do not allow INSERT/UPDATE/DELETE in trigger
action 

                       tirgger created on LOB column and no LOB is referenced in trigger action
 (*) 
AFTER INSERT trigger        No test because INSERT trigger is defined on a row, not a column.
test1InsertAfterTrigger will suffice. 
BEFORE INSERT trigger     No test because INSERT trigger is defined on a row, not a column.
test1InsertBeforeTrigger will suffice. 
AFTER DELETE trigger       No test because DELETE trigger is defined on a row, not a column.
test1DeleteAfterTrigger will suffice. 
BEFORE DELETE trigger    No test because DELETE trigger is defined on a row, not a column.
test1DeleteBeforeTrigger will suffice. 
AFTER UPDATE trigger       test3UpdateAfterTrigger 
BEFORE UPDATE trigger   test3UpdateBeforeTrigger                       


                       tirgger created on LOB column and LOB is accessed in trigger action(+)

AFTER INSERT trigger       No test because INSERT trigger is defined on a row, not a column.
test2InsertAfterTriggerAccessLOB will suffice. 
BEFORE INSERT trigger   No test because INSERT trigger is defined on a row, not a column.
test2InsertBeforeTriggerAccessLOB will suffice. 
AFTER DELETE trigger     No test because DELETE trigger is defined on a row, not a column.
test2DeleteAfterTriggerAccessLOB will suffice. 
BEFORE DELETE trigger  No test because DELETE trigger is defined on a row, not a column. test2DeleteBeforeTriggerAccessLOB
will suffice. 
AFTER UPDATE trigger     test4UpdateAfterTriggerAccessLOB 
BEFORE UPDATE trigger  test4UpdateBeforeTrigger 


                       tirgger created on LOB column and LOB is getting UPDATEed in trigger
action(+) 
AFTER INSERT trigger    No test because INSERT trigger is defined on a row, not a column.
test2InsertAfterTriggerUpdatedLOB will suffice. 
BEFORE INSERT trigger  No test - BEFORE triggers do not allow INSERT/UPDATE/DELETE in trigger
action 
AFTER DELETE trigger   No test - the row has been deleted and hence no way for trigger action
to modify the LOB of the deleted row 
BEFORE DELETE trigger  No test - BEFORE triggers do not allow INSERT/UPDATE/DELETE in trigger
action 
AFTER UPDATE trigger     test4UpdateAfterTriggerUpdatedLOB 
BEFORE UPDATE trigger  No test - BEFORE triggers do not allow INSERT/UPDATE/DELETE in trigger
action   



(*) Has two versions of this trigger+LOB combination. The trigger action is coded as either
a stored procedure call or as a direct SQL statement. 
(+)These test cases do not have stored procedure version of them. The reason is that the trigger
action references LOB columns and at this point, there is no way to pass LOB parameters to
a stored procedure 



> Update triggers on tables with blob columns stream blobs into memory even when the blobs
are not referenced/accessed.
> ---------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-1482
>                 URL: https://issues.apache.org/jira/browse/DERBY-1482
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.2.1.6
>            Reporter: Daniel John Debrunner
>            Priority: Minor
>         Attachments: derby1482DeepCopyAfterTriggerOnLobColumn.java, derby1482Repro.java,
derby1482ReproVersion2.java, TriggerTests_ver1_diff.txt, TriggerTests_ver1_stat.txt
>
>
> Suppose I have 1) a table "t1" with blob data in it, and 2) an UPDATE trigger "tr1" defined
on that table, where the triggered-SQL-action for "tr1" does NOT reference any of the blob
columns in the table. [ Note that this is different from DERBY-438 because DERBY-438 deals
with triggers that _do_ reference the blob column(s), whereas this issue deals with triggers
that do _not_ reference the blob columns--but I think they're related, so I'm creating this
as subtask to 438 ]. In such a case, if the trigger is fired, the blob data will be streamed
into memory and thus consume JVM heap, even though it (the blob data) is never actually referenced/accessed
by the trigger statement.
> For example, suppose we have the following DDL:
>     create table t1 (id int, status smallint, bl blob(2G));
>     create table t2 (id int, updated int default 0);
>     create trigger tr1 after update of status on t1 referencing new as n_row for each
row mode db2sql update t2 set updated = updated + 1 where t2.id = n_row.id;
> Then if t1 and t2 both have data and we make a call to:
>     update t1 set status = 3;
> the trigger tr1 will fire, which will cause the blob column in t1 to be streamed into
memory for each row affected by the trigger. The result is that, if the blob data is large,
we end up using a lot of JVM memory when we really shouldn't have to (at least, in _theory_
we shouldn't have to...).
> Ideally, Derby could figure out whether or not the blob column is referenced, and avoid
streaming the lob into memory whenever possible (hence this is probably more of an "enhancement"
request than a bug)... 

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message