On Monday, January 12, 2015 at 2:27:42 PM UTC-6, Ken Jorissen wrote:
>
>
> On Monday, January 5, 2015 at 2:58:17 AM UTC-6, Noel Grandin wrote:
>>
>> Hi 
>>
>> Thanks for your problem report and investigation. 
>>
>> I have committed a fix for this. 
>>
>> Regards, Noel. 
>>
>
> The current logic still doesn't reset logSizeBase and I think that is key. 
> I also think that there are two "happy path" tests and the first one is 
> unnecessary because the second one will always be true if the first one is. 
> I think PageStore.commit() should look like:
>
> ---
>
> public synchronized void commit(Session session) {
>
>         checkOpen();
>
>         openForWriting();
>
>         log.commit(session.getId());
>
>         long size = log.getSize();
>
>         if (size - logSizeBase > maxLogSize / 2) {
>
>             checkpoint();
>
>             logSizeBase = log.getSize();
>
>             if (logSizeBase < size || size < maxLogSize) {
>
>                 ignoreBigLog = false;
>
>                 return;
>
>             }
>
>             if (!ignoreBigLog) {
>
>                 ignoreBigLog = true;
>
>                 trace.error(null,
>
>                         "Transaction log could not be truncated; size: " +
>
>                         (newSize / 1024 / 1024) + " MB");
>
>             }
>
>         }
>
>     }
>
> ---
> The above gets you the original behavior (r4138) back with the only 
> addition being the one-shot logging with that one-shot being reset when the 
> "happy path" happens. 
>
>
I wrote a program (it is attached) that starts a transaction on one table 
and then does a series of inserts on the second. With that and the tracing 
cranked up you can see the checkpoint behavior I am concerned about. 

I used this to analyze the trace file:
---
awk '/\/\*SQL/ {sql=$0} /checkpoint/ {print substr(sql,0,60); print $0} 
/could not be truncated/ {print $0}' test.trace.db | less -S
---

That will write out the last SQL statement before each checkpoint, the 
checkpoint itself, and the truncation trace line.


In the implementation as of 1.3.174 you get these ranges of behavior 
assuming a transaction is pending, log.size() is growing during this - 
---
log.size() <= maxLogSize/2              : no checkpoint
maxLogSize/2 < log.size() < maxLogSize  : checkpoint for every commit
maxLogSize < log.size() < ~2*maxLogSize : no checkpoints - gap here because 
baseLogSize was reset, "Transaction log could not be truncated" is logged 
at the start
~2*maxLogSize < log.size()              : checkpoint for every commit
---

For that test case the logging looks like this - 
First checkpoint, and then one for every insert:
---
/*SQL #:1*/insert into test2 values(31058, '1234567890123456
01-17 09:03:56 pageStore: checkpoint
/*SQL #:1 t:1*/insert into test2 values(31059, '123456789012
01-17 09:03:57 pageStore: checkpoint
---

That continues up until the ignoreBigLog flag is set, logBaseSize is 
updated, and the trace message is written out:
---
/*SQL #:1*/insert into test2 values(32839, '1234567890123456
01-17 09:03:58 pageStore: checkpoint
/*SQL #:1*/insert into test2 values(32840, '1234567890123456
01-17 09:03:58 pageStore: checkpoint
01-17 09:03:58 pageStore: Transaction log could not be truncated; size: 16 
MB
---

There are no more commits until the page log grows enough again, then you 
get them for every commit:
---
/*SQL #:1*/insert into test2 values(63727, '1234567890123456
01-17 09:04:01 pageStore: checkpoint
/*SQL #:1 t:1*/insert into test2 values(63728, '123456789012
01-17 09:04:01 pageStore: checkpoint
---

That is the original performance problem.

With the 1.4.185 it is better, it now resets logBaseSize one you are over 
maxLogSize but the earlier range still does a checkpoint for every commit:
----
log.size() <= maxLogSize/2              : no checkpoint
maxLogSize/2 < log.size() < maxLogSize  : checkpoint for every commit
maxLogSize < log.size()                 : no checkpoints - gap here because 
baseLogSize was reset, "Transaction log could not be truncated" is logged 
at the start
---

With the changes I suggested in my above post against 1.3.174, the run 
analyzed the same way looks like this:
---
/*SQL #:1*/insert into test2 values(31058, '1234567890123456
01-17 09:38:10 pageStore: checkpoint
/*SQL #:1*/insert into test2 values(61968, '1234567890123456
01-17 09:38:14 pageStore: checkpoint
01-17 09:38:14 pageStore: Transaction log could not be truncated; size: 16 
MB
/*SQL #:1*/insert into test2 values(92848, '1234567890123456
01-17 09:38:17 pageStore: checkpoint
/*SQL */COMMIT;
01-17 09:38:18 pageStore: checkpoint
/*SQL */COMMIT;
01-17 09:38:19 pageStore: checkpoint
---
That is a single checkpoint at intervals of maxLogSize/2 with one warning. 

Side note, using 1.4.185 without MV_STORE=false throws an exception and 
fails:
---
org.h2.jdbc.JdbcSQLException: Timeout trying to lock table ; SQL statement:
insert into test2 values(65529, 
'1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890')
 
[50200-185]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
at org.h2.message.DbException.get(DbException.java:168)
Caught: org.h2.jdbc.JdbcSQLException: Timeout trying to lock table ; SQL 
statement:
insert into test2 values(65529, 
'1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890')
 
[50200-185]
at org.h2.command.Command.filterConcurrentUpdate(Command.java:301)
at org.h2.command.Command.executeUpdate(Command.java:256)
at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:184)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:158)
at biglogtest.BigLogTest.main(BigLogTest.java:29)
Caused by: org.h2.jdbc.JdbcSQLException: Concurrent update in table 
"TEST2": another transaction has updated or deleted the same row [90131-185]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
at org.h2.message.DbException.get(DbException.java:168)
at org.h2.mvstore.db.MVPrimaryIndex.add(MVPrimaryIndex.java:145)
at org.h2.mvstore.db.MVTable.addRow(MVTable.java:637)
at org.h2.command.dml.Insert.insertRows(Insert.java:156)
at org.h2.command.dml.Insert.update(Insert.java:114)
at org.h2.command.CommandContainer.update(CommandContainer.java:78)
at org.h2.command.Command.executeUpdate(Command.java:254)
... 3 more
Caused by: java.lang.IllegalStateException: An old transaction with the 
same id is still open: 14 [1.4.185/102]
at org.h2.mvstore.DataUtils.newIllegalStateException(DataUtils.java:773)
at org.h2.mvstore.db.TransactionStore.log(TransactionStore.java:265)
at 
org.h2.mvstore.db.TransactionStore$Transaction.log(TransactionStore.java:689)
at 
org.h2.mvstore.db.TransactionStore$TransactionMap.trySet(TransactionStore.java:1085)
at 
org.h2.mvstore.db.TransactionStore$TransactionMap.set(TransactionStore.java:1008)
at 
org.h2.mvstore.db.TransactionStore$TransactionMap.put(TransactionStore.java:986)
at org.h2.mvstore.db.MVPrimaryIndex.add(MVPrimaryIndex.java:143)
... 8 more
---

 

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
package biglogtest;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

import org.h2.tools.DeleteDbFiles;

public class BigLogTest {

	public static void main(String[] args) {
        DeleteDbFiles.execute("~", "test", true);
        
        try {
        	Connection conn1 = DriverManager.getConnection("jdbc:h2:~/test;MV_STORE=FALSE;TRACE_LEVEL_FILE=3;TRACE_MAX_FILE_SIZE=1024");
        	Connection conn2 = DriverManager.getConnection("jdbc:h2:~/test;");
        	
        	Statement stat1 = conn1.createStatement();
        	stat1.execute("create table test1(id int primary key)");
        	stat1.execute("create table test2(id int primary key, data varchar(255))");
        	
        	// Leave an open transaction on test1
        	conn1.setAutoCommit(false);
        	stat1.execute("insert into test1 values(1)");

        	// Insert rows into test2
        	Statement stat2 = conn2.createStatement();        	
        	for (long i = 0; i < 100000; i++) {
        		stat2.execute("insert into test2 values(" + i + ", '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890')");
        	}

        	stat2.close();
        	conn2.close();
        	
        	conn1.commit();
        	stat1.close();
        	conn1.close();
        } catch (Exception e) {
        	e.printStackTrace();
        	System.out.println("Caught: " + e);
        }
	}
}

Reply via email to