db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kristian Waagan (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DERBY-5680) indexStat daemon processing tables over an over even when there are no changes in the tables
Date Fri, 18 May 2012 15:35:08 GMT

     [ https://issues.apache.org/jira/browse/DERBY-5680?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Kristian Waagan updated DERBY-5680:
-----------------------------------

    Attachment: derby-5680-1b-remove_disposable_stats.diff

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).
                
> indexStat daemon processing tables over an 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
>            Reporter: Brett Bergquist
>            Assignee: Kristian Waagan
>         Attachments: derby-5680-1a-drop_orphaned_stats.diff, derby-5680-1b-remove_disposable_stats.diff
>
>
> 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: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

Mime
View raw message