Derby
  1. Derby
  2. DERBY-5680

indexStat daemon processing tables over and over even when there are no changes in the tables

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.8.2.2, 10.9.1.0
    • Fix Version/s: 10.8.3.3, 10.9.1.0
    • Component/s: Store
    • Labels:
      None
    • Bug behavior facts:
      Performance, Seen in production

      Description

      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.

      1. DERBY5680_backportTo108_patch1_stat.txt
        0.6 kB
        Mamta A. Satoor
      2. DERBY5680_backportTo108_patch1_diff.txt
        24 kB
        Mamta A. Satoor
      3. derby-5680-3a-rename_debug_property.diff
        2 kB
        Kristian Waagan
      4. derby-5680-2a-remove_redundant_check.diff
        0.8 kB
        Kristian Waagan
      5. derby-5680-1b-remove_disposable_stats.diff
        16 kB
        Kristian Waagan
      6. derby-5680-1a-drop_orphaned_stats.diff
        3 kB
        Kristian Waagan

        Issue Links

          Activity

          Hide
          Kristian Waagan added a comment -

          Attaching patch 1a, which makes the istat daemon drop any orphaned statistics if it find any when processing the index cardinality statistics for a given base table.
          Maybe the logging needs to be improved?
          (i.e. log to derby.log even if istat logging is off, also need to deal with a log entry when running system procedure explicitly).

          Patch ready for initial review.
          Not sure how to test this once DERBY-5681 has been fixed.
          Tested manually using the repro provided by Brett under the mentioned issue.

          Show
          Kristian Waagan added a comment - Attaching patch 1a, which makes the istat daemon drop any orphaned statistics if it find any when processing the index cardinality statistics for a given base table. Maybe the logging needs to be improved? (i.e. log to derby.log even if istat logging is off, also need to deal with a log entry when running system procedure explicitly). Patch ready for initial review. Not sure how to test this once DERBY-5681 has been fixed. Tested manually using the repro provided by Brett under the mentioned issue.
          Hide
          Brett Bergquist added a comment -

          Applied the patch to Derby 10.8.2.2 source manually and am getting:

          Loaded com.canoga.derby.fcn.NpaSamResultsTableFunction from database jar "PCS_V1"."PCSDERBY"
          Mon Apr 02 10:52:46 EDT 2012 Thread[index-stat-thread,5,main]

          {istat} "CORE_V1"."MANAGED_HARDWARE": wrote stats for index SQL100922220307480 (36370979-012b-3c38-b55c-000043ea6398): rows=28639, card=[28639]
          Mon Apr 02 10:52:46 EDT 2012 Thread[index-stat-thread,5,main] {istat}

          "CORE_V1"."MANAGED_HARDWARE": wrote stats for index SQL100922220413140 (07558c64-012b-3c38-b55c-000043ea6398): rows=28639, card=[28639]
          Mon Apr 02 10:52:46 EDT 2012 Thread[index-stat-thread,5,main]

          {istat} "CORE_V1"."MANAGED_HARDWARE": wrote stats for index MANAGED_HARDWARE_IX_1 (b0c8433f-0133-8aa5-52b7-000043ea6398): rows=28639, card=[2]
          Mon Apr 02 10:52:46 EDT 2012 Thread[index-stat-thread,5,main] {istat}

          "CORE_V1"."MANAGED_HARDWARE": scan durations (c25409=135ms,c25409=80ms,c46241=107ms,c59265=176ms)
          Mon Apr 02 10:52:46 EDT 2012 Thread[index-stat-thread,5,main]

          {istat}

          runtime exception during normal operation
          org.apache.derby.shared.common.sanity.AssertFailure: ASSERT FAILED
          at org.apache.derby.shared.common.sanity.SanityManager.ASSERT(SanityManager.java:98)
          at org.apache.derby.impl.services.daemon.IndexStatisticsDaemonImpl.vetIndexCardinalityStats(IndexStatisticsDaemonImpl.java:1223)
          at org.apache.derby.impl.services.daemon.IndexStatisticsDaemonImpl.updateIndexStatsMinion(IndexStatisticsDaemonImpl.java:531)
          at org.apache.derby.impl.services.daemon.IndexStatisticsDaemonImpl.generateStatistics(IndexStatisticsDaemonImpl.java:325)
          at org.apache.derby.impl.services.daemon.IndexStatisticsDaemonImpl.processingLoop(IndexStatisticsDaemonImpl.java:801)
          at org.apache.derby.impl.services.daemon.IndexStatisticsDaemonImpl.run(IndexStatisticsDaemonImpl.java:717)
          at java.lang.Thread.run(Thread.java:662)
          (Skipping thread dump because of insufficient permissions:
          java.security.AccessControlException: access denied (java.lang.RuntimePermission getStackTrace))

          Exception in thread "index-stat-thread" org.apache.derby.shared.common.sanity.AssertFailure: ASSERT FAILED
          at org.apache.derby.shared.common.sanity.SanityManager.ASSERT(SanityManager.java:98)
          at org.apache.derby.impl.services.daemon.IndexStatisticsDaemonImpl.vetIndexCardinalityStats(IndexStatisticsDaemonImpl.java:1223)
          at org.apache.derby.impl.services.daemon.IndexStatisticsDaemonImpl.updateIndexStatsMinion(IndexStatisticsDaemonImpl.java:531)
          at org.apache.derby.impl.services.daemon.IndexStatisticsDaemonImpl.generateStatistics(IndexStatisticsDaemonImpl.java:325)
          at org.apache.derby.impl.services.daemon.IndexStatisticsDaemonImpl.processingLoop(IndexStatisticsDaemonImpl.java:801)
          at org.apache.derby.impl.services.daemon.IndexStatisticsDaemonImpl.run(IndexStatisticsDaemonImpl.java:717)
          at java.lang.Thread.run(Thread.java:662)
          (Skipping thread dump because of insufficient permissions:
          java.security.AccessControlException: access denied (java.lang.RuntimePermission getStackTrace))

          Show
          Brett Bergquist added a comment - Applied the patch to Derby 10.8.2.2 source manually and am getting: Loaded com.canoga.derby.fcn.NpaSamResultsTableFunction from database jar "PCS_V1"."PCSDERBY" Mon Apr 02 10:52:46 EDT 2012 Thread [index-stat-thread,5,main] {istat} "CORE_V1"."MANAGED_HARDWARE": wrote stats for index SQL100922220307480 (36370979-012b-3c38-b55c-000043ea6398): rows=28639, card= [28639] Mon Apr 02 10:52:46 EDT 2012 Thread [index-stat-thread,5,main] {istat} "CORE_V1"."MANAGED_HARDWARE": wrote stats for index SQL100922220413140 (07558c64-012b-3c38-b55c-000043ea6398): rows=28639, card= [28639] Mon Apr 02 10:52:46 EDT 2012 Thread [index-stat-thread,5,main] {istat} "CORE_V1"."MANAGED_HARDWARE": wrote stats for index MANAGED_HARDWARE_IX_1 (b0c8433f-0133-8aa5-52b7-000043ea6398): rows=28639, card= [2] Mon Apr 02 10:52:46 EDT 2012 Thread [index-stat-thread,5,main] {istat} "CORE_V1"."MANAGED_HARDWARE": scan durations (c25409=135ms,c25409=80ms,c46241=107ms,c59265=176ms) Mon Apr 02 10:52:46 EDT 2012 Thread [index-stat-thread,5,main] {istat} runtime exception during normal operation org.apache.derby.shared.common.sanity.AssertFailure: ASSERT FAILED at org.apache.derby.shared.common.sanity.SanityManager.ASSERT(SanityManager.java:98) at org.apache.derby.impl.services.daemon.IndexStatisticsDaemonImpl.vetIndexCardinalityStats(IndexStatisticsDaemonImpl.java:1223) at org.apache.derby.impl.services.daemon.IndexStatisticsDaemonImpl.updateIndexStatsMinion(IndexStatisticsDaemonImpl.java:531) at org.apache.derby.impl.services.daemon.IndexStatisticsDaemonImpl.generateStatistics(IndexStatisticsDaemonImpl.java:325) at org.apache.derby.impl.services.daemon.IndexStatisticsDaemonImpl.processingLoop(IndexStatisticsDaemonImpl.java:801) at org.apache.derby.impl.services.daemon.IndexStatisticsDaemonImpl.run(IndexStatisticsDaemonImpl.java:717) at java.lang.Thread.run(Thread.java:662) (Skipping thread dump because of insufficient permissions: java.security.AccessControlException: access denied (java.lang.RuntimePermission getStackTrace)) Exception in thread "index-stat-thread" org.apache.derby.shared.common.sanity.AssertFailure: ASSERT FAILED at org.apache.derby.shared.common.sanity.SanityManager.ASSERT(SanityManager.java:98) at org.apache.derby.impl.services.daemon.IndexStatisticsDaemonImpl.vetIndexCardinalityStats(IndexStatisticsDaemonImpl.java:1223) at org.apache.derby.impl.services.daemon.IndexStatisticsDaemonImpl.updateIndexStatsMinion(IndexStatisticsDaemonImpl.java:531) at org.apache.derby.impl.services.daemon.IndexStatisticsDaemonImpl.generateStatistics(IndexStatisticsDaemonImpl.java:325) at org.apache.derby.impl.services.daemon.IndexStatisticsDaemonImpl.processingLoop(IndexStatisticsDaemonImpl.java:801) at org.apache.derby.impl.services.daemon.IndexStatisticsDaemonImpl.run(IndexStatisticsDaemonImpl.java:717) at java.lang.Thread.run(Thread.java:662) (Skipping thread dump because of insufficient permissions: java.security.AccessControlException: access denied (java.lang.RuntimePermission getStackTrace))
          Hide
          Brett Bergquist added a comment -

          I commented out the ASSERT and tried it again. It appears to be working correctly. I can see the orphan statistics being dropped. I shutdown the database and restarted and there are no more orphans being detected the second time around.

          May the asserted condition is not valid?

          Show
          Brett Bergquist added a comment - I commented out the ASSERT and tried it again. It appears to be working correctly. I can see the orphan statistics being dropped. I shutdown the database and restarted and there are no more orphans being detected the second time around. May the asserted condition is not valid?
          Hide
          Mike Matrigali added a comment -

          one way to test this without having to hack anything into the catalog is to turn it into an upgrade test and create the bad rows using a past release with the bug in it. Even if we backport the eventual fix, those old versions will always generate the bad row.

          Show
          Mike Matrigali added a comment - one way to test this without having to hack anything into the catalog is to turn it into an upgrade test and create the bad rows using a past release with the bug in it. Even if we backport the eventual fix, those old versions will always generate the bad row.
          Hide
          Mike Matrigali added a comment -

          I reviewed the change, could use a few more comments for those of us not familar with data design of the statistics catalog, but I would
          have expected it fix the problem. Not sure what the issue with the assert is, there is a comment about when we turn on write mode, but
          not sure when it is expected to get turned off - maybe at commit?

          I was hoping the change could be less invasive by just dropping all statistics even bad ones upfront with no checking, ie. not have to reget all the valid rows and recheck them always. But I see the current
          code logic wants to drop statistics one at a time, and that were problems trying to invalidate all at once which might be similar to problems with dropping all rows at once.

          It is not part of this change, but is there a possible problem with when invalidateStatements is called? Unless you need it for some
          locking reason it would seem better to invalidate after you do the update than in the middle.
          Can queries sneak in between when a statistic is dropped and when you insert the new updated statistic? If so it would be another reason
          not to drop them all up front as it would increase the window. brett was reporting some wierdness with working queries getting recompiled
          to bad plans. I think we can think of his system as always having concurrent processors availble to run statements constantly, so they
          can almost instantaneously start recompiling once you invalidate their plan. Now if they can get in after the invalidate, after the drop but before
          the insert then they probably have worse stats (ie. no stats), vs the old stats.

          Show
          Mike Matrigali added a comment - I reviewed the change, could use a few more comments for those of us not familar with data design of the statistics catalog, but I would have expected it fix the problem. Not sure what the issue with the assert is, there is a comment about when we turn on write mode, but not sure when it is expected to get turned off - maybe at commit? I was hoping the change could be less invasive by just dropping all statistics even bad ones upfront with no checking, ie. not have to reget all the valid rows and recheck them always. But I see the current code logic wants to drop statistics one at a time, and that were problems trying to invalidate all at once which might be similar to problems with dropping all rows at once. It is not part of this change, but is there a possible problem with when invalidateStatements is called? Unless you need it for some locking reason it would seem better to invalidate after you do the update than in the middle. Can queries sneak in between when a statistic is dropped and when you insert the new updated statistic? If so it would be another reason not to drop them all up front as it would increase the window. brett was reporting some wierdness with working queries getting recompiled to bad plans. I think we can think of his system as always having concurrent processors availble to run statements constantly, so they can almost instantaneously start recompiling once you invalidate their plan. Now if they can get in after the invalidate, after the drop but before the insert then they probably have worse stats (ie. no stats), vs the old stats.
          Hide
          Brett Bergquist added a comment -

          I have run a patched version of 10.8.2.2 (without the assert) overnight on a copy of the production database where the problem was occurring. It has worked fine, cleaned up the orphan statistics rows, and now only does the job it should. Just some feedback from testing.

          Show
          Brett Bergquist added a comment - I have run a patched version of 10.8.2.2 (without the assert) overnight on a copy of the production database where the problem was occurring. It has worked fine, cleaned up the orphan statistics rows, and now only does the job it should. Just some feedback from testing.
          Hide
          Kristian Waagan added a comment -

          Thanks a lot for testing the patch, Brett.

          I'm testing some other improvements under DERBY-5684, but it will take a while before any of it is production ready There's a patch there, but I'm not sure how well it will do out in the wild...

          Show
          Kristian Waagan added a comment - Thanks a lot for testing the patch, Brett. I'm testing some other improvements under DERBY-5684 , but it will take a while before any of it is production ready There's a patch there, but I'm not sure how well it will do out in the wild...
          Hide
          Mamta A. Satoor added a comment -

          Apologies up front if this has already been discussed.

          But I was wondering if all the stats could be dropped up front during hard upgrade time(this will take care of orphaned stats. If there is no way of clearing everything in the statistics table, then we could drop stats one table at a time at the time of hard upgrade).

          Statistic creation deamon will have to collect stats again since we have dropped all the stats at upgrade time but since stat gathering happens in background, it probably won't be very noticeable.

          We know of one case DERBY-5681 where a drop constraint can leave orphaned stats row. There is already a patch on that issue which will make sure that we do not have such stats row left behind. If that is the only case where orphaned stats get left behind, then we shouldn't run into orphaned stats again on an upgraded database.

          Show
          Mamta A. Satoor added a comment - Apologies up front if this has already been discussed. But I was wondering if all the stats could be dropped up front during hard upgrade time(this will take care of orphaned stats. If there is no way of clearing everything in the statistics table, then we could drop stats one table at a time at the time of hard upgrade). Statistic creation deamon will have to collect stats again since we have dropped all the stats at upgrade time but since stat gathering happens in background, it probably won't be very noticeable. We know of one case DERBY-5681 where a drop constraint can leave orphaned stats row. There is already a patch on that issue which will make sure that we do not have such stats row left behind. If that is the only case where orphaned stats get left behind, then we shouldn't run into orphaned stats again on an upgraded database.
          Hide
          Brett Bergquist added a comment -

          I don't like arbitrarily dropping the stats at hard upgrade time. Statistics are needed when tables have a large number of rows and it does take time and resources to recreate them. So while in the general case of testing with a few thousand rows, this may be acceptable, in my particular case with tables with many millions of rows being actively inserted and queried upon, I don't think this is best.

          Cleaning up orphaned statistics tables that are not being used but triggering the indexStat daemon is acceptable but blindly dropping all statistics is not I don't believe.

          Show
          Brett Bergquist added a comment - I don't like arbitrarily dropping the stats at hard upgrade time. Statistics are needed when tables have a large number of rows and it does take time and resources to recreate them. So while in the general case of testing with a few thousand rows, this may be acceptable, in my particular case with tables with many millions of rows being actively inserted and queried upon, I don't think this is best. Cleaning up orphaned statistics tables that are not being used but triggering the indexStat daemon is acceptable but blindly dropping all statistics is not I don't believe.
          Hide
          Kristian Waagan added a comment -

          Attaching patch 1b, which adds functionality to the update statistics code to drop disposable statistics entries.

          For now disposable statistics consist only of orphaned rows, but when DERBY-3790 goes in there will also be entries for single-column primary keys. For the check and dropping to take place, either the istat daemon must kick in or the SYSCS_UPDATE_STATISTICS system procedure must be run with null as the argument for which index to update (i.e. tell it to update statistics for all indexes). This restriction allows for simpler login when identifying disposable stats: drop all statistics entries we won't update.

          Description of the changes:

          • impl/sql/execute/AlterTableConstantAction
            Enable identification of disposable stats when no index is specified when invoking SYSCS_UPDATE_STATISTICS.
          • impl/services/daemon/IndexStatisticsDaemonImpl
            Added a property to allow users to force the old behavior.
            Added logic to not identify disposable statistics on old databases (soft upgrade).
            Made the same change as in AlterTableConstantAction (pass null).
            Added the method logAlways.
          • iapi/reference/Property
            Added property "derby.storage.indexStats.debug.forceOldBehavior".
          • functionTests/tests/lang/UpdateStatisticsTest
            Added testDisposableStatsEagerness, which verifies that the new code doesn't remove entries it should leave alone.
            There will be more tests as part of DERBY-3790 (upgrade test + testing the forceOldBehavior property).

          To sum up, all existing tests should continue to work with this patch. This is not the case with what's going in for DERBY-3790, as that patch will reduce the number of statistics entries for tables with single-column primary keys.

          Patch ready for review.
          I plan to commit pretty soon, so if anyone has planned to review the code it would be nice if it happened sooner than later

          Thanks to Dag who has already reviewed the prototype code under DERBY-5684. Since I decided to drop what I was working on for foreign indexes, which I now believe wasn't doing the correct thing, I also decided to drop the class IndexStatisticsAnalyzer. I found it to be a bit heavy-weight already, but with the second optimization pulled out it was simply just too much (over-engineered).

          Show
          Kristian Waagan added a comment - Attaching patch 1b, which adds functionality to the update statistics code to drop disposable statistics entries. For now disposable statistics consist only of orphaned rows, but when DERBY-3790 goes in there will also be entries for single-column primary keys. For the check and dropping to take place, either the istat daemon must kick in or the SYSCS_UPDATE_STATISTICS system procedure must be run with null as the argument for which index to update (i.e. tell it to update statistics for all indexes). This restriction allows for simpler login when identifying disposable stats: drop all statistics entries we won't update. Description of the changes: impl/sql/execute/AlterTableConstantAction Enable identification of disposable stats when no index is specified when invoking SYSCS_UPDATE_STATISTICS. impl/services/daemon/IndexStatisticsDaemonImpl Added a property to allow users to force the old behavior. Added logic to not identify disposable statistics on old databases (soft upgrade). Made the same change as in AlterTableConstantAction (pass null). Added the method logAlways. iapi/reference/Property Added property "derby.storage.indexStats.debug.forceOldBehavior". functionTests/tests/lang/UpdateStatisticsTest Added testDisposableStatsEagerness, which verifies that the new code doesn't remove entries it should leave alone. There will be more tests as part of DERBY-3790 (upgrade test + testing the forceOldBehavior property). To sum up, all existing tests should continue to work with this patch. This is not the case with what's going in for DERBY-3790 , as that patch will reduce the number of statistics entries for tables with single-column primary keys. Patch ready for review. I plan to commit pretty soon, so if anyone has planned to review the code it would be nice if it happened sooner than later Thanks to Dag who has already reviewed the prototype code under DERBY-5684 . Since I decided to drop what I was working on for foreign indexes, which I now believe wasn't doing the correct thing, I also decided to drop the class IndexStatisticsAnalyzer. I found it to be a bit heavy-weight already, but with the second optimization pulled out it was simply just too much (over-engineered).
          Hide
          Dag H. Wanvik added a comment -

          Thanks, Kristian. Went through the code and it looks right to me. One question:

          updateIndexStatsMinion:

          • Are these lined needed (I know they are not wrong, but possibly redundant)?

          if (conglomerateNumber[ci] == -1)

          { continue; }
          Show
          Dag H. Wanvik added a comment - Thanks, Kristian. Went through the code and it looks right to me. One question: updateIndexStatsMinion: Are these lined needed (I know they are not wrong, but possibly redundant)? if (conglomerateNumber [ci] == -1) { continue; }
          Hide
          Kristian Waagan added a comment -

          Thanks, Dag.

          You're right about the check being redundant, because objectUUID[i] will be null. I'll correct this after the patch for DERBY-3790 is in, as it will include a better test for this code (I'll first force old behavior to generate stats for single-column primary keys and then run with the new behavior to confirm that they're dropped). There will also be an upgrade test making sure the orphaned row caused by DERBY-5681 is dropped.

          I committed patch 1b to trunk with revision 1340549.
          I will not mark this issue as resolved before the patch for DERBY-3790, which includes more tests, is committed.

          Show
          Kristian Waagan added a comment - Thanks, Dag. You're right about the check being redundant, because objectUUID [i] will be null. I'll correct this after the patch for DERBY-3790 is in, as it will include a better test for this code (I'll first force old behavior to generate stats for single-column primary keys and then run with the new behavior to confirm that they're dropped). There will also be an upgrade test making sure the orphaned row caused by DERBY-5681 is dropped. I committed patch 1b to trunk with revision 1340549. I will not mark this issue as resolved before the patch for DERBY-3790 , which includes more tests, is committed.
          Hide
          Knut Anders Hatlen added a comment -

          Should we call the property "keepDisposableStats" or something more descriptive instead of "forceOldBehavior"? Just so that we don't have to call the next property "forceNotQuiteSoOldBehavior"...

          Show
          Knut Anders Hatlen added a comment - Should we call the property "keepDisposableStats" or something more descriptive instead of "forceOldBehavior"? Just so that we don't have to call the next property "forceNotQuiteSoOldBehavior"...
          Hide
          Kristian Waagan added a comment -

          We can do that, I'll append it to the todo-list
          Note that I do not plan to document this property. Regardless of whether the feature demonstrates it usefulness or not (which is a more relevant question for DERBY-3790), the property will be removed in a future release. In the former case it will simply be removed, in the latter case it will be removed together with the feature itself.

          Show
          Kristian Waagan added a comment - We can do that, I'll append it to the todo-list Note that I do not plan to document this property. Regardless of whether the feature demonstrates it usefulness or not (which is a more relevant question for DERBY-3790 ), the property will be removed in a future release. In the former case it will simply be removed, in the latter case it will be removed together with the feature itself.
          Hide
          Kristian Waagan added a comment -

          Attaching patch 2a, which removes the redundant check Dag spotted. The tests passed without the check.

          Committed patch 2a to trunk with revision 1341622.

          Show
          Kristian Waagan added a comment - Attaching patch 2a, which removes the redundant check Dag spotted. The tests passed without the check. Committed patch 2a to trunk with revision 1341622.
          Hide
          Kristian Waagan added a comment -

          Attaching patch 3a, which renames the debug property to give it a more descriptive name as suggested by Knut Anders.

          Committed to trunk with revision 1341629.

          Show
          Kristian Waagan added a comment - Attaching patch 3a, which renames the debug property to give it a more descriptive name as suggested by Knut Anders. Committed to trunk with revision 1341629.
          Hide
          Kristian Waagan added a comment -

          Resolving issue, the outstanding tests have been committed under DERBY-3790.

          Show
          Kristian Waagan added a comment - Resolving issue, the outstanding tests have been committed under DERBY-3790 .
          Hide
          Mike Matrigali added a comment -

          Is this fix ok to backport to 10.8? I think DERBY-3790 should not be backported as it is a feature, so most of need for this issue is not in 10.8. But
          I think there are some cases in 10.8 that are fixed by this, correct?

          Show
          Mike Matrigali added a comment - Is this fix ok to backport to 10.8? I think DERBY-3790 should not be backported as it is a feature, so most of need for this issue is not in 10.8. But I think there are some cases in 10.8 that are fixed by this, correct?
          Hide
          Mike Matrigali added a comment -

          much of the discussion about this issue happened on the list before the bug was created. Here is a link to the
          beginning of the discussion (would of liked to post a link to the whole thread but could not figure out how to do that), i believe
          the net is that one part of the istat daemon code finds the orphan stat row and recognizes that the number of rows is bad, but the actual
          update code just updates the good rows - thus the output looks like it is updating all rows to same values. I might be good to enhance the
          istat debugging code to print out before info for all stats so it is obvious we are running into the bug.:
          http://mail-archives.apache.org/mod_mbox/db-derby-dev/201203.mbox/%3C97EB699F861AD841B5908C7CA9C9565602065E174BD4%40VSERVER1.canoga.com%3E

          Show
          Mike Matrigali added a comment - much of the discussion about this issue happened on the list before the bug was created. Here is a link to the beginning of the discussion (would of liked to post a link to the whole thread but could not figure out how to do that), i believe the net is that one part of the istat daemon code finds the orphan stat row and recognizes that the number of rows is bad, but the actual update code just updates the good rows - thus the output looks like it is updating all rows to same values. I might be good to enhance the istat debugging code to print out before info for all stats so it is obvious we are running into the bug.: http://mail-archives.apache.org/mod_mbox/db-derby-dev/201203.mbox/%3C97EB699F861AD841B5908C7CA9C9565602065E174BD4%40VSERVER1.canoga.com%3E
          Hide
          Mamta A. Satoor added a comment -

          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.

          Show
          Mamta A. Satoor added a comment - 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.
          Hide
          Kathey Marsden added a comment -

          Thanks Mamta for backporting this.

          I think that if we are backporting to 10.8 we should also drop the disposable indexes for pre 10.8 databases, so that databases that have not been hard upgraded will get the fix.

          I have a database with the problem that is still in an earlier format and soft upgraded to 10.8 that needs to have the disposable indexes dropped. I think, because the database is in a prior version format, the fix does not kick in.

          It makes sense for 10.9 to have the version check for the newly disposable DERBY-3790 disposables, but I thiink the just wrong ones resulting from DERBY-5681 or something similar should always go. I think for the 10.8 fix it will be enough to just remove the check, but perhaps 10.9 and 10.10 will have to be changed to differentiate or maybe it is ok even for those to drop all the disposables too?

          Show
          Kathey Marsden added a comment - Thanks Mamta for backporting this. I think that if we are backporting to 10.8 we should also drop the disposable indexes for pre 10.8 databases, so that databases that have not been hard upgraded will get the fix. I have a database with the problem that is still in an earlier format and soft upgraded to 10.8 that needs to have the disposable indexes dropped. I think, because the database is in a prior version format, the fix does not kick in. It makes sense for 10.9 to have the version check for the newly disposable DERBY-3790 disposables, but I thiink the just wrong ones resulting from DERBY-5681 or something similar should always go. I think for the 10.8 fix it will be enough to just remove the check, but perhaps 10.9 and 10.10 will have to be changed to differentiate or maybe it is ok even for those to drop all the disposables too?
          Hide
          Kathey Marsden added a comment -

          With hard upgrade, the disposables get dropped properly and index statistics does not kick in. Now that I think about it, since soft upgrade is also a problem for 10.9 and later versions, maybe we should commit your change as a clean backport of this fix and then open a new issue for soft upgrade dropping disposables that are remnants of DERBY-5681, etc.

          Show
          Kathey Marsden added a comment - With hard upgrade, the disposables get dropped properly and index statistics does not kick in. Now that I think about it, since soft upgrade is also a problem for 10.9 and later versions, maybe we should commit your change as a clean backport of this fix and then open a new issue for soft upgrade dropping disposables that are remnants of DERBY-5681 , etc.
          Hide
          Mamta A. Satoor added a comment -

          Thanks for reviewing the patch, Kathey. I will wait until tomorrow in case if there are any more feedbacks.

          Like you suggested, we can take the soft upgrade case as a separate issue but I was wondering if anyone might know why we did we decide to not cleanup orphaned state rows in case of soft upgrade mode in the original changes for this jira? I see comments in the code and in the commit comment that we do not want to cleanup in soft upgrade mode but I couldn't quite comprehend why. Thanks

          Show
          Mamta A. Satoor added a comment - Thanks for reviewing the patch, Kathey. I will wait until tomorrow in case if there are any more feedbacks. Like you suggested, we can take the soft upgrade case as a separate issue but I was wondering if anyone might know why we did we decide to not cleanup orphaned state rows in case of soft upgrade mode in the original changes for this jira? I see comments in the code and in the commit comment that we do not want to cleanup in soft upgrade mode but I couldn't quite comprehend why. Thanks
          Hide
          Mike Matrigali added a comment -

          I agree with kathey, let's just backport this to 10.8 and open a new issue to correctly handle the looping problem for soft upgrade in all versions. Otherwise we might end up with 10.8 fixing
          the problem for soft upgrade but not 10.9, 10.10 and trunk.

          Hopefully we can figure out a way to handle
          the buggy orphaned stat differently than the expected "disposable" stats and deal with them differently. If you hit this problem then the problem is very serious, as it can eat all the cpu on
          at least one processor, which may be the whole machine, it also may be hard to diagnose as those with multiple processors may just notice that "derby is slow and slowing the rest of the
          machine".

          Can anyone think of a reason that the truly orphaned stat (an existing stat for no existing index) would be useful for any previous version of the software? I think worst case if someone had to
          downgrade after we deleted the orphaned stat and it was somehow needed then re-creating indexes would fix that issue.

          Show
          Mike Matrigali added a comment - I agree with kathey, let's just backport this to 10.8 and open a new issue to correctly handle the looping problem for soft upgrade in all versions. Otherwise we might end up with 10.8 fixing the problem for soft upgrade but not 10.9, 10.10 and trunk. Hopefully we can figure out a way to handle the buggy orphaned stat differently than the expected "disposable" stats and deal with them differently. If you hit this problem then the problem is very serious, as it can eat all the cpu on at least one processor, which may be the whole machine, it also may be hard to diagnose as those with multiple processors may just notice that "derby is slow and slowing the rest of the machine". Can anyone think of a reason that the truly orphaned stat (an existing stat for no existing index) would be useful for any previous version of the software? I think worst case if someone had to downgrade after we deleted the orphaned stat and it was somehow needed then re-creating indexes would fix that issue.
          Hide
          Mamta A. Satoor added a comment -

          reopening it for backport to 10.8

          Show
          Mamta A. Satoor added a comment - reopening it for backport to 10.8
          Hide
          Mamta A. Satoor added a comment -

          junit suite ran fine with the patch. still waiting for derbyall to finish

          Show
          Mamta A. Satoor added a comment - junit suite ran fine with the patch. still waiting for derbyall to finish
          Hide
          Mike Matrigali added a comment -

          I have added DERBY-6283 to track discussion and fix for the soft upgrade case of this bug.

          Show
          Mike Matrigali added a comment - I have added DERBY-6283 to track discussion and fix for the soft upgrade case of this bug.
          Hide
          Mamta A. Satoor added a comment -

          derbyall ran fine too. I will go ahead and commit the patch to backport the changes to 10.8

          Show
          Mamta A. Satoor added a comment - derbyall ran fine too. I will go ahead and commit the patch to backport the changes to 10.8
          Hide
          ASF subversion and git services added a comment -

          Commit 1497868 from Mamta A. Satoor
          [ 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.

          Show
          ASF subversion and git services added a comment - Commit 1497868 from Mamta A. Satoor [ 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.
          Hide
          ASF subversion and git services added a comment -

          Commit 1497868 from Mamta A. Satoor
          [ 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.

          Show
          ASF subversion and git services added a comment - Commit 1497868 from Mamta A. Satoor [ 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.
          Hide
          Mamta A. Satoor added a comment -

          Committed the backport to 10.8 with revision 1497868

          Show
          Mamta A. Satoor added a comment - Committed the backport to 10.8 with revision 1497868
          Hide
          ASF subversion and git services added a comment -

          Commit 1498173 from Mamta A. Satoor
          [ https://svn.apache.org/r1498173 ]

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

          KeepDisposableStatsPropertyTest test is meant for DERBY-3790 which is not in in 10.8 codeline and hence should not be run in 10.8

          Show
          ASF subversion and git services added a comment - Commit 1498173 from Mamta A. Satoor [ https://svn.apache.org/r1498173 ] DERBY-5680 (indexStat daemon processing tables over and over even when there are no changes in the tables) KeepDisposableStatsPropertyTest test is meant for DERBY-3790 which is not in in 10.8 codeline and hence should not be run in 10.8
          Hide
          ASF subversion and git services added a comment -

          Commit 1502319 from mikem@apache.org
          [ https://svn.apache.org/r1502319 ]

          DERBY-6283 indexStat daemon processing tables over and over even when there are no changes in the tables in soft upgraded database.

          Changed system to always drop orphaned stats during update statistics call.

          Without this change soft upgraded systems running on 10.8 or higher derby
          software, that had an orphaned statistic would spin forever in the index
          stat daemon due to the same problem fixed by DERBY-5680 for hard
          upgraded databases.

          Show
          ASF subversion and git services added a comment - Commit 1502319 from mikem@apache.org [ https://svn.apache.org/r1502319 ] DERBY-6283 indexStat daemon processing tables over and over even when there are no changes in the tables in soft upgraded database. Changed system to always drop orphaned stats during update statistics call. Without this change soft upgraded systems running on 10.8 or higher derby software, that had an orphaned statistic would spin forever in the index stat daemon due to the same problem fixed by DERBY-5680 for hard upgraded databases.
          Hide
          ASF subversion and git services added a comment -

          Commit 1502706 from mikem@apache.org
          [ https://svn.apache.org/r1502706 ]

          DERBY-6283 indexStat daemon processing tables over and over even when there are no changes in the tables in soft upgraded database.

          backported change #1502319 from trunk to 10.10, some manual merging was
          required.

          Changed system to always drop orphaned stats during update statistics call.

          Without this change soft upgraded systems running on 10.8 or higher derby
          software, that had an orphaned statistic would spin forever in the index
          stat daemon due to the same problem fixed by DERBY-5680 for hard
          upgraded databases.

          Show
          ASF subversion and git services added a comment - Commit 1502706 from mikem@apache.org [ https://svn.apache.org/r1502706 ] DERBY-6283 indexStat daemon processing tables over and over even when there are no changes in the tables in soft upgraded database. backported change #1502319 from trunk to 10.10, some manual merging was required. Changed system to always drop orphaned stats during update statistics call. Without this change soft upgraded systems running on 10.8 or higher derby software, that had an orphaned statistic would spin forever in the index stat daemon due to the same problem fixed by DERBY-5680 for hard upgraded databases.
          Hide
          Knut Anders Hatlen added a comment -

          I don't know if it's related, but UpdateStatisticsTest has failed twice on the 10.8 branch after the fix was backported:

          http://download.java.net/javadesktop/derby/javadb-5579017-report/javadb-task-3685070.html

          junit.framework.AssertionFailedError: Index statistics for <ALL TABLES>
          1:

          {tableId=25347265-013f-8c71-7445-fffff6662cff, tableName=DISPOSABLE_STATS_EAGERNESS_FK, indexName=SQL130628223832620, lcols=1, rows=1000, unique/card=1000, created=2013-06-28 22:38:32.672}

          2:

          {tableId=f0cfb26b-013f-8c71-7445-fffff6662cff, tableName=DISPOSABLE_STATS_EAGERNESS, indexName=NU_DISPOSABLE_STATS_EAGERNESS, lcols=1, rows=1000, unique/card=35, created=2013-06-28 22:38:32.688}

          3:

          {tableId=f0cfb26b-013f-8c71-7445-fffff6662cff, tableName=DISPOSABLE_STATS_EAGERNESS, indexName=SQL130628223832640, lcols=1, rows=1000, unique/card=1000, created=2013-06-28 22:38:32.672}

          4:

          {tableId=f0cfb26b-013f-8c71-7445-fffff6662cff, tableName=DISPOSABLE_STATS_EAGERNESS, indexName=SQL130628223832621, lcols=1, rows=1000, unique/card=1000, created=2013-06-28 22:38:32.672}

          5:

          {tableId=f0cfb26b-013f-8c71-7445-fffff6662cff, tableName=DISPOSABLE_STATS_EAGERNESS, indexName=SQL130628223832621, lcols=2, rows=1000, unique/card=1000, created=2013-06-28 22:38:32.672}

          expected:<0> but was:<5>
          at org.apache.derbyTesting.junit.IndexStatsUtil.assertStats(IndexStatsUtil.java:132)
          at org.apache.derbyTesting.junit.IndexStatsUtil.assertNoStats(IndexStatsUtil.java:109)
          at org.apache.derbyTesting.functionTests.tests.lang.UpdateStatisticsTest.testUpdateStatistics(UpdateStatisticsTest.java:93)
          at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:113)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)

          http://download.java.net/javadesktop/derby/javadb-5579314-report/javadb-task-3689573.html

          junit.framework.AssertionFailedError
          at org.apache.derbyTesting.functionTests.tests.lang.UpdateStatisticsTest.testDisposableStatsEagerness(UpdateStatisticsTest.java:481)
          at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:113)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)

          Show
          Knut Anders Hatlen added a comment - I don't know if it's related, but UpdateStatisticsTest has failed twice on the 10.8 branch after the fix was backported: http://download.java.net/javadesktop/derby/javadb-5579017-report/javadb-task-3685070.html junit.framework.AssertionFailedError: Index statistics for <ALL TABLES> 1: {tableId=25347265-013f-8c71-7445-fffff6662cff, tableName=DISPOSABLE_STATS_EAGERNESS_FK, indexName=SQL130628223832620, lcols=1, rows=1000, unique/card=1000, created=2013-06-28 22:38:32.672} 2: {tableId=f0cfb26b-013f-8c71-7445-fffff6662cff, tableName=DISPOSABLE_STATS_EAGERNESS, indexName=NU_DISPOSABLE_STATS_EAGERNESS, lcols=1, rows=1000, unique/card=35, created=2013-06-28 22:38:32.688} 3: {tableId=f0cfb26b-013f-8c71-7445-fffff6662cff, tableName=DISPOSABLE_STATS_EAGERNESS, indexName=SQL130628223832640, lcols=1, rows=1000, unique/card=1000, created=2013-06-28 22:38:32.672} 4: {tableId=f0cfb26b-013f-8c71-7445-fffff6662cff, tableName=DISPOSABLE_STATS_EAGERNESS, indexName=SQL130628223832621, lcols=1, rows=1000, unique/card=1000, created=2013-06-28 22:38:32.672} 5: {tableId=f0cfb26b-013f-8c71-7445-fffff6662cff, tableName=DISPOSABLE_STATS_EAGERNESS, indexName=SQL130628223832621, lcols=2, rows=1000, unique/card=1000, created=2013-06-28 22:38:32.672} expected:<0> but was:<5> at org.apache.derbyTesting.junit.IndexStatsUtil.assertStats(IndexStatsUtil.java:132) at org.apache.derbyTesting.junit.IndexStatsUtil.assertNoStats(IndexStatsUtil.java:109) at org.apache.derbyTesting.functionTests.tests.lang.UpdateStatisticsTest.testUpdateStatistics(UpdateStatisticsTest.java:93) at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:113) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) http://download.java.net/javadesktop/derby/javadb-5579314-report/javadb-task-3689573.html junit.framework.AssertionFailedError at org.apache.derbyTesting.functionTests.tests.lang.UpdateStatisticsTest.testDisposableStatsEagerness(UpdateStatisticsTest.java:481) at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:113) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          Hide
          Mamta A. Satoor added a comment -

          Hi Knut, I looked at the diff for UpdateStatisticsTest.testUpdateStatistics at http://download.java.net/javadesktop/derby/javadb-5579017-report/javadb-task-3685070.html and see that the test failed with svn revision 1497868. The reason for failure was DERBY-5774: Failures in UpdateStatisticsTest (order-dependent test cases) which got fixed into 10.8 with revision 1498052. After that checkin in 10.8, the test has not failed again yet.

          As for UpdateStatisticsTest.testDisposableStatsEagerness, I need to study the failure more. I do not know if it is related to fix for this jira in 10.8 but let me understand the test more to see what does the failure mean. Thanks

          Show
          Mamta A. Satoor added a comment - Hi Knut, I looked at the diff for UpdateStatisticsTest.testUpdateStatistics at http://download.java.net/javadesktop/derby/javadb-5579017-report/javadb-task-3685070.html and see that the test failed with svn revision 1497868. The reason for failure was DERBY-5774 : Failures in UpdateStatisticsTest (order-dependent test cases) which got fixed into 10.8 with revision 1498052. After that checkin in 10.8, the test has not failed again yet. As for UpdateStatisticsTest.testDisposableStatsEagerness, I need to study the failure more. I do not know if it is related to fix for this jira in 10.8 but let me understand the test more to see what does the failure mean. Thanks
          Hide
          Mamta A. Satoor added a comment -

          UpdateStatisticsTest.testDisposableStatsEagerness may be because of DERBY-5797(AssertionFailedError in functionTests.tests.lang.UpdateStatisticsTest.testDisposableStatsEagerness). Fix for DERBY-5797 is in 10.10 and 10.9 but not in 10.8, I will work on backporting it to 10.8.

          Show
          Mamta A. Satoor added a comment - UpdateStatisticsTest.testDisposableStatsEagerness may be because of DERBY-5797 (AssertionFailedError in functionTests.tests.lang.UpdateStatisticsTest.testDisposableStatsEagerness). Fix for DERBY-5797 is in 10.10 and 10.9 but not in 10.8, I will work on backporting it to 10.8.
          Hide
          Mamta A. Satoor added a comment -

          Fix for DERBY-5797 has been backported to 10.8

          Show
          Mamta A. Satoor added a comment - Fix for DERBY-5797 has been backported to 10.8
          Hide
          ASF subversion and git services added a comment -

          Commit 1505021 from mikem@apache.org in branch 'code/branches/10'
          [ https://svn.apache.org/r1505021 ]

          DERBY-6283 indexStat daemon processing tables over and over even when there are
          no changes in the tables in soft upgraded database.

          backported change #1502319 from trunk to 10.8, some manual merging was
          required.

          Changed system to always drop orphaned stats during update statistics call.

          Without this change soft upgraded systems running on 10.8 or higher derby
          software, that had an orphaned statistic would spin forever in the index
          stat daemon due to the same problem fixed by DERBY-5680 for hard
          upgraded databases.

          Show
          ASF subversion and git services added a comment - Commit 1505021 from mikem@apache.org in branch 'code/branches/10' [ https://svn.apache.org/r1505021 ] DERBY-6283 indexStat daemon processing tables over and over even when there are no changes in the tables in soft upgraded database. backported change #1502319 from trunk to 10.8, some manual merging was required. Changed system to always drop orphaned stats during update statistics call. Without this change soft upgraded systems running on 10.8 or higher derby software, that had an orphaned statistic would spin forever in the index stat daemon due to the same problem fixed by DERBY-5680 for hard upgraded databases.
          Hide
          ASF subversion and git services added a comment -

          Commit 1505054 from mikem@apache.org in branch 'code/branches/10'
          [ https://svn.apache.org/r1505054 ]

          DERBY-6283 indexStat daemon processing tables over and over even when there are
          no changes in the tables in soft upgraded database.

          backported change #1502319 from trunk to 10.8, some manual merging was
          required.

          Changed system to always drop orphaned stats during update statistics call.

          Without this change soft upgraded systems running on 10.8 or higher derby
          software, that had an orphaned statistic would spin forever in the index
          stat daemon due to the same problem fixed by DERBY-5680 for hard
          upgraded databases.

          Show
          ASF subversion and git services added a comment - Commit 1505054 from mikem@apache.org in branch 'code/branches/10' [ https://svn.apache.org/r1505054 ] DERBY-6283 indexStat daemon processing tables over and over even when there are no changes in the tables in soft upgraded database. backported change #1502319 from trunk to 10.8, some manual merging was required. Changed system to always drop orphaned stats during update statistics call. Without this change soft upgraded systems running on 10.8 or higher derby software, that had an orphaned statistic would spin forever in the index stat daemon due to the same problem fixed by DERBY-5680 for hard upgraded databases.

            People

            • Assignee:
              Kristian Waagan
              Reporter:
              Brett Bergquist
            • Votes:
              0 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development