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: Closed
    • 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_diff.txt
        24 kB
        Mamta A. Satoor
      2. DERBY5680_backportTo108_patch1_stat.txt
        0.6 kB
        Mamta A. Satoor
      3. derby-5680-1a-drop_orphaned_stats.diff
        3 kB
        Kristian Waagan
      4. derby-5680-1b-remove_disposable_stats.diff
        16 kB
        Kristian Waagan
      5. derby-5680-2a-remove_redundant_check.diff
        0.8 kB
        Kristian Waagan
      6. derby-5680-3a-rename_debug_property.diff
        2 kB
        Kristian Waagan

        Issue Links

          Activity

          Brett Bergquist created issue -
          Brett Bergquist made changes -
          Field Original Value New Value
          Link This issue is related to DERBY-5681 [ DERBY-5681 ]
          Kristian Waagan made changes -
          Attachment derby-5680-1a-drop_orphaned_stats.diff [ 12520976 ]
          Kristian Waagan made changes -
          Assignee Kristian Waagan [ kristwaa ]
          Kristian Waagan made changes -
          Status Open [ 1 ] In Progress [ 3 ]
          Kristian Waagan made changes -
          Kristian Waagan made changes -
          Fix Version/s 10.9.0.0 [ 12316344 ]
          Affects Version/s 10.9.0.0 [ 12316344 ]
          Kristian Waagan made changes -
          Kristian Waagan made changes -
          Kristian Waagan made changes -
          Status In Progress [ 3 ] Resolved [ 5 ]
          Resolution Fixed [ 1 ]
          Dag H. Wanvik made changes -
          Summary indexStat daemon processing tables over an over even when there are no changes in the tables indexStat daemon processing tables over and over even when there are no changes in the tables
          Mamta A. Satoor made changes -
          Mamta A. Satoor made changes -
          Resolution Fixed [ 1 ]
          Status Resolved [ 5 ] Reopened [ 4 ]
          Mike Matrigali made changes -
          Link This issue relates to DERBY-6283 [ DERBY-6283 ]
          Mamta A. Satoor made changes -
          Fix Version/s 10.8.3.1 [ 12323475 ]
          Mamta A. Satoor made changes -
          Status Reopened [ 4 ] Resolved [ 5 ]
          Resolution Fixed [ 1 ]
          Gavin made changes -
          Workflow jira [ 12660367 ] Default workflow, editable Closed status [ 12802392 ]
          Knut Anders Hatlen made changes -
          Status Resolved [ 5 ] Closed [ 6 ]

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Development