[
https://issues.apache.org/jira/browse/DERBY-5680?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mamta A. Satoor updated DERBY-5680:
-----------------------------------
Attachment: DERBY5680_backportTo108_patch1_stat.txt
DERBY5680_backportTo108_patch1_diff.txt
I have been able to backport(with some issues as explained below) 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, I 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. I think the intention 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. Also, if my understanding is correct, then
we can end up with orphaned/disposable stats for
1)DERBY-5681(When a foreign key constraint on a table is dropped, the
associated statistics row for the conglomerate is not removed). 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.) This fix 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. I tried copying that test from trunk 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. Just a thought.
I have attached the patch for 10.8 backport and KeepDisposableStatsPropertyTest
(although it doesn't really test the property in it's true sense because of the
absence of DERBY-3790 fix, but may be we can use DERBY-5681 to create test
cases in upgrade suite).
I am running the junit suite and derbyall with the patch. Will post the results
of it when it is done.
I haven't spent too much time understanding the impact of the backport on 10.8
codeline, ie can it break something else? My gut feeling based on the time I
have spent so far is that it should work ok in 10.8. I will look more into it.
I will appreciate if others can also take a look and share their thoughts about
10.8 backport.
> 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
