db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "ASF subversion and git services (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-5680) indexStat daemon processing tables over and over even when there are no changes in the tables
Date Fri, 28 Jun 2013 18:24:22 GMT

    [ https://issues.apache.org/jira/browse/DERBY-5680?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13695629#comment-13695629
] 

ASF subversion and git services commented on DERBY-5680:
--------------------------------------------------------

Commit 1497868 from [~mamtas]
[ https://svn.apache.org/r1497868 ]

DERBY-5680( indexStat daemon processing tables over and over even when there are no changes
in the tables )

Backporting the 3 commits that went in for DERBY-5680 to 10.8. The 3 commits were 1340549,
1341622, 1341629. The first two commits were easy to backport using svn merge command but
the third commit 1341629 ran into conflicts. For that backport, hand made the changes since
there were not too many changes.

The changes for this jira has added a new property derby.storage.indexStats.debug.keepDisposableStats.
The intention of the property is that if the property is set to true, we do not delete the
orphaned/disposable stats. If the property is set to false, the orphaned/disposable stats
will get dropped by the index stats daemon. Currently known reasons for orphaned/disposable
stats are
1)DERBY-5681(When a foreign key constraint on a table is dropped, the associated statistics
row for the conglomerate is not removed). Fix for this has been backported all the way to
10.3
2)DERBY-3790(Investigate if request for update statistics can be skipped for certain kind
of indexes, one instance may be unique indexes based on one column.) Fix for this is in 10.9
and higher

A junit test was added for this new property but it went in as part of DERBY-3790. The name
of the junit test is store.KeepDisposableStatsPropertyTest. Had to make changes to this test
to backport it to 10.8 but without the fix for DEBRY-3790 and with the absence of drop statistics
procedure, the test really does not make much sense for 10.8 codeline. The test uses drop
statistics procedure and it is mainly testing DERBY-3790 to make sure that the orphaned stats
are being deleted or left behind based on whether the property is set to true or false. But
since we do not have drop statistics procedure and we do not have DERBY-3790 fixed in 10.8,
we can't really meaningfully run the KeepDisposableStatsPropertyTest in 10.8. In any case,
I have changed the test so that atleast it will not fail in 10.8 but it is not able to truly
test the property. May be we can test this property through upgrade suite where we will create
orphaned stats because of DERBY-5681 on older releases and we will find that when the property
is set to true, even after upgrade, we will have orphaned stats but when property is set to
false, after upgrade, orphaned stats are deleted.
                
> indexStat daemon processing tables over and over even when there are no changes in the
tables
> ---------------------------------------------------------------------------------------------
>
>                 Key: DERBY-5680
>                 URL: https://issues.apache.org/jira/browse/DERBY-5680
>             Project: Derby
>          Issue Type: Bug
>          Components: Store
>    Affects Versions: 10.8.2.2, 10.9.1.0
>            Reporter: Brett Bergquist
>            Assignee: Kristian Waagan
>             Fix For: 10.9.1.0
>
>         Attachments: derby-5680-1a-drop_orphaned_stats.diff, derby-5680-1b-remove_disposable_stats.diff,
derby-5680-2a-remove_redundant_check.diff, derby-5680-3a-rename_debug_property.diff, DERBY5680_backportTo108_patch1_diff.txt,
DERBY5680_backportTo108_patch1_stat.txt
>
>
> I think there is something wrong with the indexStats.     
> The problem happens on many tables in the database.   
> None of these tables are changing however, no inserts or deletes or updates.  They are
being queried, however.  
> Here is one such table.
> Here is the statistics for this table:
> Table (Index)     2              3
> ACCOUNTTABLE_CONFIG_BUNDLE (SQL081029110443810)          numunique= 38390 numrows= 38390
    2012-03-30 13:00:26.84
> ACCOUNTTABLE_CONFIG_BUNDLE (SQL100922215819290)          numunique= 38390 numrows= 38390
    2012-03-30 13:00:26.917
> There are in fact 38390 rows in the table.
> Here is some of the indexStat trace:
> Fri Mar 30 12:47:12 EDT 2012 Thread[DRDAConnThread_43,5,main] {istat} "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE":
update scheduled, reason=[t-est=38390, i-est=2355 => cmp=2.7912562815443245] (queueSize=12)
> Fri Mar 30 12:47:48 EDT 2012 Thread[index-stat-thread,5,main] {istat} "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE":
wrote stats for index SQL081029110443810 (fc33890d-011d-491f-3d8c-0000376d74d3): rows=38390,
card=[38390]
> Fri Mar 30 12:47:48 EDT 2012 Thread[index-stat-thread,5,main] {istat} "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE":
wrote stats for index SQL100922215819290 (75608675-012b-3c38-b55c-000043ea6398): rows=38390,
card=[38390]
> Fri Mar 30 12:47:48 EDT 2012 Thread[index-stat-thread,5,main] {istat} "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE":
scan durations (c30625=91ms,c30625=98ms)
> Fri Mar 30 12:47:48 EDT 2012 Thread[index-stat-thread,5,main] {istat} "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE":
generation complete (210 ms)
> Fri Mar 30 12:47:49 EDT 2012 Thread[DRDAConnThread_44,5,main] {istat} "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE":
update scheduled, reason=[t-est=38390, i-est=2355 => cmp=2.7912562815443245] (queueSize=19)
> Fri Mar 30 12:48:25 EDT 2012 Thread[index-stat-thread,5,main] {istat} "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE":
wrote stats for index SQL081029110443810 (fc33890d-011d-491f-3d8c-0000376d74d3): rows=38390,
card=[38390]
> Fri Mar 30 12:48:25 EDT 2012 Thread[index-stat-thread,5,main] {istat} "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE":
wrote stats for index SQL100922215819290 (75608675-012b-3c38-b55c-000043ea6398): rows=38390,
card=[38390]
> Fri Mar 30 12:48:25 EDT 2012 Thread[index-stat-thread,5,main] {istat} "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE":
scan durations (c30625=93ms,c30625=95ms)
> Fri Mar 30 12:48:25 EDT 2012 Thread[index-stat-thread,5,main] {istat} "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE":
generation complete (211 ms)
> Fri Mar 30 12:48:25 EDT 2012 Thread[DRDAConnThread_50,5,main] {istat} "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE":
update scheduled, reason=[t-est=38390, i-est=2355 => cmp=2.7912562815443245] (queueSize=18)
> Fri Mar 30 12:48:57 EDT 2012 Thread[index-stat-thread,5,main] {istat} "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE":
wrote stats for index SQL081029110443810 (fc33890d-011d-491f-3d8c-0000376d74d3): rows=38390,
card=[38390]
> Fri Mar 30 12:48:57 EDT 2012 Thread[index-stat-thread,5,main] {istat} "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE":
wrote stats for index SQL100922215819290 (75608675-012b-3c38-b55c-000043ea6398): rows=38390,
card=[38390]
> Fri Mar 30 12:48:57 EDT 2012 Thread[index-stat-thread,5,main] {istat} "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE":
generation complete (243 ms)
> Fri Mar 30 12:49:27 EDT 2012 Thread[DRDAConnThread_56,5,main] {istat} "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE":
update scheduled, reason=[t-est=38390, i-est=2355 => cmp=2.7912562815443245] (queueSize=20)
> Fri Mar 30 12:49:36 EDT 2012 Thread[index-stat-thread,5,main] {istat} "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE":
wrote stats for index SQL081029110443810 (fc33890d-011d-491f-3d8c-0000376d74d3): rows=38390,
card=[38390]
> Fri Mar 30 12:49:37 EDT 2012 Thread[index-stat-thread,5,main] {istat} "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE":
wrote stats for index SQL100922215819290 (75608675-012b-3c38-b55c-000043ea6398): rows=38390,
card=[38390]
> Fri Mar 30 12:49:37 EDT 2012 Thread[index-stat-thread,5,main] {istat} "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE":
scan durations (c30625=111ms,c30625=108ms)
> Fri Mar 30 12:49:37 EDT 2012 Thread[index-stat-thread,5,main] {istat} "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE":
generation complete (238 ms)
> Fri Mar 30 12:49:37 EDT 2012 Thread[DRDAConnThread_49,5,main] {istat} "PKG_9145E_V1"."ACCOUNTTABLE_CONFIG_BUNDLE":
update scheduled, reason=[t-est=38390, i-est=2355 => cmp=2.7912562815443245] (queueSize=18)
> As can be seen, the "i-est" appears to be wrong and is used over and over even though
the statistics for the indexes have been updated.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira

Mime
View raw message