db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kristian Waagan <kristian.waa...@oracle.com>
Subject Re: Transaction contexts and log flushing
Date Tue, 03 Aug 2010 19:31:00 GMT
  On 03.08.2010 19:28, Mike Matrigali wrote:

Hi Mike,

Thank you for the feedback. See my comments below, especially the one 
regarding flushLogOnCommit.

> Kristian Waagan wrote:
>> Hi,
>> When working on an experiment for automatic index statistics 
>> (re)generation, I was exposed to the Derby transaction API.
>> Dan filed an issue [1] suggesting to clean up this API, and I can 
>> give my +1 to that :) In places the comments and actual usage aren't 
>> in sync, and missing functionality of the lcc 
>> (LanguageConnectionContext) can be obtained by working directly on 
>> the tc (TransactionController). One such example is nested read-write 
>> user transactions, which doesn't seem to be supported through the lcc 
>> (although the lcc API suggests so), but is used in some places by 
>> working with the tc.
>> I tried to use a nested read-write user transaction to write index 
>> statistics to the data dictionary, and was surprised to find that the 
>> changes were lost even though I committed the transaction (they 
>> survive if I do a proper shutdown). Turns out Derby uses the concept 
>> of transaction contexts, and the following are defined in XactFactory:
>> Now, the XactFactory also has this method:
>>     /**
>>         Decide if a transaction of this contextId needs to flush the 
>> log when
>>         it commits
>>     */
>>     public boolean flushLogOnCommit(String contextName)
>>     {
>>         //
>>         // if this is a user transaction, flush the log
>>         // if this is an internal or nested top transaction, do not
>>         // flush, let it age out.
>>         //
>>         return (contextName == USER_CONTEXT_ID ||
>>                 contextName.equals(USER_CONTEXT_ID));
>>     }
>> Most of this code is rather old, so I haven't found much history. My 
>> questions:
>>  1) Is using a nested read-write user transaction simply wrong in 
>> this case?
>>     (nested because I want to release the locks on the data 
>> dictionary as soon as possible)  There is the problem with locks not
> being compatible - see below.  I think this is what mamta kept running
> into in what she tried.  usually it is likely not to be a problem but
> if user happens to have accumulated some system catalog locks then
> there are issues (and this case I think comes up in a bunch of our
> tests).  I also see an issue with some user with a big table complaining
> when his simple query takes a long time waiting on the stats during
> compile.

Although the current prototype code is very crude, here's a brief 
  o work is queued by a thread compiling a statement. After queuing  the 
thread continues its normal work; compiling the statement and 
potentially executing it.
  o generating the statistics is done by a separate thread created 
on-demand (in it's own transaction);
     - if there is no thread, one is created
     - if there is more work when the  thread finishes the current unit 
of work, it will continue with the next item in the queue
     - if there is no more work, the thread dies
  o work is scheduled based on tables; all indexes are regenerated, not 
individual ones
  o writing the stats to the system tables is done by a user thread 
compiling a statement

In the current prototype, there has to be a mechanism for a user thread 
to detect that there are new statistics to be written. One issue right 
now, is that this happens at a time where the statement is already 
optimized (thus loosing out on the new statistics). I'm not sure if this 
is a problem or not, the new stats will be picked up the next time the 
statement is compiled.

> Ultimately the solution I think would work best is some generic way
> to queue background work from the language layer, similar to what can
> be done from the storage layer to the daemon thread.  This would avoid
> making a query wait while a full scan of the entire table is done
> to rebuild statistics.  A separate thread avoids a lot of the deadlock
> issues of a nested user thread.

Yes, this is what I have tried to do, although the "background daemon" 
is very specific.

> The issues to solve would be:
> o how to make sure only correct permissions are enforced on the 
> background work.  I think it would be best at least in first 
> implementation if it was only possible for internal systems to queue
> to this background runner.

Ignored for now, the worker can only generate statistics and the work is 
queued from within GenericStatement.

> o should the work survive a shutdown?  It would be simple enough to
>   track the work in a table, but is it worth it.

Do you mean the work queue?
If so, I feel that it isn't necessary, as work is queued as determined 
by the logic "detecting" stale/missing stats (lots of work to do here I 
Maybe saving intermediate results can be useful for huge tables, but 
then we have to handle the issue of stale intermediate results too...

> o I don't think we should add another thread always to handle this
>   background work as we already get complaints about the existing
>   1 thread per db.  Best would be some system that could add the
>   and delete the thread as needed - maybe even add more than one 
> thread if it could determine it was appropriate on the hardware - or 
> maybe based on some configuration param.

This is what is done in the prototype, but I haven't looked into 
configuration. One concern is that index regeneration will poison the 
page cache, but avoiding this is probably a big thing. I think we may 
also have to tune how much resources (CPU, IO) are used for this 
background activity.
>>  2) Is the method flushLogOnCommit doing the right thing?
> I believe flushLogOnCommit is doing the right thing for the current 
> usage of nested user transactions.  The issue is one of performance. In
> the current usage these transactions are only used for internal things
> where it is ok for either the transaction work to be backed out or 
> committed, even if the internal transaction commits.  All the work
> based on the internal transaction is logged after the internal 
> transaction.  So if this transaction is lost then it must be that all
> subsequent work is also lost.

Well, the updates to the data dictionary are lost even though I have 
committed the parent transaction. They survive a shutdown if I do a 
proper shutdown. I cannot explain this, maybe I have a severe bug in the 
prototype (I did pretty much copy the code from InsertResultSet though).

> What it is doing is avoiding a synchonous write on the log for each 
> internal transaction.  This not only benefits by avoiding the write, but
> it is likely that it will increase the "group" of log records that will
> be served by the subsequent real user transaction commit.  I believe the
> usual usage for this read/write transaction is the update of the "block"
> of numbers used for generated keys.  So the expectation is that usually
> the parent user transaction will commit soon.

Okay, so the nested transaction implementation is pretty much tailed to 
fit work related to identity columns?
I guess all contexts except USER_CONTEXT_ID are considered internal.

The prototype uses a nested transaction slightly different - it would be 
best if the work done by the nested transaction would be synced. No real 
harm is done by loosing the updates though, Derby just have to do the 
work again (may affect performance).

>> I haven't checked yet, but it is also important to know if the update 
>> locks of the nested user transaction is incompatible with the parent 
>> user transaction (to avoid deadlock when using NO_WAIT).
> The locks are not compatible.  See following documentation in
> TransactionController.java!getNestedUserTransaction()
>    * <p>
>    * The locks in the child transaction of a readOnly nested user 
> transaction
>    * will be compatible with the locks of the parent transaction.  The
>    * locks in the child transaction of a non-readOnly nested user 
> transaction
>    * will NOT be compatible with those of the parent transaction - 
> this is
>    * necessary for correct recovery behavior.
>    * <p>



>> And thanks to Mamta for the writeup regarding the index stats issue :)
>> At the moment I'm trying to implement a prototype for a first step of 
>> a hybrid solution, where the statistics generation is done in a 
>> separate thread. The generation is initialized from the user thread 
>> when compiling a statement, and writing new stats back is also done 
>> in a user thread. There are several issues to resolve, but I'll see 
>> how far I get before abandoning the approach (will attach 
>> code/comments to the appropriate JIRA later).
>> Thanks,

View raw message