db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: [jira] Commented: (DERBY-3479) Changed/unexpected query plan when running test 'lang/predicatePushdown.sql'
Date Wed, 05 Mar 2008 18:18:30 GMT

Knut Anders Hatlen (JIRA) wrote:
>     [ https://issues.apache.org/jira/browse/DERBY-3479?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12575373#action_12575373
] 
> 
> Knut Anders Hatlen commented on DERBY-3479:
> -------------------------------------------
> 
> I noticed that other tests that depend on stable query plans (wisconsin and StalePlansTest)
set derby.storage.checkpointInterval=100000. I tried to add this property to predicatePushdown_derby.properties,
and that seemed to stabilize the test.
> 
> StalePlansTest contains some magic to flush the row count for the tables. I don't understand
exactly what it does, in particular I don't understand why "select count(c1) from flusher"
is needed to make the row count for other tables than flusher visible. Perhaps changing the
concurrency/timing in the buffer manager somehow changes when the row count is flushed, and
thereby it makes the optimizer choose a different plan?
> 
>> Changed/unexpected query plan when running test 'lang/predicatePushdown.sql'
As I remember it, this is how row count estimates work.  Delta's are 
maintained per page resident in the cache, rather than centralized in 
order to not add a single point of contention for every insert/delete in 
a table.  An update of the single centralized counter is triggered in a 
number of ways:
1) language forces the update, usually as a result of doing a full scan 
and thus knowing the exact row count.
2) The incremental changes in a single page are "significant", I think 
more than 10% of the current row count.
3) When the page is written to disk (see CachedPage!writePage()). 
Writes happen either when page is forced from cache as part of 
replacement or when a checkpoint happens.

So I think variable query plans in the tests come from:
1) variable row counts because checkpoints take different amounts of 
time on different machines.  Often the tests do some sort of table setup
in the beginning generating a lot of log and then a checkpoint and then 
the checkpoint may be happening in the background while the test has 
gotten to the actual query testing phase. I assume the replacement 
policy is not variable.  So solutions include manipulating checkpoint to 
not happen
during test, call offline compress table on all tables before query 
which will update all statistics and remove dependency on any 
outstanding per page row count updates (offline creates new containers 
and recreates indexes/statistics
2) variable amount of time spent in optimizer if query is complicated 
enough to stop before costing all plans.  Workaround might be to just
set the optimizer to not timeout.

With the .sql tests in my experience often the problem is that we don't 
have a reasonable set of test data in the tables, so row estimates off 
by 1 actually affect the plan.  Usually both plans are reasonable - 
usual case is one table on left or right does not actually matter.  As
suggested in this case the test probably does not care about most of
the plan, just that the predicate pushdown happened.  But it is 
sometimes interesting to understand why a new plan is being consistently
chosen as part of new change.

>> ----------------------------------------------------------------------------
>>
>>                 Key: DERBY-3479
>>                 URL: https://issues.apache.org/jira/browse/DERBY-3479
>>             Project: Derby
>>          Issue Type: Bug
>>          Components: Regression Test Failure
>>    Affects Versions: 10.4.0.0
>>         Environment: OS: Solaris 10 6/06 s10x_u2wos_09a X86 64bits - SunOS 5.10 Generic_118855-14
>> JVM: Sun Microsystems Inc., java version "1.6.0_04", Java(TM) SE Runtime Environment
(build 1.6.0_04-b12), Java HotSpot(TM) Client VM (build 10.0-b19, mixed mode)
>>            Reporter: Ole Solberg
>>
>> Seen in tinderbox since r631930.
>> See e.g. http://dbtg.thresher.com/derby/test/tinderbox_trunk16/jvm1.6/testing/testlog/SunOS-5.10_i86pc-i386/631932-derbyall_diff.txt
:
>> *** Start: predicatePushdown jdk1.6.0_04 derbyall:derbylang 2008-02-28 14:02:49 ***
>> 9285 del
>> < 		Rows seen from the left = 20
>> 9285a9285
>>> 		Rows seen from the left = 10
>> 9297 del
>> < 			Rows seen from the right = 20
>> 9297a9297
>>> 			Rows seen from the right = 10
>> 9299 del
>> < 			Rows returned = 20
>> 9299a9299
>>> 			Rows returned = 10
>> .
>> .
>> .
> 


Mime
View raw message