[
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
